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.jamwiki.db.CacheQueryHandler.java

/**
 *
 *///from w  w  w  .  j  a  va2  s.  c om
@Override
public void insertTopicVersions(List<TopicVersion> topicVersions) {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean useBatch = (topicVersions.size() > 1);
    try {
        conn = DatabaseConnection.getConnection();
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION);
        } else if (useBatch) {
            // generated keys don't work in batch mode
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT);
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        int topicVersionId = -1;
        if (!this.autoIncrementPrimaryKeys() || useBatch) {
            // manually retrieve next topic version id when using batch
            // mode or when the database doesn't support generated keys.
            topicVersionId = DatabaseConnection.executeSequenceQuery(STATEMENT_SELECT_TOPIC_VERSION_SEQUENCE);
        }
        for (TopicVersion topicVersion : topicVersions) {
            if (!this.autoIncrementPrimaryKeys() || useBatch) {
                // FIXME - if two threads update the database simultaneously then
                // it is possible that this code could set the topic version ID
                // to a value that is different from what the database ends up
                // using.
                topicVersion.setTopicVersionId(topicVersionId++);
            }
            StringReader sr = null;
            try {
                int index = 1;
                stmt.setInt(index++, topicVersion.getTopicVersionId());
                if (topicVersion.getEditDate() == null) {
                    topicVersion.setEditDate(new Timestamp(System.currentTimeMillis()));
                }
                stmt.setInt(index++, topicVersion.getTopicId());
                stmt.setString(index++, topicVersion.getEditComment());
                //pass the content into a stream to be passed to Cach
                sr = new StringReader(topicVersion.getVersionContent());
                stmt.setCharacterStream(index++, sr, topicVersion.getVersionContent().length());
                if (topicVersion.getAuthorId() == null) {
                    stmt.setNull(index++, Types.INTEGER);
                } else {
                    stmt.setInt(index++, topicVersion.getAuthorId());
                }
                stmt.setInt(index++, topicVersion.getEditType());
                stmt.setString(index++, topicVersion.getAuthorDisplay());
                stmt.setTimestamp(index++, topicVersion.getEditDate());
                if (topicVersion.getPreviousTopicVersionId() == null) {
                    stmt.setNull(index++, Types.INTEGER);
                } else {
                    stmt.setInt(index++, topicVersion.getPreviousTopicVersionId());
                }
                stmt.setInt(index++, topicVersion.getCharactersChanged());
                stmt.setString(index++, topicVersion.getVersionParamString());
            } finally {
                if (sr != null) {
                    sr.close();
                }
            }
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
            }
            if (this.autoIncrementPrimaryKeys() && !useBatch) {
                rs = stmt.getGeneratedKeys();
                if (!rs.next()) {
                    throw new SQLException("Unable to determine auto-generated ID for database record");
                }
                topicVersion.setTopicVersionId(rs.getInt(1));
            }
        }
        if (useBatch) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        throw new UncategorizedSQLException("insertTopicVersions", null, e);
    } finally {
        DatabaseConnection.closeConnection(conn, stmt, rs);
    }
}

From source file:com.act.lcms.db.model.CuratedChemical.java

public static CuratedChemical insertCuratedChemical(DB db, String name, String inchi, Double mPlusHPlusMass,
        Integer expectedCollisionVoltage, String referenceUrl) throws SQLException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(QUERY_INSERT_CURATED_CHEMICAL,
            Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, name, inchi, mPlusHPlusMass, expectedCollisionVoltage, referenceUrl);
        stmt.executeUpdate();// ww w .  ja  va2  s. c om
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
            if (resultSet.next()) {
                // Get auto-generated id.
                int id = resultSet.getInt(1);
                return new CuratedChemical(id, name, inchi, mPlusHPlusMass, expectedCollisionVoltage,
                        referenceUrl);
            } else {
                System.err.format("ERROR: could not retrieve autogenerated key for curated chemical %s\n",
                        name);
                return null;
            }
        }
    }
}

