[Apache commons系列]DBUtils简介-3.示例代码-阿里云开发者社区

开发者社区> 数据库> 正文

[Apache commons系列]DBUtils简介-3.示例代码

简介:

  inkfish原创,请勿商业性质转载,转载请注明来源(http://blog.csdn.net/inkfish )。

  DbUtils是一个小型的类库,这里通过具体实例来说明如何使用DbUtils。示例分为3个类:DbUtilsExample演示了如何使用DbUtils 类;QueryRunnerExample 演示了如何使用QueryRunnerResultSetHandlerUser 类为一个JavaBean,对应于数据库中的表格。示例采用MySQL为数据库,使用JDBC4.0驱动(最大的区别就是不需要写Class.forName().newInstance() )。(来源:http://blog.csdn.net/inkfish)

依赖包: (来源:http://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驱动

建表语句: (来源:http://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); } } } (来源:http://blog.csdn.net/inkfish)

QueryRunnerExample.java (来源:http://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); } } } (来源:http://blog.csdn.net/inkfish)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章