Java Code Examples for java.sql.PreparedStatement#clearBatch()

The following examples show how to use java.sql.PreparedStatement#clearBatch() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
@Test
public void testBatch() throws SQLException, MalformedURLException {
    PreparedStatement preparedStatement = multiHostConnection.prepareStatement("UPDATE test SET a = ? WHERE b = ?");
    preparedStatement.setShort(1, (short) 12);
    preparedStatement.setTime(2, new Time(System.currentTimeMillis()));
    preparedStatement.addBatch();
    int[] resultSet = preparedStatement.executeBatch();
    preparedStatement.clearBatch();

    verify(mysqlPreparedStatement).executeBatch();
    verify(mysqlPreparedStatement).addBatch();
    verify(mysqlPreparedStatement).clearBatch();

    TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
    List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
    assertThat(spans.size(), is(1));
    assertDBSpan(spans.get(0), "Mysql/JDBI/PreparedStatement/executeBatch", "");

}
 
Example 2
Source Project: copper-engine   File: OracleRemove.java    License: Apache License 2.0 6 votes vote down vote up
private static void deleteResponses(final PreparedStatement stmtDelResponse, final List<String> responseIds2delete, final int batchSize) throws SQLException {
    int count = 0;
    for (String id : responseIds2delete) {
        stmtDelResponse.setString(1, id);
        stmtDelResponse.addBatch();
        count++;
        if (count == batchSize) {
            stmtDelResponse.executeBatch();
            stmtDelResponse.clearBatch();
            count = 0;
        }
    }
    if (count > 0) {
        stmtDelResponse.executeBatch();
        stmtDelResponse.clearBatch();
        count = 0;
    }
}
 
Example 3
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();
}
 
Example 4
private int insert(Connection con, List<Map<String, String>> list)
        throws SQLException {
    PreparedStatement ps;

    String sql = "insert into travelrecord (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(1, (String) map.get("user_id"));
        ps.setString(2, (String) map.get("traveldate"));
        ps.setString(3, (String) map.get("fee"));
        ps.setString(4, (String) map.get("days"));
        ps.addBatch();

    }
    ps.executeBatch();
    con.commit();
    ps.clearBatch();
    ps.close();
    return list.size();
}
 
Example 5
/**
 * 写BAttribute的内容
 * @param attrs
 * @param parentName
 * @param parentId
 * @throws SQLException
 *             ;
 */
public void insertBAttribute(Map<String, String> attrs, String parentName, int parentId) throws SQLException {
	if (attrs != null) {
		PreparedStatement stmt = null;
		String sql = dbConfig.getOperateDbSQL("insert-battribute");
		Iterator<Entry<String, String>> iter = attrs.entrySet().iterator();
		try {
			while (iter.hasNext()) {
				Entry<String, String> entry = iter.next();
				String attrName = entry.getKey();
				String attrValue = entry.getValue();
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, parentId);
				stmt.setString(2, attrName);
				stmt.setString(3, attrValue);
				stmt.setString(4, parentName);
				stmt.addBatch();
			}
			stmt.executeBatch();
			stmt.clearBatch();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
	}
}
 
Example 6
/** Submit and clear the batch, or roll back on error */
private void checkBatchExecution(final PreparedStatement insert) throws Exception
{
    try
    {   // Try to perform the inserts
        // In principle this could return update counts for
        // each batched insert, but Oracle 10g and 11g just throw
        // an exception
        insert.executeBatch();
        connection.commit();
    }
    catch (final Exception ex)
    {
        try
        {
            // On failure, roll back.
            // With Oracle 10g, the BatchUpdateException doesn't
            // indicate which of the batched commands faulted...
            insert.clearBatch();
            // Still: Commit what's committable.
            // Unfortunately no way to know what failed,
            // and no way to re-submit the 'remaining' inserts.
            connection.commit();
        }
        catch (Exception nested)
        {
            logger.log(Level.WARNING, "clearBatch(), commit() error after batch issue", nested);
        }
        throw ex;
    }
}
 
Example 7
/**
 * 写BAttribute的内容
 * @param attrs
 * @param parentName
 * @param parentId
 * @throws SQLException
 *             ;
 */