From source file:capture.MySQLDatabase.java

public void loadInputUrlFromFile(final String inputUrlsFile) {
    Element element;/*from   www. ja  v a 2  s.  co m*/
    Connection con = this.getConnection();
    Statement stmt;
    PreparedStatement ps;
    ResultSet rs;
    String line, url_id, honeypotid = null;
    String operationid = null;
    boolean check = true;
    long count = 0;
    if (inputUrlsFile == null) {
        System.out.println("Error: There is no input-url file!");
        System.exit(1);
    }

    if ((ConfigManager.getInstance().getConfigOption("import_check") != null)
            && (ConfigManager.getInstance().getConfigOption("import_check").toLowerCase().equals("false"))) {
        check = false;
    }

    try {
        stmt = con.createStatement();
        //get honeypot id
        String serverip = ConfigManager.getInstance().getConfigOption("server-listen-address");
        String serverport = ConfigManager.getInstance().getConfigOption("server-listen-port");
        rs = stmt.executeQuery("SELECT honeypot_id FROM honeypot WHERE ipaddress=\'" + serverip + "\'");
        if (rs.next()) {
            honeypotid = rs.getString(1);
        } else {
            //insert a new honeypot 
            stmt.executeUpdate(
                    "INSERT INTO honeypot(ipaddress, port) Values(\'" + serverip + "\', " + serverport + ")",
                    Statement.RETURN_GENERATED_KEYS);
            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                honeypotid = rs.getString(1);
            } else {
                System.out.println("System can't find any honeypot ip=" + serverip);
                System.exit(0);
            }
        }
        setSystemStatus(true);
        //open url file
        BufferedReader in = new BufferedReader(
                new InputStreamReader(new FileInputStream(inputUrlsFile), "UTF-8"));

        //add new operation
        stmt.executeUpdate("INSERT INTO operation(description, honeypot_id) Values (\'" + inputUrlsFile
                + "\', \'" + honeypotid + "\')", Statement.RETURN_GENERATED_KEYS);
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            operationid = rs.getString(1);
            setCurrentOperation(operationid);
        }
        System.out.println("The system is going to inspect urls in the new operation: " + operationid);

        //update visit start time for operation
        SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S");
        String date = sf.format(new Date());
        stmt.executeUpdate("UPDATE operation SET visitstarttime=\'" + date + "\' " + "WHERE operation_id="
                + operationid + " AND visitstarttime IS NULL");

        System.out.println("Please wait for inserting urls into database...");
        if (!check) //NO checking existance of url in database
        {
            while ((line = in.readLine()) != null) {
                if ((line.length() > 0)) {
                    //line = line.trim().toLowerCase();
                    line = line.trim();
                    if (!line.startsWith("#")) {
                        ps = con.prepareStatement("INSERT INTO url(url) Values (?)",
                                Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, line);
                        ps.executeUpdate();
                        rs = ps.getGeneratedKeys();
                        rs.next();
                        url_id = rs.getString(1);
                        stmt.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values (" + url_id
                                + ", " + operationid + ")");

                        element = new Element();
                        element.name = "url";
                        element.attributes.put("add", "");
                        element.attributes.put("id", url_id);
                        element.attributes.put("url", line);
                        EventsController.getInstance().notifyEventObservers(element);
                        count++;
                    }
                }
            }
        } else //checking existance of url in database. Inserting only if no existance
        {
            while ((line = in.readLine()) != null) {
                if ((line.length() > 0)) {
                    //line = line.trim().toLowerCase();
                    line = line.trim();
                    if (!line.startsWith("#")) {
                        ps = con.prepareStatement("SELECT url_id FROM url WHERE url.url = ?");
                        ps.setString(1, line);
                        rs = ps.executeQuery();
                        if (!rs.next()) {
                            ps = con.prepareStatement("INSERT INTO url(url) Values (?)",
                                    Statement.RETURN_GENERATED_KEYS);
                            ps.setString(1, line);
                            ps.executeUpdate();
                            rs = ps.getGeneratedKeys();
                            rs.next();
                            count++;
                        }
                        //check URL id and operation id: not exist
                        url_id = rs.getString(1);
                        ps = con.prepareStatement(
                                "SELECT url_id, operation_id FROM url_operation WHERE url_id = ? AND operation_id= ?");
                        ps.setLong(1, Long.parseLong(url_id));
                        ps.setLong(2, Long.parseLong(operationid));
                        rs = ps.executeQuery();
                        if (!rs.next()) {
                            stmt.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values ("
                                    + url_id + ", " + operationid + ")");
                            element = new Element();
                            element.name = "url";
                            element.attributes.put("add", "");
                            element.attributes.put("id", url_id);
                            element.attributes.put("url", line);
                            EventsController.getInstance().notifyEventObservers(element);
                        }
                    }
                }
            }
        }
        con.close();
        System.out.println("******** IMPORT URLs INTO DATABASE: " + count
                + " URLs have been inserted into database! ********");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

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

