【MySQL】JAVA之数据库篇JDBC

1 基本信息

• 命令行连接

# 命令行连接
mysql -h 主机IP -P 端口 -u 用户名 -p 密码

1.1 创建数据库字符集

# 不区分大小写(默认)  utf8_general_ci
CREATE DATABASES xhh_01 CHARACTER SET utf8;

# 区分大小写 utf8_bin
CREATE DATABASES xhh_01 CHARACTER SET utf8 COLLATE utf8_bin;

1.2 备份和恢复数据库

备份xhh_db2
应用1：从A管理系统转移到B系统使用
应用2：防止数据库被破坏

• 备份数据库

(base) PS C:\Users\Link> mysqldump -u root -p -B xhh_db2 > E:/bak.sql
Enter password: ****

• 恢复

mysql> source E:\\bak.sql;

• 备份表

mysqldump -u root -p密码 数据库 表名1 [表名2 ...] > E:/bak.sql

2 JDBC连接数据库

java.sql
javax.sql
注意版本号

-- xhh_db.actor
CREATE TABLE actor(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL DEFAULT '',
sex CHAR(1) NOT NULL DEFAULT '男');

• 静态加载

package cn.xhh.jdbc_;

import com.mysql.jdbc.Driver;

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

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.9.30
 */
public class Jdbc_01 {
    public static void main(String[] args) throws SQLException {
        // 1.注册驱动
        // 拷贝 mysql-xxx-bin.jar  到 ./lib   点击 add to proj
        Driver driver = new Driver();

        // 2.连接
        // jdbc:mysql://192.168.137.234:3306/database_name
        // jdbc:mysql://localhost:3306/xhh_db2
        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        // 将用户名和密码翻入 Properties中
        Properties idPassword = new Properties();
        idPassword.setProperty("user", "root");       // 账号
        idPassword.setProperty("password", "123456"); // 密码

        Connection conn = driver.connect(url, idPassword);

        // 3.执行sql  xhh_db2.actor
//        String sql1 = "INSERT INTO actor VALUES(null, 'mcy', 'F')";
        String sql2 = "UPDATE actor SET name='xhh' WHERE name='mcy'";
        // 用于执行静态sql语句
        Statement statement = conn.createStatement();
        int rows = statement.executeUpdate(sql2); // >0 :SUCCESS
        System.out.println(rows > 0 ? "SUCCESS!" : "FAILED.");

        // 4.关闭
        statement.close();
        conn.close();
    }
}

• 5种连接反射

package cn.xhh.jdbc_;

import com.mysql.jdbc.Driver;

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.util.Properties;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.9.30
 */
public class Jdbc_02 {
    public static void main(String[] args) throws SQLException, IllegalAccessException, InstantiationException, ClassNotFoundException, IOException {
        Jdbc_02 jdbc_02 = new Jdbc_02();
        jdbc_02.conn05();
    }

    // 连接方式1
    public void conn01() throws SQLException {
        // 1.注册驱动
        // 拷贝 mysql-xxx-bin.jar  到 ./lib   点击 add to proj
        Driver driver = new Driver();

        // 2.连接
        // jdbc:mysql://192.168.137.234:3306/database_name
        // jdbc:mysql://localhost:3306/xhh_db2
        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        // 将用户名和密码翻入 Properties中
        Properties idPassword = new Properties();
        idPassword.setProperty("user", "root");       // 账号
        idPassword.setProperty("password", "123456"); // 密码

        Connection conn = driver.connect(url, idPassword);
        System.out.println(conn);
        conn.close();
    }

    // 连接方式2:使用反射动态加载, 灵活
    public void conn02() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();