public void insertBAttribute(Map<String, String> attrs, String parentName, int parentId) throws SQLException {
	if (attrs != null) {
		PreparedStatement stmt = null;
		String sql = dbConfig.getOperateDbSQL("insert-battribute");
		Iterator<Entry<String, String>> iter = attrs.entrySet().iterator();
		try {
			while (iter.hasNext()) {
				Entry<String, String> entry = iter.next();
				String attrName = entry.getKey();
				String attrValue = entry.getValue();
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, parentId);
				stmt.setString(2, attrName);
				stmt.setString(3, attrValue);
				stmt.setString(4, parentName);
				stmt.addBatch();
			}
			stmt.executeBatch();
			stmt.clearBatch();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
	}
}
 
Example 8
Source Project: gemfirexd-oss   File: UseCase1Client.java    License: Apache License 2.0 5 votes vote down vote up
private void generate_SECT_CHANNEL_DATA(Connection conn) throws SQLException {
  if (SECT_CHANNEL_DATA_PS == null) {
    SECT_CHANNEL_DATA_PS = conn.prepareStatement(SECT_CHANNEL_DATA_SQL);
  }
  PreparedStatement stmt = SECT_CHANNEL_DATA_PS;

  int batchSize = UseCase1Prms.getBatchSize();
  int numSectChannelDataRows = UseCase1Prms.getNumSectChannelDataRows();

  String tgname = RemoteTestModule.getCurrentThread().getThreadGroupName();
  int tgthreads = TestConfig.getInstance().getThreadGroup(tgname).getTotalThreads();
  int totalBatches = numSectChannelDataRows/(batchSize * tgthreads);
  Log.getLogWriter().info("Generating " + (totalBatches * batchSize)
                    + " rows of data using " + tgthreads + " threads");
  if (totalBatches <= 0) {
    String s = "Cannot generate data with only " + (totalBatches * batchSize)
             + " rows. Fix" +
               " UseCase1Prms.numSectChannelDataRows=" + numSectChannelDataRows +
               " UseCase1Prms.batchSize=" + batchSize +
               " this.numThreads=" + this.numThreads;
    throw new QueryPerfException(s);
  }
  for (int i = 0; i < totalBatches; i++) {
    long start = this.useCase1stats.startStmt(Stmt.insertSectChannelData);
    for (int j = 0; j < batchSize; j++) {
      fillSectChannelDataStmt(conn, stmt);
      stmt.addBatch();
    }
    stmt.executeBatch();
    conn.commit();
    stmt.clearBatch();
    this.useCase1stats.endStmt(Stmt.insertSectChannelData, start, batchSize, null);
  }
  stmt.close();
}
 
Example 9
public int fillAndExecutePreparedInsertStatements(List pstmts, List stmts, int sid)
    throws SQLException {
  int numInstrumentsPerSector = SectorPrms.getNumInstrumentsPerSector();
  PreparedStatement pstmt = (PreparedStatement) pstmts.get(0);
  String stmt = (String)stmts.get(0);
  int results = 0;
  for (int i = 0; i < numInstrumentsPerSector; i++) {
    int id = sid * numInstrumentsPerSector + i; // unique
    String typeName = Instrument.getInstrument(id);
    pstmt.setString(1, typeName);
    pstmt.setInt(2, sid);
    //pstmt.setString(3, typeName);
    if (logQueries) {
      Log.getLogWriter().info("EXECUTING: " + stmt + " with id=" + typeName
         + " sector_id=" + sid);
    }
    results += positionQueryFactory.fillAndExecutePreparedInsertStatements(
        (List)pstmts.get(1), (List)stmts.get(1), id);
    pstmt.addBatch();
    results++;
  }
  pstmt.executeBatch();
  pstmt.clearBatch();
  Connection c = pstmt.getConnection();
  if (c.getTransactionIsolation() != Connection.TRANSACTION_NONE
      && SectorPrms.commitBatches()) {
    c.commit();
  }
  return results;
}
 
Example 10
@Override
protected void onEnd(PreparedStatement statement) throws SQLException {
    int[] updateCount = statement.executeBatch();
    statement.clearBatch();
}
 
