1.数据库的连接
/**
* DriverManager 是驱动的管理类.
* 1). 可以通过重载的 getConnection() 方法获取数据库连接. 较为方便
* 2). 可以同时管理多个驱动程序: 若注册了多个数据库连接, 则调用 getConnection()
* 方法时传入的参数不同, 即返回不同的数据库连接。
* @throws Exception
*/
@Test
public void testGetConnection2() throws Exception{
System.out.println(getConnection2());
}
public Connection getConnection2() throws Exception{
//1. 准备连接数据库的 4 个字符串.
//1). 创建 Properties 对象
Properties properties = new Properties();
//2). 获取 jdbc.properties 对应的输入流
InputStream in =
this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
//3). 加载 2) 对应的输入流
properties.load(in);
//4). 具体决定 user, password 等4 个字符串.
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String jdbcUrl = properties.getProperty("jdbcUrl");
String driver = properties.getProperty("driver");
//2. 加载数据库驱动程序(对应的 Driver 实现类中有注册驱动的静态代码块.)
Class.forName(driver);
//3. 通过 DriverManager 的 getConnection() 方法获取数据库连接.
return DriverManager.getConnection(jdbcUrl, user, password);
}
2.statement 和prepareStatement
@Test
public void testPreparedStatement() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
String sql = "INSERT INTO customers (name, email, birth) "
+ "VALUES(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "ATGUIGU");
preparedStatement.setString(2, "simpleit@163.com");
preparedStatement.setDate(3,
new Date(new java.util.Date().getTime()));
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
/**
* SQL 注入.
*/
@Test
public void testSQLInjection() {
String username = "a' OR PASSWORD = ";
String password = " OR '1'='1";
String sql = "SELECT * FROM users WHERE username = '" + username
+ "' AND " + "password = '" + password + "'";
System.out.println(sql);
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("登录成功!");
} else {
System.out.println("用户名和密码不匹配或用户名不存在. ");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, statement, connection);
}
}
3.JDBCTools
package com.atguigu.jdbc;
import java.io.IOException;
import java.io.InputStream;
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 java.util.Properties;
public class JDBCTools {
/**
* 执行 SQL 语句, 使用 PreparedStatement
* @param sql
* @param args: 填写 SQL 占位符的可变参数
*/
public static void update(String sql, Object ... args){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
/**
* 执行 SQL 的方法
*
* @param sql: insert, update 或 delete。 而不包含 select
*/
public static void update(String sql) {
Connection connection = null;
Statement statement = null;
try {
// 1. 获取数据库连接
connection = getConnection();
// 2. 调用 Connection 对象的 createStatement() 方法获取 Statement 对象
statement = connection.createStatement();
// 4. 发送 SQL 语句: 调用 Statement 对象的 executeUpdate(sql) 方法
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5. 关闭数据库资源: 由里向外关闭.
releaseDB(null, statement, connection);
}
}
/**
* 释放数据库资源的方法
*
* @param resultSet
* @param statement
* @param connection
*/
public static void releaseDB(ResultSet resultSet, Statement statement,
Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取数据库连接的方法
*/
public static Connection getConnection() throws IOException,
ClassNotFoundException, SQLException {
// 0. 读取 jdbc.properties
/**
* 1). 属性文件对应 Java 中的 Properties 类 2). 可以使用类加载器加载 bin 目录(类路径下)的文件
*/
Properties properties = new Properties();
InputStream inStream = ReviewTest.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
properties.load(inStream);
// 1. 准备获取连接的 4 个字符串: user, password, jdbcUrl, driverClass
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String jdbcUrl = properties.getProperty("jdbcUrl");
String driverClass = properties.getProperty("driverClass");
// 2. 加载驱动: Class.forName(driverClass)
Class.forName(driverClass);
// 3. 调用
// DriverManager.getConnection(jdbcUrl, user, password)
// 获取数据库连接
Connection connection = DriverManager.getConnection(jdbcUrl, user,
password);
return connection;
}
}
4.DAO
package com.atguigu.jdbc;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
public class DAO {
// INSERT, UPDATE, DELETE 操作都可以包含在其中
public void update(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
// 查询一条记录, 返回对应的对象
public <T> T get(Class<T> clazz, String sql, Object... args) {
List<T> result = getForList(clazz, sql, args);
if(result.size() > 0){
return result.get(0);
}
return null;
}
/**
* 传入 SQL 语句和 Class 对象, 返回 SQL 语句查询到的记录对应的 Class 类的对象的集合
* @param clazz: 对象的类型
* @param sql: SQL 语句
* @param args: 填充 SQL 语句的占位符的可变参数.
* @return
*/
public <T> List<T> getForList(Class<T> clazz,
String sql, Object... args) {
List<T> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//1. 得到结果集
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
//2. 处理结果集, 得到 Map 的 List, 其中一个 Map 对象
//就是一条记录. Map 的 key 为 reusltSet 中列的别名, Map 的 value
//为列的值.
List<Map<String, Object>> values =
handleResultSetToMapList(resultSet);
//3. 把 Map 的 List 转为 clazz 对应的 List
//其中 Map 的 key 即为 clazz 对应的对象的 propertyName,
//而 Map 的 value 即为 clazz 对应的对象的 propertyValue
list = transfterMapListToBeanList(clazz, values);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
return list;
}
public <T> List<T> transfterMapListToBeanList(Class<T> clazz,
List<Map<String, Object>> values) throws InstantiationException,
IllegalAccessException, InvocationTargetException {
List<T> result = new ArrayList<>();
T bean = null;
if (values.size() > 0) {
for (Map<String, Object> m : values) {
bean = clazz.newInstance();
for (Map.Entry<String, Object> entry : m.entrySet()) {
String propertyName = entry.getKey();
Object value = entry.getValue();
BeanUtils.setProperty(bean, propertyName, value);
}
// 13. 把 Object 对象放入到 list 中.
result.add(bean);
}
}
return result;
}
/**
* 处理结果集, 得到 Map 的一个 List, 其中一个 Map 对象对应一条记录
*
* @param resultSet
* @return
* @throws SQLException
*/
public List<Map<String, Object>> handleResultSetToMapList(
ResultSet resultSet) throws SQLException {
// 5. 准备一个 List<Map<String, Object>>:
// 键: 存放列的别名, 值: 存放列的值. 其中一个 Map 对象对应着一条记录
List<Map<String, Object>> values = new ArrayList<>();
List<String> columnLabels = getColumnLabels(resultSet);
Map<String, Object> map = null;
// 7. 处理 ResultSet, 使用 while 循环
while (resultSet.next()) {
map = new HashMap<>();
for (String columnLabel : columnLabels) {
Object value = resultSet.getObject(columnLabel);
map.put(columnLabel, value);
}
// 11. 把一条记录的一个 Map 对象放入 5 准备的 List 中
values.add(map);
}
return values;
}
/**
* 获取结果集的 ColumnLabel 对应的 List
*
* @param rs
* @return
* @throws SQLException
*/
private List<String> getColumnLabels(ResultSet rs) throws SQLException {
List<String> labels = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
labels.add(rsmd.getColumnLabel(i + 1));
}
return labels;
}
// 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.)
public <E> E getForValue(String sql, Object... args) {
//1. 得到结果集: 该结果集应该只有一行, 且只有一列
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//1. 得到结果集
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch(Exception ex){
ex.printStackTrace();
} finally{
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
//2. 取得结果
return null;
}
}
5.事务和事务的隔离级别
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class TransactionTest {
/**
* 测试事务的隔离级别 在 JDBC 程序中可以通过 Connection 的 setTransactionIsolation 来设置事务的隔离级别.
*/
@Test
public void testTransactionIsolationUpdate() {
Connection connection = null;
try {
connection = JDBCTools.getConnection();
connection.setAutoCommit(false);
String sql = "UPDATE users SET balance = "
+ "balance - 500 WHERE id = 1";
update(connection, sql);
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}
@Test
public void testTransactionIsolationRead() {
String sql = "SELECT balance FROM users WHERE id = 1";
Integer balance = getForValue(sql);
System.out.println(balance);
}
// 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.)
public <E> E getForValue(String sql, Object... args) {
// 1. 得到结果集: 该结果集应该只有一行, 且只有一列
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 1. 得到结果集
connection = JDBCTools.getConnection();
System.out.println(connection.getTransactionIsolation());
// connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
return (E) resultSet.getObject(1);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
// 2. 取得结果
return null;
}
/**
* Tom 给 Jerry 汇款 500 元.
*
* 关于事务: 1. 如果多个操作, 每个操作使用的是自己的单独的连接, 则无法保证事务. 2. 具体步骤: 1). 事务操作开始前, 开始事务:
* 取消 Connection 的默认提交行为. connection.setAutoCommit(false); 2). 如果事务的操作都成功,
* 则提交事务: connection.commit(); 3). 回滚事务: 若出现异常, 则在 catch 块中回滚事务:
*/
@Test
public void testTransaction() {
Connection connection = null;
try {
connection = JDBCTools.getConnection();
System.out.println(connection.getAutoCommit());
// 开始事务: 取消默认提交.
connection.setAutoCommit(false);
String sql = "UPDATE users SET balance = "
+ "balance - 500 WHERE id = 1";
update(connection, sql);
int i = 10 / 0;
System.out.println(i);
sql = "UPDATE users SET balance = " + "balance + 500 WHERE id = 2";
update(connection, sql);
// 提交事务
connection.commit();
} catch (Exception e) {
e.printStackTrace();
// 回滚事务
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
JDBCTools.releaseDB(null, null, connection);
}
/*
* try {
*
* //开始事务: 取消默认提交. connection.setAutoCommit(false);
*
* //...
*
* //提交事务 connection.commit(); } catch (Exception e) { //...
*
* //回滚事务 try { connection.rollback(); } catch (SQLException e1) {
* e1.printStackTrace(); } } finally{ JDBCTools.releaseDB(null, null,
* connection); }
*/
// DAO dao = new DAO();
//
// String sql = "UPDATE users SET balance = " +
// "balance - 500 WHERE id = 1";
// dao.update(sql);
//
// int i = 10 / 0;
// System.out.println(i);
//
// sql = "UPDATE users SET balance = " +
// "balance + 500 WHERE id = 2";
// dao.update(sql);
}
public void update(Connection connection, String sql, Object... args) {
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(null, preparedStatement, null);
}
}
}
6.批量处理sql语句
@Test
public void testBatch(){
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = null;
try {
connection = JDBCTools.getConnection();
JDBCTools.beginTx(connection);
sql = "INSERT INTO customers VALUES(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
long begin = System.currentTimeMillis();
for(int i = 0; i < 100000; i++){
preparedStatement.setInt(1, i + 1);
preparedStatement.setString(2, "name_" + i);
preparedStatement.setDate(3, date);
//"积攒" SQL
preparedStatement.addBatch();
//当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL
if((i + 1) % 300 == 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
//若总条数不是批量数值的整数倍, 则还需要再额外的执行一次.
if(100000 % 300 != 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin)); //569
JDBCTools.commit(connection);
} catch (Exception e) {
e.printStackTrace();
JDBCTools.rollback(connection);
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
7.DBCP和C3P0数据库连接池的使用(非配置文件和配置文件)
package com.atguigu.jdbc;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCTest {
@Test
public void testJdbcTools() throws Exception{
Connection connection = JDBCTools.getConnection();
System.out.println(connection);
}
/**
* 1. 创建 c3p0-config.xml 文件,
* 参考帮助文档中 Appendix B: Configuation Files 的内容
* 2. 创建 ComboPooledDataSource 实例;
* DataSource dataSource =
* new ComboPooledDataSource("helloc3p0");
* 3. 从 DataSource 实例中获取数据库连接.
*/
@Test
public void testC3poWithConfigFile() throws Exception{
DataSource dataSource =
new ComboPooledDataSource("helloc3p0");
System.out.println(dataSource.getConnection());
ComboPooledDataSource comboPooledDataSource =
(ComboPooledDataSource) dataSource;
System.out.println(comboPooledDataSource.getMaxStatements());
}
@Test
public void testC3P0() throws Exception{
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql:///atguigu" );
cpds.setUser("root");
cpds.setPassword("1230");
System.out.println(cpds.getConnection());
}
/**
* 1. 加载 dbcp 的 properties 配置文件: 配置文件中的键需要来自 BasicDataSource
* 的属性.
* 2. 调用 BasicDataSourceFactory 的 createDataSource 方法创建 DataSource
* 实例
* 3. 从 DataSource 实例中获取数据库连接.
*/
@Test
public void testDBCPWithDataSourceFactory() throws Exception{
Properties properties = new Properties();
InputStream inStream = JDBCTest.class.getClassLoader()
.getResourceAsStream("dbcp.properties");
properties.load(inStream);
DataSource dataSource =
BasicDataSourceFactory.createDataSource(properties);
System.out.println(dataSource.getConnection());
// BasicDataSource basicDataSource =
// (BasicDataSource) dataSource;
//
// System.out.println(basicDataSource.getMaxWait());
}
/**
* 使用 DBCP 数据库连接池
* 1. 加入 jar 包(2 个jar 包). 依赖于 Commons Pool
* 2. 创建数据库连接池
* 3. 为数据源实例指定必须的属性
* 4. 从数据源中获取数据库连接
* @throws SQLException
*/
@Test
public void testDBCP() throws SQLException{
final BasicDataSource dataSource = new BasicDataSource();
//2. 为数据源实例指定必须的属性
dataSource.setUsername("root");
dataSource.setPassword("1230");
dataSource.setUrl("jdbc:mysql:///atguigu");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
//3. 指定数据源的一些可选的属性.
//1). 指定数据库连接池中初始化连接数的个数
dataSource.setInitialSize(5);
//2). 指定最大的连接数: 同一时刻可以同时向数据库申请的连接数
dataSource.setMaxActive(5);
//3). 指定小连接数: 在数据库连接池中保存的最少的空闲连接的数量
dataSource.setMinIdle(2);
//4).等待数据库连接池分配连接的最长时间. 单位为毫秒. 超出该时间将抛出异常.
dataSource.setMaxWait(1000 * 5);
//4. 从数据源中获取数据库连接
Connection connection = dataSource.getConnection();
System.out.println(connection.getClass());
connection = dataSource.getConnection();
System.out.println(connection.getClass());
connection = dataSource.getConnection();
System.out.println(connection.getClass());
connection = dataSource.getConnection();
System.out.println(connection.getClass());
Connection connection2 = dataSource.getConnection();
System.out.println(">" + connection2.getClass());
new Thread(){
public void run() {
Connection conn;
try {
conn = dataSource.getConnection();
System.out.println(conn.getClass());
} catch (SQLException e) {
e.printStackTrace();
}
};
}.start();
try {
Thread.sleep(5500);
} catch (InterruptedException e) {
e.printStackTrace();
}
connection2.close();
}
@Test
public void testBatch(){
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = null;
try {
connection = JDBCTools.getConnection();
JDBCTools.beginTx(connection);
sql = "INSERT INTO customers VALUES(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
long begin = System.currentTimeMillis();
for(int i = 0; i < 100000; i++){
preparedStatement.setInt(1, i + 1);
preparedStatement.setString(2, "name_" + i);
preparedStatement.setDate(3, date);
//"积攒" SQL
preparedStatement.addBatch();
//当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL
if((i + 1) % 300 == 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
//若总条数不是批量数值的整数倍, 则还需要再额外的执行一次.
if(100000 % 300 != 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin)); //569
JDBCTools.commit(connection);
} catch (Exception e) {
e.printStackTrace();
JDBCTools.rollback(connection);
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
@Test
public void testBatchWithPreparedStatement(){
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = null;
try {
connection = JDBCTools.getConnection();
JDBCTools.beginTx(connection);
sql = "INSERT INTO customers VALUES(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
long begin = System.currentTimeMillis();
for(int i = 0; i < 100000; i++){
preparedStatement.setInt(1, i + 1);
preparedStatement.setString(2, "name_" + i);
preparedStatement.setDate(3, date);
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin)); //9819
JDBCTools.commit(connection);
} catch (Exception e) {
e.printStackTrace();
JDBCTools.rollback(connection);
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
/**
* 向 Oracle 的 customers 数据表中插入 10 万条记录
* 测试如何插入, 用时最短.
* 1. 使用 Statement.
*/
@Test
public void testBatchWithStatement(){
Connection connection = null;
Statement statement = null;
String sql = null;
try {
connection = JDBCTools.getConnection();
JDBCTools.beginTx(connection);
statement = connection.createStatement();
long begin = System.currentTimeMillis();
for(int i = 0; i < 100000; i++){
sql = "INSERT INTO customers VALUES(" + (i + 1)
+ ", 'name_" + i + "', '29-6月 -13')";
statement.addBatch(sql);
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin)); //39567
JDBCTools.commit(connection);
} catch (Exception e) {
e.printStackTrace();
JDBCTools.rollback(connection);
} finally{
JDBCTools.releaseDB(null, statement, connection);
}
}
}
8.DBUtils使用
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.Date;
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.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
public class DBUtilsTest {
/**
* ScalarHandler: 把结果集转为一个数值(可以是任意基本数据类型和字符串, Date 等)返回
*/
@Test
public void testScalarHandler(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT name, email " +
"FROM customers";
Object result = queryRunner.query(connection,
sql, new ScalarHandler());
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
/**
* MapListHandler: 将结果集转为一个 Map 的 List
* Map 对应查询的一条记录: 键: SQL 查询的列名(不是列的别名), 值: 列的值.
* 而 MapListHandler: 返回的多条记录对应的 Map 的集合.
*/
@Test
public void testMapListHandler(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name, email, birth " +
"FROM customers";
List<Map<String, Object>> result = queryRunner.query(connection,
sql, new MapListHandler());
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
/**
* MapHandler: 返回 SQL 对应的第一条记录对应的 Map 对象.
* 键: SQL 查询的列名(不是列的别名), 值: 列的值.
*/
@Test
public void testMapHandler(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name, email, birth " +
"FROM customers";
Map<String, Object> result = queryRunner.query(connection,
sql, new MapHandler());
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
/**
* BeanListHandler: 把结果集转为一个 List, 该 List 不为 null, 但可能为
* 空集合(size() 方法返回 0)
* 若 SQL 语句的确能够查询到记录, List 中存放创建 BeanListHandler 传入的 Class
* 对象对应的对象.
*/
@Test
public void testBeanListHandler(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name, email, birth " +
"FROM customers";
List<Customer> customers = queryRunner.query(connection,
sql, new BeanListHandler(Customer.class));
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
/**
* BeanHandler: 把结果集的第一条记录转为创建 BeanHandler 对象时传入的 Class
* 参数对应的对象.
*/
@Test
public void testBeanHanlder(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name customerName, email, birth " +
"FROM customers WHERE id >= ?";
Customer customer = queryRunner.query(connection,
sql, new BeanHandler(Customer.class), 5);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
QueryRunner queryRunner = new QueryRunner();
class MyResultSetHandler implements ResultSetHandler{
@Override
public Object handle(ResultSet resultSet)
throws SQLException {
// System.out.println("handle....");
// return "atguigu";
List<Customer> customers = new ArrayList<>();
while(resultSet.next()){
Integer id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer =
new Customer(id, name, email, birth);
customers.add(customer);
}
return customers;
}
}
/**
* QueryRunner 的 query 方法的返回值取决于其 ResultSetHandler 参数的
* handle 方法的返回值
*
*/
@Test
public void testQuery(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name, email, birth " +
"FROM customers";
Object obj = queryRunner.query(connection, sql,
new MyResultSetHandler());
System.out.println(obj);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
@Test
public void testUpdate(){
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "UPDATE customers SET name = ? " +
"WHERE id = ?";
queryRunner.update(connection, sql, "MIKE", 11);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, null, connection);
}
}
}
9.DAO接口
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* 访问数据的 DAO 接口.
* 里边定义好访问数据表的各种方法
* @param T: DAO 处理的实体类的类型.
*/
public interface DAO<T> {
/**
* 批量处理的方法
* @param connection
* @param sql
* @param args: 填充占位符的 Object [] 类型的可变参数.
* @throws SQLException
*/
void batch(Connection connection,
String sql, Object [] ... args) throws SQLException;
/**
* 返回具体的一个值, 例如总人数, 平均工资, 某一个人的 email 等.
* @param connection
* @param sql
* @param args
* @return
* @throws SQLException
*/
<E> E getForValue(Connection connection,
String sql, Object ... args) throws SQLException;
/**
* 返回 T 的一个集合
* @param connection
* @param sql
* @param args
* @return
* @throws SQLException
*/
List<T> getForList(Connection connection,
String sql, Object ... args) throws SQLException;
/**
* 返回一个 T 的对象
* @param connection
* @param sql
* @param args
* @return
* @throws SQLException
*/
T get(Connection connection, String sql,
Object ... args) throws SQLException;
/**
* INSRET, UPDATE, DELETE
* @param connection: 数据库连接
* @param sql: SQL 语句
* @param args: 填充占位符的可变参数.
* @throws SQLException
*/
void update(Connection connection, String sql,
Object ... args) throws SQLException;
}
10.DAO接口的实现类。(通过DBUtils实现)
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
/**
* 使用 QueryRunner 提供其具体的实现
* @param <T>: 子类需传入的泛型类型.
*/
public class JdbcDaoImpl<T> implements DAO<T> {
private QueryRunner queryRunner = null;
private Class<T> type;
public JdbcDaoImpl() {
queryRunner = new QueryRunner();
type = ReflectionUtils.getSuperGenericType(getClass());
}
@Override
public void batch(Connection connection, String sql, Object[]... args) throws SQLException {
queryRunner.batch(connection, sql, args);
}
@Override
public <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException {
return (E) queryRunner.query(connection, sql, new ScalarHandler(), args);
}
@Override
public List<T> getForList(Connection connection, String sql, Object... args)
throws SQLException {
return queryRunner.query(connection, sql,
new BeanListHandler<>(type), args);
}
@Override
public T get(Connection connection, String sql, Object... args) throws SQLException {
return queryRunner.query(connection, sql,
new BeanHandler<>(type), args);
}
@Override
public void update(Connection connection, String sql, Object... args) throws SQLException {
queryRunner.update(connection, sql, args);
}
}
11.DAOImpl的继承类,可以再自己定义操作数据库的方法
package com.atguigu.jdbc;
public class CustomerDao
extends JdbcDaoImpl<Customer>{
}