05-JDBC

✨你好啊，我是“ 罗师傅”，是一名程序猿哦。
🌍主页链接：楚门的世界 - 一个热爱学习和运动的程序猿
☀️博文主更方向为：分享自己的快乐 briup-jp3-ing
❤️一个“不想让我曾没有做好的也成为你的遗憾”的博主。
💪很高兴与你相遇，一起加油！

前言

目标：Mysql数据库的使用及数据库分析及设计实践

JUnit

JUint特点：

• JUnit是一个开放源代码的测试工具
• 提供注解来识别测试方法（反射技术哈）
• JUnit测试可以让你编写代码更快，并能提高质量
• JUnit优雅简洁。没那么复杂，花费时间较少
• JUnit在一个条中显示进度：如果运行良好则是绿色；如果运行失败，则变成 红色

jar包导入

因为JUnit单元测试框架，不是JDK自带的，所以我们需要额外导入JUnit的 jar包

idea导包

基础使用

public class JunitTest {
    public static void main(String[] args) {
        System.out.println("hello junit");
    }
    @Test
    public void test() {
        System.out.println("junit test");
    }
}

注意事项：

• 测试方法必须是公共的无参数无返回值的非静态方法
• 在测试方法上使用@Test注解标注该方法时一个测试方法

相关注解

// 新增代码
@Before
public void before() {
    // 在执行测试代码之前执行，一般用于初始化操作
    System.out.println("before");
}
// 新增代码
@After
public void after() {
    // 在执行测试代码之后执行，一般用于释放资源
    System.out.println("after");
}

JDBC

概述

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

JDBC ，是一种技术规范，它制定了程序中连接操作不同数据库的标准API，使得 程序员可以使用同一套标准的代码，去访问不同数据库。

理解

理解JDBC

• 官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口（JDBC）
• 各个数据库厂商去实现这套接口，提供数据库驱动jar包
• 程序通过接口（JDBC）编写代码，但是真正实行的代码是驱动jar包中的实现类

步骤

使用JDBC操作数据库，一般存在6个步骤，具体如下：

// 1、注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2、获取数据库连接对象
Connection conn = DriverManager.getConnection(url, username, password);
// 3、获取数据库操作对象（Statement类型或者子类PreparedStatement）
Statement stmt = conn.createStatement();
// 4、执行sql语句（包括了Sql定义）
String sql = "update ..."; // DML、DQL、DDL、DCL、TCL
stmt.executeUpdate(sql);

// 5、处理结果集（只有DQL语句才有结果集）
// 下面只是个例子
rs = stmt.executeQuery(sql);
List<TUser> list = new ArrayList<>();
while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");
    TUser tUser = new TUser(id, name, age);
    list.add(tUser);
}
list.forEach(System.out::println);

// 6、释放资源
rs.close;
stmt.close;
conn.close;

导入

导入MySQL的驱动jar包

JDBC-API

底层实现步骤：

• 加载驱动类（加载接口具体的实现类的内存中）
• 将驱动类注册到驱动管理器中（可以自动完成）
• 通过驱动管理器获取数据库连接对象（当前Java程序和Mysql数据库建立连接）

DriveManager

• 注册驱动

//注册驱动
public static synchronized void registerDriver(java.sql.Driver driver);

但是为什么我们还是使用Class.forName(“com.mysql.cj.jdbc.Driver”); 即通过类加载器实现驱动注册呢？

// com.mysql.cj.jdbc.Driver; 实现了 java.sql.Driver;接口
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            // 通过加载com.mysql.cj.jdbc.Driver的时候会调用static静态方法对Driver进行注册
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

注意：java.sql.Driver 是JDBC中提供的驱动接口，每种数据库驱动类都要实现这个接口

• 获取数据库连接对象

public static Connection getConnection(String url, String user, String password) throws SQLException;

Connection

//数据库连接四要素
private String driverClass = "com.mysql.cj.jdbc.Driver";
//驱动
private String url = "jdbc:mysql://127.0.0.1:3306/lwsj";
//数据库地址
private String user = "root"; //用户名
private String password = "root"; //密码
//获取数据库连接并输出连接对象
@Test
public void test_getConnection() {
    Connection conn = null;
    Class.forName(driverClass);
    conn = DriverManager.getConnection(url,user,password);
    System.out.println(conn);
}

• url：数据库连接路径，其后面可以追加连接参数，来设置连接的属性， 常见参数如下：

