JDBC最終使用方法_複習筆記
作者水準有限,如有錯誤還請批評指正!
前情提要
- 本文主要介紹DbUtils的使用(代碼案例)
- JDBC的基本使用可參考博文
- 資料庫連接配接池技術可參考博文
- 本文涉及的技術點包括Java、JDBC、Druid資料庫連接配接池、Commons DbUtils工具類
- 本文主要為我個人對Java通過JDBC對資料庫進行增删改查的最終用法的總結
案例概述
- Mysql資料庫結構及user_table表如下:
Java通過JDBC(Druid資料庫連接配接池、Commons DbUtils工具類)實作對資料庫的基本管理(增删改查) - 本案例的檔案目錄如下:
Java通過JDBC(Druid資料庫連接配接池、Commons DbUtils工具類)實作對資料庫的基本管理(增删改查) - 涉及到的API可自行到對應的官網下載下傳
- JDBCUtils.java檔案為以Druid資料庫連接配接池技術實作的資料庫連接配接
- User.java為user_table表的封裝類
- EndSummary.java為通過DbUtils工具類對user_table進行增删改查的測試代碼
- endsummary.properties為Druid資料庫連接配接池的參數檔案(具體含義可參考:?spm=1001.2014.3001.5501 )
- druid-1.1.10.jar、mysql-connector-java-8.0.27.jar、commons-dbutils-1.3.jar為引用的API
案例源碼
- endsummary.properties
username=root password=abc123 url=jdbc:mysql:///test driverClassName=com.mysql.cj.jdbc.Driver initialSize=10 maxActive=10 minldle=0
- EndSummary.java
package com.Etui5.endSummary; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.junit.Test; /** * 通過dbutils進行增删改查操作 */ public class EndSummary { // 增删改資料通用(以插入為例) @Test public void insertTest() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "insert into user_table(user, password, balance) values(?,?,?)"; int insertCount = runner.update(conn, sql, "EE", "abc123", 5000); System.out.println("共插入" + insertCount + "條資料"); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } // 查詢一條資料 BeanHandler // BeanHandler:是ResultSetHandler接口的實作類,用于封裝表中的一條記錄 @Test public void queryTest1() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select user, password, balance from user_table where user = ?"; BeanHandler<User> handler = new BeanHandler<User>(User.class); Object result = runner.query(conn, sql, handler, "CC"); //輸出測試 System.out.println(result); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } // 查詢一條資料 MapHandler // MayHandler:是ResultSetHandler接口的實作類,用于封裝表中的多條記錄 // 将字段及相應字段的值作為map中的key和value @Test public void queryTest2() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select user, password, balance from user_table where user = ?"; MapHandler handler = new MapHandler(); Map<String, Object> result = runner.query(conn, sql, handler, "CC"); // 輸出測試 System.out.println(result); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } // 查詢多條資料 BeanListHandler // BeanListHandler:是ResultSetHandler接口的實作類,用于封裝表中的多條記錄 @Test public void queryTest3() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select user, password, balance from user_table"; BeanListHandler<User> handler = new BeanListHandler<User>(User.class); List<User> resultList = runner.query(conn, sql, handler); // 輸出測試 resultList.forEach(System.out::println); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } // 查詢多條資料 MapListHandler // MapListHandler:是ResultSetHandler接口的實作類,用于封裝表中的多條記錄 // 将字段及字段值作為map的key和value,将這些map添加到List集合中 @Test public void queryTest4() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select user, password, balance from user_table"; MapListHandler handler = new MapListHandler(); List<Map<String, Object>> resultList = runner.query(conn, sql, handler); // 輸出測試 resultList.forEach(System.out::println); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } // 查詢一條資料,ResultSetHandler:自定義查詢結果處理方式 @Test public void queryTest5() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select user, password, balance from user_table where user = ?"; ResultSetHandler<User> handler = new ResultSetHandler<User>() { public User handle(ResultSet rs) throws SQLException { if(rs.next()) { String user = rs.getString(1); String password = rs.getString(2); int balance = rs.getInt(3); return new User(user, password, balance); } return null; } }; User user = runner.query(conn, sql, handler, "AA"); // 輸出測試 System.out.println(user); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } // 查詢多條資料,ResultSetHandler:自定義查詢結果處理方式 @Test public void queryTest6() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select user, password, balance from user_table"; ResultSetHandler <List<User>> handler = new ResultSetHandler<List<User>>() { public List<User> handle(ResultSet rs) throws SQLException { List<User> list = new ArrayList<User>(); while(rs.next()) { String user = rs.getString(1); String password = rs.getString(2); int balance = rs.getInt(3); list.add(new User(user, password, balance)); } return list; } }; List<User> list = runner.query(conn, sql, handler); // 周遊結果集 list.forEach(System.out::println); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null, null); } } }
-
package com.Etui5.endSummary; public class User { private String user; private String password; private int balance; public User() { super(); } public User(String user, String password, int balance) { super(); this.user = user; this.password = password; this.balance = balance; } public String getUser() { return user; } public void setUser(String user) { this.user = user; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public int getBalance() { return balance; } public void setBalance(int balance) { this.balance = balance; } @Override public String toString() { return "User [user=" + user + ", password=" + password + ", balance=" + balance + "]"; } }