Example usage for java.sql Statement RETURN_GENERATED

Introduction

In this page you can find the example usage for java.sql Statement RETURN_GENERATED_KEYS.

Prototype

int RETURN_GENERATED_KEYS

To view the source code for java.sql Statement RETURN_GENERATED_KEYS.

Click Source Link

Document

The constant indicating that generated keys should be made available for retrieval.

Usage

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * {@inheritDoc}//from   w  w  w .  j  a  v  a 2s  .  co m
 */
public void createJob(MJob job, Connection conn) {
    PreparedStatement stmt = null;
    int result;
    try {
        stmt = conn.prepareStatement(crudQueries.getStmtInsertJob(), Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, job.getName());
        stmt.setLong(2, job.getFromLinkId());
        stmt.setLong(3, job.getToLinkId());
        stmt.setBoolean(4, job.getEnabled());
        stmt.setString(5, job.getCreationUser());
        stmt.setTimestamp(6, new Timestamp(job.getCreationDate().getTime()));
        stmt.setString(7, job.getLastUpdateUser());
        stmt.setTimestamp(8, new Timestamp(job.getLastUpdateDate().getTime()));

        result = stmt.executeUpdate();
        if (result != 1) {
            throw new SqoopException(CommonRepositoryError.COMMON_0009, Integer.toString(result));
        }

        ResultSet rsetJobId = stmt.getGeneratedKeys();

        if (!rsetJobId.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0010);
        }

        long jobId = rsetJobId.getLong(1);

        // from config for the job
        createInputValues(crudQueries.getStmtInsertJobInput(), jobId, job.getFromJobConfig().getConfigs(),
                conn);
        // to config for the job
        createInputValues(crudQueries.getStmtInsertJobInput(), jobId, job.getToJobConfig().getConfigs(), conn);
        // driver config per job
        createInputValues(crudQueries.getStmtInsertJobInput(), jobId, job.getDriverConfig().getConfigs(), conn);

        job.setPersistenceId(jobId);

    } catch (SQLException ex) {
        logException(ex, job);
        throw new SqoopException(CommonRepositoryError.COMMON_0023, ex);
    } finally {
        closeStatements(stmt);
    }
}

From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * @param oldDBConn/* www .j  a  v  a  2  s . c om*/
 * @param newDBConn
 */
