JDBC 概述

基本介绍

1. JDBC为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题。
2. Java程序员使用JDBC，可以连接任何提供了JDBC驱动的数据库系统，从而完成对数据库的各种操作
3. JDBC的原理

原理示意图

代码示例

JdbcInterface 模拟Java公司提供给其它数据库厂商的接口，供给调用

package com.jdbc;

/**
 * @InterfaceName: JdbcInterface
 * @Description: Java公司提供给其它数据库厂商的接口，供给调用
 * @Author: wty
 * @Date: 2022/11/6
 */
public interface JdbcInterface {
    /**
     * 连接数据库
     *
     * @return java.lang.Object
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 11:59
     * @Author wty
     **/
    public Object getConnection();

    /**
     * 进行CRUD
     *
     * @return void
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 11:59
     * @Author wty
     **/
    public void crud();


    /**
     * 关闭连接
     *
     * @return void
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 11:59
     * @Author wty
     **/
    public void close();
}

TestJdbc 模拟一个类来实现数据库的调用和相关操作

package com.jdbc;

/**
 * 模拟一个类来实现数据库的调用和相关操作
 *
 * @author wty
 * @date 2022/11/6 12:02
 */
public class TestJdbc {
    public static void main(String[] args) {
        // 完成对mysql的操作
        JdbcInterface jdbcInterface = new MysqlJdbcimpl();
        // jdbcInterface = new OracleJdbcimpl();
        // 获取连接 : 动态绑定
        jdbcInterface.getConnection();
        // 增删改查
        jdbcInterface.crud();
        // 关闭连接
        jdbcInterface.close();
    }
}

MysqlJdbcimpl 模拟Mysql厂商使用Java

package com.jdbc;

/**
 * 模拟Mysql厂商使用Java
 *
 * @author wty
 * @date 2022/11/6 12:00
 */
public class MysqlJdbcimpl implements JdbcInterface {
    @Override
    public Object getConnection() {
        System.out.println("Mysql数据库连接到Java");
        return null;
    }

    @Override
    public void crud() {
        System.out.println("Mysql增删改查");
    }

    @Override
    public void close() {
        System.out.println("Mysql关闭连接");
    }
}

OracleJdbcimpl 模拟Oracle厂商使用Java

package com.jdbc;

/**
 * 模拟Oracle厂商使用Java
 *
 * @author wty
 * @date 2022/11/6 12:05
 */
public class OracleJdbcimpl implements JdbcInterface {
    @Override
    public Object getConnection() {
        System.out.println("Oracle数据库连接到Java");
        return null;
    }

    @Override
    public void crud() {
        System.out.println("Oracle增删改查");
    }

    @Override
    public void close() {
        System.out.println("Oracle关闭连接");

    }
}

JDBC带来的好处

JDBC API

JDBC入门

JDBC程序编写步骤

1. 注册驱动 - 加载Driver类
2. 获取连接 - 得到Connection
3. 执行增删改查 - 发送SQL给mysql执行
4. 释放资源 - 关闭相关连接

JDBC的第一个程序

package com.jdbc.myjdbc;


import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author wty
 * @date 2022/11/6 14:48
 */
public class Jdbc01 {
    public static void main(String[] args) throws SQLException {
        // 前置工作，在项目下创建文件夹，把mysql.jar拷贝，并点击 Add as Library
        // 1.注册驱动 创建Driver对象
        Driver driver = new Driver();

        // 2.获取连接
        // (1)jdbc 协议
        // (2)localhost 表示主机或者IP地址
        // (3)3306表示mysql监听的端口
        // (4)db_02 表示哪个数据库
        // (5)mysql的连接的本质是socket连接
        String url = "jdbc:mysql://localhost:3306/db_02";
        // (6)将用户名和密码放入到Properties
        Properties properties = new Properties();
        // 用户名
        properties.setProperty("user", "root");
        // 密码
        properties.setProperty("password", "hsp");

        Connection connect = driver.connect(url, properties);


        // 3. 执行增删改查
        String sqlStr = "insert into actor values(null,'jack','男','1996-01-13','13713456612')";
        // 发送和执行sql语句
        Statement statement = connect.createStatement();
        // DML语句:受影响的行数
        int rows = statement.executeUpdate(sqlStr);

        System.out.println(rows > 0 ? "成功" : "失败");

        // 4. 释放资源
        statement.close();
        connect.close();
    }
}

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

方式1

    /**
     * 方式1
     *
     * @return void
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 15:36
     * @Author wty
     **/
    @Test
    public void connection1() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/db_02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "hsp");

        Driver driver = new Driver();
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
        // 输出: com.mysql.jdbc.JDBC4Connection@2d363fb3

        connect.close();
    }

方式2

 /**
     * 使用反射加载Driver类
     *
     * @return void
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 15:41
     * @Author wty
     **/
    @Test
    public void connection2() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        // 动态加载更加灵活减少依赖:运行时加载需要的类，如果运行时不用该类，即使不存在该类，也不报错，降低了依赖。
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Object o = aClass.newInstance();

        // 向下转型
        Driver driver = (Driver) o;

        // 剩下步骤一样
        String url = "jdbc:mysql://localhost:3306/db_02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "hsp");

        Connection connect2 = driver.connect(url, properties);
        System.out.println(connect2);
        // 输出: com.mysql.jdbc.JDBC4Connection@2d363fb3
        connect2.close();
    }

方式3

    /***
     * 使用DriverManager替换Driver
     *
     * @Param No such property: code for class: Script1
     * @return void
     * @Date 2022/11/6 15:53
     * @Author wty
     **/
    @Test
    public void connection3() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        // 直接向下转型
        Driver driver = (Driver) aClass.newInstance();

        // 创建url 和 user 和 password
        String url = "jdbc:mysql://localhost:3306/db_02";
        String user = "root";
        String password = "hsp";

        // 注册Driver驱动,用DriverManager统一管理
        DriverManager.registerDriver(driver);
        Connection connection3 = DriverManager.getConnection(url, user, password);

        System.out.println(connection3);
        // com.mysql.jdbc.JDBC4Connection@7d6f77cc
        connection3.close();
    }

