List of usage examples for java.sql Statement execute
boolean execute(String sql) throws SQLException;
From source file:org.h2gis.drivers.geojson.GeoJsonReaderDriver.java
/** * Parses the first GeoJSON feature to create the PreparedStatement. * * @throws SQLException//from w w w . j a v a 2s . c o m * @throws IOException */ private boolean parseMetadata() throws SQLException, IOException { FileInputStream fis = null; StringBuilder metadataBuilder = new StringBuilder(); try { fis = new FileInputStream(fileName); this.fc = fis.getChannel(); this.fileSize = fc.size(); // Given the file size and an average node file size. // Skip how many nodes in order to update progression at a step of 1% readFileSizeEachNode = Math.max(1, (this.fileSize / AVERAGE_NODE_SIZE) / 100); nodeCountProgress = 0; JsonParser jp = jsFactory.createParser(fis); metadataBuilder.append("CREATE TABLE "); metadataBuilder.append(tableLocation); metadataBuilder.append(" ("); jp.nextToken();//START_OBJECT jp.nextToken(); // field_name (type) jp.nextToken(); // value_string (FeatureCollection) String geomType = jp.getText(); if (geomType.equalsIgnoreCase(GeoJsonField.FEATURECOLLECTION)) { jp.nextToken(); // FIELD_NAME features String firstParam = jp.getText(); //Read the CRS if (firstParam.equalsIgnoreCase(GeoJsonField.CRS)) { parsedSRID = readCRS(jp); readFeatures(jp, geomType, metadataBuilder); } else if (firstParam.equalsIgnoreCase(GeoJsonField.FEATURES)) { readFeatures(jp, geomType, metadataBuilder); } else { throw new SQLException( "Malformed GeoJSON file. Expected 'features', found '" + firstParam + "'"); } } else { throw new SQLException( "Malformed GeoJSON file. Expected 'FeatureCollection', found '" + geomType + "'"); } jp.close(); } catch (FileNotFoundException ex) { throw new SQLException(ex); } finally { try { if (fis != null) { fis.close(); } } catch (IOException ex) { throw new IOException(ex); } } // Now we create the table if there is at leat one geometry field. if (hasGeometryField) { Statement stmt = connection.createStatement(); stmt.execute(metadataBuilder.toString()); stmt.close(); if (fieldIndex > 0) { StringBuilder insert = new StringBuilder("INSERT INTO ").append(tableLocation) .append(" VALUES ( ?"); for (int i = 1; i < fieldIndex; i++) { insert.append(",?"); } insert.append(");"); preparedStatement = connection.prepareStatement(insert.toString()); return true; } } else { throw new SQLException("The first feature must contains a geometry field."); } return false; }
From source file:com.amazon.carbonado.repo.jdbc.JDBCStorage.java
/** * @since 1.2/* ww w .java 2 s .c o m*/ */ public void truncate() throws PersistException { String truncateFormat = mSupportStrategy.getTruncateTableStatement(); try { if (truncateFormat == null || mTriggerManager.getDeleteTrigger() != null) { query().deleteAll(); return; } Connection con = getConnection(); try { java.sql.Statement st = con.createStatement(); try { st.execute(String.format(truncateFormat, mInfo.getQualifiedTableName())); } finally { st.close(); } } catch (SQLException e) { throw toPersistException(e); } finally { yieldConnection(con); } } catch (FetchException e) { throw e.toPersistException(); } }
From source file:fetchBookDetails.java
/** * Handles the HTTP <code>POST</code> method. * * @param request servlet request//from w w w . j a v a 2 s . co m * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //processRequest(request, response); try { System.out.println("Inside try 1"); Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance(); //setting up connection String dbURL = "jdbc:derby://localhost:1527/autolib_db_test"; Connection conn = DriverManager.getConnection(dbURL, "ishtiaq", "ishtiaq"); Statement stmt = null; ResultSet rslt = null; if (conn == null) { System.out.println("Connection Failed"); } stmt = conn.createStatement(); String bookId = request.getParameter("book"); // pass this book value from displayBookDetails function String buyBook = request.getParameter("buy"); // pass this book value from displayBookDetails function String sqlQry = ""; if (buyBook.equals("YES")) { //if a book is selected to be bought Integer bookQty = Integer.parseInt(request.getParameter("qty")); // pass this book value from displayBookDetails function sqlQry = "update books_tbl set qty = qty - " + bookQty.toString() + " where id = " + bookId + ""; //reducing quantity by bookQty which increments by 1 on every click stmt.execute(sqlQry); //executing the update query } System.out.println("SEARCH Book id " + bookId); //query to return book details by giving the ID value sqlQry = "SELECT * FROM books_tbl where id = " + bookId + ""; rslt = stmt.executeQuery(sqlQry); //creating JSON object, array object and a jason variable JSONObject jObj = new JSONObject(); JSONArray bookArr = new JSONArray(); JSONObject bookData; try { System.out.println("Inside Try"); //adding ID, name, author, price, topic, available and qty to bookData by using put while (rslt.next()) { bookData = new JSONObject(); bookData.put("id", rslt.getInt("id")); bookData.put("name", rslt.getString("bookname")); bookData.put("author", rslt.getString("author")); bookData.put("price", rslt.getInt("price")); bookData.put("topic", rslt.getString("topic")); bookData.put("available", rslt.getBoolean("available")); bookData.put("qty", rslt.getInt("qty")); bookArr.put(bookData); //putting all bookdata in book array } jObj.put("Books", bookArr); //adding book array content to string named "Books" } catch (JSONException jse) { } response.setContentType("application/json"); response.getWriter().write(jObj.toString()); //closing connection if (!stmt.isClosed()) stmt.close(); if (!rslt.isClosed()) rslt.close(); } catch (Exception e) { //return null; } }
From source file:com.mirth.connect.donkey.test.util.TestUtils.java
public static void runChannelTest(String testMessage, final String testName, final Integer testSize, Integer testMillis, Integer warmupMillis, Channel[] channels) throws Exception { TestSourceConnector[] sourceConnectors = new TestSourceConnector[channels.length]; List<List<Long>> sentMessageIds = new ArrayList<List<Long>>(); boolean isPostgres = getDatabaseType().equals("postgres"); for (int i = 0; i < channels.length; i++) { ChannelController.getInstance().deleteAllMessages(channels[i].getChannelId()); long localChannelId = ChannelController.getInstance().getLocalChannelId(channels[i].getChannelId()); if (isPostgres) { System.out.print("Vacuuming tables for channel: " + channels[i].getChannelId() + "..."); Connection connection = null; Statement statement = null; try { connection = getConnection(); connection.setAutoCommit(true); statement = connection.createStatement(); statement.execute("VACUUM ANALYZE d_m" + localChannelId); statement.execute("VACUUM ANALYZE d_mm" + localChannelId); statement.execute("VACUUM ANALYZE d_mc" + localChannelId); statement.execute("VACUUM ANALYZE d_mcm" + localChannelId); statement.execute("VACUUM ANALYZE d_ms" + localChannelId); statement.execute("VACUUM ANALYZE d_ma" + localChannelId); } finally { close(statement);/* w w w. j a v a 2 s .co m*/ if (connection != null && !connection.isClosed()) { connection.close(); } } System.out.println("done"); } sourceConnectors[i] = (TestSourceConnector) channels[i].getSourceConnector(); sentMessageIds.add(new ArrayList<Long>()); } for (Channel channel : channels) { channel.deploy(); channel.start(null); } List<Long> times = new ArrayList<Long>(); long testStartTime = System.currentTimeMillis(); long duration = 0; if (testMillis != null) { if (warmupMillis == null) { warmupMillis = 0; } testMillis += warmupMillis; long testBeginTime = testStartTime + warmupMillis; long testEndTime = testStartTime + testMillis; while (System.currentTimeMillis() < testEndTime) { for (int j = 0; j < channels.length; j++) { logger.debug("Sending message"); long msgStartTime = System.currentTimeMillis(); sentMessageIds.get(j).add(sourceConnectors[j].readTestMessage(testMessage).getMessageId()); long totalTime = System.currentTimeMillis() - msgStartTime; if (System.currentTimeMillis() > testBeginTime) { times.add(totalTime); } } } for (Channel channel : channels) { channel.stop(); } for (Channel channel : channels) { channel.undeploy(); } duration = System.currentTimeMillis() - testBeginTime; } else { for (int i = 0; i < testSize; i++) { for (int j = 0; j < channels.length; j++) { logger.debug("Sending message"); long msgStartTime = System.currentTimeMillis(); sentMessageIds.get(j).add(sourceConnectors[j].readTestMessage(testMessage).getMessageId()); long totalTime = System.currentTimeMillis() - msgStartTime; times.add(totalTime); } } for (Channel channel : channels) { channel.processSourceQueue(0); channel.stop(); } for (Channel channel : channels) { channel.undeploy(); } duration = System.currentTimeMillis() - testStartTime; } if (testName != null) { System.out.println(testName); } System.out.println(TestUtils.getPerformanceText(channels[0].getDestinationCount(), duration, times)); long size = 0; for (Channel channel : channels) { size += TestUtils.getChannelStorageSize(channel.getChannelId()); } System.out.println("Total Storage Used: " + Precision.round((((float) size) / 1048576f), 2) + " MB"); for (int i = 0; i < channels.length; i++) { List<Long> channelSentMessageIds = sentMessageIds.get(i); assertTrue(channelSentMessageIds.size() > 0); for (DestinationChainProvider chain : channels[i].getDestinationChainProviders()) { for (DestinationConnector destinationConnector : chain.getDestinationConnectors().values()) { List<Long> receivedMessageIds = ((TestDestinationConnector) destinationConnector) .getMessageIds(); // test that the number of messages processed by this destination connector // is equal to the number of messages sent to the channel assertEquals(channelSentMessageIds.size(), receivedMessageIds.size()); for (int j = 0; j < channelSentMessageIds.size(); j++) { assertTrue(channelSentMessageIds.get(j) > 0); // test that the messages were processed by the destination in the correct order assertEquals(channelSentMessageIds.get(j), receivedMessageIds.get(j)); } } } } }
From source file:com.l2jserver.service.database.sql.AbstractSQLDatabaseService.java
/** * Executes the SQL code in the databases * /*from w w w . ja va 2s .c om*/ * @param conn * the SQL connection * @param sql * the SQL query * @return (see {@link Statement#execute(String)}) * @throws SQLException * if any error occur while executing the sql query */ protected boolean executeSQL(Connection conn, String sql) throws SQLException { final Statement st = conn.createStatement(); try { return st.execute(sql); } finally { st.close(); } }
From source file:backtype.storm.scheduler.adaptive.DataManager.java
public void StoreAssignment(String topologies, String assignment) throws Exception { Connection connection = null; Statement statement = null; logger.debug(/*from w w w.j av a2 s . c o m*/ "Going to store an assignment (topologies: " + topologies + ", assignment: " + assignment + ")"); try { connection = getConnection(); statement = connection.createStatement(); String sql = "insert into assignment(time, topologies, assignment) values(" + System.currentTimeMillis() + ", '" + topologies + "', '" + assignment + "')"; logger.debug("SQL script: " + sql); statement.execute(sql); } catch (Exception e) { logger.error("An error occurred storing an assignment", e); throw e; } finally { if (statement != null) statement.close(); if (connection != null) connection.close(); } }
From source file:com.mysql.stresstool.RunnableQueryDelete.java
public void run() { if (doDelete) { Connection conn = null;// w ww. j a v a 2 s. c o m 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; conn.setAutoCommit(false); { SoftReference sf = new SoftReference(conn.createStatement()); stmt = (Statement) sf.get(); } // stmt2 = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); long execTime = 0; int pkStart = 0; int pkEnds = 0; ThreadInfo thInfo; long threadTimeStart = System.currentTimeMillis(); active = true; thInfo = new ThreadInfo(); thInfo.setId(this.ID); thInfo.setType("delete"); thInfo.setStatusActive(this.isActive()); StressTool.setInfoDelete(this.ID, thInfo); int deletedRows = 0; int[] pkStartAr = null; int[] pkEndsAr = null; String[][] sqlParameterValues; int[] iLine = { 0, 0 }; // for(int repeat = 0 ; repeat < repeatNumber ; repeat++ ){ // pkEndsAr[repeat] = StressTool.getNumberFromRandom(2147483647).intValue(); // pkStartAr[repeat] = StressTool.getNumberFromRandom(pkEndsAr[repeat]- 10).intValue(); // // } for (int repeat = 0; repeat < repeatNumber; repeat++) { int maxDel = 0; totalLineDeleted = 0; // pkStart = pkStartAr[repeat]; // pkEnds = pkEndsAr[repeat]; // System.gc(); String deleteCheck1 = ""; long timeStart = System.currentTimeMillis(); try { stmt.execute("BEGIN"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { ResultSet rsToDelete = stmt .executeQuery("Select max(a),min(a) from tbtest" + iTable); rsToDelete.next(); DecimalFormat df = new DecimalFormat("#.000000"); long maxDelete = rsToDelete.getLong(1); long minDelete = rsToDelete.getLong(2); long maxToDelete = new Double( ((double) this.getDeleterowmaxpct() * maxDelete) / 100).longValue(); PreparedStatement pstmt = null; { SoftReference sf = new SoftReference(conn.prepareStatement( "DELETE FROM tbtest" + iTable + " where a between ? and ?")); pstmt = (PreparedStatement) sf.get(); } int deleted = 0; if (maxDelete > 0) { for (long iCdelete = minDelete; iCdelete < maxToDelete; iCdelete += getDeleterowsinterval()) { pstmt.setLong(1, iCdelete); pstmt.setLong(2, iCdelete += getDeleterowsinterval()); int rows = pstmt.executeUpdate(); if (rows > 0) deleted += rows; if (deleted >= maxToDelete) { totalLineDeleted += deleted; break; } stmt.execute("COMMIT"); } } stmt.execute("COMMIT"); } if (!doSimplePk) { for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { ResultSet rsToDelete = stmt .executeQuery("Select max(a),min(a) from tbtest_child" + iTable); rsToDelete.next(); DecimalFormat df = new DecimalFormat("#.000000"); long maxDelete = rsToDelete.getLong(1); long minDelete = rsToDelete.getLong(2); long maxToDelete = new Double( ((double) this.getDeleterowmaxpct() * maxDelete) / 100).longValue(); PreparedStatement pstmt = conn.prepareStatement( "DELETE FROM tbtest_child" + iTable + " where a between ? and ?"); int deleted = 0; if (maxDelete > 0) { for (long iCdelete = minDelete; iCdelete < maxToDelete; iCdelete += getDeleterowsinterval()) { pstmt.setLong(1, iCdelete); pstmt.setLong(2, iCdelete += getDeleterowsinterval()); int rows = pstmt.executeUpdate(); if (rows > 0) deleted += rows; if (deleted >= maxToDelete) { totalLineDeleted += deleted; break; } stmt.execute("COMMIT"); } } stmt.execute("COMMIT"); } } long timeEnds = System.currentTimeMillis(); execTime = (timeEnds - timeStart); } catch (SQLException sqle) { conn.rollback(); // System.out.println("Query Delete1 = " + deleteCheck1); /** Silently skip any deadlock **/ if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(sqle.toString()); } // sqle.printStackTrace(); } finally { } if (doLog) { System.out.println("Query Delete TH = " + this.getID() + " Id = " + pkStart + " IdEnd = " + pkEnds + " " + "Deleted lines " + (totalLineDeleted) + " Exec Time(ms) =" + execTime); } thInfo.setExecutedLoops(repeat); Thread.sleep(sleepFor); } stmt.close(); // stmt2.close(); conn.close(); long threadTimeEnd = System.currentTimeMillis(); this.executionTime = (threadTimeEnd - threadTimeStart); this.setExecutionTime(executionTime); active = false; // System.out.println("Query Delete TH = " + this.getID() +" Id = " + pkStart + // " IdEnd = " + pkEnds + " " + "Deleted lines " + // deletedRows + " Exec Time(ms) =" + execTime + " Sec =" + (execTime/1000)); thInfo.setExecutionTime(executionTime); thInfo.setStatusActive(false); StressTool.setInfoDelete(this.ID, thInfo); return; } catch (Exception ex) { ex.printStackTrace(); } } } }
From source file:jp.co.tis.gsp.tools.dba.dialect.MysqlDialect.java
@Override protected void dropObjectsInSchema(Connection conn, String dropListSql, String schema, OBJECT_TYPE objType) throws SQLException { Statement stmt = null; ResultSet rs = null;/* w w w. j a va 2s .c om*/ try { stmt = conn.createStatement(); rs = stmt.executeQuery(dropListSql); String dropSql = ""; while (rs.next()) { switch (objType) { case FK: dropSql = "ALTER TABLE " + rs.getString(1) + " DROP FOREIGN KEY " + rs.getString(2); break; case TABLE: dropSql = "DROP TABLE " + rs.getString(1); break; case VIEW: dropSql = "DROP VIEW " + rs.getString(1); break; default: // ?? break; } stmt = conn.createStatement(); System.err.println(dropSql); stmt.execute(dropSql); } } finally { rs.close(); StatementUtil.close(stmt); } }
From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java
private ResultSet getTableResultSet(String folder, String specify, boolean updatable) throws SQLException { if (!_tables.containsKey(folder.toLowerCase())) { return null; }//from www .j a va 2 s .c o m StringBuffer query = new StringBuffer(); query.append("SELECT * FROM " + folder); if (specify != null) { query.append(" WHERE ").append(specify); } Statement stmt = getConnection().createStatement(_resultSetType, (updatable ? ResultSet.CONCUR_UPDATABLE : ResultSet.CONCUR_READ_ONLY)); stmt.execute(query.toString()); ResultSet resultSet = stmt.getResultSet(); return resultSet; }
From source file:mom.trd.opentheso.bdd.helper.GpsHelper.java
public boolean insertPreferences(HikariDataSource ds, String id_Theso, boolean integrerTraduction, boolean reemplacerTraduction, boolean alignementAutomatique, int id_gps_source, int id_user) { boolean status = false; Connection conn;/*w w w. j av a 2 s . com*/ Statement stmt; ResultSet resultSet; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "INSERT INTO gps_preferences" + "(id_thesaurus, id_user, id_alignement_source, gps_integrertraduction," + " gps_reemplacertraduction, gps_alignementautomatique)" + " values('" + id_Theso + "'," + id_user + "," + id_gps_source + ",'" + integrerTraduction + "','" + reemplacerTraduction + "','" + alignementAutomatique + "')"; stmt.execute(query); status = true; } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while add gps preferences : ", sqle); } return status; }