JDBC操作全攻略

JDBC操作全攻略

JDBC简介

JDBC(Java Database Connectivity),也称为Java数据库连接，是Java用于连接数据库的接口规范，需要注意的是，JDBC并没有提供的实现，具体的实现是由数据库提供商，比如MySQL，Oracle等负责提供，JDBC提供了一整套完整的连接规范，包括了Driver，DriverManager，Connection，Statement，PreparedStatement,ResultSet，DatabaseMetaData，ResultSetMetaData，ParameterMetaData等等

连接数据库

在Java程序中，操作数据库时，通常需要几个步骤
1. 导入对应的JDBC实现：由于JDBC本身没有提供对应的实现，也不太可能提供对应的实现，所以需要导入对应数据库供应商提供的实现，比如MySQL的实现
2. 在程序中手动注册对应的数据库驱动
3. 建立并且打开对应的连接
4. 通过连接获取一个Statement对象，用于发送SQL，并且获得对应的结果
5. 从Statement中获得结果集对象
6. 关闭对应的结果集、Statement以及连接

接下来通过代码来具体查看如何操作

首先是导入MySQL的实现，对应的Maven依赖为

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>

然后建立测试类TestJDBC.java

注册MySQL驱动

    // 注册MySQL驱动
    Class.forName("com.mysql.jdbc.Driver");

建立连接

    private static final String SQL_URL = "jdbc:mysql://localhost:3306/spring"; // 数据库连接地址
    private static final String SQL_USER_NAME = "root"; // 账号
    private static final String SQL_PASSWORD = "huanfeng"; // 密码

    // 建立连接
    Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

    // 获得Statment对象
    Statement statement = connection.createStatement();