方式4

    /**
     * 推荐使用!!!!
     * 使用Class.forName自动完成注册驱动
     *
     * @return void
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 16:04
     * @Author wty
     **/
    @Test
    public void connection4() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        // 使用反射加载Driver类
        // 加载Driver类的时候会自动注册
        Class.forName("com.mysql.jdbc.Driver");
        // 上面这句话也可以去掉，因为类java.sql.Driver中已经加载
        /**
         * 源码分析
         *
         * public class Driver extends NonRegisteringDriver implements java.sql.Driver {
         *     public Driver() throws SQLException {
         *     }
         *     静态代码块:类加载会执行一次
         *     static {
         *         try {
         *             // 这里已经注册了
         *             DriverManager.registerDriver(new Driver());
         *         } catch (SQLException var1) {
         *             throw new RuntimeException("Can't register driver!");
         *         }
         *     }
         * }
         */

        // 创建url 和 Properties
        String url = "jdbc:mysql://localhost:3306/db_02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "hsp");

        Connection connection4 = DriverManager.getConnection(url, properties);

        System.out.println(connection4);
        // com.mysql.jdbc.JDBC4Connection@2d363fb3
        connection4.close();

    }

方式5

配置文件

user=root
password=hsp
url=jdbc:mysql://localhost:3306/db_02
driver=com.mysql.jdbc.Driver

连接数据库

    /**
     * 使用配置文件连接数据库
     *
     * @return void
     * @Param No such property: code for class: Script1
     * @Date 2022/11/6 16:14
     * @Author wty
     **/
    @Test
    public void connection5() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);

        Connection connection5 = DriverManager.getConnection(url, user, password);
        System.out.println(connection5);
        // com.mysql.jdbc.JDBC4Connection@2d363fb3
        connection5.close();
    }

课后作业

加载类

package com.jdbc.HomeWork;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author wty
 * @date 2022/11/6 16:36
 */
public class HomeWork01 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/com/jdbc/HomeWork/res.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");

        Class.forName(driver);

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

//        String sqlStr = "insert into actor values" +
//                "(2,'tom','男','1996-01-01','13779669189')," +
//                "(3,'tony','男','1997-02-01','17779669189')," +
//                "(4,'Anna','女','1998-08-01','13179669189'),(5,'Linda','女','1999-09-01','13279669189');";
        // 修改id = 1的记录为我的名字
        //String sqlStr = "update actor set name = 'hsp' where id = 1;";

        // 删除id = 3的记录
        String sqlStr = "delete from actor where id = 3;";

        int rows = statement.executeUpdate(sqlStr);
        System.out.println(rows > 0 ? "成功" : "失败");

        statement.close();
        connection.close();
    }
}

配置文件

url=jdbc:mysql://localhost:3306/db_02
user=root
password=hsp
driver=com.mysql.jdbc.Driver

ResultSet[结果集]

基本介绍

应用实例

package com.jdbc.resultset;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * 演示select 语句并返回ResultSet,并取出结果
 *
 * @author wty
 * @date 2022/11/6 16:58
 */
public class ResultSetExercise {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        // 1. 注册驱动
        Class.forName(driver);

        // 2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        // 3.得到Statement
        Statement statement = connection.createStatement();
        // 4. 组织sql
        String sqlStr = "select id,name,sex,borndate,phone from actor;";

        // 5.返回结果集(类似于一张表),并用while取出
        ResultSet resultSet = statement.executeQuery(sqlStr);
        /**
         * +----+-------+-----+---------------------+-------------+
         * | id | name  | sex | borndate            | phone       |
         * +----+-------+-----+---------------------+-------------+
         * |  1 | hsp   | 男  | 1996-01-13 00:00:00 | 13713456612 |
         * |  2 | tom   | 男  | 1996-01-01 00:00:00 | 13779669189 |
         * |  4 | Anna  | 女  | 1998-08-01 00:00:00 | 13179669189 |
         * |  5 | Linda | 女  | 1999-09-01 00:00:00 | 13279669189 |
         * +----+-------+-----+---------------------+-------------+
         * 4 rows in set (0.00 sec)
         */
        // resultSet.next()让光标向后移动，如果后面没有了，就是false
        while (resultSet.next()) {
            // 获取第一列数据
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date borndate = resultSet.getDate(4);
            String phone = resultSet.getString(5);
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone + "\t");
        }


        // 6.关闭连接
        resultSet.close();
        statement.close();
        // com.mysql.jdbc.JDBC4Connection@2d363fb3
        connection.close();
    }
}

Statement

基本介绍

SQL注入小案例(sqlyog操作)

CREATE TABLE admin (
  -- 管理员表
  NAME VARCHAR (32) NOT NULL UNIQUE,
  pwd VARCHAR (32) NOT NULL DEFAULT ''
) CHARACTER SET utf8 ;

INSERT INTO admin
VALUES
('tom','123');

SELECT * FROM admin a
WHERE a.`NAME` = 'tom'
AND a.`pwd` = '123';

-- SQL注入
-- 用户名 1' or
-- 密码 or '1' = '1
SELECT * FROM admin a
WHERE a.`NAME` = '1' OR'
AND a.`pwd` = 'OR '1' = '1';

SQL注入小案例(IDEA演示)

package com.jdbc.statement;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * 演示sql注入
 *
 * @author wty
 * @date 2022/11/6 19:15
 */
