JDBC入门

JDBC全称Java Database connectivity，翻译过来是数据库连接，可以帮助我们在Java应用中用于和MySQL、Oracle等关系型数据库进行交互操作

一、什么是JDBC

• 全称是Java数据库连接：Java Database connectivity
• 根本目的就是让Java通过程序操作关系型数据库
• 核心就是基于驱动程序实现与数据库的连接与操作

二、JDBC开发流程

* 1、加载并注册JDBC驱动
* 2、创建数据库连接
* 3、创建Statement对象
* 4、遍历查询结果
* 5、关闭连接，释放资源

    public static void main(String[] args) {

        Connection conn = null;
        try {
            // 1、加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2、创建数据库连接
            String url = "jdbc:mysql://localhost:3306/imooc1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            conn = DriverManager.getConnection(url, "root", "123456");
            // 3、创建Statement对象（存在sql注入风险，改用PreparedStatement，下面有介绍）
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from employee");
            // 4、遍历查询结果
            while (rs.next()) {
                int eno = rs.getInt(1);
                String ename = rs.getString("ename");
                float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5、关闭连接，释放资源
            try {
                if (conn != null && conn.isClosed() == false) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

1、JDBC驱动jar包下载链接

https://dev.mysql.com/downloads/connector/j/
Platform Independent 表示独立系统，比如 MAC

2、Class.forName的作用

• Class.forName用于加载指定的JDBC驱动类
• Class.forName本质是通知JDBC注册这个驱动类
• 驱动由数据库厂商自行开发，连接字符串也不同

数据库	JDBC驱动类	连接字符串
MySQL 5	com.mysql.jdbc.Driver	jdbc:mysql://主机ip:端口/数据库名
MySQL 8	com.mysql.cj.jdbc.Driver	jdbc:mysql://主机ip:端口/数据库名
Oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@主机ip:端口:数据库名
SQL Server	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:microsoft:sqlserver:主机ip:端口;databasename=数据库名

3、DriverManager的作用

1、DriverManager 用于注册/管理JDBC驱动程序
2、DriverManger.getConnection(连接字符串,用户名,密码)
3、返回值 Connection 对象，对应数据库的物理网络连接

4、Connection对象

1、Connection 对象用于JDBC与数据库的网络通信对象
2、java.sql.Connection 是一个接口，具体由驱动厂商实现
3、所有数据库的操作都建立在 Connection 基础上

5、MySQL连接字符串

1、格式：jdbc:mysql://[主机ip][:端口]/数据库名?参数列表
2、主机ip与端口是可选设置，默认值为 127.0.0.1 与 3306
3、参数列表采用url编码，格式：参数1=值1&参数2=值2&....

下面是MySQL连接字符串常用参数

参数名	建议参数值	说明
useSSL	true(生产)/false(开发)	是否禁用ssl
useUnicode	true	启用unicode编码传输数据
characterEncoding	UTF-8	使用UTF-8编码传输数据
serverTimezone	Asia/Shanghai	使用东8时区时间，UTC+8
allowPublicKeyRetrieval	true	允许从客户端获取公钥加密传输

三、PreparedStatement

1、PreparedStatement预编译Statement是Statement的子接口
2、PreparedStatement对SQL进行参数化，预防SQL注入攻击
3、PreparedStatement比Statement执行效率更高

注：SQL注入攻击是指利用SQL漏洞越权获取数据的黑客行为，SQL注入攻击根源是未对原始SQL中的敏感字符做特殊处理解决，方法：放弃Statement改用PreparedStatement处理SQL

// 下面代码存在SQL注入风险
// dname值为 ' or 1=1 or 1= '，所有筛选条件均失效
// SQL：select * from employee where dname = '' or 1=1 or 1=''
String sql = "select * from employee where dname = '" + pdname + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
    ....
}

// 利用PreparedStatement预防SQL注入风险
// dname值为 ' or 1=1 or 1= '，查询不到任何结果
// SQL：select * from employee where dname = '\' or 1=1 or 1='\'
String sql = "select * from employee where dname = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dname); // 设置SQL参数，从1开始
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    ....
}

四、事务

1、事务是以一种可靠的、一致的方式，访问和操作数据库的程序单元
翻译成人话就是：要么把事情做完，要么什么都不做，不要做一半
2、事务依赖于数据库实现，MySQL通过 事务区 作为数据缓冲地带

JDBC允许两种事务模式

1、自动提交事务模式

1、自动提交模式是指每执行一次写操作SQL，自动提交事务
2、自动提交开启方法：conn.setAutoCommit(true)
3、自动事务是JDBC默认行为，此模式无法保证多数据一致性

2、手动提交事务模式

1、手动提交模式是指显式调用commit()与rollback()方法管理事务
2、手动提交开启方法：conn.setAutoCommit(false)
3、手动提交事务可保证多数据一致性，但必须手动调用提交/回滚方法

五、批处理

需要反复执行，并且一次性插入很多数据的操作通常使用批处理

/**
 * 批处理
 */
public class BatchSample {

    // 标准方式插入若干数据
    public static void tc1() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            long startTime = new Date().getTime();
            conn = DbUtils.getConnection();
            // JDBC默认使用自动提交模式
            conn.setAutoCommit(false);
            String sql = "insert into employee(eno, ename, salary, dname) values(?, ?, ?, ?)";
            for (int i = 20000; i < 30000; i++) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "李强" + i);
                pstmt.setFloat(3, 1000);
                pstmt.setString(4, "市场部");
                pstmt.executeUpdate();
            }
            // 手动提交
            conn.commit();
            long endTime = new Date().getTime();
            System.out.println("tc1处理时长：" + (endTime - startTime));
            System.out.println("插入成功");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }

    // 使用批处理方式插入若干数据
    public static void tc2() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            long startTime = new Date().getTime();
            conn = DbUtils.getConnection();
            // JDBC默认使用自动提交模式
            conn.setAutoCommit(false);
            String sql = "insert into employee(eno, ename, salary, dname) values(?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            for (int i = 10000; i < 20000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "李强" + i);
                pstmt.setFloat(3, 1000);
                pstmt.setString(4, "市场部");
                pstmt.addBatch(); // 将参数加入批处理任务
            }
            pstmt.executeBatch(); // 执行批处理任务
            // 手动提交
            conn.commit();
            long endTime = new Date().getTime();
            System.out.println("tc2处理时长：" + (endTime - startTime));
            System.out.println("插入成功");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }

    public static void main(String[] args) {

        tc1();
        tc2();

        /** 
         * 打印结果如下：
         * tc1处理时长：6585
         * 插入成功
         * tc2处理时长：874
         * 插入成功
         */
    }
}

