List of usage examples for java.sql Statement getGeneratedKeys
ResultSet getGeneratedKeys() throws SQLException;
Statement object. From source file:org.openmrs.module.spreadsheetimport.DatabaseBackend.java
public static String importData(Map<UniqueImport, Set<SpreadsheetImportTemplateColumn>> rowData, boolean rollbackTransaction) throws Exception { Connection conn = null;//from w w w . j av a 2 s . com Statement s = null; Exception exception = null; String sql = null; String encounterId = null; try { // Connect to db Class.forName("com.mysql.jdbc.Driver").newInstance(); Properties p = Context.getRuntimeProperties(); String url = p.getProperty("connection.url"); conn = DriverManager.getConnection(url, p.getProperty("connection.username"), p.getProperty("connection.password")); conn.setAutoCommit(false); s = conn.createStatement(); List<String> importedTables = new ArrayList<String>(); // Import for (UniqueImport uniqueImport : rowData.keySet()) { String tableName = uniqueImport.getTableName(); boolean isEncounter = "encounter".equals(tableName); boolean isPerson = "person".equals(tableName); boolean isPatientIdentifier = "patient_identifier".equals(tableName); boolean isObservation = "obs".equals(tableName); boolean skip = false; // SPECIAL TREATMENT // for encounter, if the data is available in the row, it means we're UPDATING observations for an EXISTING encounter, so we don't have to create encounter // otherwise, we need to create a new encounter if (isEncounter) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { Object columnValue = column.getValue(); if (!columnValue.equals("")) { column.setGeneratedKey(columnValue.toString()); skip = true; importedTables.add("encounter"); // fake as just imported encounter break; } } if (skip) continue; } // SPECIAL TREATMENT // for observation, if the data to be inserted is empty, then simply skip if (isObservation) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { Object columnValue = column.getValue(); if (columnValue.equals("")) { skip = true; importedTables.add("observation"); // fake as just imported observation, not meaningful, just for consistency purpose break; } } if (skip) continue; } if (isPerson) { boolean isIdentifierExist = false; // SPECIAL TREATMENT 1 // if the patient_identifier.identifier is specified and it is linked to a person, then use that person instead // note: patient.patient_id == person.person_id (http://forum.openmrs.org/viewtopic.php?f=2&t=436) UniqueImport patientIdentifier = new UniqueImport("patient_identifier", null); if (rowData.containsKey(patientIdentifier)) { Set<SpreadsheetImportTemplateColumn> patientIdentifierColumns = rowData .get(patientIdentifier); for (SpreadsheetImportTemplateColumn patientIdentifierColumn : patientIdentifierColumns) { String columnName = patientIdentifierColumn.getColumnName(); if ("identifier".equals(columnName)) { isIdentifierExist = true; sql = "select patient_id from patient_identifier where identifier = " + patientIdentifierColumn.getValue(); System.out.println("Searching for existing patient of id " + patientIdentifierColumn.getValue()); ResultSet rs = s.executeQuery(sql); if (rs.next()) { String patientId = rs.getString(1); System.out.println("Found patient with patient_id = " + patientId); // no need to insert person, use the found patient_id as person_id Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { column.setGeneratedKey(patientId); } importedTables.add("person"); // fake as just imported person importedTables.add("patient"); // fake as just imported patient importedTables.add("patient_identifier"); // fake as just imported patient_identifier importedTables.add("person_name"); // fake as just imported person_name importedTables.add("person_address"); // fake as just imported person_address skip = true; } rs.close(); break; } } } if (skip) continue; // now, if we proceed to this point, it means patient identifier, if exists, does not match, and in that case, no point to match with person name // SPECIAL TREATMENT 2 // if first name, last name, middle name, gender, and birthdate match existing record, then use that record instead UniqueImport personName = new UniqueImport("person_name", null); if (rowData.containsKey(personName) && !isIdentifierExist) { Set<SpreadsheetImportTemplateColumn> personNameColumns = rowData.get(personName); // getting gender, birthdate from person Object gender = null; Object birthdate = null; for (SpreadsheetImportTemplateColumn personColumn : rowData.get(uniqueImport)) { String columnName = personColumn.getColumnName(); if ("birth_date".equals(columnName)) birthdate = personColumn.getValue(); if ("gender".equals(columnName)) gender = personColumn.getValue(); } // getting first name, last name, middle name from person Object givenName = null; Object familyName = null; Object middleName = null; for (SpreadsheetImportTemplateColumn personNameColumn : personNameColumns) { String columnName = personNameColumn.getColumnName(); if ("given_name".equals(columnName)) givenName = personNameColumn.getValue(); if ("family_name".equals(columnName)) familyName = personNameColumn.getValue(); if ("middle_name".equals(columnName)) middleName = personNameColumn.getValue(); } // find matching person name sql = "select person.person_id from person_name join person where gender " + (gender == null ? "is NULL" : "= " + gender) + " and birthdate " + (birthdate == null ? "is NULL" : "= " + birthdate) + " and given_name " + (givenName == null ? "is NULL" : "= " + givenName) + " and family_name " + (familyName == null ? "is NULL" : "= " + familyName) + " and middle_name " + (middleName == null ? "is NULL" : "= " + middleName); ResultSet rs = s.executeQuery(sql); String personId = null; if (rs.next()) { // matched => no need to insert person, use the found patient_id as person_id Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { column.setGeneratedKey(personId); } importedTables.add("person"); // fake as just imported person importedTables.add("patient"); // fake as just imported patient importedTables.add("person_name"); // fake as just imported person_name importedTables.add("person_address"); // fake as just imported person_address skip = true; } } if (skip) continue; } if (isPatientIdentifier && importedTables.contains("patient_identifier")) continue; // Data from columns Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); String columnNames = ""; String columnValues = ""; Set<SpreadsheetImportTemplateColumnPrespecifiedValue> columnPrespecifiedValueSet = null; Set<SpreadsheetImportTemplateColumnColumn> columnColumnsImportBefore = null; boolean isFirst = true; for (SpreadsheetImportTemplateColumn column : columnSet) { // special treatment for encounter: simply ignore this loop since we don't want to insert encounter_id if (isEncounter) { columnPrespecifiedValueSet = column.getColumnPrespecifiedValues(); columnColumnsImportBefore = column.getColumnColumnsImportBefore(); // inject date_created columnNames += "date_created"; columnValues += "now()"; // find encounter_datetime based on observation date time java.sql.Date encounterDatetime = new java.sql.Date(System.currentTimeMillis()); Set<UniqueImport> uniqueImports = rowData.keySet(); for (UniqueImport u : uniqueImports) { if ("obs".equals(u.getTableName())) { Set<SpreadsheetImportTemplateColumn> obsColumns = rowData.get(u); for (SpreadsheetImportTemplateColumn obsColumn : obsColumns) { if ("obs_datetime".equals(obsColumn.getColumnName())) { String obsColumnValue = obsColumn.getValue().toString(); obsColumnValue = obsColumnValue.substring(1, obsColumnValue.length() - 1); Date obsColumnValueDate = java.sql.Date.valueOf(obsColumnValue); if (obsColumnValueDate.before(encounterDatetime)) encounterDatetime = obsColumnValueDate; } } } } columnNames += ", encounter_datetime"; columnValues += ",'" + encounterDatetime.toString() + "'"; isFirst = false; break; } // Check for duplicates if (column.getDisallowDuplicateValue()) { sql = "select " + column.getColumnName() + " from " + column.getTableName() + " where " + column.getColumnName() + " = " + column.getValue(); if (log.isDebugEnabled()) { log.debug(sql); System.out.println(sql); } ResultSet rs = s.executeQuery(sql); boolean foundDuplicate = rs.next(); rs.close(); if (foundDuplicate) { throw new SpreadsheetImportDuplicateValueException(column); } } if (isFirst) { // Should be same for all columns in unique import columnPrespecifiedValueSet = column.getColumnPrespecifiedValues(); columnColumnsImportBefore = column.getColumnColumnsImportBefore(); isFirst = false; } else { columnNames += ","; columnValues += ","; } columnNames += column.getColumnName(); columnValues += column.getValue().toString(); } // Data from pre-specified values for (SpreadsheetImportTemplateColumnPrespecifiedValue columnPrespecifiedValue : columnPrespecifiedValueSet) { if (isFirst) isFirst = false; else { columnNames += ","; columnValues += ","; } columnNames += columnPrespecifiedValue.getColumnName(); columnValues += columnPrespecifiedValue.getPrespecifiedValue().getValue(); } // Data from columns import before if (!columnColumnsImportBefore.isEmpty()) { // Set up Map<String, String> mapPrimaryKeyColumnNameToGeneratedKey = new HashMap<String, String>(); for (SpreadsheetImportTemplateColumnColumn columnColumn : columnColumnsImportBefore) { String primaryKeyColumnName = columnColumn.getColumnName(); String columnGeneratedKey = columnColumn.getColumnImportFirst().getGeneratedKey(); if (mapPrimaryKeyColumnNameToGeneratedKey.containsKey(primaryKeyColumnName)) { String mapGeneratedKey = mapPrimaryKeyColumnNameToGeneratedKey .get(primaryKeyColumnName); if (!mapGeneratedKey.equals(columnGeneratedKey)) { throw new SpreadsheetImportUnhandledCaseException(); } } else { mapPrimaryKeyColumnNameToGeneratedKey.put(primaryKeyColumnName, columnGeneratedKey); } // TODO: I believe patient and person are only tables with this relationship, if not, then this // needs to be generalized if (primaryKeyColumnName.equals("patient_id") && importedTables.contains("person") && !importedTables.contains("patient")) { sql = "insert into patient (patient_id, creator) values (" + columnGeneratedKey + ", " + Context.getAuthenticatedUser().getId() + ")"; if (log.isDebugEnabled()) { log.debug(sql); } s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); // ResultSet rs = s.getGeneratedKeys(); // rs.next(); // if (!columnGeneratedKey.equals(rs.getString(1))) { // throw new SpreadsheetImportUnhandledCaseException(); // } importedTables.add("patient"); } } // Add columns for (String columnName : mapPrimaryKeyColumnNameToGeneratedKey.keySet()) { if (isFirst) isFirst = false; else { columnNames += ","; columnValues += ","; } columnNames += columnName; columnValues += mapPrimaryKeyColumnNameToGeneratedKey.get(columnName); } } // SPECIAL TREATMENT: if this is observation, then check for column obs_datetime. If not available, then use current time if (isObservation) { boolean hasDatetime = false; for (SpreadsheetImportTemplateColumn column : columnSet) { if ("obs_datetime".equals(column.getColumnName())) { hasDatetime = true; break; } } if (!hasDatetime) { columnNames += ",obs_datetime"; columnValues += ",now()"; } columnNames += ", date_created"; columnValues += ",now()"; } // SPECIAL TREATMENT: if this is patient identifier, then set location_id to NULL, to avoid CONSTRAINT `patient_identifier_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`)) if (isPatientIdentifier) { columnNames += ", location_id"; columnValues += ", NULL"; } // creator columnNames += ",creator"; columnValues += "," + Context.getAuthenticatedUser().getId(); // uuid DatabaseMetaData dmd = conn.getMetaData(); ResultSet rsColumns = dmd.getColumns(null, null, uniqueImport.getTableName(), "uuid"); if (rsColumns.next()) { columnNames += ",uuid"; columnValues += ",uuid()"; } rsColumns.close(); // Insert tableName sql = "insert into " + uniqueImport.getTableName() + " (" + columnNames + ")" + " values (" + columnValues + ")"; System.out.println(sql); if (log.isDebugEnabled()) { log.debug(sql); } s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet rs = s.getGeneratedKeys(); rs.next(); for (SpreadsheetImportTemplateColumn column : columnSet) { column.setGeneratedKey(rs.getString(1)); } // SPECIAL TREATMENT: update Encounter ID back to the Excel file by returning it to the caller if (isEncounter) encounterId = rs.getString(1); rs.close(); importedTables.add(uniqueImport.getTableName()); } } catch (SQLSyntaxErrorException e) { throw new SpreadsheetImportSQLSyntaxException(sql, e.getMessage()); } catch (Exception e) { log.debug(e.toString()); exception = e; throw new SpreadsheetImportSQLSyntaxException(sql, e.getMessage()); // TODO: for web debug purpose only, should comment out later } finally { if (s != null) { try { s.close(); } catch (Exception e) { } } if (conn != null) { if (rollbackTransaction) { conn.rollback(); } else { conn.commit(); } try { conn.close(); } catch (Exception e) { } } } if (exception != null) { throw exception; } return encounterId; }