Example 11
Source Project: sagacity-sqltoy   File: SqlUtilsExt.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * @todo 通过jdbc方式批量插入数据,一般提供给数据采集时或插入临时表使用
 * @param updateSql
 * @param rowDatas
 * @param fieldsType
 * @param fieldsDefaultValue
 * @param fieldsNullable
 * @param batchSize
 * @param autoCommit
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
private static Long batchUpdateByJdbc(final String updateSql, final List<Object[]> rowDatas,
		final Integer[] fieldsType, final String[] fieldsDefaultValue, final Boolean[] fieldsNullable,
		final int batchSize, final Boolean autoCommit, final Connection conn, final Integer dbType)
		throws Exception {
	if (rowDatas == null || rowDatas.isEmpty()) {
		logger.warn("batchUpdateByJdbc批量插入或修改数据库操作数据为空!");
		return 0L;
	}
	long updateCount = 0;
	PreparedStatement pst = null;
	// 判断是否通过default转换方式插入
	boolean supportDefaultValue = (fieldsDefaultValue != null && fieldsNullable != null) ? true : false;
	try {
		boolean hasSetAutoCommit = false;
		// 是否自动提交
		if (autoCommit != null && autoCommit.booleanValue() != conn.getAutoCommit()) {
			conn.setAutoCommit(autoCommit.booleanValue());
			hasSetAutoCommit = true;
		}
		pst = conn.prepareStatement(updateSql);
		int totalRows = rowDatas.size();
		// 只有一条记录不采用批量
		boolean useBatch = (totalRows > 1) ? true : false;
		Object[] rowData;
		// 批处理计数器
		int meter = 0;
		for (int i = 0; i < totalRows; i++) {
			rowData = rowDatas.get(i);
			if (rowData != null) {
				// 使用对象properties方式传值
				for (int j = 0, n = rowData.length; j < n; j++) {
					if (supportDefaultValue) {
						setParamValue(conn, dbType, pst, rowData[j], fieldsType[j], fieldsNullable[j],
								fieldsDefaultValue[j], j + 1);
					} else {
						SqlUtil.setParamValue(conn, dbType, pst, rowData[j],
								fieldsType == null ? -1 : fieldsType[j], j + 1);
					}
				}
				meter++;
				// 批量
				if (useBatch) {
					pst.addBatch();
					// 判断是否是最后一条记录或到达批次量,执行批处理
					if ((meter % batchSize) == 0 || i + 1 == totalRows) {
						int[] updateRows = pst.executeBatch();
						for (int t : updateRows) {
							updateCount = updateCount + ((t > 0) ? t : 0);
						}
						pst.clearBatch();
					}
				} else {
					pst.execute();
					updateCount = updateCount + ((pst.getUpdateCount() > 0) ? pst.getUpdateCount() : 0);
				}
			}
		}
		// 恢复conn原始autoCommit默认值
		if (hasSetAutoCommit) {
			conn.setAutoCommit(!autoCommit);
		}
	} catch (Exception e) {
		logger.error(e.getMessage(), e);
		throw e;
	} finally {
		try {
			if (pst != null) {
				pst.close();
				pst = null;
			}
		} catch (SQLException se) {
			logger.error(se.getMessage(), se);
		}
	}
	return updateCount;
}
 