public class StatementExercise {
    @Test
    public void statementExerciseTest() throws IOException, ClassNotFoundException, SQLException {

        System.out.println("请输入用户名");
        Scanner scanner = new Scanner(System.in);
        // nextLine允许单引号和空格
        String inUser = scanner.nextLine();
        System.out.println("请输入密码");
        String inPwd = scanner.nextLine();


        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        // 1. 注册驱动
        Class.forName(driver);

        // 2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        // 3.得到Statement
        Statement statement = connection.createStatement();

        // 4.查询
        String sqlStr = "select name,pwd from admin where name = '" + inUser + "' and pwd = '" + inPwd + "'";

        ResultSet resultSet = statement.executeQuery(sqlStr);

        if (resultSet.next()) {
            // 如果查询到记录，说明用户存在
            System.out.println("恭喜登录成功!");
        } else {
            System.out.println("登录失败，没有该用户!");
        }


        // 关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

PreparedStatement

预处理的好处

查询query

package com.jdbc.preparedstatement;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * PreparedStatement演示控制sql注入
 *
 * @author wty
 * @date 2022/11/6 19:35
 */
public class PrepareperedStatementExercise {
    @Test
    public void prepareperedStatementExerciseTest() throws IOException, ClassNotFoundException, SQLException {
        System.out.println("请输入用户名");
        Scanner scanner = new Scanner(System.in);
        // nextLine允许单引号和空格
        String inUser = scanner.nextLine();
        System.out.println("请输入密码");
        String inPwd = scanner.nextLine();


        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        // 1. 注册驱动
        Class.forName(driver);

        // 2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3.构建查询语句 , ? 相当于占位符
        String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";

        // 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);
        // 给问号赋值
        preparedStatement.setString(1, inUser);
        preparedStatement.setString(2, inPwd);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            // 如果查询到记录，说明用户存在
            System.out.println("恭喜登录成功!");
        } else {
            System.out.println("登录失败，没有该用户!");
        }


        // 关闭连接
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }
}

PreparedStatement DML语句

插入 Insert

package com.jdbc.preparedstatement;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * PreparedStatement演示处理DML语句--插入
 *
 * @author wty
 * @date 2022/11/6 19:52
 */
public class PrepareperedStatementExercise02 {
    @Test
    public void PrepareperedStatementExercise02Test() throws SQLException, ClassNotFoundException, IOException {
        System.out.println("请输入要添加的用户名");
        Scanner scanner = new Scanner(System.in);
        // nextLine允许单引号和空格
        String inUser = scanner.nextLine();
        System.out.println("请输入要添加的密码");
        String inPwd = scanner.nextLine();


        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        // 1. 注册驱动
        Class.forName(driver);

        // 2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3.构建查询语句， ? 相当于占位符
        //String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";

        // 3.添加记录
        String sqlStr = "insert into admin values (?,?)";

        // 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);
        // 给问号赋值
        preparedStatement.setString(1, inUser);
        preparedStatement.setString(2, inPwd);

        int rows = preparedStatement.executeUpdate();

        System.out.println(rows > 0 ? "成功" : "失败");


        // 关闭连接
        preparedStatement.close();
        connection.close();
    }
}

更新Update

package com.jdbc.preparedstatement;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;

/**
 * PreparedStatement演示处理DML语句--更新
 * 
 * @author wty
 * @date 2022/11/6 20:00
 */
public class PrepareperedStatementExercise03 {
    @Test
    public void PrepareperedStatementExercise03Test() throws SQLException, ClassNotFoundException, IOException {
        System.out.println("请输入修改后的用户名");
        Scanner scanner = new Scanner(System.in);
        // nextLine允许单引号和空格
        String inUser = scanner.nextLine();
        System.out.println("请输入修改后的密码");
        String inPwd = scanner.nextLine();


        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        // 1. 注册驱动
        Class.forName(driver);

        // 2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3.构建查询语句， ? 相当于占位符
        //String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";

        // 3.添加记录
        String sqlStr = "update admin set name = ?,pwd = ? where name = 'admin'";

        // 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);
        // 给问号赋值
        preparedStatement.setString(1, inUser);
        preparedStatement.setString(2, inPwd);

        int rows = preparedStatement.executeUpdate();

        System.out.println(rows > 0 ? "成功" : "失败");


        // 关闭连接
        preparedStatement.close();
        connection.close();
    }
}

删除Delete

package com.jdbc.preparedstatement;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;

/**
 * PreparedStatement演示处理DML语句--删除
 *
 * @author wty
 * @date 2022/11/6 20:00
 */
public class PrepareperedStatementExercise04 {
    @Test
    public void PrepareperedStatementExercise04Test() throws SQLException, ClassNotFoundException, IOException {
        System.out.println("请输入需要删除的用户名");
        Scanner scanner = new Scanner(System.in);
        // nextLine允许单引号和空格
        String inUser = scanner.nextLine();

        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        // 1. 注册驱动
        Class.forName(driver);

        // 2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3.构建查询语句， ? 相当于占位符
        //String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";

        // 3.添加记录
        String sqlStr = "delete from admin where name = ?";

        // 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);
        // 给问号赋值
        preparedStatement.setString(1, inUser);

        int rows = preparedStatement.executeUpdate();

        System.out.println(rows > 0 ? "成功" : "失败");


        // 关闭连接
        preparedStatement.close();
        connection.close();
    }
}

课堂练习

package com.jdbc.preparedstatement;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * @author wty
 * @date 2022/11/6 22:36
 */
public class HomeWork {
    @Test
    public void homeWorkTest() throws IOException, ClassNotFoundException, SQLException {
//        System.out.println("请输入需要的用户名");
//        Scanner scanner = new Scanner(System.in);
//        String inUser = scanner.nextLine();
//        System.out.println("请输入新的密码");
//        scanner = new Scanner(System.in);
//        String inPsd = scanner.nextLine();


        Properties properties = new Properties();
        properties.load(new FileInputStream("src/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        //String sqlStr = "insert into admin values (?,?)";
        String sqlStr = "select name, pwd from admin";
        PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);
        //preparedStatement.setString(1, inUser);
        //preparedStatement.setString(2, inPsd);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            String name = resultSet.getString(1);
            String pwd = resultSet.getString(2);
            System.out.println(name + "\t" + pwd + "\t");
        }

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


    }
}

JDBC API

封装JDBCUtils 关闭连接得到连接

说明

工具类

package com.jdbc.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具包:完成mysql的连接和关闭资源
 *
 * @author wty
 * @date 2022/11/6 23:14
 */
public class JDBCUtils {
    // 因为只需要一份，所以做成静态的
    /**
     * 用户名
     */
    private static String user;
    /**
     * 密码
     */
    private static String password;
    /**
     * url
     */
    private static String url;
    /**
     * driver
     */
    private static String driver;

    // 在静态代码块初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src/mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            // 在实际开发中，这样处理
            // 1.将编译异常转换成运行异常
            // 2.调用者可以选择捕获异常，或者默认处理
            throw new RuntimeException(e);
        }
    }

    /***
     * 连接数据库,返回Connection
     *
     * @Param No such property: code for class: Script1
     * @return java.sql.Connection
     * @Date 2022/11/6 23:30
     * @Author wty
     **/
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            // 在实际开发中，这样处理
            // 1.将编译异常转换成运行异常
            // 2.调用者可以选择捕获异常，或者默认处理
            throw new RuntimeException(e);
        }
    }

    /***
     * 关闭相关资源
     *
     * @Param * @param resultSet
     * @param statement
     * @param connection
     * @return void
     * @Date 2022/11/6 23:38
     * @Author wty
     **/
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        // 1. ResultSet 结果集
        // 2. Statement PreparedStatement
        // 3. Connection
        // 4. 如果需要关闭资源，就传入资源，否则就传入空

        try {
            if (resultSet != null) {
                resultSet.close();
            }

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

            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}

