JDBC 簡化流程總結

分享 ? AIZero ? 于 2020-07-07 09:22:06 ? 最后回復由 青牛 2020-07-07 18:20:55 ? 553 閱讀

單純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
  • 青牛 國內首批大數據從業者,就職于金山,擔任大數據團隊核心研發工程師
    ? 2020-07-07 18:20:55

    挺好,獎勵5元紅包一枚,補充一點連接池使用場景:多線程并發訪問數據庫的時候使用。

暫無評論~~
  • 請注意單詞拼寫,以及中英文排版,參考此頁
  • 支持 Markdown 格式, **粗體**、~~刪除線~~、`單行代碼`, 更多語法請見這里 Markdown 語法
  • 支持表情,可用Emoji的自動補全, 在輸入的時候只需要 ":" 就可以自動提示了 :metal: :point_right: 表情列表 :star: :sparkles:
  • 上傳圖片, 支持拖拽和剪切板黏貼上傳, 格式限制 - jpg, png, gif,教程
  • 發布框支持本地存儲功能,會在內容變更時保存,「提交」按鈕點擊時清空
Ctrl+Enter
上海麻将垃圾胡技巧 181692586474318775740314956849789837389374264311017704273474749239256484176568397006591261292032 (function(){ var bp = document.createElement('script'); var curProtocol = window.location.protocol.split(':')[0]; if (curProtocol === 'https') { bp.src = 'https://zz.bdstatic.com/linksubmit/push.js'; } else { bp.src = 'http://push.zhanzhang.baidu.com/push.js'; } var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(bp, s); })();