Example 12
/**
 * @todo 针对sqlserver timestamp不能保存情况,提供特殊的批量执行方式,跳过类型为timestamp的值
 * @param updateSql
 * @param rowDatas
 * @param batchSize
 * @param updateTypes
 * @param autoCommit
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
private static Long batchUpdateByJdbc(final String updateSql, final List<Object[]> rowDatas, final int batchSize,
		final Integer[] updateTypes, final Boolean autoCommit, final Connection conn, final Integer dbType)
		throws Exception {
	if (rowDatas == null) {
		logger.error("batchUpdateByJdbc:{} 传递的数据为空!", updateSql);
		return 0L;
	}
	PreparedStatement pst = null;
	long updateCount = 0;
	try {
		boolean hasSetAutoCommit = false;
		// 是否自动提交
		if (autoCommit != null && !autoCommit == conn.getAutoCommit()) {
			conn.setAutoCommit(autoCommit);
			hasSetAutoCommit = true;
		}
		pst = conn.prepareStatement(updateSql);
		int totalRows = rowDatas.size();
		boolean useBatch = (totalRows > 1) ? true : false;
		Object[] rowData;
		// 批处理计数器
		int meter = 0;
		int pstIndex = 0;
		for (int i = 0; i < totalRows; i++) {
			rowData = rowDatas.get(i);
			if (rowData != null) {
				// 使用对象properties方式传值
				pstIndex = 0;
				for (int j = 0, n = rowData.length; j < n; j++) {
					// 类型为timestamp 则跳过
					if (!updateTypes[j].equals(java.sql.Types.TIMESTAMP)) {
						SqlUtil.setParamValue(conn, dbType, pst, rowData[j], updateTypes[j], pstIndex + 1);
						pstIndex++;
					}
				}
				meter++;
				if (useBatch) {
					pst.addBatch();
					if ((meter % batchSize) == 0 || i + 1 == totalRows) {
						int[] updateRows = pst.executeBatch();
						for (int t : updateRows) {
							updateCount = updateCount + ((t > 0) ? t : 0);
						}
						pst.clearBatch();
					}
				} else {
					pst.execute();
					updateCount = updateCount + ((pst.getUpdateCount() > 0) ? pst.getUpdateCount() : 0);
				}
			}
		}
		if (hasSetAutoCommit) {
			conn.setAutoCommit(!autoCommit);
		}
	} catch (Exception e) {
		logger.error(e.getMessage(), e);
		e.printStackTrace();
		throw e;
	} finally {
		try {
			if (pst != null) {
				pst.close();
				pst = null;
			}
		} catch (SQLException se) {
			logger.error(se.getMessage(), se);
			se.printStackTrace();
		}
	}
	return updateCount;
}
 
Example 13
Source Project: gemfirexd-oss   File: POCClient.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * TABLE XML_DOC_1
 *   XML_DOC_ID_NBR DECIMAL(19) NOT NULL,
 *   STRUCTURE_ID_NBR DECIMAL(22) NOT NULL,
 *   CREATE_MINT_CD CHAR(1) NOT NULL,
 *   MSG_PAYLOAD_QTY DECIMAL(22) NOT NULL,
 *   MSG_PAYLOAD1_IMG BLOB(2000) NOT NULL,
 *   MSG_PAYLOAD2_IMG BLOB(2000),
 *   MSG_PAYLOAD_SIZE_NBR DECIMAL(22),
 *   MSG_PURGE_DT DATE,
 *   DELETED_FLG CHAR(1) NOT NULL,
 *   LAST_UPDATE_SYSTEM_NM VARCHAR(30),
 *   LAST_UPDATE_TMSTP TIMESTAMP NOT NULL,
 *   MSG_MAJOR_VERSION_NBR DECIMAL(22),
 *   MSG_MINOR_VERSION_NBR DECIMAL(22),
 *   OPT_LOCK_TOKEN_NBR DECIMAL(22) DEFAULT 1,
 *   PRESET_DICTIONARY_ID_NBR DECIMAL(22) DEFAULT 0 NOT NULL
 */