public static void moveStratFieldsToCEA(final Connection oldDBConn, final Connection newDBConn) {
    String sql = null;
    try {
        IdMapperIFace ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent",
                "CollectingEventID", false);

        String postFix = " FROM collectingevent ce Inner Join collectingeventattribute AS cea ON ce.CollectingEventAttributeID = cea.CollectingEventAttributeID ";

        /*
        Specify 5 Field ----------> Specify 6 Field
        Stratigraphy.superGroup --> CEA.text3
        Stratigraphy.group      --> CEA.text4
        Stratigraphy.formation  --> CEA.text5
        Stratigraphy.text1      --> CEA.text1
        Stratigraphy.number1    --> CEA.number1
        Stratigraphy.text2      --> CEA.text2
         */

        Timestamp now = new Timestamp(System.currentTimeMillis());
        PreparedStatement pStmt = newDBConn.prepareStatement(
                "UPDATE collectingeventattribute SET Text1=?, Text2=?, Text3=?, Text4=?, Text5=?, Number1=? WHERE CollectingEventAttributeID=?");

        PreparedStatement pStmt2 = newDBConn.prepareStatement(
                "INSERT INTO collectingeventattribute SET Text1=?, Text2=?, Text3=?, Text4=?, Text5=?, Number1=?, Version=0, DisciplineID=?, TimestampCreated=?, TimestampModified=?",
                Statement.RETURN_GENERATED_KEYS);
        PreparedStatement pStmt3 = newDBConn.prepareStatement(
                "UPDATE collectingevent SET CollectingEventAttributeID=? WHERE CollectingEventID=?");

        int cnt = 0;
        // Query to Create PickList
        sql = "SELECT StratigraphyID, Text1, Text2, SuperGroup, `Group`, Formation, Number1 FROM stratigraphy";
        for (Object[] row : BasicSQLUtils.query(oldDBConn, sql)) {
            Integer id = (Integer) row[0];
            Integer newCEId = ceMapper.get(id);
            if (newCEId != null) {
                Vector<Object[]> colList = BasicSQLUtils.query(
                        "SELECT DisciplineID, CollectingEventAttributeID FROM collectingevent WHERE CollectingEventID = "
                                + newCEId);
                Object[] cols = colList.get(0);

                if (cols[1] != null) {
                    pStmt.setString(1, (String) row[1]);
                    pStmt.setString(2, (String) row[2]);
                    pStmt.setString(3, (String) row[3]);
                    pStmt.setString(4, (String) row[4]);
                    pStmt.setString(5, (String) row[5]);
                    pStmt.setString(6, (String) row[6]);
                    pStmt.setInt(7, newCEId);

                    int rv = pStmt.executeUpdate();
                    if (rv != 1) {
                        log.error(String.format("Error updating CEA New Id %d  Old: %d  rv: %d", newCEId, id,
                                rv));
                    }
                } else {
                    Integer disciplineID = (Integer) cols[0];
                    pStmt2.setString(1, (String) row[1]);
                    pStmt2.setString(2, (String) row[2]);
                    pStmt2.setString(3, (String) row[3]);
                    pStmt2.setString(4, (String) row[4]);
                    pStmt2.setString(5, (String) row[5]);
                    pStmt2.setString(6, (String) row[6]);
                    pStmt2.setInt(7, disciplineID);
                    pStmt2.setTimestamp(8, now);
                    pStmt2.setTimestamp(9, now);

                    int rv = pStmt2.executeUpdate();
                    if (rv == 1) {
                        Integer newCEAId = BasicSQLUtils.getInsertedId(pStmt2);
                        if (newCEAId != null) {
                            pStmt3.setInt(1, newCEAId);
                            pStmt3.setInt(2, newCEId);
                            rv = pStmt3.executeUpdate();
                            if (rv != 1) {
                                log.error(String.format("Error updating CEA New Id %d To CE ID: %d", newCEAId,
                                        newCEId));
                            }
                        } else {
                            log.error("Couldn't get inserted CEAId");
                        }

                    } else {
                        log.error(String.format("Error updating CEA New Id %d  Old: %d  rv: %d", newCEId, id,
                                rv));
                    }
                }
            } else {
                log.error(String.format("No Map for Old CE Id %d", id));
            }
            cnt++;
            if (cnt % 500 == 0) {
                log.debug("Count " + cnt);
            }
        }
        log.debug("Count " + cnt);
        pStmt.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

From source file:com.quinsoft.zeidon.dbhandler.JdbcHandler.java

/**
 * Returns a PreparedStatement for the sql.  If we are caching PreparedStatements then this will
 * perform necessary logic for caching.  Will bind attributes if there are any.
 * @param stmt TODO/* w  w w .  ja va  2  s  . c  o m*/
 * @param sql
 * @param view TODO
 * @param entityDef TODO
 * @param commandType
 *
 * @return
 * @throws SQLException
 */
private PreparedStatement prepareAndBind(SqlStatement stmt, String sql, View view, EntityDef entityDef,
        SqlCommand commandType) throws SQLException {
    PreparedStatement ps = null;

    // Not every statement can be cached.
    boolean cacheThisCommand = false;
    if (commandType != null) {
        switch (commandType) {
        case SELECT:
        case DELETE:
            cacheThisCommand = true;
            break;

        default:
            break;
        }
    }

    if (cacheThisCommand && cachedStatements != null && entityDef != null) // Are we using cached PreparedStatements?
    {
        PreparedStatementCacheKey key = new PreparedStatementCacheKey(entityDef, commandType, sql);
        PreparedStatementCacheValue value = cachedStatements.get(key.getKey());
        if (value == null) {
            ps = useDbGenerateKeys()
                    ? transaction.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
                    : transaction.getConnection().prepareStatement(sql);

            if (stmt.activateLimit > 0) {
                task.dblog().debug("setMaxRows = %d", stmt.activateLimit);
                ps.setMaxRows(stmt.activateLimit);
            }

            value = new PreparedStatementCacheValue(ps, sql);
            cachedStatements.put(key.getKey(), value);
        } else {
            task.dblog().trace("Using cached statement for Entity => %s \n=> %s", entityDef, sql);
            ps = value.ps;
        }
    } else {
        // Some JDBC implementations don't support Statement.NO_GENERATED_KEYS (SQLDroid I'm looking
        // at you) so we have to use the single-argument prepareStatement if we aren't keeping the
        // generated keys.
        if (useDbGenerateKeys())
            ps = transaction.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        else
            ps = transaction.getConnection().prepareStatement(sql);

        if (stmt != null && stmt.activateLimit > 0) {
            task.dblog().debug("setMaxRows = %d", stmt.activateLimit);
            ps.setMaxRows(stmt.activateLimit);
        }
    }

    if (stmt != null) // When executing simple statements this will be null.
    {
        int idx = 0;
        for (Object boundValue : stmt.getBoundValues()) {
            idx++;
            String valueAsString;
            if (boundValue instanceof DataField) {
                DataField dataField = (DataField) boundValue;
                valueAsString = getTranslator().bindAttributeValue(ps, view, dataField, idx);
            } else {
                valueAsString = getTranslator().bindAttributeValue(ps, boundValue, idx);
            }

            if (task.dblog().isDebugEnabled())
                task.dblog().debug("Bind idx %d = %s (attr value)", idx, leftStr(valueAsString));
        }
    }

    return ps;
}

From source file:com.skycloud.management.portal.admin.sysmanage.dao.impl.UserManageDaoImpl.java

@Override
public int saveCompany(final TCompanyInfo tcompanyinfo) throws SQLException {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final String sql = "insert into T_SCS_COMPANY_INFO (COMP_LEGAL_PERSON,COMP_CN_NAME,"
            + "COMP_LEGAL_PERSON_ID," + " COMP_ADDRESS,POST_CODE,"
            + " COMP_PHONE,RELA_MOBILE,COMP_FAX,COMP_EMAIL," + " COMP_ORG_CODE,BUS_LICENSE_NUM, "
            + " COMP_BANK_NAME,COMP_BANK_ACCOUNT,FIR_CHECK_COMMENT,CHECK_STATE,BLN_START_TIME,BLN_END_TIME) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,4,?,?);";

    try {//from  w w w  .java2s .  co  m
        this.getJdbcTemplate().update(new PreparedStatementCreator() {
            int i = 1;

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(i++, tcompanyinfo.getCompLegalPerson());
                ps.setString(i++, tcompanyinfo.getCompCnName());
                ps.setString(i++, tcompanyinfo.getCompLegalPersonId());
                ps.setString(i++, tcompanyinfo.getCompAddress());
                ps.setString(i++, tcompanyinfo.getPostCode());
                ps.setString(i++, tcompanyinfo.getCompPhone());
                ps.setString(i++, tcompanyinfo.getRelaMobile());
                ps.setString(i++, tcompanyinfo.getCompFax());
                ps.setString(i++, tcompanyinfo.getCompEmail());
                ps.setString(i++, tcompanyinfo.getCompOrgCode());
                ps.setString(i++, tcompanyinfo.getBusLicenseNum());
                ps.setString(i++, tcompanyinfo.getCompBankName());
                ps.setString(i++, tcompanyinfo.getCompBankAccount());
                ps.setString(i++, tcompanyinfo.getFirCheckComment());
                ps.setTimestamp(i++, new Timestamp(new Date(System.currentTimeMillis()).getTime()));
                ps.setTimestamp(i++, new Timestamp(new Date(System.currentTimeMillis()).getTime()));
                return ps;
            }
        }, keyHolder);
    } catch (Exception e) {
        e.printStackTrace();
        throw new SQLException("?");
    }
    return keyHolder.getKey().intValue();
}