url=jdbc:mysql://127.0.0.1:3306/jd2311?serverTimezone=UTC&useServerPrepStmts=true

获取执行对象：

• 普通执行SQL对象

Statement createStatement();

• 预编译SQL的执行SQL对象：防止SQL注入（听我细细道来）

PreparedStatement prepareStatement(sql);

Statement

• 执行DDL（成功返回 0 ）、DML语句

//如果执行的是DML语句，则返回修改的行数；如果执行DDL语句，则返回0
int executeUpdate(String sql) throws SQLException;

• 执行DQL语句

//返回结果集对象(包含给定查询产生的数据),或者null
ResultSet executeQuery(String sql) throws SQLException;

DDL

DML

注意：JDBC代码执行的sql语句不需要加分号 ；，但是命令行里面执行sql语句，是要加分号的！

DQL

• ResultSet

结果集ResultSet对象作用： 封装了SQL查询语句的结果。

boolean next() throws SQLException; // 从-1开始，每执行一次光标就往后++
public xxx getXxx(?,?); // 第一个问号是下标从1开始，第二个是数据

rs = stmt.executeQuery(sql);
List<TUser> list = new ArrayList<>();
while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");
    TUser tUser = new TUser(id, name, age);
    list.add(tUser);
}
list.forEach(System.out::println);

SQL注入

SQL注入攻击原理：利用用户输入的数据作为SQL查询语句的一部分，从而改变原始查询的意图。

例子：

# 第一种sql注入，只需要知道账号就可以直接进入了
select * from t_user where name='admin' and password='' or '1'='1'
# 第二种是连账号都不需要知道
select * from t_user where name='' OR '1'='1'; -- and password='随便密码'

// String username = "admin";
// String password = "asdf' or '1'='1";

// String username = "admin'; -- ";
// String password = "随便密码";

PreparedStatement

PreparedStatement 是 Statement 接口的子接口。

与 Statement 不同， PreparedStatement 在执行之前会先将SQL语句发送给数据库进行预编译

PreparedStatement预编译优点：

• 提高执行效率
• 能够防止SQL注入攻击
• 支持各种数据类型和批处理操作

同构异构

• 异构SQL语句

delete from t_user where id=3;
select * from t_user where name='tom'

• 同构SQL语句

同构即结构相同。如果有多条sql语句，它们的格式相同，只是要操作的数据不 同，那么可以称它们为同构sql语句。

insert into t_user(id,name,password,age) values(1,'tom1','tom',21);
insert into t_user(id,name,password,age) values(2,'tom2','tom',22);
insert into t_user(id,name,password,age) values(3,'tom3','tom',23);

-- 问号表示占位符
insert into t_user(id,name,password,age) values(?,?,?,?);
-- 第一次4个问号对应值： 1 tom1 tom 21
-- 第二次4个问号对应值： 2 tom2 tom 22
-- 第三次4个问号对应值： 3 tom3 tom 23
// 使用pstmt对象给?赋不同数据值
pstmt.setInt(id);
pstmt.setString(name);
pstmt.setString(password);
pstmt.setInt(age);

注意：执行大量同构SQL语句的情况下，使用PreparedStatement会大大提高效率。

原理分析

JDBC使用Statement对象执行SQL语句流程（非预编译）：

• 将SQL语句（含结构+数据）发送到MySQL服务端
• MySQL服务端检查SQL查询 （检查语法是否正确）
• MySQL服务端编译SQL语句（将SQL语句编译成可执行的函数）
• 执行SQL语句

注意：检查和编译SQL需要花费大量时间，甚至比执行SQL时间还要长

PreparedStatement（预编译）：

1. 将SQL语句（含结构）发送到MySQL服务器端
2. MySQL服务端检查SQL语句
3. MySQL服务端编译SQL语句（编译成功，可重复使用）
4. PreparedStatement对象传递参数值到服务器
5. 执行SQL语句
6. 如果再次执行SQL语句，则到第4步，再次传递参数值到服务器，然后直接 执行即可。

结论：预编译省去了后续执行SQL的检查和编译过程，大大提高了性能

相关API

• 获取 PreparedStatement 对象

// SQL语句中的参数值，使用？占位符替代
String sql = "select count(*) from t_user where name = ? and password = ?";
// 通过Connection对象获取，并传入对应的sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);

• 设置参数值并执行SQL语句

void setString(int parameterIndex, String x) throws SQLException;
void setInt(int parameterIndex, int x) throws SQLException;
// 省略setXXXX...