/**
 * Helper method to insert the configs from the MConnector into the
 * repository. The job and connector configs within <code>mc</code> will get
 * updated with the id of the configs when this function returns.
 * @param mc The connector to use for updating configs
 * @param conn JDBC connection to use for inserting the configs
 *///from  www .  j a v  a  2  s  . c  o  m
private void insertConfigsForConnector(MConnector mc, Connection conn) {
    long connectorId = mc.getPersistenceId();
    PreparedStatement baseConfigStmt = null;
    PreparedStatement baseInputStmt = null;
    try {
        baseConfigStmt = conn.prepareStatement(STMT_INSERT_INTO_CONFIG, Statement.RETURN_GENERATED_KEYS);

        baseInputStmt = conn.prepareStatement(STMT_INSERT_INTO_INPUT, Statement.RETURN_GENERATED_KEYS);

        // Register link type config
        registerConfigs(connectorId, null /* No direction for LINK type config*/,
                mc.getLinkConfig().getConfigs(), MConfigType.LINK.name(), baseConfigStmt, baseInputStmt, conn);

        // Register both from/to job type config for connector
        if (mc.getSupportedDirections().isDirectionSupported(Direction.FROM)) {
            registerConfigs(connectorId, Direction.FROM, mc.getFromConfig().getConfigs(),
                    MConfigType.JOB.name(), baseConfigStmt, baseInputStmt, conn);
        }
        if (mc.getSupportedDirections().isDirectionSupported(Direction.TO)) {
            registerConfigs(connectorId, Direction.TO, mc.getToConfig().getConfigs(), MConfigType.JOB.name(),
                    baseConfigStmt, baseInputStmt, conn);
        }
    } catch (SQLException ex) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0014, mc.toString(), ex);
    } finally {
        closeStatements(baseConfigStmt, baseInputStmt);
    }

}

From source file:uk.ac.cam.cl.dtg.segue.dos.PgLocationHistory.java

/**
 * Creates a brand new event.//from   ww w . j  a v  a  2 s. c om
 * 
 * @param ipAddress
 *            of interest
 * @param location
 *            geocoded
 * @return a copy of the event.
 * @throws SegueDatabaseException
 *             - if there is a db error.
 * @throws JsonProcessingException
 *             - if we can't parse / serialize the json
 */
private LocationHistoryEvent createNewEvent(final String ipAddress, final Location location)
        throws SegueDatabaseException, JsonProcessingException {
    PreparedStatement pst;
    try (Connection conn = database.getDatabaseConnection()) {
        Date creationDate = new Date();

        PGobject jsonObject = new PGobject();
        jsonObject.setType("jsonb");
        jsonObject.setValue(new ObjectMapper().writeValueAsString(location));

        pst = conn.prepareStatement("INSERT INTO ip_location_history "
                + "(id, ip_address, location_information, created, last_lookup, is_current) "
                + "VALUES (DEFAULT, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

        pst.setString(1, ipAddress);
        pst.setObject(2, jsonObject);
        pst.setTimestamp(3, new java.sql.Timestamp(creationDate.getTime()));
        pst.setTimestamp(4, new java.sql.Timestamp(creationDate.getTime()));
        pst.setBoolean(5, true);

        if (pst.executeUpdate() == 0) {
            throw new SegueDatabaseException("Unable to save location event.");
        }

        try (ResultSet generatedKeys = pst.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                Long id = generatedKeys.getLong(1);
                return new PgLocationEvent(id, ipAddress, location, creationDate, creationDate);
            } else {
                throw new SQLException("Creating location event failed, no ID obtained.");
            }
        }

    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    }
}

From source file:org.ut.biolab.medsavant.server.serverapi.AnnotationManager.java

public static int addAnnotation(String sessID, String prog, String vers, int refID, String path, boolean hasRef,
        boolean hasAlt, int type, boolean endInclusive) throws SQLException, SessionExpiredException {

    LOG.debug("Adding annotation...");

    TableSchema table = MedSavantDatabase.AnnotationTableSchema;
    InsertQuery query = MedSavantDatabase.AnnotationTableSchema.insert(PROGRAM, prog, VERSION, vers,
            REFERENCE_ID, refID, PATH, path, HAS_REF, hasRef, HAS_ALT, hasAlt, TYPE, type, IS_END_INCLUSIVE,
            endInclusive);/*from w  ww.  ja va 2s  .  com*/

    PooledConnection c = ConnectionController.connectPooled(sessID);
    PreparedStatement stmt = c.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);
    stmt.execute();
    ResultSet res = stmt.getGeneratedKeys();
    res.next();

    int annotid = res.getInt(1);

    c.close();

    return annotid;
}

From source file:org.ohmage.query.impl.SurveyUploadQuery.java

