JDBC 保姆教程JDBC 原理示意图 JDBC 快速入门注册驱动 - 加载 Driver 类获取连接 - 得到 C

JDBC 保姆教程

JDBC 原理示意图

JDBC 快速入门

注册驱动 - 加载 Driver 类
获取连接 - 得到 Connection
执行增删改查 - 发送 SQL 给 mysql
释放资源

public class Test {
    public static void main(String[] args) throws SQLException, IOException {
        // 前置工作：在项目下创建一个文件夹 比如 libs
        // 将 mysql.jar 放到该目录下，然后右击,接着点击 add to project 加入到该项目
        // 1. 注册驱动
        Driver driver = new Driver();
        // 2. 获取连接
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

        String url = properties.getProperty("url");

        Connection connect = driver.connect(url, properties);
        // 3. 执行SQL
        Statement statement = connect.createStatement();
        String sql = "insert into user values(null, 'lyq', 'dasdsa','laozhang',18,0,'长沙')";

        int i = statement.executeUpdate(sql);
        System.out.println(i);
        // 4. 关闭资源
        statement.close();
        connect.close();
    }
}

数据库连接的几种方式

方式一：静态加载

Driver driver = new Driver();

方式二：反射加载

Class<?> cls = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver =(Driver)cls.getConstructor().newInstance();

方式三：DriverManager

public void test() throws Exception{
    
    // 这句话也可以不写，因为 jdbc 在 service 下面有一个文本文件，自动实现了注册
    Class.forName("com.mysql.cj.jdbc.Driver");

    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

    String url = properties.getProperty("url");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");

    Connection connection = DriverManager.getConnection(url, user, password);
}

JDBC 课堂练习

创建 actor 表
使用 jdbc 添加 5 条记录
修改 id = 1 的记录，将 name 改成自己名字
删除 id = 3 的记录

public void test() throws Exception{
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

    Class.forName("com.mysql.cj.jdbc.Driver");

    String url = properties.getProperty("url");
    String password = properties.getProperty("password");
    String user = properties.getProperty("user");

    Connection connection = DriverManager.getConnection(url, user, password);

    Statement statement = connection.createStatement();

    // String sql = "insert into user values(null, 'liyongqi', 'lyq199992', '劳资', 18, 0, '长沙')";
    // String sql = "delete from user where id = 9";
    // String sql = "update user set username='liyongqi' where id = 5";
    String sql = "select * from user";
    ResultSet resultSet = statement.executeQuery(sql);

    while (resultSet.next()){
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);

        System.out.println(id);
        System.out.println(name);
    }
}

Statement

Statement 对象用于执行静态SQL语句返回其生成的结果对象

在建立连接后，需要对数据库进行访问，执行命令或者SQL语句，可以通过
- Statement 存在SQL注入问题
- PreparedStatement 预处理
- CallableStatement 存储过程
SQL 注入是利用某些系统没有对用户输入的数据进行充分检查，而在用户输入数据中注入非法的SQL语句，恶意攻击数据库
要防范 SQL 注入，只要使用 PreparedStatement (从Statement扩展而来)取代 Statement

PreparedStatement

PreparedStatement 执行的 SQL 语句中的参数用问号（？）来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数
调用 executeQuery()，返回 ResultSet 对象
调用 executeUpdate()，执行更新，包括：增、删、改

PreparedStatement 练习

public void test4() throws Exception{
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

    Class.forName("com.mysql.cj.jdbc.Driver");

    String url = properties.getProperty("url");
    String password = properties.getProperty("password");
    String user = properties.getProperty("user");

    Connection connection = DriverManager.getConnection(url, user, password);

    // String sql = "insert into user values(null, 'liyongqi', 'lyq199992', '劳资', 18, 0, '长沙')";
    // String sql = "delete from user where id = 9";
    String sql = "select * from user where id = ?";

    // String sql = "update user set username='liyongqi' where id = ?";

    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1, 5);

    ResultSet resultSet = preparedStatement.executeQuery();

    while (resultSet.next()){
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);

        System.out.println(id);
        System.out.println(name);
    }
}

Jdbc API

Jdbc 工具类

public class JdbcUtils {

    private static String user;
    private static String password;
    private static String url;
    private static String driver;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\main\resources\jdbc.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");


        } catch (IOException e) {
            // 将编译异常，转成运行时异常
           throw new RuntimeException(e);
        }
    }

    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
        return connection;
    }

    public static void close(Connection connection, Statement preparedStatement, ResultSet set) throws Exception{
        if(connection != null){
            connection.close();
        }

        if(set != null){
            set.close();
        }

        if(preparedStatement != null){
            preparedStatement.close();
        }
    }
}