        // 2.连接
        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        // 将用户名和密码翻入 Properties中
        Properties idPassword = new Properties();
        idPassword.setProperty("user", "root");       // 账号
        idPassword.setProperty("password", "123456"); // 密码
        Connection conn = driver.connect(url, idPassword);
        System.out.println(conn);
        conn.close();
    }

    // 连接方式3: 利用DriverManager 替换 Driver 进行统一管理
    public void conn03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();

        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        String user = "root";
        String password = "123456";

        // 注册驱动
        DriverManager.registerDriver(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
        conn.close();
    }

    // [荐]连接方式4: Class.forName  自动完成注册驱动   简化代码
    public void conn04() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver"); // 自动注册驱动  5.1.6之后可以省去此行

        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        String user = "root";
        String password = "123456";

        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
        conn.close();
    }

    // [荐]连接方式5: 把方式4 写成配置文件
    public void conn05() throws IOException, SQLException, ClassNotFoundException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("./conf/mysql.properties"));

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

        Class.forName(driver); // 加载驱动

        Connection conn = DriverManager.getConnection(url, user, password); // 连接
        System.out.println(conn);
        conn.close();
    }
}

3 ResultSet（查询语句）

package cn.xhh.jdbc_;

import java.sql.*;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.9.30
 */
public class Jdbc_select {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // 连接
        Class.forName("com.mysql.jdbc.Driver"); // 自动注册驱动  5.1.6之后可以省去此行
        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        String user = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement statement = conn.createStatement();

        /**
         * +------+------+
         * | id   | name |
         * +------+------+
         * |    0 | xhh  |
         * |  888 | mcy  |
         * |    2 | abc  |
         * |    3 | xyz  |
         * +------+------+
         */

        // 查询语句
        String sqlSelect = "SELECT id, name FROM news";
        ResultSet resultSet = statement.executeQuery(sqlSelect);


        // 使用while 取出数据  next 把光标下移动，如果没有返回false
        while (resultSet.next()){
            int id = resultSet.getInt(1);        // 当前行 获取第1列
            String name = resultSet.getString(2);// 当前行 获取第2列

            System.out.println("id:" + id + " name:" + name);
        }

        // 关闭
        resultSet.close();
        statement.close();
        conn.close();
    }
}

4 分析语句

4.1 Statement （存在Sql注入）

SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输
入数据中注入非法的SQL语句段或命令，恶意攻击数据库。

4.2 【荐】PreparedStatement（开发使用）

• 查询

// 输入的 账号密码
String inputName = "mcy";
String inputPassword = "3123";

String sql = "SELECT name FROM table_info WHERE name = ? AND password = ?"
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, inputName);
preparedStatement.setString(2, inputPassword);

//        preparedStatement.executeUpdate(); // 用于 增删改
ResultSet resultSet = preparedStatement.executeQuery();// 用于查询
if(resultSet.next()){
    System.out.println("登录成功");
}else {
    System.out.println("登录失败");
}

• dml语句

String sqlCRUD = "INSERT INTO admin VALUES(?, ?)";
PreparedStatement preSta = conn.prepareStatement(sqlCRUD);
preSta.setString(1, "xhh");
preSta.setString(2, "0678");
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "exec Success" : "exec Failed");

4.3 JDBC_API

• 连接工具类

package com.hspedu.jdbc.utils;

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

/**
 * 这是一个工具类，完成 mysql的连接和关闭资源
 */
public class JDBCUtils {
    //定义相关的属性(4个), 因为只需要一份，因此，我们做出static
    private static String user; //用户名
    private static String password; //密码
    private static String url; //url
    private static String driver; //驱动名

    //在static代码块去初始化
    static {

        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            //读取相关的属性值
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //在实际开发中，我们可以这样处理
            //1. 将编译异常转成 运行异常
            //2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
            throw new RuntimeException(e);

        }
    }

    //连接数据库, 返回Connection
    public static Connection getConnection() {

        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            //1. 将编译异常转成 运行异常
            //2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
            throw new RuntimeException(e);
        }
    }

    //关闭相关资源
    /*
        1. ResultSet 结果集
        2. Statement 或者 PreparedStatement
        3. Connection
        4. 如果需要关闭资源，就传入对象，否则传入 null
     */
    public static void close(ResultSet set, Statement statement, Connection connection) {

        //判断是否为null
        try {
            if (set != null) {
                set.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            //将编译异常转成运行异常抛出
            throw new RuntimeException(e);
        }

    }

}