private void useCase2LoadData() throws SQLException {
  long currentTime = System.currentTimeMillis();
  Date date = new Date(currentTime);
  Timestamp timestamp = new Timestamp(currentTime);
  SerialBlob blob = new SerialBlob("12345678452984560289456029847609487234785012934857109348156034650234560897628900985760289207856027895602785608560786085602857602985760206106110476191087345601456105610478568347562686289765927868972691785634975604562056104762978679451308956205620437861508561034756028475180756917856190348756012876510871789546913485620720476107856479238579385923847934".getBytes(Charset.forName("UTF-8")));

  String stmt = "INSERT INTO XML_DOC_1 ("
              + " LAST_UPDATE_SYSTEM_NM,"    //  1
              + " XML_DOC_ID_NBR,"           //  2
              + " MSG_PAYLOAD1_IMG,"         //  3
              + " MSG_PAYLOAD_QTY,"          //  4
              + " MSG_MINOR_VERSION_NBR,"    //  5
              + " PRESET_DICTIONARY_ID_NBR," //  6
              + " CREATE_MINT_CD,"           //  7
              + " MSG_PAYLOAD_SIZE_NBR,"     //  8
              + " DELETED_FLG,"              //  9
              + " STRUCTURE_ID_NBR,"         // 10
              + " MSG_PURGE_DT,"             // 11
              + " OPT_LOCK_TOKEN_NBR,"       // 12
              + " MSG_MAJOR_VERSION_NBR,"    // 13
              + " LAST_UPDATE_TMSTP"         // 14
              + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  PreparedStatement pstmt = this.connection.prepareStatement(stmt);

  int numBatches = POCPrms.getNumBatches();
  int batchSize = POCPrms.getBatchSize();

  Log.getLogWriter().info("Generating " + numBatches + " batches of " + batchSize);
  for (int i = 0; i < numBatches; i++) {
    for (int j = 0; j < batchSize; j++) {
      pstmt.setString(1, USECASE2_APP);
      pstmt.setInt(2, i * batchSize + j);
      pstmt.setBlob(3, blob);
      pstmt.setBigDecimal(4, BIG_DECIMAL_1);
      pstmt.setBigDecimal(5, BIG_DECIMAL_1);
      pstmt.setBigDecimal(6, BIG_DECIMAL_1);
      pstmt.setString(7, N_FLAG);
      pstmt.setBigDecimal(8, BIG_DECIMAL_1);
      pstmt.setString(9, N_FLAG);
      pstmt.setBigDecimal(10, BIG_DECIMAL_9);
      pstmt.setDate(11, date);
      pstmt.setBigDecimal(12, BIG_DECIMAL_1);
      pstmt.setBigDecimal(13, BIG_DECIMAL_1);
      pstmt.setTimestamp(14, timestamp);
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.clearBatch();
  }
  pstmt.close();
}
 
Example 14
Source Project: gemfirexd-oss   File: BlobTest.java    License: Apache License 2.0 4 votes vote down vote up
private void insertDefaultData() throws Exception {
  PreparedStatement ps = getPreparedStatement("INSERT INTO testClob "
      + "(a, b, c) VALUES (?, ?, ?)");

  String clobValue = "";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();
  clobValue = "you can lead a horse to water but you can't form it "
      + "into beverage";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();
  clobValue = "a stitch in time says ouch";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();
  clobValue = "here is a string with a return \n character";
  ps.setString(1, clobValue);
  ps.setInt(2, clobValue.length());
  ps.setLong(3, 0);
  ps.addBatch();

  ps.executeBatch();
  ps.clearBatch();

  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(), 0);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(), 56);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(),
      5000);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(),
      10000);
  insertLoopingAlphabetStreamData(ps, CharAlphabet.modernLatinLowercase(),
      300000);

  ps.setNull(1, Types.CLOB);
  ps.setInt(2, 0);
  ps.setLong(3, 0);
  ps.executeUpdate();

  ps.close();

  ps = getPreparedStatement("INSERT INTO testBlob (a, b, crc32) "
      + "VALUES (?, ?, ?)");

  byte[] blobValue = "".getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();
  blobValue = ("you can lead a horse to water but you can't form it "
      + "into beverage").getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();
  blobValue = "a stitch in time says ouch".getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();
  blobValue = "here is a string with a return \n character"
      .getBytes("US-ASCII");
  ps.setBytes(1, blobValue);
  ps.setInt(2, blobValue.length);
  ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream(blobValue)));
  ps.addBatch();

  ps.executeBatch();
  ps.clearBatch();

  insertLoopingAlphabetStreamData(ps, 0);
  insertLoopingAlphabetStreamData(ps, 56);
  insertLoopingAlphabetStreamData(ps, 5000);
  insertLoopingAlphabetStreamData(ps, 10000);
  insertLoopingAlphabetStreamData(ps, 300000);

  ps.setNull(1, Types.BLOB);
  ps.setInt(2, 0);
  ps.setNull(3, Types.BIGINT);
  ps.executeUpdate();

  ps.close();
}
 
Example 15
/**
 * 批量添加扩展属性
 * @param parentName
 * @param parentIds
 * @param attName
 * @param attriValue
 * @param monitor
 * @return
 * @throws SQLException
 *             ;
 */
