18. JDBC

开发步骤

  1. 在 SE 阶段,在项目路径下新建 libs 文件夹专门存放 jar 包

  1. 编写代码
所设计到的 API :
1. Connection 接口,代表连接
2. DriverManger,驱动管理类
    getConnection(xx),获取链接
3. Statement
    executeUpdate(sql),执行更新类的SQL
    executeQuery(sql),执行查询,返回 ResultSet 类型
4. ResultSet
    next(),判断是否还有数据
    getXXX(),获取数据
package com.itguigu.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class TestJDBC {
    /**
     * 新增
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void test1() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动,加载驱动类到内存中,即内存中有驱动类的 Class 对象

        // Class.forName("com.gjt.mm.mysql.Driver");  旧版本
        Class.forName("com.mysql.jdbc.Driver");  // 新版驱动

        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 执行 SQL
        String sql = "insert into xxx values(null, '1', '2')";
        Statement statement = connection.createStatement();
        int rows = statement.executeUpdate(sql);
        System.out.println("影响行数:" + rows);

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

    @Test
    public void test2() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动,加载驱动类到内存中,即内存中有驱动类的 Class 对象

        Class.forName("com.mysql.jdbc.Driver");  // 新版驱动

        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 执行 SQL
        String sql = "select * from xxx";
        Statement statement = connection.createStatement();
        ResultSet executeQuery = statement.executeQuery(sql);
        while (executeQuery.next()) {
            int id = executeQuery.getInt("id");            // 根据字段名和类型取结果
            String name = executeQuery.getString("name");  // 根据字段名和类型取结果
            String age = executeQuery.getString(3);        // 根据字段位置和类型取结果
            Object salary = executeQuery.getObject(4);     // 根据字段位置,但不知道类型获取结果
        }

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

使用 Statement 存在的问题有:1. 需要拼接 SQL,2. 不能防止 SQL 注入,3. 不能插入 blob 等二进制类型数据。

PreparedStatement

PreparedStatement 能解决上面 Statement 的问题。Statement 是 PreparedStatement 的父接口。

package com.itguigu.mysql;

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

import org.junit.Test;

public class TestPrepare {
    @Test
    public void test1() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动,加载驱动类到内存中,即内存中有驱动类的 Class 对象

        Class.forName("com.mysql.jdbc.Driver");  // 新版驱动

        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 执行 SQL
        String sql = "insert into xxx(name, age, salary) values(?, ?, ?)";
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        // 以前的方式为 Statement statement = connection.createStatement();
        prepareStatement.setObject(1, "zhangsan"); // 不知道类型就按照位置 setObject 
        prepareStatement.setObject(2, 20);
        prepareStatement.setObject(3, 30000);
        int len = prepareStatement.executeUpdate(); // 返回影响行数

        prepareStatement.close();
        connection.close();
    }
}

获取自增主键的值

Statement 中有一个常量值 RETURN_GENERATED_KEYS, 可在创建 PreparedStatement 的设置,让其返回自增主键的值。

package com.itguigu.mysql;

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

import org.junit.Test;

public class TestPrepare {
    @Test
    public void test1() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动,加载驱动类到内存中,即内存中有驱动类的 Class 对象

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

        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 执行 SQL
        String sql = "insert into xxx(name, age, salary) values(?, ?, ?)";

        // 设置返回自增主键 
        PreparedStatement prepareStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        prepareStatement.setObject(1, "zhangsan");
        prepareStatement.setObject(2, 20);
        prepareStatement.setObject(3, 30000);
        int len = prepareStatement.executeUpdate();

        // 获取自增的主键的值,封装在 ResultSet 结果集中
        ResultSet generatedKeys = prepareStatement.getGeneratedKeys(); 
        if(generatedKeys.next()) {
            System.out.println(generatedKeys.getObject(1));
        }

        generatedKeys.close();
        prepareStatement.close();
        connection.close();
    }
}

批处理

  1. addBatch, 将 SQL 添加到批处理命令中,进行缓存。如果缓冲区满了之后会自动执行。
  2. excuteBatch, 执行批处理语句

注意:MySQL 中是默认不进行批处理的(即速度很慢),需要在连接的时候加上 rewriteBatchedStatements = true。另外使用 insert 插入的时候应该使用 values 而不是 value,不然也会导致批处理速度变慢。

package com.itguigu.mysql;

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

import org.junit.Test;

public class TestPrepare {
    @Test
    public void test1() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动,加载驱动类到内存中,即内存中有驱动类的 Class 对象

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

        // 2. 获取连接, 需要加上 rewriteBatchedStatements 批处理速度才能快
        String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements = true";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 执行 SQL, 这里使用 value 也能进行插入,但是速度很慢。所以使用 values
        String sql = "insert into xxx(name, age, salary) values(?, ?, ?)";

        PreparedStatement prepareStatement = connection.prepareStatement(sql);

        for (int i = 0; i < 1000; i++) {
            prepareStatement.setObject(1, "value" + i);
            prepareStatement.setObject(2, 20 + i);
            prepareStatement.setObject(3, 30000 + i);
            prepareStatement.addBatch();  // 将 SQL 进行缓存
        }
        // 执行批处理缓存,返回的是每一个处理的结果,所以是数组
        int[] executeBatch = prepareStatement.executeBatch();

        prepareStatement.close();
        connection.close();
    }
}

事务

MySQL 是自动提交事务的,执行一句,提交一句。使用事务时需要注意:

  1. 同一个事务必须保证使用的是同一个 Connection 对象
  2. 在执行 SQL 之前,设置自动提交为 false
  3. 如果没问题则进行 commit,有问题则 rollback。
  4. 进行连接关闭前,需要将自动提交还原为以前的 true。
package com.itguigu.mysql;

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

import org.junit.Test;

public class TestPrepare {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver"); 

            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "123456";
            connection = DriverManager.getConnection(url, user, password);
            // 设置自动更新为 false
            connection.setAutoCommit(false);

            // 将两个操作放到一个事务当中
            insert(connection);
            update(connection);

            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                // 将自动更新还原
                connection.setAutoCommit(true);
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void insert(Connection connection) throws Exception {
        String sql = "insert into xxx(name, age, salary) values(?, ?, ?)";
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        prepareStatement.setObject(1, "1");
        prepareStatement.setObject(2, "2");
        prepareStatement.setObject(3, "3");

        prepareStatement.executeUpdate();
        prepareStatement.close();
    }

    public static void update(Connection connection) throws Exception {
        String sql = "update xxx set name = ? where id =?";
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        prepareStatement.setObject(1, "zhangsan");
        prepareStatement.setObject(2, "2");

        prepareStatement.executeUpdate();
        prepareStatement.close();
    }
}

连接池 JDBCUtils

使用阿里云德鲁伊(Druid)。步骤如下:

  1. 引入 jar 包

  2. 增加配置文件

    在 src 下新建 druid.properties 文件,输入以下配置

    url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
    username=root
    password=123456
    driverClassName=com.mysql.jdbc.Driver
    initialSize=10
    maxActive=20
    maxWait=1000
    
  3. 创建连接池

package com.itguigu.mysql;

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

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;


public class JDBCUtils {
    private static DataSource ds;
    private static ThreadLocal<Connection> local;

    static {
        local = new ThreadLocal<>();

        // 静态代码块中加载配置文件,创建 DataSource
        Properties properties = new Properties();
        try {
            properties.load(JDBCUtils.class.getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e1) {
            e1.printStackTrace();
        }
    }

    // 简单写法,但是保证同一个线程共享一个连接对象,如果有事务需要处理那么就会出现问题
    /**
    public Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    */

    // 上面的写法不能保证同一个线程获取到的是同一个连接对象,所以这里使用 ThreadLocal
    public static Connection getConnection() throws SQLException {
        // 如果 ThreadLocal 中没有则获取,有则返回
        Connection connection = local.get();
        if (connection==null) {
            connection = ds.getConnection();
            local.set(connection);
        }
        return connection;
    }

    public static void Close() throws SQLException {
        Connection connection = local.get();
        if (connection!=null) {
            local.remove();
            // 还原自动提交
            connection.setAutoCommit(true);
            connection.close();    
        }
    }
}

