List of usage examples for java.sql Statement execute
boolean execute(String sql) throws SQLException;
From source file:com.mysql.stresstool.RunnableSC2013QueryInsert.java
public void run() { BufferedReader d = null;//from www . ja v a 2 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 < 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:UploadImage.java
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // change the following parameters to connect to the oracle database String username = "patzelt"; String password = "Chocolate1"; String drivername = "oracle.jdbc.driver.OracleDriver"; String dbstring = "jdbc:oracle:thin:@gwynne.cs.ualberta.ca:1521:CRS"; int photo_id; try {//from ww w.j a v a 2 s . c o m // Parse the HTTP request to get the image stream DiskFileUpload fu = new DiskFileUpload(); List FileItems = fu.parseRequest(request); // Process the uploaded items, assuming only 1 image file uploaded Iterator i = FileItems.iterator(); FileItem item = (FileItem) i.next(); while (i.hasNext() && item.isFormField()) { item = (FileItem) i.next(); } long size = item.getSize(); // Get the image stream InputStream instream = item.getInputStream(); // Connect to the database and create a statement Connection conn = getConnected(drivername, dbstring, username, password); Statement stmt = conn.createStatement(); /* * First, to generate a unique pic_id using an SQL sequence */ ResultSet rset1 = stmt.executeQuery("SELECT pic_id_sequence.nextval from dual"); // good rset1.next(); photo_id = rset1.getInt(1); /** // Insert an empty blob into the table first. Note that you have to // use the Oracle specific function empty_blob() to create an empty // blob stmt.execute("INSERT INTO pictures (photo_id, pic_des, pic) VALUES(" + photo_id + ",'test',empty_blob())"); // to retrieve the lob_locator // Note that you must use "FOR UPDATE" in the select statement String cmd = "SELECT * FROM pictures WHERE photo_id = " + photo_id + " FOR UPDATE"; ResultSet rset = stmt.executeQuery(cmd); rset.next(); BLOB myblob = ((OracleResultSet) rset).getBLOB(3); **/ stmt.execute("INSERT INTO pictures VALUES(" + photo_id + ",'test',empty_blob())"); PreparedStatement stmt1 = conn .prepareStatement("UPDATE pictures SET pic = ? WHERE photo_id = + " + photo_id); stmt1.setBinaryStream(1, instream); stmt1.executeUpdate(); /** // Write the image to the blob object OutputStream outstream = myblob.setBinaryStream(size); //int bufferSize = myblob.getBufferSize(); //byte[] buffer = new byte[bufferSize]; //int length = -1; //while ((length = instream.read(buffer)) != -1) //outstream.write(buffer, 0, length); outstream.write(pic); instream.close(); outstream.close(); //String update = "UPDATE pictures SET pic = " + myblob + " WHERE photo_id = " + photo_id; //stmt.execute(update); **/ response_message = "YAHHOOOOOO"; conn.close(); } catch (Exception ex) { // System.out.println( ex.getMessage()); response_message = ex.getMessage(); } // Output response to the client response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " + "Transitional//EN\">\n" + "<HTML>\n" + "<HEAD><TITLE>Upload Message</TITLE></HEAD>\n" + "<BODY>\n" + "<H1>" + response_message + "</H1>\n" + "</BODY></HTML>"); }
From source file:backtype.storm.scheduler.adaptive.DataManager.java
public void removeTopologies(List<String> topologies) throws Exception { Connection connection = null; Statement statement = null; logger.debug("Going to remove these topologies: " + Utils.collectionToString(topologies)); try {/*w w w .j a v a2s.co m*/ connection = getConnection(); connection.setAutoCommit(false); statement = connection.createStatement(); for (String topology : topologies) { logger.debug("Removing load stats of topology " + topology); String sql = "delete from `load` where topology_id in (select id from topology where storm_id = '" + topology + "')"; logger.debug("SQL script: " + sql); statement.execute(sql); logger.debug("Removing traffic stats of topology " + topology); sql = "delete from traffic where topology_id in (select id from topology where storm_id = '" + topology + "')"; logger.debug("SQL script: " + sql); statement.execute(sql); logger.debug("Removing topology " + topology); sql = "delete from topology where storm_id = '" + topology + "'"; logger.debug("SQL script: " + sql); statement.execute(sql); } connection.commit(); } catch (Exception e) { logger.error("An error occurred removing topologies", e); connection.rollback(); throw e; } finally { if (statement != null) statement.close(); if (connection != null) { connection.setAutoCommit(true); connection.close(); } } }
From source file:com.flexive.core.storage.GenericDBStorage.java
/** * {@inheritDoc}/*from w w w . j av a 2s . co m*/ */ @Override public void importDivision(Connection _con, ZipFile zip) throws Exception { long startTime = System.currentTimeMillis(); GenericDivisionImporter importer = getDivisionImporter(); FxDivisionExportInfo exportInfo = importer.getDivisionExportInfo(zip); if (FxSharedUtils.getDBVersion() != exportInfo.getSchemaVersion()) { LOG.warn("DB Version mismatch! Current:" + FxSharedUtils.getDBVersion() + ", exported schema:" + exportInfo.getSchemaVersion()); } boolean isNonTX = importer.importRequiresNonTXConnection(); Connection con = isNonTX ? Database.getNonTXDataSource().getConnection() : _con; boolean autoCommit = false; if (isNonTX) { autoCommit = con.getAutoCommit(); con.setAutoCommit(false); con.commit(); //ensure a "clean" connection } Exception inner = null; try { importer.wipeDivisionData(con); if (isNonTX) con.commit(); Statement stmt = con.createStatement(); if (isNonTX) con.commit(); try { importer.importLanguages(con, zip); if (isNonTX) con.commit(); importer.importMandators(con, zip); if (isNonTX) con.commit(); importer.importSecurity(con, zip); if (isNonTX) con.commit(); importer.importWorkflows(con, zip); if (isNonTX) con.commit(); importer.importConfigurations(con, zip); if (isNonTX) con.commit(); importer.importBinaries(con, zip); if (isNonTX) con.commit(); stmt.execute(getReferentialIntegrityChecksStatement(false)); importer.importStructures(con, zip); if (isNonTX) con.commit(); importer.importHierarchicalContents(con, zip); if (isNonTX) con.commit(); importer.importScripts(con, zip); if (isNonTX) con.commit(); importer.importTree(con, zip); if (isNonTX) con.commit(); importer.importHistory(con, zip); if (isNonTX) con.commit(); importer.importResources(con, zip); if (isNonTX) con.commit(); importer.importBriefcases(con, zip); if (isNonTX) con.commit(); importer.importFlatStorages(con, zip, exportInfo); if (isNonTX) con.commit(); importer.importSequencers(con, zip); if (isNonTX) con.commit(); } catch (Exception e) { if (isNonTX) con.rollback(); inner = e; throw e; } finally { if (isNonTX) con.commit(); stmt.execute(getReferentialIntegrityChecksStatement(true)); } if (isNonTX) con.commit(); //rebuild fulltext index FulltextIndexer ft = StorageManager.getStorageImpl().getContentStorage(TypeStorageMode.Hierarchical) .getFulltextIndexer(null, con); ft.rebuildIndex(); if (isNonTX) con.commit(); } catch (Exception e) { if (isNonTX) con.rollback(); if (inner != null) { LOG.error(e); throw inner; } throw e; } finally { if (isNonTX) { con.commit(); con.setAutoCommit(autoCommit); Database.closeObjects(GenericDBStorage.class, con, null); } LOG.info(" Importing took " + FxFormatUtils.formatTimeSpan((System.currentTimeMillis() - startTime))); } }
From source file:com.ca.dvs.utilities.lisamar.JDBCUtil.java
/** * Execute a SQL statement //from w w w .j a v a 2s . c o m * * @param conn - connection to database * @param sql - sql command * * @return boolean - true the command executed successful, false if the command failed */ private boolean execSQLStatement(Connection conn, String sql) { boolean bOK = false; String message = ""; try { Statement stmt = conn.createStatement(); stmt.execute(sql); //message = "Completed:" + sql; //Log.write().info(message); bOK = true; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); message = e.getMessage(); Log.write().error(message); if (sql.startsWith("INSERT INTO")) { // If failed to insert seeddata, report the error then continue the process Map<String, Object> err = new LinkedHashMap<String, Object>(); err.put("insertTo", message); insertDataErrors.add(err); bOK = true; } else { LisaMarUtil.mapErrors.put("execSQLStatement", message); } } return bOK; }
From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java
/** * Execute query with no result./*from www . j a v a 2 s . c o m*/ * * @param query the query * @param giveException throws any exception generated if true, * if false all exceptions are consumed * @throws Exception if giveException is true and their is an error executing * the query */ public void executeQueryNoResult(String query, boolean giveException) throws Exception { Connection con = null; Statement stmt = null; SQLException exc = null; try { con = this.getConnection(); con.setAutoCommit(false); stmt = con.createStatement(); stmt.execute(query); con.commit(); } catch (SQLException e) { if (!giveException) { if (log.isErrorEnabled()) { log.error(query, e); } } if (con != null) { try { con.rollback(); } catch (SQLException e1) { if (log.isErrorEnabled()) { log.error("Error during rollback.", e1); } } } if (giveException) { exc = e; } } finally { try { if (con != null && !con.isClosed()) { con.setAutoCommit(true); con.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error during close.", e); } } if (exc != null && giveException) { throw exc; } } }
From source file:com.mysql.stresstool.RunnableQueryInsert.java
public void run() { BufferedReader d = null;//from w w w . j ava2s . 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:backtype.storm.scheduler.adaptive.DataManager.java
public void checkNode(long totalSpeed) throws Exception { Connection connection = null; Statement statement = null; try {// w w w .j a v a 2 s . co m connection = getConnection(); statement = connection.createStatement(); long absoluteCapacity = totalSpeed / 100 * capacity; String sql = "update node set capacity = " + absoluteCapacity + " where name = '" + nodeName + "'"; logger.debug("SQL script: " + sql); if (statement.executeUpdate(sql) == 0) { sql = "insert into node(name, capacity, cores) values('" + nodeName + "', " + totalSpeed + ", " + CPUInfo.getInstance().getNumberOfCores() + ")"; logger.debug("SQL script: " + sql); statement.execute(sql); } } catch (Exception e) { logger.error("An error occurred checking the node", e); throw e; } finally { if (statement != null) statement.close(); if (connection != null) connection.close(); } }
From source file:it.unibas.spicy.persistence.DAOMappingTaskLines.java
private void getOffSetFromDB(Element elementGetId, String sequence) throws DAOException, SQLException { Element driverElement = elementGetId.getChild("driver"); Element uriElement = elementGetId.getChild("uri"); Element schemaNameElement = elementGetId.getChild("schema"); Element loginElement = elementGetId.getChild("login"); Element passwordElement = elementGetId.getChild("password"); Element tableElement = elementGetId.getChild("table"); Element columnElement = elementGetId.getChild("column"); Element functionElement = elementGetId.getChild("function"); String schema = ""; if (schemaNameElement != null) { schema = schemaNameElement.getTextTrim(); }// w w w .ja va 2s . c o m GetIdFromDb newIdFromDb = new GetIdFromDb(driverElement.getTextTrim(), uriElement.getTextTrim(), schema, loginElement.getTextTrim(), passwordElement.getTextTrim(), tableElement.getTextTrim(), columnElement.getTextTrim(), functionElement.getTextTrim()); SpicyEngineConstants.GET_ID_FROM_DB.put(sequence, newIdFromDb); AccessConfiguration accessConfiguration = new AccessConfiguration(); accessConfiguration.setDriver(newIdFromDb.getDriver()); accessConfiguration.setUri(newIdFromDb.getUri()); if (schemaNameElement != null) { accessConfiguration.setSchemaName(newIdFromDb.getSchema()); } accessConfiguration.setLogin(newIdFromDb.getLogin()); accessConfiguration.setPassword(newIdFromDb.getPassword()); IConnectionFactory connectionFactory = new SimpleDbConnectionFactory(); Connection connection = connectionFactory.getConnection(accessConfiguration); Statement statement = connection.createStatement(); if (newIdFromDb.getFunction().equalsIgnoreCase("max")) { statement.execute( "SELECT MAX(\"" + newIdFromDb.getColumn() + "\") FROM \"" + newIdFromDb.getTable() + "\";"); ResultSet rs = statement.getResultSet(); if (rs.next()) { SpicyEngineConstants.OFFSET_MAPPING.put(sequence, String.valueOf(rs.getInt(1))); } else { SpicyEngineConstants.OFFSET_MAPPING.put(sequence, "0"); } } }
From source file:com.taobao.datax.plugins.writer.mysqlwriter.MysqlWriter.java
@Override public int startWrite(LineReceiver receiver) { com.mysql.jdbc.Statement stmt = null; try {//from w ww. jav a 2 s . c om this.connection = DBSource.getConnection(this.sourceUniqKey); stmt = (com.mysql.jdbc.Statement) ((org.apache.commons.dbcp.DelegatingConnection) this.connection) .getInnermostDelegate() .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); /* set max count */ this.logger.info(String.format("Config max_error_count: set max_error_count=%d", MAX_ERROR_COUNT)); stmt.executeUpdate(String.format("set max_error_count=%d;", MAX_ERROR_COUNT)); /* set connect encoding */ this.logger.info(String.format("Config encoding %s .", this.encoding)); for (String sql : this.makeLoadEncoding(encoding)) stmt.execute(sql); /* load data begin */ String loadSql = this.makeLoadSql(); this.logger.info(String.format("Load sql: %s.", visualSql(loadSql))); MysqlWriterInputStreamAdapter localInputStream = new MysqlWriterInputStreamAdapter(receiver, this); stmt.setLocalInfileInputStream(localInputStream); stmt.executeUpdate(visualSql(loadSql)); this.lineCounter = localInputStream.getLineNumber(); this.logger.info("DataX write to mysql ends ."); return PluginStatus.SUCCESS.value(); } catch (Exception e2) { if (null != this.connection) { try { this.connection.close(); } catch (SQLException e) { } } throw new DataExchangeException(e2.getCause()); } finally { if (null != stmt) try { stmt.close(); } catch (SQLException e3) { } } }