Example usage for java.sql Statement RETURN_GENERATED_KEYS

List of usage examples for java.sql Statement RETURN_GENERATED_KEYS

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. RETURN_GENERATED_KEYS

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:uk.ac.ebi.sail.server.data.DataManager.java

public ParameterShadow addParameter(ParameterShadow sp) throws ParameterManagementException {
    Connection conn = null;//from www  .ja v  a  2 s.  c  o  m
    ResultSet rst = null;

    Parameter pr = sp.createParameter();

    if (sp.getInheritedParameters() != null) {
        for (int ipid : sp.getInheritedParameters()) {
            Parameter inhP = params.get(ipid);

            if (inhP == null)
                throw new ParameterManagementException("Invalid inherited parameter ID=" + ipid,
                        ParameterManagementException.INV_INH_PARAMETER_ID);

            pr.addInheritedParameter(inhP);

        }
    }

    if (sp.getTags() != null) {
        for (int tid : sp.getTags()) {
            Tag t = tags.get(tid);

            if (t == null)
                throw new ParameterManagementException("Invalid tag ID=" + tid,
                        ParameterManagementException.INV_TAG_ID);

            pr.addClassificationTag(t);
        }
    }

    if (sp.getAnnotations() != null) {
        for (AnnotationShadow ans : sp.getAnnotations()) {
            Tag t = tags.get(ans.getTag());

            if (t == null)
                throw new ParameterManagementException("Invalid annotation tag ID=" + ans.getTag(),
                        ParameterManagementException.INV_TAG_ID);

            Annotation an = ans.createAnnotation();
            an.setTag(t);

            pr.addAnnotation(an);
        }
    }

    if (sp.getRelations() != null) {
        int i = 1;
        for (int[] rl : sp.getRelations()) {
            Parameter rP = params.get(rl[1]);

            if (rP == null)
                throw new ParameterManagementException("Invalid inherited parameter ID=" + rl[1],
                        ParameterManagementException.INV_INH_PARAMETER_ID);

            Tag t = tags.get(rl[2]);

            if (t == null)
                throw new ParameterManagementException("Invalid tag ID=" + rl[2],
                        ParameterManagementException.INV_TAG_ID);

            rl[0] = i;

            Relation r = new Relation();
            r.setId(i);
            r.setHostParameter(pr);
            r.setTargetParameter(rP);
            r.setTag(t);

            pr.addRelation(r);

            i++;
        }
    }

    try {
        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO " + TBL_PARAMETER + " (" + FLD_CODE + ','
                + FLD_NAME + ',' + FLD_DESCRIPTION + ") VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, sp.getCode());
        pstmt.setString(2, sp.getName());
        pstmt.setString(3, sp.getDesc());

        try {
            pstmt.executeUpdate();
        } catch (SQLIntegrityConstraintViolationException ex) {
            throw new ParameterManagementException("Parameter with code: '" + sp.getCode() + "' already exists",
                    ex, ParameterManagementException.CODE_EXISTS);
        }

        rst = pstmt.getGeneratedKeys();
        int id = -1;

        if (rst.next())
            id = rst.getInt(1);

        pstmt.close();

        if (sp.getVariables() != null || sp.getQualifiers() != null) {
            PreparedStatement vStmt = null;

            pstmt = conn.prepareStatement(insertPartSQL, Statement.RETURN_GENERATED_KEYS);

            if (sp.getVariables() != null) {
                for (Variable v : sp.getVariables()) {
                    pstmt.setInt(1, id);
                    pstmt.setString(2, v.getName());
                    pstmt.setString(3, v.getDescription());
                    pstmt.setString(4, v.getType().name());
                    pstmt.setBoolean(5, v.isPredefined());
                    pstmt.setBoolean(6, v.isMandatory());

                    pstmt.executeUpdate();

                    rst = pstmt.getGeneratedKeys();

                    if (rst.next())
                        v.setId(rst.getInt(1));

                    rst.close();

                    if (v.getType() == Type.ENUM && v.getVariants() != null) {
                        if (vStmt == null)
                            vStmt = conn.prepareStatement(insertVariantSQL, Statement.RETURN_GENERATED_KEYS);

                        for (Variant vr : v.getVariants()) {
                            if (!vr.isPredefined())
                                continue;

                            vStmt.setInt(1, v.getId());
                            vStmt.setString(2, vr.getName());
                            vStmt.setInt(3, vr.getCoding());
                            vStmt.setBoolean(4, vr.isPredefined());

                            vStmt.executeUpdate();

                            rst = vStmt.getGeneratedKeys();

                            if (rst.next())
                                vr.setId(rst.getInt(1));

                            rst.close();
                        }
                    }
                }
            }

            if (sp.getQualifiers() != null) {
                for (Qualifier q : sp.getQualifiers()) {
                    pstmt.setInt(1, id);
                    pstmt.setString(2, q.getName());
                    pstmt.setString(3, q.getDescription());
                    pstmt.setString(4, QUALIFIER_TYPE);
                    pstmt.setBoolean(5, q.isPredefined());
                    pstmt.setBoolean(6, q.isMandatory());

                    pstmt.executeUpdate();

                    rst = pstmt.getGeneratedKeys();

                    if (rst.next())
                        q.setId(rst.getInt(1));

                    rst.close();

                    if (q.getVariants() != null) {
                        if (vStmt == null)
                            vStmt = conn.prepareStatement(insertVariantSQL, Statement.RETURN_GENERATED_KEYS);

                        for (Variant vr : q.getVariants()) {
                            if (!vr.isPredefined())
                                continue;

                            vStmt.setInt(1, q.getId());
                            vStmt.setString(2, vr.getName());
                            vStmt.setInt(3, vr.getCoding());
                            vStmt.setBoolean(4, vr.isPredefined());

                            vStmt.executeUpdate();

                            rst = vStmt.getGeneratedKeys();

                            if (rst.next())
                                vr.setId(rst.getInt(1));

                            rst.close();
                        }
                    }
                }
            }

            if (vStmt != null)
                vStmt.close();

            pstmt.close();
        }

        if (sp.getInheritedParameters() != null) {
            pstmt = conn.prepareStatement("INSERT INTO " + TBL_INHERITED + " (" + FLD_HOST_PARAM_ID + ','
                    + FLD_TARGET_PARAM_ID + ") VALUES (" + id + ",?)");

            for (int ip : sp.getInheritedParameters()) {
                pstmt.setInt(1, ip);
                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        if (sp.getAnnotations() != null) {
            pstmt = conn.prepareStatement(insertParameterAnnotationsSQL);

            for (AnnotationShadow ans : sp.getAnnotations()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, ans.getTag());
                pstmt.setString(3, ans.getText());
                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        if (sp.getTags() != null) {
            pstmt = conn.prepareStatement("INSERT INTO " + TBL_PARAMETER_CLASSIFICATION + " ("
                    + FLD_PARAMETER_ID + ',' + FLD_TAG_ID + ") VALUES (" + id + ",?)");

            for (int t : sp.getTags()) {
                pstmt.setInt(1, t);
                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        if (sp.getRelations() != null) {
            pstmt = conn.prepareStatement(
                    "INSERT INTO " + TBL_RELATION + " (" + FLD_HOST_PARAM_ID + ',' + FLD_TARGET_PARAM_ID + ','
                            + FLD_TAG_ID + ") VALUES (" + id + ",?,?)",
                    PreparedStatement.RETURN_GENERATED_KEYS);

            for (int[] r : sp.getRelations()) {
                pstmt.setInt(1, r[1]);
                pstmt.setInt(2, r[2]);
                pstmt.executeUpdate();

                rst = pstmt.getGeneratedKeys();

                int rlid = -1;

                if (rst.next())
                    rlid = rst.getInt(1);

                for (Relation rl : pr.getRelations()) {
                    if (rl.getId() == r[0]) {
                        rl.setId(rlid);
                        break;
                    }
                }

                r[0] = rlid;
            }

            pstmt.close();
        }

        SSParameterInfo ssp = new SSParameterInfo();

        ssp.setShadow(sp);
        pr.setAuxInfo(ssp);

        pr.setId(id);
        sp.setId(id);

        params.put(id, pr);
        paramCodeMap.put(pr.getCode(), pr);
        paramList.add(sp);

        return sp;
    } catch (SQLException e) {
        logger.error("SQL error", e);
        throw new ParameterManagementException("SQL error: " + e.getMessage(), e,
                ParameterManagementException.SQL_ERROR);
    } finally {
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                Log.error("Connection closing error", e);
            }
        }
    }

}

From source file:org.lockss.subscription.SubscriptionManager.java

/**
 * Adds a subscription to the database.//w w w .j av  a 2  s  .  c  o m
 * 
 * @param conn
 *          A Connection with the database connection to be used.
 * @param publicationSeq
 *          A Long with the identifier of the publication.
 * @param providerSeq
 *          A Long with the identifier of the provider.
 * @return a Long with the identifier of the subscription just added.
 * @throws DbException
 *           if any problem occurred accessing the database.
 */
Long persistSubscription(Connection conn, Long publicationSeq, Long providerSeq) throws DbException {
    final String DEBUG_HEADER = "persistSubscription(): ";
    if (log.isDebug2()) {
        log.debug2(DEBUG_HEADER + "publicationSeq = " + publicationSeq);
        log.debug2(DEBUG_HEADER + "providerSeq = " + providerSeq);
    }

    PreparedStatement insertSubscription = dbManager.prepareStatement(conn, INSERT_SUBSCRIPTION_QUERY,
            Statement.RETURN_GENERATED_KEYS);

    ResultSet resultSet = null;
    Long subscriptionSeq = null;

    try {
        // Skip auto-increment key field #0
        insertSubscription.setLong(1, publicationSeq);
        insertSubscription.setLong(2, providerSeq);
        dbManager.executeUpdate(insertSubscription);
        resultSet = insertSubscription.getGeneratedKeys();

        if (!resultSet.next()) {
            log.error("Unable to create SUBSCRIPTION table row: publicationSeq = " + publicationSeq
                    + ", providerSeq = " + providerSeq + " - No keys were generated.");
            if (log.isDebug2())
                log.debug2(DEBUG_HEADER + "subscriptionSeq = null");
            return null;
        }

        subscriptionSeq = resultSet.getLong(1);
        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "Added subscriptionSeq = " + subscriptionSeq);
    } catch (SQLException sqle) {
        log.error("Cannot insert subscription", sqle);
        log.error("SQL = '" + INSERT_SUBSCRIPTION_QUERY + "'.");
        log.error("publicationSeq = " + publicationSeq);
        log.error("providerSeq = " + providerSeq);
        throw new DbException("Cannot insert subscription", sqle);
    } finally {
        DbManager.safeCloseResultSet(resultSet);
        DbManager.safeCloseStatement(insertSubscription);
    }

    if (log.isDebug2())
        log.debug2(DEBUG_HEADER + "subscriptionSeq = " + subscriptionSeq);
    return subscriptionSeq;
}

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

/**
 * Resolves counter group database id./* ww  w .j  a  v  a 2  s .  co m*/
 *
 * @param group Given group
 * @param conn Connection to database
 * @return Id
 * @throws SQLException
 */
private long getCounterGroupId(CounterGroup group, Connection conn) throws SQLException {
    PreparedStatement select = null;
    PreparedStatement insert = null;
    ResultSet rsSelect = null;
    ResultSet rsInsert = null;

    try {
        select = conn.prepareStatement(STMT_SELECT_COUNTER_GROUP);
        select.setString(1, group.getName());

        rsSelect = select.executeQuery();

        if (rsSelect.next()) {
            return rsSelect.getLong(1);
        }

        insert = conn.prepareStatement(STMT_INSERT_COUNTER_GROUP, Statement.RETURN_GENERATED_KEYS);
        insert.setString(1, group.getName());
        insert.executeUpdate();

        rsInsert = insert.getGeneratedKeys();

        if (!rsInsert.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0013);
        }

        return rsInsert.getLong(1);
    } finally {
        closeResultSets(rsSelect, rsInsert);
        closeStatements(select, insert);
    }
}

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

/**
 * Resolves counter id./*from  ww  w . j a  v a2s  . co m*/
 *
 * @param counter Given counter
 * @param conn Connection to database
 * @return Id
 * @throws SQLException
 */
private long getCounterId(Counter counter, Connection conn) throws SQLException {
    PreparedStatement select = null;
    PreparedStatement insert = null;
    ResultSet rsSelect = null;
    ResultSet rsInsert = null;

    try {
        select = conn.prepareStatement(STMT_SELECT_COUNTER);
        select.setString(1, counter.getName());

        rsSelect = select.executeQuery();

        if (rsSelect.next()) {
            return rsSelect.getLong(1);
        }

        insert = conn.prepareStatement(STMT_INSERT_COUNTER, Statement.RETURN_GENERATED_KEYS);
        insert.setString(1, counter.getName());
        insert.executeUpdate();

        rsInsert = insert.getGeneratedKeys();

        if (!rsInsert.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0013);
        }

        return rsInsert.getLong(1);
    } finally {
        closeResultSets(rsSelect, rsInsert);
        closeStatements(select, insert);
    }
}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public BigInteger getNewSimulationGroupId()
        throws ApolloDatabaseRecordNotInsertedException, ApolloDatabaseException {
    String query = "INSERT INTO simulation_groups VALUES ()";

    try (Connection conn = datasource.getConnection()) {
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pstmt.execute();//from w w  w. ja v  a  2 s. c  o  m
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            return new BigInteger(rs.getString(1));
        } else {
            throw new ApolloDatabaseRecordNotInsertedException(
                    "Unable to create new simulation group, insert failed.");
        }

    } catch (SQLException ex) {
        throw new ApolloDatabaseException("SQLException getting new simulation group ID: " + ex.getMessage());
    }
}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public BigInteger[] addVisualizationRun(RunVisualizationMessage runVisualizationMessage, int md5CollisionId,
        Authentication authentication)/*from  w ww.j  a v  a 2s .c o  m*/
        throws ApolloDatabaseException, ApolloDatabaseRecordNotInsertedException, Md5UtilsException {

    String userName = authentication.getRequesterId();
    String password = authentication.getRequesterPassword();

    String[] userIdTokens = parseUserId(userName);
    userName = userIdTokens[0];

    int userKey = getUserKey(userName, password);

    int softwareKey = getSoftwareIdentificationKey(runVisualizationMessage.getSoftwareIdentification());

    try (Connection conn = datasource.getConnection()) {
        //conn = getConn();
        List<BigInteger> runIds = new ArrayList<>();
        for (RunIdentificationAndLabel runIdentificationAndLabel : runVisualizationMessage
                .getSimulationRunIds()) {
            runIds.add(runIdentificationAndLabel.getRunIdentification());
        }
        BigInteger simulationGroupId = getNewSimulationGroupId();
        addRunIdsToSimulationGroup(simulationGroupId, runIds);

        String query = "INSERT INTO run (md5_hash_of_run_message, software_id, requester_id, last_service_to_be_called, simulation_group_id, md5_collision_id) VALUES (?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, md5Utils.getMd5(runVisualizationMessage));
        pstmt.setInt(2, softwareKey);
        pstmt.setInt(3, userKey);
        pstmt.setInt(4, 4); // 4 is translator
        pstmt.setLong(5, simulationGroupId.longValue());
        pstmt.setInt(6, md5CollisionId);
        pstmt.execute();

        BigInteger runId = null;
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            runId = new BigInteger(rs.getString(1));
        } else {
            throw new ApolloDatabaseRecordNotInsertedException("Record not inserted!");
        }

        // ALSO NEED TO ADD serialized runVisualizationMessage(JSON) to
        // run_data_content table...
        // use insertDataContentForRun for this
        int dataContentKey = addTextDataContent(jsonUtils.getJSONString(runVisualizationMessage));
        int runDataDescriptionId = getRunDataDescriptionId(ContentDataFormatEnum.TEXT, "run_message.json",
                ContentDataTypeEnum.RUN_MESSAGE, 0,
                getSoftwareIdentificationKey(runVisualizationMessage.getSoftwareIdentification()));
        // int runDataId = the following line returns the runDataId, but
        // it's not used at this point.
        associateContentWithRunId(new BigInteger(String.valueOf(runId)), dataContentKey, runDataDescriptionId);
        BigInteger[] runIdSimulationGroupId = new BigInteger[2];
        runIdSimulationGroupId[0] = runId;
        runIdSimulationGroupId[1] = simulationGroupId;
        return runIdSimulationGroupId;
        //  } catch (ClassNotFoundException ex) {
        //     throw new ApolloDatabaseException(
        //            "ClassNotFoundException attempting to add visualization run: "
        //                   + ex.getMessage());
    } catch (SQLException ex) {
        throw new ApolloDatabaseException(
                "SQLException attempting to add visualization run: " + ex.getMessage());
    }
}

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

