JAVA——JDBC学习

视频连接：https://www.bilibili.com/video/BV1sK411B71e/?spm_id_from=333.337.search-card.all.click&vd_source=619f8ed6df662d99db4b3673d1d3ddcb

《视频讲解很详细！！推荐》

JDBC（Java DataBase Connectivity Java数据库连接）

栗子：类比USB

1. 概述⛺

出现原因

• 在Java中提供对数据库的访问支持，便于软件开发过程中使用数据库来存储和管理数据
• 如果用户直接操作数据库，数据库种类变化之后，代码改动量很大。JDBC由各数据库厂商按照统一的规范提供数据库驱动（JDBC实现类），可以操作所有的关系型数据库，减少了用户和底层数据库的交互，增强了代码的通用性。

推行

SUN公司于1996年提供访问数据库的标准Java类库

原理

步骤

• 步骤1：java.sql.* JDBC接口 对象 = 第三方实现类实例；（JDBC API主要位于java.sql包中，该包定义了一系列访问数据的接口和类）
• 步骤2：对象.jdbc标准方法();

组成

• Java提供的jdbc规范（接口，多态，面向接口编程）
• 各个数据库厂商的实现驱动jar包

2. JDBC技术组成🌃

2.0 使用步骤

0）准备数据库

mysql> create table t_user(-> id int primary key auto_increment comment '用户主键',-> account varchar(20) not null unique comment '账户',-> password varchar(64) not null comment '密码',-> nickname varchar(20) not null comment '昵称');insert into t_user(account, password, nickname) values ('root', '123456', '经理'), ('admin', '666666', '管理员');

1）注册驱动：安装jar包，注册了之后Java就允许远程数据库的连接了

2）获取连接：建立java程序和数据库软件的通道

3）创建发送sql语句对象：sql语句的载体

4）发送sql语句，并获取返回结果：载体发送sql到数据库，并获取返回结果，得到一个结果对象

5）结果集解析：把结果对象拆出来

6）资源关闭：销毁资源，关闭连接，关闭载体，释放结果对象

2.1 DriverManager

作用

• 将第三方数据库厂商的实现驱动jar注册到程序中
• 可以根据数据库的连接信息获取Connection

2.2 Connection

作用

• 和数据库建立连接，在连接对象上，可以多次执行数据库操作
• 可以获得下面2.3节的三类对象

2.3 Statement | PreparedStatement | CallableStatement

1）Statement：静态SQL路线（没有动态值语句，也就是没有条件的）