public boolean addExAttributes(String parentName, List<Integer> parentIds, String attName, String attriValue,
		IProgressMonitor monitor) throws SQLException {
	if (parentIds.isEmpty()) {
		return false;
	}
	int total = parentIds.size() / 1000;
	monitor.beginTask("", total == 0 ? 1 : total);
	// sql : INSERT INTO MEXTRA (PARENTNAME , PARENTID ,NTYPE ,NNAME,CONTENT) VALUES(?,?,'A',?,?)
	String insertPropSql = dbConfig.getOperateDbSQL("add-attribute");
	PreparedStatement prepareStatement = conn.prepareStatement(insertPropSql);
	int count = 0;
	int[] executeBatch = new int[0];
	boolean flag = false;
	for (int id : parentIds) {
		prepareStatement.setString(1, parentName);
		prepareStatement.setInt(2, id);
		prepareStatement.setString(3, attName);
		prepareStatement.setString(4, attriValue);
		count++;
		prepareStatement.addBatch();
		if (count % 1000 == 0) {
			executeBatch = prepareStatement.executeBatch();
			prepareStatement.clearBatch();
			if (executeBatch.length > 0) {
				flag = true;
			}
			count = 0;
			monitor.worked(1);
		}
	}
	if (count > 0) {
		executeBatch = prepareStatement.executeBatch();
		prepareStatement.clearBatch();
		if (executeBatch.length > 0) {
			flag = true;
		}
		monitor.worked(1);
	}
	prepareStatement.close();
	monitor.done();
	return flag;
}
 
Example 16
/**
 * @param conn
 * @param idPId
 * @param addedRoles
 * @param deletedRoles
 * @param renamedOldRoles
 * @param renamedNewRoles
 * @throws SQLException
 */
private void updateIdPRoles(Connection conn, int idPId, List<String> addedRoles,
                            List<String> deletedRoles, List<String> renamedOldRoles, List<String> renamedNewRoles)
        throws SQLException {

    PreparedStatement prepStmt1 = null;
    PreparedStatement prepStmt2 = null;
    PreparedStatement prepStmt3 = null;
    String sqlStmt = null;

    try {

        for (String deletedRole : deletedRoles) {
            sqlStmt = IdPManagementConstants.SQLQueries.DELETE_IDP_ROLES_SQL;
            prepStmt1 = conn.prepareStatement(sqlStmt);
            prepStmt1.setInt(1, idPId);
            prepStmt1.setString(2, deletedRole);
            prepStmt1.addBatch();
        }

        prepStmt1.executeBatch();

        for (String addedRole : addedRoles) {
            sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_ROLES_SQL;
            prepStmt2 = conn.prepareStatement(sqlStmt);
            prepStmt2.setInt(1, idPId);
            prepStmt2.setString(2, addedRole);
            prepStmt2.addBatch();
        }

        prepStmt2.executeBatch();
        prepStmt2.clearParameters();
        prepStmt2.clearBatch();

        for (int i = 0; i < renamedOldRoles.size(); i++) {
            sqlStmt = IdPManagementConstants.SQLQueries.UPDATE_IDP_ROLES_SQL;
            prepStmt3 = conn.prepareStatement(sqlStmt);
            prepStmt3.setString(1, renamedNewRoles.get(i));
            prepStmt3.setInt(2, idPId);
            prepStmt3.setString(3, renamedOldRoles.get(i));
            prepStmt3.addBatch();
        }

        prepStmt3.executeBatch();

    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt3);
        IdentityDatabaseUtil.closeStatement(prepStmt2);
        IdentityDatabaseUtil.closeStatement(prepStmt1);
    }

}
 
