Java JDBC高级特性

1、JDBC批处理

实际开发中需要向数据库发送多条SQL语句，这时，如果逐条执行SQL语句，效率会很低，因此可以使用JDBC提供的批处理机制。Statement和PreparedStatemen都实现了批处理。测试表结构如下：

Statement批处理程序示例

package server;

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

import com.mysql.jdbc.PreparedStatement;

public class DemoJDBC {
    public static void main(String[] args) throws Exception {
        // 加载驱动类
        Class.forName(“com.mysql.jdbc.Driver”);
   
        // 通过DriverManager获取数据库连接
        String url = “jdbc:mysql://192.168.1.150/test”;
        String user = “teamtalk”;
        String password = “123456”;
        Connection connection = (Connection) DriverManager.getConnection(
                url, user, password);
       
        String sql1 = “DROP TABLE IF EXISTS people”;
        String sql2 = “CREATE TABLE people(id int, name varchar(20))”;
        String sql3 = “INSERT people VALUES(2, ‘hdu’)”;
        String sql4 = “UPDATE people SET id = 1”;
        Statement statement = (Statement) connection.createStatement();
        statement.addBatch(sql1);
        statement.addBatch(sql2);
        statement.addBatch(sql3);
        statement.addBatch(sql4);
        statement.executeBatch();
       
        ResultSet resultSet = statement.executeQuery(“SELECT * from people”);
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1) + ” ” + resultSet.getString(2));
        }
    }
}

PreparedStatement批处理

package server;

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

import com.mysql.jdbc.PreparedStatement;

public class DemoJDBC {
    public static void main(String[] args) throws Exception {
        // 加载驱动类
        Class.forName(“com.mysql.jdbc.Driver”);
   
        // 通过DriverManager获取数据库连接
        String url = “jdbc:mysql://192.168.1.150/test”;
        String user = “teamtalk”;
        String password = “123456”;
        Connection connection = (Connection) DriverManager.getConnection(
                url, user, password);
       
        PreparedStatement statement =  (PreparedStatement) connection.prepareStatement(“INSERT people VALUES(?,?)”);
        for (int i = 1; i < 4; i++) {
            statement.setInt(1, i);
            statement.setString(2, “hdu” + i);
            statement.addBatch();
        }
        statement.executeBatch();
       
        ResultSet resultSet = statement.executeQuery(“SELECT * from people”);
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1) + ” ” + resultSet.getString(2));
        }
    }
}

2、JDBC处理事务

针对JDBC处理事务的操作，在Connection接口中，提供了3个相关的方法，具体如下：

1 setAutoCommit(boolean autoCommit); // 设置是否自动提交事务
2 commit(); // 提交事务
3 rollback(); // 撤销事务

将setAutoCommit()方法参数设置为false后，事务必须使用conn.commit()方法提交，而事务回滚不一定显式执行conn.rollback()。如果程序最后没有执行conn.commit()，事务也会回滚，一般是直接抛出异常，终止程序的正常执行。因此，通常情况下，会conn.rollback()语句放在catch语句块执行。

package demo.jdbc;

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

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class FirstJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        Connection connection = null;
        try {
            // 加载驱动类
            Class.forName(“com.mysql.jdbc.Driver”);
       
            // 通过DriverManager获取数据库连接
            String url = “jdbc:mysql://192.168.1.150/test”;
            String user = “teamtalk”;
            String password = “123456”;
            connection = (Connection) DriverManager.getConnection(
                    url, user, password);
            // 关闭事务的自动提交
            connection.setAutoCommit(false);
           
            Statement statement = (Statement) connection.createStatement();
            PreparedStatement statement1 = (PreparedStatement) connection.prepareStatement(“INSERT people VALUES(?, ?)”);
            PreparedStatement statement2 = (PreparedStatement) connection.prepareStatement(“INSERT people VALUES(?, ?)”);
           
            statement1.setInt(1, 1);
            statement1.setString(2, “hdu1”);
            statement2.setInt(1, 2);
            statement2.setString(2, “hdu2”);
           
            statement1.executeUpdate();
            statement2.executeUpdate();
           
            ResultSet resultSet = statement.executeQuery(“SELECT * from people”);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + ” ” + resultSet.getString(2));
            }
        }
        catch (Exception e) {
            // 回滚事务
            connection.rollback();
            e.printStackTrace();
        }
    }
}

更多详情