六、阿里巴巴Druid连接池

• Druid是阿里巴巴开源连接池组件，目前最好用的连接池之一
• Druid对数据库连接进行有效管理与重用，最大化程序执行效率
• 连接池负责创建管理连接，程序只负责取用与归还

使用步骤

1、下载jar包，放入工程中

下载地址：https://repo1.maven.org/maven2/com/alibaba/druid/1.2.11/

2、在 src 目录下创建 druid-config.properties文件，用来保存JDBC执行时必要的信息

// JDBC驱动类
driverClassName=com.mysql.cj.jdbc.Driver
// 连接字符串
url=jdbc:mysql://localhost:3306/imooc1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
// 用户名
username=root
// 密码
password=123456
// 初始数据库连接
initialSize=20
// 最大数据库连接
maxActive=20

3、编码阶段，分三步

        // 1、加载属性文件
        Properties properties = new Properties();
        // 获取在当前类路径下对应文件的路径
        String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();

        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
        } catch (Exception e) {
            e.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 2、获取DataSource数据源对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            // 3、创建数据库连接
            conn = dataSource.getConnection();
            String sql = "select * from employee limit 0,10";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Integer eno = rs.getInt("eno");
                System.out.println("员工编号：" + eno);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            /**
             * 不使用连接池：conn.close()关闭连接
             * 使用连接池：conn.close()将连接回收至连接池
             */
            DbUtils.closeConnection(rs, pstmt, conn);
        }

七、Apache Commons DBUtils

• commons-dbutils 是 Apache 提供的开源 JDBC 工具类库
• 它是对 JDBC 的简单封装，学习成本极低
• 使用 commons-dbutils 可以极大简化 JDBC 编码工作量

1、jar包下载链接

https://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

2、Apache Commons DbUtils + Druid 联合使用

    /**
     * 查询
     */
    private static void query() {
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();

        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            QueryRunner qr = new QueryRunner(dataSource);
            List<Employee> list = qr.query("select * from employee limit ?,10",
                    new BeanListHandler<>(Employee.class),
                    new Object[]{10});
            for (Employee emp : list) {
                System.out.println(emp.getEname());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新
     */
    private static void update() {
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();

        Connection conn = null;
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            String sql1 = "update employee set salary = salary + 1000 where eno = ?";
            String sql2 = "update employee set salary = salary - 500 where eno = ?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn, sql1, new Object[]{1000});
            qr.update(conn, sql2, new Object[]{1001});
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }