List of usage examples for java.sql Statement getGeneratedKeys
ResultSet getGeneratedKeys() throws SQLException;
Statement object. From source file:capture.MySQLDatabase.java
public void loadInputUrlFromDatabase() { Element element;/*w ww. j a va 2s .c o m*/ Connection con = this.getConnection(); Statement stmt1, stmt2; ResultSet rs; String url, url_id, honeypotid = null; String operationid = null; long count = 0; try { stmt1 = con.createStatement(); stmt2 = con.createStatement(); //get honeypot id String serverip = ConfigManager.getInstance().getConfigOption("server-listen-address"); String serverport = ConfigManager.getInstance().getConfigOption("server-listen-port"); rs = stmt1.executeQuery("SELECT honeypot_id FROM honeypot WHERE ipaddress=\'" + serverip + "\'"); if (rs.next()) { honeypotid = rs.getString(1); } else { //insert a new honeypot stmt1.executeUpdate( "INSERT INTO honeypot(ipaddress, port) Values(\'" + serverip + "\', " + serverport + ")", Statement.RETURN_GENERATED_KEYS); rs = stmt1.getGeneratedKeys(); if (rs.next()) { honeypotid = rs.getString(1); } else { System.out.println("System can't find any honeypot ip=" + serverip); System.exit(0); } } setSystemStatus(true); //add new operation stmt1.executeUpdate("INSERT INTO operation(description, honeypot_id) Values (\'" + "Load urls from database" + "\', \'" + honeypotid + "\')", Statement.RETURN_GENERATED_KEYS); rs = stmt1.getGeneratedKeys(); if (rs.next()) { operationid = rs.getString(1); setCurrentOperation(operationid); } System.out.println("The system is going to inspect urls in the new operation: " + operationid); //update visit start time for operation SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S"); String date = sf.format(new Date()); stmt1.executeUpdate("UPDATE operation SET visitstarttime=\'" + date + "\' " + "WHERE operation_id=" + operationid + " AND visitstarttime IS NULL"); //load urls from url table System.out.println("Loading urls from database...."); rs = stmt1.executeQuery("SELECT url_id, url FROM url"); while (rs.next()) { url_id = rs.getString(1); url = rs.getString(2); stmt2.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values (" + url_id + ", " + operationid + ")"); element = new Element(); element.name = "url"; element.attributes.put("add", ""); element.attributes.put("id", url_id); element.attributes.put("url", url); EventsController.getInstance().notifyEventObservers(element); count++; } con.close(); System.out.println("******** LOADING URL FROM DATABASE: " + count + " urls have been loaded! ********"); } catch (Exception e) { e.printStackTrace(); } }
From source file:backtype.storm.scheduler.adaptive.DataManager.java
public int checkTopology(String stormId) throws Exception { Connection connection = null; Statement statement = null; ResultSet resultSet = null;//www.j av a 2 s.c om int id = -1; logger.debug("Going to check topology " + stormId); try { connection = getConnection(); connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); connection.setAutoCommit(false); statement = connection.createStatement(); String sql = "select id from topology where storm_id = '" + stormId + "'"; logger.debug("SQL query: " + sql); resultSet = statement.executeQuery(sql); if (resultSet.next()) { id = resultSet.getInt(1); logger.debug("Topology found, id: " + id); } else { logger.debug("Topology not found, let's create it"); resultSet.close(); sql = "insert into topology(storm_id) values('" + stormId + "')"; logger.debug("SQL script: " + sql); statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); logger.debug("Retrieving generated id..."); resultSet = statement.getGeneratedKeys(); if (resultSet.next()) { id = resultSet.getInt(1); logger.debug("Ok, id: " + id); } else { throw new Exception("Cannot retrieve generated key"); } } connection.commit(); } catch (Exception e) { connection.rollback(); logger.error("An error occurred checking a topology", e); throw e; } finally { if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (connection != null) connection.close(); } return id; }
From source file:code.Servlet.java
private void register(BufferedReader reader, HttpServletResponse response, Statement stmt, PrintWriter out) throws IOException, SQLException { ResultSet rs;//from w w w.j av a 2 s . c o m System.out.println("register"); String line = null; String email = null; String password = null; String message = null; String sql; line = reader.readLine(); email = line.substring(10, line.length() - 2); line = reader.readLine(); password = line.substring(13, line.length() - 2); line = reader.readLine(); String name = line.substring(9, line.length() - 2); line = reader.readLine(); String surname = line.substring(12, line.length() - 2); sql = "INSERT INTO `mydb_treasure`.`User`(`name`, `surname`, `points`, `email`, `password`) VALUES ('" + name + "', '" + surname + "', '" + "0" + "', '" + email + "', '" + password + "')"; stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet newIdUser = stmt.getGeneratedKeys(); if (newIdUser.next()) { idUser = newIdUser.getString(1); System.out.println(idUser); } else { ; // exception!!! *** } message = "ok register"; response.setContentType("text/plain"); response.setContentLength(message.length()); PrintWriter reply = response.getWriter(); reply.println(message); out.close(); //non so se serve out out.flush(); }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * Returns the ID of the record that was just inserted. * @param stmt the insert statement/*from w w w. j av a 2 s . c om*/ * @return null on error, or the ID */ public static Integer getInsertedId(final Statement stmt) { Integer count = null; ResultSet resultSet = null; try { resultSet = stmt.getGeneratedKeys(); if (resultSet != null && resultSet.next()) { count = resultSet.getInt(1); } } catch (SQLException ex) { ex.printStackTrace(); } finally { try { if (resultSet != null) resultSet.close(); } catch (SQLException ex) { } } return count; }
From source file:capture.MySQLDatabase.java
public void loadInputUrlFromFile(final String inputUrlsFile) { Element element;/* w w w .j a v a2 s .c o m*/ Connection con = this.getConnection(); Statement stmt; PreparedStatement ps; ResultSet rs; String line, url_id, honeypotid = null; String operationid = null; boolean check = true; long count = 0; if (inputUrlsFile == null) { System.out.println("Error: There is no input-url file!"); System.exit(1); } if ((ConfigManager.getInstance().getConfigOption("import_check") != null) && (ConfigManager.getInstance().getConfigOption("import_check").toLowerCase().equals("false"))) { check = false; } try { stmt = con.createStatement(); //get honeypot id String serverip = ConfigManager.getInstance().getConfigOption("server-listen-address"); String serverport = ConfigManager.getInstance().getConfigOption("server-listen-port"); rs = stmt.executeQuery("SELECT honeypot_id FROM honeypot WHERE ipaddress=\'" + serverip + "\'"); if (rs.next()) { honeypotid = rs.getString(1); } else { //insert a new honeypot stmt.executeUpdate( "INSERT INTO honeypot(ipaddress, port) Values(\'" + serverip + "\', " + serverport + ")", Statement.RETURN_GENERATED_KEYS); rs = stmt.getGeneratedKeys(); if (rs.next()) { honeypotid = rs.getString(1); } else { System.out.println("System can't find any honeypot ip=" + serverip); System.exit(0); } } setSystemStatus(true); //open url file BufferedReader in = new BufferedReader( new InputStreamReader(new FileInputStream(inputUrlsFile), "UTF-8")); //add new operation stmt.executeUpdate("INSERT INTO operation(description, honeypot_id) Values (\'" + inputUrlsFile + "\', \'" + honeypotid + "\')", Statement.RETURN_GENERATED_KEYS); rs = stmt.getGeneratedKeys(); if (rs.next()) { operationid = rs.getString(1); setCurrentOperation(operationid); } System.out.println("The system is going to inspect urls in the new operation: " + operationid); //update visit start time for operation SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S"); String date = sf.format(new Date()); stmt.executeUpdate("UPDATE operation SET visitstarttime=\'" + date + "\' " + "WHERE operation_id=" + operationid + " AND visitstarttime IS NULL"); System.out.println("Please wait for inserting urls into database..."); if (!check) //NO checking existance of url in database { while ((line = in.readLine()) != null) { if ((line.length() > 0)) { //line = line.trim().toLowerCase(); line = line.trim(); if (!line.startsWith("#")) { ps = con.prepareStatement("INSERT INTO url(url) Values (?)", Statement.RETURN_GENERATED_KEYS); ps.setString(1, line); ps.executeUpdate(); rs = ps.getGeneratedKeys(); rs.next(); url_id = rs.getString(1); stmt.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values (" + url_id + ", " + operationid + ")"); element = new Element(); element.name = "url"; element.attributes.put("add", ""); element.attributes.put("id", url_id); element.attributes.put("url", line); EventsController.getInstance().notifyEventObservers(element); count++; } } } } else //checking existance of url in database. Inserting only if no existance { while ((line = in.readLine()) != null) { if ((line.length() > 0)) { //line = line.trim().toLowerCase(); line = line.trim(); if (!line.startsWith("#")) { ps = con.prepareStatement("SELECT url_id FROM url WHERE url.url = ?"); ps.setString(1, line); rs = ps.executeQuery(); if (!rs.next()) { ps = con.prepareStatement("INSERT INTO url(url) Values (?)", Statement.RETURN_GENERATED_KEYS); ps.setString(1, line); ps.executeUpdate(); rs = ps.getGeneratedKeys(); rs.next(); count++; } //check URL id and operation id: not exist url_id = rs.getString(1); ps = con.prepareStatement( "SELECT url_id, operation_id FROM url_operation WHERE url_id = ? AND operation_id= ?"); ps.setLong(1, Long.parseLong(url_id)); ps.setLong(2, Long.parseLong(operationid)); rs = ps.executeQuery(); if (!rs.next()) { stmt.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values (" + url_id + ", " + operationid + ")"); element = new Element(); element.name = "url"; element.attributes.put("add", ""); element.attributes.put("id", url_id); element.attributes.put("url", line); EventsController.getInstance().notifyEventObservers(element); } } } } } con.close(); System.out.println("******** IMPORT URLs INTO DATABASE: " + count + " URLs have been inserted into database! ********"); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private void writeOutGeneratedKeys(Statement stmt, XMLStreamWriter xmlWriter, InternalParamCollection params, int queryLevel) throws DataServiceFault, SQLException { ResultSet krs = stmt.getGeneratedKeys(); DataEntry dataEntry;/*from www. ja v a 2s . c o m*/ while (krs.next()) { dataEntry = this.getDataEntryFromRS(new ResultSetWrapper(krs)); this.writeResultEntry(xmlWriter, dataEntry, params, queryLevel); } }
From source file:it.fub.jardin.server.DbUtils.java
private ResultSet doUpdate(final Connection connection, final String query) throws SQLException { Statement update = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); update.executeUpdate(query, Statement.RETURN_GENERATED_KEYS); return update.getGeneratedKeys(); }
From source file:org.openconcerto.sql.model.SQLDataSource.java
/** * Execute la requete avec le statement pass. Attention cette mthode ne peut fermer le * statement car elle retourne directement le resultSet. * /*w ww . ja v a2 s .c om*/ * @param query le requte excuter. * @param stmt le statement. * @return le rsultat de la requte, should never be null according to the spec but Derby don't * care. * @throws SQLException si erreur lors de l'excution de la requte. */ private ResultSet execute(String query, Statement stmt) throws SQLException, RTInterruptedException { // System.err.println("\n" + count + "*** " + query + "\n"); if (State.DEBUG) State.INSTANCE.beginRequest(query); // test before calling JDBC methods and creating threads boolean interrupted = false; if (QUERY_TUNING > 0) { try { Thread.sleep(QUERY_TUNING); } catch (InterruptedException e1) { interrupted = true; } } else { interrupted = Thread.currentThread().isInterrupted(); } if (interrupted) { throw new RTInterruptedException("request interrupted : " + query); } final long t1 = System.currentTimeMillis(); ResultSet rs = null; try { // MAYBE un truc un peu plus formel if (query.startsWith("INSERT") || query.startsWith("UPDATE") || query.startsWith("DELETE") || query.startsWith("CREATE") || query.startsWith("ALTER") || query.startsWith("DROP") || query.startsWith("SET")) { // MS SQL doesn't support UPDATE final boolean returnGenK = query.startsWith("INSERT") && stmt.getConnection().getMetaData().supportsGetGeneratedKeys(); stmt.executeUpdate(query, returnGenK ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS); rs = returnGenK ? stmt.getGeneratedKeys() : null; } else { // TODO en faire qu'un seul par Connection final ExecutorThread thr = new ExecutorThread(stmt, query); // on lance l'excution thr.start(); // et on attend soit qu'elle finisse soit qu'on soit interrompu try { rs = thr.getRs(); } catch (InterruptedException e) { thr.stopQuery(); throw new InterruptedQuery("request interrupted : " + query, e, thr); } } } finally { if (State.DEBUG) State.INSTANCE.endRequest(query); } long t2 = System.currentTimeMillis(); // obviously very long queries tend to last longer, that's normal so don't warn if (t2 - t1 > 1000 && query.length() < 1000) { System.err.println("Warning:" + (t2 - t1) + "ms pour :" + query); } count++; return rs; }
From source file:org.kawanfw.sql.servlet.sql.ServerStatementRawExecute.java
/** * Execute the passed SQL Statement as execute(sql) and return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * /*ww w.j av a 2s. com*/ * @param sqlOrder * the qsql order * @param sqlParms * the sql parameters * @param out * the output stream where to write to result set output * * * @throws SQLException */ private void executeStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); // sqlOrder = HtmlConverter.fromHtml(sqlOrder); // Should never be called? if (statementHolder.isDoExtractResultSetMetaData()) { sqlOrder = DbVendorManager.addLimit1(sqlOrder, connection); } Statement statement = null; try { if (!SqlConfiguratorCall.allowExecute(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized for execute}" + "{sql order : " + sqlOrder + "}" + "]"; throw new SecurityException(message); } statement = connection.createStatement(); ServerSqlUtil.setStatementProperties(statement, statementHolder); debug("before ServerPreparedStatementParameters"); boolean isAllowed = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, new Vector<Object>()); if (!isAllowed) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } debug("before statement.execute(sqlOrder)"); debug("sqlOrder: " + sqlOrder); ServerSqlUtil.setMaxRowsToReturn(statement, sqlConfigurator); boolean isResultSet = false; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { isResultSet = statement.execute(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { isResultSet = statement.execute(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { isResultSet = statement.execute(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { debug("before isResultSet = statement.execute(sqlOrder);"); isResultSet = statement.execute(sqlOrder); } debug("isResultSet :" + isResultSet); debug("usesAutoGeneratedKeys: " + usesAutoGeneratedKeys); if (isResultSet) { ResultSet rs = statement.getResultSet(); try { //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } else { debug("int rc = statement.getUpdateCount();"); int rc = statement.getUpdateCount(); //br.write(TransferStatus.SEND_OK + CR_LF); //br.write("getUpdateCount=" + rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "getUpdateCount=" + rc); if (usesAutoGeneratedKeys) { ResultSet rs = statement.getGeneratedKeys(); try { ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } } } catch (SQLException e) { ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT_PRODUCT_FAIL + CR_LF + "Statement: " + sqlOrder + CR_LF + "- sql order: " + sqlOrder + CR_LF + "- exception: " + e.toString()); throw e; } finally { IOUtils.closeQuietly(out); if (statement != null) { statement.close(); } } }
From source file:org.kawanfw.sql.servlet.sql.ServerStatement.java
/** * Execute the passed SQL Statement and return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * /*from www . ja v a 2s . c o m*/ * @param sqlOrder * the qsql order * @param sqlParms * the sql parameters * @param out * the output stream where to write to result set output * * * @throws SQLException */ private void executeQueryOrUpdateStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); debug("statementHolder: " + statementHolder.getSqlOrder()); debug("sqlOrder : " + sqlOrder); // sqlOrder = HtmlConverter.fromHtml(sqlOrder); if (statementHolder.isDoExtractResultSetMetaData()) { sqlOrder = DbVendorManager.addLimit1(sqlOrder, connection); } Statement statement = null; try { if (!SqlConfiguratorCall.allowStatementClass(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } statement = connection.createStatement(); ServerSqlUtil.setStatementProperties(statement, statementHolder); debug("before ServerPreparedStatementParameters"); boolean isAllowedAfterAnalysis = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, new Vector<Object>()); if (!isAllowedAfterAnalysis) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } isAllowedAfterAnalysis = SqlConfiguratorCall.allowResultSetGetMetaData(sqlConfigurator, username, connection); if (statementHolder.isDoExtractResultSetMetaData() && !isAllowedAfterAnalysis) { String message = Tag.PRODUCT_SECURITY + " ResultSet.getMetaData() Query not authorized."; throw new SecurityException(message); } debug("before executeQuery() / executeUpdate(sqlOrder)"); if (statementHolder.isExecuteUpdate()) { if (!SqlConfiguratorCall.allowExecuteUpdate(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Statement not authorized for ExecuteUpdate}" + "{sql order: " + sqlOrder + "}]"; throw new SecurityException(message); } int rc = -1; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { rc = statement.executeUpdate(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { rc = statement.executeUpdate(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { rc = statement.executeUpdate(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { rc = statement.executeUpdate(sqlOrder); } //br.write(TransferStatus.SEND_OK + CR_LF); //br.write(rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "" + rc); if (usesAutoGeneratedKeys) { ResultSet rs = null; try { rs = statement.getGeneratedKeys(); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) { rs.close(); } } } } else { ResultSet rs = null; try { if (statementHolder.isDoExtractResultSetMetaData()) { statement.setMaxRows(1); } else { ServerSqlUtil.setMaxRowsToReturn(statement, sqlConfigurator); } rs = statement.executeQuery(sqlOrder); //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); if (statementHolder.isDoExtractResultSetMetaData()) { ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(out, commonsConfigurator, sqlConfigurator); resultSetMetaDataWriter.write(rs); } else { // print(rs, br); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } } finally { if (rs != null) { rs.close(); } } } } catch (SQLException e) { ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT_EXCEPTION_RAISED + CR_LF + "Statement: " + sqlOrder + CR_LF + "- sql order: " + sqlOrder + CR_LF + "- exception: " + e.toString()); throw e; } finally { IOUtils.closeQuietly(out); if (statement != null) { statement.close(); } } }