DML使用类

package com.jdbc.utils;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 该类演示如何使用JDBCUtils工具类
 *
 * @author wty
 * @date 2022/11/6 23:42
 */
public class JDBCUtilsUse {
    @Test
    public void testDML() {
        // 1. 得到连接
        Connection connection = null;

        // 2.组织一个sql
        String sqlStr = "update admin set name = ? where name = ?";

        // 3.创建一个PreparedStatement
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sqlStr);
            preparedStatement.setString(1, "周星驰");
            preparedStatement.setString(2, "king");
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }

    }
}

DQL使用类

package com.jdbc.utils;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author wty
 * @date 2022/11/7 9:28
 */
public class JDBCUtilsUsebf {
    @Test
    public void JDBCUtilsUsebfTest() {
        // 连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtilsbf.getConnection();
            String sqlStr = "select name,pwd from admin";
            preparedStatement = connection.prepareStatement(sqlStr);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("pwd");
                System.out.println(name + "\t" + pwd + "\t");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        // 关闭
        JDBCUtilsbf.close(connection, preparedStatement, resultSet);

    }
}

事务

基本介绍

应用实例

模拟转账业务

没有事务控制出现数据不一致问题

执行代码前

没有事务控制代码如下:

package com.jdbc.transaction;

import com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 通过转账案例，模拟JDBC中的事务
 *
 * @author wty
 * @date 2022/11/7 9:55
 */