Example 17
Source Project: gemfirexd-oss   File: TPCCDUnit.java    License: Apache License 2.0 4 votes vote down vote up
private void populateTablesAndDelete(boolean largeData) throws SQLException,
    InterruptedException {

  PreparedStatement customers = TestUtil.jdbcConn
      .prepareStatement("insert into customer values(?,?,?)");
  PreparedStatement neworders = TestUtil.jdbcConn
      .prepareStatement("insert into new_order values(?,?,?)");

  for (int w_id = 1; w_id <= 2; w_id++) {
    for (int d_id = 1; d_id <= 10; d_id++) {
      for (int c_id = 1; c_id <= (largeData ? 3000 : 100); c_id++) {
        customers.setInt(1, w_id);
        customers.setInt(2, d_id);
        customers.setInt(3, c_id);
        customers.addBatch();

        if (c_id > (largeData ? 2100 : 10)) {
          neworders.setInt(1, w_id);
          neworders.setInt(2, d_id);
          neworders.setInt(3, c_id);
          neworders.addBatch();
        }
      }
      getLogWriter().info("committing for d_id=" + d_id + " w_id=" + w_id);
      customers.executeBatch();
      neworders.executeBatch();
      customers.clearBatch();
      neworders.clearBatch();
    }
  }

  Thread terminals[] = new Thread[20];
  for (int i = terminals.length; i > 0; i--)
    terminals[i - 1] = new Thread(new ticket40864Query(i),
        "40864Query thread " + i);

  getLogWriter().info("spwaned all the threads");

  for (int i = terminals.length; i > 0; i--)
    terminals[i - 1].start();

  getLogWriter().info("started all the threads");

  for (int i = terminals.length; i > 0; i--)
    terminals[i - 1].join();
}
 
Example 18
Source Project: gemfirexd-oss   File: BatchUpdateTest.java    License: Apache License 2.0 4 votes vote down vote up
public void testCombinationsOfClearPreparedStatBatch()
throws SQLException {

    int updateCount[];

    println("Positive Prepared Stat: add 3 statements, " +
        "clear batch and execute batch");
    PreparedStatement pStmt =
        prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    pStmt.clearBatch();
    /* there were 0 statements in the batch,
     * update count length should be 0 */
    assertBatchUpdateCounts(new int[] {}, pStmt.executeBatch());

    println("Positive Prepared Stat: " +
        "add 3 statements, clear batch, add 3 and execute batch");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    pStmt.clearBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();

    assertBatchUpdateCounts(new int[] {1,1,1}, pStmt.executeBatch());

    assertTableRowCount("T1", 3);

    pStmt.close();

    commit();
}
 
Example 19
Source Project: gemfirexd-oss   File: POCClient.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * TABLE XML_DOC_1
 *   XML_DOC_ID_NBR DECIMAL(19) NOT NULL,
 *   STRUCTURE_ID_NBR DECIMAL(22) NOT NULL,
 *   CREATE_MINT_CD CHAR(1) NOT NULL,
 *   MSG_PAYLOAD_QTY DECIMAL(22) NOT NULL,
 *   MSG_PAYLOAD1_IMG BLOB(2000) NOT NULL,
 *   MSG_PAYLOAD2_IMG BLOB(2000),
 *   MSG_PAYLOAD_SIZE_NBR DECIMAL(22),
 *   MSG_PURGE_DT DATE,
 *   DELETED_FLG CHAR(1) NOT NULL,
 *   LAST_UPDATE_SYSTEM_NM VARCHAR(30),
 *   LAST_UPDATE_TMSTP TIMESTAMP NOT NULL,
 *   MSG_MAJOR_VERSION_NBR DECIMAL(22),
 *   MSG_MINOR_VERSION_NBR DECIMAL(22),
 *   OPT_LOCK_TOKEN_NBR DECIMAL(22) DEFAULT 1,
 *   PRESET_DICTIONARY_ID_NBR DECIMAL(22) DEFAULT 0 NOT NULL
 */