From source file:opengovcrawler.DB.java

/**
 * Starts the crawler's activity log// ww w.  jav  a  2s .  c om
 *
 * @param startTime - The start time of the crawling procedure
 * @return - The activity's log id
 * @throws java.sql.SQLException
 */
public static int LogCrawler(long startTime) throws SQLException {
    String insertLogSql = "INSERT INTO log.activities (module_id, start_date, end_date, status_id, message) VALUES (?,?,?,?,?)";
    PreparedStatement prepLogCrawlStatement = connection.prepareStatement(insertLogSql,
            Statement.RETURN_GENERATED_KEYS);
    prepLogCrawlStatement.setInt(1, 1);
    prepLogCrawlStatement.setTimestamp(2, new java.sql.Timestamp(startTime));
    prepLogCrawlStatement.setTimestamp(3, null);
    prepLogCrawlStatement.setInt(4, 1);
    prepLogCrawlStatement.setString(5, null);
    prepLogCrawlStatement.executeUpdate();
    ResultSet rsq = prepLogCrawlStatement.getGeneratedKeys();
    int crawlerId = 0;
    if (rsq.next()) {
        crawlerId = rsq.getInt(1);
    }
    prepLogCrawlStatement.close();
    return crawlerId;
}

From source file:dk.netarkivet.harvester.datamodel.DomainDBDAO.java