public class TransactionExercise {
    /**
     * 没有事务的情况
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 9:56
     * @Author wty
     **/
    @Test
    public void noTransaction() {
        // 操作转账的业务

        // 1. 得到连接
        Connection connection = null;

        // 2.组织一个sql
        String sqlStr = "update account set balance = balance - 100 where id = ?";
        String sqlStr2 = "update account set balance = balance + 100 where id = ?";

        // 3.创建一个PreparedStatement
        PreparedStatement preparedStatement = null;
        try {
            // connection在默认情况下是自动提交的
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sqlStr);
            preparedStatement.setInt(1, 1);
            preparedStatement.executeUpdate();

            // 人为抛出异常
            int i = 1 / 0;

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


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

执行代码抛出异常

数据不一致问题出现

马化腾的金额没有增加

事务控制后消除数据不一致问题

package com.jdbc.transaction;

import com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 通过转账案例，模拟JDBC中的事务
 *
 * @author wty
 * @date 2022/11/7 9:55
 */
public class TransactionExercise {
    /**
     * 有事务的情况
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 9:56
     * @Author wty
     **/
    @Test
    public void haveTransaction() {
        // 操作转账的业务

        // 1. 得到连接
        Connection connection = null;

        // 2.组织一个sql
        String sqlStr = "update account set balance = balance - 100 where id = ?";
        String sqlStr2 = "update account set balance = balance + 100 where id = ?";

        // 3.创建一个PreparedStatement
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            // 连接后，设置为不自动提交，(回滚到这里)
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlStr);
            preparedStatement.setInt(1, 1);
            preparedStatement.executeUpdate();

            // 人为抛出异常
            //int i = 1 / 0;

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

            connection.commit();
            System.out.println("执行成功，提交事务!");

        } catch (SQLException e) {
            try {
                System.out.println("执行发生错误，回滚事务!");
                // 默认回滚到事务开始的状态
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

正常情况下马云金额减少，马化腾金额增加。
抛出异常情况下马云和马化腾的金额保持不变。

批处理

基本介绍

package com.jdbc.batch;

import com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author wty
 * @date 2022/11/7 10:31
 */
public class BatchExercise {
    /**
     * 普通方法逐条处理
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 10:31
     * @Author wty
     **/
    @Test
    public void noBatch() {
        // 获取连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 执行sql
            connection = JDBCUtils.getConnection();
            // 设置不自动提交事务
            connection.setAutoCommit(false);
            String sqlStr = "insert into admin2 values (null,?,?)";
            preparedStatement = connection.prepareStatement(sqlStr);
            System.out.println("开始执行");
            long begin = System.currentTimeMillis();
            for (int i = 1; i <= 5000; i++) {

                preparedStatement.setString(1, "tom" + i);
                preparedStatement.setString(2, "123");
                preparedStatement.executeUpdate();
            }
            long end = System.currentTimeMillis();
            System.out.println("总时长: " + (end - begin));
            /**
             * 开始执行
             * 总时长: 603
             */


            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }


        // 关闭连接
        JDBCUtils.close(null, preparedStatement, connection);

    }


    /**
     * 批处理
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 10:31
     * @Author wty
     **/
    @Test
    public void haveBatch() {
        // 获取连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 执行sql
            connection = JDBCUtils.getConnection();
            // 设置不自动提交事务
            connection.setAutoCommit(false);
            String sqlStr = "insert into admin2 values (null,?,?)";
            preparedStatement = connection.prepareStatement(sqlStr);
            System.out.println("开始执行");
            long begin = System.currentTimeMillis();
            for (int i = 1; i <= 5000; i++) {

                preparedStatement.setString(1, "tom" + i);
                preparedStatement.setString(2, "123");
                // 将sql语句加入Batch 批处理包
                preparedStatement.addBatch();
                /**源码分析
                 * 1. 第一次创建ArrayList -对象数组 protected List<Object> batchedArgs;
                 * 2. elementData 即 Object[] 就会存放sql语句
                 * 3. 当elementData满后，就按照1.5倍扩容(ArrayList扩容规则)
                 * 4. 当添加到指定的值后，就executeBatch
                 * 5. 批处理会减少我们发送sql语句的网络开销，而且减少编译次数，因此效率提高
                 *
                 *     public void addBatch() throws SQLException {
                 *         synchronized(this.checkClosed().getConnectionMutex()) {
                 *             if (this.batchedArgs == null) {
                 *                 // 这里看底层是 ArrayList
                 *                 this.batchedArgs = new ArrayList();
                 *             }
                 *
                 *             this.batchedArgs.add(new ServerPreparedStatement.BatchedBindValues(this.parameterBindings));
                 *         }
                 *     }
                 */

                // 当有1000条数据的时候执行一次
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch();
                    // 清空一次
                    preparedStatement.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("批处理总时长: " + (end - begin));
            /**
             * 开始执行
             * 批处理总时长: 101
             */


            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }


        // 关闭连接
        JDBCUtils.close(null, preparedStatement, connection);

    }
}

数据库连接池

5000次连接数据库问题

package com.jdbc.datasource;

import com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author wty
 * @date 2022/11/7 11:26
 */
public class ConQuestion {
    /**
     * 传统方式连接数据库5000次
     *
     * @return
     * @Param * @param null
     * @Date 2022/11/7 11:26
     * @Author wty
     **/
    @Test
    public void ConQuestionTest() throws SQLException {
        long begin = System.currentTimeMillis();
        for (int i = 1; i <= 5000; i++) {
            // 获取连接
            Connection connection = JDBCUtils.getConnection();
            // 不关闭连接
            /**
             * 抛出异常
             *
             * java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from
             * server: "Too many connections"
             */
            JDBCUtils.close(null, null, connection);
        }
        long end = System.currentTimeMillis();
        System.out.println("共用时:" + (end - begin));
        /**
         * 共用时:10439
         */

    }

}

传统方式获取Connection问题分析

数据库连接池的基本介绍

数据库连接池类型

C3P0应用实例

package com.jdbc.datasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 演示C3P0的使用
 *
 * @author wty
 * @date 2022/11/7 14:19
 */
public class C3P0Use {
    /**
     * C3P0的使用方式1:相关参数，在程序中指定user,url,password
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 14:20
     * @Author wty
     **/
    @Test
    public void C3P0Use01() throws IOException, PropertyVetoException, SQLException {
        // 1.创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        // 2.通过配置文件获取相关信息
        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");

        // 3. 给数据源设置相关的参数
        // 连接管理由数据源对象掌控
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        // 设置数据源的连接数(初始化连接数)
        comboPooledDataSource.setInitialPoolSize(10);
        // 最大连接数：超过了就进入等待队列中
        comboPooledDataSource.setMaxPoolSize(50);


        // 测试调用5000次的效率
        long begin = System.currentTimeMillis();
        for (int i = 1; i <= 5000; i++) {
            // 核心方法，从DataSource接口实现
            Connection connection = comboPooledDataSource.getConnection();
            //System.out.println("连接成功");
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("共用时:" + (end - begin));
        /**
         * 共用时:654
         */

    }

    /**
     * C3P0的使用方式2:
     * <p>
     * 1.将c3p0提供的配置文件c3p0-config.xml拷贝到src目录下
     * 2.该文件指定了连接池和数据库的相关参数
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 14:20
     * @Author wty
     **/
    @Test
    public void C3P0Use02() throws IOException, PropertyVetoException, SQLException {
        // 1.创建一个数据源对象(把数据源名称写入括号内)
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("wty");

        // 测试调用5000次的效率
        long begin = System.currentTimeMillis();
        for (int i = 1; i <= 5000; i++) {
            // 核心方法，从DataSource接口实现
            Connection connection = comboPooledDataSource.getConnection();
            //System.out.println("连接成功");
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("共用时:" + (end - begin));
        /**
         * 共用时:657
         */

    }

}

Druid(德鲁伊)应用实例

package com.jdbc.datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import jdk.nashorn.internal.ir.CallNode;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;

/**
 * Druid德鲁伊的使用
 *
 * @author wty
 * @date 2022/11/7 15:04
 */
public class DruidUse {
    /**
     * Druid的使用:
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 15:07
     * @Author wty
     **/
    @Test
    public void DruidUseTest() throws Exception {
        //1.加入 Druid jar 包
        //2.加入 配置文件 druid.properties
        //3.创建Properties对象用来获取信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/druid.properties"));

        // 4.创建一个指定参数的数据库连接池,Druid连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long begin = System.currentTimeMillis();
        for (int i = 1; i <= 5000; i++) {
            Connection connection = dataSource.getConnection();
            //System.out.println("连接成功");
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("共用时:" + (end - begin));
        /**
         * 共用时:592
         */
    }
}

将JDBCUtils更改成Druid实现

DruidUtils

package com.HomeWork.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author wty
 * @date 2022/11/7 15:30
 */
public class DruidUtils {
    private static DataSource dataSource = null;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src/druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return connection;
    }

    /**
     * 数据库连接池技术中：close方法不是真正断掉连接，而是把使用的Connection对象放回连接池
     *
     * @param statement
     * @param connection
     * @return void
     * @Param * @param resultSet
     * @Date 2022/11/7 15:43
     * @Author wty
     **/
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (null != resultSet) {
                resultSet.close();
            }
            if (null != statement) {
                statement.close();
            }
            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}

测试类:

package com.jdbc.utils;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * DruidUtils的使用
 *
 * @author wty
 * @date 2022/11/7 15:47
 */
public class DruidUtilsUse {
    /**
     * DruidUtils的使用--DML语句：批量插入
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 16:19
     * @Author wty
     **/
    @Test
    public void DruidUtilsUseTest01() {
        // 连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DruidUtils.getConnection();
            connection.setAutoCommit(false);
            String sqlStr = "insert into admin2 values (null,?,?)";
            long begin = System.currentTimeMillis();
            // 这里一定要放在for循环外面
            preparedStatement = connection.prepareStatement(sqlStr);
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setString(1, "tom" + i);
                preparedStatement.setString(2, "123");

                preparedStatement.addBatch();
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("共用时:" + (end - begin));
            System.out.println("connection的运行类型是；" + connection.getClass());

            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }

        // 断开连接池
        DruidUtils.close(null, preparedStatement, connection);
    }

    /**
     * DruidUtils的使用--DQL语句
     *
     * @return void
     * @Param * @param
     * @Date 2022/11/7 16:19
     * @Author wty
     **/
    @Test
    public void DruidUtilsUseTest02() {
        // 连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DruidUtils.getConnection();
            String sqlStr = "select id,username,password from admin2";
            // 这里一定要放在for循环外面
            preparedStatement = connection.prepareStatement(sqlStr);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                System.out.println(id + "\t" + username + "\t" + password + "\t");
            }
            System.out.println("connection的运行类型是；" + connection.getClass());

        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 断开连接池
        DruidUtils.close(resultSet, preparedStatement, connection);
    }
}

运行结果:表明Druid中，close()方法的运行类型是com.alibaba.druid.pool.DruidPooledConnection，并非是直接断开数据库连接，而是把使用的Connection对象放回连接池

Apache—DBUtils

分析一个问题

用自己的土方法模拟DBUtils

Actor类

package com.jdbc.dbutils;

import java.util.Date;

/**
 * Actor类 和表db_02.actor每个字段相对应
 *
 * @author wty
 * @date 2022/11/7 18:42
 */
public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private String phone;
    private Date borndate;

    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 String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.phone = phone;
        this.borndate = borndate;
    }

    /***
     * 一定要给一个无参构造器用于反射
     *
     * @Param * @param
     * @return
     * @Date 2022/11/7 18:57
     * @Author wty
     **/
    public Actor() {

    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", phone='" + phone + '\'' +
                ", borndate=" + borndate +
                '}';
    }
}

土办法模拟的类

package com.jdbc.dbutils;

import com.jdbc.utils.DruidUtils;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

/**
 * 土方法实现底层DBUtils工具类
 *
 * @author wty
 * @date 2022/11/7 18:41
 */
public class DBUtilsExercise {
    @Test
    public ArrayList DBUtilsExerciseTest() {
        // 连接
        Connection connection = null;

        // 组装sql
        Properties properties = new Properties();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        // 把resultSet1保存到集合里
        ArrayList<Actor> actors = new ArrayList<>();
        try {
            connection = DruidUtils.getConnection();
            properties.load(new FileInputStream("src/druid.properties"));
            String sqlStr = "select id,name,sex,borndate,phone from actor";
            preparedStatement = connection.prepareStatement(sqlStr);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");

                // 把resultSet集合元素放入到actors中
                actors.add(new Actor(id, name, sex, borndate, phone));
            }

            for (Actor actor : actors) {
                System.out.println(actor);
            }


        } catch (IOException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            DruidUtils.close(resultSet, preparedStatement, connection);
        }
        // 因为ArrayList 和connection无关联，所以该集合可以复用
        return actors;

    }
}