使用 JDBCUtils

package com.itguigu.mysql;

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

public class TestJDBCUtils {
    public static void main(String[] args) throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        String sql = "insert into xxx(name, age, salary) values(?, ?, ?)";
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        prepareStatement.setObject(1, "zhangsan");
        prepareStatement.setObject(2, 20);
        prepareStatement.setObject(3, 30000);

        prepareStatement.close();
        JDBCUtils.Close();
    }
}

抽取 BasicDAO

在现实业务中,很多业务代码都是重复的,例如员工的增删改查,部门的增删改查等,我们可以使用反射等技术,将通用的代码逻辑进行封装。执行查询后的 ResultSet 可以获取到结果的元对象,可以通过元对象获取到结果集的列数(即 JavaBean 有多少个属性,知道有多少后我们就可以给每一个属性赋值),或者根据位置获取列名称 (获取每一列的名称)

package com.itguigu.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import com.itguigu.mysql.JDBCUtils;

public class BasicDAO {
    /**
     * 支持 insert,update,delete 
     * @param sql 相关的 sql 语句
     * @param args 相关参数
     * @return 
     * @throws Exception
     */
    public int update(String sql, Object...args) throws Exception {
        // 获取连接
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        // 不知道多少个参数和类型,所以设置 Object 的可变参数
        if (args!=null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                // prepareStatement 设置值下标从1开始
                prepareStatement.setObject(i+1, args[i]);
            }
        }
        // 执行
        int executeUpdate = prepareStatement.executeUpdate();
        // 关闭相关资源
        prepareStatement.close();
        JDBCUtils.Close();
        return executeUpdate;
    }

    /**
     * 获取所有数据
     * @param clazz 哪个对象的,例如员工,部门
     * @param sql 查询的 SQL
     * @param args SQL 的变量值
     * @return
     * @throws Exception
     */
    public <T> ArrayList<T> getAllData(Class<T> clazz, String sql, Object...args) throws Exception{
        // 获取连接
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        // 不知道多少个参数和类型,所以设置 Object 的可变参数
        if (args!=null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                // prepareStatement 设置值下标从1开始
                prepareStatement.setObject(i+1, args[i]);
            }
        }
        // 执行
        ResultSet resultSet = prepareStatement.executeQuery(sql);
        // 需要将 resultSet 结果集封装在一个 Java bean 中去
        ArrayList<T> resultList = new ArrayList<>();

        // ResultSet 可以获取到结果的元对象,可以通过元对象获取到结果集的列数,或者根据位置获取列名称
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();  // 获取到结果集的列数

        // 反射创建对象
        T instance = clazz.newInstance();

        // 创建 Bean 对象
        for (int i = 0; i < columnCount; i++) {

            // 反射为对象属性赋值(通过元数据的 getColumnLabel 可以根据位置获取列名称)
            String columnNmae = metaData.getColumnLabel(i+1);
            // 获取到 Filed 对象
            Field field = clazz.getDeclaredField(columnNmae);
            // 为属性赋值
            field.setAccessible(true);
            field.set(instance, resultSet.getObject(i+1));
        }
        resultList.add(instance);

        // 关闭相关资源
        prepareStatement.close();
        JDBCUtils.Close();
        return resultList;
    }
}