// 执行DDL语句和DML语句
ResultSet executeQuery() throws SQLException;
// 执行DQL语句
int executeUpdate() throws SQLException;

注意：调用上述2个执行方法时不需要传递SQL语句，因为获取SQL语句执行对象 时已经对SQL语句进行预编译了。

Batch

批处理（Batch）：一次操作中执行多条SQL语句，相比于一次执行一条SQL语 句，效率会提高很多。

public interface Statement extends Wrapper, AutoCloseable {
    // 向Batch中加入SQL语句
    void addBatch( String sql ) throws SQLException;
    // 执行Batch中的SQL语句,返回数组，元素对应每条sql语句的执行结果
    int[] executeBatch() throws SQLException;
    // 省略...
}

//把要执行的sql语句，添加到批处理中
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.addBatch(sql4);
stmt.addBatch(sql5);
stmt.addBatch(sql6);
//执行批处理中的sql语句，返回每一个语句的结果
int[] rows = stmt.executeBatch();

Transaction

默认情况下，在JDBC中执行的DML语句，所产生的事务，都是自动提交的。也 就是说，每执行一次DML语句，所产生的事务，就会自动提交。

//1.加载注册驱动
Class.forName(driverClass);
//2.获取连接对象
conn = DriverManager.getConnection(url,user,password);
//设置事务自动提交为false，也就是需要手动提交！！！！！！！！！！！
conn.setAutoCommit(false);
//3.获取Statement对象
String sql = "update t_user set name=? where id = ?";
pstmt = conn.prepareStatement(sql);
//4.执行SQL语句
pstmt.setString(1,"lucy");
pstmt.setInt(2,1);
int rows = pstmt.executeUpdate(sql);
//手动提交事务！！！！！！！！！！！！！！！
conn.commit();
conn.close();

• 注意1，代码中当前连接中的事务设置为了手动提交
• 注意2，代码中操作完成提交事务，出现异常则回滚事务
• 注意3，finally语句中的 conn.close() 代码，也可以提交事务

另外，如果有需要，也可以在程序中设置回滚点：

//DML
Savepoint p1 = conn.setSavepoint("p1");
//DML
Savepoint p2 = conn.setSavepoint("p2");
//DML
Savepoint p3 = conn.setSavepoint("p3");
//DML
Savepoint p4 = conn.setSavepoint("p4");
conn.rollback(p2);

注意：Mysql中要主动开启预编译功能

//在代码中编写url时需要加上以下参数
useServerPrepStmts=true
// 即
url=jdbc:mysql://127.0.0.1:3306/jd2311?serverTimezone=UTC&useServerPrepStmts=true

连接池

概述

池(Pool)技术在一定程度上可以明显优化服务器应用程序的性能，提高程序执行效率和降低系统资源开销。

数据库连接池：在系统初始化时创建一定数量的数据库连接对象，需要时直接从池中取出一个空闲对象，用完后并不直接释放掉对象，而是再放入到对象池中，以便下一次对象请求可以直接复用。消除了对象创建和销毁所带来的延迟，从而提高系统的性能。

优点：

• 资源复用
  • 由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销；
• 更快的系统响应速度
  • 对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化 和释放过程的时间，从而缩减了系统整体响应时间。
• 统一的连接管理，避免数据库连接泄漏
  • 根据预先的连接占用超时设定，强制收回被占用连接，从而避免了常规数据 库连接操作中可能出现的资源泄漏。

Druid

Druid（德鲁伊）

• Druid连接池是阿里巴巴开源的数据库连接池项目
• 功能强大，性能优秀，是Java语言最好的数据库连接池之一

官方：Druid连接池介绍 · alibaba/druid Wiki (github.com)

使用

javax.sql.DataSource 是Java中定义的一个数据源标准的接口，通过它获取 到的数据库连接对象，如果调用 close() 方法，不会再关闭连接，而是归还连 接到连接池中。

• 第一步：导jar包

• 第二部：配置文件 (druid.properties)

#MySQL的驱动，注意版本区别
driverClassName=com.mysql.cj.jdbc.Driver
#本地数据库的url：只有jd2311库名 按照自己的数据库名称来书写，其他的是一致的
url=jdbc:mysql://127.0.0.1:3306/jd2311?serverTimezone=UTC&useServerPrepStmts=true
#自己的数据库用户名
username=lwsj
#自己的数据库密码
password=lwsj
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

注意：位置一定是在src文件夹下面