private void useCase2LoadData() throws SQLException {
  long currentTime = System.currentTimeMillis();
  Date date = new Date(currentTime);
  Timestamp timestamp = new Timestamp(currentTime);
  SerialBlob blob = new SerialBlob("12345678452984560289456029847609487234785012934857109348156034650234560897628900985760289207856027895602785608560786085602857602985760206106110476191087345601456105610478568347562686289765927868972691785634975604562056104762978679451308956205620437861508561034756028475180756917856190348756012876510871789546913485620720476107856479238579385923847934".getBytes(Charset.forName("UTF-8")));

  String stmt = "INSERT INTO XML_DOC_1 ("
              + " LAST_UPDATE_SYSTEM_NM,"    //  1
              + " XML_DOC_ID_NBR,"           //  2
              + " MSG_PAYLOAD1_IMG,"         //  3
              + " MSG_PAYLOAD_QTY,"          //  4
              + " MSG_MINOR_VERSION_NBR,"    //  5
              + " PRESET_DICTIONARY_ID_NBR," //  6
              + " CREATE_MINT_CD,"           //  7
              + " MSG_PAYLOAD_SIZE_NBR,"     //  8
              + " DELETED_FLG,"              //  9
              + " STRUCTURE_ID_NBR,"         // 10
              + " MSG_PURGE_DT,"             // 11
              + " OPT_LOCK_TOKEN_NBR,"       // 12
              + " MSG_MAJOR_VERSION_NBR,"    // 13
              + " LAST_UPDATE_TMSTP"         // 14
              + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  PreparedStatement pstmt = this.connection.prepareStatement(stmt);

  int numBatches = POCPrms.getNumBatches();
  int batchSize = POCPrms.getBatchSize();

  Log.getLogWriter().info("Generating " + numBatches + " batches of " + batchSize);
  for (int i = 0; i < numBatches; i++) {
    for (int j = 0; j < batchSize; j++) {
      pstmt.setString(1, USECASE2_APP);
      pstmt.setInt(2, i * batchSize + j);
      pstmt.setBlob(3, blob);
      pstmt.setBigDecimal(4, BIG_DECIMAL_1);
      pstmt.setBigDecimal(5, BIG_DECIMAL_1);
      pstmt.setBigDecimal(6, BIG_DECIMAL_1);
      pstmt.setString(7, N_FLAG);
      pstmt.setBigDecimal(8, BIG_DECIMAL_1);
      pstmt.setString(9, N_FLAG);
      pstmt.setBigDecimal(10, BIG_DECIMAL_9);
      pstmt.setDate(11, date);
      pstmt.setBigDecimal(12, BIG_DECIMAL_1);
      pstmt.setBigDecimal(13, BIG_DECIMAL_1);
      pstmt.setTimestamp(14, timestamp);
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.clearBatch();
  }
  pstmt.close();
}
 
Example 20
public int fillAndExecutePreparedInsertStatements(List pstmts, List stmts, int iid)
    throws SQLException {

  String instrument = Instrument.getInstrument(iid);
  int numPositionsPerInstrument = SectorPrms.getNumPositionsPerInstrument();
  int numBookValues = SectorPrms.getNumBookValues();
  PreparedStatement pstmt = (PreparedStatement) pstmts.get(0);
  String stmt = (String)stmts.get(0);
  int synthetic = 0;
  int results = 0;
  for (int i = 0; i < numPositionsPerInstrument; i++) {
    int id = iid * numPositionsPerInstrument + i; // unique
    int amount = id;
    int bookId = id % numBookValues;
    String owner = Position.getOwner(id);
    synthetic = 1 - synthetic;
    String symbol = Position.getSymbol(id);
    pstmt.setInt(1, id);
    pstmt.setInt(2, bookId);
    pstmt.setString(3, instrument);
    pstmt.setInt(4, amount);
    pstmt.setInt(5, synthetic);
    pstmt.setString(6, owner);
    pstmt.setString(7, symbol);
    if (logQueries) {
      Log.getLogWriter().info("EXECUTING: " + stmt + " with id=" + id
         + " book_id=" + bookId + " instrument=" + instrument
         + " amount=" + amount + " synthetic=" + synthetic
         + " owner=" + owner + " symbol=" + symbol);
    }
    results += riskQueryFactory.fillAndExecutePreparedInsertStatements((List) pstmts
        .get(1), (List)stmts.get(1), id);
    pstmt.addBatch();
    results++;
  }
  pstmt.executeBatch();
  pstmt.clearBatch();
  List rpstmts = (List)pstmts.get(1);
  PreparedStatement rpstmt = (PreparedStatement)rpstmts.get(rpstmts.size() - 1);
  rpstmt.executeBatch();
  rpstmt.clearBatch();
  Connection c = pstmt.getConnection();
  if (c.getTransactionIsolation() != Connection.TRANSACTION_NONE
      && SectorPrms.commitBatches()) {
    c.commit();
  }
  return results;
}