使用 BasicDAO

新建 Deparment Java Bean

package com.itguigu.bean;

public class Department {
    private String name;
    private String desc;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getDesc() {
        return desc;
    }
    public void setDesc(String desc) {
        this.desc = desc;
    }
    @Override
    public String toString() {
        return "Department [name=" + name + ", desc=" + desc + "]";
    }
    public Department(String name, String desc) {
        super();
        this.name = name;
        this.desc = desc;
    }
    public Department() {
        super();
    }
}

新建 DepartmentDAO 接口

package com.itguigu.department;

import java.util.ArrayList;

import com.itguigu.bean.Department;

interface DepartmentDAO {
    void add(Department department);
    ArrayList<Department> getAll();
    void delete(String name);
}

新建 DepartmentDAOImpl 继承自 BasicDAO 和实现 DepartmentDAO 接口

package com.itguigu.department;

import java.util.ArrayList;

import com.itguigu.bean.Department;
import com.itguigu.dao.BasicDAO;

public class DepartmentDAOImpl extends BasicDAO implements DepartmentDAO{

    @Override
    public void add(Department department) {
        String sql = "insert into department(name, desc) values(?, ?)";
        try {
            update(sql, department.getName(), department.getDesc());
        } catch (Exception e) {
            // 这里是直接创建一个运行时异常,这样的好处是将编译时候的运行转换成了运行时异常
            // 这样上层在使用的时候就不需要处理这个异常了
            throw new RuntimeException(e.getMessage());
        }
    }