/**
 * Insert new harvest info for a domain.
 * @param c /*from  w w  w . j  a  va 2  s. c  om*/
 *            A connection to the database
 * @param d
 *            A domain to insert on. The domains ID must be correct.
 * @param harvestInfo
 *            Harvest info to insert.
 */
private void insertHarvestInfo(Connection c, Domain d, HarvestInfo harvestInfo) {
    PreparedStatement s = null;
    try {
        // Note that the config_id is grabbed from the configurations table.
        s = c.prepareStatement(
                "INSERT INTO historyinfo " + "( stopreason, objectcount, bytecount, config_id, "
                        + "job_id, harvest_id, harvest_time ) " + "VALUES ( ?, ?, ?, ?, ?, ?, ? )",
                Statement.RETURN_GENERATED_KEYS);
        s.setInt(1, harvestInfo.getStopReason().ordinal());
        s.setLong(2, harvestInfo.getCountObjectRetrieved());
        s.setLong(3, harvestInfo.getSizeDataRetrieved());
        // TODO More stable way to get IDs, use a select
        s.setLong(4, d.getConfiguration(harvestInfo.getDomainConfigurationName()).getID());
        if (harvestInfo.getJobID() != null) {
            s.setLong(5, harvestInfo.getJobID());
        } else {
            s.setNull(5, Types.BIGINT);
        }
        s.setLong(6, harvestInfo.getHarvestID());
        s.setTimestamp(7, new Timestamp(harvestInfo.getDate().getTime()));
        s.executeUpdate();
        harvestInfo.setID(DBUtils.getGeneratedID(s));
    } catch (SQLException e) {
        throw new IOFailure("SQL error while inserting harvest info " + harvestInfo + " for " + d + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    }
}

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

/**
 * Insert directions: FROM and TO./*from   w w  w.ja  v  a  2 s  .c o m*/
 * @param conn
 * @return Map<Direction, Long> direction ID => Direction
 */
protected Map<Direction, Long> insertDirections(Connection conn) {
    // Add directions
    Map<Direction, Long> directionMap = new TreeMap<Direction, Long>();
    PreparedStatement insertDirectionStmt = null;
    try {
        // Insert directions and get IDs.
        for (Direction direction : Direction.values()) {
            insertDirectionStmt = conn.prepareStatement(STMT_INSERT_DIRECTION, Statement.RETURN_GENERATED_KEYS);
            insertDirectionStmt.setString(1, direction.toString());
            if (insertDirectionStmt.executeUpdate() != 1) {
                throw new SqoopException(DerbyRepoError.DERBYREPO_0046,
                        "Could not add directions FROM and TO.");
            }

            ResultSet directionId = insertDirectionStmt.getGeneratedKeys();
            if (directionId.next()) {
                if (LOG.isInfoEnabled()) {
                    LOG.info("Loaded direction: " + directionId.getLong(1));
                }

                directionMap.put(direction, directionId.getLong(1));
            } else {
                throw new SqoopException(DerbyRepoError.DERBYREPO_0047,
                        "Could not get ID of direction " + direction);
            }
        }
    } catch (SQLException e) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0000, e);
    } finally {
        closeStatements(insertDirectionStmt);
    }

    return directionMap;
}

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;//  w  w  w  .j a  v  a2s . co m
    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;
}