完整的过程如下所示

    private static final String SQL_URL = "jdbc:mysql://localhost:3306/spring";
    private static final String SQL_USER_NAME = "root";
    private static final String SQL_PASSWORD = "huanfeng";

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try {

        // 注册MySQL驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 建立连接
        connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        String sql = "SELECT * FROM user";

        // 获得Statement
        statement = connection.createStatement();

        // 获得ResultSet
        resultSet = statement.executeQuery(sql);

        // 操作ResultSet

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        // 注意关闭的顺序，应该从ResultSet再到Statement再到Connection
        // 关闭ResultSet
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭Statement
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭Connection
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

这里一定要注意，资源用完之后一定要关闭，因为建立一个对应的连接是非常消耗资源的，而如果使用完之后不关闭资源，会造成非常大的资源浪费，为了证实这一点，下面进行一个简单的测试，测试建立一个Connection所消耗的时间

 @Test
    public void testConnection() throws ClassNotFoundException, SQLException {

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

        long start = System.currentTimeMillis();
        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
        long end = System.currentTimeMillis();
        connection.close();
        System.out.printf("cost time %d ms\n", (end - start));

    }

对应的输出结果如下所示

    cost time 360 ms

````

可以看到，建立一个Connection连接需要消耗非常多的时间，原因在于，建立连接的时候，本质上是通过驱动程序与数据库之间建立一个Socket连接，对于Socket有了解的朋友应该知道，建立Socket连接是比较消耗时间的，而且维持一个Socket连接也是非常消耗资源的，所以当资源使用完毕之后，应该关闭对应的资源，当然，更好的做法是使用数据库连接池技术，将Connection对象缓存起来，来避免频繁创建Connection对象。





<div class="se-preview-section-delimiter"></div>

## JDBC实战

上面大概了解了JDBC的操作步骤以及基本的注意事项，接下来通过一个例子来使用JDBC操作数据库，加深对JDBC使用的了解

建立连接的过程同上，这里不重复叙述





<div class="se-preview-section-delimiter"></div>

### 插入数据





<div class="se-preview-section-delimiter"></div>

```Java
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {

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

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        String sql = "Insert into user(id, name) value(4, 'Tom')";
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);
        System.out.println(result);
        statement.close();
        connection.close();

    }

上面是最简单的插入操作，不过一般来说，我们会插入参数而不是固定的数值，所以上面的内容会变成

    int id = 4;
    String name = "Tom";
    // 拼接SQL语句
    String sql = "Insert into user(id, name) value("+ id +", '"+ name +"')";

通过拼接SQL语句，然后使用Statement来执行语句是解决插入参数的一种方式，但是，通过这种方式进行数据库操作会出现SQL注入的危险，比如说，当有人把参数name的内容填写为 Tom'); DELETE FROM USER; --，那么此时拼接后的SQL语句就会变成insert into user(id, name) values(4, 'tom'); delete from user; -- 此时就非常危险了，虽然这里是不会出现问题的，因为statement一次只能执行一条语句，但是如果是查询后面被加上这些内容，那就非常危险了。为了避免被注入的情况发生，JDBC提供了另外一种方式，采用预编译处理SQL，然后使用PreparedStatement设置参数以及执行对应的SQL语句，如下。

    // 采用占位符来处理
    String sql = "Insert into user(id, name) value(?, ?)";

    PreparedStatement statement = connection.prepareStatement(sql);
    // 设置对应的参数，注意这里是从1开始，不是从0开始
    statement.setInt(1, id);
    statement.setString(2, name);
    // 这里也可以直接使用
    // statement.setObject(1, id);
    // statement.setObject(2, name);
    // 不需要指定对应的参数类型，将其交给JDBC进行判断即可

JDBC是推荐使用PreparedStatement来进行SQL处理，而不是Statement来处理，原因在于PreparedStatement提供了预处理机制，可以预防SQL注入的发送，而且由于是采用预处理机制，所以PreparedStatement的执行效率要高于Statement

删除数据

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        int id = 3;
        String sql = "DELETE FROM  USER where id = ?";

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1, id);

        int result = statement.executeUpdate();

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

修改数据

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        int id = 2;
        String name = "Jack";
        String sql = "UPDATE USER SET NAME = ? where id = ?";

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1, name);
        statement.setObject(2, id);

        int result = statement.executeUpdate();

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

查询数据

谈到查询数据，这里就有一个新的对象需要介绍，ResultSet，在JDBC中，查询的结果一般都是以行为单位的，可以是单行也可以是多行，JDBC将每一行都封装成一个ResultSet对象，对于ResultSet的操作，其实就是一个迭代的过程，在获取的时候，需要通过 ResultSet.getXX(ID) 或者ResultSet.getXX(CLO_NAME)来获取对应行的数据，然后通过ResultSet.next()将指针移动到下一行。

    @Test
    public void testQuery() throws ClassNotFoundException, SQLException {

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

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        int id = 1;
        String sql = "SELECT id, name FROM USER where id = ?";

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1, id);

        // 查询返回的ResultSet
        ResultSet resultSet = statement.executeQuery();

        // 遍历ResultSet
        while (resultSet.next()){
            // 取出对应的数据
            int n_id = resultSet.getInt("id");
            String n_name = resultSet.getString("name");
            // 封装成对应的对象
            User user = new User(n_id, n_name);
            System.out.println("user: " + user);
        }
        // 关闭对应的连接
        resultSet.close();
        statement.close();
        connection.close();
    }

大对象处理

所谓的大对象，指的是数据库中定义的CLOB(Character Large Object )、BLOB(Binary Large Object)，这两个数据类型是用于把包含信息量比较大的数据存储在列中，当做一个属性，其中CLOB主要是用于存放字符类型大对象，比如说一本书的内容，BLOB主要用于存放二进制类型的大对象，比如说一部小电影。JDBC同样为操作这两种数据类型提供支持，不过，操作起来跟普通的数据类型有所不同，具体如下所示

    @Test
    public void testBLOB() throws ClassNotFoundException, SQLException, IOException, InterruptedException {
        Class.forName("com.mysql.jdbc.Driver");

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        // 存入Blob类型的数据
        File file = new File("d:/img.jpg");
        int id = 121;
        String name = "jack";
        String sql = "insert into user(id, name, b_data) values(?, ?, ?)";

        PreparedStatement statement = connection.prepareStatement(sql);

        statement.setObject(1, id);
        statement.setObject(2, name);
        // 这里需要注意，类型应该为Blob，直接传一个输入流即可
        statement.setBlob(3, new FileInputStream(file));

        int result = statement.executeUpdate();
        System.out.println(result);

        // 读取Blob类型的数据
        sql = "SELECT b_data FROM user WHERE id = ?";
        statement = connection.prepareStatement(sql);
        statement.setObject(1, id);

        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()){
            // 注意这里，返回的类型是BLob
            Blob b_data = resultSet.getBlob("b_data");
            InputStream inputStream = b_data.getBinaryStream();
            byte[] buf = new byte[inputStream.available()];
            FileOutputStream fileOutputStream = new FileOutputStream(new File("d:/new_img.jpg"));
            inputStream.read(buf);
            fileOutputStream.write(buf);
            fileOutputStream.flush();
            System.out.println("finish");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

CLOB的操作基本同BLOB，只是对应的类型设置为CLOB即可，需要注意的是，在MySQL中，没有数据类型为CLOB的数据类型，其对应的是text，这里在创建数据表的时候需要注意一下。

批量处理

所谓的批处理，其实就是同时指定多个SQL语句，通常由两种做法，一种是循环执行多个SQL语句，另外一种则是采用JDBC提供的Batch功能，具体如下所示

 @Test
    public void testBatch() throws ClassNotFoundException, SQLException {

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

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        String sql = "insert into user(id, name) values(?, 'Tom')";
        PreparedStatement statement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        // 普通循环执行
        for (int i = 0; i < 1000; i++){
            statement.setObject(1, i);
            statement.executeUpdate();
        }

        long end = System.currentTimeMillis();

        System.out.printf("cost time %d\n", end - start);

        statement.close();

        statement = connection.prepareStatement(sql);

        start = System.currentTimeMillis();
        // 使用Batch执行
        for (int i = 1001 ; i < 2000; i++){
            statement.setObject(1, i);
            statement.addBatch();
        }
        statement.executeBatch();
        end = System.currentTimeMillis();

        System.out.printf("cost time %d\n", end - start);

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

有点可惜的是，经过测试，这两种方式的性能很接近，不知道是我操作的问题还是事实确实是如此，还望有懂这个的朋友指点指点，

元数据

所谓的元数据，也就是是用于定义其他数据的数据，在SQL中，元数据可以理解为就是数据库、表的定义数据，在JDBC中，有三种类型的元数据，分别是DatabaseMetaData，ResultSetMetaData，ParameterMetaData，分别是数据库相关信息，结果集相关信息，以及PreparedStatement语句中的相关参数信息，具体操作如下所示

    @Test
    public void testMetaData() throws ClassNotFoundException, SQLException {

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

        Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);

        // DatabaseMetaData 相关的信息，比如数据库供应商名称，版本等
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        System.out.println("version : " + databaseMetaData.getDatabaseMajorVersion());
        System.out.println("name : " + databaseMetaData.getDatabaseProductName());

        int id = 4;
        String sql = "select id, name from user where id < ? ";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1, id);

        // ParameterMetaData 相关信息，比如参数个数
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        int paramCount = parameterMetaData.getParameterCount();
        System.out.println("parameter count : " + paramCount);

        ResultSet resultSet = statement.executeQuery();

        // ResultSetMetaData 相关信息，比如取出来的数据的列名
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int resultCount = resultSetMetaData.getColumnCount();
        System.out.println("resultCount : " + resultCount);
        for (int i = 0; i < resultCount; i++) {
            System.out.print(resultSetMetaData.getColumnName(i + 1) + "  ");
        }
        System.out.println();
    }

事务管理

所谓的事务，是指做一件事情，这件事情有多个步骤，这多个步骤这件，要么都完成，要么都不完成，事务具有四个特性ACID，分别是原子性，一致性，独立性，持久性
在JDBC中，可以通过connection.setAutoCommit(true/false);开控制事务，默认为true，也就是默认自动提交事务。通过connection.commit()手动提交事务，connection.setSavepoint();设置保存点，connection.rollback();回滚到保存到。