# mysql.properties
# 用户名和密码
user=root
password=123456

url=jdbc:mysql://192.168.137.235:3306/xhh_db
driver=com.mysql.jdbc.Driver

4.4 事务（案例：转账）

package com.hspedu.jdbc.transaction_;

import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

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

/**
 * @author 韩顺平
 * @version 1.0
 * 演示jdbc 中如何使用事务
 */
public class Transaction_ {
    //事务来解决
    public void useTransaction() {

        //操作转账的业务
        //1. 得到连接
        Connection connection = null;
        //2. 组织一个sql
        String sql = "update account set balance = balance - 100 where id = 1";
        String sql2 = "update account set balance = balance + 100 where id = 2";
        PreparedStatement preparedStatement = null;
        //3. 创建PreparedStatement 对象
        try {
            connection = JDBCUtils.getConnection(); // 在默认情况下，connection是默认自动提交
            //将 connection 设置为不自动提交
            connection.setAutoCommit(false); //开启了事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate(); // 执行第1条sql

            int i = 1 / 0; //抛出异常
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate(); // 执行第3条sql

            //这里提交事务
            connection.commit();

        } catch (SQLException e) {
            //这里我们可以进行回滚，即撤销执行的SQL
            //默认回滚到事务开始的状态.
            System.out.println("执行发生了异常，撤销执行的sql");
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

4.5 批处理

# mysql.properties
# 用户名和密码
user=root
password=123456

url=jdbc:mysql://192.168.137.235:3306/xhh_db?rewriteBatchedStatements=true
driver=com.mysql.jdbc.Driver

package com.hspedu.jdbc.batch_;

import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

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

/**
 * @author 韩顺平
 * @version 1.0
 * 演示java的批处理
 */
public class Batch_ {

    //使用批量方式添加数据
    public void batch() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();//开始时间
        for (int i = 0; i < 5000; i++) {//5000执行
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            //将sql 语句加入到批处理包中 -> 看源码
            /*
            //1. //第一就创建 ArrayList - elementData => Object[]
            //2. elementData => Object[] 就会存放我们预处理的sql语句
            //3. 当elementData满后,就按照1.5扩容
            //4. 当添加到指定的值后，就executeBatch
            //5. 批量处理会减少我们发送sql语句的网络开销，而且减少编译次数，因此效率提高
             */
            preparedStatement.addBatch();
            //当有1000条记录时，在批量执行
            if((i + 1) % 1000 == 0) {//满1000条sql
                preparedStatement.executeBatch();
                //清空一把
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("批量方式 耗时=" + (end - start));//批量方式 耗时=108
        //关闭连接
        JDBCUtils.close(null, preparedStatement, connection);
    }
}

5 数据库连接池（开发使用）

5.1 简介

5.2 C3P0

• 方式1：设置参数（或者加载配置）

package cn.xhh.pool_;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.9.30
 */
public class C3P0_xhh {
    public static void main(String[] args) throws PropertyVetoException, SQLException {
        // [1] 创建数据源对象(连接池)
        ComboPooledDataSource cpds = new ComboPooledDataSource();

        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.137.235:3306/xhh_db";
        String user = "root";
        String password = "123456";

        // [2] 设置数据源
        cpds.setDriverClass(driver);
        cpds.setJdbcUrl(url);
        cpds.setUser(user);
        cpds.setPassword(password);

        // 设置初始连接数量和最大连接数
        cpds.setInitialPoolSize(6);
        cpds.setMaxPoolSize(20);

        // 获取连接
        Connection conn = cpds.getConnection();

        // do something ...
        // .....
        System.out.println("Connect ... OK");
        // 释放连接 (放回连接池)
        conn.close();
    }
}

• 方式2：利用配置文件模板

c3p0-config.xml
放在 src/

<c3p0-config>
    <!-- 数据源名称代表连接池 -->
  <named-config name="xhh_mysql_conf">
<!-- 驱动类 -->
  <property name="driverClass">com.mysql.jdbc.Driver</property>
  <!-- url-->
  	<property name="jdbcUrl">jdbc:mysql://192.168.137.235:3306/xhh_db</property>
  <!-- 用户名 -->
  		<property name="user">root</property>
  		<!-- 密码 -->
  	<property name="password">hsp</property>
  	<!-- 每次增长的连接数-->
    <property name="acquireIncrement">5</property>
    <!-- 初始的连接数 -->
    <property name="initialPoolSize">10</property>
    <!-- 最小连接数 -->
    <property name="minPoolSize">5</property>
   <!-- 最大连接数 -->
    <property name="maxPoolSize">50</property>

	<!-- 可连接的最多的命令对象数 -->
    <property name="maxStatements">5</property> 
    
    <!-- 每个连接对象可连接的最多的命令对象数 -->
    <property name="maxStatementsPerConnection">2</property>
  </named-config>
</c3p0-config>

package cn.xhh.pool_;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.9.30
 */
public class C3P0_xhh {
    public static void main(String[] args) throws SQLException {
        // [1] 创建数据源对象(连接池)
        ComboPooledDataSource cpds = new ComboPooledDataSource("xhh_mysql_conf");

        // 获取连接
        Connection conn = cpds.getConnection();

        // do something ...
        // .....
        System.out.println("Connect ... OK");
        // 释放连接 (放回连接池)
        conn.close();
    }
}

5.3 【荐】Druid

配置文件druid.properties放在src/

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.137.235:3306/xhh_db?rewriteBatchedStatements=true
username=root
password=123456

#initial connection Size  初始连接数
initialSize=10
#min idle connecton size  最小连接数
minIdle=5
#max active connection size 最大连接数
maxActive=50
#max wait time (5000 mil seconds) 最大等待事件
maxWait=5000

package cn.xhh.pool_;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.9.30
 */
public class Druid_xhh {
    public static void main(String[] args) throws Exception {
        // 1.加入 Druid jar包
        // 2.加入配置文件
        // 读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/druid.properties"));

        // 3.创建连接数数据源,指定配置文件   (连接池)
        DataSource ds = DruidDataSourceFactory.createDataSource(properties);

        Connection conn = ds.getConnection();
        System.out.println("Success!");
        conn.close();
    }
}

5.4 【荐】JDBCUtilsByDruid

package com.hspedu.jdbc.datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * 基于druid数据库连接池的工具类
 */
public class JDBCUtilsByDruid {

    private static DataSource ds;

    //在静态代码块完成 ds初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src/druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //编写getConnection方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //关闭连接, 老师再次强调： 在数据库连接池技术中，close 不是真的断掉连接
    //而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {

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

6 【荐】DBUtils（Apache）

6.1 产生的原因

局部连接返回的ResultSet，在关闭的时候数据集就释放了
解决：利用ArrayList< Actor>进行存储

6.2 自定义版本

定义一个和数据库映射的类
把ResultSet集合中的数据放入ArrayList集合
关闭ResultSet等等资源

package com.hspedu.jdbc.datasource;

import java.util.Date;

/**
 * Actor 对象和 actor表的记录对应
 *
 */
public class Actor { //Javabean, POJO, Domain对象

    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor() { //一定要给一个无参构造器[反射需要]
    }

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

    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 Date getBorndate() {
        return borndate;
    }

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

    public String getPhone() {
        return phone;
    }

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

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

//遍历该结果集
while (set.next()) {
	int id = set.getInt("id");
	String name = set.getString("name");//getName()
	String sex = set.getString("sex");//getSex()
	Date borndate = set.getDate("borndate");
	String phone = set.getString("phone");
	//把得到的resultset 的记录，封装到 Actor对象，放入到list集合
	list.add(new Actor(id, name, sex, borndate, phone));
}

6.3 库Apache–DBUtils（QueryRunner线程安全的）

package com.hspedu.jdbc.datasource;


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 org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 韩顺平
 * @version 1.0
 */
@SuppressWarnings({"all"})
public class DBUtils_USE {

    //使用apache-DBUtils 工具类 + druid 完成对表的crud操作
    @Test
    public void testQueryMany() throws SQLException { //返回结果是多行的情况

        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 就可以执行相关的方法，返回ArrayList 结果集
        //String sql = "select * from actor where id >= ?";
        //   注意: sql 语句也可以查询部分列
        String sql = "select id, name from actor where id >= ?";
        // 老韩解读
        //(1) query 方法就是执行sql 语句，得到resultset ---封装到 --> ArrayList 集合中
        //(2) 返回集合
        //(3) connection: 连接
        //(4) sql : 执行的sql语句
        //(5) new BeanListHandler<>(Actor.class): 在将resultset -> Actor 对象 -> 封装到 ArrayList
        //    底层使用反射机制 去获取Actor 类的属性，然后进行封装
        //(6) 1 就是给 sql 语句中的? 赋值，可以有多个值，因为是可变参数Object... params
        //(7) 底层得到的resultset ,会在query 关闭, 关闭PreparedStatment
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        System.out.println("输出集合的信息");
        for (Actor actor : list) {
            System.out.print(actor);
        }

        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象)
    @Test
    public void testQuerySingle() throws SQLException {

        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 就可以执行相关的方法，返回单个对象
        String sql = "select * from actor where id = ?";
        // 老韩解读
        // 因为我们返回的单行记录<--->单个对象 , 使用的Hander 是 BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 10);
        System.out.println(actor);

        // 释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //演示apache-dbutils + druid 完成查询结果是单行单列-返回的就是object
    @Test
    public void testScalar() throws SQLException {

        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();

        //4. 就可以执行相关的方法，返回单行单列 , 返回的就是Object
        String sql = "select name from actor where id = ?";
        //老师解读： 因为返回的是一个对象, 使用的handler 就是 ScalarHandler
        Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 4);
        System.out.println(obj);

        // 释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //演示apache-dbutils + druid 完成 dml (update, insert ,delete)
    @Test
    public void testDML() throws SQLException {

        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();

        //4. 这里组织sql 完成 update, insert delete
        //String sql = "update actor set name = ? where id = ?";
        //String sql = "insert into actor values(null, ?, ?, ?, ?)";
        String sql = "delete from actor where id = ?";

        //老韩解读
        //(1) 执行dml 操作是 queryRunner.update()
        //(2) 返回的值是受影响的行数 (affected: 受影响)
        //int affectedRow = queryRunner.update(connection, sql, "林青霞", "女", "1966-10-10", "116");
        int affectedRow = queryRunner.update(connection, sql, 1000 );
        System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响到表");

        // 释放资源
        JDBCUtilsByDruid.close(null, null, connection);

    }
}

7 BasicDAO

data access object

7.1 简介

7.2 开发BasicDAO

• druid.properties

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.137.235:3306/xhh_db?rewriteBatchedStatements=true
username=root
password=123456

#initial connection Size  初始连接数
initialSize=10
#min idle connecton size  最小连接数
minIdle=5
#max active connection size 最大连接数
maxActive=50
#max wait time (5000 mil seconds) 最大等待事件
maxWait=5000

• JDBCUtilsByDruid

package cn.xhh.daotest.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;

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


/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.10.2
 */
public class JDBCUtilsByDruid {
    private static DataSource ds;

    //在静态代码块完成 ds初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src/druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //编写getConnection方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //关闭连接, 老师再次强调： 在数据库连接池技术中，close 不是真的断掉连接
    //而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {

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

• BasicDAO

package cn.xhh.daotest.dao;

import cn.xhh.daotest.utils.JDBCUtilsByDruid;
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;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.10.2
 */
public class BasicDAO<T> {


     private QueryRunner qr = new QueryRunner();

     // 开发通用的dml
    public int update(String sql, Object... parameters){

        Connection conn = null;

        try {
            conn = JDBCUtilsByDruid.getConnection();

            return qr.update(conn, sql, parameters);

        } catch (SQLException e) {
//            e.printStackTrace();
            throw  new RuntimeException(e); //
        }finally {
            JDBCUtilsByDruid.close(null, null, conn);
        }
    }

    // 完成返回多个对象 针对任意的表

    /**
     *
     * @param sql
     * @param classT  Actor.class
     * @param parameters  传入?
     * @return
     */

    // 查询多行
    public List<T> queryMulti(String sql, Class<T> classT, Object... parameters){

        Connection conn = null;

        try {
            conn = JDBCUtilsByDruid.getConnection();
            return qr.query(conn, sql, new BeanListHandler<T>(classT), parameters);

        } catch (SQLException e) {
            throw  new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null, null, conn);
        }
    }


    // 查询单行
    public T queryLine(String sql, Class<T> classT, Object... parameters){

        Connection conn = null;

        try {
            conn = JDBCUtilsByDruid.getConnection();
            return qr.query(conn, sql, new BeanHandler<>(classT), parameters);

        } catch (SQLException e) {
            throw  new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null, null, conn);
        }
    }


    // 查询Scalar
    public Object queryScalar(String sql, Object... parameters){

        Connection conn = null;

        try {
            conn = JDBCUtilsByDruid.getConnection();
            return qr.query(conn, sql, new ScalarHandler(), parameters);

        } catch (SQLException e) {
            throw  new RuntimeException(e);
        }finally {
            JDBCUtilsByDruid.close(null, null, conn);
        }
    }
}

• ActorDAO

package cn.xhh.daotest.dao;

import cn.xhh.daotest.domain.Actor;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.10.2
 */
public class ActorDAO extends BasicDAO<Actor>{
    // 1.有公用方法

    // 2.可以添加个性化方法
}

• Actor

package cn.xhh.daotest.domain;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.10.2
 */
public class Actor {

    // need
    public Actor() {
    }


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

    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;
    }

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

    private Integer id;
    private String name;
    private String sex;
}

• TestActorDao

package cn.xhh.daotest.test;

import cn.xhh.daotest.dao.ActorDAO;
import cn.xhh.daotest.domain.Actor;

import java.util.List;

/**
 * @author : xhh
 * @email : xhh0608@foxmail.com
 * @date : 2021.10.2
 */
public class TestActorDao {
    public static void main(String[] args) {
        ActorDAO actorDAO = new ActorDAO();

        // 测试查询
        String sql = "SELECT * FROM actor WHERE sex=? AND name=?";
        List<Actor> actors = actorDAO.queryMulti(sql, Actor.class, "G", "mcy");

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

//        // 测试插入
//        sql = "INSERT INTO actor VALUES(?, ?, ?)";
//        int update = actorDAO.update(sql, null, "abc", "G");
//        System.out.println(update > 0 ? "Insert Success" : "Insert Failed");

        // 查询Line
        sql = "SELECT * FROM actor WHERE id=?";
        Actor actor = actorDAO.queryLine(sql, Actor.class, 1);
        System.out.println(actor);

        // 查询姓名
        sql = "SELECT ac.name FROM actor AS ac WHERE id=?";
        Object o = actorDAO.queryScalar(sql, 1);
        System.out.println(o);
    }
}

---

参考

[1] 韩顺平-零基础30天学会Java