@Override
public UserCommentGroup createUserCommentGroup(String sessID, int projectId, int refId, String chrom,
        long start_position, long end_position, String ref, String alt)
        throws RemoteException, SQLException, SessionExpiredException, IllegalArgumentException {
    UserCommentGroup lcg = getUserCommentGroup(sessID, projectId, refId, chrom, start_position, end_position,
            ref, alt, true);/*from w  w  w .  jav a 2 s  .  c  o  m*/
    if (lcg != null) {
        throw new IllegalArgumentException("A comment group already exists at chrom=" + chrom + ", start="
                + start_position + " end=" + end_position + " ref=" + ref + " alt=" + alt);
    }

    //insert, get groupId and modDate
    TableSchema lcgTable = MedSavantDatabase.UserCommentGroupTableSchema;
    InsertQuery iq = new InsertQuery(lcgTable.getTable());
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_REFERENCE_ID), refId);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_CHROMOSOME), chrom);
    iq.addColumn(
            MedSavantDatabase.UserCommentGroupTableSchema
                    .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_START_POSITION),
            start_position);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema.getDBColumn(
            MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_END_POSITION), end_position);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_REF), ref);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_ALT), alt);
    PooledConnection conn = ConnectionController.connectPooled(sessID);
    PreparedStatement stmt = null;
    ResultSet res = null;
    int groupId = -1;
    try {
        LOG.info(iq.toString());
        stmt = conn.prepareStatement(iq.toString(), Statement.RETURN_GENERATED_KEYS);
        stmt.execute();
        res = stmt.getGeneratedKeys();
        res.next();
        groupId = res.getInt(1);
    } catch (SQLException sqe) {
        LOG.error("SQL Error ", sqe);
        throw sqe;
    } finally {
        if (conn != null) {
            conn.close();
        }
        if (res != null) {
            res.close();
        }
        if (stmt != null) {
            stmt.close();
        }
    }

    if (groupId < 0) {
        throw new SQLException("Unable to create new group - invalid insertion id");
    }

    //The modification time is the time when a change was made to the last comment.  Since there are 
    //no comments yet, modstamp is null.
    //Date modStamp = DBUtils.getCurrentDatabaseTime(sessID);
    Date modStamp = null;
    lcg = new UserCommentGroup(groupId, projectId, refId, chrom, start_position, end_position, ref, alt,
            modStamp, null);
    return lcg;
}

From source file:uk.ac.ed.epcc.webapp.model.data.Repository.java

/** Default insert operation that uses Generated Keys to 
 * obtain the unique id. Not all DBs support this but it is a good default.
 * //from w w w.j ava 2  s. c om
 * If the id value is greater than zero this is taken as a required id to be inserted.
 * 
 * @param r
 * @return
 * @throws DataFault
 */
protected int insert(Record r) throws DataFault {
    if (READ_ONLY_FEATURE.isEnabled(ctx)) {
        return -1;
    }
    TimerService time = ctx.getService(TimerService.class);
    if (time != null) {
        time.startTimer(getTag() + "-insert");
    }
    int id;
    // Ok, now we should save the object in the database before
    // anything else happens
    StringBuilder query = new StringBuilder("INSERT INTO ");
    addTable(query, true);
    query.append(" (");
    StringBuilder query_values = new StringBuilder(") VALUES (");
    boolean atleastone = false;
    if (r.id > 0) {
        addUniqueName(query, false, true);
        query_values.append('?');
        atleastone = true;
    }
    for (Iterator<String> it = getFields().iterator(); it.hasNext();) {
        String field = it.next();
        FieldInfo info = getInfo(field);
        // as this is an insert we should skip null fields and allow the database default to
        // take precedence
        if (r.get(field) != null) {
            if (atleastone) {
                query.append(", ");
                query_values.append(", ");
            } else {
                atleastone = true;
            }
            info.addName(query, false, true);
            query_values.append('?');

        }
    }
    query.append(query_values.toString());
    query.append(')');
    if (!atleastone) {
        throw new DataFault("Insert with no values");
    }

    try {
        PreparedStatement stmt = sql.getConnection().prepareStatement(query.toString(),
                Statement.RETURN_GENERATED_KEYS);
        int pos = 1;
        if (r.id > 0) {
            stmt.setInt(pos, r.id);
            pos++;
        }
        for (Iterator it = getFields().iterator(); it.hasNext();) {
            String field = (String) it.next();
            if (r.get(field) != null) {
                r.setValue(query, stmt, pos, field);
                pos++;
            }
        }
        if (DatabaseService.LOG_INSERT_FEATURE.isEnabled(getContext())) {
            LoggerService serv = getContext().getService(LoggerService.class);
            if (serv != null) {
                serv.getLogger(getClass()).debug("insert query is " + query.toString());
            }
        }
        int count = stmt.executeUpdate();
        if (time != null) {
            time.stopTimer(getTag() + "-insert");
        }
        if (count != 1) {
            throw new DataFault("Wrong count from INSERT");
        }
        if (r.id > 0) {
            //know the id 
            return r.id;
        }
        if (use_id) {
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            } else {
                throw new DataFault("cannot retrieve auto_key");
            }
            rs.close();
            stmt.close();
            return id;
        } else {
            return 0;
        }
    } catch (SQLException e) {
        throw new DataFault("Insert exception " + query.toString(), e);
    }
}

From source file:uk.ac.ebi.sail.server.data.DataManager.java

public ClassifierShadow addClassifier(ClassifierShadow cs) {
    Connection conn = null;//from  w w  w  .j av a2s  .com
    ResultSet rst = null;

    try {
        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(insertClassifierSQL, Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, cs.getName());
        pstmt.setString(2, cs.getDesc());
        pstmt.setBoolean(3, cs.isAllowMulty());
        pstmt.setBoolean(4, cs.isMandatory());
        pstmt.setString(5, cs.getTarget().name());

        pstmt.executeUpdate();

        rst = pstmt.getGeneratedKeys();
        int id = -1;

        if (rst.next())
            id = rst.getInt(1);

        rst.close();
        pstmt.close();

        if (cs.getTags() != null) {
            pstmt = conn.prepareStatement(insertTagSQL, Statement.RETURN_GENERATED_KEYS);

            for (Tag t : cs.getTags()) {
                pstmt.setString(1, t.getName());
                pstmt.setString(2, t.getDescription());
                pstmt.setInt(3, id);

                pstmt.executeUpdate();

                rst = pstmt.getGeneratedKeys();
                if (rst.next())
                    t.setId(rst.getInt(1));

                rst.close();
            }

            pstmt.close();
        }

        if (cs.getClassificationTags() != null) {
            pstmt = conn.prepareStatement(insertClassifierClassificationSQL);

            for (int tid : cs.getClassificationTags()) {
                Tag t = tags.get(tid);

                if (t == null) {
                    logger.warn("Invalid tag ID=" + tid);
                    continue;
                }

                pstmt.setInt(1, id);
                pstmt.setInt(2, tid);

                pstmt.executeUpdate();
            }
        }

        Classifier nc = cs.createClassifier();
        nc.setId(id);
        cs.setId(id);
        nc.setTags(cs.getTags());

        if (nc.getTags() != null) {
            for (Tag t : nc.getTags())
                tags.put(t.getId(), t);
        }

        classifiers.put(nc.getId(), nc);
        classifiersList.add(nc);

        return cs;
    } catch (SQLException e) {
        logger.error("SQL error", e);
    } finally {
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                Log.error("Connection closing error", e);
            }
        }
    }

    return null;
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from  ww w.j  a  v  a 2s  .  c o m
public void insertTopic(Topic topic, int virtualWikiId, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC);
            int topicId = this.nextTopicId(conn);
            topic.setTopicId(topicId);
            stmt.setInt(index++, topic.getTopicId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setInt(index++, virtualWikiId);
        stmt.setString(index++, topic.getName());
        stmt.setInt(index++, topic.getTopicType().id());
        stmt.setInt(index++, (topic.getReadOnly() ? 1 : 0));
        if (topic.getCurrentVersionId() == null) {
            stmt.setNull(index++, Types.INTEGER);
        } else {
            stmt.setInt(index++, topic.getCurrentVersionId());
        }
        stmt.setTimestamp(index++, topic.getDeleteDate());
        stmt.setInt(index++, (topic.getAdminOnly() ? 1 : 0));
        stmt.setString(index++, topic.getRedirectTo());
        stmt.setInt(index++, topic.getNamespace().getId());
        stmt.setString(index++, topic.getPageName());
        stmt.setString(index++, topic.getPageName().toLowerCase());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            topic.setTopicId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}