/*
使用statement查询t_user表下，全部数据*/
public class StatementQueryPart {public static void main(String[] args) throws SQLException {// 1. 注册驱动DriverManager.registerDriver(new Driver());// 2. 获取连接// 参数1：url——jdbc:数据库厂商名://ip地址:port/数据库名// java.sql 接口 = 实现类Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test", "xy", "123456");// 3. 创建发送sql语句的对象 StatementStatement statement = connection.createStatement();// 4. 发送sql语句并返回结果集String sql = "select * from t_user;";ResultSet resultSet = statement.executeQuery(sql);// 5. 解析结果// next是看看下一行有没有数据，有的话就取，没有的话就退出了。它初始指的位置是指向字段的while (resultSet.next()) {int id = resultSet.getInt("id");String account = resultSet.getString("account");String password = resultSet.getString("password");String nickname = resultSet.getString("nickname");System.out.println("id:" + id + " | " + "account: " + account + " | " + "password: " + password + " | " + "nickname: " + nickname);}// 6. 资源关闭resultSet.close();statement.close();connection.close();}
}/* 
结果
id:1 | account: root | password: 123456 | nickname: 经理
id:2 | account: admin | password: 666666 | nickname: 管理员
*/

// 详细知识！！
public class StatementUserLoginPart {public static void main(String[] args) throws Exception {// 1. 键盘输入事件Scanner sc = new Scanner(System.in);System.out.print("请输入账号：");String account = sc.nextLine();System.out.print("请输入密码：");String password = sc.nextLine();// 2. 注册驱动// DriverManager.registerDriver(new Driver());  // 问题：注册两次驱动（方法本身注册一次，new对象里面有静态代码块也会注册一次），性能消耗// 改进：只触发静态代码块——类加载的时候1）new关键字；2）调用静态方法；3）调用静态属性；4）接口default默认实现；5）反射；6）子类出发父类；7）程序入口main// new Driver();// 反射：字符串参数提取到外部配置文件——>灵活Class.forName("com.mysql.cj.jdbc.Driver");// 3. 获取数据库连接// 如果本机并且端口是3306，url可以简写jdbc:mysql:///test// 三个参数// Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test", "xy", "123456");// 两个参数// Properties info = new Properties();// info.put("user", "xy");// info.put("password", "123456");// Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test", info);// 一个参数// jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");// 4. 创建发送sql语句的Statement对象Statement statement = connection.createStatement();// 5. 发送sql并返回结果集String sql = "select * from t_user where account = '"+ account + "'and password = '" + password + "';";// 非DQL int row = executeUpdate(sql) ;  DML影响的行数，其他就是返回0// ResultSet resultSet = statement.executeQuery(sql)； 返回的是结果封装对象ResultSet resultSet = statement.executeQuery(sql);// 6. 查询结果集的解析
//        while (resultSet.next()) {
//            int id = resultSet.getInt(1);
//            String account1 = resultSet.getString("account");
//            String password1 = resultSet.getString(3);
//            String nickname = resultSet.getString("nickname");
//            System.out.println("id:" + id + " | " + "account: " + account1 + " | " + "password: " + password1 + " | " + "nickname: " + nickname);
//        }// 检查是否登录成功if (resultSet.next()) {System.out.println("登录成功啦！") ;} else {System.out.println("啊哦~~失败咯~");}// 7. 关闭资源resultSet.close();statement.close();connection.close();}
}

存在的问题：

• SQL语句需要字符串拼接，比较麻烦
• 只能拼接字符串类型，其他的数据库类型无法处理
• 可能发生注入共计——动态值充当了SQL语句结构，影响了原有的查询结果

2）PreparedStatement：预编译SQL路线（有动态值语句）——常用

public class PSUserLoginPart {public static void main(String[] args) throws Exception {// 1. 键盘输入事件Scanner sc = new Scanner(System.in);System.out.print("请输入账号：");String account = sc.nextLine();System.out.print("请输入密码：");String password = sc.nextLine();// 2. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver");// 3. 获取连接Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");// 4. 编写SQL语句String sql = "select * from t_user where account = ? and password = ? ;";// 创建预编译statement并设置SQL语句结果PreparedStatement preparedStatement = connection.prepareStatement(sql);// 单独的占位符赋值// 第一个参数是index占位符的位置，从左往由从1开始；第二个参数是object占位符的值，可以设置任何类型的数据，避免了拼接和类型更加丰富！preparedStatement.setObject(1, account);preparedStatement.setObject(2, password);// 5. 发送SQL语句，并获取返回结果ResultSet resultSet = preparedStatement.executeQuery();  // 不需要传递参数，因为已经知道sql语句并且获得了动态值// 6. 结果集解析if (resultSet.next()) {System.out.println("登录成功啦！") ;} else {System.out.println("啊哦~~失败咯~");}// 7. 关闭资源resultSet.close();preparedStatement.close();connection.close();}
}

// 更加灵活地取到结果集对象
public class PSCURDPart {public static void main(String[] args) throws Exception {// 目标：查询tb_user表中的数据，更加灵活地取到结果集对象// 1. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver");// 2. 获取连接Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");// 3. 获取创建sql语句的对象String sql = "select id, account, password, nickname from t_user";PreparedStatement preparedStatement = connection.prepareStatement(sql);// preparedStatement.setObject();  // 给占位符赋值，这里没有使用占位符，所以不进行赋值操作// 4. 将sql语句发送到数据库并返回结果集ResultSet resultSet = preparedStatement.executeQuery();// 5. 解析结果集的对象/* 一般情况while (resultSet.next()){int id = resultSet.getInt("id");}存在的问题要手动获取列名——>我们就要知道由哪些列，不智能万一列名修改了——>代码这里也要进行相应的修改，不通用*/// metaData 可以获取列的名称，也可以获取列的数量ResultSetMetaData metaData = resultSet.getMetaData();// 获取列的数量int columnCount = metaData.getColumnCount();List<Map> list = new ArrayList<>();while(resultSet.next()) {Map map = new HashMap();for (int i = 1; i <= columnCount; i++) {// 根据下角标获取值Object value = resultSet.getObject(i);// 获取指定下角标的列名String key = metaData.getColumnLabel(i);  // 会获取列的别名map.put(key, value);}list.add(map);}System.out.println(list);// 6. 释放资源resultSet.close();preparedStatement.close();connection.close();}
}

3）CallableStatement：执行标准存储过程SQL路线（有存储过程的）

2.4 Result

作用

• 数据库的查询结果表
• 存储DQL查询数据库结果的对象
• 需要我们进行解析，获取具体的数据库数据

3. 提升扩展🏙️

3.1 自增长主键回显实现

获取数据库自增长的主键——主键回显

给关联的子表插入的时候使用

public class PSOtherPart {// 创建prepareStatement的时候添加一个参数Statement.RETURN_GENERATED_KEYS// 解析结果集的时候Statement.getGeneratedKeys()返回ResultSet@Testpublic void returnPrimaryKey() throws Exception {// 1. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver");// 2. 获取连接Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");// 3. 创建statementString sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);preparedStatement.setObject(1, "test1");preparedStatement.setObject(2, "123456");preparedStatement.setObject(3, "嘎嘎嘎");// 4. 发送sql语句并获取结果int row = preparedStatement.executeUpdate();// 5. 解析结果if(row > 0) {System.out.println("数据插入成功");ResultSet resultSet = preparedStatement.getGeneratedKeys();resultSet.next();int id = resultSet.getInt(1);System.out.println("主键值是：" + id);} else{System.out.println("数据插入失败");}// 6. 关闭资源preparedStatement.close();connection.close();}
}

3.2 批量数据插入性能提升

1）url?rewriteBatchedStatements=true

2）statement.addBatch(); // 不执行，追加到values后面

3）statement.executeBatch(); // 执行批量操作

4）insert语句后面不加分号结束

@Test
public void testInsert() throws Exception {// 批量插入// 一般：插入的时候循环// 优化：批量// url?rewriteBatchedStatements=true// statement.addBatch();  // 不执行，追加到values后面// statement.executeBatch();  // 执行批量操作// 1. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver");// 2. 获取连接Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?rewriteBatchedStatements=true", "xy", "123456");// 3. 创建statementString sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";  // 批量的话sql不能加；结束PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);for (int i = 100; i < 110; i++) {preparedStatement.setObject(1, "test1" + i);preparedStatement.setObject(2, "123456" + i);preparedStatement.setObject(3, "嘎嘎嘎" + i);preparedStatement.addBatch();  // 追加}preparedStatement.executeBatch();  // 统一批量执行// 4. 发送sql语句并获取结果// 5. 解析结果// 6. 关闭资源preparedStatement.close();connection.close();
}

3.3 jdbc中数据库事务实现

事务的最基础要求是：必须是同一个连接对象 connection

业务里面开启事务

步骤：

• 事务添加是在业务方法中
• 利用try catch，开启事务，提交事务和事务回滚
• 将connection传入方法中，方法中就不需要close了

业务方法

public class BankService {@Testpublic void testtransfer() throws Exception {transfer("ergouzi", "lvdandan", 50);}public void transfer(String addAccount, String subAccount, int money) throws Exception {// 1. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver");// 2. 获取连接Connection connection = DriverManager.getConnection("jdbc:mysql://***.***.***.***:3306/test?user=xy&password=123456");        BankDao bankDao = new BankDao();try {// 开启事务// 关闭事务提交connection.setAutoCommit(false);// 执行bankDao.add(addAccount, money, connection);System.out.println("-----");bankDao.sub(subAccount, money, connection);// 事务提交connection.commit();} catch (Exception e){// 事务回滚connection.rollback();// 抛出异常throw e;} finally {connection.close();}}
}

实现方法

public class BankDao {// 加钱public void add(String account, int money, Connection connection) throws Exception {// 3. 创建statementString sql = "update t_bank set money = money + ? where account = ?;";PreparedStatement preparedStatement = connection.prepareStatement(sql);// 4. 给占位符赋值preparedStatement.setObject(1, money);preparedStatement.setObject(2, account);// 5. 发送SQL语句preparedStatement.executeUpdate();// 6. 关闭资源preparedStatement.close();System.out.println("加钱成功");}// 减钱public void sub(String account, int money, Connection connection) throws Exception {// 3. 创建statementString sql = "update t_bank set money = money - ? where account = ?;";PreparedStatement preparedStatement = connection.prepareStatement(sql);// 4. 给占位符赋值preparedStatement.setObject(1, money);preparedStatement.setObject(2, account);// 5. 发送SQL语句preparedStatement.executeUpdate();// 6. 关闭资源preparedStatement.close();System.out.println("减钱成功");}
}

4. Druid连接池技术使用🌄

4.1 连接性能消耗问题分析

connection可以复用！！容器池——节约了创建和销毁连接的性能消耗，提升了时间的响应

4.2 数据库连接池作用

• 连接池可以存储一定数量的连接对象
• 用户要的时候就直接拿，拿完再放回去
• 池子李的连接都用完了，可以向服务器申请新的连接放到池子里
• 池中的连接达到最大连接数的时候，就不能申请新的连接了，等等嗷

4.3 市面常见连接池产品和对比

javax.sql.DataSource接口

• 规范了连接池获取连接的方法
• 规范了连接池回收连接的方法

DataSource = 第三方连接池的实现

选择考虑

• 性能优势
• 性能扩展

4.4 druid连接池使用

硬编码

public class DruidUsePart {/** 直接使用代码设置连接池连接参数* 1. 创建一个druid连接池对象* 2. 设置连接池参数 [必须 | 非必须]* 3. 获取连接 [通用方法，所有连接池都一样]* 4. 回收连接 [通用方法，所有连接池都一样]* */public void testHard() throws Exception {// 连接池对象DruidDataSource dataSource = new DruidDataSource();// 设置参数// 必须 注册驱动 | url | user| passworddataSource.setUrl("jdbc:mysql://***.***.***.***:3306/test");dataSource.setUsername("xy");dataSource.setPassword("123456");dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");  // 驱动注册和获取连接// 非必须 初始化连接数量，最大的连接数量……dataSource.setInitialSize(5); // 初始化连接数量dataSource.setMaxActive(10);  // 最大的数量// 获取连接Connection connection = dataSource.getConnection();// 数据库// 回收连接connection.close();}
}

软编码

// 软连接：通过读取外部配置文件的方法，实例化druid连接池对象
@Test
public void testSoft() throws Exception {// 1. 读取外部配置文件 PropertiesProperties properties = new Properties();// src下的文件，可以使用类加载器提供的方法InputStream ips = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");properties.load(ips);// 2. 使用连接池工具类的工程模式，创建连接池DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);Connection connection = dataSource.getConnection();// 中间做数据库curdconnection.close();
}

避免每次重复代码，实现工具类

需求：内部包含一个连接池对象，并对外提供获取连接和回收连接的方法！

工具类

// 内部包含一个连接池对象，并对外提供获取连接和回收连接的方法！
/*
* 属性：连接池对象【实例化一次】
* 方法：
*       对外提供连接的方法
*       回收外部传入连接方法
* */
public class JdbcUtils {private static DataSource dataSource = null;  // 连接池对象static {// 初始化连接池对象// 1. 读取外部配置文件 PropertiesProperties properties = new Properties();// src下的文件，可以使用类加载器提供的方法InputStream ips = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");try {properties.load(ips);} catch (IOException e) {e.printStackTrace();}try {// 2. 使用连接池工具类的工程模式，创建连接池dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() throws Exception {return dataSource.getConnection();}public static void freeConnection(Connection connection) throws Exception {connection.close();}
}

测试

public class JdbcCrudPart {public void testInsert() throws Exception {Connection connection = JdbcUtils.getConnection();// 数据库curd动作JdbcUtils.freeConnection(connection);}
}

基于以上部分的思考：在事务中，需要保证整个事务都是一个连接。之前的做法是在业务类中创建连接，在调用实际方法的时候，将连接作为参数传递——>增加参数传递这个步——>实质：保证同一线程的方法使用同一个连接——>将连接保存在这个本地线程对象中，执行的时候确定一下这个本地线程对象中的连接

做法：全局声明一个线程本地对象

​ 工具类中判断线程本地对象中的连接是否存在

public class JdbcUtilsV2 {private static DataSource dataSource = null;  // 连接池对象// 线程本地对象***private static ThreadLocal<Connection> tl = new ThreadLocal<>();static {// 初始化连接池对象// 1. 读取外部配置文件 PropertiesProperties properties = new Properties();// src下的文件，可以使用类加载器提供的方法InputStream ips = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");try {properties.load(ips);} catch (IOException e) {e.printStackTrace();}try {// 2. 使用连接池工具类的工程模式，创建连接池dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() throws Exception {// 查看线程本地变量中是否存在Connection connection = tl.get();// 判断有没有if (connection == null) {connection = dataSource.getConnection();tl.set(connection);}return connection;}public static void freeConnection() throws Exception {// 看看线程本地变量中有没有这个连接Connection connection = tl.get();if (connection != null) {tl.remove();  // 情况线程本地变量connection.setAutoCommit(true);  // 事务状态回归connection.close();}}
}

5. baseDao🌅

5.1 baseDo概念

Dao：每一个数据表对应的DAO接口及其实现类，实现对数据表的增删改查

DaoseDao：Dao实现类中重复度很高的代码抽象成的公共父类

5.2 非DQL方法封装

public class BaseDao {public static int executeUpdate(String sql, Object...parms) throws Exception {// 获取连接Connection connection = JdbcUtilsV2.getConnection();// 创建statementPreparedStatement preparedStatement = connection.prepareStatement(sql);for (int i = 1; i <= parms.length; i++) {preparedStatement.setObject(i, parms[i-1]);}// 发送sql语句int rows = preparedStatement.executeUpdate();// 关闭连接-要看这里是否是事务中的连接，如果是事务里面的，后面可能还会用，就不关闭连接；否则关闭连接if (connection.getAutoCommit()) {// 没事务JdbcUtilsV2.freeConnection();}return rows;}
}

调用一下

@Test
public void testInsert() throws Exception {String sql = "insert into t_user(account, password, nickname) values (?, ?, ?)";  // 批量的话sql不能加；结束executeUpdate(sql, "测试1", "1234", "222");System.out.println("执行成功！");
}

执行之后，数据库内插入成功了，但是报错如下：信息: {dataSource-1} inited

Debug——配置文件是否有问题

大no特no——这不是出现问题了(还改了一大堆)

参考连接：https://wenku.csdn.net/answer/55db899163e9498f8c36d2ff9b8d8504

5.3 DQL查询方式封装

public <T> List<T> executrQuery(Class<T> clazz, String sql, Object... params) throws Exception {// 获取连接Connection connection = JdbcUtilsV2.getConnection();PreparedStatement preparedStatement = connection.prepareStatement(sql);// 动态值if (params != null && params.length != 0) {for (int i = 1; i <= params.length; i++) {preparedStatement.setObject(i, params[i-1]);}}// 发送sqlResultSet resultSet = preparedStatement.executeQuery();// 结果集解析List<T> list = new ArrayList<>();ResultSetMetaData metaData = resultSet.getMetaData();int columnCount = metaData.getColumnCount();while (resultSet.next()) {T t = clazz.newInstance();  // 调用类的无参构造函数实例化对象for (int i = 1; i <= columnCount; i++) {Object value = resultSet.getObject(i);String propertyName = metaData.getColumnLabel(i);// 反射——给赋值Field field = clazz.getDeclaredField(propertyName);field.setAccessible(true);  // 防止私有的不能赋值field.set(t, value);}list.add(t);}// 关闭资源resultSet.close();preparedStatement.close();if (connection.getAutoCommit()) {JdbcUtilsV2.freeConnection();}return list;
}

问题：

这里应该如何传参？——试了好多，希望学到更多知识的时候能回来解决

总结