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:de.static_interface.sinksql.SqlDatabase.java
@Override public <T extends Row> T insert(AbstractTable<T> abstractTable, T row) { Validate.notNull(row);// w ww . j a v a 2 s . co m String columns = ""; char bt = getBacktick(); int i = 0; List<Field> fields = ReflectionUtil.getAllFields(abstractTable.getRowClass()); Map<Field, String> autoIncrements = new HashMap<>(); for (Field f : fields) { Column column = FieldCache.getAnnotation(f, Column.class); if (column == null) { continue; } String name = StringUtil.isEmptyOrNull(column.name()) ? f.getName() : column.name(); if (column.autoIncrement()) { autoIncrements.put(f, name); } name = bt + name + bt; if (i == 0) { columns = name; i++; continue; } columns += ", " + name; i++; } if (i == 0) { throw new IllegalStateException( abstractTable.getRowClass().getName() + " doesn't have any public fields!"); } String valuesPlaceholders = ""; for (int k = 0; k < i; k++) { if (k == 0) { valuesPlaceholders = "?"; continue; } valuesPlaceholders += ",?"; } String sql = "INSERT INTO `{TABLE}` (" + columns + ") " + "VALUES(" + valuesPlaceholders + ")"; List<Object> values = new ArrayList<>(); for (Field f : fields) { try { values.add(f.get(row)); } catch (IllegalAccessException e) { throw new RuntimeException(e); } } PreparedStatement ps = abstractTable.createPreparedStatement(sql, Statement.RETURN_GENERATED_KEYS, values.toArray(new Object[values.size()])); try { ps.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } ResultSet rs; try { rs = ps.getGeneratedKeys(); } catch (SQLException e) { throw new RuntimeException(e); } try { rs.next(); } catch (SQLException e) { e.printStackTrace(); } for (Field f : autoIncrements.keySet()) { abstractTable.setFieldFromResultSet(row, rs, f, autoIncrements.get(f)); } try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } return row; }
From source file:org.geowebcache.storage.jdbc.jobstore.JDBCJobWrapper.java
public void putJobLog(JobLogObject stObj) throws SQLException, StorageException { // Not really a fan of tacking log_ onto the front of every field, but it ensures // common keywords like log, level, time, summary, text won't clash with database // keywords, causing unnecessary pain. String query = "MERGE INTO " + "JOB_LOGS(job_log_id, job_id, log_level, log_time, log_summary, log_text) " + "KEY(job_log_id) " + "VALUES(?,?,?,?,?,?)"; final Connection conn = getConnection(); try {//from ww w . ja v a 2s . com Long insertId; PreparedStatement prep = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); try { if (stObj.getJobLogId() == -1) { prep.setNull(1, java.sql.Types.BIGINT); } else { prep.setLong(1, stObj.getJobLogId()); } prep.setLong(2, stObj.getJobId()); prep.setString(3, stObj.getLogLevel().name()); prep.setTimestamp(4, stObj.getLogTime()); prep.setString(5, stObj.getLogSummary()); Reader reader = (Reader) new BufferedReader(new StringReader(stObj.getLogText())); prep.setCharacterStream(6, reader, stObj.getLogText().length()); insertId = wrappedInsert(prep); } finally { close(prep); } if (insertId == null) { log.error("Did not receive an id for " + query); } else { if (stObj.getJobLogId() == -1) { // only use the inserted id if we were doing an insert. // what insertid will be if we weren't doing an insert is not defined. stObj.setJobLogId(insertId.longValue()); } } } finally { conn.close(); } }
From source file:edu.ku.brc.specify.conversion.AgentConverter.java
/** * Specify 5.x points at AgentAdress instead of an Agent. The idea was that to point at an Agent * and possibly a differnt address that represents what that person does. This was really * confusing so we are changing it to point at an Agent instead. * //w w w. j a va 2s . c o m * So that means we need to pull apart these relationships and have all foreign keys that point * to an AgentAddress now point at an Agent and we then need to add in the Agents and then add * the Address to the Agents. * * The AgentAdress, Agent and Address (triple) can have a NULL Address but it cannot have a NULL * Agent. If there is a NULL Agent then this method will throw a RuntimeException. */ public boolean convertAgents(final boolean doFixAgents) { boolean debugAgents = false; log.debug("convert Agents"); BasicSQLUtils.removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); // Create the mappers here, but fill them in during the AgentAddress Process IdTableMapper agentIDMapper = idMapperMgr.addTableMapper("agent", "AgentID"); IdTableMapper agentAddrIDMapper = idMapperMgr.addTableMapper("agentaddress", "AgentAddressID"); agentIDMapper.setInitialIndex(4); if (shouldCreateMapTables) { log.info("Mapping Agent Ids"); agentIDMapper.mapAllIds("SELECT AgentID FROM agent ORDER BY AgentID"); } // Just like in the conversion of the CollectionObjects we // need to build up our own SELECT clause because the MetaData of columns names returned // FROM // a query doesn't include the table names for all columns, this is far more predictable List<String> oldFieldNames = new ArrayList<String>(); StringBuilder agtAdrSQL = new StringBuilder("SELECT "); List<String> agentAddrFieldNames = getFieldNamesFromSchema(oldDBConn, "agentaddress"); agtAdrSQL.append(buildSelectFieldList(agentAddrFieldNames, "agentaddress")); agtAdrSQL.append(", "); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentAddrFieldNames, "agentaddress"); List<String> agentFieldNames = getFieldNamesFromSchema(oldDBConn, "agent"); agtAdrSQL.append(buildSelectFieldList(agentFieldNames, "agent")); log.debug("MAIN: " + agtAdrSQL); agtAdrSQL.append(", "); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentFieldNames, "agent"); List<String> addrFieldNames = getFieldNamesFromSchema(oldDBConn, "address"); log.debug(agtAdrSQL); agtAdrSQL.append(buildSelectFieldList(addrFieldNames, "address")); GenericDBConversion.addNamesWithTableName(oldFieldNames, addrFieldNames, "address"); // Create a Map FROM the full table/fieldname to the index in the resultset (start at 1 not zero) HashMap<String, Integer> indexFromNameMap = new HashMap<String, Integer>(); agtAdrSQL.append( " FROM agent INNER JOIN agentaddress ON agentaddress.AgentID = agent.AgentID INNER JOIN address ON agentaddress.AddressID = address.AddressID Order By agentaddress.AgentAddressID Asc"); // These represent the New columns of Agent Table // So the order of the names are for the new table // the names reference the old table String[] agentColumns = { "agent.AgentID", "agent.TimestampModified", "agent.AgentType", "agentaddress.JobTitle", "agent.FirstName", "agent.LastName", "agent.MiddleInitial", "agent.Title", "agent.Interests", "agent.Abbreviation", "agentaddress.Email", "agentaddress.URL", "agent.Remarks", "agent.TimestampCreated", // User/Security changes "agent.ParentOrganizationID" }; HashMap<Integer, AddressInfo> addressHash = new HashMap<Integer, AddressInfo>(); // Create a HashMap to track which IDs have been handled during the conversion process try { log.info("Hashing Address Ids"); Integer agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AddressID) FROM address ORDER BY AddressID"); // So first we hash each AddressID and the value is set to 0 (false) Statement stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rsX = stmtX .executeQuery("SELECT AgentAddressID, AddressID FROM agentaddress ORDER BY AgentAddressID"); conv.setProcess(0, agentCnt); int cnt = 0; // Needed to add in case AgentAddress table wasn't used. while (rsX.next()) { int agentAddrId = rsX.getInt(1); int addrId = rsX.getInt(2); addressHash.put(addrId, new AddressInfo(agentAddrId, addrId)); if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); conv.setProcess(0, 0); // Next we hash all the Agents and set their values to 0 (false) log.info("Hashing Agent Ids"); stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(*) FROM agent ORDER BY AgentID"); rsX = stmtX.executeQuery( "SELECT AgentID, AgentType, LastName, Name, FirstName FROM agent ORDER BY AgentID"); conv.setProcess(0, agentCnt); cnt = 0; while (rsX.next()) { int agentId = rsX.getInt(1); agentHash.put(agentId, new AgentInfo(agentId, agentIDMapper.get(agentId), rsX.getByte(2), rsX.getString(3), rsX.getString(4), rsX.getString(5))); if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); conv.setProcess(0, 0); // Now we map all the Agents to their Addresses AND // All the Addresses to their Agents. // // NOTE: A single Address Record May be used by more than one Agent so // we will need to Duplicate the Address records later // log.info("Cross Mapping Agents and Addresses"); String post = " FROM agentaddress WHERE AddressID IS NOT NULL and AgentID IS NOT NULL"; agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AgentAddressID)" + post); stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String asSQL = "SELECT AgentAddressID, AgentID" + post; log.debug(asSQL); rsX = stmtX.executeQuery(asSQL); conv.setProcess(0, agentCnt); cnt = 0; // Needed to add in case AgentAddress table wasn't used. while (rsX.next()) { int agentAddrId = rsX.getInt(1); int agentId = rsX.getInt(2); // /////////////////////// // Add Address to Agent // /////////////////////// AgentInfo agentInfo = agentHash.get(agentId); if (agentInfo == null) { String msg = "The AgentID [" + agentId + "] in AgentAddress table id[" + agentAddrId + "] desn't exist"; log.error(msg); tblWriter.logError(msg); } else { agentInfo.add(agentAddrId, agentAddrId); } if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); //dumpInfo("beforeInfo.txt", addressHash); conv.setProcess(0, 0); // It OK if the address is NULL, but the Agent CANNOT be NULL log.info("Checking for null Agents"); agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AgentAddressID) FROM agentaddress a where AddressID IS NOT NULL and AgentID is null"); // If there is a Single Record With a NULL Agent this would be BAD! if (agentCnt != null && agentCnt > 0) { showError("There are " + agentCnt + " AgentAddress Records where the AgentID is null and the AddressId IS NOT NULL!"); } // //////////////////////////////////////////////////////////////////////////////// // This does the part of AgentAddress where it has both an Address AND an Agent // //////////////////////////////////////////////////////////////////////////////// log.info(agtAdrSQL.toString()); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); log.debug("AgentAddress: " + agtAdrSQL.toString()); // Create Map of column name to column index number int inx = 1; for (String fldName : oldFieldNames) { // log.info("["+fldName+"] "+inx+" ["+rsmd.getColumnName(inx)+"]"); indexFromNameMap.put(fldName, inx++); } Statement updateStatement = newDBConn.createStatement(); // Figure out certain column indexes we will need alter int agentIdInx = indexFromNameMap.get("agent.AgentID"); int agentTypeInx = indexFromNameMap.get("agent.AgentType"); int lastEditInx = indexFromNameMap.get("agent.LastEditedBy"); int nameInx = indexFromNameMap.get("agent.Name"); int lastNameInx = indexFromNameMap.get("agent.LastName"); int firstNameInx = indexFromNameMap.get("agent.FirstName"); int recordCnt = 0; ResultSet rs = stmt.executeQuery(agtAdrSQL.toString()); while (rs.next()) { int agentAddressId = rs.getInt(1); int agentId = rs.getInt(agentIdInx); String lastEditedBy = rs.getString(lastEditInx); AgentInfo agentInfo = agentHash.get(agentId); // Deal with Agent FirstName, LastName and Name] String lastName = rs.getString(lastNameInx); String name = rs.getString(nameInx); namePair.second = StringUtils.isNotEmpty(name) && StringUtils.isEmpty(lastName) ? name : lastName; namePair.first = rs.getString(firstNameInx); // Now tell the AgentAddress Mapper the New ID to the Old AgentAddressID if (shouldCreateMapTables) { agentAddrIDMapper.setShowLogErrors(false); if (debugAgents) log.info(String.format("Map - agentAddressId (Old) %d to Agent -> New ID: %d", agentAddressId, agentInfo.getNewAgentId())); if (agentAddrIDMapper.get(agentAddressId) == null) { agentAddrIDMapper.put(agentAddressId, agentInfo.getNewAgentId()); } else { log.debug(String.format("ERROR - agentAddressId %d Already mapped to New ID: %d", agentAddressId, agentInfo.getNewAgentId())); } agentAddrIDMapper.setShowLogErrors(true); } // Because of the old DB relationships we want to make sure we only add each agent // in one time // So start by checking the HashMap to see if it has already been added if (!agentInfo.wasAdded()) { agentInfo.setWasAdded(true); //agentInfo.addWrittenAddrOldId(addrInfo.getOldAddrId()); BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); // It has not been added yet so Add it StringBuilder sqlStr = new StringBuilder(); sqlStr.append("INSERT INTO agent "); sqlStr.append( "(AgentID, DivisionId, TimestampModified, AgentType, JobTitle, FirstName, LastName, MiddleInitial, "); sqlStr.append("Title, Interests, Abbreviation, Email, URL, Remarks, TimestampCreated, "); sqlStr.append("ParentOrganizationID, CreatedByAgentID, ModifiedByAgentID, Version)"); sqlStr.append(" VALUES ("); for (int i = 0; i < agentColumns.length; i++) { if (i > 0) { sqlStr.append(","); } if (i == 0) { if (debugAgents) log.info("Adding: " + agentColumns[i] + " New ID: " + agentInfo.getNewAgentId()); sqlStr.append(agentInfo.getNewAgentId()); sqlStr.append(","); sqlStr.append(conv.getCurDivisionID()); } else if (agentColumns[i].equals("agent.ParentOrganizationID")) { Object obj = rs.getObject(indexFromNameMap.get(agentColumns[i])); if (obj != null) { int oldId = rs.getInt(agentColumns[i]); Integer newID = agentIDMapper.get(oldId); if (newID == null) { log.error("Couldn't map ParentOrganizationID [" + oldId + "]"); } sqlStr.append(BasicSQLUtils.getStrValue(newID)); } else { sqlStr.append("NULL"); } } else if (agentColumns[i].equals("agent.LastName") || agentColumns[i].equals("LastName")) { int lastNameLen = 120; String lstName = namePair.second; lstName = lstName == null ? null : lstName.length() <= lastNameLen ? lstName : lstName.substring(0, lastNameLen); sqlStr.append(BasicSQLUtils.getStrValue(lstName)); } else if (agentColumns[i].equals("agent.FirstName") || agentColumns[i].equals("FirstName")) { sqlStr.append(BasicSQLUtils.getStrValue(namePair.first)); } else { inx = indexFromNameMap.get(agentColumns[i]); sqlStr.append(BasicSQLUtils.getStrValue(rs.getObject(inx))); } } sqlStr.append("," + conv.getCreatorAgentIdForAgent(lastEditedBy) + "," + conv.getModifiedByAgentIdForAgent(lastEditedBy) + ",0"); sqlStr.append(")"); try { if (debugAgents) { log.info(sqlStr.toString()); } updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement); if (newAgentId == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } //conv.addAgentDisciplineJoin(newAgentId, conv.getDisciplineId()); } catch (SQLException e) { log.error(sqlStr.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); System.exit(0); throw new RuntimeException(e); } } BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); if (recordCnt % 250 == 0) { log.info("AgentAddress Records: " + recordCnt); } recordCnt++; } // while BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); log.info("AgentAddress Records: " + recordCnt); rs.close(); stmt.close(); // //////////////////////////////////////////////////////////////////////////////// // This does the part of AgentAddress where it has JUST Agent // //////////////////////////////////////////////////////////////////////////////// log.info("******** Doing AgentAddress JUST Agent"); int newRecordsAdded = 0; StringBuilder justAgentSQL = new StringBuilder(); justAgentSQL.setLength(0); justAgentSQL.append("SELECT "); justAgentSQL.append(buildSelectFieldList(agentAddrFieldNames, "agentaddress")); justAgentSQL.append(", "); getFieldNamesFromSchema(oldDBConn, "agent", agentFieldNames); justAgentSQL.append(buildSelectFieldList(agentFieldNames, "agent")); justAgentSQL.append( " FROM agent INNER JOIN agentaddress ON agentaddress.AgentID = agent.AgentID WHERE agentaddress.AddressID IS NULL ORDER BY agentaddress.AgentAddressID ASC"); log.info(justAgentSQL.toString()); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(justAgentSQL.toString()); oldFieldNames.clear(); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentAddrFieldNames, "agentaddress"); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentFieldNames, "agent"); indexFromNameMap.clear(); inx = 1; for (String fldName : oldFieldNames) { indexFromNameMap.put(fldName, inx++); } agentIdInx = indexFromNameMap.get("agent.AgentID"); lastEditInx = indexFromNameMap.get("agent.LastEditedBy"); agentTypeInx = indexFromNameMap.get("agent.AgentType"); recordCnt = 0; while (rs.next()) { byte agentType = rs.getByte(agentTypeInx); int agentAddressId = rs.getInt(1); int agentId = rs.getInt(agentIdInx); String lastEditedBy = rs.getString(lastEditInx); AgentInfo agentInfo = agentHash.get(agentId); // Now tell the AgentAddress Mapper the New ID to the Old AgentAddressID if (shouldCreateMapTables) { agentAddrIDMapper.put(agentAddressId, agentInfo.getNewAgentId()); } recordCnt++; if (!agentInfo.wasAdded()) { agentInfo.setWasAdded(true); BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); // Create Agent StringBuilder sqlStr = new StringBuilder("INSERT INTO agent "); sqlStr.append( "(AgentID, DivisionID, TimestampModified, AgentType, JobTitle, FirstName, LastName, MiddleInitial, Title, Interests, "); sqlStr.append("Abbreviation, Email, URL, Remarks, TimestampCreated, ParentOrganizationID, "); sqlStr.append("CreatedByAgentID, ModifiedByAgentID, Version)"); sqlStr.append(" VALUES ("); for (int i = 0; i < agentColumns.length; i++) { if (i > 0) sqlStr.append(","); if (i == 0) { if (debugAgents) log.info(agentColumns[i]); sqlStr.append(agentInfo.getNewAgentId()); sqlStr.append(","); sqlStr.append(conv.getCurDivisionID()); } else if (i == lastEditInx) { // Skip the field } else if (agentColumns[i].equals("agent.LastName")) { if (debugAgents) log.info(agentColumns[i]); int srcColInx = agentType != 1 ? nameInx : lastNameInx; String lName = BasicSQLUtils.getStrValue(rs.getObject(srcColInx)); sqlStr.append(lName); } else { if (debugAgents) log.info(agentColumns[i]); inx = indexFromNameMap.get(agentColumns[i]); sqlStr.append(BasicSQLUtils.getStrValue(rs.getObject(inx))); } } sqlStr.append("," + conv.getCreatorAgentIdForAgent(lastEditedBy) + "," + conv.getModifiedByAgentIdForAgent(lastEditedBy) + ", 0"); // '0' is Version sqlStr.append(")"); try { if (debugAgents) { log.info(sqlStr.toString()); } updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement); if (newAgentId == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } newRecordsAdded++; } catch (SQLException e) { log.error(sqlStr.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } if (recordCnt % 250 == 0) { log.info("AgentAddress (Agent Only) Records: " + recordCnt); } } // while log.info("AgentAddress (Agent Only) Records: " + recordCnt + " newRecordsAdded " + newRecordsAdded); rs.close(); updateStatement.close(); conv.setProcess(0, BasicSQLUtils.getNumRecords(oldDBConn, "agent")); conv.setDesc("Adding Agents"); // Now Copy all the Agents that where part of an Agent Address Conversions stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery("SELECT AgentID FROM agent"); recordCnt = 0; while (rs.next()) { Integer agentId = rs.getInt(1); AgentInfo agentInfo = agentHash.get(agentId); if (agentInfo == null || !agentInfo.wasAdded()) { copyAgentFromOldToNew(agentId, agentIDMapper); } recordCnt++; if (recordCnt % 50 == 0) { conv.setProcess(recordCnt); } } conv.setProcess(recordCnt); BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); //------------------------------------------------------------ // Now Copy all the Agents that where missed //------------------------------------------------------------ conv.setProcess(0); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery("SELECT AgentID FROM agent"); recordCnt = 0; while (rs.next()) { Integer agentId = rs.getInt(1); Integer newId = agentIDMapper.get(agentId); if (newId != null) { Integer isThere = BasicSQLUtils.getCount(newDBConn, "SELECT COUNT(*) FROM agent WHERE AgentID = " + newId); if (isThere == null || isThere == 0) { copyAgentFromOldToNew(agentId, agentIDMapper); } } else { tblWriter.logError("Mapping missing for old Agent id[" + agentId + "]"); } recordCnt++; if (recordCnt % 50 == 0) { conv.setProcess(recordCnt); } } conv.setProcess(recordCnt); if (doFixAgents) { fixAgentsLFirstLastName(); } //---------------------------------------------------------------------------------------------------------------------------------- // Now loop through the Agents hash and write the addresses. If the address has already been written then it will need to be // duplicate in the second step. //---------------------------------------------------------------------------------------------------------------------------------- StringBuilder sqlStr1 = new StringBuilder("INSERT INTO address "); sqlStr1.append( "(TimestampModified, Address, Address2, City, State, Country, PostalCode, Remarks, TimestampCreated, "); sqlStr1.append( "IsPrimary, IsCurrent, Phone1, Phone2, Fax, RoomOrBuilding, PositionHeld, AgentID, CreatedByAgentID, ModifiedByAgentID, Version, Ordinal)"); sqlStr1.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); PreparedStatement pStmt = newDBConn.prepareStatement(sqlStr1.toString(), Statement.RETURN_GENERATED_KEYS); // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 String addrSQL = "SELECT a.TimestampModified, a.Address, a.City, a.State, a.Country, a.Postalcode, a.Remarks, a.TimestampCreated, aa.Phone1, aa.Phone2, aa.Fax, aa.RoomOrBuilding , aa.IsCurrent, a.LastEditedBy, aa.JobTitle " + "FROM address AS a " + "INNER JOIN agentaddress AS aa ON a.AddressID = aa.AddressID WHERE aa.AgentAddressID = %d"; BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); int fixCnt = 0; for (AgentInfo agentInfo : agentHash.values()) { HashMap<Integer, Integer> addrs = agentInfo.getAddrs(); for (Integer oldAgentAddrId : addrs.keySet()) { String adrSQL = String.format(addrSQL, oldAgentAddrId); rs = stmt.executeQuery(adrSQL); if (!rs.next()) { rs.close(); continue; } String lastEditedBy = rs.getString(14); String posHeld = rs.getString(15); if (posHeld != null && posHeld.length() > 32) { posHeld = posHeld.substring(0, 32); } String addr1 = rs.getString(2); String addr2 = null; if (addr1 != null && addr1.length() > 255) { addr1 = addr1.substring(0, 255); addr2 = addr1.substring(255); } pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setString(2, addr1); pStmt.setString(3, addr2); // Address 2 pStmt.setString(4, rs.getString(3)); pStmt.setString(5, rs.getString(4)); pStmt.setString(6, rs.getString(5)); pStmt.setString(7, rs.getString(6)); pStmt.setString(8, rs.getString(7)); pStmt.setTimestamp(9, rs.getTimestamp(8)); pStmt.setBoolean(10, rs.getByte(13) != 0); pStmt.setBoolean(11, rs.getByte(13) != 0); pStmt.setString(12, rs.getString(9)); pStmt.setString(13, rs.getString(10)); pStmt.setString(14, rs.getString(11)); pStmt.setString(15, rs.getString(12)); pStmt.setString(16, posHeld); pStmt.setInt(17, agentInfo.getNewAgentId()); pStmt.setInt(18, conv.getCreatorAgentIdForAgent(lastEditedBy)); pStmt.setInt(19, conv.getModifiedByAgentIdForAgent(lastEditedBy)); pStmt.setInt(20, 0); pStmt.setInt(21, agentInfo.addrOrd); Integer newID = BasicSQLUtils.getInsertedId(pStmt); log.debug(String.format("Saved New Id %d", newID)); //agentInfo.addWrittenAddrOldId(addrInfo.getOldAddrId()); agentInfo.addrOrd++; rs.close(); try { if (debugAgents) { log.info(sqlStr1.toString()); } if (pStmt.executeUpdate() != 1) { log.error("Error inserting address.)"); } //addrInfo.setWasAdded(true); } catch (SQLException e) { log.error(sqlStr1.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } } log.info(String.format("Added %d new Addresses", fixCnt)); pStmt.close(); //------------------------------------------------------------------ // Step #2 - Now duplicate the addresses for the agents that had // already been written to the database //------------------------------------------------------------------ /*fixCnt = 0; for (AgentInfo agentInfo : agentHash.values()) { for (Integer oldAgentAddrId : agentInfo.getUnwrittenOldAddrIds()) { Integer oldAddrId = agentInfo.getAddrs().get(oldAgentAddrId); //AddressInfo addrInfo = addressHash.get(oldAddrId); System.out.println(String.format("%d %d", oldAgentAddrId, oldAddrId)); //duplicateAddress(newDBConn, addrInfo.getOldAddrId(), addrInfo.getNewAddrId(), agentInfo.getNewAgentId()); } } log.info(String.format("Duplicated %d new Addresses", fixCnt)); */ //---------------------------------------------------------------------------------------------------------------------------------- // Now loop through the Agents hash and write the addresses. If the address has already been written then it will need to be // duplicate in the second step. //---------------------------------------------------------------------------------------------------------------------------------- /*BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); sqlStr1 = new StringBuilder("INSERT INTO address "); sqlStr1.append("(TimestampModified, Address, Address2, City, State, Country, PostalCode, Remarks, TimestampCreated, "); sqlStr1.append("IsPrimary, IsCurrent, Phone1, Phone2, Fax, RoomOrBuilding, AgentID, CreatedByAgentID, ModifiedByAgentID, Version, Ordinal)"); sqlStr1.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); pStmt = newDBConn.prepareStatement(sqlStr1.toString()); // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 String addrOnlySQL = "SELECT aa.TimestampModified, a.Address, a.City, a.State, a.Country, a.Postalcode, a.Remarks, aa.TimestampCreated, aa.Phone1, aa.Phone2, aa.Fax, aa.RoomOrBuilding , aa.IsCurrent, a.LastEditedBy, aa.AgentID " + "FROM agentaddress AS aa " + "LEFT JOIN address AS a ON a.AddressID = aa.AddressID " + "WHERE a.addressID IS NULL AND aa.AgentID IS NOT NULL"; fixCnt = 0; rs = stmt.executeQuery(addrOnlySQL); while (rs.next()) { int agentId = rs.getInt(15); int newAgentId = agentIDMapper.get(agentId); String lastEditedBy = rs.getString(14); pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setString(2, rs.getString(2)); pStmt.setString(3, null); // Address 2 pStmt.setString(4, rs.getString(3)); pStmt.setString(5, rs.getString(4)); pStmt.setString(6, rs.getString(5)); pStmt.setString(7, rs.getString(6)); pStmt.setString(8, rs.getString(7)); pStmt.setTimestamp(9, rs.getTimestamp(8)); pStmt.setBoolean(10, rs.getByte(13) != 0); pStmt.setBoolean(11, rs.getByte(13) != 0); pStmt.setString(12, rs.getString(9)); pStmt.setString(13, rs.getString(10)); pStmt.setString(14, rs.getString(11)); pStmt.setString(15, rs.getString(12)); pStmt.setInt(16, newAgentId); pStmt.setInt(17, conv.getCreatorAgentIdForAgent(lastEditedBy)); pStmt.setInt(18, conv.getModifiedByAgentIdForAgent(lastEditedBy)); pStmt.setInt(19, 0); pStmt.setInt(20, 1); try { if (debugAgents) { log.info(sqlStr1.toString()); } if (pStmt.executeUpdate() != 1) { log.error("Error inserting address.)"); } else { fixCnt++; } } catch (SQLException e) { log.error(sqlStr1.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } rs.close(); log.info(String.format("Added %d new Addresses", fixCnt)); pStmt.close();*/ stmt.close(); //dumpInfo("afterInfo.txt", addressHash); BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); return true; } catch (SQLException ex) { log.error(ex); ex.printStackTrace(); System.exit(0); throw new RuntimeException(ex); } }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId, String userType, File file) throws Exception { logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId + " userType ::" + userType); QueryRunner qrun = new QueryRunner(getDataSource()); Connection conn = getConnection(); conn.setAutoCommit(false);/* w w w .ja v a 2 s .c om*/ PreparedStatement pstmt = null; ResultSet rs = null; try { StringBuilder sbQuery = new StringBuilder( "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,"); sbQuery.append( " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID, "); sbQuery.append( " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN, "); sbQuery.append( " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09, "); if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) { sbQuery.append(" IEP_ID, STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) { sbQuery.append(" MRV_ID, STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) { sbQuery.append(" FO_ID, STATUS "); } sbQuery.append( " ,CREATED_DATE ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); //get Additional Details from GIER DB GIERInfoDetails gierInfo = null; gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber()); System.out.println("Before UIIA Datasource"); UIIAInfoDetails uiiaInfo = null; uiiaInfo = getUIIAdetailsforDVIR(eb); //logger.info("gierInfo ::"+gierInfo); pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, eb.getIsaheader()); pstmt.setString(2, eb.getGsHeader()); pstmt.setString(3, eb.getSenderId()); pstmt.setString(4, userType); pstmt.setString(5, eb.getIsaDateTime()); pstmt.setString(6, eb.getGsControl()); pstmt.setString(7, eb.getStControl()); pstmt.setString(8, eb.getInspDate()); pstmt.setString(9, eb.getInspTime()); pstmt.setString(10, eb.getInspTimeZone()); pstmt.setString(11, eb.getEqpInitial()); pstmt.setString(12, eb.getEqpNumber()); pstmt.setString(13, eb.getChassisId()); pstmt.setString(14, gierInfo.getCompanySCACCode()); pstmt.setString(15, eb.getPortQualifier()); pstmt.setString(16, eb.getPortIdentifier()); pstmt.setString(17, eb.getDrvState()); pstmt.setString(18, eb.getDrvLicNo()); pstmt.setString(19, uiiaInfo.getDrvName()); pstmt.setString(20, eb.getMcScac()); pstmt.setString(21, eb.getMcName()); pstmt.setString(22, eb.getRcdInfo()); pstmt.setString(23, gierInfo.getUsDotNumber()); pstmt.setString(24, uiiaInfo.getMcEin()); pstmt.setString(25, uiiaInfo.getMcDot()); pstmt.setString(26, uiiaInfo.getIddPin()); pstmt.setString(27, eb.getQ5Details()); pstmt.setString(28, eb.getN7Details()); pstmt.setString(29, eb.getR4Details()); pstmt.setString(30, eb.getN1Details()); pstmt.setString(31, eb.getN1DrDetails()); pstmt.setInt(32, bean.getNoDefectsCount()); pstmt.setInt(33, bean.getBrakesCount()); pstmt.setInt(34, bean.getLightsCount()); pstmt.setInt(35, bean.getWheelCount()); pstmt.setInt(36, bean.getAirlineCount()); pstmt.setInt(37, bean.getCouplingCount()); pstmt.setInt(38, bean.getFrameCount()); pstmt.setInt(39, bean.getBolsterCount()); pstmt.setInt(40, bean.getFastenerCount()); pstmt.setInt(41, bean.getSliderCount()); pstmt.setString(42, boescUserId); pstmt.setString(43, GlobalVariables.STATUS_PENDING); pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp()); int dbStat = 0; int dvirKey = 0; dbStat = pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); if (dbStat != 0) { if (rs != null) { while (rs.next()) { dvirKey = rs.getInt(1); logger.info("dvirKey: " + dvirKey); } } } if (dvirKey != 0) { conn.commit(); //Update BOESC_UNIQUE_NO : using business logic String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? "; qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey }); logger.info("Record Inserted successfully for DVIR..." + file.getName()); return true; } else { conn.rollback(); logger.error("Failure Data insertion in DVIR.."); } } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex1) { logger.error( "Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage()); ex1.printStackTrace(); throw ex1; } catch (Exception e) { logger.error("Caught SQL exception in finally block " + e.getMessage()); e.printStackTrace(); throw e; } } return false; }
From source file:Tim.MarkovChains.java
private int getMarkovWordId(String word) { Connection con = null;/*from w w w . j a va 2 s . com*/ try { con = db.pool.getConnection(timeout); PreparedStatement checkword, addword; checkword = con.prepareStatement("SELECT id FROM markov_words WHERE word = ?"); addword = con.prepareStatement("INSERT INTO markov_words SET word = ?", Statement.RETURN_GENERATED_KEYS); checkword.setString(1, word); ResultSet checkRes = checkword.executeQuery(); if (checkRes.next()) { return checkRes.getInt("id"); } else { addword.setString(1, word); addword.executeUpdate(); ResultSet rs = addword.getGeneratedKeys(); if (rs.next()) { return rs.getInt(1); } } } catch (SQLException ex) { Logger.getLogger(MarkovChains.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(Tim.class.getName()).log(Level.SEVERE, null, ex); } } return 0; }
From source file:com.webapp.security.SecurityDataContext.java
/** * @param role/*from w ww. ja v a2 s.com*/ */ private boolean createRole(final Role role) { if (role != null) { final String insertRoleSql = "insert into SECURITY_ROLE (CONTEXT, ROLE, DESCRIPTION) values (?,?,?)"; final String insertRolePermSql = "insert into SECURITY_ROLE_PERMISSION (CONTEXT, ROLE, TARGET, PERMISSION) values (?,?,?,?)"; LOG.info("Inserting new role (" + role.getName() + "-" + role.getDescription() + ") into " + getName() + " context"); // If there is a value for the password... if (role.getName() != null && role.getName().trim().length() > 0) { try { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(insertRoleSql.toString(), Statement.RETURN_GENERATED_KEYS); ps.setString(1, getName().toLowerCase()); //context ps.setString(2, role.getName().toLowerCase()); // name ps.setString(3, role.getDescription()); // description return ps; } }); } catch (DataAccessException e) { LOG.error("Could not create role: " + e.getMessage()); return false; } // now we have to insert any permissions in the role List<Permission> perms = role.getPermissions(); for (final Permission perm : perms) { try { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(insertRolePermSql.toString(), Statement.RETURN_GENERATED_KEYS); ps.setString(1, getName().toLowerCase()); //context ps.setString(2, role.getName().toLowerCase()); // name ps.setString(3, perm.getTarget()); // target of the permission ps.setLong(4, perm.getAction()); return ps; } }); } catch (DataAccessException e) { LOG.error("Could not record permission target '" + perm.getTarget() + "' for role '" + role.getName() + "' : " + e.getMessage()); return false; } } return true; } else { LOG.error("Empty role name - role not added"); } } else { LOG.error("Null role cannot be added to context"); } return false; }
From source file:org.ut.biolab.medsavant.server.serverapi.ProjectManager.java
@Override public int addProject(String sessID, String name, CustomField[] fields) throws SQLException, RemoteException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; InsertQuery query = new InsertQuery(table.getTable()); query.addColumn(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), name); Connection c = ConnectionController.connectPooled(sessID); PreparedStatement stmt = c.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); stmt.execute();/* www.ja va2s . co m*/ ResultSet res = stmt.getGeneratedKeys(); res.next(); int projID = res.getInt(1); PatientManager.getInstance().createPatientTable(sessID, projID, fields); c.close(); return projID; }
From source file:org.rimudb.Table.java
/** * Return a prepared statement for the given SQL. *///w w w . j a va2 s . c om protected PreparedStatement createPreparedStatement(Connection con, String sql, CrudType crudType) throws SQLException { if (con == null) throw new SQLException("Connection (con) is null"); if (sql == null) throw new SQLException("SQL statement string (sql) is null"); if (crudType == CrudType.CREATE && hasAutoIncrementKey() && sqlAdapter.getDatabaseMetaData().getSupportsGetGeneratedKeys()) { // If the database permits us to get the key value using getGeneratedKeys() if (sqlAdapter.getSupportsReturnGeneratedKeys()) { return con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // Else the database requires us to pass an array of identity columns on the prepare } else { int identityColumn = getTableMetaData().getAutoIncrementColumn().getColumnNbr() + 1; int identityColumns[] = new int[] { identityColumn }; return con.prepareStatement(sql, identityColumns); } } else { return con.prepareStatement(sql); } }
From source file:org.bidtime.dbutils.QueryRunnerEx.java
/** * Executes the given INSERT SQL statement. * @param conn The connection to use for the query call. * @param closeConn True if the connection should be closed, false otherwise. * @param sql The SQL statement to execute. * @param rsh The handler used to create the result object from * the <code>ResultSet</code> of auto-generated keys. * @param params The query replacement parameters. * @return An object generated by the handler. * @throws SQLException If there are database or parameter errors. * @since 1.6/*from w w w . j av a 2 s . c om*/ */ private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException { if (conn == null) { throw new SQLException("Null connection"); } if (sql == null) { if (closeConn) { close(conn); } throw new SQLException("Null SQL statement"); } if (rsh == null) { if (closeConn) { close(conn); } throw new SQLException("Null ResultSetHandler"); } PreparedStatement stmt = null; long startTime = System.currentTimeMillis(); T generatedKeys = null; try { stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stmt.setQueryTimeout(StmtParams.getInstance().getStmtUpdateTimeOut()); this.fillStatement(stmt, params); stmt.executeUpdate(); ResultSet resultSet = stmt.getGeneratedKeys(); generatedKeys = rsh.handle(resultSet); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { close(stmt); if (closeConn) { close(conn); } if (LogInsertSql.logInfoOrDebug()) { LogInsertSql.logFormatTimeNow(startTime, sql, params); } } return generatedKeys; }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
@Override public ExecutableFeed addExecutableFeed(String name, String metadata, boolean readable, boolean writable, List<String> keywords, ExecutableFeedDescription executableFeedDescription) { ExecutableFeed executableFeed = null; if (executableFeedDescription == null) { Log.e(TAG, "One cannot create an executable feed with no description"); return null; }//ww w . j a va 2 s .co m try { checkOpenness(); connection.setAutoCommit(false); //First things first, insert the feed's values to the feed table String sqlFeedInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FEED + "(" + IotHubDataHandler.KEY_FEED_NAME + "," + IotHubDataHandler.KEY_FEED_METADATA + "," + IotHubDataHandler.KEY_FEED_TYPE + "," + IotHubDataHandler.KEY_FEED_STORAGE + "," + IotHubDataHandler.KEY_FEED_READABLE + "," + IotHubDataHandler.KEY_FEED_WRITABLE + ") VALUES (?,?,?,0,?,?)"; PreparedStatement psFeedInsert = connection.prepareStatement(sqlFeedInsert, Statement.RETURN_GENERATED_KEYS); psFeedInsert.setString(1, name); psFeedInsert.setString(2, metadata); psFeedInsert.setString(3, IotHubDataHandler.EXECUTABLE_FEED); psFeedInsert.setInt(4, readable ? 1 : 0); psFeedInsert.setInt(5, writable ? 1 : 0); psFeedInsert.executeUpdate(); ResultSet genKeysFeed = psFeedInsert.getGeneratedKeys(); if (genKeysFeed.next()) { long insertIdFeed = genKeysFeed.getLong(1); //Now we add the keywords addFeedKeywords(insertIdFeed, keywords); //Now we add the fields addExecutableFeedDescription(insertIdFeed, executableFeedDescription); //At point we should have everything set so it is time to retrieve the composed feed from the database //Log.d(TAG, "Now i will try to collect the executable feed that was just added to the db"); executableFeed = getExecutableFeed(insertIdFeed); if (executableFeed == null) { Log.e(TAG, "The feed should not be null"); } //Now I want to make some checks if (!compareExecutableFeeds(executableFeed, name, metadata, readable, writable, keywords, executableFeedDescription)) { Log.e(TAG, "Retrieving feed " + name + " did not work"); executableFeed = null; } } else { Log.e(TAG, "The insert of feed " + name + " did not work"); } genKeysFeed.close(); psFeedInsert.close(); } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); executableFeed = null; } try { if (executableFeed == null) { connection.rollback(); } connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return executableFeed; }