List of usage examples for java.sql Statement RETURN_GENERATED_KEYS
int RETURN_GENERATED_KEYS
To view the source code for java.sql Statement RETURN_GENERATED_KEYS.
Click Source Link
From source file:at.alladin.rmbt.controlServer.RegistrationResource.java
@Post("json") public String request(final String entity) { long startTime = System.currentTimeMillis(); final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();/*from w w w . ja va 2s . c o m*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; final String clientIpRaw = getIP(); final InetAddress clientAddress = InetAddresses.forString(clientIpRaw); final String clientIpString = InetAddresses.toAddrString(clientAddress); System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST"), clientIpRaw)); final String geoIpCountry = GeoIPHelper.lookupCountry(clientAddress); // public_ip_asn final Long asn = Helperfunctions.getASN(clientAddress); // public_ip_as_name // country_asn (2 digit country code of AS, eg. AT or EU) final String asName; final String asCountry; if (asn == null) { asName = null; asCountry = null; } else { asName = Helperfunctions.getASName(asn); asCountry = Helperfunctions.getAScountry(asn); } if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); int typeId = 0; final String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } // System.out.println(request.toString(4)); if (conn != null) { final Client clientDb = new Client(conn); if (!request.optString("type").isEmpty()) { typeId = clientDb.getTypeId(request.getString("type")); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (clientNames.contains(request.optString("client")) && clientVersions.contains(request.optString("version")) && typeId > 0) { UUID uuid = null; final String uuidString = request.optString("uuid", ""); if (uuidString.length() != 0) uuid = UUID.fromString(uuidString); final String clientName = request.getString("client"); final String clientVersion = request.getString("version"); String timeZoneId = request.getString("timezone"); // String tmpTimeZoneId = timeZoneId; final long clientTime = request.getLong("time"); final Timestamp clientTstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); final JSONObject location = request.optJSONObject("location"); long geotime = 0; double geolat = 0; double geolong = 0; float geoaccuracy = 0; double geoaltitude = 0; float geobearing = 0; float geospeed = 0; String geoprovider = ""; if (!request.isNull("location")) { geotime = location.optLong("time", 0); geolat = location.optDouble("lat", 0); geolong = location.optDouble("long", 0); geoaccuracy = (float) location.optDouble("accuracy", 0); geoaltitude = location.optDouble("altitude", 0); geobearing = (float) location.optDouble("bearing", 0); geospeed = (float) location.optDouble("speed", 0); geoprovider = location.optString("provider", ""); } Calendar timeWithZone = null; if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); long clientUid = 0; /* * if (uuid == null) { * clientDb.setTimeZone(timeWithZone); * clientDb.setTime(tstamp); * clientDb.setClient_type_id(typeId); uuid = * clientDb.storeClient(); if (clientDb.hasError()) { * errorList.addError(clientDb.getError()); } else { * answer.put("uuid", uuid.toString()); } } */ if (errorList.getLength() == 0 && uuid != null) { clientUid = clientDb.getClientByUuid(uuid); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } if (clientUid > 0) { final String testUuid = UUID.randomUUID().toString(); final String testOpenUuid = UUID.randomUUID().toString(); boolean testServerEncryption = true; // default is // true // hack for android api <= 10 (2.3.x) // using encryption with test doesn't work if (request.has("plattform") && request.optString("plattform").equals("Android")) if (request.has("api_level")) { final String apiLevelString = request.optString("api_level"); try { final int apiLevel = Integer.parseInt(apiLevelString); if (apiLevel <= 10) testServerEncryption = false; } catch (final NumberFormatException e) { } } final String serverType; if (request.optString("client").equals("RMBTws")) serverType = "RMBTws"; else serverType = "RMBT"; final Boolean ipv6; if (clientAddress instanceof Inet6Address) ipv6 = true; else if (clientAddress instanceof Inet4Address) ipv6 = false; else // should never happen, unless ipv > 6 is available ipv6 = null; final TestServer server = getNearestServer(errorList, geolat, geolong, geotime, clientIpString, asCountry, geoIpCountry, serverType, testServerEncryption, ipv6); try { if (server == null) throw new JSONException("could not find server"); if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); answer.put("test_server_address", server.address); answer.put("test_server_port", server.port); answer.put("test_server_name", server.name); answer.put("test_server_encryption", testServerEncryption); answer.put("test_duration", getSetting("rmbt_duration")); answer.put("test_numthreads", getSetting("rmbt_num_threads")); answer.put("test_numpings", getSetting("rmbt_num_pings")); answer.put("client_remote_ip", clientIpString); final String resultUrl = new Reference(getURL(), settings.getString("RMBT_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_url", resultUrl); final String resultQoSUrl = new Reference(getURL(), settings.getString("RMBT_QOS_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_qos_url", resultQoSUrl); } catch (final JSONException e) { System.out.println("Error generating Answer " + e.toString()); errorList.addError("ERROR_RESPONSE_JSON"); } if (errorList.getLength() == 0) try { PreparedStatement st; st = conn.prepareStatement( "INSERT INTO test(time, uuid, open_test_uuid, client_id, client_name, client_version, client_software_version, client_language, client_public_ip, client_public_ip_anonymized, country_geoip, server_id, port, use_ssl, timezone, client_time, duration, num_threads_requested, status, software_revision, client_test_counter, client_previous_test_status, public_ip_asn, public_ip_as_name, country_asn, public_ip_rdns, run_ndt)" + "VALUES(NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); int i = 1; // uuid st.setObject(i++, UUID.fromString(testUuid)); // open_test_uuid st.setObject(i++, UUID.fromString(testOpenUuid)); // client_id st.setLong(i++, clientUid); // client_name st.setString(i++, clientName); // client_version st.setString(i++, clientVersion); // client_software_version st.setString(i++, request.optString("softwareVersion", null)); // client_language st.setString(i++, lang); // client_public_ip st.setString(i++, clientIpString); // client_public_ip_anonymized st.setString(i++, Helperfunctions.anonymizeIp(clientAddress)); // country_geoip (2digit country code derived from public IP of client) st.setString(i++, geoIpCountry); // server_id st.setInt(i++, server.id); // port st.setInt(i++, server.port); // use_ssl st.setBoolean(i++, testServerEncryption); // timezone (of client) st.setString(i++, timeZoneId); // client_time (local time of client) st.setTimestamp(i++, clientTstamp, timeWithZone); // duration (requested) st.setInt(i++, Integer.parseInt(getSetting("rmbt_duration"))); // num_threads_requested st.setInt(i++, Integer.parseInt(getSetting("rmbt_num_threads"))); // status (of test) st.setString(i++, "STARTED"); //was "RUNNING" before // software_revision (of client) st.setString(i++, request.optString("softwareRevision", null)); // client_test_counter (number of tests the client has performed) final int testCounter = request.optInt("testCounter", -1); if (testCounter == -1) // older clients did not support testCounter st.setNull(i++, Types.INTEGER); else st.setLong(i++, testCounter); // client_previous_test_status (outcome of previous test) st.setString(i++, request.optString("previousTestStatus", null)); // AS name if (asn == null) st.setNull(i++, Types.BIGINT); else st.setLong(i++, asn); if (asName == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asName); // AS country if (asCountry == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asCountry); //public_ip_rdns String reverseDNS = Helperfunctions.reverseDNSLookup(clientAddress); if (reverseDNS == null || reverseDNS.isEmpty()) st.setNull(i++, Types.VARCHAR); else { reverseDNS = reverseDNS.replaceFirst("\\.$", ""); st.setString(i++, reverseDNS); // cut off last dot (#332) } // run_ndt if (request.has("ndt")) st.setBoolean(i++, request.getBoolean("ndt")); else st.setNull(i++, Types.BOOLEAN); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_STORE_TEST"); else { long key = 0; final ResultSet rs = st.getGeneratedKeys(); if (rs.next()) // Retrieve the auto generated // key(s). key = rs.getLong(1); rs.close(); final PreparedStatement getProviderSt = conn .prepareStatement("SELECT rmbt_set_provider_from_as(?)"); getProviderSt.setLong(1, key); String provider = null; if (getProviderSt.execute()) { final ResultSet rs2 = getProviderSt.getResultSet(); if (rs2.next()) provider = rs2.getString(1); } if (provider != null) answer.put("provider", provider); final PreparedStatement testSlotStatement = conn .prepareStatement("SELECT rmbt_get_next_test_slot(?)"); testSlotStatement.setLong(1, key); int testSlot = -1; if (testSlotStatement.execute()) { final ResultSet rs2 = testSlotStatement.getResultSet(); if (rs2.next()) testSlot = rs2.getInt(1); } if (testSlot < 0) errorList.addError("ERROR_DB_STORE_GENERAL"); else { final String data = testUuid + "_" + testSlot; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); final String token = data + "_" + hmac; final PreparedStatement updateSt = conn .prepareStatement("UPDATE test SET token = ? WHERE uid = ?"); updateSt.setString(1, token); updateSt.setLong(2, key); updateSt.executeUpdate(); answer.put("test_token", token); answer.put("test_uuid", testUuid); answer.put("test_id", key); final long now = System.currentTimeMillis(); int wait = testSlot - (int) (now / 1000); if (wait < 0) wait = 0; answer.put("test_wait", wait); if (geotime != 0 && geolat != 0 && geolong != 0) { final GeoLocation clientLocation = new GeoLocation(conn); clientLocation.setTest_id(key); final Timestamp geotstamp = java.sql.Timestamp .valueOf(new Timestamp(geotime).toString()); clientLocation.setTime(geotstamp, timeZoneId); clientLocation.setAccuracy(geoaccuracy); clientLocation.setAltitude(geoaltitude); clientLocation.setBearing(geobearing); clientLocation.setSpeed(geospeed); clientLocation.setProvider(geoprovider); clientLocation.setGeo_lat(geolat); clientLocation.setGeo_long(geolong); clientLocation.storeLocation(); if (clientLocation.hasError()) errorList.addError(clientLocation.getError()); } } } st.close(); } catch (final SQLException e) { errorList.addError("ERROR_DB_STORE_GENERAL"); e.printStackTrace(); } } else errorList.addError("ERROR_CLIENT_UUID"); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_DB_CONNECTION"); // System.out.println(answer.toString(4)); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } answerString = answer.toString(); long elapsedTime = System.currentTimeMillis() - startTime; System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST_SUCCESS"), clientIpRaw, Long.toString(elapsedTime))); return answerString; }
From source file:com.github.shynixn.blockball.bukkit.logic.persistence.context.SqlDbContextImpl.java
/** * Executes a preparedStatement and sets the given parameters to the statement * * @param sql sql// www. j av a2 s . c o m * @param connection connection * @param parameters parameters * @return id * @throws SQLException exception */ public int executeInsert(String sql, Connection connection, Object... parameters) throws SQLException { if (sql == null) throw new IllegalArgumentException("Sql cannot be null!"); if (connection == null) throw new IllegalArgumentException("Connection cannot be null!"); if (connection.isClosed()) throw new IllegalArgumentException("Connection is closed. Cannot create statement!"); try (final PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { this.setParameters(preparedStatement, parameters); preparedStatement.executeUpdate(); try (final ResultSet resultSet = preparedStatement.getGeneratedKeys()) { resultSet.next(); return resultSet.getInt(1); } } }
From source file:org.jenkinsci.plugins.pipeline.maven.dao.PipelineMavenPluginH2Dao.java
protected long getOrCreateArtifactPrimaryKey(String groupId, String artifactId, String version, String type) { try (Connection cnn = jdbcConnectionPool.getConnection()) { cnn.setAutoCommit(false);//from ww w . java2 s.c o m // get or create build record Long artifactPrimaryKey = null; try (PreparedStatement stmt = cnn.prepareStatement( "SELECT ID FROM MAVEN_ARTIFACT WHERE GROUP_ID = ? AND ARTIFACT_ID = ? AND VERSION = ? AND TYPE = ?")) { stmt.setString(1, groupId); stmt.setString(2, artifactId); stmt.setString(3, version); stmt.setString(4, type); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { artifactPrimaryKey = rst.getLong(1); } } } if (artifactPrimaryKey == null) { try (PreparedStatement stmt = cnn.prepareStatement( "INSERT INTO MAVEN_ARTIFACT(GROUP_ID, ARTIFACT_ID, VERSION, TYPE) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) { stmt.setString(1, groupId); stmt.setString(2, artifactId); stmt.setString(3, version); stmt.setString(4, type); stmt.execute(); try (ResultSet rst = stmt.getGeneratedKeys()) { if (rst.next()) { artifactPrimaryKey = rst.getLong(1); } else { throw new IllegalStateException(); } } } } cnn.commit(); return artifactPrimaryKey; } catch (SQLException e) { throw new RuntimeSqlException(e); } }
From source file:com.bloidonia.vertx.mods.JdbcProcessor.java
private void doUpdate(Message<JsonObject> message, Connection connection, final boolean insert, TransactionalHandler transaction) throws SQLException { new BatchHandler(connection, message, transaction) { void initialiseStatement(Message<JsonObject> initial) throws SQLException { if (insert) { this.statement = connection.prepareStatement(initial.body().getString("stmt"), Statement.RETURN_GENERATED_KEYS); } else { this.statement = connection.prepareStatement(initial.body().getString("stmt")); }/*ww w. j a va 2 s . co m*/ } public JsonObject process() throws SQLException { JsonObject reply = new JsonObject(); ArrayList<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); // processing int nRows = 0; if (insert) { while ((resultSet != null || valueIterator.hasNext()) && (batchSize == -1 || result.size() < batchSize)) { LimitedMapListHandler handler = new LimitedMapListHandler( batchSize == -1 ? -1 : batchSize - result.size()); if (resultSet == null) { List<Object> params = valueIterator.next(); statementFiller.fill(statement, params); nRows += statement.executeUpdate(); resultSet = statement.getGeneratedKeys(); } store(result, handler); } reply.putArray("result", JsonUtils.listOfMapsToJsonArray(result)); } else { while (valueIterator.hasNext()) { List<Object> params = valueIterator.next(); statementFiller.fill(statement, params); nRows += statement.executeUpdate(); } } reply.putNumber("updated", nRows); return reply; } }.handle(message); }
From source file:org.apache.stratos.adc.mgt.utils.PersistenceManager.java
public static int persistSubscription(CartridgeSubscription cartridgeSubscription) throws Exception { int cartridgeSubscriptionId = 0; int repoId = 0; int dataCartridgeId = 0; ResultSet res = null;//from ww w . ja va 2s.c o m PreparedStatement insertSubscriptionStmt = null; PreparedStatement insertRepoStmt = null; PreparedStatement insertDataCartStmt = null; Connection con = null; // persist cartridge_subscription try { con = StratosDBUtils.getConnection(); // persist repo if (cartridgeSubscription.getRepository() != null) { String encryptedRepoUserPassword = encryptPassword( cartridgeSubscription.getRepository().getRepoUserPassword()); String insertRepo = "INSERT INTO REPOSITORY (REPO_NAME,STATE,REPO_USER_NAME,REPO_USER_PASSWORD)" + " VALUES (?,?,?,?)"; insertRepoStmt = con.prepareStatement(insertRepo, Statement.RETURN_GENERATED_KEYS); insertRepoStmt.setString(1, cartridgeSubscription.getRepository().getRepoName()); insertRepoStmt.setString(2, "ACTIVE"); insertRepoStmt.setString(3, cartridgeSubscription.getRepository().getRepoUserName()); insertRepoStmt.setString(4, encryptedRepoUserPassword); if (log.isDebugEnabled()) { log.debug("Executing insert: " + insertRepo); } insertRepoStmt.executeUpdate(); res = insertRepoStmt.getGeneratedKeys(); if (res.next()) { repoId = res.getInt(1); } StratosDBUtils.closeResultSet(res); } // persist data cartridge if (cartridgeSubscription.getDataCartridge() != null) { String insertDataCartridge = "INSERT INTO DATA_CARTRIDGE (TYPE,USER_NAME,PASSWORD,STATE)" + " VALUES (?,?,?,?)"; insertDataCartStmt = con.prepareStatement(insertDataCartridge, Statement.RETURN_GENERATED_KEYS); insertDataCartStmt.setString(1, cartridgeSubscription.getDataCartridge().getDataCartridgeType()); insertDataCartStmt.setString(2, cartridgeSubscription.getDataCartridge().getUserName()); insertDataCartStmt.setString(3, cartridgeSubscription.getDataCartridge().getPassword()); insertDataCartStmt.setString(4, "ACTIVE"); if (log.isDebugEnabled()) { log.debug("Executing insert: " + insertDataCartridge); } insertDataCartStmt.executeUpdate(); res = insertDataCartStmt.getGeneratedKeys(); if (res.next()) { dataCartridgeId = res.getInt(1); } StratosDBUtils.closeResultSet(res); } String insertSubscription = "INSERT INTO CARTRIDGE_SUBSCRIPTION (TENANT_ID, CARTRIDGE, PROVIDER," + "HOSTNAME, POLICY, CLUSTER_DOMAIN, " + "CLUSTER_SUBDOMAIN, MGT_DOMAIN, MGT_SUBDOMAIN, STATE, " + "ALIAS, TENANT_DOMAIN, BASE_DIR, REPO_ID, DATA_CARTRIDGE_ID)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; insertSubscriptionStmt = con.prepareStatement(insertSubscription, Statement.RETURN_GENERATED_KEYS); insertSubscriptionStmt.setInt(1, cartridgeSubscription.getTenantId()); insertSubscriptionStmt.setString(2, cartridgeSubscription.getCartridge()); insertSubscriptionStmt.setString(3, cartridgeSubscription.getProvider()); insertSubscriptionStmt.setString(4, cartridgeSubscription.getHostName()); insertSubscriptionStmt.setString(5, cartridgeSubscription.getPolicy()); insertSubscriptionStmt.setString(6, cartridgeSubscription.getClusterDomain()); insertSubscriptionStmt.setString(7, cartridgeSubscription.getClusterSubdomain()); insertSubscriptionStmt.setString(8, cartridgeSubscription.getMgtClusterDomain()); insertSubscriptionStmt.setString(9, cartridgeSubscription.getMgtClusterSubDomain()); insertSubscriptionStmt.setString(10, cartridgeSubscription.getState()); insertSubscriptionStmt.setString(11, cartridgeSubscription.getAlias()); insertSubscriptionStmt.setString(12, cartridgeSubscription.getTenantDomain()); insertSubscriptionStmt.setString(13, cartridgeSubscription.getBaseDirectory()); insertSubscriptionStmt.setInt(14, repoId); insertSubscriptionStmt.setInt(15, dataCartridgeId); if (log.isDebugEnabled()) { log.debug("Executing insert: " + insertSubscription); } insertSubscriptionStmt.executeUpdate(); res = insertSubscriptionStmt.getGeneratedKeys(); if (res.next()) { cartridgeSubscriptionId = res.getInt(1); } List<PortMapping> portMapping = cartridgeSubscription.getPortMappings(); // persist port map if (portMapping != null && !portMapping.isEmpty()) { for (PortMapping portMap : portMapping) { String insertPortMapping = "INSERT INTO PORT_MAPPING (SUBSCRIPTION_ID, TYPE, PRIMARY_PORT, PROXY_PORT, STATE)" + " VALUES (?,?,?,?,?)"; PreparedStatement insertPortsStmt = con.prepareStatement(insertPortMapping); insertPortsStmt.setInt(1, cartridgeSubscriptionId); insertPortsStmt.setString(2, portMap.getType()); insertPortsStmt.setString(3, portMap.getPrimaryPort()); insertPortsStmt.setString(4, portMap.getProxyPort()); insertPortsStmt.setString(5, "ACTIVE"); if (log.isDebugEnabled()) { log.debug("Executing insert: " + insertPortMapping); } insertPortsStmt.executeUpdate(); StratosDBUtils.closeStatement(insertPortsStmt); } } con.commit(); // Commit manually } catch (Exception e) { if (con != null) { try { con.rollback(); } catch (SQLException e1) { log.error("Failed to rollback", e); } } ; log.error(e.getMessage()); String msg = "Exception : " + e.getMessage(); log.error(msg, e); throw new Exception("Subscription failed!", e); } finally { StratosDBUtils.closeResultSet(res); StratosDBUtils.closeAllConnections(con, insertRepoStmt, insertDataCartStmt, insertSubscriptionStmt); } return cartridgeSubscriptionId; }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
private void addFeedFields(long id, List<FieldDescription> fields) throws SQLException { if (fields == null || fields.size() == 0) { Log.e(TAG, "One cannot create a composed feed with no fields"); return;//from ww w .j av a2 s . c om } String sqlInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FIELD + "(" + IotHubDataHandler.KEY_FIELD_FEED_ID + "," + IotHubDataHandler.KEY_FIELD_NAME + "," + IotHubDataHandler.KEY_FIELD_METADATA + "," + IotHubDataHandler.KEY_FIELD_TYPE + "," + IotHubDataHandler.KEY_FIELD_OPTIONAL + ") values (?,?,?,?,?)"; PreparedStatement psInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); for (FieldDescription fd : fields) { psInsert.setLong(1, id); psInsert.setString(2, fd.getName()); psInsert.setString(3, fd.getMetadata()); psInsert.setString(4, fd.getType()); psInsert.setInt(5, fd.isOptional() ? 1 : 0); psInsert.executeUpdate(); ResultSet genKeysFeed = psInsert.getGeneratedKeys(); if (genKeysFeed.next()) { long idField = genKeysFeed.getLong(1); addFieldKeywords(idField, fd.getKeywords()); } genKeysFeed.close(); } psInsert.close(); }
From source file:org.geoserver.jdbcstore.internal.JDBCQueryHelper.java
public List<Integer> anyInsertQuery(QueryBuilder query) { LOGGER.log(Level.FINEST, query.toString()); try (Connection c = ds.getConnection()) { try (PreparedStatement stmt = query.toStatement(c, Statement.RETURN_GENERATED_KEYS)) { if (stmt.executeUpdate() <= 0) { return null; }/* www . j a v a 2 s . c o m*/ try (ResultSet rs = stmt.getGeneratedKeys()) { List<Integer> list = new ArrayList<Integer>(); while (rs.next()) { list.add(rs.getInt(1)); } return list; } } } catch (SQLException ex) { throw new IllegalStateException("InsertQuery Failed", ex); } }
From source file:com.feedzai.commons.sql.abstraction.engine.impl.H2Engine.java
@Override protected MappedEntity createPreparedStatementForInserts(final DbEntity entity) throws DatabaseEngineException { List<String> insertInto = new ArrayList<String>(); insertInto.add("INSERT INTO"); insertInto.add(quotize(entity.getName())); List<String> insertIntoWithAutoInc = new ArrayList<String>(); insertIntoWithAutoInc.add("INSERT INTO"); insertIntoWithAutoInc.add(quotize(entity.getName())); List<String> columns = new ArrayList<String>(); List<String> values = new ArrayList<String>(); List<String> columnsWithAutoInc = new ArrayList<String>(); List<String> valuesWithAutoInc = new ArrayList<String>(); for (DbColumn column : entity.getColumns()) { columnsWithAutoInc.add(quotize(column.getName())); valuesWithAutoInc.add("?"); if (!column.isAutoInc()) { columns.add(quotize(column.getName())); values.add("?"); }//from www .j a va 2s. co m } insertInto.add("(" + join(columns, ", ") + ")"); insertInto.add("VALUES (" + join(values, ", ") + ")"); insertIntoWithAutoInc.add("(" + join(columnsWithAutoInc, ", ") + ")"); insertIntoWithAutoInc.add("VALUES (" + join(valuesWithAutoInc, ", ") + ")"); final String statement = join(insertInto, " "); final String statementWithAutoInt = join(insertIntoWithAutoInc, " "); logger.trace(statement); PreparedStatement ps, psWithAutoInc; try { ps = conn.prepareStatement(statement, Statement.RETURN_GENERATED_KEYS); psWithAutoInc = conn.prepareStatement(statementWithAutoInt); return new MappedEntity().setInsert(ps).setInsertWithAutoInc(psWithAutoInc); } catch (SQLException ex) { throw new DatabaseEngineException("Something went wrong handling statement", ex); } }
From source file:com.logger.TrackServlet.java
private String getOrganizationId(JSONArray org) { String id = "1"; String name = ""; String address = ""; String city = ""; String stateProv = ""; String postalCode = ""; String country = ""; for (int j = 0; j < org.length(); j++) { try {/*from w w w . j av a 2 s. c o m*/ JSONObject temp = org.getJSONObject(j); if (String.valueOf(temp.get("key")).equalsIgnoreCase("Organization") || String.valueOf(temp.get("key")).equalsIgnoreCase("OrgName") || String.valueOf(temp.get("key")).equalsIgnoreCase("Customer") || String.valueOf(temp.get("key")).equalsIgnoreCase("CustName") || String.valueOf(temp.get("key")).equalsIgnoreCase("owner") || String.valueOf(temp.get("key")).equalsIgnoreCase("descr")) { name = String.valueOf(temp.get("value")); } if (String.valueOf(temp.get("key")).equalsIgnoreCase("Address")) { address = String.valueOf(temp.get("value")); } if (String.valueOf(temp.get("key")).equalsIgnoreCase("City")) { city = String.valueOf(temp.get("value")); } if (String.valueOf(temp.get("key")).equalsIgnoreCase("StateProv")) { stateProv = String.valueOf(temp.get("value")); } if (String.valueOf(temp.get("key")).equalsIgnoreCase("PostalCode")) { postalCode = String.valueOf(temp.get("value")); } if (String.valueOf(temp.get("key")).equalsIgnoreCase("Country")) { country = String.valueOf(temp.get("value")); } } catch (JSONException ex) { Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex); } } java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("SELECT Id FROM LeadOrganization WHERE Name = ?"); stmt.setString(1, name); rs = stmt.executeQuery(); while (rs.next()) { id = rs.getString("Id"); } if (!name.isEmpty() && id.equals("1")) { stmt = con.prepareStatement( "INSERT INTO [dbo].[LeadOrganization] ([Name],[Address],[City],[StateProv],[PostalCode],[Country]) VALUES (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); stmt.setString(1, name); stmt.setString(2, address); stmt.setString(3, city); stmt.setString(4, stateProv); stmt.setString(5, postalCode); stmt.setString(6, country); stmt.execute(); rs = stmt.getGeneratedKeys(); while (rs.next()) { id = rs.getString(1); } } con.close(); } catch (IOException | SQLException | PropertyVetoException ex) { Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } return id; }
From source file:com.feedzai.commons.sql.abstraction.engine.impl.MySqlEngine.java
@Override protected MappedEntity createPreparedStatementForInserts(final DbEntity entity) throws DatabaseEngineException { List<String> insertInto = new ArrayList<String>(); insertInto.add("INSERT INTO"); insertInto.add(quotize(entity.getName(), escapeCharacter())); List<String> insertIntoWithAutoInc = new ArrayList<String>(); insertIntoWithAutoInc.add("INSERT INTO"); insertIntoWithAutoInc.add(quotize(entity.getName(), escapeCharacter())); List<String> columns = new ArrayList<String>(); List<String> values = new ArrayList<String>(); List<String> columnsWithAutoInc = new ArrayList<String>(); List<String> valuesWithAutoInc = new ArrayList<String>(); for (DbColumn column : entity.getColumns()) { columnsWithAutoInc.add(quotize(column.getName(), escapeCharacter())); valuesWithAutoInc.add("?"); if (!column.isAutoInc()) { columns.add(quotize(column.getName(), escapeCharacter())); values.add("?"); }//from ww w .jav a2 s .c o m } insertInto.add("(" + join(columns, ", ") + ")"); insertInto.add("VALUES (" + join(values, ", ") + ")"); insertIntoWithAutoInc.add("(" + join(columnsWithAutoInc, ", ") + ")"); insertIntoWithAutoInc.add("VALUES (" + join(valuesWithAutoInc, ", ") + ")"); final String statement = join(insertInto, " "); final String statementWithAutoInt = join(insertIntoWithAutoInc, " "); logger.trace(statement); logger.trace(statementWithAutoInt); PreparedStatement ps, psWithAutoInc; try { ps = conn.prepareStatement(statement, Statement.RETURN_GENERATED_KEYS); psWithAutoInc = conn.prepareStatement(statementWithAutoInt); return new MappedEntity().setInsert(ps).setInsertWithAutoInc(psWithAutoInc); } catch (SQLException ex) { throw new DatabaseEngineException("Something went wrong handling statement", ex); } }