DBUtils
一、DBUtils说明:
DBUtils是Apache公司编写的数据库操作实用的工具,小巧,简单,实用;
导包:commons-dbutils-1.7.jar;druid-1.1.9.jar;mysql-connector-java-5.1.47.jar;
二、核心类:
1.DbUtils:连接数据库对象----jdbc辅助方法的集合类,线程安全
构造方法:DbUtils()
2.QueryRunner:SQL语句的操作对象,可以设置查询结果集的封装策略,线程安全。
(1)无参构造:QueryRunner():
创建一个与数据库无关的QueryRunner对象,后期再操作数据库的会后,需要手动给一个Connection对象,它可以手动控制事务。
1)事务操作:对Connection对象操作;
Connection.setAutoCommit(false); //设置手动管理事务
Connection.commit(); //提交事务
Connection.rollback(); //回滚事务
//事务的设置不能在dao层,必须在service层进行;
//操作的事务时候需要使用同一个Connection对象;
2)核心方法:
update(Connection conn, String sql, Object... params)
query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
(2)有参构造:QueryRunner(DataSource ds);//DataSource:数据库连接池对象。
创建一个与数据库关联的queryRunner对象,后期再操作数据库的时候,不需要Connection对象,自动管理事务。
1)核心方法:
update(String sql, Object... params)
query(String sql, ResultSetHandler<T> rsh, Object... params)
3.ResultSetHandler接口实现类:
(1)ArrayHandler:.query(sql, new ArrayHandler());
将查询结果的第一行数据,保存到Object数组中;
(2)ArrayListHandler:.query(sql, new ArrayListHandler());
将查询的结果,每一行先封装到Object数组中,然后将数据存入List集合;
(3)BeanHandler:.query(sql, new BeanHandler<Student>(Student.class), params)
把查询的结果封装成一个指定对象;
(4)BeanListHandler:.query(sql, new BeanListHandler<Student>(Student.class)
把查询结果封装成一个指定对象的List集合;
(5)BeanMapHandler:.query(sql, new BeanMapHandler<K, V>(type))
将查询结果的每一行数据,封装到User对象,再存入map集合中(key==列名,value==列值)
(6)ScalarHandler:.query(sql, new ScalarHandler())
查询单个值,返回为一个Long类型;//封装类似count、avg、max、min、sum......函数的执行结果;
(7)ColumnListHandler:.query(sql, new ColumnListHandler("列名"))
将查询结果的指定列的数据封装到List集合中;
(8)MapHandler:.query(sql, new MapHandler())
将查询结果的第一行数据封装到map结合(key==列名,value==列值);
(9)MapListHandler():.query(sql, new MapListHandler())
将查询结果的每一行封装到map集合(key==列名,value==列值),再将map集合存入List集合;
(10)KeyedHandler:.query(sql, new KeyedHandler<K>())
将查询的结果的每一行数据,封装到map1(key==列名,value==列值 ),然后将map1集合(有多个)存入map2集合(只有一个)
三、代码示例:
1)druid.properties
url:jdbc:mysql://localhost:3306/servlettest
driverClassName:com.mysql.jdbc.Driver
username:root
password:000000
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200
2)bean实体类:
public class User {
private Integer id;
private String name;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
3)封装工具类:
import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class DBPoolConnection { private static DBPoolConnection dbPoolConnection = new DBPoolConnection(); private static DataSource dataSource = null; private DBPoolConnection (){}; static { Properties properties = new Properties(); try { properties.load(new FileInputStream("src/druid.properties")); dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); System.out.println("配置失败"); } } public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } public static DataSource getDataSource() { return dataSource; } public void closeAll(Object... objects) { for (int i = 0; i < objects.length; i++) { if (objects[i] instanceof Statement) { Statement statement = (Statement) objects[i]; if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } if (objects[i] instanceof ResultSet) { ResultSet resultSet = (ResultSet) objects[i]; if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } } if (objects[i] instanceof Connection) { Connection connection = (Connection) objects[i]; if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } }
4)测试类:
public class Test { public static void main(String[] args) throws SQLException { Connection connection = DBPoolConnection.getConnection(); String sql = "select * from user where name = ?"; QueryRunner qr = new QueryRunner(); User user = qr.query(connection, sql, new BeanHandler<User>(User.class),"ls"); System.out.println(user); } }