JaveEE-JDBC

JDBC概述

引入连接MySql的jar包

1. 前往https://mvnrepository.com/搜索对应版本jar包进行下载。

2. 在工程项目下创建一个lib文件夹用于存放jar包，右键lib文件将将其添加为库。

JDBC基本使用步骤

1. 注册驱动
2. 获取链接
3. 创建发送sql语句对象
4. 发送sql语句，并获取返回结果
5. 结果集解析
6. 资源关闭

具体操作

package com.geo.api.statement;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

/**
 * @author yuxiaohong
 * @package com.geo.api.statement
 * @date 2023/12/3 17:07
 * @description
 */
public class StatementQueryPart {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动，驱动版本8+的要导com.mysql.cj.jdbc.Driver这个包，版本低于8的选择不带cj的
        DriverManager.registerDriver(new Driver());
        //2.获取链接
        /*
        * 参数一：url：jdbc:数据库厂商名://ip地址:port/数据库名
        * 参数二：user：数据库账号
        * 参数三：password：数据库密码
        * */
        //java.sql 接口=实现类
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jd", "root", "123456");
        //3.创建statement
        Statement statement = connection.createStatement();
        //4.发送sql语句，并且获取返回结果
        String sql = "select * from t_user;";
        ResultSet resultSet = statement.executeQuery(sql);
        //5.进行结果集解析
        //先看看下一行有没有数据，有就继续获取
        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+"-"+account+"-"+password+"-"+nickname);
        }
        //6.关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }

}

注册驱动存在的问题：

1. DriverManager.registerDriver()方法本身会注册一次。
2. Driver.static{}静态代码块中也会注册一次。
   这就导致了不必要的性能消耗。
   解决方案：使用反射，可以更加灵活的通过传进来的字符串来更改数据库的配置文件
   Class.forName(“com.mysql.cj.jdbc.Driver”);

SQL分类：

DDL(容器创建、修改、删除)、DML(插入、修改、删除)、DQL(查询)、DCL(权限控制)、TCL(事物控制语句)

1. 参数：sql语句：非DQL
   返回：int型
   情况1：DML返回影响的行数。比如删除了三行，返回3；插入了两行，返回2；修改了0条，返回0。
   情况2：非DML return 0。
   int row =executeUpdate(sql)

2. 参数：DQL
   返回：resultSet结果封装对象
   ResultSet resultSet = statement.executeQuery(sql);

基于statement实现模拟登录中存在的问题

1. SQL语句需要进行字符串拼接，比较麻烦。
2. 只能拼接字符串类型，其他数据库类型无法处理。
3. 可能发生注入攻击：动态值充当了SQL语句结构，影响了原有的查询结果：比如在输入密码的时候输入’ or ‘1’ = ‘1，就会登录成功。

使用preparedstatement进行登录模拟（防止注入攻击）

package com.geo.api.preparedstatement;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.Scanner;

/**
 * @author yuxiaohong
 * @package com.geo.api.preparedstatement
 * @date 2023/12/18 10:28
 * @description 使用预编译的statement完成用户登录，防止注入攻击，演示preparedstatement的使用
 */
public class PreparedstatementUserLogin {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String password = "";
        String account = "";
        // 1.收集用户信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入账号：");
        account = scanner.next();
        System.out.println("请输入密码：");
        password = scanner.next();

        // 2.ps的数据库流程
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jd", "root", "123456");
        // 3.编写SQL语句结果
        String sql = "select * from t_user where account = ? and password = ? ; ";
        // 4.创建预编译的statement结构并且设置sql语句结果。
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 5.单独的占位符进行赋值
        /*
         * 参数1：index占位符的位置，从左向右数，从1开始
         * 参数2：占位符的值，可以设置任意类型的数值
         * */
        preparedStatement.setObject(1, account);
        preparedStatement.setObject(2, password);

        // 6.发送sql语句，并接受返回结果
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        // 7.关闭流资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
        scanner.close();
    }
}

动态实现增删改查操作

插入数据