@Override
public List<Integer> insertSurveys(final String username, final String client, final String campaignUrn,
        final List<SurveyResponse> surveyUploadList, final Map<UUID, Image> bufferedImageMap,
        final Map<String, Video> videoContentsMap, final Map<String, Audio> audioContentsMap)
        throws DataAccessException {

    List<Integer> duplicateIndexList = new ArrayList<Integer>();
    int numberOfSurveys = surveyUploadList.size();

    // The following variables are used in logging messages when errors occur
    SurveyResponse currentSurveyResponse = null;
    PromptResponse currentPromptResponse = null;
    String currentSql = null;/*  w  w  w.j av a  2 s  . c o  m*/

    List<File> fileList = new LinkedList<File>();

    // Wrap all of the inserts in a transaction 
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("survey upload");
    DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
    TransactionStatus status = transactionManager.getTransaction(def); // begin transaction

    // Use a savepoint to handle nested rollbacks if duplicates are found
    Object savepoint = status.createSavepoint();

    try { // handle TransactionExceptions

        for (int surveyIndex = 0; surveyIndex < numberOfSurveys; surveyIndex++) {

            try { // handle DataAccessExceptions

                final SurveyResponse surveyUpload = surveyUploadList.get(surveyIndex);
                currentSurveyResponse = surveyUpload;
                currentSql = SQL_INSERT_SURVEY_RESPONSE;

                KeyHolder idKeyHolder = new GeneratedKeyHolder();

                // First, insert the survey
                getJdbcTemplate().update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(SQL_INSERT_SURVEY_RESPONSE,
                                Statement.RETURN_GENERATED_KEYS);

                        String locationString = null;
                        Location location = surveyUpload.getLocation();
                        if (location != null) {
                            try {
                                locationString = location.toJson(false, LocationColumnKey.ALL_COLUMNS)
                                        .toString();
                            } catch (JSONException e) {
                                throw new SQLException(e);
                            } catch (DomainException e) {
                                throw new SQLException(e);
                            }
                        }

                        ps.setString(1, surveyUpload.getSurveyResponseId().toString());
                        ps.setString(2, username);
                        ps.setString(3, campaignUrn);
                        ps.setLong(4, surveyUpload.getTime());
                        ps.setString(5, surveyUpload.getTimezone().getID());
                        ps.setString(6, surveyUpload.getLocationStatus().toString());
                        ps.setString(7, locationString);
                        ps.setString(8, surveyUpload.getSurvey().getId());
                        try {
                            ps.setString(9,
                                    surveyUpload
                                            .toJson(false, false, false, false, true, true, true, true, true,
                                                    false, false, true, true, true, true, false, false)
                                            .toString());
                        } catch (JSONException e) {
                            throw new SQLException("Couldn't create the JSON.", e);
                        } catch (DomainException e) {
                            throw new SQLException("Couldn't create the JSON.", e);
                        }
                        ps.setString(10, client);
                        ps.setTimestamp(11, new Timestamp(System.currentTimeMillis()));
                        try {
                            ps.setString(12, surveyUpload.getLaunchContext().toJson(true).toString());
                        } catch (JSONException e) {
                            throw new SQLException("Couldn't create the JSON.", e);
                        }
                        try {
                            ps.setString(13, PreferenceCache.instance()
                                    .lookup(PreferenceCache.KEY_DEFAULT_SURVEY_RESPONSE_SHARING_STATE));
                        } catch (CacheMissException e) {
                            throw new SQLException("Error reading from the cache.", e);
                        }
                        return ps;
                    }
                }, idKeyHolder);

                savepoint = status.createSavepoint();

                final Number surveyResponseId = idKeyHolder.getKey(); // the primary key on the survey_response table for the 
                                                                      // just-inserted survey
                currentSql = SQL_INSERT_PROMPT_RESPONSE;

                // Now insert each prompt response from the survey
                Collection<Response> promptUploadList = surveyUpload.getResponses().values();

                createPromptResponse(username, client, surveyResponseId, fileList, promptUploadList, null,
                        bufferedImageMap, videoContentsMap, audioContentsMap, transactionManager, status);

            } catch (DataIntegrityViolationException dive) { // a unique index exists only on the survey_response table

                if (isDuplicate(dive)) {

                    LOGGER.debug("Found a duplicate survey upload message for user " + username);

                    duplicateIndexList.add(surveyIndex);
                    status.rollbackToSavepoint(savepoint);

                } else {

                    // Some other integrity violation occurred - bad!! All 
                    // of the data to be inserted must be validated before 
                    // this query runs so there is either missing validation 
                    // or somehow an auto_incremented key has been duplicated.

                    LOGGER.error("Caught DataAccessException", dive);
                    logErrorDetails(currentSurveyResponse, currentPromptResponse, currentSql, username,
                            campaignUrn);
                    for (File f : fileList) {
                        f.delete();
                    }
                    rollback(transactionManager, status);
                    throw new DataAccessException(dive);
                }

            } catch (org.springframework.dao.DataAccessException dae) {

                // Some other database problem happened that prevented
                // the SQL from completing normally.

                LOGGER.error("caught DataAccessException", dae);
                logErrorDetails(currentSurveyResponse, currentPromptResponse, currentSql, username,
                        campaignUrn);
                for (File f : fileList) {
                    f.delete();
                }
                rollback(transactionManager, status);
                throw new DataAccessException(dae);
            }

        }

        // Finally, commit the transaction
        transactionManager.commit(status);
        LOGGER.info("Completed survey message persistence");
    }

    catch (TransactionException te) {

        LOGGER.error("failed to commit survey upload transaction, attempting to rollback", te);
        rollback(transactionManager, status);
        for (File f : fileList) {
            f.delete();
        }
        logErrorDetails(currentSurveyResponse, currentPromptResponse, currentSql, username, campaignUrn);
        throw new DataAccessException(te);
    }

    LOGGER.info(
            "Finished inserting survey responses and any associated images into the database and the filesystem.");
    return duplicateIndexList;
}

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

