List of usage examples for java.sql Statement addBatch
void addBatch(String sql) throws SQLException;
Statement object. From source file:org.lockss.db.SqlDbManager.java
/** * Executes a batch of statements to be used during initialization. * /* w w w . j av a 2s .co m*/ * @param conn * A connection with the database connection to be used. * @param stmts * A String[] with the statements to be executed. * @throws BatchUpdateException * if a batch update exception occurred. * @throws SQLException * if any other problem occurred accessing the database. */ private void executeBatchBeforeReady(Connection conn, String[] stmts) throws BatchUpdateException, SQLException { if (conn == null) { throw new NullPointerException("Null connection."); } Statement statement = null; try { statement = conn.createStatement(); for (String stmt : stmts) { statement.addBatch(stmt); } statement.executeBatch(); } finally { safeCloseStatement(statement); } }
From source file:com.edgenius.wiki.installation.DBLoader.java
/** * @throws SQLException /*from www . j ava 2s .c o m*/ */ public void createUser(ConnectionProxy con, String type, String fullname, String username, String password, String email) throws SQLException { PreparedStatement stat = null; PreparedStatement stat2 = null; PreparedStatement stat3 = null; Statement stat1 = null; ResultSet rs = null; try { if (Global.EncryptPassword) { String algorithm = Global.PasswordEncodingAlgorithm; if (algorithm == null) { algorithm = "MD5"; } password = CodecUtil.encodePassword(password, algorithm); } String sql = prototype.getProperty(type + ".user.create"); sql = sql.replaceAll(TOKEN_TABLE_PREFIX, Constants.TABLE_PREFIX); stat = con.prepareStatement(sql); int idx = 1; stat.setString(idx++, fullname); stat.setString(idx++, username); stat.setString(idx++, password); stat.setString(idx++, email); // if( Server.DBTYPE_POSTGRESQL.equalsIgnoreCase(type)){ //TODO: oracle need get from key table and update key table //stat.setInt(idx++,userKey); // } if (Server.DBTYPE_MYSQL.equalsIgnoreCase(type)) { try { //mysql 5 and 4 has different value for bit/tinyint, so try 5 first, if fail try mysql 4 again stat.executeUpdate(); } catch (Exception e) { String sql4 = prototype.getProperty(type + "4.user.create"); sql4 = sql4.replaceAll(TOKEN_TABLE_PREFIX, Constants.TABLE_PREFIX); stat2 = con.prepareStatement(sql4); idx = 1; stat2.setString(idx++, fullname); stat2.setString(idx++, username); stat2.setString(idx++, password); stat2.setString(idx++, email); stat2.executeUpdate(); } } else { stat.executeUpdate(); } int id = -1; stat3 = con.prepareStatement("select puid from " + Constants.TABLE_PREFIX + "USERS where user_name=?"); stat3.setString(1, username); rs = stat3.executeQuery(); if (rs == null || rs.next()) { id = rs.getInt(1); } if (id >= 0) { //give user all roles stat1 = con.createStatement(); String role1 = "INSERT INTO " + Constants.TABLE_PREFIX + "USER_ROLE (user_puid,role_puid) values(" + id + ",1)"; String role2 = "INSERT INTO " + Constants.TABLE_PREFIX + "USER_ROLE (user_puid,role_puid) values(" + id + ",2)"; String role3 = "INSERT INTO " + Constants.TABLE_PREFIX + "USER_ROLE (user_puid,role_puid) values(" + id + ",3)"; stat1.addBatch(role1); stat1.addBatch(role2); stat1.addBatch(role3); stat1.executeBatch(); } else { log.error("unable initialize role for user " + username + ". roleback"); stat.execute("delete from " + Constants.TABLE_PREFIX + "USER where user_name=" + username); throw new SQLException("unable initialize role for user " + username + ". roleback"); } } finally { try { if (rs != null) rs.close(); if (stat != null) stat.close(); if (stat1 != null) stat1.close(); if (stat2 != null) stat2.close(); if (stat3 != null) stat3.close(); } catch (Exception e) { } } }
From source file:com.mysql.stresstool.RunnableQueryInsertPartRange.java
public void run() { BufferedReader d = null;//w ww .j a v a2 s.com Connection conn = null; try { if (stikyconnection && jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else if (stikyconnection) conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); } catch (SQLException ex) { ex.printStackTrace(); } 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++) { if (!stikyconnection) { 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) { for (int icon = 0; icon <= 3; icon++) { try { Thread.sleep(10000); SoftReference sf = new SoftReference( DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"))); conn = (Connection) sf.get(); } catch (SQLException ex2) { ex2.printStackTrace(); } } //ex.printStackTrace(); } } if (conn != null) { Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); 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 = executeSQL(stmt); // 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 (SQLException 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(); if (conn != null && !conn.isClosed()) { try { stmt.addBatch("COMMIT"); executeSQL(stmt); } catch (SQLException sqle) { System.out.println( "#####################\n[Warning] Cannot explicitly commit given error\n#################"); conn.close(); continue; } } else { System.out.println( "#####################\n[Warning] Cannot explicitly commit given connection was interrupted unexpectedly\n#################"); } // 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.flexive.core.storage.genericSQL.GenericTreeStorageSpreaded.java
/** * {@inheritDoc}// ww w .ja va 2 s . c om */ @Override public void activateSubtree(Connection con, SequencerEngine seq, ContentEngine ce, FxTreeMode mode, long nodeId, boolean activateContents) throws FxApplicationException { if (nodeId == ROOT_NODE) { activateAll(con, mode); return; } final FxTreeNodeInfo sourceNode = getTreeNodeInfo(con, mode, nodeId); final long destination = sourceNode.getParentId(); // Make sure the path up to the root node is activated activateNode(con, seq, ce, mode, sourceNode.getParentId(), activateContents); try { // lock edit tree acquireLocksForUpdate(con, mode, selectAllChildNodeIds(con, mode, sourceNode.getLeft(), sourceNode.getRight(), true)); // lock live tree acquireLocksForUpdate(con, FxTreeMode.Live, selectAllChildNodeIds(con, mode, sourceNode.getLeft(), sourceNode.getRight(), true)); } catch (SQLException e) { throw new FxDbException(e); } //*************************************************************** //* Cleanup all affected nodes //*************************************************************** // First we clear all affected nodes in the live tree, since we will copy them from the edit tree. // We also need to delete all nodes that are children of the specified node in the edit tree, since they // were moved into our new subtree. // get node in live tree (with current bounds) FxTreeNodeInfo oldDestNode = null; try { oldDestNode = getTreeNodeInfo(con, FxTreeMode.Live, nodeId); } catch (FxNotFoundException e) { if (LOG.isDebugEnabled()) { LOG.debug("Activated node " + nodeId + " not yet present in Live tree."); } } Statement stmt = null; if (oldDestNode != null) { try { String sql = "SELECT ID FROM " + getTable(FxTreeMode.Live) + " WHERE (LFT>=" + oldDestNode.getLeft() + " AND RGT<=" + oldDestNode.getRight() + ") OR ID=" + nodeId; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { try { removeNode(con, FxTreeMode.Live, ce, rs.getLong(1), true /* always forced true in live mode */); } catch (FxNotFoundException e) { // removed by previous call } } stmt.close(); } catch (SQLException exc) { throw new FxTreeException("ex.tree.activate.failed", nodeId, true, exc.getMessage()); } finally { try { if (stmt != null) stmt.close(); } catch (Exception exc) { /*ignore*/} } } //*************************************************************** //* Now we can copy all affected nodes to the live tree //*************************************************************** int position = 0; // Make space for the new nodes BigInteger spacing = makeSpace(con, seq, FxTreeMode.Live, destination, /*sourceNode.getPosition()*/position, sourceNode.getTotalChildCount() + 1); // Reload the node to obtain the new boundary and spacing informations FxTreeNodeInfoSpreaded destinationNode = (FxTreeNodeInfoSpreaded) getTreeNodeInfo(con, FxTreeMode.Live, destination); // Copy the data BigInteger boundaries[] = getBoundaries(con, destinationNode, position); int depthDelta = (destinationNode.getDepth() + 1) - sourceNode.getDepth(); reorganizeSpace(con, seq, mode, FxTreeMode.Live, sourceNode.getId(), true, spacing, boundaries[0], null, 0, null, null, depthDelta, destination, true, true, true); try { // Update the childcount of the new parents stmt = con.createStatement(); stmt.addBatch( "UPDATE " + getTable(FxTreeMode.Live) + " SET CHILDCOUNT=CHILDCOUNT+1 WHERE ID=" + destination); stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=" + StorageManager.getBooleanFalseExpression() + " WHERE LFT>=" + sourceNode.getLeft() + " AND RGT<=" + sourceNode.getRight()); stmt.executeBatch(); } catch (SQLException exc) { throw new FxTreeException("ex.tree.activate.failed", nodeId, true, exc.getMessage()); } finally { try { if (stmt != null) stmt.close(); } catch (Exception exc) { /*ignore*/} } //clear nodes that can not be activated since their content has no live step boolean orgNodeRemoved = false; PreparedStatement psRemove = null; PreparedStatement psFixChildCount = null; PreparedStatement psFlagDirty = null; PreparedStatement psEditBoundaries = null; try { // Update the childcount of the new parents stmt = con.createStatement(); // 1 2 3 4 5 ResultSet rs = stmt.executeQuery( "SELECT DISTINCT l.ID, l.PARENT, l.LFT, l.RGT, c.ID FROM " + getTable(FxTreeMode.Live) + " l, " + TBL_CONTENT + " c WHERE l.LFT>" + destinationNode.getLeft() + " AND l.RGT<" + destinationNode.getRight() + " AND l.ref=c.id and c.live_ver=0 ORDER BY l.LFT DESC"); while (rs != null && rs.next()) { long rmNodeId = rs.getLong(1); if (activateContents) { FxPK reference = new FxPK(rs.getLong(5)); FxContent co = ce.load(reference); //create a Live version reference = createContentLiveVersion(ce, co); LOG.info("Created new live version " + reference + " during activation of node " + rmNodeId); } else { System.out.println("removing node #" + rmNodeId + " and children"); if (rmNodeId == nodeId) orgNodeRemoved = true; if (psRemove == null) { psRemove = con.prepareStatement( "DELETE FROM " + getTable(FxTreeMode.Live) + " WHERE LFT>=? AND RGT<=?"); } setNodeBounds(psRemove, 1, getNodeBounds(rs, 3)); setNodeBounds(psRemove, 2, getNodeBounds(rs, 4)); psRemove.execute(); if (psFixChildCount == null) { psFixChildCount = con.prepareStatement( "UPDATE " + getTable(FxTreeMode.Live) + " SET CHILDCOUNT=CHILDCOUNT-1 WHERE ID=?"); } psFixChildCount.setLong(1, rs.getLong(2)); psFixChildCount.executeUpdate(); if (psEditBoundaries == null) { psEditBoundaries = con.prepareStatement( "SELECT LFT,RGT FROM " + getTable(FxTreeMode.Edit) + " WHERE ID=?"); } psEditBoundaries.setLong(1, rmNodeId); ResultSet rsBoundaries = psEditBoundaries.executeQuery(); if (rsBoundaries != null && rsBoundaries.next()) { if (psFlagDirty == null) { psFlagDirty = con.prepareStatement("UPDATE " + getTable(FxTreeMode.Edit) + " SET DIRTY=" + StorageManager.getBooleanTrueExpression() + " WHERE LFT>=? AND RGT<=?"); } setNodeBounds(psFlagDirty, 1, getNodeBounds(rsBoundaries, 1)); setNodeBounds(psFlagDirty, 2, getNodeBounds(rsBoundaries, 2)); psFlagDirty.executeUpdate(); } } } } catch (SQLException exc) { throw new FxTreeException("ex.tree.activate.failed", nodeId, true, exc.getMessage()); } finally { Database.closeObjects(GenericTreeStorageSpreaded.class, stmt, psRemove, psFixChildCount, psFlagDirty); } // Make sure the node is at the correct position if (!orgNodeRemoved) move(con, seq, FxTreeMode.Live, sourceNode.getId(), sourceNode.getParentId(), sourceNode.getPosition()); }
From source file:org.rhq.enterprise.installer.ConfigurationBean.java
public StartPageResults createDatabase() { Properties config = getConfigurationAsProperties(configuration); String dbType = config.getProperty(ServerProperties.PROP_DATABASE_TYPE, "-unknown-"); Properties adminConfig = new Properties(); adminConfig.put(ServerProperties.PROP_DATABASE_CONNECTION_URL, adminConnectionUrl); adminConfig.put(ServerProperties.PROP_DATABASE_USERNAME, adminUsername); adminConfig.put(ServerProperties.PROP_DATABASE_PASSWORD, adminPassword); Connection conn = null;/*from w ww . j a v a 2s . c o m*/ Statement stmt = null; // If we successfully add the user/database, we'll change the values in the UI // by modifying the configuration property items that this bean manages. PropertyItemWithValue propertyItemUsername = null; PropertyItemWithValue propertyItemPassword = null; PropertyItemWithValue propertyItemUrl = null; for (PropertyItemWithValue item : configuration) { String propName = item.getItemDefinition().getPropertyName(); if (propName.equals(ServerProperties.PROP_DATABASE_USERNAME)) { propertyItemUsername = item; } else if (propName.equals(ServerProperties.PROP_DATABASE_PASSWORD)) { propertyItemPassword = item; } else if (propName.equals(ServerProperties.PROP_DATABASE_CONNECTION_URL)) { propertyItemUrl = item; } } if (propertyItemUsername == null || propertyItemPassword == null || propertyItemUrl == null) { throw new NullPointerException("Missing a property item - this is a bug please report it"); } LOG.info("Will attempt to create user/database 'rhqadmin' using URL [" + getAdminConnectionUrl() + "] and admin user [" + getAdminUsername() + "]. Admin password was" + (getAdminPassword().length() > 0 ? " not " : " ") + "empty"); try { String sql1, sql2; conn = serverInfo.getDatabaseConnection(adminConfig); conn.setAutoCommit(true); stmt = conn.createStatement(); if (dbType.equalsIgnoreCase("postgresql")) { sql1 = "CREATE ROLE rhqadmin LOGIN ENCRYPTED PASSWORD 'rhqadmin' NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE"; sql2 = "CREATE DATABASE rhq WITH OWNER = rhqadmin ENCODING = 'SQL_ASCII' TABLESPACE = pg_default"; } else if (dbType.equalsIgnoreCase("oracle10g")) { sql1 = "CREATE USER rhqadmin IDENTIFIED BY rhqadmin"; sql2 = "GRANT connect, resource TO rhqadmin"; } else if (dbType.equalsIgnoreCase("h2")) { // I have no idea if these are correct for H2 - I just copied oracle's sql sql1 = "CREATE USER rhqadmin IDENTIFIED BY rhqadmin"; sql2 = "GRANT connect, resource TO rhqadmin"; } else if (dbType.equalsIgnoreCase("sqlserver")) { // I have no idea if these are correct for sql server - I just copied oracle's sql sql1 = "CREATE USER rhqadmin IDENTIFIED BY rhqadmin"; sql2 = "GRANT connect, resource TO rhqadmin"; } else { throw new Exception("Unknown database type: " + dbType); } stmt.addBatch(sql1); stmt.addBatch(sql2); int[] results = stmt.executeBatch(); if (results[0] == Statement.EXECUTE_FAILED) throw new Exception("Failed to execute: " + sql1); if (results[1] == Statement.EXECUTE_FAILED) throw new Exception("Failed to execute: " + sql2); // success! let's set our properties to the values we just created propertyItemUsername.setValue("rhqadmin"); propertyItemPassword.setValue("rhqadmin"); if (dbType.equalsIgnoreCase("postgresql") || dbType.equalsIgnoreCase("mysql")) { if (!propertyItemUrl.getValue().endsWith("/rhq")) { propertyItemUrl.setValue(propertyItemUrl.getValue() + "/rhq"); } } testConnection(); lastCreate = "OK"; } catch (Exception e) { LOG.warn("Installer failed to create database", e); lastCreate = ThrowableUtil.getAllMessages(e); } finally { adminConnectionUrl = null; adminUsername = null; adminPassword = null; if (stmt != null) try { stmt.close(); } catch (Exception e) { } if (conn != null) try { conn.close(); } catch (Exception e) { } } return StartPageResults.STAY; }
From source file:com.mysql.stresstool.RunnableClusterQueryInsert.java
public void run() { BufferedReader d = null;//from w w w .j a v a 2s . 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 ww w . jav a2 s .com 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.RunnableSC2013QueryInsert.java
public void run() { BufferedReader d = null;/*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 < 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.RunnableQueryInsertPartRange.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 www .ja v a2 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(" `date` DATE NOT NULL,"); sb.append(" `strrecordtype` char(3) NULL"); if (this.isUseAutoIncrement()) { if (this.partitionType.equals("range")) { sb.append( ", PRIMARY KEY (`autoInc`,`date`), INDEX `IDX_a` (a), INDEX `IDX_uuid` (uuid) "); } else { sb.append( ", PRIMARY KEY (`autoInc`,`partitionid`), INDEX `IDX_a` (a), INDEX `IDX_uuid` (uuid) "); } } else { if (!this.doSimplePk) if (this.partitionType.equals("range")) { sb.append(", PRIMARY KEY (`uuid`,`date`), INDEX `IDX_a` (a) "); } else { sb.append(", PRIMARY KEY (`uuid`,`partitionid`), INDEX `IDX_a` (a) "); } else { if (this.partitionType.equals("range")) { sb.append(", PRIMARY KEY (`a`,`date`), INDEX `IDX_uuid` (uuid) "); } else { sb.append(", PRIMARY KEY (`a`,`partitionid`), 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(" `date` DATE 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; } } }
From source file:com.mysql.stresstool.RunnableQueryInsert.java
public void run() { BufferedReader d = null;//from w w w . jav a2 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(); } } } }