commons-dbutils工具 · Java知识点

commons-dbutils 是一个封装了数据库增、删、改、查的工具类，代码非常简洁。只需要调用类`QueryRunner`中方法即可。 <br/> 使用 commons-dbutils 工具步骤如下： **1. pom.xml** ```xml <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>mchange-commons-java</artifactId> <version>0.2.15</version> </dependency> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.6</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.8.2</version> <scope>test</scope> </dependency> ``` **2. 配置文件，选择xml或者properties都可以** * `resources/c3p0.properties` ```properties c3p0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/jdbc_db?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=UTC c3p0.driverClass=com.mysql.jdbc.Driver c3p0.user=root c3p0.password=root c3p0.acquireIncrement=3 c3p0.idleConnectionTestPeriod=60 c3p0.initialPoolSize=10 c3p0.maxIdleTime=60 c3p0.maxPoolSize=20 c3p0.maxStatements=100 c3p0.minPoolSize=5 ``` * `resources/c3p0-config.xml` ```xml <?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <named-config name="jdbc_db"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl"> jdbc:mysql://localhost:3306/jdbc_db?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC</property> <property name="user">root</property> <property name="password">root</property> <property name="maxPoolSize">20</property> <property name="minPoolSize">3</property> <property name="maxStatements">30</property> <property name="maxIdleTime">150</property> </named-config> </c3p0-config> ``` **3. 调用类`QueryRunner`中方法完成数据库操作** ```java public class CommonsDbutils { //采用.properties格式，则不需要传入参数 //private DataSource dataSource = new ComboPooledDataSource(); //如果采用xml格式，则需要传入在xml文件创建的逻辑名 private DataSource dataSource = new ComboPooledDataSource("jdbc_db"); private QueryRunner queryRunner = new QueryRunner(dataSource); /** * 增、删、改可以统一调用 update 方法。 */ @Test public void update() throws SQLException { int r1 = queryRunner.update("insert into student values(?,?,?,?)", "1011", "张无忌", 25, "男"); int r2 = queryRunner.update("update student set `name`=? where id=?", "张三丰", "1011"); int r3 = queryRunner.update("delete from student where id=?", "1011"); } /** * 查询返回的是单个实体。 */ @Test public void queryBean() throws SQLException { Student student = queryRunner.query("select * from student where id=?", new BeanHandler<>(Student.class), 1009); System.out.println(student); //Student(id=1009, name=田七9, age=32, sex=男9) } /** * 查询返回的是多个实体。 */ @Test public void queryListBean() throws SQLException { List<Student> studentList = queryRunner.query("select * from student limit ?,?", new BeanListHandler<>(Student.class), 1, 5); System.out.println(studentList); //[Student(id=10010, name=田七10, age=33, sex=男10), // Student(id=1003, name=王五, age=27, sex=男), // Student(id=1005, name=田七5, age=28, sex=男5), // Student(id=1006, name=田七6, age=29, sex=男6), // Student(id=1007, name=田七7, age=30, sex=男7)] } /** * 统计。 */ @Test public void queryCount() throws SQLException { long count = queryRunner.query("select count(*) from student", new ScalarHandler<Long>()); System.out.println(count); //8 } } ```