List of usage examples for java.sql Statement executeBatch
int[] executeBatch() throws SQLException;
From source file:it.cnr.icar.eric.server.persistence.rdb.PostalAddressDAO.java
/** * @param registryObjects is a List of Organizations or Users * @throws RegistryException if the RegistryObject is not Organization or User, * or it has SQLException when inserting their PostalAddress *///from w w w.j ava2 s. c om @SuppressWarnings("resource") public void insert(@SuppressWarnings("rawtypes") List registryObjects) throws RegistryException { Statement stmt = null; if (registryObjects.size() == 0) { return; } try { /* String sqlStr = "INSERT INTO " + getTableName() + " VALUES( " + "?, " + // city "?, " + // country "?, " + // postalCode "?, " + // state "?, " + // street "?, " + // streetNum "?)"; // Parent id PreparedStatement pstmt = context.getConnection().prepareStatement(sqlStr); */ stmt = context.getConnection().createStatement(); Iterator<?> rosIter = registryObjects.iterator(); while (rosIter.hasNext()) { Object ro = rosIter.next(); String parentId = null; PostalAddressType postalAddress = null; if (ro instanceof OrganizationType) { OrganizationType org = (OrganizationType) ro; postalAddress = org.getAddress().get(0); parentId = org.getId(); } else if (ro instanceof UserType) { UserType user = (UserType) ro; //TODO: Save extra addresses, if required postalAddress = user.getAddress().get(0); parentId = user.getId(); } else { throw new RegistryException( ServerResourceBundle.getInstance().getString("message.incorrectRegistryObject")); } /* stmt.setString(1, postalAddress.getCity()); stmt.setString(2, postalAddress.getCountry()); stmt.setString(3, postalAddress.getPostalCode()); stmt.setString(4, postalAddress.getStateOrProvince()); stmt.setString(5, postalAddress.getStreet()); stmt.setString(6, postalAddress.getStreetNumber()); stmt.setString(7, org.getId()); stmt.addBatch();*/ String city = postalAddress.getCity(); if (city != null) { city = "'" + city + "'"; } String country = postalAddress.getCountry(); if (country != null) { country = "'" + country + "'"; } String postalCode = postalAddress.getPostalCode(); if (postalCode != null) { postalCode = "'" + postalCode + "'"; } String state = postalAddress.getStateOrProvince(); if (state != null) { state = "'" + state + "'"; } String street = postalAddress.getStreet(); if (street != null) { street = "'" + street + "'"; } String streetNum = postalAddress.getStreetNumber(); if (streetNum != null) { streetNum = "'" + streetNum + "'"; } String str = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )"; log.trace("stmt = " + str); stmt.addBatch(str); } // end looping all Organizations if (registryObjects.size() > 0) { stmt.executeBatch(); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:org.freebxml.omar.server.persistence.rdb.PostalAddressDAO.java
/** * @param registryObjects is a List of Organizations or Users * @throws RegistryException if the RegistryObject is not Organization or User, * or it has SQLException when inserting their PostalAddress */// w w w . java 2 s.c om public void insert(List registryObjects) throws RegistryException { Statement stmt = null; if (registryObjects.size() == 0) { return; } try { /* String sqlStr = "INSERT INTO " + getTableName() + " VALUES( " + "?, " + // city "?, " + // country "?, " + // postalCode "?, " + // state "?, " + // street "?, " + // streetNum "?)"; // Parent id PreparedStatement pstmt = context.getConnection().prepareStatement(sqlStr); */ stmt = context.getConnection().createStatement(); Iterator rosIter = registryObjects.iterator(); while (rosIter.hasNext()) { Object ro = rosIter.next(); String parentId = null; PostalAddressType postalAddress = null; if (ro instanceof OrganizationType) { OrganizationType org = (OrganizationType) ro; postalAddress = (PostalAddressType) org.getAddress().get(0); parentId = org.getId(); } else if (ro instanceof UserType) { UserType user = (UserType) ro; //TODO: Save extra addresses, if required postalAddress = (PostalAddressType) user.getAddress().get(0); parentId = user.getId(); } else { throw new RegistryException( ServerResourceBundle.getInstance().getString("message.incorrectRegistryObject")); } /* stmt.setString(1, postalAddress.getCity()); stmt.setString(2, postalAddress.getCountry()); stmt.setString(3, postalAddress.getPostalCode()); stmt.setString(4, postalAddress.getStateOrProvince()); stmt.setString(5, postalAddress.getStreet()); stmt.setString(6, postalAddress.getStreetNumber()); stmt.setString(7, org.getId()); stmt.addBatch();*/ String city = postalAddress.getCity(); if (city != null) { city = "'" + city + "'"; } String country = postalAddress.getCountry(); if (country != null) { country = "'" + country + "'"; } String postalCode = postalAddress.getPostalCode(); if (postalCode != null) { postalCode = "'" + postalCode + "'"; } String state = postalAddress.getStateOrProvince(); if (state != null) { state = "'" + state + "'"; } String street = postalAddress.getStreet(); if (street != null) { street = "'" + street + "'"; } String streetNum = postalAddress.getStreetNumber(); if (streetNum != null) { streetNum = "'" + streetNum + "'"; } String str = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )"; log.trace("SQL = " + str); stmt.addBatch(str); } // end looping all Organizations if (registryObjects.size() > 0) { stmt.executeBatch(); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:com.dbmojo.QueryExecutor.java
/** * Execute a set of queries/updates encoded in JSON via <b>reqStr</b> in the * format <br><br><i>[{query:"select x from y",values:[]},{}...] * </i>.<br><br>The <b>update</b> flag determines whether or not to * treat each statement in the <b>reqStr</b> as an update or a query. *///w ww . java2 s . c o m public String execute(String reqStr, boolean update) throws Exception { if (DebugLog.enabled) { DebugLog.add(this, "Begin execute"); } String message = ""; ArrayList<HashMap> resultsList = new ArrayList<HashMap>(); LinkedHashMap<String, PreparedStatement> bpstmts = null; Statement bstmt = null; try { this.open(update); if (update) { conn.setAutoCommit(false); } final JSONArray reqs = new JSONArray(reqStr); final boolean batchUpdates = reqs.length() > 1; //Connection MUST be ready to go if (this.conn == null) { throw new QueryExecutorException("Connection could not be checked out"); } final int rLen = reqs.length(); if (rLen <= 0) { throw new QueryExecutorException("No queries specified"); } for (int r = 0; r < rLen; r++) { String rMessage = ""; final JSONObject reqObj = reqs.getJSONObject(r); JSONArray tValues = reqObj.optJSONArray("values"); String[] values = new String[(tValues != null ? tValues.length() : 0)]; //Convert the JSONArray to a String[] for (int v = 0; v < values.length; v++) { values[v] = tValues.getString(v); } String query = reqObj.getString("query"); final boolean prepared = values != null; //Can't move forward without a query! if (query == null || query.equals("")) { throw new QueryExecutorException("Query is missing"); } //Here's where we need to do either an update or a query if (update) { if (batchUpdates) { // This is NOT a prepared statement and we need to create a // batch statement to add all non prepared statements to if (!prepared && bstmt == null) { bstmt = conn.createStatement(); // This IS a prepared statement and we need to create a // ordered map of prepared statements so we can execute // these statements together in order (sortof...) } else if (prepared && bpstmts == null) { bpstmts = new LinkedHashMap<String, PreparedStatement>(); } addBatchUpdate(this.conn, prepared, query, values, bstmt, bpstmts); } else { // Single update query / prepared statement to execute executeUpdate(this.conn, prepared, query, values); } } else { resultsList.add(executeQuery(this.conn, prepared, query, values)); } } //Execute Batch Updates if (update && batchUpdates) { //Execute any prepared statement batches that have been gathered. //If we have an SQL error and exception will be thrown if (bpstmts != null && bpstmts.size() > 0) { for (PreparedStatement p : bpstmts.values()) { if (DebugLog.enabled) { DebugLog.add(this, "Executing batch prepared statement"); } p.executeBatch(); } } //Execute all the standard SQL in a batch. //If we have a SQL error an Exception will be thrown if (bstmt != null) { if (DebugLog.enabled) { DebugLog.add(this, "Executing batch statement"); } bstmt.executeBatch(); } } if (update) { this.conn.commit(); } } catch (JSONException je) { //There was an error parsing the JSON final String err = je.toString(); if (DebugLog.enabled) { DebugLog.add(this, err); } resultsList.add(Util.getError(err)); } catch (Exception e) { //We couldn't connect to the DB... if (this.conn == null) { final String err = e.toString(); if (ErrorLog.enabled) { ErrorLog.add(this, err, false); } resultsList.add(Util.getError(err)); //There was an error executing the query/update } else if (update) { final String err = "Rolling Back Update(s): " + e; if (DebugLog.enabled) { DebugLog.add(this, err); } if (this.conn != null) { this.conn.rollback(); } resultsList.add(Util.getError(err)); } else { final String err = e.toString(); if (DebugLog.enabled) { DebugLog.add(this, err); } resultsList.add(Util.getError(err)); } } finally { //Cleanup batch statement (If applicable) if (bstmt != null) { try { if (DebugLog.enabled) { DebugLog.add(this, "Closing batch statement"); } bstmt.close(); } catch (Exception se) { String err = "Error closing batch statement - " + se.toString(); if (ErrorLog.enabled) { ErrorLog.add(this, err, false); } resultsList.add(Util.getError(err)); } } //Cleanup the batch prepared statement (If applicable) if (bpstmts != null) { for (PreparedStatement p : bpstmts.values()) { try { if (p != null) { p.close(); if (DebugLog.enabled) { DebugLog.add(this, "Closing batch prepared stmnt"); } } } catch (Exception pse) { String err = "Error closing batch prepared stmnt - " + pse.toString(); if (ErrorLog.enabled) { ErrorLog.add(this, err, false); } resultsList.add(Util.getError(err)); } } } if (DebugLog.enabled) { DebugLog.add(this, "Closing connection"); } //Cleanup DB connection (Always applicable) this.conn.close(); } if (DebugLog.enabled) { DebugLog.add(this, "End execute"); } //UPDATE => [{message:"",status:"success"}] if (update && resultsList.size() <= 0) { HashMap pObj = new HashMap(); pObj.put("message", ""); pObj.put("status", "success"); pObj.put("rows", new ArrayList()); pObj.put("types", new ArrayList()); pObj.put("cols", new ArrayList()); resultsList.add(pObj); } //Serialize resultsArray into JSON return serializeToJson(resultsList); }
From source file:com.mysql.stresstool.RunnableClusterQueryInsert.java
public void run() { BufferedReader d = null;//from ww w . j ava2 s .co 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(); } } } }
From source file:com.mysql.stresstool.RunnableSC2013QueryInsert.java
public void run() { BufferedReader d = null;//from w w w . ja va 2s. 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 < 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.ja v 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(); } } } }
From source file:com.mysql.stresstool.RunnableQueryInsertPCH.java
public void run() { BufferedReader d = null;/*from w w w. j a v a 2 s . co m*/ Connection conn = null; if (conn == null) { try { 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++) { try { if (conn != null && !conn.isClosed()) { conn.close(); } SoftReference sf = new SoftReference( DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"))); conn = (Connection) sf.get(); } catch (SQLException ex) { ex.printStackTrace(); } Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); ResultSet rs = null; int ServerId = 0; String query = null; ArrayList insert1 = null; ArrayList insert2 = null; int pk = 0; { SoftReference sf = new SoftReference( stmt.executeQuery("show global variables like 'SERVER_ID'")); rs = (ResultSet) sf.get(); } rs.next(); ServerId = rs.getInt(2); if (repeat > 0 && lazyInterval < 500) { lazy = true; ++lazyInterval; } else { lazy = false; lazyInterval = 0; } intBlobInterval = StressTool.getNumberFromRandom(10).intValue(); // intBlobInterval++; //IMPLEMENTING lazy Vector v = null; { SoftReference sf = new SoftReference(this.getTablesValues(lazy, ServerId)); v = (Vector) sf.get(); } 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=10737418"); 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) { // System.out.println("Blob insert value :" + intBlobInterval); 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(); if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(("FAILED QUERY1==" + insert1)); StressTool.getErrorLogHandler().appendToFile(("FAILED QUERY2==" + insert2)); StressTool.getErrorLogHandler().appendToFile(sqle.toString()); } else { sqle.printStackTrace(); 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"); rs.close(); stmt.close(); rs = null; stmt = null; // 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); } conn.close(); conn = null; } 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) { if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(ex.toString() + "\n"); } else ex.printStackTrace(); try { conn.close(); conn = null; } catch (SQLException e) { // TODO Auto-generated catch block if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(e.toString() + "\n"); conn = null; } else e.printStackTrace(); } } } }
From source file:com.mysql.stresstool.RunnableClusterQueryInsert.java
@Override public boolean createSchema(StressTool sTool) { // Custom schema creation this is the default for the stresstool but can be anything String DropTables1 = "Drop table IF EXISTS tbtest"; String DropTables2 = "Drop table IF EXISTS tbtest_child"; String TruncateTables1 = "Truncate table tbtest"; String TruncateTables2 = "Truncate table tbtest_child"; Connection conn = null;/* w w w. j av a 2 s .co m*/ Statement stmt = 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")); conn.setAutoCommit(false); stmt = conn.createStatement(); StringBuffer sb = new StringBuffer(); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { sb.append("CREATE TABLE IF NOT EXISTS tbtest" + iTable + "("); if (this.isUseAutoIncrement()) { sb.append("`autoInc` bigint(11) AUTO_INCREMENT NOT NULL,"); } sb.append(" `a` int(11) NOT NULL,"); sb.append(" `uuid` char(36) NOT NULL,"); sb.append(" `b` varchar(100) NOT NULL,"); sb.append(" `c` char(200) NOT NULL,"); sb.append(" `counter` bigint(20) NULL, "); sb.append(" `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"); sb.append(" `partitionid` int NOT NULL DEFAULT 0,"); sb.append(" `strrecordtype` char(3) NULL"); if (this.isUseAutoIncrement()) { sb.append(", PRIMARY KEY (`autoInc`), INDEX `IDX_a` (a), INDEX `IDX_uuid` (uuid) "); } else { if (!this.doSimplePk) sb.append(", PRIMARY KEY (`uuid`), INDEX `IDX_a` (a) "); else sb.append(", PRIMARY KEY (`a`), INDEX `IDX_uuid` (uuid) "); } sb.append(") ENGINE=" + sTool.tableEngine); if (!sb.toString().equals("")) stmt.addBatch(sb.toString()); sb.delete(0, sb.length()); } String tbts1 = sb.toString(); sb = new StringBuffer(); for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { sb.append("CREATE TABLE IF NOT EXISTS tbtest_child" + iTable); sb.append("(`a` int(11) NOT NULL,"); sb.append("`bb` int(11) AUTO_INCREMENT NOT NULL,"); sb.append(" `partitionid` int NOT NULL DEFAULT 0,"); if (operationShort) sb.append(" `stroperation` VARCHAR(254) NULL,"); else sb.append(" `stroperation` TEXT(41845) NULL,"); sb.append(" `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"); sb.append(", PRIMARY KEY (`a`,`bb`), UNIQUE(`bb`)"); sb.append(") ENGINE=" + sTool.tableEngine); if (!sb.toString().equals("")) stmt.addBatch(sb.toString()); sb.delete(0, sb.length()); } String tbts2 = sb.toString(); System.out.println(tbts1); if (!doSimplePk) System.out.println(tbts2); if (sTool.droptable) { System.out.println( "****============================================================================*******"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { System.out.println( "**** Please wait DROP table tbtest" + iTable + " it could take a LOT of time *******"); stmt.execute(DropTables1 + iTable); } for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { System.out.println("**** Please wait DROP table tbtest_child" + iTable + " it could take a LOT of time *******"); stmt.execute(DropTables2 + iTable); } stmt.execute("COMMIT"); System.out.println("**** DROP finished *******"); System.out.println( "****============================================================================*******"); } if (sTool.createtable) stmt.executeBatch(); if (sTool.truncate) { System.out.println( "****============================================================================*******"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { System.out.println("**** Please wait TRUNCATE table tbtest" + iTable + " it could take a LOT of time *******"); stmt.execute(TruncateTables1 + iTable); } if (!doSimplePk) { for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { System.out.println("**** Please wait TRUNCATE table tbtest_child" + iTable + " it could take a LOT of time *******"); stmt.execute(TruncateTables2 + iTable); } } System.out.println("**** TRUNCATE finish *******"); System.out.println( "****============================================================================*******"); } } catch (Exception ex) { ex.printStackTrace( ); return false; } finally { try { conn.close(); return true; } catch (SQLException ex1) { ex1.printStackTrace(); return false; } } }
From source file:com.mysql.stresstool.RunnableQueryInsertDR.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; populateLocalInfo(conn); 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; } } if (debug) { System.out.println("Thread " + thInfo.getId() + " Executing loop " + thInfo.getExecutedLoops() + " QUERY1==" + insert1); System.out.println("Thread " + thInfo.getId() + " Executing loop " + thInfo.getExecutedLoops() + " QUERY2==" + insert2); } 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
@Override public boolean createSchema(StressTool sTool) { // Custom schema creation this is the default for the stresstool but can be anything String DropTables1 = "Drop table IF EXISTS tbtest"; String DropTables2 = "Drop table IF EXISTS tbtest_child"; String TruncateTables1 = "Truncate table tbtest"; String TruncateTables2 = "Truncate table tbtest_child"; Connection conn = null;//from w w w . java 2 s . c om Statement stmt = 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")); conn.setAutoCommit(false); stmt = conn.createStatement(); StringBuffer sb = new StringBuffer(); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { sb.append("CREATE TABLE IF NOT EXISTS tbtest" + iTable + "("); if (this.isUseAutoIncrement()) { sb.append("`autoInc` bigint(11) AUTO_INCREMENT NOT NULL,"); } sb.append(" `a` int(11) NOT NULL,"); sb.append(" `uuid` char(36) NOT NULL,"); sb.append(" `b` varchar(100) NOT NULL,"); sb.append(" `c` char(200) NOT NULL,"); sb.append(" `counter` bigint(20) NULL, "); sb.append(" `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"); sb.append(" `partitionid` int NOT NULL DEFAULT 0,"); sb.append(" `strrecordtype` char(3) NULL"); if (this.isUseAutoIncrement()) { sb.append(", PRIMARY KEY (`autoInc`), INDEX `IDX_a` (a), INDEX `IDX_uuid` (uuid) "); } else { if (!this.doSimplePk) sb.append(", PRIMARY KEY (`uuid`), INDEX `IDX_a` (a) "); else sb.append(", PRIMARY KEY (`a`), INDEX `IDX_uuid` (uuid) "); } sb.append(") ENGINE=" + sTool.tableEngine); if (!sb.toString().equals("")) stmt.addBatch(sb.toString()); sb.delete(0, sb.length()); } String tbts1 = sb.toString(); sb = new StringBuffer(); for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { sb.append("CREATE TABLE IF NOT EXISTS tbtest_child" + iTable); sb.append("(`a` int(11) NOT NULL,"); sb.append("`bb` int(11) AUTO_INCREMENT NOT NULL,"); sb.append(" `partitionid` int NOT NULL DEFAULT 0,"); if (operationShort) sb.append(" `stroperation` VARCHAR(254) NULL,"); else sb.append(" `stroperation` TEXT(41845) NULL,"); sb.append(" `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"); sb.append(", PRIMARY KEY (`a`,`bb`), UNIQUE(`bb`)"); sb.append(") ENGINE=" + sTool.tableEngine); if (!sb.toString().equals("")) stmt.addBatch(sb.toString()); sb.delete(0, sb.length()); } String tbts2 = sb.toString(); System.out.println(tbts1); if (!doSimplePk) System.out.println(tbts2); if (sTool.droptable) { System.out.println( "****============================================================================*******"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { System.out.println( "**** Please wait DROP table tbtest" + iTable + " it could take a LOT of time *******"); stmt.execute(DropTables1 + iTable); } if (!doSimplePk) { for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { System.out.println("**** Please wait DROP table tbtest_child" + iTable + " it could take a LOT of time *******"); stmt.execute(DropTables2 + iTable); } } stmt.execute("COMMIT"); System.out.println("**** DROP finished *******"); System.out.println( "****============================================================================*******"); } if (sTool.createtable) stmt.executeBatch(); if (sTool.truncate) { System.out.println( "****============================================================================*******"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { System.out.println("**** Please wait TRUNCATE table tbtest" + iTable + " it could take a LOT of time *******"); stmt.execute(TruncateTables1 + iTable); } if (!doSimplePk) { for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { System.out.println("**** Please wait TRUNCATE table tbtest_child" + iTable + " it could take a LOT of time *******"); stmt.execute(TruncateTables2 + iTable); } } System.out.println("**** TRUNCATE finish *******"); System.out.println( "****============================================================================*******"); } } catch (Exception ex) { ex.printStackTrace( ); return false; } finally { try { conn.close(); return true; } catch (SQLException ex1) { ex1.printStackTrace(); return false; } } }