From source file:com.flexoodb.engines.FlexJAXBMappedDBDataEngine.java

private Object persist(Object obj, Connection conn, String targettable) throws Exception {
    // check if the object has a table
    String tablename = (targettable == null ? ((FlexContainer) obj).getObject().getClass().getSimpleName()
            : targettable).toLowerCase();

    tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename;

    String id = ((FlexContainer) obj).getId();
    String parentid = ((FlexContainer) obj).getParentId();
    Object obj2 = ((FlexContainer) obj).getObject();

    // retrieve the methods of the object and use as param.

    FlexElement element = null;/*  ww  w .j a  v a 2s  .c o m*/

    if (tablename.indexOf("_") > -1) {
        element = _elements.get(tablename.substring(tablename.indexOf("_") + 1));
    } else {
        element = _elements.get(tablename);
    }

    String idcolumn = element.getAttribute("idcolumn").getValue();
    String realtablename = element.getAttribute("realtablename").getValue();
    String parentidcolumn = element.getAttribute("parentidcolumn").getValue();
    String autoincrement = element.getAttribute("autoincrement").getValue();
    boolean includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false
            : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true"));

    if (!autoincrement.equalsIgnoreCase("true")) {
        if (id == null || id.isEmpty()) {
            id = getNewId();
            FlexUtils.setId(id, (FlexContainer) obj);
        }

        if (parentid == null || parentid.isEmpty()) {
            ((FlexContainer) obj).setParentId(id);
        }
    }

    StringBuffer fields = new StringBuffer();
    StringBuffer entries = new StringBuffer();

    Hashtable<String, Object[]> fieldswithcontent = getNonNullObjectFields(tablename, idcolumn, parentidcolumn,
            id, parentid, obj2, element);

    Enumeration en = fieldswithcontent.keys();
    while (en.hasMoreElements()) {
        String field = (String) en.nextElement();
        //boolean ok = true;
        fields.append(",`" + field + "`");
        entries.append(",?");
    }

    //System.out.println(">>>insert into "+tablename.toLowerCase()+" ("+fields.substring(1)+") values ("+entries.substring(1)+")");
    PreparedStatement ps = null;

    if (autoincrement.equalsIgnoreCase("true")) {
        ps = (PreparedStatement) conn.prepareStatement(
                "insert into " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase())
                        + " (" + fields.substring(1) + ") values (" + entries.substring(1) + ")",
                Statement.RETURN_GENERATED_KEYS);
        // then we get the preparedstatement
        updatePreparedStatement(tablename, fieldswithcontent, ps);
        ps.executeUpdate();
        ResultSet keys = ps.getGeneratedKeys();
        keys.next();

        int newkey = keys.getInt(1);

        keys.close();

        FlexUtils.setId(newkey + "", (FlexContainer) obj);

        if (parentid == null || parentid.isEmpty()) {
            ((FlexContainer) obj).setParentId(newkey + "");
        }

        FlexUtils.setObjectMethod(((FlexContainer) obj).getObject(), idcolumn, new BigInteger(newkey + ""));

    } else {
        ps = (PreparedStatement) conn.prepareStatement(
                "insert into " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase())
                        + " (" + fields.substring(1) + ") values (" + entries.substring(1) + ")");
        // then we get the preparedstatement
        updatePreparedStatement(tablename, fieldswithcontent, ps);
        ps.executeUpdate();
    }

    ps.close();

    return obj;
}

