二十五、JDBC和数据库连接池

一、JDBC概述

二、JDBC快速入门

2.1 添加驱动

• 新建 libs 文件夹 --> 复制驱动 --> 右键 .jar 文件 Add as Library …
  

2.2 执行sql

package com.gyhedu.jdbc.myjdbc;import com.mysql.jdbc.Driver;import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class JDBC01 {public static void main(String[] args) throws SQLException {// 前置工作：在项目下创建一个文件夹比如 libs// 将 mysql.jar 拷贝到该目录下，点击 add to project .. 加入到项目中// 1. 注册驱动Driver driver = new Driver();// 2. 得到连接// 老师解读// (1) jdbc:mysql:// 规定好表示协议，通过jdbc的方式连接mysql// (2) localhost  主机, 可以是ip地址// (3) 13306  表示mysql监听的端口// (4) dbtest1  连接到 mysql dbms 的哪个数据库// (5) mysql的连接本质就是前面学过的socket连接String url = "jdbc:mysql://localhost:13306/dbtest1";// 将用户和密码封装到Properties中Properties properties = new Properties();properties.setProperty("user","root"); // 用户properties.setProperty("password","gyhgzf"); // 用户// 网络连接Connection connect = driver.connect(url, properties);// 3、执行sqlString sql = "INSERT INTO actor(name, sex, borndate, phone) VALUES('刘德华', '男', '1970-11-11', '110')";// statement 用于执行静态SQL语句并返回其生成的结果的对象Statement statement = connect.createStatement();int rows = statement.executeUpdate(sql);System.out.println(rows > 0?"成功":"失败");// 关闭连接statement.close();connect.close();}}

2.3 获取数据库连接5种方式

2.4 ResultSet[结果集]

package com.gyhedu.jdbc.myjdbc;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class JDBC03 {public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {// 0. 获取连接数据库的配置信息Properties properties = new Properties();properties.load(new FileInputStream("src\\mysql.properties"));String driver = properties.getProperty("driver");String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");// 1. 动态加载类（注册驱动）Class.forName(driver);// 2. 获取连接Connection connection = DriverManager.getConnection(url, user, password);// 3. 得到 statement 对象Statement statement = connection.createStatement();// 4. 组织sql语句String sName = "刘德华";String sql = "SELECT id, `name`, sex, borndate, phone FROM actor WHERE `name` = '" + sName + "';";// 4. 得到结果集对象ResultSet resultSet = statement.executeQuery(sql);// 5. 使用while循环遍历数据while (resultSet.next()) {int id = resultSet.getInt(1); // 获取该该行第一列数据String name = resultSet.getString(2); // 获取该该行第二列数据String sex = resultSet.getString(3); // 获取该该行第三列数据String bornDate = resultSet.getString(4); // 获取该该行第四列数据String phone = resultSet.getString(5); // 获取该该行第五列数据System.out.println(id + '\t' + name + '\t' + sex + '\t' + bornDate + '\t' + phone);}resultSet.close();statement.close();connection.close();}
}

2.5 statement（SQL注入）

2.6 PreparedStatement

package com.gyhedu.jdbc.myjdbc;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class PreparedStatement01 {public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {Properties properties = new Properties();properties.load(new FileInputStream("src\\mysql.properties"));String driver = properties.getProperty("driver");String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);// ? 相当于占位符String sql = "SELECT COUNT(*) FROM actor WHERE id > ?";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, 2);ResultSet resultSet = preparedStatement.executeQuery();while(resultSet.next()){System.out.println(resultSet.getInt(1));}// 关闭流preparedStatement.close();connection.close();}
}

三、JDBC API

四、JDBCUtils

package com.gyhedu.jdbc.jdbcutils;import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class JDBCUtils {private static String url;private static String user;private static String password;private static String driver;static {Properties properties = new Properties();try {properties.load(new FileInputStream("src\\mysql.properties"));url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");driver = properties.getProperty("driver");Class.forName(driver);} catch (IOException | ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}public static void close(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();}}}
}

package com.gyhedu.jdbc.jdbcutils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/*** @author Gao YongHao* @version 1.0*/
public class JDBCUtilsTest {public static void main(String[] args) throws SQLException {// 获取连接Connection connection = JDBCUtils.getConnection();String sql = "SELECT id, `name`, sex, borndate, phone FROM actor WHERE id > ?";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, 1);ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {int id = resultSet.getInt(1);String name = resultSet.getString(2);String sex = resultSet.getString(3);String bornDate = resultSet.getString(4);String phone = resultSet.getString(5);System.out.println(id + "\t" + name + "\t" + sex + "\t" + bornDate + "\t" + phone);}// 关闭连接JDBCUtils.close(resultSet, preparedStatement, connection);}
}

五、事务

5.1 基本介绍

5.2 应用实例

package com.gyhedu.jdbc.event;import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;/*** @author Gao YongHao* @version 1.0*/
public class Event {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;PreparedStatement preparedStatement1 = null;try {connection = JDBCUtils.getConnection();// 设置不自动提交(开启事务)connection.setAutoCommit(false);// 马云向马化腾转100元String sql1 = "UPDATE account SET balance = balance - ? WHERE name = ?";preparedStatement = connection.prepareStatement(sql1);preparedStatement.setInt(1, 100);preparedStatement.setString(2, "马云");String sql2 = "UPDATE account SET balance = balance + ? WHERE name = ?";preparedStatement1 = connection.prepareStatement(sql2);preparedStatement1.setInt(1, 100);preparedStatement1.setString(2, "马化腾");preparedStatement.executeUpdate();int n = 1 / 0;preparedStatement1.executeUpdate();// 手动提交connection.commit();} catch (SQLException e) {e.printStackTrace();} finally {if (connection != null) {try {// 异常回滚connection.rollback();} catch (SQLException e) {e.printStackTrace();}}JDBCUtils.close(null, preparedStatement, connection);JDBCUtils.close(null, preparedStatement1, null);}}
}

六、批处理

七、连接池

7.1 基本介绍

7.2 数据库连接池种类

7.3 C3P0应用实例

package com.gyhedu.jdbc.dbpool;import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class C3P0Test {public static void main(String[] args) throws SQLException {c3p0Test_02();}// 方式一：使用private static void testC3P0_01() throws Exception {// 1、创建一个数据源对象ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();// 2、通过配置文件mysql.properties获取相关连接的信息Properties properties = new Properties();properties.load(new FileInputStream("src\\mysql.properties"));String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");// 给数据源 comboPooledDataSource 设置相关参数// 注意：连接管理是由 comboPooledDataSource 管理的comboPooledDataSource.setDriverClass(driver);comboPooledDataSource.setJdbcUrl(url);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);// 设置初始化连接数comboPooledDataSource.setInitialPoolSize(10);// 设置最大连接数comboPooledDataSource.setMaxPoolSize(50);// 这个方法就是对 DataSource 接口实现Connection connection = comboPooledDataSource.getConnection();connection.close();}// 方式二：使用配置文件模板来操作// 1. 将c3p0提供的 c3p0-config.xml 拷贝到 src 目录下// 2. 该文件指定了相关的参数private static void c3p0Test_02() throws SQLException {ComboPooledDataSource myc3p0 = new ComboPooledDataSource("Myc3p0");Connection connection = myc3p0.getConnection();System.out.println("连接成功");connection.close();}
}

<!-- c3p0-config.xml -->
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config><!-- 数据源名称代表连接池 --><named-config name="Myc3p0"><!-- 指定连接数据源的基本属性 --><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:13306/dbtest1</property><property name="user">root</property><property name="password">gyhgzf</property><!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 --><!-- 每次增长的连接数，即在小于最大连接数的情况下，每次新增的连接数 --><property name="acquireIncrement">5</property><!-- 初始化数据库连接池时连接的数量 --><property name="initialPoolSize">5</property><!-- 数据库连接池中的最小的数据库连接数 --><property name="minPoolSize">5</property><!-- 数据库连接池中的最大的数据库连接数 --><property name="maxPoolSize">10</property><!-- C3P0 数据库连接池可以维护的 Statement 的个数 --><property name="maxStatements">20</property><!-- 每个连接同时可以使用的 Statement 对象的个数 --><property name="maxStatementsPerConnection">5</property></named-config></c3p0-config>

7.4 Druid应用实例

package com.gyhedu.jdbc.dbpool;import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class DruidTest {public static void main(String[] args) throws Exception {// 1. 加入 Druid 的jar包// 2. 加入配置文件 druid.properties，将该文件拷贝至项目的src目录// 3. 创建Properties对象Properties properties = new Properties();properties.load(new FileInputStream("src\\druid.properties"));// 4. 创建一个指定参数的数据库连接池,Druid连接池DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);Connection connection = dataSource.getConnection();System.out.println("连接成功");connection.close();}
}

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:13306/dbtest1?rewriteBatchStatements=true
#url=jdbc:mysql://localhost:13306/dbtest1
username=root
password=gyhgzf
#initial connection Size
initialSize=10
#min idle connection size
minIdle=5
#max active connection size
maxActive=50
#max wait time（5000 mil seconds）
maxWait=5000

package com.gyhedu.jdbc.jdbcutils;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class JDBCUtilsByDruid {private static DataSource dataSource;static {try {Properties properties = new Properties();properties.load(new FileInputStream("src\\druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {return dataSource.getConnection();}// 关闭连接池。在数据库连接池技术中，close 不是真的断掉连接// 而是把使用的Connection对象放回连接池public static void close(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();}}}
}

八、Apache — DBUtils

8.1 自定义的解决方法

package com.gyhedu.jdbc.jdbcutils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;/*** @author Gao YongHao* @version 1.0*/
public class JDBCUtilsByDruidTest {public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException {Connection connection = JDBCUtilsByDruid.getConnection();String sql = "SELECT id, name, sex, borndate, phone FROM actor WHERE id >= ?";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, 1);ResultSet resultSet = preparedStatement.executeQuery();List<Actor> result = JDBCUtilsByDruid.getResult(resultSet, Actor.class);result.forEach(System.out::println);JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);}
}

package com.gyhedu.jdbc.jdbcutils;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;/*** @author Gao YongHao* @version 1.0*/
public class JDBCUtilsByDruid {private static DataSource dataSource;static {try {Properties properties = new Properties();properties.load(new FileInputStream("src\\druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {return dataSource.getConnection();}public static <T> List<T> getResult(ResultSet resultSet, Class<T> beanClass) throws SQLException, IllegalAccessException, InstantiationException {List<T> list = new ArrayList<>();Field[] declaredFields = beanClass.getDeclaredFields();while(resultSet.next()){T entity = null;entity = beanClass.newInstance();for(Field f:declaredFields){f.setAccessible(true);f.set(entity,resultSet.getObject(f.getName()));}list.add(entity);}return list;}// 关闭连接池。在数据库连接池技术中，close 不是真的断掉连接// 而是把使用的Connection对象放回连接池public static void close(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();}}}
}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;}@Overridepublic String toString() {return "Actor{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", borndate=" + borndate +", phone='" + phone + '\'' +'}';}
}

8.2 Apache — DBUtils

package com.gyhedu.jdbc.jdbcutils;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;/*** @author Gao YongHao* @version 1.0*/
public class DBUtilsUse {public static void main(String[] args) throws SQLException {// 1. 得到连接Connection connection = JDBCUtilsByDruid.getConnection();// 2. 使用 DBUtils 类和接口，先引入 DBUtils 相关的jar，加入到本 Project// 3. 创建 QueryRunnerQueryRunner queryRunner = new QueryRunner();// 4. 就可以执行相关的方法，返回ArrayList结果集String sql = "SELECT * FROM actor WHERE id >= ?";// 解读// (1) query 方法就是执行sql语句，得到 ResultSet --- 封装到 ---> ArrayList 集合中// (2) 返回集合// (3) connection：连接// (4) sql：执行的sql语句// (5) new BeanListHandler<>(Actor.class)：在将resultset -> Actor对象 -> 封装到 ArrayList// (6) 1 就是给 sql 语句中 ? 赋值，可以多个值，因为是可变参数// (7) 底层的带的 resultset，会在query中关闭，关闭PreparedStatementList<Actor> list =queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);System.out.println("输出集合的信息");for (Actor actor : list) {System.out.println(actor);}JDBCUtilsByDruid.close(null, null, connection);}
}

九、DAO增删改查 - BasicDao

• DAO：数据访问对象
  

// BasicDAO
package com.gyhedu.dao;import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import com.gyhedu.utils.JDBCUtilsByDruid;
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;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;/*** @author Gao YongHao* @version 1.0* 开发BasicDAO，是其他DAO的父类*/
public class BasicDAO<T> {private QueryRunner queryRunner = new QueryRunner();// 开发通用的dml方法，针对任意的表public int update(String sql, Object... parameters) {Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();int update = queryRunner.update(connection, sql, parameters);return update;} catch (SQLException e) {throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}/*** @param sql        sql语句* @param clazz      传入一个类的Class对象 比如 Actor.class* @param parameters 传入 ? 的具体的值，可以多个* @param <T>* @return*/// 返回多个对象（即查询的结果是多行），针对任意表public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();List<T> query =queryRunner.query(connection, sql, new BeanListHandler<>(clazz), parameters);return query;} catch (SQLException e) {throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}/*** 返回第一行数据* @param sql* @param clazz* @param parameters* @param <T>* @return*/public T querySingle(String sql, Class<T> clazz, Object... parameters) {Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();T query =queryRunner.query(connection, sql, new BeanHandler<>(clazz), parameters);return query;} catch (SQLException e) {throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}/*** 返回单值的方法* @param sql* @param parameters* @return*/public Object queryScalar(String sql, Object... parameters) {Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();Object query =queryRunner.query(connection, sql, new ScalarHandler<>(), parameters);return query;} catch (SQLException e) {throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出} finally {JDBCUtilsByDruid.close(null, null, connection);}}}

// ActorDAO
package com.gyhedu.dao;import com.gyhedu.jdbc.jdbcutils.Actor;/*** @author Gao YongHao* @version 1.0*/
public class ActorDAO extends BasicDAO<Actor>{// 1. 就有 BasicDAO 方法// 2. 根据业务需求，可以编写特有的方法}