public void test1() throws ClassNotFoundException, SQLException {
    String account = "";
    String password = "";
    String nickname = "";
    Scanner scanner = new Scanner(System.in);
    System.out.println("请输入要添加的账号：");
    account = scanner.next();
    System.out.println("请输入密码：");
    password = scanner.next();
    System.out.println("请输入昵称：");
    nickname = scanner.next();

    // 1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.建立链接
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd", "root", "123456");
    // 3.编写sql语句
    String sql = "insert into t_user(account,password,nickname) values(?,?,?);";
    // 4.创建preparedStatement，并且传入sql语句结果
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 5.占位符赋值
    preparedStatement.setObject(1, account);
    preparedStatement.setObject(2, password);
    preparedStatement.setObject(3, nickname);
    // 6.发送SQL语句
    int i = preparedStatement.executeUpdate();
    // 7.输出结果
    if (i != 0) {
        System.out.println("添加成功！");
    }
    // 8.关闭资源
    preparedStatement.close();
    connection.close();
    scanner.close();

修改数据

public void test2() throws ClassNotFoundException, SQLException {
    // 1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.建立链接
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd", "root", "123456");
    // 3.编写sql语句
    String sql = "update t_user set nickname = ? where id = ?;";
    // 4.创建preparedStatement，并且传入sql语句结果
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 5.占位符赋值
    preparedStatement.setObject(1, "db");
    preparedStatement.setObject(2, 3);
    // 6.发送SQL语句
    int i = preparedStatement.executeUpdate();
    // 7.输出结果
    if (i != 0) {
        System.out.println("修改成功！");
    }
    // 8.关闭资源
    preparedStatement.close();
    connection.close();
}

删除数据

public void test3() throws ClassNotFoundException, SQLException {
    // 1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.建立链接
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd", "root", "123456");
    // 3.编写sql语句
    String sql = "delete from t_user where id = ?;";
    // 4.创建preparedStatement，并且传入sql语句结果
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 5.占位符赋值
    preparedStatement.setObject(1, 3);
    // 6.发送SQL语句
    int i = preparedStatement.executeUpdate();
    // 7.输出结果
    if (i != 0) {
        System.out.println("删除成功！");
    }
    // 8.关闭资源
    preparedStatement.close();
    connection.close();
}

查询数据

public void test4() throws ClassNotFoundException, SQLException {
        List<Map> list = new ArrayList<>();
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.建立链接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd", "root", "123456");
        // 3.编写sql语句
        String sql = "select * from t_user;";
        // 4.创建preparedStatement，并且传入sql语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 6.发送SQL语句
        ResultSet resultSet = preparedStatement.executeQuery();
        // 获取列的信息对象
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 获取列的数量，可以水平遍历列
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            Map map = new HashMap();
            // 纯手动取值
            // map.put("id", resultSet.getInt("id"));
            // map.put("account", resultSet.getString("account"));
            // map.put("password", resultSet.getString("password"));
            // map.put("nickname", resultSet.getString("nickname"));


//重要************************************************************************************** */
            // 自动遍历列
            for (int i = 1; i <= columnCount; i++) {
                // 获取指定列下角标的值
                Object value = resultSet.getObject(i);
                // 获取指定下角标的列的名称
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel, value);
            }
            list.add(map);
        }
        // 7.输出结果
        System.out.println(list);
        // 8.关闭资源
        preparedStatement.close();
        connection.close();
    }

JDBC扩展提升

自增长主键回显的实现

1. t_user插入一条数据，并且获取数据库自增长的主键
2. 创建preparestatement的时候，告知携带回数据库自增长的主键(sql,Statement.RETURN_GENERATED_KEYS)

public class PSOtherPart {
    @Test
    public void test1() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///jd", "root", "123456");
        String sql = "insert into t_user(account,password,nickname) values (?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setObject(1, "66667");
        preparedStatement.setObject(2, "13511");
        preparedStatement.setObject(3, "haha");
        int i = preparedStatement.executeUpdate();
        if (i != 0) {
            System.out.println("执行完毕");
            // 此处可以主键回显
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            generatedKeys.next();
            int id = generatedKeys.getInt(1);
            System.out.println("主键是：" + id);
        }

        preparedStatement.close();
        connection.close();
    }

批量数据插入性能提升

正常插入1万条数据所需要的时间

public void test2() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///jd", "root", "123456");
    String sql = "insert into t_user(account,password,nickname) values (?,?,?);";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        preparedStatement.setObject(1, "66667" + i);
        preparedStatement.setObject(2, "13511" + i);
        preparedStatement.setObject(3, "haha" + i);
        preparedStatement.executeUpdate();
    }
    long end = System.currentTimeMillis();
    System.out.println(end - start);  // 31487
    preparedStatement.close();
    connection.close();
}

使用分批次插入1万条数据所用的时间

public void test3() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///jd?rewriteBatchedStatements=true", "root", "123456");
    String sql = "insert into t_user(account,password,nickname) values (?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        preparedStatement.setObject(1, "xxx" + i);
        preparedStatement.setObject(2, "111" + i);
        preparedStatement.setObject(3, "haha" + i);
        preparedStatement.addBatch();  // 不执行，追加到values后边
    }
    preparedStatement.executeBatch();  // 所有遍历完之后再一起添加
    long end = System.currentTimeMillis();
    System.out.println(end - start);  // 425，说明性能提升了非常多
    preparedStatement.close();
    connection.close();
}

1. 可以看到，在本机上面采用原始的插入方法，要花费将近半分钟的时间，但是使用分批次插入则在不到半秒的时间内就完成了，可见其性能提升幅度之大。
2. 操作要点：
   1. 在路径参数中添加rewriteBatchedStatements=true
   2. sql语句中必须使用values，不能使用value，且后边不可以加分号结束
   3. 不是执行语句，是批量添加addBatch()
   4. 都添加完毕之后，同一批量执行executeBatch()