这里我就要解释一下idea的路径问题了：

• 使用File(url)这种的相对路径

• 使用 类名.class.getClassLoader().getResourceAsStream(url) 的相对路径

封装

这个有点难度哈！看看就行

package com.briup.jdbc.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @ClassName JDBCUtil
 * @Author luozongwei
 * @Date 2023/9/12 16:13
 * @Version 1.0
 * @Description
 */
public class JDBCUtils {
    // 维护一个数据源
    private static DataSource dataSource;
    // 配置文件初始化
    public static final String CLASS_NAME;
    public static final String URL;
    public static final String USERNAME;
    public static final String PASSWORD;
    // 定义分类中用到的三个变量
    private static Connection conn;
    private static Statement stmt;
    private static ResultSet rs;

    static {
        Properties properties = new Properties();
        InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        // 记得final修饰的变量不能放在try里边，因为异常可能导致类加载的顺序有所不同
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }

        CLASS_NAME = properties.getProperty("driverClassName");
        URL = properties.getProperty("url");
        USERNAME = properties.getProperty("username");
        PASSWORD = properties.getProperty("password");

        // 也可用通过properties创建数据源
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /** 
     * 从配置文件中加载数据库连接信息
     *
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Properties p = loadProperties();
        String driverClassName = p.getProperty("driverClassName");
        String url = p.getProperty("url");
        String username = p.getProperty("username");
        String password = p.getProperty("password");
        Class.forName(driverClassName);
        return DriverManager.getConnection(url, username, password);
    }


    /**
     * 加载properties文件
     *
     * @return
     */
    private static Properties loadProperties() {
        Properties p = new Properties();
        try (InputStream in = new FileInputStream("resources/jdbc.properties")) {
            // try (InputStream in = new FileInputStream("jdbc.properties")) {
            p.load(in);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return p;
    }


    /**
     * 执行SQL语句
     *
     * @param conn   数据库连接
     * @param sql    sql语句
     * @param params 参数（如果有）
     * @return 如果是DQL操作，返回结果集；如果是DML，返回操作行数
     */
    public static Object execute(Connection conn, String sql, Object... params) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }
        if (sql.trim().toLowerCase().startsWith("select")) {
            return pstmt.executeQuery();
        } else {
            return pstmt.executeUpdate();
        }
    }

    // 1、不使用连接池获得连接对象
    public static Connection getCommonConnection() throws ClassNotFoundException, SQLException {
        Class.forName(JDBCUtils.CLASS_NAME);
        conn = DriverManager.getConnection(JDBCUtils.URL, JDBCUtils.USERNAME, JDBCUtils.PASSWORD);
        return conn;
    }

    // 2、使用连接池获得连接对象
    public static Connection getDruidConnection() throws SQLException {
        conn = dataSource.getConnection();
        return conn;
    }

    // 3、执行DDL语句的方法（stmt）
    public static int executeDDL(String sql) throws SQLException {
        stmt = getDruidConnection().createStatement();
        return stmt.executeUpdate(sql);
    }

    // 4、执行DML语句的方法（stmt）
    public static int executeDML(String sql) throws SQLException {
        return executeDDL(sql);
    }

    // 5、执行DQL语句的方法（stmt）
    public static ResultSet executeDQL(String sql) throws SQLException {
        stmt = getDruidConnection().createStatement();
        rs = stmt.executeQuery(sql);
        return rs;
    }

    public static void close() {
        close(conn, stmt, rs);
    }

    public static void close(Connection conn, Statement stmt) {
        close(conn, stmt, null);
    }

    // 6、关闭方法
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

目前感觉最优雅的一段代码！！ 与大家一同欣赏

/**
* 执行SQL语句
*
* @param conn   数据库连接
* @param sql    sql语句
* @param params 参数（如果有）
* @return 如果是DQL操作，返回结果集；如果是DML，返回操作行数
*/
public static Object execute(Connection conn, String sql, Object... params) throws SQLException {
    PreparedStatement pstmt = conn.prepareStatement(sql);
    for (int i = 0; i < params.length; i++) {
        pstmt.setObject(i + 1, params[i]);
    }
    if (sql.trim().toLowerCase().startsWith("select")) {
        return pstmt.executeQuery();
    } else {
        return pstmt.executeUpdate();
    }
}

❤️❤️❤️忙碌的敲代码也不要忘了浪漫鸭！

大鹏一日同风起，扶摇直上九万里。💪