List of usage examples for java.sql Statement getGeneratedKeys
ResultSet getGeneratedKeys() throws SQLException;
Statement object. From source file:org.aludratest.cloud.impl.app.LogDatabase.java
public Long executeStatement(String sql, int[] autoGenerationIndices) throws SQLException { Connection connection = getConnection(); Statement stmt = null; try {/*from w w w .ja v a 2 s .com*/ stmt = connection.createStatement(); LOG.debug("Executing database statement: " + sql); if (autoGenerationIndices != null) { stmt.execute(sql, autoGenerationIndices); } else { stmt.execute(sql); } LOG.debug("Statement execution complete."); ResultSet rs = stmt.getGeneratedKeys(); if (rs == null || !rs.next()) { return null; } try { return rs.getLong(1); } catch (SQLException e) { return null; } } finally { closeQuietly(stmt); closeQuietly(connection); } }
From source file:com.mirth.connect.server.userutil.DatabaseConnection.java
/** * Executes an INSERT/UPDATE statement on the database and returns a CachedRowSet containing any * generated keys.//from w ww.j av a 2 s.co m * * @param expression * The statement to be executed. * @return A CachedRowSet containing any generated keys. * @throws SQLException */ public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression) throws SQLException { Statement statement = null; try { statement = connection.createStatement(); logger.debug("executing update:\n" + expression); statement.executeUpdate(expression, Statement.RETURN_GENERATED_KEYS); CachedRowSet crs = new MirthCachedRowSet(); crs.populate(statement.getGeneratedKeys()); return crs; } catch (SQLException e) { throw e; } finally { DbUtils.closeQuietly(statement); } }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testUUIDGeneratedKeys() throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST_UUID(id UUID DEFAULT random_UUID() PRIMARY KEY)"); stat.execute("INSERT INTO TEST_UUID() VALUES()"); ResultSet rs = stat.getGeneratedKeys(); rs.next();/*from ww w . j ava 2s . com*/ byte[] data = rs.getBytes(1); assertEquals(16, data.length); stat.execute("INSERT INTO TEST_UUID VALUES(random_UUID())"); rs = stat.getGeneratedKeys(); assertFalse(rs.next()); stat.execute("DROP TABLE TEST_UUID"); }
From source file:com.emr.utilities.CSVLoader.java
/** * Parse CSV file using OpenCSV library and load in * given database table. /*from w ww. j a va 2 s .c o m*/ * @param csvFile {@link String} Input CSV file * @param tableName {@link String} Database table name to import data * @param truncateBeforeLoad {@link boolean} Truncate the table before inserting * new records. * @param destinationColumns {@link String[]} Array containing the destination columns */ public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad, String[] destinationColumns, List columnsToBeMapped) throws Exception { CSVReader csvReader = null; if (null == this.connection) { throw new Exception("Not a valid connection."); } try { csvReader = new CSVReader(new FileReader(csvFile), this.seprator); } catch (Exception e) { String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e); JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace, "File Error", JOptionPane.ERROR_MESSAGE); throw new Exception("Error occured while executing file. " + stacktrace); } String[] headerRow = csvReader.readNext(); if (null == headerRow) { throw new FileNotFoundException( "No columns defined in given CSV file." + "Please check the CSV file format."); } //Get indices of columns to be mapped List mapColumnsIndices = new ArrayList(); for (Object o : columnsToBeMapped) { String column = (String) o; column = column.substring(column.lastIndexOf(".") + 1, column.length()); int i; for (i = 0; i < headerRow.length; i++) { if (headerRow[i].equals(column)) { mapColumnsIndices.add(i); } } } String questionmarks = StringUtils.repeat("?,", headerRow.length); questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1); String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName); query = query.replaceFirst(KEYS_REGEX, StringUtils.join(destinationColumns, ",")); query = query.replaceFirst(VALUES_REGEX, questionmarks); String log_query = query.substring(0, query.indexOf("VALUES(")); String[] nextLine; Connection con = null; PreparedStatement ps = null; PreparedStatement ps2 = null; PreparedStatement reader = null; ResultSet rs = null; try { con = this.connection; con.setAutoCommit(false); ps = con.prepareStatement(query); File file = new File("sqlite/db"); if (!file.exists()) { file.createNewFile(); } db = new SQLiteConnection(file); db.open(true); //if destination table==person, also add an entry in the table person_identifier //get column indices for the person_id and uuid columns int person_id_column_index = -1; int uuid_column_index = -1; int maxLength = 100; int firstname_index = -1; int middlename_index = -1; int lastname_index = -1; int clanname_index = -1; int othername_index = -1; if (tableName.equals("person")) { int i; ps2 = con.prepareStatement( "insert ignore into person_identifier(person_id,identifier_type_id,identifier) values(?,?,?)"); for (i = 0; i < headerRow.length; i++) { if (headerRow[i].equals("person_id")) { person_id_column_index = i; } if (headerRow[i].equals("uuid")) { uuid_column_index = i; } /*if(headerRow[i].equals("first_name")){ System.out.println("Found firstname index: " + i); firstname_index=i; } if(headerRow[i].equals("middle_name")){ System.out.println("Found firstname index: " + i); middlename_index=i; } if(headerRow[i].equals("last_name")){ System.out.println("Found firstname index: " + i); lastname_index=i; } if(headerRow[i].equals("clan_name")){ System.out.println("Found firstname index: " + i); clanname_index=i; } if(headerRow[i].equals("other_name")){ System.out.println("Found firstname index: " + i); othername_index=i; }*/ } } if (truncateBeforeLoad) { //delete data from table before loading csv try (Statement stmnt = con.createStatement()) { stmnt.execute("DELETE FROM " + tableName); stmnt.close(); } } if (tableName.equals("person")) { try (Statement stmt2 = con.createStatement()) { stmt2.execute( "ALTER TABLE person CHANGE COLUMN first_name first_name VARCHAR(50) NULL DEFAULT NULL AFTER person_guid,CHANGE COLUMN middle_name middle_name VARCHAR(50) NULL DEFAULT NULL AFTER first_name,CHANGE COLUMN last_name last_name VARCHAR(50) NULL DEFAULT NULL AFTER middle_name;"); stmt2.close(); } } final int batchSize = 1000; int count = 0; Date date = null; while ((nextLine = csvReader.readNext()) != null) { if (null != nextLine) { int index = 1; int person_id = -1; String uuid = ""; int identifier_type_id = 3; if (tableName.equals("person")) { reader = con.prepareStatement( "select identifier_type_id from identifier_type where identifier_type_name='UUID'"); rs = reader.executeQuery(); if (!rs.isBeforeFirst()) { //no uuid row //insert it Integer numero = 0; Statement stmt = con.createStatement(); numero = stmt.executeUpdate( "insert into identifier_type(identifier_type_id,identifier_type_name) values(50,'UUID')", Statement.RETURN_GENERATED_KEYS); ResultSet rs2 = stmt.getGeneratedKeys(); if (rs2.next()) { identifier_type_id = rs2.getInt(1); } rs2.close(); stmt.close(); } else { while (rs.next()) { identifier_type_id = rs.getInt("identifier_type_id"); } } } int counter = 1; String temp_log = log_query + "VALUES("; //string to be logged for (String string : nextLine) { //if current index is in the list of columns to be mapped, we apply that mapping for (Object o : mapColumnsIndices) { int i = (int) o; if (index == (i + 1)) { //apply mapping to this column string = applyDataMapping(string); } } if (tableName.equals("person")) { //get person_id and uuid if (index == (person_id_column_index + 1)) { person_id = Integer.parseInt(string); } if (index == (uuid_column_index + 1)) { uuid = string; } } //check if string is a date if (string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4} \\d{2}:\\d{2}:\\d{2}") || string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4}")) { java.sql.Date dt = formatDate(string); temp_log = temp_log + "'" + dt.toString() + "'"; ps.setDate(index++, dt); } else { if ("".equals(string)) { temp_log = temp_log + "''"; ps.setNull(index++, Types.NULL); } else { temp_log = temp_log + "'" + string + "'"; ps.setString(index++, string); } } if (counter < headerRow.length) { temp_log = temp_log + ","; } else { temp_log = temp_log + ");"; System.out.println(temp_log); } counter++; } if (tableName.equals("person")) { if (!"".equals(uuid) && person_id != -1) { ps2.setInt(1, person_id); ps2.setInt(2, identifier_type_id); ps2.setString(3, uuid); ps2.addBatch(); } } ps.addBatch(); } if (++count % batchSize == 0) { ps.executeBatch(); if (tableName.equals("person")) { ps2.executeBatch(); } } } ps.executeBatch(); // insert remaining records if (tableName.equals("person")) { ps2.executeBatch(); } con.commit(); } catch (Exception e) { if (con != null) con.rollback(); if (db != null) db.dispose(); String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e); JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace, "File Error", JOptionPane.ERROR_MESSAGE); throw new Exception("Error occured while executing file. " + stacktrace); } finally { if (null != reader) reader.close(); if (null != ps) ps.close(); if (null != ps2) ps2.close(); if (null != con) con.close(); csvReader.close(); } }
From source file:org.wandora.modules.GenericDatabaseInterface.java
public int update(String query, Rows[] generatedKeys) throws SQLException { Connection connection = connectionPool.getConnection(); if (connection == null) throw new SQLException("Couldn't get connection from the connection pool"); Statement stmt = null; ResultSet rs = null;//from w w w . j a va2 s . c om try { stmt = connection.createStatement(); if (generatedKeys != null && generatedKeys.length > 0) { int ret = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS); rs = stmt.getGeneratedKeys(); generatedKeys[0] = makeRows(rs); return ret; } else { return stmt.executeUpdate(query); } } finally { if (rs != null) try { rs.close(); } catch (SQLException ignore) { } if (stmt != null) try { stmt.close(); } catch (SQLException ignore) { } if (connection != null) try { connection.close(); } catch (SQLException ignore) { } } /* int retries=connectionRetries; while(true){ try{ Statement stmt=connection.createStatement(); try{ if(generatedKeys!=null && generatedKeys.length>0){ int ret=stmt.executeUpdate(query,Statement.RETURN_GENERATED_KEYS); ResultSet rs=stmt.getGeneratedKeys(); generatedKeys[0]=makeRows(rs); return ret; } else { return stmt.executeUpdate(query); } } finally{ stmt.close(); } }catch(SQLException sqle){ retries--; if(retries<0) throw sqle; else { if(retries<connectionRetries-1) try{Thread.sleep(10000);}catch(InterruptedException ie){} reconnect(); } } } */ }
From source file:org.kawanfw.test.api.client.autogeneratedkeys.InsertStatementTestAutoKeysTest.java
/** * Do a 100 row insert inside a loop//from www .ja v a 2 s . com * * @param connection * the AceQL Connection * * @param numberToInsert * the number for instances to insert * * @param useRawExecute * if true, we will insert using execute() * * @throws Exception * it any Exception occurs */ public static void insertStatement(Connection connection, int numberToInsert, boolean useRawExecute, boolean autoCommitOn) throws Exception { long maxCustomerId = getMaxCustomerId(connection); MessageDisplayer.display("maxCustomerId: " + maxCustomerId); // We can now use our Remote JDBC Connection as a regular Connection! if (!autoCommitOn) { connection.setAutoCommit(false); } // We will do all our remote insert in a SQL Transaction try { Statement statement = null; String title; String fname; String lname; String addressline; String town; String zipcode; String phone; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " customers..."); statement = connection.createStatement(); // //SystemOutHandle.display("customer_id: " + customerId); title = "Sir"; fname = "Smith_" + numberToInsert; lname = "Smith_" + numberToInsert; addressline = numberToInsert + ", Csar Avenue"; town = "JavaLand_" + numberToInsert; zipcode = numberToInsert + "45"; phone = numberToInsert + "-12345678"; String sql = "insert into customer_auto (customer_title, fname, lname, addressline, town, zipcode, phone) " + " values ( '?2', '?3', '?4', '?5', '?6', '?7', '?8' )"; sql = sql.replace("?2", title); sql = sql.replace("?3", fname); sql = sql.replace("?4", lname); sql = sql.replace("?5", addressline); sql = sql.replace("?6", town); sql = sql.replace("?7", zipcode); sql = sql.replace("?8", phone); if (useRawExecute) { statement.execute(sql, Statement.RETURN_GENERATED_KEYS); } else { statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); } // We do either everything in a single transaction or nothing if (!autoCommitOn) { connection.commit(); // Commit is propagated on Server } ResultSet keys = statement.getGeneratedKeys(); long lastKey = -1; while (keys.next()) { lastKey = keys.getLong(1); } keys.close(); MessageDisplayer.display("Last Key: " + lastKey); // Don't know why: there is a bug in some engines where sometime // increment is > 1 // Assert.assertEquals("last key = maxCustomerId + 1", lastKey, // maxCustomerId + 1); // So do another test: Assert.assertEquals("last key >= 1", true, lastKey > 1); statement.close(); } catch (Exception e) { if (!autoCommitOn) { connection.rollback(); } throw e; } finally { if (!autoCommitOn) { connection.setAutoCommit(true); } } }
From source file:edu.ku.brc.specify.config.FixDBAfterLogin.java
/** * @param disciplineId//w w w .j a v a 2 s . co m * @return * @throws SQLException */ public static Integer addSymbiotaExportSchema(Integer disciplineId) throws SQLException { Statement stmt = DBConnection.getInstance().getConnection().createStatement(); Integer schemaId = null; try { String sql = "insert into spexportschema(TimestampCreated,Version,Description,SchemaName,SchemaVersion,DisciplineID,CreatedByAgentID) " + "values(now(),0,'http://rs.tdwg.org/dwc/terms','SymbiotaDwc','1.0'," + disciplineId + "," + AppContextMgr.getInstance().getClassObject(SpecifyUser.class).getId() + ")"; stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet key = stmt.getGeneratedKeys(); key.next(); schemaId = key.getInt(1); Integer spuId = AppContextMgr.getInstance().getClassObject(SpecifyUser.class).getId(); //XXX This is so stoopid. insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','created'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','description'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identifier'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','language'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','license'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','modified'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','publisher'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','references'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','title'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','accessRights'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','associatedTaxa'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','basisOfRecord'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','catalogNumber'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','collectionCode'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:double','coordinateUncertaintyInMeters'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','country'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','county'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dateTimeISO','dateIdentified'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:gDay','day'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:decimalLatitudeDataType','decimalLatitude'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:decimalLongitudeDataType','decimalLongitude'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','disposition'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','dynamicProperties'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dayOfYearDataType','endDayOfYear'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','establishmentMeans'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dateTimeISO','eventDate'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','family'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','fieldNotes'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','fieldNumber'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','footprintWKT'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','genus'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','geodeticDatum'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceProtocol'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceSources'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceVerificationStatus'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferencedBy'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','habitat'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:nonEmptyString','identificationID'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identificationQualifier'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identificationReferences'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identificationRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identifiedBy'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:positiveInteger','individualCount'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','informationWithheld'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','infraspecificEpithet'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','institutionCode'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','lifeStage'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','locality'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:nonEmptyString','locationID'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:double','maximumElevationInMeters'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:double','minimumElevationInMeters'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:gMonth','month'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','municipality'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','occurrenceRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','otherCatalogNumbers'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','ownerInstitutionCode'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','preparations'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','recordNumber'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','recordedBy'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','reproductiveCondition'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','rights'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','rightsHolder'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','samplingProtocol'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','scientificName'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','scientificNameAuthorship'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','sex'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','specificEpithet'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dayOfYearDataType','startDayOfYear'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','stateProvince'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','taxonRank'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','taxonRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','typeStatus'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','verbatimCoordinates'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','verbatimElevation'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','verbatimEventDate'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:gYear','year'," + schemaId + "," + spuId + ")"); return schemaId; } catch (SQLException e) { if (schemaId != null) { stmt.executeUpdate("DELETE FROM spexportschemaitem WHERE SpExportSchemaID=" + schemaId); stmt.executeUpdate("DELETE FROM spexportschema WHERE SpExportSchemaID=" + schemaId); } throw e; } finally { stmt.close(); } }
From source file:org.efaps.db.databases.MySQLDatabase.java
/** * Fetches next number for sequence <code>_name</code> by inserting new * row into representing table. The new auto generated key is returned as * next number of the sequence.//from w w w . j a v a 2 s .c o m * * @param _con SQL connection * @param _name name of the sequence * @return current inserted value of the table * @throws SQLException if next number from the sequence could not be * fetched * @see #PREFIX_SEQUENCE */ @Override public long nextSequence(final Connection _con, final String _name) throws SQLException { final long ret; final Statement stmt = _con.createStatement(); try { // insert new line final String insertCmd = new StringBuilder().append("INSERT INTO `") .append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase()).append("` VALUES ()") .toString(); final int row = stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS); if (row != 1) { throw new SQLException("no sequence found for '" + _name + "'"); } // fetch new number final ResultSet resultset = stmt.getGeneratedKeys(); if (resultset.next()) { ret = resultset.getLong(1); } else { throw new SQLException("no sequence found for '" + _name + "'"); } } finally { stmt.close(); } return ret; }
From source file:org.openhab.persistence.sql.internal.SqlPersistenceService.java
private String getTable(Item item) { Statement statement = null; String sqlCmd = null;// w w w . ja v a 2s . c om int rowId = 0; String itemName = item.getName(); String tableName = sqlTables.get(itemName); // Table already exists - return the name if (tableName != null) return tableName; // Create a new entry in the Items table. This is the translation of // item name to table try { sqlCmd = new String("INSERT INTO Items (ItemName) VALUES ('" + itemName + "')"); statement = connection.createStatement(); statement.executeUpdate(sqlCmd, Statement.RETURN_GENERATED_KEYS); ResultSet resultSet = statement.getGeneratedKeys(); if (resultSet != null && resultSet.next()) { rowId = resultSet.getInt(1); } if (rowId == 0) { throw new SQLException("SQL: Creating table for item '" + itemName + "' failed."); } // Create the table name tableName = new String("Item" + rowId); logger.debug("SQL: new item " + itemName + " is Item" + rowId); } catch (SQLException e) { logger.error("SQL: Could not create table for item '" + itemName + "': " + e.getMessage()); } finally { if (statement != null) { try { statement.close(); } catch (SQLException logOrIgnore) { } } } // An error occurred! if (tableName == null) return null; // Default the type to double String mysqlType = new String("DOUBLE"); String itemType = item.getClass().toString().toUpperCase(); itemType = itemType.substring(itemType.lastIndexOf('.') + 1); if (sqlTypes.get(itemType) != null) { mysqlType = sqlTypes.get(itemType); } // We have a rowId, create the table for the data sqlCmd = new String( "CREATE TABLE " + tableName + " (Time DATETIME, Value " + mysqlType + ", PRIMARY KEY(Time));"); logger.debug("SQL: " + sqlCmd); try { statement = connection.createStatement(); statement.executeUpdate(sqlCmd); logger.debug("SQL: Table created for item '" + itemName + "' with datatype " + mysqlType + " in SQL database."); sqlTables.put(itemName, tableName); } catch (Exception e) { logger.error("SQL: Could not create table for item '" + itemName + "' with statement '" + sqlCmd + "': " + e.getMessage()); } finally { if (statement != null) { try { statement.close(); } catch (Exception hidden) { } } } return tableName; }
From source file:com.mycompany.demos.Servlet3b.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from www . j a v a 2 s. c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; final String DB_URL = "jdbc:mysql://localhost:3306/garbagecollectionv2"; final String USER = "root"; final String PASS = "1234"; double existingLocationLat = 0, existingLocationLng = 0; int existingLocationId, newLocationId; //float fullness = 0; //System.out.println(request.getParameter("action")); Connection conn = null; Statement stmt = null; Statement stmt2 = null; try { //STEP 2: Register JDBC driver System.out.println("Loading Driver..."); Class.forName(JDBC_DRIVER); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); //STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); stmt2 = conn.createStatement(); String sql; if (request.getParameter("action").equals("add")) { sql = "insert into locations (lat,lng) values (" + request.getParameter("lat") + "," + request.getParameter("lng") + ");"; //System.out.println(sql); stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet rs2 = stmt.getGeneratedKeys(); rs2.next(); newLocationId = rs2.getInt(1); rs2.close(); //System.out.println(newBinId); GraphHopper graphHopper = new GraphHopper() .setGraphHopperLocation("C:\\Users\\panikas\\Desktop\\diploma") // "gh-car" .setEncodingManager(new EncodingManager("car")) // "car" .setOSMFile("europe_germany_berlin.osm") // "germany-lastest.osm.pbf" .forServer(); graphHopper.importOrLoad(); GHRequest Grequest = null; GHResponse route = null; sql = "SELECT * FROM locations WHERE locationId != " + newLocationId + ";"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { existingLocationLat = rs.getDouble("lat"); existingLocationLng = rs.getDouble("lng"); existingLocationId = rs.getInt("locationId"); Grequest = new GHRequest(existingLocationLat, existingLocationLng, Double.parseDouble(request.getParameter("lat")), Double.parseDouble(request.getParameter("lng"))); Grequest.setWeighting("fastest"); Grequest.setVehicle("car"); route = graphHopper.route(Grequest); try (PrintWriter out = new PrintWriter( "C:\\Users\\panikas\\Desktop\\diploma\\code\\Demos\\target\\Demos-1.0-SNAPSHOT\\locations\\route" + existingLocationId + "_" + newLocationId + ".gpx")) { out.print(route.getBest().getInstructions().createGPX("Graphhopper", new Date().getTime(), false, false, true, false)); } catch (FileNotFoundException ex) { System.out.println("exception filenotfound"); } sql = "insert into distances (originId,destinationId,distance) values (" + existingLocationId + "," + newLocationId + "," + route.getBest().getTime() + ");"; stmt2.executeUpdate(sql); Grequest = new GHRequest(Double.parseDouble(request.getParameter("lat")), Double.parseDouble(request.getParameter("lng")), existingLocationLat, existingLocationLng); Grequest.setWeighting("fastest"); Grequest.setVehicle("car"); route = graphHopper.route(Grequest); try (PrintWriter out = new PrintWriter( "C:\\Users\\panikas\\Desktop\\diploma\\code\\Demos\\target\\Demos-1.0-SNAPSHOT\\locations\\route" + newLocationId + "_" + existingLocationId + ".gpx")) { out.print(route.getBest().getInstructions().createGPX("Graphhopper", new Date().getTime(), false, false, true, false)); } catch (FileNotFoundException ex) { System.out.println("exception filenotfound"); } sql = "insert into distances (originId,destinationId,distance) values (" + newLocationId + "," + existingLocationId + "," + route.getBest().getTime() + ");"; stmt2.executeUpdate(sql); } if (request.getParameter("type").equals("bin")) { sql = "insert into bins (locationId,fullness) values (" + newLocationId + "," + request.getParameter("fullness") + ");"; stmt.executeUpdate(sql); } else if (request.getParameter("type").equals("depot")) { sql = "insert into depots (locationId,numOfVehicles) values (" + newLocationId + "," + request.getParameter("numOfVehicles") + ");"; stmt.executeUpdate(sql); } rs.close(); } else if (request.getParameter("action").equals("delete")) { //System.out.println(request.getParameter("id")); sql = "delete from locations where locationId=" + request.getParameter("locationId") + ";"; stmt.executeUpdate(sql); } else if (request.getParameter("action").equals("update")) { //System.out.println(request.getParameter("id")); //System.out.println(request.getParameter("fullness")); sql = "update bins set fullness=" + request.getParameter("fullness") + " where binId=" + request.getParameter("binId") + ";"; stmt.executeUpdate(sql); } else if (request.getParameter("action").equals("optimise")) { String[] colours = { "#3333ff", "#ff33cc", "#ff6600", "#cc6600", "#cccc00" }; Collection<Location> locations = new ArrayList<Location>(); //new problem builder VehicleRoutingProblem.Builder vrpBuilder = VehicleRoutingProblem.Builder.newInstance(); vrpBuilder.setFleetSize(VehicleRoutingProblem.FleetSize.FINITE); //add vehicles sql = "SELECT * FROM depots;"; ResultSet rs = stmt.executeQuery(sql); VehicleType type = VehicleTypeImpl.Builder.newInstance("garbageCollector") .addCapacityDimension(0, 1000).build(); VehicleImpl vehicle; Location currentLocation; int locationId, numOfVehicles, depotId; while (rs.next()) { locationId = rs.getInt("locationId"); numOfVehicles = rs.getInt("numOfVehicles"); depotId = rs.getInt("depotId"); currentLocation = Location.newInstance(Integer.toString(locationId)); for (int i = 0; i < numOfVehicles; i++) { vehicle = VehicleImpl.Builder.newInstance(depotId + "_" + i) .setStartLocation(currentLocation).setType(type).setReturnToDepot(true).build(); vrpBuilder.addVehicle(vehicle); } locations.add(currentLocation); } sql = "SELECT * FROM bins where fullness > 50;"; rs = stmt.executeQuery(sql); Service currentService; float fullness; while (rs.next()) { locationId = rs.getInt("locationId"); fullness = rs.getFloat("fullness"); currentLocation = Location.newInstance(Integer.toString(locationId)); currentService = Service.Builder.newInstance("service" + Integer.toString(locationId)) .addSizeDimension(0, (int) fullness).setLocation(currentLocation).build(); vrpBuilder.addJob(currentService); locations.add(currentLocation); } //distance matrix VehicleRoutingTransportCostsMatrix.Builder costMatrixBuilder = VehicleRoutingTransportCostsMatrix.Builder .newInstance(false); float distance; for (Location origin : locations) { //System.out.println(location.getId()); costMatrixBuilder.addTransportDistance(origin.getId(), origin.getId(), 0); for (Location destination : locations) { //System.out.println(origin.getId()); // System.out.println(destination.getId()); if (origin.getId() != destination.getId()) { sql = "SELECT distance FROM distances where originId = " + origin.getId() + " and destinationId = " + destination.getId() + ";"; rs = stmt.executeQuery(sql); rs.next(); distance = rs.getFloat("distance"); costMatrixBuilder.addTransportDistance(origin.getId(), destination.getId(), distance); } } } VehicleRoutingTransportCosts costMatrix = costMatrixBuilder.build(); vrpBuilder.setRoutingCost(costMatrix); VehicleRoutingProblem vrp = vrpBuilder.build(); VehicleRoutingAlgorithm vra = Jsprit.createAlgorithm(vrp); Collection<VehicleRoutingProblemSolution> solutions = vra.searchSolutions(); SolutionPrinter.print(vrp, Solutions.bestOf(solutions), SolutionPrinter.Print.VERBOSE); //System.out.println("optimise"); JSONArray files, routes; JSONObject route, bins, bin, solution; routes = new JSONArray(); bins = new JSONObject(); solution = new JSONObject(); int routeCounter = 0; int position; for (VehicleRoute jroute : Solutions.bestOf(solutions).getRoutes()) { TourActivity prevAct = jroute.getStart(); route = new JSONObject(); files = new JSONArray(); position = 1; for (TourActivity act : jroute.getActivities()) { files.add("locations\\route" + prevAct.getLocation().getId() + "_" + act.getLocation().getId() + ".gpx"); bin = new JSONObject(); bin.put("colour", colours[routeCounter]); bin.put("position", position); bins.put(act.getLocation().getId(), bin); position++; prevAct = act; } files.add("locations\\route" + prevAct.getLocation().getId() + "_" + jroute.getEnd().getLocation().getId() + ".gpx"); route.put("files", files); route.put("colour", colours[routeCounter]); routes.add(route); routeCounter++; } solution.put("routes", routes); solution.put("bins", bins); System.out.println(solution.toString()); response.setContentType("application/json"); response.setCharacterEncoding("UTF-8"); response.getWriter().write(solution.toString()); } stmt.close(); stmt2.close(); conn.close(); } catch (SQLException se) { //Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { //Handle errors for Class.forName e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } } catch (SQLException se2) { } try { if (stmt2 != null) { stmt2.close(); } } catch (SQLException se3) { } try { if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } // JSONObject name = new JSONObject(); // JSONArray names = new JSONArray(); // // name.put( // "name", "foo1"); // name.put( // "surname", "bar1"); // // names.add(name); // name = new JSONObject(); // // name.put( // "name", "foo2"); // name.put( // "surname", "bar2"); //// names.add(name); // response.setContentType( // "application/json"); // response.setCharacterEncoding( // "UTF-8"); // response.getWriter() // .write(name.toString()); // System.out.println("done"); //System.out.println(names.toString()); //System.out.println(request.getParameter("action")); //System.out.println("blah"); }