From source file:com.ywang.alone.handler.task.AuthTask.java

/**
 * //w ww  .jav a2  s . co m
 * 
 * @param msg
 *            {
 *            'phoneNum':'ywang','password':'e10adc3949ba59abbe56e057f20f883
 *            e ' , 'deviceToken':'8a2597aa1d37d432a88a446d82b6561e',
 *            'lng':'117.157954','lat':'31.873432','osVersion':'8.0',
 *            'systemType':'iOS','phoneModel':'iPhone 5s','key':''}
 * 
 * @return
 */
private static JSONObject regNewUser(String msg) {
    JSONObject jsonObject = AloneUtil.newRetJsonObject();
    JSONObject user = JSON.parseObject(msg);
    DruidPooledConnection conn = null;
    PreparedStatement updatestmt = null;
    try {
        conn = DataSourceFactory.getInstance().getConn();
        conn.setAutoCommit(false);
        String uuid = UUID.randomUUID().toString();
        uuid = uuid.replaceAll("-", "");
        String token = MD5.getMD5String(uuid);

        String im_user = user.getString("phoneNum").trim();
        UserInfo userInfo = new UserInfo();
        userInfo.setRegTime(System.currentTimeMillis());
        userInfo.setOnline("1");
        userInfo.setKey(token);
        userInfo.setMessageUser(im_user);
        userInfo.setMessagePwd("alone123456");

        updatestmt = conn.prepareStatement(
                "insert into userbase (PHONE_NUM, PWD, REG_TIME, LNG, LAT, DEVICE_TOKEN, SYSTEM_TYPE, OS_VERSION,PHONE_MODEL, PKEY, MESSAGE_USER, MESSAGE_PWD) VALUES (?,?,?, ?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        updatestmt.setString(1, user.getString("phoneNum").trim());
        updatestmt.setString(2, user.getString("password").trim());
        updatestmt.setLong(3, userInfo.getRegTime());
        updatestmt.setString(4, user.getString("lng").trim());
        updatestmt.setString(5, user.getString("lat").trim());

        updatestmt.setString(6, user.getString("deviceToken").trim());
        updatestmt.setString(7, user.getString("systemType").trim());
        updatestmt.setString(8, user.getString("osVersion").trim());
        updatestmt.setString(9, user.getString("phoneModel").trim());
        updatestmt.setString(10, userInfo.getKey());
        updatestmt.setString(11, userInfo.getMessageUser());
        updatestmt.setString(12, "alone123456");

        int result = updatestmt.executeUpdate();

        if (result == 1) {
            ResultSet idRS = updatestmt.getGeneratedKeys();
            if (idRS.next()) {
                int userId = idRS.getInt(1);
                userInfo.setUserId(userId + "");
            }
            jsonObject.put("ret", Constant.RET.REG_SUCC);
            jsonObject.put("data", userInfo);
        } else {
            jsonObject.put("ret", Constant.RET.SYS_ERR);
            jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
            jsonObject.put("errDesc", Constant.ErrorCode.SYS_ERR);
            LoggerUtil.logServerErr("insert into userbase no result");
        }

        conn.commit();
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        LoggerUtil.logServerErr(e);
        jsonObject.put("ret", Constant.RET.SYS_ERR);
        jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
        jsonObject.put("errDesc", Constant.ErrorCode.SYS_ERR);
    } finally {
        try {
            if (null != updatestmt) {
                updatestmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException e) {
            LoggerUtil.logServerErr(e.getMessage());
        }
    }

    return jsonObject;
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage