本例以JDBC 和 MY SQL DataBase为基础，演示执行SQL来批量操作数据，使用Statement和Prepared Statement两种方式。
在批量操作什么，可以用来批量插入，删除，更新数据。

一，示例代码

连接数据库uilt: MySqlJDBSUtil.java 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.devnp.jdbc;

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

public class MySqlJDBSUtil {
	private static String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8";
	private static String userName = "root"; // base on your login user name
	private static String password = "!qaz2wsx"; // base on your login user

	public static Connection getConnection() {
		
		Connection connection = null;
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, userName, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static void connectionClose(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

操作代码：
JDBCBatchRecord.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
package com.devnp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class JDBCBatchRecord {

	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		batchDbRecordWithStatement();
		
		batchDbRecordWithPreparedStatement();
	}
	
	/**
	 * 采用 Statement 方式插入数据
	 * @throws SQLException
	 */
	public static void batchDbRecordWithStatement() throws SQLException {
		Connection dbConnection = null;
		Statement statement = null;

		String insertTableSQL1 = "INSERT INTO USER_INFO"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(3,'duliu','system',  NOW())";

		String insertTableSQL2 = "INSERT INTO USER_INFO"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(4,'duliu','system', NOW())";

		String updateTableSQL1 = "UPDATE USER_INFO SET USERNAME = 'devp.com' "
				+ " WHERE USER_ID = 4" ;

		try {
			dbConnection = MySqlJDBSUtil.getConnection();
			statement = dbConnection.createStatement();

			dbConnection.setAutoCommit(false);

			statement.addBatch(insertTableSQL1);
			statement.addBatch(insertTableSQL2);
			statement.addBatch(updateTableSQL1);

			statement.executeBatch();

			dbConnection.commit();

			System.out.println("Records are inserted and updated USER_INFO table!");

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}
	}
	
	/**
	 * 采用Prepared Statement 方式插入数据
	 * @throws SQLException
	 */
	public static void batchDbRecordWithPreparedStatement() throws SQLException {
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "INSERT INTO USER_INFO"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
				+ "(?,?,?,?)";

		try {
			dbConnection = MySqlJDBSUtil.getConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			dbConnection.setAutoCommit(false);

			preparedStatement.setInt(1, 5);
			preparedStatement.setString(2, "duliu");
			preparedStatement.setString(3, "system");
			preparedStatement.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
			preparedStatement.addBatch();

			preparedStatement.setInt(1, 6);
			preparedStatement.setString(2, "devnp.com");
			preparedStatement.setString(3, "system");
			preparedStatement.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
			preparedStatement.addBatch();

			preparedStatement.setInt(1, 7);
			preparedStatement.setString(2, "devnp.com");
			preparedStatement.setString(3, "system");
			preparedStatement.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
			preparedStatement.addBatch();

			preparedStatement.executeBatch();

			dbConnection.commit();

			System.out.println("Record is inserted into USER_INFO table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());
			
			dbConnection.rollback();

		} finally {

			if (preparedStatement != null) {
				preparedStatement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}
	}

}

二，测试

运行结果： 
 Records are inserted and updated USER_INFO table!
 Record is inserted into USER_INFO table!

数据库截图：