JDBC 简化流程总结

分享 ⋅ AIZero ⋅ 于 2020-07-07 09:22:06 ⋅ 最后回复由 青牛 于 2020-07-07 18:20:55 ⋅ 3275 阅读

单纯JDBC连接数据库写法 

Connection conn = null;
Statement stml = null;
ResultSet rs = null;
try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
    stml = conn.createStatement();
    String sql = "select * from account";
    rs = stml.executeQuery(sql);
    while(rs.next()){
        int id = rs.getInt(1);
        String name = rs.getString(2);
        double balance = rs.getDouble(3);
        System.out.println(id + name + balance);
    }
} catch (SQLException | ClassNotFoundException throwables) {
    throwables.printStackTrace();
}finally {
    if(rs != null){                // 流的关闭不要合并
        try {
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    if(stml != null){
        try {
            stml.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    if(conn != null){
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

优化思路：去除代码过度冗余

  1. 载入驱动，获取连接代码重复

  2. 关闭文件流代码重复

JDBC工具类创建 

public class JDBCUtils {
    private static Connection conn = null;
    private static Statement stmt = null;
    private static final String URL = "jdbc:mysql://localhost:3306/db3";
    private static final String DRIVER= "com.mysql.jdbc.Driver";
    private static final String USER = "root";
    private static final String PWD = "root";
    static{
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306","root","root");
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL,USER,PWD);
    }
    public static void close(Statement stmt, Connection conn){
        close(null,stmt,conn);
    }
    public static void close(ResultSet rs , Statement stmt, Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

为避免数据注入问题，将Statment改为PrepareStatement来优化

public class JdbcDemo {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from account";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                double balance = rs.getDouble("balance");
                System.out.println(id + name + balance);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
    }
}

数据库连接池

定义：存放数据库连接的集合容器，当系统初始化好后被容器被创建，容器会申请连接对象，用户访问完成后将对象归还容器。节约资源，用户访问高效

Druid数据连接池：

  1. 导入jar包
  2. 定义配置文件：properities
  3. 加载配置文件
  4. 获取连接池对象：DruidDataSourceFactory
  5. 获取连接：getConnection

JDBCUtils连接池版本 

public class JDBCUtils {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/db3";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    private static DruidDataSource ds = new DruidDataSource();
    static {
        ds.setDriverClassName(DRIVER);
        ds.setUrl(URL);
        ds.setUsername(USER);
        ds.setPassword(PASSWORD);
    }
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static void close(Statement stmt, Connection conn){
        close(null,stmt,conn);
    }
    public static void close(ResultSet rs, Statement stmt, Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static DataSource getDataSource(){
        return ds;
    }
}

全流程优化

SpringJDBC和DBUtils对全流程进行优化，其中DBUtils虽然对全流程没把优化做到极致但也足够用，但SpringJDBC的事务用法我还在摸索之中

全流程优化的思路

  1. PrepareStatement的创建，sql封装，语句执行可以包装成一个函数来完成
  2. 结果集的返回可以指定特定类型并进行优化
  3. 通过数据库来快速创建对象集合

JDBCUtiles最终版本分文件编写 

public class JDBCUtils {
private static DataSource ds;
static{                              // src根目录下配置druid.properties文件
    try {
        Properties pro = new Properties();    
        pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
        ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    public static DataSource getDataSource(){
        return ds;
    }
}

放在src根目录下druid.properties文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db3
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000

JDBCUtiles最终版本单文件编写 

public class JDBCUtils0 {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/db3";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    private static DruidDataSource ds = new DruidDataSource();
    static {
        ds.setDriverClassName(DRIVER);
        ds.setUrl(URL);
        ds.setUsername(USER);
        ds.setPassword(PASSWORD);
    }
    public static DataSource getDataSource() {
        return ds;
    }
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

DBUtils全流程优化

使用方式

  1. 导入jar包
  2. 创建QueryRunner对象，依赖于数据源DataSource

QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

​ 3. 调用QueryRunner的方法来完成CRUD的操作

增删改优化：update

public class JdbcDemo2 {
    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    @Test
    public void test01() throws SQLException {
        String sql = "update account set balance = 20000 where id = 1";
        int count = qr.update(sql);
        System.out.println(count);
    }
    @Test
    public void test02() throws SQLException {
        String sql = "insert into account values (?,?,?)";
        Object[] params = {6,"关羽",10000};
        int count = qr.update(sql, params);
        System.out.println(count);
    }
    @Test
    public void test03() throws SQLException {
        String sql = "delete from account where id = ?";
        Object[] params = {6};
        qr.update(sql,params); 
    }
}

结果查询优化

  1. MapHandler()：获取单条记录，直接形成单个map
  2. MapListHandler()：获取多条记录，形成map集合
  3. ScalarHandler()：根据记录定义返回值类型，一般与聚合函数集合
public class JdbcDemo3 {
    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    // 使用MapHandler()返回的是一行数据的map的集合体
    @Test
    public void test01() throws SQLException {
        String sql = "select * from account where id = ?";
        Object[] params = {6};
        Map<String, Object> maps = qr.query(sql, new MapHandler(), params);
        System.out.println(maps);
    }
    // 使用MapListHandler()返回的是多行数据的map集合的list集合
    @Test
    public void test02() throws SQLException {
        String sql = "select * from account";
        List<Map<String, Object>> maplist = qr.query(sql, new MapListHandler());
        for (Map<String,Object> map : maplist){
            System.out.println(map);
        }
    }
    // 使用ScalarHandler可以自定义返回结果，默认Object
    @Test
    public void test03() throws SQLException {
        String sql = "select count(id) from account";
        Long total = qr.query(sql,new ScalarHandler<Long>());
        System.out.println(total);
    }
}

对象集合创建优化

BeanHandler：将单行数据转换为JavaBean对象。

BeanListHandler：将结果封装为JavaBean对象

public class JdbcDemo4 {
    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    @Test
    public void test01() throws SQLException {
        String sql = "select * from account";
        List<Account> accounts = qr.query(sql, new BeanListHandler<Account>(Account.class));
        for (Account a : accounts){
            System.out.println(a);
        }
    }
}

数据库转为对象数组的代码封装

注意点：对象封装需要set方法，变量名与列名名称需相同但不受大小写影响

public static <T> List<T> getListFromResultSet(ResultSet set, Class<T> clz) throws Exception {
    List<T> list = new ArrayList<T>();
    ResultSetMetaData md = set.getMetaData();
    int count = md.getColumnCount();
    String[] clomonNames = new String[count];
    for (int i = 1; i <= count; i++) {
        clomonNames[i - 1] = md.getColumnName(i);
    }
    T obj = null;
    Method[] methods = clz.getMethods();
    while (set.next()) {
        obj = clz.newInstance();
        for (Method method : methods) {
            for (String cn : clomonNames) {
                if (method.getName().toLowerCase().endsWith(cn.toLowerCase()) && method.getName().startsWith("set")) {
                    String ptn = method.getParameterTypes()[0].getSimpleName();
                    if (ptn.equals("Integer")) {
                        method.invoke(obj, set.getInt(cn));
                    } else if (ptn.equals("Double")) {
                        method.invoke(obj, set.getDouble(cn));
                    } else {
                        method.invoke(obj, set.getString(cn));
                    }
                }
            }
        }
        list.add(obj);
    }
    return list;
}

事务的调用

由Connection调用，调用事务和CRUD必须是同一个Connection

public void test01() throws SQLException {
    Connection conn = JDBCUtils.getConnection();
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    try {
        conn.setAutoCommit(false);
        String sql1 = "update account set balance = balance - 1000 where id = 1";
        String sql2 = "update account set balance = balance + 1000 where id = 2";

        int i = 1 / 0;
        qr.update(conn,sql1);   // 核心，一定要保证事务开启的conn是同一个
        qr.update(conn,sql2);
        conn.commit();
    } catch (SQLException throwables) {
        conn.rollback();
        throwables.printStackTrace();
    }
}
版权声明:原创作品,允许转载，转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海汼部落－AIZero,http://hainiubl.com/topics/75185
本帖已被设为精华帖！
本帖由 青牛 于 4年前 加精
点赞
回复数量: 1
  • 青牛
    青牛 海汼部落创始人，80后程序员一枚，曾就职于金山，喜欢倒腾技术做产品
    ⋅ 2020-07-07 18:20:55

    挺好，奖励5元红包一枚，补充一点连接池使用场景：多线程并发访问数据库的时候使用。

暂无评论~~
  • 请注意单词拼写，以及中英文排版，参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情，可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif，教程
  • 发布框支持本地存储功能，会在内容变更时保存，「提交」按钮点击时清空
Ctrl+Enter
新 建 话 题

作者：AIZero

AIZero 的其他话题

分类下其他主题

随机推荐话题

QQ群1：551936262 海汼部落QQ群1

关注海汼部落技术社区