DBUtils基本介绍

应用实例

package com.jdbc.dbutils;

import com.jdbc.utils.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * DBUtils工具类的使用--CRUD
 *
 * @author wty
 * @date 2022/11/7 19:11
 */
public class DBUtilsUse {
    @SuppressWarnings({"all"})
    @Test
    public void DBUtilsUseTest() throws SQLException {
        // 1. 得到连接
        Connection connection = DruidUtils.getConnection();
        // 2. 使用DBUtils 类和接口，先引入DBUtils相关的jar包，加入project
        // 3. 创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        String sqlStr = "select id,name,sex,borndate,phone from actor where id <= ?";
        // 注意:sql也可以查询部分列
        // 老韩解读
        // (1)query 方法就是执行sql语句并返回得到ResultSet,并且封装到ArrayList中
        // (2)返回集合
        // (3)connection 连接
        // (4)sqlStr sql语句
        // (5)new BeanListHandler<>(Actor.class) 在将ResultSet取出到Actor对象里，封装到ArrayList中
        // (6)4是sql语句中的?赋值 可以有多个
        // (7)queryRunner.query()方法底层会关闭结果集resultSet、PreparedStatement
        List<Actor> list = queryRunner.query(connection, sqlStr, new BeanListHandler<>(Actor.class), 4);
        // 底层使用反射机制 获取Actor类中的属性,然后进行封装
        /**
         * // 底层源码
         *     public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
         *         PreparedStatement stmt = null;
         *         // ResultSet查询sql的结果集
         *         ResultSet rs = null;
         *         // 返回的ArrayList
         *         Object result = null;
         *
         *         try {
         *             stmt = this.prepareStatement(conn, sql);
         *             // sql语句中？的填充赋值
         *             this.fillStatement(stmt, params);
         *             // 执行sql,获取查询结果放到ResultSet
         *             rs = this.wrap(stmt.executeQuery());
         *             // 从Actor中获取对象封装到Arraylist中，用到反射
         *             result = rsh.handle(rs);
         *         } catch (SQLException var33) {
         *             this.rethrow(var33, sql, params);
         *         } finally {
         *             try {
         *                  // 这里关闭了结果集resultSet
         *                 this.close(rs);
         *             } finally {
         *                  // 这里关闭了结果集statement
         *                 this.close((Statement)stmt);
         *             }
         *         }
         *
         *         return result;
         *     }
         */

        for (Actor actor : list) {
            System.out.println(actor);
        }

        // 释放资源,关闭对象
        DruidUtils.close(null, null, connection);

    }
}

演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象)

/**
 * 演示 apache-dbutils + druid 完成 返回的结果是单行多列记录(单个对象)
 *
 * @author wty
 * @date 2022/11/7 20:00
 */
public class DBUtilsUseSingleObject {
    @Test
    public void DBUtilsUseSingleObjectTest01() {
        // 获取连接
        Connection connection = DruidUtils.getConnection();

        // 组装sql
        String sqlStr = "select * from actor where id = ?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            // 因为返回的单个对象，就是单条记录，就不是集合了，用BeanHandler
            Actor actor = queryRunner.query(connection, sqlStr, new BeanHandler<>(Actor.class), 1);

            System.out.println(actor);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            DruidUtils.close(null, null, connection);

        }


    }

演示 apache-dbutils + druid 返回的结果是单行单列(Object)

   /**
     * 演示 apache-dbutils + druid 完成 返回的结果是单行单列
     *
     * @author wty
     * @date 2022/11/7 20:00
     */
    @Test
    public void DBUtilsUseSingleObjectTestScalar() {
        // 获取连接
        Connection connection = DruidUtils.getConnection();

        // 组装sql
        String sqlStr = "select name from actor where id = ? ";
        QueryRunner queryRunner = new QueryRunner();
        try {
            // 因为返回的单个对象，就是单行单列记录，就不是集合了，用BeanHandler
            Object query = queryRunner.query(connection, sqlStr, new ScalarHandler(), 1);

            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            DruidUtils.close(null, null, connection);
        }
    }