    @Override
    public ArrayList<Department> getAll() {
        String sql = "select * from department";
        try {
            // 这里可变参数 agrs 没有,则不用传递
            ArrayList<Department> allData = getAllData(Department.class, sql);
            return allData;
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    @Override
    public void delete(String name) {
        String sql = "delete from department where name = ?";
        try {
            update(sql, name);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }
}

新建测试类

package com.itguigu.department;

import java.util.ArrayList;

import org.junit.Test;

import com.itguigu.bean.Department;

public class TestDeparmentDAOImpl {
    @Test
    public void testAdd() {
        Department department = new Department("运行", "运行部");
        DepartmentDAOImpl departmentDAOImpl = new DepartmentDAOImpl();
        departmentDAOImpl.add(department);
    }

    @Test
    public void testGetAll()  {
        DepartmentDAOImpl departmentDAOImpl = new DepartmentDAOImpl();
        ArrayList<Department> allData = departmentDAOImpl.getAll();
        for (Department department : allData) {
            System.out.println(department);
        }
    }

    @Test
    public void testDel()  {
        DepartmentDAOImpl departmentDAOImpl = new DepartmentDAOImpl();
        departmentDAOImpl.delete("运行部");
    }
}

项目结构如图所示

注意⚠️:在书写 java bean 的时候,属性的类型必须使用包装类,因为数据库中的所有类型,包括整数等类型都可能是 null,而在 java 中只有引用数据类型才能赋值为 null,基本数据类型是不可以的,所以在书写 java bean 的时候,属性的类型必须使用包装类。

Apache DBUtils

Apache DBUtils 包中提供了主要的 DBUtils 类和 QueryRunner 类,DBUtils 的功能主要是对连接的关闭操作。QueryRunner 主要提供了 增删改查,事务共用 connection 等方法。我们使用 Apache DBUtils 重新实现 BasicDao。学习 Apache DBUtils 的目的在于理解数据如何从数据库映射到一个对象,一个数据对象如果转换成一条数据库数据,理解里面的工作原理 ——— 反射。

Apache DBUtils 中提供了结果集处理器的接口 ResultSetHandler,在调用 QueryRunner 的 query 方法时,可以传入对应的结果集处理实现类。常见的实现类有

  1. BeanListHandler,结果集是一个 List
  2. BeanHandler,结果是一个 Java Bean 对象。
  3. MapHandler,结果是一行队列,但不是 Java Bean 对象
  4. MapListHandler,结果是多行多列,但不是 Java Bean 对象
  5. ScalarHandler,单个值的封装对象
package com.itguigu.dao;

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

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.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class BasicDAO2 {
    private QueryRunner queryRunner;

    public int update(String sql, Object...args) throws Exception {
        // QueryRunner 中提供了 update 方法。
        int update = queryRunner.update(sql, args);
        return update;
    }

    // 如果要共享同一个 Connection 连接,可以用以下方法
    public int update(Connection connection, String sql, Object...args) throws Exception {
        int update = queryRunner.update(connection, sql, args);
        return update;
    }

    // 查询结果为多个 JavaBean
    public <T> List<T> getList(Class<T> clazz, String sql, Object...args) throws Exception{
        return queryRunner.query(sql, new BeanListHandler<T>(clazz), args);
    }

    // 查询结果为一个 JavaBean
    public <T> T getBean(Class<T> clazz, String sql, Object...args) throws Exception{
        return queryRunner.query(sql, new BeanHandler<T>(clazz), args);
    }

    // 查询单个值,例如总数,最大值,最小值
    public Object queryObject(String sql, Object...args) throws Exception {
        return queryRunner.query(sql, new ScalarHandler<>(), args);
    }

    // 查询结果为多行多列
    public List<Map<String, Object>> queryListMap(String sql, Object...args) throws Exception {
        return queryRunner.query(sql, new MapListHandler(), args);
    }

    public Map<String, Object> queryMap(String sql, Object...args) throws Exception {
        return queryRunner.query(sql, new MapHandler(), args);
    }
}

 上一篇
19. Servlet 19. Servlet
使用 Servlet 创建一个类,实现 Servlet 接口 在 web.xml 中注册这个实现类 Tomcat (Servlet 容器)会创建实现对象 Servlet 执行流程 发送请求 匹配 web.xml 中的 url 根据 url
2019-12-29
下一篇 
17. Lambda 17. Lambda
Lambda只有 SAM接口(函数式接口【类中只有一个抽象方法的接口,可以有其他成员,但是抽象方法只有一个】)才能使用 Lambda 表达式,Lambda 表达式的作用就是简化代码,使代码更整洁。 函数式接口现在已学的 SAM 接口: j
2019-12-12
  目录