Java PreparedStatement.addBatch方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addOperations(PreparedStatement statement, long testId, String version, MeasuredOperationList operations) throws SQLException {
    for (MeasuredOperation operation : operations) {
        statement.setLong(1, testId);
        statement.setString(2, version);
        statement.setBigDecimal(3, operation.getTotalTime().toUnits(Duration.MILLI_SECONDS).getValue());
        statement.setBigDecimal(4, operation.getConfigurationTime().toUnits(Duration.MILLI_SECONDS).getValue());
        statement.setBigDecimal(5, operation.getExecutionTime().toUnits(Duration.MILLI_SECONDS).getValue());
        statement.setBigDecimal(6, operation.getTotalMemoryUsed().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(7, operation.getTotalHeapUsage().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(8, operation.getMaxHeapUsage().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(9, operation.getMaxUncollectedHeap().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(10, operation.getMaxCommittedHeap().toUnits(DataAmount.BYTES).getValue());
        if (operation.getCompileTotalTime() != null) {
            statement.setBigDecimal(11, operation.getCompileTotalTime().toUnits(Duration.MILLI_SECONDS).getValue());
        } else {
            statement.setNull(11, Types.DECIMAL);
        }
        if (operation.getGcTotalTime() != null) {
            statement.setBigDecimal(12, operation.getGcTotalTime().toUnits(Duration.MILLI_SECONDS).getValue());
        } else {
            statement.setNull(12, Types.DECIMAL);
        }
        statement.addBatch();
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 在'PreparedStatement'中设置SQL语句参数.
 * @param pstmt PreparedStatement实例
 * @param paramsArr 不定长数组参数
 * @return PreparedStatement实例
 * @throws SQLException SQL异常
 */
private static PreparedStatement setBatchPreparedStatementParams(PreparedStatement pstmt,
        Object[]... paramsArr) throws SQLException {
    if (paramsArr == null) {
        return pstmt;
    }

    // 遍历批量执行每条语句的数组参数.
    for (Object[] params: paramsArr) {
        if (params == null) {
            continue;
        }
        setParams(pstmt, params);
        pstmt.addBatch();
    }

    return pstmt;
}
 

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包/类
public void addAnnReferencesToNakedSecondaryAnnotation(int dbAnnId, Tuple secAnnReferencesHolder, Map<String, Integer> dbAnIdByAlvisAnnId) throws ProcessingException {
    PreparedStatement addAnnRefStatement = getPreparedStatement(PreparedStatementId.AddAnnotationReference);
    try {
        int ordNum = 0;
        for (String role : secAnnReferencesHolder.getRoles()) {
            String referencedAnnAlvisId = secAnnReferencesHolder.getArgument(role).getStringId();
            int referencedAnnId = dbAnIdByAlvisAnnId.get(referencedAnnAlvisId);
            addAnnRefStatement.setInt(1, dbAnnId);
            addAnnRefStatement.setInt(2, referencedAnnId);
            addAnnRefStatement.setString(3, role);
            addAnnRefStatement.setInt(4, ordNum++);
            addAnnRefStatement.addBatch();
        }
        addAnnRefStatement.executeBatch();
    } catch (SQLException ex) {
        logger.log(Level.SEVERE, ex.getMessage());
        throw new ProcessingException("Could not add Annotation references", ex);
    }
}
 

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包/类
/**
 * Bug #39426 - executeBatch passes most recent PreparedStatement params to
 * StatementInterceptor
 */
public void testBug39426() throws Exception {
    Connection c = null;
    try {
        createTable("testBug39426", "(x int)");
        c = getConnectionWithProps("statementInterceptors=testsuite.regression.StatementRegressionTest$Bug39426Interceptor,useServerPrepStmts=false");
        PreparedStatement ps = c.prepareStatement("insert into testBug39426 values (?)");
        ps.setInt(1, 1);
        ps.addBatch();
        ps.setInt(1, 2);
        ps.addBatch();
        ps.setInt(1, 3);
        ps.addBatch();
        ps.executeBatch();
        List<Integer> vals = Bug39426Interceptor.vals;
        assertEquals(new Integer(1), vals.get(0));
        assertEquals(new Integer(2), vals.get(1));
        assertEquals(new Integer(3), vals.get(2));
    } finally {
        if (c != null) {
            c.close();
        }
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void collect(Configuration conf, BaseDimension key, BaseStatsValueWritable value, PreparedStatement pstmt,
		IDimensionConverter converter) throws SQLException, IOException {
	// 进行强制后获取对应的值
	StatsUserDimension statsUser = (StatsUserDimension) key;
	IntWritable activeUserValue = (IntWritable) ((MapWritableValue) value).getValue().get(new IntWritable(-1));

	// 进行参数设置
	int i = 0;
	pstmt.setInt(++i, converter.getDimensionIdByValue(statsUser.getStatsCommon().getPlatform()));
	pstmt.setInt(++i, converter.getDimensionIdByValue(statsUser.getStatsCommon().getDate()));
	pstmt.setInt(++i, converter.getDimensionIdByValue(statsUser.getBrowser()));
	pstmt.setInt(++i, activeUserValue.get());
	pstmt.setString(++i, conf.get(GlobalConstants.RUNNING_DATE_PARAMES));
	pstmt.setInt(++i, activeUserValue.get());

	// 添加到batch中
	pstmt.addBatch();
}
 

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包/类
@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包/类
@Override
public void lowerAckFlag(Connection txn, ContactId c,
		Collection<MessageId> acked) throws DbException {
	PreparedStatement ps = null;
	try {
		String sql = "UPDATE statuses SET ack = FALSE"
				+ " WHERE messageId = ? AND contactId = ?";
		ps = txn.prepareStatement(sql);
		ps.setInt(2, c.getInt());
		for (MessageId m : acked) {
			ps.setBytes(1, m.getBytes());
			ps.addBatch();
		}
		int[] batchAffected = ps.executeBatch();
		if (batchAffected.length != acked.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包/类
@Override
/** {@inheritDoc} */
protected PreparedStatement getPreparedStatement(
    List<SqoopRecord> userRecords) throws SQLException {

  PreparedStatement stmt = null;

  // Synchronize on connection to ensure this does not conflict
  // with the operations in the update thread.
  Connection conn = getConnection();
  synchronized (conn) {
    stmt = conn.prepareStatement(getUpdateStatement());
  }

  // Inject the record parameters into the UPDATE and WHERE clauses.  This
  // assumes that the update key column is the last column serialized in
  // by the underlying record. Our code auto-gen process for exports was
  // responsible for taking care of this constraint.
  for (SqoopRecord record : userRecords) {
    record.write(stmt, 0);
    stmt.addBatch();
  }

  return stmt;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addOperations(PreparedStatement statement, long executionId, BuildDisplayInfo displayInfo, MeasuredOperationList operations) throws SQLException {
    for (MeasuredOperation operation : operations) {
        statement.setLong(1, executionId);
        statement.setString(2, displayInfo.getProjectName());
        statement.setString(3, displayInfo.getDisplayName());
        statement.setObject(4, toArray(displayInfo.getTasksToRun()));
        statement.setObject(5, toArray(displayInfo.getArgs()));
        statement.setObject(6, toArray(displayInfo.getGradleOpts()));
        statement.setObject(7, displayInfo.getDaemon());
        statement.setBigDecimal(8, operation.getTotalTime().toUnits(Duration.MILLI_SECONDS).getValue());
        statement.setBigDecimal(9, operation.getConfigurationTime().toUnits(Duration.MILLI_SECONDS).getValue());
        statement.setBigDecimal(10, operation.getExecutionTime().toUnits(Duration.MILLI_SECONDS).getValue());
        statement.setBigDecimal(11, operation.getTotalMemoryUsed().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(12, operation.getTotalHeapUsage().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(13, operation.getMaxHeapUsage().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(14, operation.getMaxUncollectedHeap().toUnits(DataAmount.BYTES).getValue());
        statement.setBigDecimal(15, operation.getMaxCommittedHeap().toUnits(DataAmount.BYTES).getValue());
        if (operation.getCompileTotalTime() != null) {
            statement.setBigDecimal(16, operation.getCompileTotalTime().toUnits(Duration.MILLI_SECONDS).getValue());
        } else {
            statement.setNull(16, Types.DECIMAL);
        }
        if (operation.getGcTotalTime() != null) {
            statement.setBigDecimal(17, operation.getGcTotalTime().toUnits(Duration.MILLI_SECONDS).getValue());
        } else {
            statement.setNull(17, Types.DECIMAL);
        }
        statement.addBatch();
    }
}
 

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包/类
static void populateTable(Connection con) throws SQLException {

        long      startTime = System.currentTimeMillis();
        Timestamp now       = new Timestamp(startTime);

        con.setAutoCommit(false);

        String            sql  = createInsertSQL(true, false);
        PreparedStatement prep = con.prepareStatement(sql);

        prep.clearParameters();
        prep.setString(1, "xxx");
        prep.setTimestamp(2, now);    // last_update

        for (int ii = 0; ii < DECIMAL_FIELDS_PER_DATASET; ii++) {
            prep.setDouble(ii + 3, 0.123456789);    // Wert
        }

        prep.addBatch();
        prep.setString(1, "yyy");
        prep.setTimestamp(2, now);    // last_update

        for (int ii = 0; ii < DECIMAL_FIELDS_PER_DATASET; ii++) {
            prep.setDouble(ii + 3, 0.123456789);    // Wert
        }

        prep.addBatch();

        int[] updateCounts = prep.executeBatch();

        con.setAutoCommit(true);
        prep.close();
    }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
  * {@inheritDoc}
  */
 @Override
 protected PreparedStatement getPreparedStatement(
     List<SqoopRecord> userRecords) throws SQLException {

   PreparedStatement stmt = null;

   // Synchronize on connection to ensure this does not conflict
   // with the operations in the update thread.
   Connection conn = getConnection();
   synchronized (conn) {
stmt = conn.prepareStatement(getUpdateStatement(userRecords.size()));
   }

   // Inject the record parameters into the UPDATE and WHERE clauses.  This
   // assumes that the update key column is the last column serialized in
   // by the underlying record. Our code auto-gen process for exports was
   // responsible for taking care of this constraint.
   int i = 0;
   for (SqoopRecord record : userRecords) {
     record.write(stmt, i);
     i += columnNames.length;
   }
   stmt.addBatch();

   return stmt;
 }