Java PreparedStatement.executeBatch方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
 * generated keys (even though that's not JDBC compliant).
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug3873() throws Exception {
    PreparedStatement batchStmt = null;

    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
        this.stmt.executeUpdate("CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
        batchStmt = this.conn.prepareStatement("INSERT INTO testBug3873 (dataField) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
        batchStmt.setString(1, "abc");
        batchStmt.addBatch();
        batchStmt.setString(1, "def");
        batchStmt.addBatch();
        batchStmt.setString(1, "ghi");
        batchStmt.addBatch();

        @SuppressWarnings("unused")
        int[] updateCounts = batchStmt.executeBatch();

        this.rs = batchStmt.getGeneratedKeys();

        while (this.rs.next()) {
            System.out.println(this.rs.getInt(1));
        }

        this.rs = batchStmt.getGeneratedKeys();
        assertTrue(this.rs.next());
        assertTrue(1 == this.rs.getInt(1));
        assertTrue(this.rs.next());
        assertTrue(2 == this.rs.getInt(1));
        assertTrue(this.rs.next());
        assertTrue(3 == this.rs.getInt(1));
        assertTrue(!this.rs.next());
    } finally {
        if (batchStmt != null) {
            batchStmt.close();
        }

        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private long insert(Connection con, List<Map<String, String>> list)
        throws SQLException {
    PreparedStatement ps;

    String sql = "insert into travelrecord (id,user_id,traveldate,fee,days) values(?,?,?,?,?)";
    ps = con.prepareStatement(sql);
    for (Map<String, String> map : list) {
        ps.setLong(1, Long.parseLong(map.get("id")));
        ps.setString(2, (String) map.get("user_id"));
        ps.setString(3, (String) map.get("traveldate"));
        ps.setString(4, (String) map.get("fee"));
        ps.setString(5, (String) map.get("days"));
        ps.addBatch();
    }
    ps.executeBatch();
    con.commit();
    ps.clearBatch();
    ps.close();
    return list.size();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void addReport(int reporterid, int victimid, int reason, String description, String chatlog) {
  Connection con = DatabaseConnection.getConnection();
        try {
            PreparedStatement ps = con.prepareStatement("INSERT INTO reports (`reporterid`, `victimid`, `reason`, `description`, `chatlog`) VALUES (?, ?, ?, ?, ?)");
            ps.setInt(1, reporterid);
            ps.setInt(2, victimid);
            ps.setInt(3, reason);
            ps.setString(4, description);
            ps.setString(5, chatlog);
            ps.addBatch();
            ps.executeBatch();
            ps.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void lowerRequestedFlag(Connection txn, ContactId c,
		Collection<MessageId> requested) throws DbException {
	PreparedStatement ps = null;
	try {
		String sql = "UPDATE statuses SET requested = FALSE"
				+ " WHERE messageId = ? AND contactId = ?";
		ps = txn.prepareStatement(sql);
		ps.setInt(2, c.getInt());
		for (MessageId m : requested) {
			ps.setBytes(1, m.getBytes());
			ps.addBatch();
		}
		int[] batchAffected = ps.executeBatch();
		if (batchAffected.length != requested.size())
			throw new DbStateException();
		for (int rows: batchAffected) {
			if (rows < 0) throw new DbStateException();
			if (rows > 1) throw new DbStateException();
		}
		ps.close();
	} catch (SQLException e) {
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 该方法用于批处理，当需要一次执行多条相同SQL语句时使用该方法时效率较高
 *
 * @param sql 需要执行的SQL语句
 * @param arg 传入SQL语句所需要的占位符参数，下标均从0开始，每个一维下标对应于SQL语句的一组占位符参数
 */
public static void updateBatch(String sql, Object[][] arg) {
    Connection connection = JDBCUtils.getConnection();
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < arg.length; i++) {
            for (int j = 0; j < arg[i].length; j++) {
                ps.setObject(j + 1, arg[i][j]);
            }
            ps.addBatch();
        }
        ps.executeBatch();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    JDBCUtils.release(ps, connection);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void updateBatch(byte[][] ids, String[] names) throws SQLException {
	assertEquals(ids.length, names.length);
	String sql = "UPDATE foo SET name = ? WHERE uniqueId = ?";
	try {
		PreparedStatement ps = connection.prepareStatement(sql);
		for (int i = 0; i < ids.length; i++) {
			if (ids[i] == null) ps.setNull(2, BINARY);
			else ps.setBytes(2, ids[i]);
			ps.setString(1, names[i]);
			ps.addBatch();
		}
		int[] batchAffected = ps.executeBatch();
		assertEquals(ids.length, batchAffected.length);
		for (int affected : batchAffected) assertEquals(1, affected);
		ps.close();
	} catch (SQLException e) {
		connection.close();
		throw e;
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void detachFromQueue(Collection<DbOperation> dbOperations) throws BrokerException {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = getConnection();
        statement = connection.prepareStatement(RDBMSConstants.PS_DELETE_FROM_QUEUE);
        for (DbOperation dbOperation : dbOperations) {
            statement.setLong(1, dbOperation.getMessageId());
            statement.setString(2, dbOperation.getQueueName());
            statement.addBatch();
        }

        statement.executeBatch();
        connection.commit();
    } catch (SQLException e) {
        throw new BrokerException("Error detaching messages from queues.", e);
    } finally {
        close(connection, statement);
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void removeOfferedMessages(Connection txn, ContactId c,
		Collection<MessageId> requested) throws DbException {
	PreparedStatement ps = null;
	try {
		String sql = "DELETE FROM offers"
				+ " WHERE contactId = ? AND messageId = ?";
		ps = txn.prepareStatement(sql);
		ps.setInt(1, c.getInt());
		for (MessageId m : requested) {
			ps.setBytes(2, m.getBytes());
			ps.addBatch();
		}
		int[] batchAffected = ps.executeBatch();
		if (batchAffected.length != requested.size())
			throw new DbStateException();
		for (int rows : batchAffected)
			if (rows != 1) throw new DbStateException();
		ps.close();
	} catch (SQLException e) {
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    PreparedStatement ps = con.prepareStatement(this.sql, autoGeneratedKey);

    if (null != args){
        SQLTools.fillStatement(ps, args);
        if (isBatch  && (rowCont = args.length) > 0){
            int success = ps.executeBatch().length ;
            if (success > 0 && success < rowCont){
                logger.warn("The number of successful {}, now successful {} ", rowCont, success );
            }
            rowCont = success;
        }else {
            rowCont = ps.executeUpdate();
        }
        if (rowCont < 1) {
            throw new SQLException("sql:{} On failure.", ps.toString());
        }
    }

    return ps;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#30550 - executeBatch() on an empty batch when there are
 * no elements in the batch causes a divide-by-zero error when rewriting is
 * enabled.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug30550() throws Exception {
    createTable("testBug30550", "(field1 int)");

    Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
    PreparedStatement batchPStmt = null;
    Statement batchStmt = null;

    try {
        batchStmt = rewriteConn.createStatement();
        assertEquals(0, batchStmt.executeBatch().length);

        batchStmt.addBatch("INSERT INTO testBug30550 VALUES (1)");
        int[] counts = batchStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchStmt.executeBatch().length);

        batchPStmt = rewriteConn.prepareStatement("INSERT INTO testBug30550 VALUES (?)");
        batchPStmt.setInt(1, 1);
        assertEquals(0, batchPStmt.executeBatch().length);
        batchPStmt.addBatch();
        counts = batchPStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchPStmt.executeBatch().length);
    } finally {
        if (batchPStmt != null) {
            batchPStmt.close();
        }

        if (batchStmt != null) {
            batchStmt.close();
        }
        if (rewriteConn != null) {
            rewriteConn.close();
        }
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private int[] testBug68562ExecuteBatch(int batchSize, boolean useAffectedRows, boolean rewriteBatchedStatements, boolean realUpdate)
        throws ClassNotFoundException, SQLException {

    String tableName = "testBug68562";

    Properties properties = new Properties();
    if (useAffectedRows) {
        properties.put("useAffectedRows", "true");
        tableName += "_affected";
    } else {
        tableName += "_found";
    }
    if (rewriteBatchedStatements) {
        properties.put("rewriteBatchedStatements", "true");
    }
    Connection connection = getConnectionWithProps(properties);

    PreparedStatement statement = connection
            .prepareStatement("INSERT INTO " + tableName + "(id, name, version) VALUES(?,?,?) ON DUPLICATE KEY UPDATE version = "
                    + (realUpdate ? "CONCAT(VALUES(version),'updated'), name = CONCAT(VALUES(name),'updated')" : "VALUES(version), name = VALUES(name)"));
    for (int i = 0; i < batchSize; i++) {
        statement.setInt(1, i);
        statement.setString(2, "name" + i);
        statement.setString(3, "version" + i);
        statement.addBatch();
    }

    int[] affectedRows = statement.executeBatch();

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

    return affectedRows;

}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
protected void insertNode ( final ConnectionContext connectionContext, final DataNode node, final String data ) throws SQLException
{
    if ( data == null )
    {
        return;
    }

    final PreparedStatement stmt = connectionContext.getConnection ().prepareStatement ( SQL_INSERT );

    final int len = data.length ();

    for ( int i = 0; i <= len / this.chunkSize; i++ )
    {
        int end = ( i + 1 ) * this.chunkSize;
        if ( end > len )
        {
            end = len;
        }

        final String chunk = data.substring ( i * this.chunkSize, end );

        stmt.setObject ( 1, node.getId () );
        stmt.setObject ( 2, this.instanceId );
        stmt.setObject ( 3, i );
        stmt.setObject ( 4, chunk );
        stmt.addBatch ();
    }

    stmt.executeBatch ();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void performExecution() {
	try {
		for ( Map.Entry<String,PreparedStatement> entry : getStatements().entrySet() ) {
			try {
				final PreparedStatement statement = entry.getValue();
				final int[] rowCounts;
				try {
					transactionContext().startBatchExecution();
					rowCounts = statement.executeBatch();
				}
				finally {
					transactionContext().endBatchExecution();
				}
				checkRowCounts( rowCounts, statement );
			}
			catch ( SQLException e ) {
				abortBatch();
				throw sqlExceptionHelper().convert( e, "could not execute batch", entry.getKey() );
			}
		}
	}
	catch ( RuntimeException re ) {
		LOG.unableToExecuteBatch( re.getMessage() );
		throw re;
	}
	finally {
		batchPosition = 0;
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#20029 - NPE thrown from executeBatch().
 * 
 * @throws Exception
 */
public void testBug20029() throws Exception {
    createTable("testBug20029", ("(field1 int)"));

    long initialTimeout = 20; // may need to raise this depending on environment we try and do this automatically in this testcase

    for (int i = 0; i < 10; i++) {
        final Connection toBeKilledConn = getConnectionWithProps(new Properties());
        final long timeout = initialTimeout;
        PreparedStatement toBeKilledPstmt = null;

        try {
            toBeKilledPstmt = ((com.mysql.jdbc.Connection) toBeKilledConn).clientPrepareStatement("INSERT INTO testBug20029 VALUES (?)");

            for (int j = 0; j < 1000; j++) {
                toBeKilledPstmt.setInt(1, j);
                toBeKilledPstmt.addBatch();
            }

            Thread t = new Thread() {
                @Override
                public void run() {
                    try {
                        sleep(timeout);
                        toBeKilledConn.close();
                    } catch (Throwable thr) {

                    }
                }
            };

            t.start();

            try {
                if (!toBeKilledConn.isClosed()) {
                    initialTimeout *= 2;
                    continue;
                }

                toBeKilledPstmt.executeBatch();
                fail("Should've caught a SQLException for the statement being closed here");
            } catch (BatchUpdateException batchEx) {
                assertEquals("08003", batchEx.getSQLState());
                break;
            } catch (SQLException sqlEx) {
                assertEquals("08003", sqlEx.getSQLState());
                break;
            }

            fail("Connection didn't close while in the middle of PreparedStatement.executeBatch()");
        } finally {
            if (toBeKilledPstmt != null) {
                toBeKilledPstmt.close();
            }

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

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testInsertBatch() throws SQLException {

  PreparedStatement ps = con.prepareStatement("INSERT INTO Fortune (id, message) VALUES (?, ?)");

  ps.setInt(1, 2000);
  ps.setString(2, "Hello");
  ps.addBatch();

  ps.setInt(1, 2001);
  ps.setString(2, "Vert.x");
  ps.addBatch();

  ps.setInt(1, 2002);
  ps.setString(2, "World");
  ps.addBatch();

  ps.executeBatch();

  assertEquals(-1, ps.getUpdateCount());

  ps.close();

  assertEquals(true, ps.isClosed());

}