演示修改Update

    /**
     * 演示 apache-dbutils + druid 完成 Update语句
     *
     * @author wty
     * @date 2022/11/7 20:00
     */
    @Test
    public void DBUtilsUseSingleObjectTestUpdate() {
        // 获取连接
        Connection connection = DruidUtils.getConnection();

        // 组装sql 完成 修改
        String sqlStr = "update actor set name = ? where id = ?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            // 返回的值是受影响的行数
            // queryRunner.update()可以用于增删改DML语句
            int rows = queryRunner.update(connection, sqlStr, "tommy", 2);

            System.out.println(rows > 0 ? "成功" : "没有影响");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            DruidUtils.close(null, null, connection);
        }
    }

演示插入Insert

  /**
     * 演示 apache-dbutils + druid 完成 insert语句
     *
     * @author wty
     * @date 2022/11/7 20:00
     */
    @Test
    public void DBUtilsUseSingleObjectTestInsert() {
        // 获取连接
        Connection connection = DruidUtils.getConnection();

        // 组装sql 完成 修改
        String sqlStr = "insert into actor values (null,?,?,?,?)";
        QueryRunner queryRunner = new QueryRunner();
        try {
            int rows = queryRunner.update(connection, sqlStr, "Lily", "女", "1998-03-04", "17312113421");

            System.out.println(rows > 0 ? "成功" : "未删除");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            DruidUtils.close(null, null, connection);
        }
    }

演示删除Delete

    /**
     * 演示 apache-dbutils + druid 完成 Delete语句
     *
     * @author wty
     * @date 2022/11/7 20:00
     */
    @Test
    public void DBUtilsUseSingleObjectTestDelete() {
        // 获取连接
        Connection connection = DruidUtils.getConnection();

        // 组装sql 完成 修改
        String sqlStr = "delete from actor where id = ?";
        QueryRunner queryRunner = new QueryRunner();
        try {
            int rows = queryRunner.update(connection, sqlStr, 4);

            System.out.println(rows > 0 ? "成功" : "未删除");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            DruidUtils.close(null, null, connection);
        }
    }

表和 JavaBean 的类型映射关系

DAO 和增删改查通用方法-BasicDao

分析一个问题

基本说明

BasicDAO

package com.dao.dao;

import com.dao.utils.DruidUtils;
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;

/**
 * 开发 BasicDAO 是其他DAO的父类
 * <T>泛型指定具体类型
 *
 * @author wty
 * @date 2022/11/7 23:37
 */
@SuppressWarnings({"all"})
public class BasicDAO<T> {
    private QueryRunner queryRunner = new QueryRunner();

