556
[Apache commons係列]DBUtils簡介-3.示例代碼
inkfish原創，請勿商業性質轉載，轉載請注明來源（https://blog.csdn.net/inkfish ）。
DbUtils是一個小型的類庫，這裏通過具體實例來說明如何使用DbUtils。示例分為3個類：DbUtilsExample演示了如何使用DbUtils
類；QueryRunnerExample
演示了如何使用QueryRunner
、ResultSetHandler
；User
類為一個JavaBean，對應於數據庫中的表格。示例采用MySQL為數據庫，使用JDBC4.0驅動（最大的區別就是不需要寫Class.forName().newInstance()
）。（來源：https://blog.csdn.net/inkfish）
依賴包： （來源：https://blog.csdn.net/inkfish）
- commons-dbcp-1.2.2.jar：提供數據庫連接池
- commons-dbutils-1.3.jar：DbUtils包
- commons-lang-2.4.jar：提供常用的工具方法
- commons-pool-1.5.3.jar：提供DBCP對象池化機製
- mysql-connector-java-5.1.10-bin.jar：MySQL JDBC4.0驅動
建表語句： （來源：https://blog.csdn.net/inkfish）
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `USERNAME` varchar(30) NOT NULL, `PASSWORDHASH` varchar(256), PRIMARY KEY (`USERNAME`), KEY `USERNAME` (`USERNAME`) );
DbUtilsExample.java
：
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.dbutils.DbUtils; public class DbUtilsExample { public static void main(String[] args) { String url = "jdbc:mysql://127.0.0.1:3306/test"; String user = "test"; String password = "test"; Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = DriverManager.getConnection(url, user, password); stmt = conn.createStatement(); stmt .executeUpdate("INSERT INTO user (USERNAME, PASSWORDHASH) values ('testUser', '123456')"); DbUtils.closeQuietly(stmt); DbUtils.commitAndCloseQuietly(conn); } catch (SQLException ex) { DbUtils.printStackTrace(ex); DbUtils.rollbackAndCloseQuietly(conn); } PreparedStatement psmt = null; try { conn = DriverManager.getConnection(url, user, password); psmt = conn.prepareStatement("SELECT USERNAME, PASSWORDHASH FROM user"); rs = psmt.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1) + "/t" + rs.getString(2)); } } catch (SQLException ex) { DbUtils.printStackTrace(ex); } finally { DbUtils.closeQuietly(conn, psmt, rs); } try { conn = DriverManager.getConnection(url, user, password); stmt = conn.createStatement(); stmt.executeUpdate("DELETE FROM user WHERE USERNAME='testUser'"); DbUtils.closeQuietly(stmt); } catch (SQLException ex) { DbUtils.printStackTrace(ex); } finally { DbUtils.printWarnings(conn); DbUtils.closeQuietly(conn); } } } （來源：https://blog.csdn.net/inkfish）
QueryRunnerExample.java
：
（來源：https://blog.csdn.net/inkfish）
import static java.lang.System.out; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Arrays; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSource; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ColumnListHandler; import org.apache.commons.dbutils.handlers.KeyedHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.apache.commons.lang.StringUtils; public class QueryRunnerExample { public static void main(String[] args) throws SQLException { QueryRunnerExample example = new QueryRunnerExample(); example.batch(); example.fillStatement(); example.query(); example.update(); example.closeDataSource(); } private DataSource dataSource = null; private QueryRunner runner = null; public QueryRunnerExample() { initDataSource(); runner = new QueryRunner(dataSource); } private void batch() { String sql = "INSERT INTO user (USERNAME, PASSWORDHASH) values (?, ?)"; try { out.println("/n" + StringUtils.center("Test QueryRunner batch", 80, '*')); int[] result = runner.batch(sql, new Object[][] { { "user1", "pwd1" }, { "user2", "pwd2" }, { "user3", "pwd3" }, { "user4", "pwd4" } }); out.printf("運行結果：%s/n", Arrays.toString(result)); } catch (SQLException ex) { DbUtils.printStackTrace(ex); } } private void closeDataSource() throws SQLException { ((BasicDataSource) dataSource).close(); } private void fillStatement() throws SQLException { String sql = "INSERT INTO user (USERNAME, PASSWORDHASH) VALUES (?, ?)"; Connection conn = null; try { out.println("/n" + StringUtils.center("Test QueryRunner fillStatement", 80, '*')); conn = dataSource.getConnection(); PreparedStatement psmt = conn.prepareStatement(sql); runner.fillStatementWithBean(psmt, new User("testUser5", "pwd5"), "userName", "passwordHash"); out.println(psmt.executeUpdate()); } catch (SQLException ex) { DbUtils.printStackTrace(ex); } finally { DbUtils.commitAndCloseQuietly(conn); } } private DataSource initDataSource() { if (dataSource == null) { BasicDataSource basicDs = new BasicDataSource(); basicDs.setUrl("jdbc:mysql://127.0.0.1:3306/test"); basicDs.setUsername("test"); basicDs.setPassword("test"); this.dataSource = basicDs; } return dataSource; } private void query() throws SQLException { out.println("/n" + StringUtils.center("Test QueryRunner query", 80, '*')); String sql = "SELECT * FROM user WHERE USERNAME LIKE ?"; out.println("1.Test QueryRunner query, ArrayHandler"); //把ResultSet第一行包裝成Object[] Object[] r1 = runner.query(sql, new ArrayHandler(), "user%"); out.println(" " + Arrays.deepToString(r1)); out.println("2.Test QueryRunner query, ArrayListHandler"); //把ResultSet包裝成List<Object[]> List<Object[]> r2 = runner.query(sql, new ArrayListHandler(), "user%"); out.println(" " + Arrays.deepToString(r2.toArray())); out.println("3.Test QueryRunner query, BeanHandler"); //把ResultSet第一行包裝成一個JavaBean User r3 = runner.query(sql, new BeanHandler<User>(User.class), "user%"); out.println(" " + r3.toString()); out.println("4.Test QueryRunner query, BeanListHandler"); //把ResultSet第一行包裝成一個List<JavaBean>； List<User> r4 = runner.query(sql, new BeanListHandler<User>(User.class), "user%"); out.println(" " + Arrays.deepToString(r4.toArray())); out.println("5.Test query, ColumnListHandler"); //抽取ResultSet指定的列，以List<Object>對象的形式返回，默認第一列 List<Object> r5 = runner.query(sql, new ColumnListHandler(2), "user%"); out.println(" " + Arrays.deepToString(r5.toArray())); out.println("6.Test QueryRunner query, KeyedHandler"); //包裝ResultSet，以Map<Object,Map<String,Object>>對象形式返回，第一個Object是指定的列值，第二個Map中String是列名且對大小寫不敏感 Map<Object, Map<String, Object>> r6 = runner.query(sql, new KeyedHandler(2), "user%"); out.println(" " + r6.toString()); out.println("7.Test QueryRunner query, MapHandler"); //把ResultSet第一行包裝成Map<String, Object>，key對大小寫不敏感 Map<String, Object> r7 = runner.query(sql, new MapHandler(), "user%"); out.println(" " + r7.toString()); out.println("8.Test QueryRunner query, MapListHandler"); //把ResultSet包裝成List<Map<String,Object>>>，Map的key對大小寫不敏感 List<Map<String, Object>> r8 = runner.query(sql, new MapListHandler(), "user%"); out.println(" " + r8.toString()); out.println("9.Test QueryRunner query, ScalarHandler"); //抽取ResultSet第一行指定列，以Object對象形式返回 Object r9 = runner.query(sql, new ScalarHandler("passwordhash"), "user%"); out.println(" " + r9.toString()); } private void update() { String sql = "DELETE FROM user WHERE PASSWORDHASH LIKE ?"; try { out.println("/n" + StringUtils.center("Test QueryRunner update", 80, '*')); out.println(runner.update(sql, "pwd%")); } catch (SQLException ex) { DbUtils.printStackTrace(ex); } } } （來源：https://blog.csdn.net/inkfish）
最後更新：2017-04-02 04:01:44