List of usage examples for java.sql Statement clearBatch
void clearBatch() throws SQLException;
Statement object's current list of SQL commands. From source file:com.mysql.stresstool.RunnableQueryInsertPartRange.java
public int[] executeSQL(Statement stmt) throws Exception { int[] iLine = new int[0]; try {/* ww w .java 2s. co m*/ iLine = stmt.executeBatch(); stmt.clearBatch(); } catch (SQLException sqle) { if ((sqle.getErrorCode() == 1205) && lockRetry < 4) { lockRetry++; System.out.println("Lock Found for thread = " + Thread.currentThread().getId() + " repeat N: " + lockRetry + " OF 3"); iLine = executeSQL(stmt); try { iLine = executeSQL(stmt); Thread.sleep(1000); } catch (InterruptedException e) { System.out.println("Error from JDBC for thread = " + Thread.currentThread().getId() + " | " + sqle.getErrorCode() + " : " + sqle.getMessage() + "\n Reducing the FLOW and try to recover transaction"); Thread.sleep(2000); executeSQL(stmt); // TODO Auto-generated catch block System.out.println("========= Reporting stack trace for debug ========="); e.printStackTrace(); } } else if (sqle.getErrorCode() > 0 && sqle.getErrorCode() != 1205 && lockRetry < 4) { lockRetry++; System.out.println("ERROR Found for thread = " + Thread.currentThread().getId() + " repeat N: " + lockRetry + " OF 3"); try { iLine = executeSQL(stmt); Thread.sleep(1000); } catch (InterruptedException e) { System.out.println("Error from JDBC for thread = " + Thread.currentThread().getId() + " | " + sqle.getErrorCode() + " : " + sqle.getMessage() + "\n Reducing the FLOW and try to recover transaction"); Thread.sleep(2000); executeSQL(stmt); // TODO Auto-generated catch block System.out.println("========= Reporting stack trace for debug ========="); e.printStackTrace(); } } else if (lockRetry >= 4) { stmt.clearBatch(); try { stmt.execute("ROLLBACK"); } catch (Exception eex) { } lockRetry = 0; System.out.println("Error from JDBC for thread = " + Thread.currentThread().getId() + " | " + sqle.getErrorCode() + " : " + sqle.getMessage() + " Aborting"); //throw new Exception(sqle); return iLine; } } catch (Exception ex) { throw new Exception(ex); } return iLine; }
From source file:edu.ku.brc.specify.tasks.RecordSetTask.java
/** * Delete a record set//from www. java 2s .c om * @param recordSet the recordSet to be deleted */ protected void deleteRecordSet(final RecordSetIFace recordSet) { UsageTracker.incrUsageCount("RS.DEL"); // Deleting this manually because the RecordSet may not be loaded (with Hibernate) // and the items are loaded EAGER, and there is not reason to take all the time (and memory) // to load them all just to delete them. // So doing this manually with JDBC is the faster way. Connection connection = null; Statement updateStatement = null; try { connection = DBConnection.getInstance().createConnection(); String deleteRS = "DELETE FROM recordset WHERE RecordSetID = " + recordSet.getRecordSetId(); String deleteRSI = "DELETE FROM recordsetitem WHERE RecordSetID = " + recordSet.getRecordSetId(); updateStatement = connection.createStatement(); updateStatement.executeUpdate(deleteRSI); updateStatement.executeUpdate(deleteRS); updateStatement.clearBatch(); updateStatement.close(); } catch (Exception ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(RecordSetTask.class, ex); ex.printStackTrace(); } finally { try { if (updateStatement != null) { updateStatement.close(); } if (connection != null) { connection.close(); } } catch (Exception e) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(RecordSetTask.class, e); e.printStackTrace(); } } }
From source file:edu.ku.brc.specify.conversion.IdHashMapper.java
/** * Initializes the Hash Database Table./* w w w . j a v a 2s.c om*/ */ protected void init(final boolean checkOldDB) { oldConn = IdMapperMgr.getInstance().getOldConnection(); int numRecs = checkOldDB ? BasicSQLUtils.getNumRecords(oldConn, tableName) : 0; int mappingCount = oldConn != null ? getMapCount(mapTableName) : 0; wasEmpty = mappingCount == 0; log.info(numRecs + " Records in " + tableName + " wasEmpty: " + wasEmpty); try { if (enableRemoveRecords) { wasEmpty = true; if (BasicSQLUtils.doesTableExist(oldConn, mapTableName)) { try { String sql = String.format("DELETE FROM %s", mapTableName); BasicSQLUtils.update(oldConn, sql); } catch (Exception ex) { } } return; } if ((doDelete || mappingCount == 0) && enableDelete) { wasEmpty = true; Statement stmt = oldConn.createStatement(); String str = "DROP TABLE " + mapTableName; try { log.info(str); stmt.executeUpdate(str); } catch (Exception ex) { // Exception may occur if table doesn't exist } str = "CREATE TABLE `" + mapTableName + "` (" + "`OldID` int(11) NOT NULL default '0', " + "`NewID` int(11) NOT NULL default '0', " + " PRIMARY KEY (`OldID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1"; if (debug) log.info("orig sql: " + str); str = BasicSQLUtils.getServerTypeSpecificSQL(str, BasicSQLUtils.myDestinationServerType); if (debug) log.info("sql standard query: " + str); stmt.executeUpdate(str); String str2 = "ALTER TABLE " + mapTableName + " ADD INDEX INX_" + mapTableName + " (NewID)"; if (debug) log.info("orig sql: " + str2); str2 = BasicSQLUtils.createIndexFieldStatment(mapTableName, BasicSQLUtils.myDestinationServerType); if (debug) log.info("sql standard query: " + str2); stmt.executeUpdate(str2); stmt.clearBatch(); stmt.close(); } } catch (SQLException ex) { edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(IdHashMapper.class, ex); // log.error(ex); ex.printStackTrace(); } }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * Deletes all the records from a table//from www . ja v a 2 s . co m * @param connection connection to the DB * @param tableName the name of the table * @return the return value from the SQL update statement (or -1 on an exception) */ public static int deleteAllRecordsFromTable(final Connection connection, final String tableName, final SERVERTYPE currentServerType) { try { if (doesTableExist(connection, tableName)) { Integer count = getCount(connection, "SELECT COUNT(*) FROM " + tableName); if (count == null || count == 0) { return 0; } Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (currentServerType != SERVERTYPE.MS_SQLServer) { removeForeignKeyConstraints(stmt.getConnection(), currentServerType); } int retVal = exeUpdateCmd(stmt, "delete from " + tableName); stmt.clearBatch(); stmt.close(); log.info("Deleted " + count + " records from " + tableName); return retVal; } } catch (SQLException ex) { edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex); log.error(ex); ex.printStackTrace(); } return 0; }
From source file:net.ymate.platform.persistence.jdbc.base.impl.BatchUpdateOperator.java
protected int __doExecute() throws Exception { Statement _statement = null; AccessorEventContext _context = null; try {/* w w w . j a v a 2 s . co m*/ IAccessor _accessor = new BaseAccessor(this.getAccessorConfig()); if (StringUtils.isNotBlank(this.getSQL())) { _statement = _accessor.getPreparedStatement(this.getConnectionHolder().getConnection(), this.getSQL()); // for (SQLBatchParameter _batchParam : this.__batchParameters) { for (int i = 0; i < _batchParam.getParameters().size(); i++) { SQLParameter _param = _batchParam.getParameters().get(i); if (_param.getValue() == null) { ((PreparedStatement) _statement).setNull(i + 1, 0); } else { ((PreparedStatement) _statement).setObject(i + 1, _param.getValue()); } } ((PreparedStatement) _statement).addBatch(); } } else { _statement = _accessor.getStatement(this.getConnectionHolder().getConnection()); } // for (String _batchSQL : this.__batchSQL) { _statement.addBatch(_batchSQL); } // if (this.getAccessorConfig() != null) { this.getAccessorConfig().beforeStatementExecution( _context = new AccessorEventContext(_statement, JDBC.DB_OPERATION_TYPE.BATCH_UPDATE)); } effectCounts = _statement.executeBatch(); // ?? int _count = 0; for (int _c : effectCounts) { _count += _c; } return _count; } finally { if (this.getAccessorConfig() != null && _context != null) { this.getAccessorConfig().afterStatementExecution(_context); } if (_statement != null) { _statement.clearBatch(); _statement.close(); } } }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * Removes all the records from all the tables */// w w w .j a v a 2 s. c o m public static void cleanAllTables(final Connection connection, SERVERTYPE currentServerType) { try { Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery("show tables"); if (rs.first()) { do { String tableName = rs.getString(1); //System.out.println("Deleting Records from "+tableName); deleteAllRecordsFromTable(connection, tableName, currentServerType); } while (rs.next()); } rs.close(); stmt.clearBatch(); stmt.close(); } catch (SQLException ex) { edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex); log.error(ex); ex.printStackTrace(); } }
From source file:edu.ku.brc.specify.conversion.AgentConverter.java
/** * @param oldAgentId//from w w w .j a v a 2 s . com * @param agentIDMapper * @param tblWriter */ protected void copyAgentFromOldToNew(final Integer oldAgentId, final IdTableMapper agentIDMapper) { boolean doDebug = false; DBTableInfo agentTI = DBTableIdMgr.getInstance().getByShortClassName("Agent"); DBFieldInfo lastNameField = agentTI.getFieldByColumnName("LastName"); DBFieldInfo firstNameField = agentTI.getFieldByColumnName("FirstName"); StringBuilder sql = new StringBuilder("SELECT "); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { BasicSQLUtils.removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); } BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); List<String> oldAgentFieldNames = getFieldNamesFromSchema(oldDBConn, "agent"); String oldFieldListStr = buildSelectFieldList(oldAgentFieldNames, "agent"); sql.append(oldFieldListStr); sql.append(" FROM agent WHERE AgentID = " + oldAgentId); //log.info(oldFieldListStr); List<String> newAgentFieldNames = getFieldNamesFromSchema(newDBConn, "agent"); String newFieldListStr = buildSelectFieldList(newAgentFieldNames, "agent"); //log.info(newFieldListStr); int lastNameLen = 120; HashMap<String, Integer> oldIndexFromNameMap = new HashMap<String, Integer>(); int inx = 1; for (String fldName : oldAgentFieldNames) { oldIndexFromNameMap.put(fldName, inx++); } HashMap<String, Integer> newIndexFromNameMap = new HashMap<String, Integer>(); inx = 1; for (String fldName : newAgentFieldNames) { newIndexFromNameMap.put(fldName, inx++); } try { // So first we hash each AddressID and the value is set to 0 (false) Statement stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rsX = stmtX.executeQuery(sql.toString()); int agentIDInx = oldIndexFromNameMap.get("AgentID"); int agentTypeInx = oldIndexFromNameMap.get("AgentType"); int nameInx = oldIndexFromNameMap.get("Name"); int lastNameInx = oldIndexFromNameMap.get("LastName"); int firstNameInx = oldIndexFromNameMap.get("FirstName"); // log.debug(sql.toString()); int cnt = 0; while (rsX.next()) { int agentId = rsX.getInt(1); StringBuilder sqlStr = new StringBuilder(); sqlStr.append("INSERT INTO agent "); sqlStr.append("(" + newFieldListStr); sqlStr.append(")"); sqlStr.append(" VALUES ("); int fCnt = 0; for (String fieldName : newAgentFieldNames) { if (fCnt > 0) sqlStr.append(", "); if (StringUtils.contains(fieldName.toLowerCase(), "disciplineid")) { sqlStr.append(conv.getDisciplineId()); } else if (StringUtils.contains(fieldName, "FirstName")) { String firstName = rsX.getString(firstNameInx); if (firstName != null && firstName.length() > firstNameField.getLength()) { String str = firstName.substring(0, firstNameField.getLength()); tblWriter.logError("Agent id: " + rsX.getString(agentIDInx) + " - Concatinating First Name FROM [" + firstName + "] to [" + str + "]"); firstName = str; } sqlStr.append(BasicSQLUtils.getStrValue(firstName)); } else if (StringUtils.contains(fieldName, "LastName")) { int oldType = rsX.getInt(agentTypeInx); int srcColInx = oldType != 1 ? nameInx : lastNameInx; String lName = rsX.getString(srcColInx); if (lName == null && oldType != 1) { lName = rsX.getString(lastNameInx); } if (lName != null && lName.length() > lastNameField.getLength()) { String str = lName.substring(0, firstNameField.getLength()); tblWriter.logError("Agent id: " + rsX.getString(agentIDInx) + " - Concatinating Last Name FROM [" + lName + "] to [" + str + "]"); lName = str; } String lstName = lName; lName = lstName == null ? null : lstName.length() <= lastNameLen ? lstName : lstName.substring(0, lastNameLen); sqlStr.append(BasicSQLUtils.getStrValue(lName)); } else { String value = ""; Integer index; if (fieldName.equals("ModifiedByAgentID")) { index = oldIndexFromNameMap.get("LastEditedBy"); } else { index = oldIndexFromNameMap.get(fieldName); } if (index == null) { // log.debug(fieldName); value = "NULL"; } else if (fCnt == 0) { value = agentIDMapper.get(agentId).toString(); } else { value = BasicSQLUtils.getStrValue(rsX.getObject(index.intValue())); } BasicSQLUtilsMapValueIFace valueMapper = conv.getColumnValueMapper().get(fieldName); if (valueMapper != null) { value = valueMapper.mapValue(value); } sqlStr.append(value); } fCnt++; } sqlStr.append(")"); // log.info(sqlStr.toString()); Statement updateStatement = newDBConn.createStatement(); // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); if (doDebug) { log.info(sqlStr.toString()); } updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement); if (newAgentId == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; //conv.addAgentDisciplineJoin(newAgentId, conv.getDisciplineId()); cnt++; BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); } } catch (Exception ex) { log.error(ex); ex.printStackTrace(); System.exit(0); } }
From source file:com.mysql.stresstool.RunnableClusterQueryInsert.java
public void run() { BufferedReader d = null;//from w ww .j a v a2 s . c om Connection conn = null; try { if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); } catch (SQLException ex) { ex.printStackTrace(); } if (conn != null) { try { Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); long execTime = 0; int pkStart = 0; int pkEnds = 0; int intDeleteInterval = 0; int intBlobInterval = 0; int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue(); ThreadInfo thInfo; long threadTimeStart = System.currentTimeMillis(); active = true; thInfo = new ThreadInfo(); thInfo.setId(this.ID); thInfo.setType("insert"); thInfo.setStatusActive(this.isActive()); StressTool.setInfo(this.ID, thInfo); boolean lazy = false; int lazyInterval = 0; for (int repeat = 0; repeat <= repeatNumber; repeat++) { String query = null; ArrayList insert1 = null; ArrayList insert2 = null; int pk = 0; if (repeat > 0 && lazyInterval < 500) { lazy = true; ++lazyInterval; } else { lazy = false; lazyInterval = 0; } intBlobInterval++; //IMPLEMENTING lazy Vector v = this.getTablesValues(lazy); insert1 = (ArrayList<String>) v.get(0); insert2 = (ArrayList<String>) v.get(1); // System.out.println(insert1); // System.out.println(insert2); // pk = ((Integer) v.get(2)).intValue(); int[] iLine = { 0, 0 }; // pkStart = StressTool.getNumberFromRandom(2147483647).intValue(); // pkEnds = StressTool.getNumberFromRandom(2147483647).intValue(); try { long timeStart = System.currentTimeMillis(); if (this.ignoreBinlog) stmt.execute("SET sql_log_bin=0"); // stmt.execute("SET GLOBAL max_allowed_packet=1073741824"); if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE")) stmt.execute("BEGIN"); else stmt.execute("COMMIT"); // stmt.execute("SET TRANSACTION NAME 'TEST'"); { Iterator<String> it = insert1.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } } if (!this.doSimplePk) { if (intBlobInterval > intBlobIntervalLimit) { Iterator<String> it = insert2.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } intBlobInterval = 0; } } iLine = stmt.executeBatch(); stmt.clearBatch(); // System.out.println("Query1 = " + insert1); // System.out.println("Query2 = " + insert2); // stmt.execute("START TRANSACTION"); // stmt.execute(insert1); // iLine = stmt.executeBatch(); // conn.commit(); long timeEnds = System.currentTimeMillis(); execTime = (timeEnds - timeStart); } catch (Exception sqle) { conn.rollback(); System.out.println("FAILED QUERY1==" + insert1); System.out.println("FAILED QUERY2==" + insert2); sqle.printStackTrace(); System.exit(1); //conn.close(); //this.setJdbcUrl(jdbcUrl); //System.out.println("Query Insert TH RE-INIZIALIZING"); } finally { // conn.commit(); stmt.execute("COMMIT"); // intDeleteInterval++; if (doLog) { System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " " + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) =" + execTime + " Running = " + repeat + " of " + repeatNumber + " to go =" + (repeatNumber - repeat) + " Using Lazy=" + lazy); } } thInfo.setExecutedLoops(repeat); if (sleepFor > 0 || this.getSleepWrite() > 0) { if (this.getSleepWrite() > 0) { Thread.sleep(getSleepWrite()); } else Thread.sleep(sleepFor); } } long threadTimeEnd = System.currentTimeMillis(); this.executionTime = (threadTimeEnd - threadTimeStart); // this.setExecutionTime(executionTime); active = false; // System.out.println("Query Insert TH = " + this.getID() + " COMPLETED! TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000)); thInfo.setExecutionTime(executionTime); thInfo.setStatusActive(false); StressTool.setInfo(this.ID, thInfo); return; } catch (Exception ex) { ex.printStackTrace(); try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
From source file:com.mysql.stresstool.RunnableSC2013QueryInsert.java
public void run() { BufferedReader d = null;//from w w w .j a v a 2 s . c o m Connection conn = null; try { if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); } catch (SQLException ex) { ex.printStackTrace(); } if (conn != null) { try { Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); long execTime = 0; int pkStart = 0; int pkEnds = 0; int intDeleteInterval = 0; int intBlobInterval = 0; int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue(); ThreadInfo thInfo; long threadTimeStart = System.currentTimeMillis(); active = true; thInfo = new ThreadInfo(); thInfo.setId(this.ID); thInfo.setType("insert"); thInfo.setStatusActive(this.isActive()); StressTool.setInfo(this.ID, thInfo); boolean lazy = false; int lazyInterval = 0; for (int repeat = 0; repeat <= repeatNumber; repeat++) { String query = null; ArrayList insert1 = null; ArrayList insert2 = null; int pk = 0; if (repeat > 0 && lazyInterval < 100) { lazy = true; ++lazyInterval; } else { lazy = false; lazyInterval = 0; } intBlobInterval++; //IMPLEMENTING lazy Vector v = this.getTablesValues(lazy); insert1 = (ArrayList<String>) v.get(0); insert2 = (ArrayList<String>) v.get(1); // System.out.println(insert1); // System.out.println(insert2); // pk = ((Integer) v.get(2)).intValue(); int[] iLine = { 0, 0 }; // pkStart = StressTool.getNumberFromRandom(2147483647).intValue(); // pkEnds = StressTool.getNumberFromRandom(2147483647).intValue(); try { long timeStart = System.currentTimeMillis(); if (this.ignoreBinlog) stmt.execute("SET sql_log_bin=0"); // stmt.execute("SET GLOBAL max_allowed_packet=1073741824"); if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE")) stmt.execute("BEGIN"); else stmt.execute("COMMIT"); // stmt.execute("SET TRANSACTION NAME 'TEST'"); { Iterator<String> it = insert1.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } } if (!this.doSimplePk) { if (intBlobInterval > intBlobIntervalLimit) { Iterator<String> it = insert2.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } intBlobInterval = 0; } } iLine = stmt.executeBatch(); stmt.clearBatch(); // System.out.println("Query1 = " + insert1); // System.out.println("Query2 = " + insert2); // stmt.execute("START TRANSACTION"); // stmt.execute(insert1); // iLine = stmt.executeBatch(); // conn.commit(); long timeEnds = System.currentTimeMillis(); execTime = (timeEnds - timeStart); } catch (Exception sqle) { conn.rollback(); System.out.println("FAILED QUERY1==" + insert1); System.out.println("FAILED QUERY2==" + insert2); sqle.printStackTrace(); System.exit(1); //conn.close(); //this.setJdbcUrl(jdbcUrl); //System.out.println("Query Insert TH RE-INIZIALIZING"); } finally { // conn.commit(); stmt.execute("COMMIT"); // intDeleteInterval++; if (doLog) { System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " " + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) =" + execTime + " Running = " + repeat + " of " + repeatNumber + " to go =" + (repeatNumber - repeat) + " Using Lazy=" + lazy); } } thInfo.setExecutedLoops(repeat); if (sleepFor > 0 || this.getSleepWrite() > 0) { if (this.getSleepWrite() > 0) { Thread.sleep(getSleepWrite()); } else Thread.sleep(sleepFor); } } long threadTimeEnd = System.currentTimeMillis(); this.executionTime = (threadTimeEnd - threadTimeStart); // this.setExecutionTime(executionTime); active = false; // System.out.println("Query Insert TH = " + this.getID() + " COMPLETED! TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000)); thInfo.setExecutionTime(executionTime); thInfo.setStatusActive(false); StressTool.setInfo(this.ID, thInfo); return; } catch (Exception ex) { ex.printStackTrace(); try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
From source file:com.mysql.stresstool.RunnableQueryInsert.java
public void run() { BufferedReader d = null;/*from w w w . jav a2 s . c o m*/ Connection conn = null; try { if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); } catch (SQLException ex) { ex.printStackTrace(); } if (conn != null) { try { Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); long execTime = 0; int pkStart = 0; int pkEnds = 0; int intDeleteInterval = 0; int intBlobInterval = 0; int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue(); ThreadInfo thInfo; long threadTimeStart = System.currentTimeMillis(); active = true; thInfo = new ThreadInfo(); thInfo.setId(this.ID); thInfo.setType("insert"); thInfo.setStatusActive(this.isActive()); StressTool.setInfo(this.ID, thInfo); boolean lazy = false; int lazyInterval = 0; for (int repeat = 0; repeat <= repeatNumber; repeat++) { String query = null; ArrayList insert1 = null; ArrayList insert2 = null; int pk = 0; if (repeat > 0 && lazyInterval < 500) { lazy = true; ++lazyInterval; } else { lazy = false; lazyInterval = 0; } intBlobInterval++; //IMPLEMENTING lazy Vector v = this.getTablesValues(lazy); insert1 = (ArrayList<String>) v.get(0); insert2 = (ArrayList<String>) v.get(1); // System.out.println(insert1); // System.out.println(insert2); // pk = ((Integer) v.get(2)).intValue(); int[] iLine = { 0, 0 }; // pkStart = StressTool.getNumberFromRandom(2147483647).intValue(); // pkEnds = StressTool.getNumberFromRandom(2147483647).intValue(); try { long timeStart = System.currentTimeMillis(); if (this.ignoreBinlog) stmt.execute("SET sql_log_bin=0"); stmt.execute("SET GLOBAL max_allowed_packet=1073741824"); if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE")) stmt.execute("BEGIN"); else stmt.execute("COMMIT"); // stmt.execute("SET TRANSACTION NAME 'TEST'"); { Iterator<String> it = insert1.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } } if (!this.doSimplePk) { if (intBlobInterval > intBlobIntervalLimit) { Iterator<String> it = insert2.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } intBlobInterval = 0; } } iLine = stmt.executeBatch(); stmt.clearBatch(); // System.out.println("Query1 = " + insert1); // System.out.println("Query2 = " + insert2); // stmt.execute("START TRANSACTION"); // stmt.execute(insert1); // iLine = stmt.executeBatch(); // conn.commit(); long timeEnds = System.currentTimeMillis(); execTime = (timeEnds - timeStart); } catch (Exception sqle) { conn.rollback(); System.out.println("FAILED QUERY1==" + insert1); System.out.println("FAILED QUERY2==" + insert2); sqle.printStackTrace(); System.exit(1); //conn.close(); //this.setJdbcUrl(jdbcUrl); //System.out.println("Query Insert TH RE-INIZIALIZING"); } finally { // conn.commit(); stmt.execute("COMMIT"); // intDeleteInterval++; if (doLog) { System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " " + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) =" + execTime + " Running = " + repeat + " of " + repeatNumber + " to go =" + (repeatNumber - repeat) + " Using Lazy=" + lazy); } } thInfo.setExecutedLoops(repeat); if (sleepFor > 0 || this.getSleepWrite() > 0) { if (this.getSleepWrite() > 0) { Thread.sleep(getSleepWrite()); } else Thread.sleep(sleepFor); } } long threadTimeEnd = System.currentTimeMillis(); this.executionTime = (threadTimeEnd - threadTimeStart); // this.setExecutionTime(executionTime); active = false; // System.out.println("Query Insert TH = " + this.getID() + " COMPLETED! TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000)); thInfo.setExecutionTime(executionTime); thInfo.setStatusActive(false); StressTool.setInfo(this.ID, thInfo); return; } catch (Exception ex) { ex.printStackTrace(); try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }