Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.bidtime.dbutils.QueryRunnerEx.java

/**
 * Executes the given batch of INSERT SQL statements.
 * @param conn The connection to use for the query call.
 * @param closeConn True if the connection should be closed, false otherwise.
 * @param sql The SQL statement to execute.
 * @param rsh The handler used to create the result object from
 * the <code>ResultSet</code> of auto-generated keys.
 * @param params The query replacement parameters.
 * @return The result generated by the handler.
 * @throws SQLException If there are database or parameter errors.
 * @since 1.6// w w  w.  j a  va  2  s.c  o  m
 */
private <T> T insertBatch(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh,
        Object[][] params) throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }

    if (sql == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (params == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
    }

    PreparedStatement stmt = null;
    long startTime = System.currentTimeMillis();
    T generatedKeys = null;
    try {
        stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setQueryTimeout(StmtParams.getInstance().getStmtBatchTimeOut());

        for (int i = 0; i < params.length; i++) {
            this.fillStatement(stmt, params[i]);
            stmt.addBatch();
        }
        stmt.executeBatch();
        ResultSet rs = stmt.getGeneratedKeys();
        generatedKeys = rsh.handle(rs);
    } catch (SQLException e) {
        this.rethrow(e, sql, (Object[]) params);
    } finally {
        close(stmt);
        if (closeConn) {
            close(conn);
        }
        if (LogInsertSql.logInfoOrDebug()) {
            LogInsertSql.logFormatTimeNow(startTime, sql, params);
        }
    }
    return generatedKeys;
}

From source file:net.mindengine.oculus.frontend.service.test.JdbcTestDAO.java

@Override
public long create(Test test) throws Exception {
    PreparedStatement ps = getConnection().prepareStatement(
            "insert into tests (name, description, project_id, author_id, date, mapping, group_id, content, automated) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");

    ps.setString(1, test.getName());/*from w w w  . jav a  2s  .com*/
    ps.setString(2, test.getDescription());
    ps.setLong(3, test.getProjectId());
    ps.setLong(4, test.getAuthorId());
    ps.setTimestamp(5, new Timestamp(test.getDate().getTime()));
    ps.setString(6, test.getMapping());
    ps.setLong(7, test.getGroupId());
    ps.setString(8, test.getContent());
    ps.setBoolean(9, test.getAutomated());

    logger.info(ps);
    ps.executeUpdate();

    ResultSet rs = ps.getGeneratedKeys();
    Long testId = 0L;
    if (rs.next()) {
        testId = rs.getLong(1);
    }

    /*
     * Increasing the tests_count value for current project
     */
    update("update projects set tests_count=tests_count+1 where id = :id", "id", test.getProjectId());
    return testId;
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.ProfileDAOImpl.java

public Profile addProfile(Profile profile) throws ProfileManagerDAOException {

    Connection conn;/*from  www . ja  v a 2s  . c o m*/
    PreparedStatement stmt = null;
    ResultSet generatedKeys = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_PROFILE "
                + "(PROFILE_NAME, TENANT_ID, DEVICE_TYPE, CREATED_TIME, UPDATED_TIME) VALUES (?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(query, new String[] { "id" });

        stmt.setString(1, profile.getProfileName());
        stmt.setInt(2, tenantId);
        stmt.setString(3, profile.getDeviceType());
        stmt.setTimestamp(4, profile.getCreatedDate());
        stmt.setTimestamp(5, profile.getUpdatedDate());

        int affectedRows = stmt.executeUpdate();

        if (affectedRows == 0 && log.isDebugEnabled()) {
            String msg = "No rows are updated on the profile table.";
            log.debug(msg);
        }
        generatedKeys = stmt.getGeneratedKeys();

        if (generatedKeys.next()) {
            profile.setProfileId(generatedKeys.getInt(1));
        }
        // Checking the profile id here, because profile id could have been passed from the calling method.
        if (profile.getProfileId() == 0) {
            throw new RuntimeException("Profile id is 0, this could be an issue.");
        }

    } catch (SQLException e) {
        String msg = "Error occurred while adding the profile to database.";
        log.error(msg, e);
        throw new ProfileManagerDAOException(msg, e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, generatedKeys);
    }
    return profile;
}

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

public TaxDTO addTax(TaxDTO tax) throws BusinessException {

    Connection con = null;/* w w w  . jav  a  2 s  .  c om*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer taxId = 0;

    try {

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

            log.error(DBERRORMSG + DataSourceNames.WSO2TELCO_RATE_DB + DBCONERRORMSG);
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.TAX.getTObject());
        query.append(" (taxcode, taxname, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?)");

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

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

        ps.setString(1, tax.getTaxCode());
        ps.setString(2, tax.getTaxName());
        ps.setString(3, tax.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            taxId = rs.getInt(1);
        }

        tax.setTaxId(taxId);
    } catch (SQLException e) {

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

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

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

    return tax;
}

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

public CurrencyDTO addCurrency(CurrencyDTO currency) throws BusinessException {

    Connection con = null;//from   w  w  w .  ja  va  2  s .  c o m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer currencyId = 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.CURRENCY.getTObject());
        query.append(" (currencycode, currencydesc, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?)");

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

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

        ps.setString(1, currency.getCurrencyCode());
        ps.setString(2, currency.getCurrencyDescription());
        ps.setString(3, currency.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            currencyId = rs.getInt(1);
        }

        currency.setCurrencyId(currencyId);
    } catch (SQLException e) {

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

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

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

    return currency;
}

From source file:net.mindengine.oculus.frontend.service.runs.JdbcTestRunDAO.java

@Override
public Long createSuiteRun(SuiteRun suite) throws Exception {
    PreparedStatement ps = getConnection().prepareStatement(
            "insert into suite_runs (start_time, end_time, name, runner_id, parameters, agent_name) "
                    + "values (?,?,?,?,?,?)");

    ps.setTimestamp(1, new Timestamp(suite.getStartTime().getTime()));
    ps.setTimestamp(2, new Timestamp(suite.getEndTime().getTime()));
    ps.setString(3, suite.getName());/*  ww  w  . j av  a 2 s  .  c  om*/
    if (suite.getRunnerId() == null)
        suite.setRunnerId(0L);
    ps.setLong(4, suite.getRunnerId());
    ps.setString(5, suite.getParameters());
    String agentName = suite.getAgentName();
    if (agentName == null) {
        agentName = "";
    }
    ps.setString(6, agentName);

    logger.info(ps);
    ps.execute();

    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        return rs.getLong(1);
    }
    return null;
}

From source file:com.nabla.wapp.server.basic.general.ImportService.java

@Override
public String executeAction(final HttpServletRequest request, final List<FileItem> sessionFiles)
        throws UploadActionException {
    final UserSession userSession = UserSession.load(request);
    if (userSession == null) {
        if (log.isTraceEnabled())
            log.trace("missing user session");
        throw new UploadActionException("permission denied");
    }/*from w  w  w .  j  av  a  2  s .co m*/
    Assert.state(sessionFiles.size() == 1);
    try {
        for (FileItem file : sessionFiles) {
            if (file.isFormField())
                continue;
            if (log.isDebugEnabled()) {
                log.debug("field '" + file.getFieldName() + "': uploading " + file.getName());
                log.debug("field: " + file.getFieldName());
                log.debug("filename: " + file.getName());
                log.debug("content_type: " + file.getContentType());
                log.debug("size: " + file.getSize());
            }
            final Connection conn = db.getConnection();
            try {
                final PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO import_data (field_name, file_name, content_type, length, content, userSessionId) VALUES(?,?,?,?,?,?);",
                        Statement.RETURN_GENERATED_KEYS);
                try {
                    stmt.setString(1, file.getFieldName());
                    stmt.setString(2, file.getName());
                    stmt.setString(3, file.getContentType());
                    stmt.setLong(4, file.getSize());
                    stmt.setString(6, userSession.getSessionId());
                    final InputStream fs = file.getInputStream();
                    try {
                        stmt.setBinaryStream(5, fs);
                        if (stmt.executeUpdate() != 1) {
                            if (log.isErrorEnabled())
                                log.error("failed to add imported file record");
                            throw new UploadActionException("internal error");
                        }
                        final ResultSet rsKey = stmt.getGeneratedKeys();
                        try {
                            rsKey.next();
                            final Integer id = rsKey.getInt(1);
                            if (log.isDebugEnabled())
                                log.debug(
                                        "uploading " + file.getName() + " successfully completed. id = " + id);
                            return id.toString();
                        } finally {
                            rsKey.close();
                        }
                    } finally {
                        fs.close();
                    }
                } catch (IOException e) {
                    if (log.isErrorEnabled())
                        log.error("error reading file " + file.getName(), e);
                    throw new UploadActionException("internal error");
                } finally {
                    Database.close(stmt);
                }
            } finally {
                // remove any orphan import records i.e. older than 48h (beware of timezone!)
                final Calendar dt = Util.dateToCalendar(new Date());
                dt.add(GregorianCalendar.DATE, -2);
                try {
                    Database.executeUpdate(conn, "DELETE FROM import_data WHERE created < ?;",
                            Util.calendarToSqlDate(dt));
                } catch (final SQLException __) {
                }
                Database.close(conn);
            }
        }
    } catch (SQLException e) {
        if (log.isErrorEnabled())
            log.error("error uploading file", e);
        throw new UploadActionException("internal error");
    } finally {
        super.removeSessionFileItems(request);
    }
    return null;
}