/**
 * @param oldDBConn/*  w w  w.  j  a v a 2 s. com*/
 * @param newDBConn
 * @param disciplineID
 * @return
 */
public static boolean convertKUFishObsData(final Connection oldDBConn, final Connection newDBConn) {
    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdMapperIFace coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog",
            "CollectionObjectCatalogID", false);

    PreparedStatement pStmt1 = null;
    PreparedStatement pStmt2 = null;
    PreparedStatement pStmt3 = null;
    try {
        pStmt1 = newDBConn.prepareStatement(
                "INSERT INTO collectionobjectattribute (Remarks, CollectionMemberID, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pStmt2 = newDBConn.prepareStatement(
                "UPDATE collectionobjectattribute SET Remarks=? WHERE CollectionObjectAttributeID = ?");

        pStmt3 = newDBConn.prepareStatement(
                "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?");

        String sql = " SELECT BiologicalObjectID, Text1, TimestampCreated, TimestampModified FROM observation WHERE Text1 IS NOT NULL AND LENGTH(Text1) > 0";
        Statement stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int oldCOId = rs.getInt(1);
            Integer newCOId = coMapper.get(oldCOId);
            if (newCOId != null) {
                sql = "SELECT CollectionObjectAttributeID, CollectionMemberID FROM collectionobject WHERE CollectionObjectID = "
                        + newCOId;
                Object[] row = BasicSQLUtils.getRow(sql);
                if (row == null || row.length == 0) {
                    log.error("Couldn't get record for  newCOId " + newCOId);
                    continue;
                }

                Integer newCOAId = (Integer) row[0];
                Integer collMemId = (Integer) row[1];

                if (newCOAId != null) // Do Update
                {
                    pStmt2.setString(1, rs.getString(2));
                    pStmt2.setInt(2, newCOAId);
                    pStmt2.executeUpdate();

                } else // Do Insert
                {
                    pStmt1.setString(1, rs.getString(2));
                    pStmt1.setInt(2, collMemId);
                    pStmt1.setTimestamp(3, rs.getTimestamp(3));
                    pStmt1.setTimestamp(4, rs.getTimestamp(4));
                    pStmt1.setInt(5, 1);
                    pStmt1.executeUpdate();
                    newCOAId = BasicSQLUtils.getInsertedId(pStmt1);
                }

                pStmt3.setInt(1, newCOAId);
                pStmt3.setInt(2, newCOId);
                pStmt3.executeUpdate();

            } else {
                log.error("No mapped CO for Obs.BiologicalObjectID " + oldCOId);
            }
        }
        rs.close();
        stmt.close();

        return true;

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

    } finally {
        try {
            if (pStmt1 != null)
                pStmt1.close();
            if (pStmt2 != null)
                pStmt2.close();
            if (pStmt3 != null)
                pStmt3.close();

        } catch (Exception ex) {
        }
    }

    return false;
}

From source file:com.emr.utilities.CSVLoader.java

/**
* Parse CSV file using OpenCSV library and load in 
* given database table. /*w w w.jav a 2 s.  c  o m*/
* @param csvFile {@link String} Input CSV file
* @param tableName {@link String} Database table name to import data
* @param truncateBeforeLoad {@link boolean} Truncate the table before inserting 
*          new records.
 * @param destinationColumns {@link String[]} Array containing the destination columns