Jdbc 事务

JDBC 程序中当一个 Connection 对象创建时，默认情况下自动提交事务：每次执行完 SQL 语句时，执行成功，就会自动向数据库自动提交，而不能回滚
JDBC 程序中为了让多个 SQL 语句作为一个整体执行，需要使用事务
调用 Connection 的 setAutoCommit(false)可以取消自动提交事务
在所有的 SQL 语句都成功执行时，调用 commit() 方法提交事务
在其中某个操作失败或出现异常时，调用 rollback() 方法回滚事务

转账案例

public void test5() throws Exception{

    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

    String url = properties.getProperty("url");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");


    Connection connection = DriverManager.getConnection(url, user, password);
    connection.setAutoCommit(false);

   try {

       String sql = "update tb_account set money = money - 100 where id = ? ";
       String sql2 = "update tb_account set money = money + 100 where id = ? ";


       PreparedStatement preparedStatement = connection.prepareStatement(sql);
       preparedStatement.setInt(1, 2);
       preparedStatement.executeUpdate();

       // int i = 1/0;

       preparedStatement = connection.prepareStatement(sql2);
       preparedStatement.setInt(1, 3);
       preparedStatement.executeUpdate();
       connection.commit();
   }catch (Exception ex){
       connection.rollback();
   }
}

Jdbc 批处理

使用批处理执行 sql，相比于执行单条 sql 效率会高很多。

JDBC 的批处理处理语句包括下面方法：
- addBatch()：添加需要批量处理的 sql
- clearBatch() ：清空 sql
- executeBatch() ：执行批处理语句
使用 JDBC 连接 mysql，如果需要使用批处理功能，请在 url 中加入参数 rewriteBatchStatements=true
批处理往往和 PreparedStatement 搭配使用，可以提高执行效率和编译效率

public void test6() throws Exception{
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

    String url = properties.getProperty("url");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");

    Connection connection = DriverManager.getConnection(url, user, password);

    String sql = "insert into user values(?, ?, ?, ? ,? ,? ,?)";

    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    for (int i = 0; i < 5000; i++) {
        preparedStatement.setObject(1, null);
        preparedStatement.setString(2, "张三");
        preparedStatement.setString(3, "123456");
        preparedStatement.setString(4, "laozhang");
        preparedStatement.setInt(5, 10);
        preparedStatement.setInt(6, 0);
        preparedStatement.setString(7, "长沙");
        preparedStatement.addBatch(sql);

        if((i + 1) % 1000 == 0){
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }
    }
}

数据库连接池

预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从缓冲池中取出一个，使用完毕在放回去
数据库连接池负责分配、管理和释放数据库连接，他允许重复使用一个现有的数据库连接，而不是重新建立
当应用程序向连接池请求的连接数超过最大数量，这些请求将会被放入队列中，等有空闲的连接就分配

数据库连接池示意图

数据库连接池种类

C3P0 数据库连接池，速度相对慢，但稳定
DBCP 数据库连接池，速度比C3P0快，不稳定
Proxool 数据库连接池，有监控连接池状态的功能，稳定比C3P0差
BoneCP 数据库连接池，速度快
Druid 数据库连接池，是阿里提供的，集DBCP、Proxool、BoneCP 优点于一身

C3P0 连接池案例

public void testC3P0() throws Exception{
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\jdbc.properties"));

    String url = properties.getProperty("url");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String driver = properties.getProperty("driver");

    comboPooledDataSource.setJdbcUrl(url);
    comboPooledDataSource.setDriverClass(driver);
    comboPooledDataSource.setPassword(password);
    comboPooledDataSource.setUser(user);

    // 设置初始化连接数
    comboPooledDataSource.setInitialPoolSize(10);
    // 设置最大连接数
    comboPooledDataSource.setMaxPoolSize(50);

    Connection connection = comboPooledDataSource.getConnection();

    System.out.println(connection);
}

Druid 连接池案例

测试类

public void testDruid() throws Exception {
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\main\resources\druid.properties"));

    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();

    connection.close();
}

druid.properties 文件

#key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://10.67.181.199/maomao?rewriteBatchedStatements=true
username=application
password=Uts_123456
driver=com.mysql.jdbc.Driver
#initial connection Sizeinitialsize=10
#min idle connecton size
minIdle=5
#tmax active connection size
maxActive=50
maxWait=5000