Druid连接池技术的使用

1. Druid连接池的使用是为了降低性能消耗
2. 前往Maven下载Druid的jar包，拖进lib文件夹中
3. 硬编码和软编码两种使用方式，一般使用软编码，更加灵活。

硬编码方式

public void test1() throws SQLException {
    // 1.创建连接池对象
    DruidDataSource druidDataSource = new DruidDataSource();
    // 2.设置参数：必须：连接数据库驱动类的全限定符[注册驱动]/url/user/password；非必须：初始化连接数量、最大连接数量
    druidDataSource.setUrl("jdbc:mysql://localhost:3306/jd");
    druidDataSource.setUsername("root");
    druidDataSource.setPassword("123456");
    druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    // 非必须：
    druidDataSource.setInitialSize(5);  // 初始化连接数量
    druidDataSource.setMaxActive(10);  // 最大连接数量

    // 获取连接
    DruidPooledConnection connection = druidDataSource.getConnection();

    // 数据库curd

    // 回收连接
    connection.close();
}

软编码方式

public void test2() throws Exception {
    // 1.读取外部properties配置文件
    Properties properties = new Properties();
    // src下的文件可以使用类加载器提供的方法
    InputStream resourceAsStream = DruidUsePart.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(resourceAsStream);
    // 2.使用连接池的工具类的工程模式。创建连接池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();

    // 数据库CURD

    // 回收连接
    connection.close();
}

properties文件

driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql:///jd

全新JDBC使用优化以及工具类的封装

获取连接工具类v1

将重复的步骤封装起来，只将需要的方法暴露出去。

package com.geo.api.jdbcutils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author yuxiaohong
 * @package com.geo.api.jdbcutils
 * @date 2023/12/21 8:44
 * @description JDBC工具类
 */
public class JdbcUtils {
    private static DataSource dataSource = null;

    
    //使用静态方法是为了方便不用造类的实例就可以直接调用
    static {
        Properties properties = new Properties();
        InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 提供对外连接的方法
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    public static void closeConnection(Connection connection) throws SQLException {
        connection.close();
    }
}

获取连接工具类v2

因为在数据的事务操作时，Service和Dao方法属于一个事务；利用线程的本地变量，存储连接信息，就可以不用再进行传递参数了。

package com.geo.api.jdbcutils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author yuxiaohong
 * @package com.geo.api.jdbcutils
 * @date 2023/12/21 8:44
 * @description JDBC工具类
 * 利用线程的本地变量，存储连接信息，确保一个线程的多个方法可以获取同一个连接。
 * 优势：在做事务操作时，Service和Dao方法属于一个事务，就不用再传递参数了。
 */
public class JdbcUtilsV2 {
    private static DataSource dataSource = null;

    private static ThreadLocal<Connection> tl = new ThreadLocal<>();

    static {
        Properties properties = new Properties();
        InputStream resourceAsStream = JdbcUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 提供对外连接的方法
    public static Connection getConnection() throws SQLException {
        // 判断本地变量是否存在
        Connection connection = tl.get();
        if (connection == null) {
            // 线程本地变量没有，连接池获取
            connection = dataSource.getConnection();
            tl.set(connection);

        }
        return connection;
    }


    public static void closeConnection() throws SQLException {
        Connection connection = tl.get();
        if (connection != null) {
            tl.remove();  // 清空线程本地变量数据
            connection.setAutoCommit(true);  // 事务状态回顾 false
            connection.close();  // 回收到连接池
        }
        connection.close();
    }
}

baseDao概念和非DQL方法封装

上述是将创建连接和关闭连接的方式封装了，本节将中间的操作部分也进行封装，减少代码的重复使用。

public int executeUpdate(String sql, Object... params) throws SQLException {

    Connection connection = JdbcUtilsV2.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 5.占位符赋值
    for (int i = 1; i < params.length; i++) {
        preparedStatement.setObject(1, params[i - 1]);
    }
    // 6.发送SQL语句
    int rows = preparedStatement.executeUpdate();
    // 7.输出结果
    if (rows != 0) {
        System.out.println("修改成功！");
    }
    if (connection.getAutoCommit()) {
        // 没有开启事务，正常回收即可
        JdbcUtilsV2.closeConnection();
    }
    return rows;
}

DQL查询方式封装

public <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
    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]);
        }
    }
    ResultSet 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 object = resultSet.getObject(i);
            // 获取指定下角标列的名称
            String columnLabel = metaData.getColumnLabel(i);

            // 反射，给对象的属性赋值
            Field field = clazz.getDeclaredField(columnLabel);
            // 权限可能是私有的，更改权限
            field.setAccessible(true);
            field.set(t, object);
        }
        list.add(t);

    }
    resultSet.close();
    preparedStatement.close();
    if (connection.getAutoCommit()) {
        JdbcUtilsV2.closeConnection();
    }
    return list;

}