*/
public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad, String[] destinationColumns,
        List columnsToBeMapped) throws Exception {
    CSVReader csvReader = null;
    if (null == this.connection) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace,
                "File Error", JOptionPane.ERROR_MESSAGE);
        throw new Exception("Error occured while executing file. " + stacktrace);
    }
    String[] headerRow = csvReader.readNext();

    if (null == headerRow) {
        throw new FileNotFoundException(
                "No columns defined in given CSV file." + "Please check the CSV file format.");
    }
    //Get indices of columns to be mapped
    List mapColumnsIndices = new ArrayList();
    for (Object o : columnsToBeMapped) {
        String column = (String) o;
        column = column.substring(column.lastIndexOf(".") + 1, column.length());
        int i;

        for (i = 0; i < headerRow.length; i++) {

            if (headerRow[i].equals(column)) {
                mapColumnsIndices.add(i);
            }
        }
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);
    questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    query = query.replaceFirst(KEYS_REGEX, StringUtils.join(destinationColumns, ","));
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    String log_query = query.substring(0, query.indexOf("VALUES("));

    String[] nextLine;
    Connection con = null;
    PreparedStatement ps = null;
    PreparedStatement ps2 = null;
    PreparedStatement reader = null;
    ResultSet rs = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        File file = new File("sqlite/db");
        if (!file.exists()) {
            file.createNewFile();
        }
        db = new SQLiteConnection(file);
        db.open(true);

        //if destination table==person, also add an entry in the table person_identifier
        //get column indices for the person_id and uuid columns
        int person_id_column_index = -1;
        int uuid_column_index = -1;
        int maxLength = 100;
        int firstname_index = -1;
        int middlename_index = -1;
        int lastname_index = -1;
        int clanname_index = -1;
        int othername_index = -1;
        if (tableName.equals("person")) {
            int i;
            ps2 = con.prepareStatement(
                    "insert ignore into person_identifier(person_id,identifier_type_id,identifier) values(?,?,?)");
            for (i = 0; i < headerRow.length; i++) {
                if (headerRow[i].equals("person_id")) {
                    person_id_column_index = i;
                }
                if (headerRow[i].equals("uuid")) {
                    uuid_column_index = i;
                }
                /*if(headerRow[i].equals("first_name")){
                    System.out.println("Found firstname index: " + i);
                    firstname_index=i;
                }
                if(headerRow[i].equals("middle_name")){
                    System.out.println("Found firstname index: " + i);
                    middlename_index=i;
                }
                if(headerRow[i].equals("last_name")){
                    System.out.println("Found firstname index: " + i);
                    lastname_index=i;
                }
                if(headerRow[i].equals("clan_name")){
                    System.out.println("Found firstname index: " + i);
                    clanname_index=i;
                }
                if(headerRow[i].equals("other_name")){
                    System.out.println("Found firstname index: " + i);
                    othername_index=i;
                }*/
            }
        }

        if (truncateBeforeLoad) {
            //delete data from table before loading csv
            try (Statement stmnt = con.createStatement()) {
                stmnt.execute("DELETE FROM " + tableName);
                stmnt.close();
            }
        }
        if (tableName.equals("person")) {
            try (Statement stmt2 = con.createStatement()) {
                stmt2.execute(
                        "ALTER TABLE person CHANGE COLUMN first_name first_name VARCHAR(50) NULL DEFAULT NULL AFTER person_guid,CHANGE COLUMN middle_name middle_name VARCHAR(50) NULL DEFAULT NULL AFTER first_name,CHANGE COLUMN last_name last_name VARCHAR(50) NULL DEFAULT NULL AFTER middle_name;");
                stmt2.close();
            }
        }
        final int batchSize = 1000;
        int count = 0;
        Date date = null;

        while ((nextLine = csvReader.readNext()) != null) {

            if (null != nextLine) {
                int index = 1;
                int person_id = -1;
                String uuid = "";
                int identifier_type_id = 3;
                if (tableName.equals("person")) {
                    reader = con.prepareStatement(
                            "select identifier_type_id from identifier_type where identifier_type_name='UUID'");
                    rs = reader.executeQuery();
                    if (!rs.isBeforeFirst()) {
                        //no uuid row
                        //insert it
                        Integer numero = 0;
                        Statement stmt = con.createStatement();
                        numero = stmt.executeUpdate(
                                "insert into identifier_type(identifier_type_id,identifier_type_name) values(50,'UUID')",
                                Statement.RETURN_GENERATED_KEYS);
                        ResultSet rs2 = stmt.getGeneratedKeys();
                        if (rs2.next()) {
                            identifier_type_id = rs2.getInt(1);
                        }
                        rs2.close();
                        stmt.close();
                    } else {
                        while (rs.next()) {
                            identifier_type_id = rs.getInt("identifier_type_id");
                        }
                    }

                }
                int counter = 1;
                String temp_log = log_query + "VALUES("; //string to be logged

                for (String string : nextLine) {
                    //if current index is in the list of columns to be mapped, we apply that mapping
                    for (Object o : mapColumnsIndices) {
                        int i = (int) o;
                        if (index == (i + 1)) {
                            //apply mapping to this column
                            string = applyDataMapping(string);
                        }
                    }
                    if (tableName.equals("person")) {
                        //get person_id and uuid

                        if (index == (person_id_column_index + 1)) {
                            person_id = Integer.parseInt(string);
                        }

                        if (index == (uuid_column_index + 1)) {
                            uuid = string;
                        }

                    }
                    //check if string is a date
                    if (string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4} \\d{2}:\\d{2}:\\d{2}")
                            || string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4}")) {
                        java.sql.Date dt = formatDate(string);
                        temp_log = temp_log + "'" + dt.toString() + "'";
                        ps.setDate(index++, dt);
                    } else {
                        if ("".equals(string)) {
                            temp_log = temp_log + "''";
                            ps.setNull(index++, Types.NULL);
                        } else {
                            temp_log = temp_log + "'" + string + "'";
                            ps.setString(index++, string);
                        }

                    }
                    if (counter < headerRow.length) {
                        temp_log = temp_log + ",";
                    } else {
                        temp_log = temp_log + ");";
                        System.out.println(temp_log);
                    }
                    counter++;
                }
                if (tableName.equals("person")) {
                    if (!"".equals(uuid) && person_id != -1) {
                        ps2.setInt(1, person_id);
                        ps2.setInt(2, identifier_type_id);
                        ps2.setString(3, uuid);

                        ps2.addBatch();
                    }
                }

                ps.addBatch();
            }
            if (++count % batchSize == 0) {
                ps.executeBatch();
                if (tableName.equals("person")) {
                    ps2.executeBatch();
                }
            }
        }
        ps.executeBatch(); // insert remaining records
        if (tableName.equals("person")) {
            ps2.executeBatch();
        }

        con.commit();
    } catch (Exception e) {
        if (con != null)
            con.rollback();
        if (db != null)
            db.dispose();
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace,
                "File Error", JOptionPane.ERROR_MESSAGE);
        throw new Exception("Error occured while executing file. " + stacktrace);
    } finally {
        if (null != reader)
            reader.close();
        if (null != ps)
            ps.close();
        if (null != ps2)
            ps2.close();
        if (null != con)
            con.close();

        csvReader.close();
    }
}

From source file:com.wso2telco.dep.ratecardservice.dao.OperationRateDAO.java

public OperationRateDTO addOperationRate(OperationRateDTO operationRate) throws BusinessException {

    Connection con = null;//  w ww  .  j  av  a 2s .  co  m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer operationRateId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.OPERATION_RATE.getTObject());
        query.append(" (operator_id, api_operationid, rate_defid, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addOperationRate : " + ps);

        Integer operatorId = operationRate.getOperator().getOperatorId();
        if (operatorId != null) {
            ps.setInt(1, operatorId);
        } else {
            ps.setNull(1, Types.INTEGER);
        }

        ps.setInt(2, operationRate.getApiOperation().getApiOperationId());
        ps.setInt(3, operationRate.getRateDefinition().getRateDefId());
        ps.setString(4, operationRate.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            operationRateId = rs.getInt(1);
        }

        operationRate.setOperationRateId(operationRateId);
    } catch (SQLException e) {

        log.error("database operation error in addOperationRate : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addOperationRate : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return operationRate;
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage