JDBC和连接池
JDBC概述
-
JDBC为访问不同的数据库提供了统一的接口,为使用者屏蔽了细节问题
-
Java程序员使用JDBC,可以连接任何提供了JDBC驱动程序的数据库系统,从而完成对数据库的各种操作。
-
JDBC原理图:
-
JDBC是Java提供一套用于数据库操作的接口API,Java程序员只需要面向这个接口编程即可。不同的数据库厂商,需要针对这套接口,提供不同实现。
JDBC入门
基本步骤
- 注册驱动 – 加载Driver类
- 获取连接 – 得到Connection类
- 执行增删改查 – 发送SQL命令 给mysql执行
- 释放资源 – 关闭相关连接
连接方式
方式1
// 0. 加入jar包
// 1. 注册驱动 -- 加载Driver类
Driver driver = new Driver();
// 2. 获取连接 -- 得到Connection类
// 2.1 jdbc:mysql:// 规定好的协议,通过jdbc的方式连接mysql
// 2.2 localhost 主机,可以是ip地址
// 2.3 3306 表示mysql监听的端口
// 2.4 study_java 表示连接的数据库
String url = "jdbc:mysql://localhost:3306/Java_Study";
// 2.5 将用户名和密码封装到Properties对象中
// key 是 规定好的 user password不要修改
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "wxy981023");
Connection connect = driver.connect(url, properties);
// 3. 执行增删改查 -- 发送SQL命令 给mysql执行
String sql = "insert into actor values (2, 'wxy', '男', '1998-12-02', '15588089909')";
// 3.1 创建statement
Statement statement = connect.createStatement();
// 3. 利用statement执行SQL语句
int rows = statement.executeUpdate(sql);
System.out.println(rows);
// 4. 释放资源 -- 关闭相关连接
statement.close();
connect.close();
方式2
public void connect02() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
// 使用反射加载
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/Java_Study";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "wxy981023");
Connection connect = driver.connect(url, properties);
Statement statement = connect.createStatement();
String sql = "insert into actor values (2, 'wxy', '男', '1998-12-02', '15588089909')";
int rows = statement.executeUpdate(sql);
System.out.println(rows);
statement.close();
connect.close();
}
方式3
// 使用DriverManger替代Diver 进行统一管理
@Test
public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/Java_Study";
String user = "root";
String pwd = "wxy981023";
DriverManager.registerDriver(driver); // 注册驱动
Connection connection = DriverManager.getConnection(url, user, pwd);
System.out.println(connection);
connection.close();
}
方式4 △
// 这种使用较多
@Test
public void connect04() throws ClassNotFoundException, SQLException {
// 存在静态Driver类,底层帮助实现了
// 在加载Driver时,完成了注册,反射存在静态代码块
// 这句话也可以省略,当jar包版本大于5.1.6之后,可以实现
// 建议写出
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/Java_Study";
String user = "root";
String pwd = "wxy981023";
Connection connection = DriverManager.getConnection(url, user, pwd);
System.out.println(connection);
connection.close();
}
方式5 △
// 在方式4 的基础上, 增加配置文件,使连接mysql更加灵活
@Test
public void connect05() throws IOException, ClassNotFoundException, SQLException {
// 1. 通过Properties对象获取配置文件的信息
Properties properties = new Properties();
properties.load(new FileInputStream("/Users/xinyu/IdeaProjects/IdeaWorkspace" +
"/Study_Java/Study_Java_thirteen/src/mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 2. 加载Driver
Class.forName(driver);
// 3. 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
resultSet底层
- 表示数据库结果集的数据表,通常通过执行查询数据库的语句生成
- ResultSet对象保持一个光标指向其当前的数据行。最初,光标魏宇第一行之前
- next方法将光标移动到一下行,并且由于在ResultSet对象中没有更好行时返回false,因此可以在while循环中使用循环来遍历结果集
// 1. 通过Properties对象获取配置文件的信息
Properties properties = new Properties();
properties.load(new FileInputStream("/Users/xinyu/IdeaProjects/IdeaWorkspace" +
"/Study_Java/Study_Java_thirteen/src/mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 2. 加载Driver
Class.forName(driver);
// 3. 连接
Connection connection = DriverManager.getConnection(url, user, password);
// 4. 得到statement
Statement statement = connection.createStatement();
// 5. sql
String sql = "select id,name,sex,borndate from actor";
/* ResultSet
1. size
2. rows --> elementData 通过 ASCII 存储
3. rowData 所有数据
4. rows 所有行
5. elementData 所有列
*/
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
}
// 6. 关闭
resultSet.close();
statement.close();
connection.close();
JDBC API △
Statement
-
statement对象,用于执行静态sql语句并返回其生成的结果的对象
-
在建立连接后,需要第数据库进行访问,执行命名或是sql语句,可以通过
statement 【存在sql注入的问题】
prepareStatement 【预处理】
CallableStatement 【存储过程】
-
Statement对象执行SQL语句,存在SQL注入风险
-
SQL注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的SQL语句段或命令,恶意攻击数据库
-
要防范SQL注入,只要用PrepareStatement(从Statement扩展而来)取代Statement就可以
![image-20220316135810867](/Users/xinyu/Library/Application%20Support/typora-user-images/image-20220316135810867.png)
// 管理员输入登录用户名和密码 万能密码
// String admin_name = " 1' or";
// String admin_pwd = "or '1' = 1'";
Scanner scanner = new Scanner(System.in);
String admin_name = scanner.nextLine();
String admin_pwd = scanner.nextLine();
String mysqlPath = "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties";
Properties properties = new Properties();
properties.load(new FileInputStream(mysqlPath));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select name, pwd from admin where name = '"
+ admin_name + "' and pwd = '" + admin_pwd + "'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString("name");
String pwd = resultSet.getString("pwd");
System.out.println(name + " " + pwd);
}
resultSet.close();
statement.close();
connection.close();
PreparedStatement 预处理
- PrepareStatement执行的SQL语句中的参数用问号(?)来表示,调用PreparedStatement对象的setXxx()方法来设置这些参数。setXxx()方法有两个参数,第一个参数是要设置的SQL语句中的参数的索引(从1开始),第二个参数是设置SQL语句中的参数的值
- 调用executeQuery(),返回ResultSet对象
- 调用executeUpdate() 执行更新,包括增删改,返回rows-受影响的行数
- 好处:
- 不再使用+拼接sql语句,减少语法错误
- 有效的解决了sql注入问题
- 大大减少了编译次数,效率较高
Scanner scanner = new Scanner(System.in);
String admin_name = scanner.nextLine();
String admin_pwd = scanner.nextLine();
String mysqlPath = "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties";
Properties properties = new Properties();
properties.load(new FileInputStream(mysqlPath));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql1 = "select name, pwd from admin where name = ? and pwd = ?";
String sql2 = "insert into admin values (?, ?)";
String sql3 = "update admin set pwd = ? where name = ?";
java.sql.PreparedStatement preparedStatement = connection.prepareStatement(sql1);
java.sql.PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
java.sql.PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);
preparedStatement.setString(1, admin_name);
preparedStatement.setString(2, admin_pwd);
preparedStatement2.setString(1, "wxy2");
preparedStatement2.setString(2, "456");
preparedStatement3.setString(1, "abc");
preparedStatement3.setString(2, "wxy");
// 不再添加sql了
ResultSet resultSet = preparedStatement.executeQuery();
int rows = preparedStatement2.executeUpdate();
int rows3 = preparedStatement3.executeUpdate();
while (resultSet.next()) {
String name = resultSet.getString("name");
String pwd = resultSet.getString("pwd");
System.out.println(name + " " + pwd);
}
System.out.println(rows);
System.out.println(rows3);
resultSet.close();
preparedStatement.close();
connection.close();
JDBCUtils
Utils
public class JDBCUtils {
// 1. 定义常用属性(4个) 做成静态
private static String user; //用户
private static String password; //用户
private static String url; //用户
private static String driver; //用户
// 2. 通过静态代码块完成初始化
static {
String mysqlPath = "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties";
Properties properties = new Properties();
try {
properties.load(new FileInputStream(mysqlPath));
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
url = properties.getProperty("url");
} catch (IOException e) {
// 在实际开发中,可以这样处理
// 1. 将编译异常转成 运行异常
// 2. 这个是调用者,可以选择捕获该异常,也可以选择默认处理该异常,比较方便
throw new RuntimeException();
}
}
// 3. 连接
public static Connection getConnection() {
Connection connection = null;
try {
// Class<?> aClass = Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
throw new RuntimeException();
}
return connection;
}
// 4. 关闭资源
// 4,1 ResultSet PreparedStatement Connection
// 4.2 若Connection
// 4.3 若需要关闭资源,就传入对象,否则传入null
public static void closeSource(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
throw new RuntimeException();
}
}
}
Use
public class JDBCUse {
@Test
public void testDML() {
// 1.获取连接
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = null;
// 2. sql
String sql = "insert into admin values (?, ?)";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "xwr");
preparedStatement.setString(2, "789");
int rows = preparedStatement.executeUpdate();
System.out.println("受影响行数为:" + rows);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 3. 关闭资源
JDBCUtils.closeSource(null, preparedStatement, connection);
}
}
@Test
public void testSelect() {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = null;
// 2. sql
String sql = "select * from admin";
try {
preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
String pwd = resultSet.getString(2);
System.out.println(name + " " + pwd);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 3. 关闭资源
JDBCUtils.closeSource(null, preparedStatement, connection);
}
}
}
事务 △
基本介绍
- JDBC程序中当一个Connection对象创建时,默认情况下是自动提交事务:每次执行一个SQL语句时,若执行成功,就会向数据库自动提交,而不能回滚
- JDBC程序中为了让多个SQL语句作为一个整体执行,需要使用事务
- 调用Connection的setAutoCommit(false)可以取消自动提交事务
- 在所有的SQL语句都成功执行后,调用Connection的commit()方法提交事务
- 在其中某个操作失败或出现异常时,调用rollback()方法回滚事务
事务处理
@Test
public void noTransaction() {
Connection connection = JDBCUtils.getConnection();
String sql1 = "update account set balance = balance -1000 where id = 1";
String sql2 = "update account set balance = balance +1000 where id = 2";
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql1);
int rows1 = preparedStatement.executeUpdate();
int i = 1 / 0; // 异常后不再执行
preparedStatement = connection.prepareStatement(sql2);
int rows2 = preparedStatement.executeUpdate();
System.out.println("受影响行数:" + rows1);
System.out.println("受影响行数:" + rows2);
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtils.closeSource(null, preparedStatement, connection);
}
}
@Test
public void uesTransaction() {
Connection connection = JDBCUtils.getConnection();
String sql1 = "update account set balance = balance -1000 where id = 1";
String sql2 = "update account set balance = balance +1000 where id = 2";
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql1);
connection.setAutoCommit(false);
connection.setSavepoint("a"); // 设置回滚点 可选
int rows1 = preparedStatement.executeUpdate();
int i = 1 / 0; // 异常后不再执行
preparedStatement = connection.prepareStatement(sql2);
int rows2 = preparedStatement.executeUpdate();
System.out.println("受影响行数:" + rows1);
System.out.println("受影响行数:" + rows2);
// 最后进行提交
connection.commit();
} catch (SQLException e) {
// 这里可以进行回滚,撤销执行的SQL
try {
connection.rollback(); // 回滚到开启事务
} catch (SQLException ex) {
ex.printStackTrace();
}
throw new RuntimeException();
} finally {
JDBCUtils.closeSource(null, preparedStatement, connection);
}
}
批处理
基本介绍
- 当需要成批插入或者更新记录时,可以采用java的批量更新机制,这一机制云溪许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。
- JDBC的批量处理语句包括下面方法:
- addBatch() 添加需要批量处理的sql语句或参数
- executeBatch() 执行批量处理语句
- clearBatch() 情况批处理包的语句
- JDBC连接MySql时,若要使用批处理功能,请在url中加参数?rewriteBatchedStatements=true
- 批处理往往和PrepareStatement一起搭配使用,可以既减少编译次数,又减少运行次数,效率提高
批处理操作
@Test
public void noBatch() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values(null, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "wxy" + 1);
preparedStatement.setString(2, "wxy" + 1);
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
JDBCUtils.closeSource(null, preparedStatement, connection);
}
@Test
public void useBatch() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values(null, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "wxy" + 1);
preparedStatement.setString(2, "wxy" + 1);
// 加入到批处理中
preparedStatement.addBatch();
// 当够1000条时,执行一次
if ((i + 1) % 1000 == 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println(end - start);
JDBCUtils.closeSource(null, preparedStatement, connection);
}
addBatch()机制
/* 1.
public void addBatch() throws SQLException {
try {
synchronized(this.checkClosed().getConnectionMutex()) {
QueryBindings<?> queryBindings = ((PreparedQuery)this.query).getQueryBindings();
queryBindings.checkAllParametersSet(); 检查参数
this.query.addBatch(queryBindings.clone()); 添加
}
} catch (CJException var6) {
throw SQLExceptionsMapping.translateException(var6, this.getExceptionInterceptor());
}
}
2.
if (this.batchedArgs == null) { // 若第一次添加,则创建一个数组链表
this.batchedArgs = new ArrayList();
}
3.
batchedArgs 有 elementData对象数组
4.
elementData[size++] = e; // 将要添加的数据放入到elementData中
5. 当超过容量限制后,会进行扩容1.5*
6. 当添加到指定值后,就executeBatch
批处理会减少发送SQL语句的网络开销,而且减少编译次数
*/
连接池 △
传统获取Connection问题
- 传统的JDBC数据库连接使用DriverManager来获取,每次想数据库建立连接的时候都要将Connection加载到内存中,再验证IP地址,用户名和密码(0.05s-1s)。需要数据库连接的时候,就想数据库要求一个,频繁的进行数据库连接操作将占用很多的系统资源,容易造成服务器崩溃
- 每一次数据库连接,使用完后都得断开,若程序出现异常而未能关闭,将导致数据库内存泄漏,最终将导致重启数据库
- 传统获取连接的方式,不能控制创建的连接的数量,若连接过多,也可能导致内存泄漏,MySQL崩溃
- 解决传统开发中的数据库连接问题,可以采用数据库连接池技术(Connection pool)
基本介绍
- 预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时,只需从”缓冲池“中取出一个,使用完毕之后再放回去
- 数据库连接池负责分配、管理和释放数据库的链接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。
- 当应用程序向连接池请求的链接数超过最大连接数量时,这些请求将被加入到等待队列中
连接池种类
- JDBC的数据库连接池使用javax.sql.DataSource来表示,DataSource只是一个接口,该接口由第三方提供实现【提供jar包】
- C3P0数据库连接池,速度相对较慢,稳定性不错
- DBCP数据库连接池,速度相对C3P0较快,但不稳定
- Proxool数据库连接池,有监控连接池状态的功能,稳定性交C3P0差一点
- BoneCP数据库连接池,速度快
- Druid(德鲁伊)是阿里提供的数据库连接池,集DBCP、C3P0、Proxool优点于一身的数据库连接池
C3P0
@Test
// 方式1 : 相关参数,在程序中指定user, url, password等
public void testC3P0_1() throws Exception {
// 1. 创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
// 2. 通过配置文件 获取相关连接信息
String mysqlPath = "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties";
Properties properties = new Properties();
properties.load(new FileInputStream(mysqlPath));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
// 3. 传给数据源comboPooledDataSource 设置相关参数
// 注意: 连接管理是由comboPooledDataSource来管理的
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
// 设置初始化链接数
comboPooledDataSource.setInitialPoolSize(10);
// 设置最大连接数
comboPooledDataSource.setMaxPoolSize(50);
long start = System.currentTimeMillis();
Connection connection = null;
for (int i = 0; i < 5000; i++) {
connection = comboPooledDataSource.getConnection();
JDBCUtils.closeSource(null, null, connection);
}
long end = System.currentTimeMillis();
System.out.println("连接成功");
System.out.println(end - start);
connection.close();
comboPooledDataSource.close();
}
@Test
// 第二种方式 使用配置文件来完成
// 1. c3p0提供的c3p0.config.xml 拷贝到进来
// 2. 该文件制定了连接数据库和连接池的相关参数
public void testC3P0_2() throws Exception {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("wxy");
Connection connection = comboPooledDataSource.getConnection();
System.out.println("连接成功");
connection.close();
}
Druid △
@Test
public void testDruid1() throws Exception {
// 1. 加入jar包
// 2. 加入配置文件 拷贝到src
// 3. 创建Properties对象
Properties properties = new Properties();
properties.load(new FileInputStream("/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/druid.properties"));
// 4. 创建一个制定参数的数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = dataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("连接成功");
System.out.println(end - start);
}
public class JDBCUtilsByDruid {
private static DataSource ds;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream(
"/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
// 关闭连接,在数据库连接池技术中,close不是真的断掉连接
// 而是把使用的Connection对象放回连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException();
}
}
}
@Test
public void testSelect() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
String sql = "select * from admin";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
String pwd = resultSet.getString(2);
System.out.println(name + "\t" + pwd);
}
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
Apache–DBUtils
// 使用apache-DbUtils 工具类+ druid 完成对表的操作
@Test
public void QueryMany() throws Exception {
// 1. 获得连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 2. 使用DBUtils类和接口,先引入jar包
// 3. 创建QuerRunner
QueryRunner queryRunner = new QueryRunner();
// 4. 执行相关方法,返回ArrayList结果集 可以查询部分字段
String sql = "select * from actor where id > ?";
// String sql = "select id, name, sex from actor where id > ?";
// 4.1 query 方法就是执行sql语句,获得ResultSet --》 封装袋 --》 Arraylis集合中
// 4.2 返回集合
// 4.3 connection 连接
// 4.4 sql 执行的SQL语句
// 4.5 new BeanListHandler<>(Actor.class) 在将ResultSet --> Actor 对象 --> 封装ArraList
// 底层使用反射机制,以获取Actor类的属性,然后进行封装
// 4.6 1 就是给sql语句中的? 赋值, 可以有多个值,因为是可变参数
// 4.7 底层得到的resultSet 会在query关闭, 关闭 PreparedStatement
List<Actor> list =
queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 0);
for (Actor actor : list) {
System.out.println(actor);
}
JDBCUtilsByDruid.close(null, null, connection);
}
/**
* 底层实现
* PreparedStatement stmt = null; --定义PreparedStatement
* ResultSet rs = null; --接收返回的结果集ResultSet
* Object result = null; --返回ArrayList
*
* try {
* stmt = this.prepareStatement(conn, sql); --创建prepareStatement
* this.fillStatement(stmt, params); --对sql进行 ? 赋值
* rs = this.wrap(stmt.executeQuery()); // 执行sql 返回resultSet
* result = rsh.handle(rs); --返回的resultSet --> arrayList[result] [使用反射机制,对传入的class进行处理]
* } catch (SQLException var33) {
* this.rethrow(var33, sql, params);
* } finally {
* try {
* this.close(rs); --关闭返回resultSet
* } finally {
* this.close(stmt); --关闭定义PreparedStatement
* if (closeConn) {
* this.close(conn); 关闭connection
* }
*
* }
* }
*
*
*/
Query
@Test
public void QuerySingle() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
String sql = "select * from actor where id = ?";
QueryRunner queryRunner = new QueryRunner();
// 返回一行信息,用BeanHandler
Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 10);
System.out.println(actor);
JDBCUtilsByDruid.closeConn(connection);
}
@Test
public void QueryScalar() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
String sql = "select name from actor where id = ?";
QueryRunner queryRunner = new QueryRunner();
// 因为返回的是一个地下,使用handler就是ScalarHandler
Object o = queryRunner.query(connection, sql, new ScalarHandler<>(), 1);
System.out.println(o);
JDBCUtilsByDruid.closeConn(connection);
}
Update
@Test
public void UpdateMess() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
String sql1 = "update actor set name = ? where id = ?";
String sql2 = "insert into actor values(null, ?, ?, ?, ?)";
String sql3 = "delete from actor where id = ?";
QueryRunner queryRunner = new QueryRunner();
// 1. 操作是queryRunner.update()
// 2. 返回值是受影响的行数
int affectRow1 = queryRunner.update(connection, sql1, "zhangsan", 1);
int affectRow2 = queryRunner.update(connection, sql2,
"lisi", "女", "1999-09-03", "123456789");
int affectRow3 = queryRunner.update(connection, sql3, 5);
System.out.println(affectRow1);
System.out.println(affectRow2);
System.out.println(affectRow3);
JDBCUtilsByDruid.closeConn(connection);
}
Dao 增删改查 – BasicDao △
Actor
public class Actor {
private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor() {
}
public Actor(Integer id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
JDBCUtilsByDruid
public class JDBCUtilsByDruid {
private static DataSource ds;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream(
"/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
// 关闭连接,在数据库连接池技术中,close不是真的断掉连接
// 而是把使用的Connection对象放回连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException();
}
}
public static void closeConn(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException();
}
}
}
BasicDao
public class BasicDao<T> { // 泛型,指定pojo
private QueryRunner queryRunner = new QueryRunner();
// 开发统一dml方法,针对任意表
// 修改表 增删改
public int update(String sql, Object... paramters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = queryRunner.update(connection, sql, paramters);
return update;
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.closeConn(connection);
}
}
// 查询多个对象 记得传入Class对象,进行反射
public List<T> queryMulti(String sql, Class<T> clazz, Object... paramters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler<>(clazz), paramters);
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.closeConn(connection);
}
}
// 查询单行记录
public T querySingle(String sql, Class<T> clazz, Object... paramters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanHandler<>(clazz), paramters);
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.closeConn(connection);
}
}
// 查询单行记录
public Object queryScalar(String sql, Object... paramters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new ScalarHandler<>(), paramters);
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.closeConn(connection);
}
}
}
ActorDao
public class ActorDao extends BasicDao<Actor> {
// 1. 拥有BasicDao的方法
// 2. 根据需求,可以编写特有的方法
}
Test
public class Test {
public static void main(String[] args) {
ActorDao actorDao = new ActorDao();
String sql = "select * from actor where id > ?";
List<Actor> actors = actorDao.queryMulti(sql, Actor.class, 0);
for (Actor actor : actors) {
System.out.println(actor);
}
Actor actor = actorDao.querySingle("select * from actor where id = ?",
Actor.class, 1);
System.out.println(actor);
Object o = actorDao.queryScalar("select name from actor where id = 1");
System.out.println(o);
int affectRows = actorDao.update("insert into actor values(null, ?, ? ,?, ?)",
"wuwuwu", "女", "2000-5-7", "123456");
System.out.println(affectRows);
}
}