    /**
     * 开发通用的DML方法
     *
     * @param params 这种写法可以参考源码 queryRunner.query()
     * @param sqlStr
     * @return int
     * @Date 2022/11/7 23:43
     * @Author wty
     **/
    public int dml(String sqlStr, Object... params) {
        Connection connection = null;

        int affectedrows = 0;
        try {
            connection = DruidUtils.getConnection();
            affectedrows = queryRunner.update(connection, sqlStr, params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DruidUtils.close(null, null, connection);
        }

        return affectedrows;
    }

    /**
     * 返回多个对象(即查询的结果是多行)，针对任意表
     *
     * @return java.util.List<T> 返回对应的ArrayList 集合
     * @param: sqlStr sql语句可以有?
     * @param: clazz 传入一个类的Class对象，比如 Actor.class
     * @param: params 传入?具体的值，可以是多个
     * @date 2022/11/8 0:04
     * @author wty
     **/
    public List<T> queryMulti(String sqlStr, Class<T> clazz, Object... params) {
        Connection connection = null;
        List<T> list = null;

        try {
            connection = DruidUtils.getConnection();
            list = queryRunner.query(connection, sqlStr, new BeanListHandler<T>(clazz), params);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return list;
    }


    /**
     * 返回单行多列数据
     *
     * @return T
     * @param: sqlStr
     * @param: clazz
     * @param: params
     * @date 2022/11/8 0:19
     * @author wty
     **/
    public T querySingle(String sqlStr, Class<T> clazz, Object... params) {
        Connection connection = null;
        T t = null;

        try {
            connection = DruidUtils.getConnection();
            t = queryRunner.query(connection, sqlStr, new BeanHandler<T>(clazz), params);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return t;
    }

    /**
     * 返回单行单列
     *
     * @return T
     * @param: sqlStr
     * @param: clazz
     * @param: params
     * @date 2022/11/8 0:22
     * @author wty
     **/
    public Object queryScalar(String sqlStr, Object... params) {
        Connection connection = null;
        Object o = null;

        try {
            connection = DruidUtils.getConnection();
            o = queryRunner.query(connection, sqlStr, new ScalarHandler(), params);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return o;
    }
}

ActorDao

package com.dao.dao;

import com.dao.domain.Actor;

/**
 * @author wty
 * @date 2022/11/8 0:27
 */
public class ActorDao extends BasicDAO<Actor> {
    // 1. 就有BasicDAO所有的方法
    // 2. 根据业务需求可以编写特有的方法

}

Actor

package com.dao.domain;

import java.util.Date;

/**
 * Actor类 和表db_02.actor每个字段相对应
 *
 * @author wty
 * @date 2022/11/7 18:42
 */
public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private String phone;
    private Date borndate;

    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 String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.phone = phone;
        this.borndate = borndate;
    }

    /***
     * 一定要给一个无参构造器用于反射
     *
     * @Param * @param
     * @return
     * @Date 2022/11/7 18:57
     * @Author wty
     **/
    public Actor() {

    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", phone='" + phone + '\'' +
                ", borndate=" + borndate +
                '}';
    }
}

TestDao

package com.dao.test;

import com.dao.dao.ActorDao;
import com.dao.domain.Actor;
import org.junit.Test;

import java.util.List;

/**
 * 测试ActorDao对Actor表的CRUD操作
 *
 * @author wty
 * @date 2022/11/8 0:29
 */
public class TestDao {
    @Test
    public void testDao() {
        ActorDao actorDao = new ActorDao();

        // 1.测试查询语句 多行记录
        String sqlStr = "SELECT * FROM actor where id >= ?";
        List<Actor> actors = actorDao.queryMulti(sqlStr, Actor.class, 1);

        System.out.println("多行多列查询结果：");
        for (Actor actor : actors) {
            System.out.println(actor);
        }

        // 2.测试查询语句 单行记录
        sqlStr = "SELECT * FROM actor where id = ?";
        Actor actor = actorDao.querySingle(sqlStr, Actor.class, 1);
        System.out.println("单行多列查询结果是:");
        System.out.println(actor);

        // 3.测试查询语句 单行单列
        sqlStr = "select name from actor where id = ?";
        Object o = actorDao.queryScalar(sqlStr, 5);
        System.out.println("单行单列查询结果是:");
        System.out.println(o);

        // 4.测试DML语句
        // 修改
        sqlStr = "update actor set name = ? where id = ?";
        int affectedrows = actorDao.dml(sqlStr, "Lili", 6);
        System.out.println(affectedrows > 0 ? "修改成功" : "没有受影响的行");

        // 新增
        sqlStr = "insert into actor values(null,?,?,?,?)";
        int dml_insert = actorDao.dml(sqlStr, "sam", "男", "1991-03-04", "13167541122");
        System.out.println(dml_insert > 0 ? "添加成功" : "没有受影响的行");

        // 删除
        sqlStr = "delete from actor where id = ?";
        int dml_delete = actorDao.dml(sqlStr, 2);
        System.out.println(dml_delete > 0 ? "删除成功" : "没有受影响的行");
    }

}

课后练习

BasicDao

package com.HomeWork.dao;

import com.HomeWork.utils.DruidUtils;
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.util.List;

/**
 * @author wty
 * @date 2022/11/8 9:55
 */
public class BasicDao<T> {
    /**
     * dml语句
     *
     * @param
     * @return int
     * @param: sqlStr
     * @param: params
     * @date 2022/11/8 9:59
     * @author wty
     **/
    public int dml(String sqlStr, Object... params) {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();
        int affectedrows = 0;
        try {
            // 连接
            connection = DruidUtils.getConnection();

            // 组装sql
            affectedrows = queryRunner.update(connection, sqlStr, params);


        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return affectedrows;

    }

    /**
     * 多行多列查询
     *
     * @param
     * @return java.util.List<T>
     * @param: sqlStr
     * @param: params
     * @date 2022/11/8 10:01
     * @author wty
     **/
    public List<T> mutilResult(String sqlStr, Class<T> clazz, Object... params) {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();
        List<T> list = null;
        try {
            // 连接
            connection = DruidUtils.getConnection();

            // 组装sql
            list = queryRunner.query(connection, sqlStr, new BeanListHandler<>(clazz), params);


        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return list;
    }

    /**
     * 返回单行多列
     *
     * @param
     * @return T
     * @param: sqlStr
     * @param: clazz
     * @param: params
     * @date 2022/11/8 10:04
     * @author wty
     **/
    public T SingleResult(String sqlStr, Class<T> clazz, Object... params) {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();
        T t = null;
        try {
            // 连接
            connection = DruidUtils.getConnection();

            // 组装sql
            t = queryRunner.query(connection, sqlStr, new BeanHandler<>(clazz), params);


        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return t;
    }

    /**
     * 返回单行单列
     *
     * @param
     * @return java.lang.Object
     * @param: sqlStr
     * @param: clazz
     * @param: params
     * @date 2022/11/8 10:06
     * @author wty
     **/
    public Object SalarResult(String sqlStr, Object... params) {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();
        Object o = null;
        try {
            // 连接
            connection = DruidUtils.getConnection();

            // 组装sql
            o = queryRunner.query(connection, sqlStr, new ScalarHandler(), params);


        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DruidUtils.close(null, null, connection);
        }
        return o;
    }


}

GoodsDao

package com.HomeWork.dao;

import com.HomeWork.domain.Goods;
import com.dao.dao.BasicDAO;
import org.junit.Test;

import java.util.List;

/**
 * @author wty
 * @date 2022/11/8 10:07
 */
public class GoodsDao extends BasicDAO<Goods> {
   
}

Goods

package com.HomeWork.domain;

/**
 * @author wty
 * @date 2022/11/8 9:53
 */
public class Goods {
    private Integer id;
    private String goods_name;
    private Double price;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getGoods_name() {
        return goods_name;
    }

    public void setGoods_name(String goods_name) {
        this.goods_name = goods_name;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "id=" + id +
                ", goods_name='" + goods_name + '\'' +
                ", price=" + price +
                '}';
    }

    public Goods(Integer id, String goods_name, Double price) {
        this.id = id;
        this.goods_name = goods_name;
        this.price = price;
    }

    public Goods() {
    }
}

GoodsTest

package com.HomeWork.test;

import com.HomeWork.dao.GoodsDao;
import com.HomeWork.domain.Goods;
import org.junit.Test;

import java.util.List;

/**
 * @author wty
 * @date 2022/11/8 10:27
 */
public class GoodsTest {
    @Test
    public void testInsert() {
        GoodsDao goodsDao = new GoodsDao();
        String sqlStr = "insert into goods values (?,?,?)";
        int rows = goodsDao.dml(sqlStr, 1006, "喜之郎", 32.99);
        System.out.println(rows > 0 ? "插入成功" : "没有收影响的结果集");

    }

    @Test
    public void testUpdate() {
        GoodsDao goodsDao = new GoodsDao();
        String sqlStr = "update goods set price = ? where id = ?";
        int rows = goodsDao.dml(sqlStr, 3.5, 1004);
        System.out.println(rows > 0 ? "更新成功" : "没有收影响的结果集");

    }

    @Test
    public void testDelete() {
        GoodsDao goodsDao = new GoodsDao();
        String sqlStr = "delete from goods where id = ?";
        int rows = goodsDao.dml(sqlStr, 1005);
        System.out.println(rows > 0 ? "删除成功" : "没有收影响的结果集");

    }

    @Test
    public void multiSelect() {
        GoodsDao goodsDao = new GoodsDao();
        String sqlStr = "select id,goods_name,price from goods";

        List<Goods> list = goodsDao.queryMulti(sqlStr, Goods.class);
        for (Goods goods : list) {
            System.out.println(goods);
        }
    }

    @Test
    public void SingleSelect() {
        GoodsDao goodsDao = new GoodsDao();
        String sqlStr = "select id,goods_name,price from goods where id = ?";

        Goods goods = goodsDao.querySingle(sqlStr, Goods.class, 1001);
        System.out.println(goods);
    }

    @Test
    public void SalarSelect() {
        GoodsDao goodsDao = new GoodsDao();
        String sqlStr = "select goods_name from goods where id = ?";

        Object o = goodsDao.queryScalar(sqlStr, 1001);
        System.out.println(o);
    }
}