From source file:com.softberries.klerk.dao.AddressDao.java

public void create(Address c, QueryRunner run, Connection conn, ResultSet generatedKeys) throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_INSERT_ADDRESS, Statement.RETURN_GENERATED_KEYS);
    st.setString(1, c.getCountry());/*from  w  ww  .  j  a va 2 s.co  m*/
    st.setString(2, c.getCity());
    st.setString(3, c.getStreet());
    st.setString(4, c.getPostCode());
    st.setString(5, c.getHouseNumber());
    st.setString(6, c.getFlatNumber());
    st.setString(7, c.getNotes());
    st.setBoolean(8, c.isMain());
    if (c.getPerson_id().longValue() == 0 && c.getCompany_id().longValue() == 0) {
        throw new SQLException("For Address either Person or Company needs to be specified");
    }
    if (c.getPerson_id().longValue() != 0) {
        st.setLong(9, c.getPerson_id());
    } else {
        st.setNull(9, java.sql.Types.NUMERIC);
    }
    if (c.getCompany_id().longValue() != 0) {
        st.setLong(10, c.getCompany_id());
    } else {
        st.setNull(10, java.sql.Types.NUMERIC);
    }
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_INSERT_ADDRESS);
    }
    generatedKeys = st.getGeneratedKeys();
    if (generatedKeys.next()) {
        c.setId(generatedKeys.getLong(1));
    } else {
        throw new SQLException("Creating address failed, no generated key obtained.");
    }
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.feature.GenericFeatureDAOImpl.java

@Override
public List<ProfileFeature> addProfileFeatures(List<ProfileFeature> features, int profileId)
        throws FeatureManagerDAOException {

    Connection conn;//  w w  w .  ja v  a2s.co  m
    PreparedStatement stmt = null;
    ResultSet generatedKeys = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_PROFILE_FEATURES (PROFILE_ID, FEATURE_CODE, DEVICE_TYPE, CONTENT, "
                + "TENANT_ID) VALUES (?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(query, new String[] { "id" });

        for (ProfileFeature feature : features) {
            stmt.setInt(1, profileId);
            stmt.setString(2, feature.getFeatureCode());
            stmt.setString(3, feature.getDeviceType());
            // if (conn.getMetaData().getDriverName().contains("H2")) {
            //    stmt.setBytes(4, PolicyManagerUtil.getBytes(feature.getContent()));
            // } else {
            stmt.setBytes(4, PolicyManagerUtil.getBytes(feature.getContent()));
            //}
            stmt.setInt(5, tenantId);
            stmt.addBatch();
            //Not adding the logic to check the size of the stmt and execute if the size records added is over 1000
        }
        stmt.executeBatch();

        generatedKeys = stmt.getGeneratedKeys();
        int i = 0;

        while (generatedKeys.next()) {
            features.get(i).setId(generatedKeys.getInt(1));
            i++;
        }

    } catch (SQLException | IOException e) {
        throw new FeatureManagerDAOException("Error occurred while adding the feature list to the database.",
                e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, generatedKeys);
    }
    return features;
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.MonitoringDAOImpl.java

@Override
public int addComplianceDetails(int deviceId, int policyId) throws MonitoringDAOException {
    Connection conn;/*from   w w w. j a v  a  2 s .c  o  m*/
    PreparedStatement stmt = null;
    ResultSet generatedKeys = null;
    Timestamp currentTimestamp = new Timestamp(Calendar.getInstance().getTime().getTime());
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();
    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_POLICY_COMPLIANCE_STATUS (DEVICE_ID, POLICY_ID, STATUS, ATTEMPTS, "
                + "LAST_REQUESTED_TIME, TENANT_ID) VALUES (?, ?, ?,?, ?, ?) ";
        stmt = conn.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
        stmt.setInt(1, deviceId);
        stmt.setInt(2, policyId);
        stmt.setInt(3, 1);
        stmt.setInt(4, 1);
        stmt.setTimestamp(5, currentTimestamp);
        stmt.setInt(6, tenantId);
        stmt.executeUpdate();

        generatedKeys = stmt.getGeneratedKeys();
        if (generatedKeys.next()) {
            return generatedKeys.getInt(1);
        } else {
            return 0;
        }
    } catch (SQLException e) {
        throw new MonitoringDAOException("Error occurred while adding the none compliance to the database.", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, generatedKeys);
    }
}