/**
* Test batched callable statements where the call includes literal parameters which prevent the
* use of RPC calls.
*/
public void testCallStmtBatch2() throws Exception {
dropProcedure("jTDS_PROC");
try {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
stmt.execute(
"create proc jTDS_PROC @p1 varchar(10), @p2 varchar(255) as "
+ "INSERT INTO #testbatch VALUES (convert(int, @p1), @p2)");
CallableStatement cstmt = con.prepareCall("{call jTDS_PROC (?, 'literal parameter')}");
for (int i = 0; i < 5; i++) {
if (i == 2) {
cstmt.setString(1, "XXX");
} else {
cstmt.setString(1, Integer.toString(i));
}
cstmt.addBatch();
}
int x[];
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
cstmt.setString(1, Integer.toString(i));
cstmt.addBatch();
}
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
} finally {
dropProcedure("jTDS_PROC");
}
}
/** Test batched callable statements where the call has no parameters. */
public void testCallStmtNoParams() throws Exception {
dropProcedure("jTDS_PROC");
try {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id numeric(10) identity, data varchar(255))");
stmt.execute(
"create proc jTDS_PROC as " + "INSERT INTO #testbatch (data) VALUES ('same each time')");
CallableStatement cstmt = con.prepareCall("{call jTDS_PROC}");
for (int i = 0; i < 5; i++) {
cstmt.addBatch();
}
int x[];
try {
x = cstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
} finally {
dropProcedure("jTDS_PROC");
}
}
private void testExecuteCall() throws SQLException {
deleteDb("batchUpdates");
conn = getConnection("batchUpdates");
stat = conn.createStatement();
stat.execute("CREATE ALIAS updatePrices FOR \"" + getClass().getName() + ".updatePrices\"");
CallableStatement call = conn.prepareCall("{call updatePrices(?, ?)}");
call.setString(1, "Hello");
call.setFloat(2, 1.4f);
call.addBatch();
call.setString(1, "World");
call.setFloat(2, 3.2f);
call.addBatch();
int[] updateCounts = call.executeBatch();
int total = 0;
for (int t : updateCounts) {
total += t;
}
assertEquals(4, total);
conn.close();
}
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
Driver drv = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(drv);
conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/tests", "root", "123456");
conn.setAutoCommit(false); // 设置事务非自动提�?
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量添加前：");
System.out.println("id\tname\tage");
while (rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getInt(3) + "\n");
}
// 批量更新
cstmt = conn.prepareCall("{call proc_insert_test(?,?)}");
cstmt.setString(1, "xiaoming1");
cstmt.setInt(2, 33);
cstmt.addBatch();
cstmt.setString(1, "xiaoming2");
cstmt.setInt(2, 13);
cstmt.addBatch();
int count[] = cstmt.executeBatch();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量更新返回�?");
for (int i = 0; i < count.length; i++) {
System.out.print(count[i] + ",\t");
}
System.out.println();
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量添加�?");
System.out.println("id\tname\tage");
while (rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getInt(3) + "\n");
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (cstmt != null) {
cstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/** @see java.sql.Statement#addBatch(java.lang.String) */
public void addBatch(String sql) throws SQLException {
original.addBatch(sql);
}
/** @see java.sql.PreparedStatement#addBatch() */
public void addBatch() throws SQLException {
original.addBatch();
}