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:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//*w  w w  .j  ava2 s  . co m*/
public void insertWikiUser(WikiUser user, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_USER);
            int nextUserId = this.nextWikiUserId(conn);
            user.setUserId(nextUserId);
            stmt.setInt(index++, user.getUserId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_USER_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setString(index++, user.getUsername());
        stmt.setString(index++, user.getDisplayName());
        stmt.setTimestamp(index++, user.getCreateDate());
        stmt.setTimestamp(index++, user.getLastLoginDate());
        stmt.setString(index++, user.getCreateIpAddress());
        stmt.setString(index++, user.getLastLoginIpAddress());
        stmt.setString(index++, user.getEmail());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            user.setUserId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
    // Store user preferences
    Map<String, String> defaults = this.lookupUserPreferencesDefaults(conn);
    Map<String, String> preferences = user.getPreferences();
    try {
        stmt = conn.prepareStatement(STATEMENT_INSERT_USER_PREFERENCE);
        // Only store preferences that are not default
        for (String key : defaults.keySet()) {
            String defVal = defaults.get(key);
            String cusVal = preferences.get(key);
            if (StringUtils.isBlank(cusVal)) {
                user.setPreference(key, defVal);
            } else if (StringUtils.isBlank(defVal) || !defaults.get(key).equals(preferences.get(key))) {
                stmt.setInt(1, user.getUserId());
                stmt.setString(2, key);
                stmt.setString(3, cusVal);
                stmt.executeUpdate();
            }
        }
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

From source file:edu.ku.brc.specify.dbsupport.SpecifySchemaUpdateService.java

/**
 * @param connection//ww w  .  java2s  .  c o m
 */
public void fixDuplicatedPaleoContexts(final Connection conn) {
    String sql = "SELECT PaleoContextID FROM (SELECT pc.PaleoContextID, COUNT(pc.PaleoContextID) cnt "
            + "FROM paleocontext pc INNER JOIN collectionobject co ON pc.PaleoContextID = co.PaleoContextID "
            + "GROUP BY pc.PaleoContextID) T1 WHERE cnt > 1 ";

    String coSQL = "SELECT CollectionObjectID FROM collectionobject WHERE PaleoContextID = ";

    List<String> pcFieldNames = getFieldNamesFromSchema(conn, "paleocontext");
    String fieldStr = buildSelectFieldList(pcFieldNames, null);
    fieldStr = StringUtils.remove(fieldStr, "PaleoContextID, ");

    StringBuilder sb = new StringBuilder("INSERT INTO paleocontext (");
    sb.append(fieldStr);
    sb.append(") SELECT ");
    sb.append(fieldStr);
    sb.append(" FROM paleocontext WHERE PaleoContextID = ?");

    String updateSQL = sb.toString();
    //System.out.println(updateSQL);

    boolean isErr = false;
    PreparedStatement pStmt = null;
    PreparedStatement pStmt2 = null;
    try {
        pStmt = conn.prepareStatement(updateSQL, Statement.RETURN_GENERATED_KEYS);
        pStmt2 = conn
                .prepareStatement("UPDATE collectionobject SET PaleoContextID=? WHERE CollectionObjectID = ?");

        for (Integer pcId : BasicSQLUtils.queryForInts(conn, sql)) {
            Vector<Integer> colObjIds = BasicSQLUtils.queryForInts(conn, coSQL + pcId);
            for (int i = 1; i < colObjIds.size(); i++) {
                pStmt.setInt(1, pcId);
                int rv = pStmt.executeUpdate();
                if (rv == 1) {
                    Integer newPCId = BasicSQLUtils.getInsertedId(pStmt);
                    pStmt2.setInt(1, newPCId);
                    pStmt2.setInt(2, colObjIds.get(i));
                    rv = pStmt2.executeUpdate();
                    if (rv != 1) {
                        log.error("Error updating co " + colObjIds.get(i));
                        isErr = true;
                    }
                } else {
                    log.error("Error updating pc " + pcId);
                    isErr = true;
                }
            }
        }
    } catch (SQLException ex) {
        ex.printStackTrace();
        edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(NavigationTreeMgr.class, ex);
    } finally {
        try {
            if (pStmt != null)
                pStmt.close();
            if (pStmt2 != null)
                pStmt2.close();
        } catch (SQLException ex) {
        }
    }

    if (isErr) {
        UIRegistry
                .showError("There was an error updating the duplicated PaleoContexts\nPlease contact support.");
    }
}

From source file:i5.las2peer.services.mobsos.SurveyService.java

/**
 * Stores a new survey described with JSON into the MobSOS database.
 * The MobSOS database thereby generates a new id returned by this method.
 * @throws ParseException //w ww  .ja  va  2  s  .c  om
 */
private int storeNewSurvey(JSONObject survey) throws IllegalArgumentException, SQLException, ParseException {

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rset = null;

    String sub = (String) getActiveUserInfo().get("sub");

    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement("insert into " + jdbcSchema
                + ".survey(owner, organization, logo, name, description, resource, start, end, lang ) values (?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);

        stmt.clearParameters();
        stmt.setString(1, sub); // active agent becomes owner automatically
        stmt.setString(2, (String) survey.get("organization"));
        stmt.setString(3, (String) survey.get("logo"));
        stmt.setString(4, (String) survey.get("name"));
        stmt.setString(5, (String) survey.get("description"));
        stmt.setString(6, (String) survey.get("resource"));
        stmt.setTimestamp(7,
                new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("start")).getTimeInMillis()));
        stmt.setTimestamp(8,
                new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("end")).getTimeInMillis()));
        stmt.setString(9, (String) survey.get("lang"));

        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys();

        if (rs.next()) {
            return rs.getInt(1);
        } else {
            throw new NoSuchElementException("No new survey was created!");
        }

    } catch (UnsupportedOperationException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rset != null)
                rset.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    throw new NoSuchElementException("No new survey was created!");
}

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

public void importData(String txt, int collectionID) throws ParseException {
    studySummaryCache.clear();//from ww w  . j av  a 2s.  co m
    collectionSummaryCache.clear();

    Collection<Record> rcs = new ArrayList<Record>();
    int cpos = 0;
    int len = txt.length();
    int ln = 0;

    char colSeparator = '\0';

    if (!txt.startsWith(SAMPLE_ID_COL)) {
        if (!txt.startsWith("\"" + SAMPLE_ID_COL + "\""))
            throw new ParseException(1, "The first column must be " + SAMPLE_ID_COL);

        colSeparator = txt.charAt(SAMPLE_ID_COL.length() + 2);
    } else
        colSeparator = txt.charAt(SAMPLE_ID_COL.length());

    if (colSeparator != '\t' && colSeparator != ',')
        throw new ParseException(1, "Column separator must be either tab or comma");

    String sep = "" + colSeparator;

    String lineSep = "\r\n";

    if (txt.indexOf(lineSep) == -1)
        lineSep = "\n";

    if (txt.indexOf(lineSep) == -1)
        throw new ParseException(1, "File must contains at least 2 lines separated by either \\n or \\r\\n ");

    Map<ParameterPart, List<Variant>> tmpVarisMap = new TreeMap<ParameterPart, List<Variant>>();

    //  String qSep = "\""+sep;

    List<String> parts = new ArrayList<String>(100);
    FullPartRef[] pparts = null;
    while (cpos < len) {
        //   ln++;
        //   System.out.println("Line: "+ln);
        int pos = txt.indexOf(lineSep, cpos);

        if (pos == -1)
            break;

        parts.clear();
        StringUtil.splitExcelString(txt.substring(cpos, pos), sep, parts);

        //   String[] parts = txt.substring(cpos, pos).split(sep,-2);

        if (ln == 0) {
            ln++;
            pparts = analyzeHeader(parts);
        } else {
            ln++;
            //    String[] row = new String[header.length];

            if (parts.size() == 0)
                break;

            Record rc = new Record();
            rc.setCollectionId(collectionID);

            for (int i = -1; i < pparts.length; i++) {
                if (i == -1)
                    rc.setCollectionRecordIDs(parts.get(0));
                else {
                    String val = "";

                    if (i + 1 < parts.size())
                        val = parts.get(i + 1).trim();

                    ParameterPart pp = pparts[i].getParameterPart();
                    if (val.length() != 0) {

                        if (pp.isEnum()) {
                            short vid = pp.getVariantIndexByValue(val);
                            // short vid = pparts[i].getParameterPart().getVariantID(val);

                            if (vid == -1) {
                                if (pp.isPredefined())
                                    throw new ParseException(ln,
                                            "Variant '" + val + "' is not allowed for column: '"
                                                    + pparts[i].getParameter().getCode() + '.' + pp.getName()
                                                    + "'");

                                List<Variant> tmpVaris = tmpVarisMap.get(pp);

                                short n = -1;
                                if (tmpVaris == null) {
                                    tmpVaris = new ArrayList<Variant>(5);
                                    tmpVarisMap.put(pp, tmpVaris);
                                } else {
                                    n = 1;
                                    for (Variant v : tmpVaris) {
                                        if (v.getName().equals(val)) {
                                            v.incCount();
                                            break;
                                        }

                                        n++;
                                    }

                                    if (n > tmpVaris.size())
                                        n = -1;
                                }

                                if (n < 0) {
                                    Variant nV = new Variant(val, 0, false);
                                    nV.setId(0);
                                    nV.incCount();

                                    tmpVaris.add(nV);

                                    n = (short) tmpVaris.size();

                                }

                                vid = (short) -n;
                            }

                            VariantPartValue pv = new VariantPartValue(pp);

                            pv.setVariant(vid);
                            rc.addPartValue(pv);

                        } else {
                            if (ParameterPart.SECURED_VARIANT_SIGN.equals(val)) {
                                PartValue pv = new PartValue(pp);
                                rc.addPartValue(pv);
                            } else {
                                Variable vrbl = (Variable) pp;

                                if (vrbl.getType() == Type.REAL) {
                                    float realValue;
                                    try {
                                        realValue = Float.parseFloat(val);
                                    } catch (Exception e) {
                                        throw new ParseException(ln, "Invalid value for REAL type column "
                                                + pparts[i].getParameter().getCode() + '.' + pp.getName());
                                    }

                                    RealPartValue rpv = new RealPartValue(pp);
                                    rpv.setRealValue(realValue);
                                    rc.addPartValue(rpv);
                                } else if (vrbl.getType() == Type.INTEGER || vrbl.getType() == Type.DATE) {
                                    int intValue;
                                    try {
                                        intValue = Integer.parseInt(val);
                                    } catch (Exception e) {
                                        throw new ParseException(ln,
                                                "Invalid value for " + vrbl.getType().name() + " type column "
                                                        + pparts[i].getParameter().getCode() + '.'
                                                        + pp.getName());
                                    }

                                    IntPartValue ipv = new IntPartValue(pp);
                                    ipv.setIntValue(intValue);
                                    rc.addPartValue(ipv);
                                } else if (vrbl.getType() == Type.BOOLEAN) {
                                    boolean boolValue;
                                    try {
                                        boolValue = Boolean.parseBoolean(val);
                                    } catch (Exception e) {
                                        throw new ParseException(ln,
                                                "Invalid value for " + vrbl.getType().name() + " type column "
                                                        + pparts[i].getParameter().getCode() + '.'
                                                        + pp.getName());
                                    }

                                    if ((!boolValue) && "1".equals(val))
                                        boolValue = true;

                                    IntPartValue ipv = new IntPartValue(pp);
                                    ipv.setIntValue(boolValue ? 1 : 0);
                                    rc.addPartValue(ipv);
                                } else
                                    throw new ParseException(ln,
                                            "Invalid value for " + vrbl.getType().name() + " type column "
                                                    + pparts[i].getParameter().getCode() + '.' + pp.getName());
                            }
                        }
                    } else {
                        PartValue pv = new EmptyPartValue(pp);
                        rc.addPartValue(pv);
                    }

                }
            }

            rcs.add(rc);
        }

        cpos = pos + lineSep.length();
    }

    Connection conn = null;
    ResultSet rst = null;
    try {
        conn = dSrc.getConnection();

        PreparedStatement recstmt = conn.prepareStatement("INSERT INTO " + TBL_RECORD + " ("
                + FLD_COLLECTION_RECORD_ID + "," + FLD_COUNT + "," + FLD_COLLECTION_ID + ") VALUES (?,1,?)",
                Statement.RETURN_GENERATED_KEYS);
        PreparedStatement contstmt = conn
                .prepareStatement("INSERT INTO " + TBL_RECORD_CONTENT + " (" + FLD_INT_VALUE + ","
                        + FLD_REAL_VALUE + "," + FLD_RECORD_ID + "," + FLD_PART_ID + ") VALUES (?,?,?,?)");
        PreparedStatement updcontstmt = conn
                .prepareStatement("UPDATE " + TBL_RECORD_CONTENT + " SET " + FLD_INT_VALUE + "=?,"
                        + FLD_REAL_VALUE + "=? WHERE " + FLD_RECORD_ID + "=? AND " + FLD_PART_ID + "=?");
        PreparedStatement rmcontstmt = conn.prepareStatement("DELETE FROM " + TBL_RECORD_CONTENT + " WHERE "
                + FLD_RECORD_ID + "=? AND " + FLD_PART_ID + "=?");
        PreparedStatement insvarstmt = conn
                .prepareStatement(
                        "INSERT INTO " + TBL_VARIANT + " (" + FLD_PART_ID + ',' + FLD_NAME + ','
                                + FLD_VARI_CODING + ',' + FLD_PREDEFINED + ") VALUES (?,?,0,0)",
                        Statement.RETURN_GENERATED_KEYS);

        PreparedStatement delRecStmt = null;
        PreparedStatement delRecContStmt = null;

        for (Record r : rcs) {
            Record exR = findRecord(r);

            int rid = 0;
            if (exR == null) {
                if (r.getPartValues().size() > 0) {
                    recstmt.setString(1, r.getCollectionRecordIDs());
                    recstmt.setInt(2, collectionID);
                    recstmt.executeUpdate();

                    rst = recstmt.getGeneratedKeys();

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

                    r.setId(rid);

                    data.add(r);
                }
            } else {
                rid = exR.getId();

                if (r.getPartValues().size() == 0) {
                    if (delRecStmt == null) {
                        delRecStmt = conn
                                .prepareStatement("DELETE FROM " + TBL_RECORD + " WHERE " + FLD_ID + "=?");
                        delRecContStmt = conn.prepareStatement(
                                "DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=?");
                    }

                    delRecStmt.setInt(1, rid);
                    delRecStmt.executeUpdate();

                    delRecContStmt.setInt(1, rid);
                    delRecContStmt.executeUpdate();

                    data.remove(exR);

                    for (PartValue pv : exR.getPartValues()) {
                        if (pv instanceof VariantPartValue)
                            pv.getPart().uncountVariantByIndex(((VariantPartValue) pv).getVariant());
                    }

                    continue;
                }

            }

            for (PartValue pv : r.getPartValues()) {

                PartValue opv = exR != null ? exR.getPartValue(pv.getPartID()) : null;

                if (pv instanceof EmptyPartValue) {
                    if (opv != null) {
                        exR.removePartValue(opv);

                        rmcontstmt.setInt(1, rid);
                        rmcontstmt.setInt(2, pv.getPartID());

                        rmcontstmt.executeUpdate();

                        if (opv instanceof VariantPartValue)
                            opv.getPart().uncountVariantByIndex(((VariantPartValue) opv).getVariant());

                        if (exR.getPartValues().size() == 0) {
                            if (delRecStmt == null) {
                                delRecStmt = conn.prepareStatement(
                                        "DELETE FROM " + TBL_RECORD + " WHERE " + FLD_ID + "=?");
                                delRecContStmt = conn.prepareStatement(
                                        "DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=?");
                            }

                            delRecStmt.setInt(1, exR.getId());
                            delRecStmt.executeUpdate();

                            delRecContStmt.setInt(1, exR.getId());
                            delRecContStmt.executeUpdate();

                            data.remove(exR);
                            exR = null;
                        }
                    }
                } else {
                    PreparedStatement stmt = null;

                    if (opv != null)
                        stmt = updcontstmt;
                    else
                        stmt = contstmt;

                    stmt.setInt(3, rid);
                    stmt.setInt(4, pv.getPartID());

                    if (pv instanceof IntPartValue) {
                        stmt.setInt(1, ((IntPartValue) pv).getIntValue());
                        stmt.setNull(2, java.sql.Types.FLOAT);
                    } else if (pv instanceof RealPartValue) {
                        stmt.setNull(1, java.sql.Types.INTEGER);
                        stmt.setFloat(2, ((RealPartValue) pv).getRealValue());
                    } else if (pv instanceof VariantPartValue) {
                        short vidx = ((VariantPartValue) pv).getVariant();

                        Variant tv = null;
                        if (vidx < 0) {
                            List<Variant> tmpVaris = tmpVarisMap.get(pv.getPart());

                            tv = tmpVaris.get((-vidx) - 1);

                            if (tv.getId() == 0) {
                                insvarstmt.setInt(1, pv.getPartID());
                                insvarstmt.setString(2, tv.getName());

                                insvarstmt.executeUpdate();

                                ResultSet vrst = insvarstmt.getGeneratedKeys();
                                vrst.next();
                                tv.setId(vrst.getInt(1));

                                vrst.close();

                                short nidx = pv.getPart().addVariant(tv);

                                tv.setCoding(nidx);

                                ((VariantPartValue) pv).setVariant(nidx);
                            } else
                                ((VariantPartValue) pv).setVariant((short) tv.getCoding());
                        } else {
                            tv = pv.getPart().getVariant(vidx);
                            tv.incCount();
                        }

                        stmt.setInt(1, tv.getId());
                        stmt.setNull(2, java.sql.Types.FLOAT);
                    } else {
                        stmt.setNull(1, java.sql.Types.INTEGER);
                        stmt.setNull(2, java.sql.Types.FLOAT);
                    }

                    stmt.executeUpdate();

                    if (opv != null)
                        exR.removePartValue(opv);

                    if (exR != null)
                        exR.addPartValue(pv);
                }
            }

            if (exR != null)
                exR.completeRecord();
            else
                r.completeRecord();

        }

        recstmt.close();
        contstmt.close();
        updcontstmt.close();
        rmcontstmt.close();
        insvarstmt.close();

        for (List<Variant> tvl : tmpVarisMap.values())
            for (Variant tv : tvl)
                tv.setCoding(0);

        Statement stmt = conn.createStatement();
        stmt.executeUpdate("UPDATE " + TBL_COLLECTION + " SET " + FLD_LAST_UPDATE + "="
                + System.currentTimeMillis() + " WHERE " + FLD_ID + "=" + collectionID);
        stmt.close();

        Collections.sort(data, RecordComparator.getIntstance());

        prepareCounts(); // TODO count only parameter with new data

    } catch (SQLException e) {
        Log.error("SQL error", e);
        throw new ParseException(0, "SQL error");
    } catch (Exception e1) {
        logger.error("Data import error", e1);
        throw new ParseException(0, "Unknown error: " + e1.getMessage());
    } 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:i5.las2peer.services.mobsos.SurveyService.java

/**
 * Stores a new questionnaire described with JSON into the MobSOS database.
 * The MobSOS database thereby generates a new id returned by this method.
 * @throws UnsupportedEncodingException 
 * @throws ParseException /*  w  ww.j a v a 2s . c  o m*/
 */
private int storeNewQuestionnaire(JSONObject questionnaire)
        throws IllegalArgumentException, SQLException, UnsupportedEncodingException, ParseException {

    String sub = (String) getActiveUserInfo().get("sub");

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rset = null;

    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement("insert into " + jdbcSchema
                + ".questionnaire(owner, organization, logo, name, description, lang) values (?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);

        stmt.clearParameters();
        stmt.setString(1, sub); // active agent becomes owner automatically
        stmt.setString(2, (String) questionnaire.get("organization"));
        stmt.setString(3, (String) questionnaire.get("logo"));
        stmt.setString(4, (String) questionnaire.get("name"));
        stmt.setString(5, (String) questionnaire.get("description"));
        stmt.setString(6, (String) questionnaire.get("lang"));

        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys();

        if (rs.next()) {
            return rs.getInt(1);
        } else {
            throw new NoSuchElementException("No new questionnaire was created!");
        }

    } catch (UnsupportedOperationException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rset != null)
                rset.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    throw new NoSuchElementException("No new questionnaire was created!");
}

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

public int addStudy(StudyShadow rs) throws StudyManagementException {
    Connection conn = null;/*w  ww . j a v  a2s .c o m*/
    ResultSet rst = null;
    try {

        Collection<Annotation> annots = null;
        if (rs.getAnnotations() != null) {
            annots = new ArrayList<Annotation>(rs.getAnnotations().size());

            for (AnnotationShadow ant : rs.getAnnotations()) {
                Tag t = tags.get(ant.getTag());

                if (t == null)
                    throw new StudyManagementException("Invalid tag ID=" + ant.getTag(),
                            StudyManagementException.INV_TAG_ID);

                Annotation a = new Annotation();
                a.setText(ant.getText());
                a.setTag(t);

                annots.add(a);
            }
        }

        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(insertStudySQL, Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, rs.getName());
        pstmt.setLong(2, System.currentTimeMillis());

        pstmt.executeUpdate();

        rst = pstmt.getGeneratedKeys();

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

        pstmt.close();

        if (rs.getAnnotations() != null) {
            pstmt = conn.prepareStatement(insertStudyAnnotationSQL);

            for (AnnotationShadow ant : rs.getAnnotations()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, ant.getTag());
                pstmt.setString(3, ant.getText());

                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        if (rs.getCollections() != null) {
            pstmt = conn.prepareStatement(insertStudyCollectionsSQL);

            for (int cohId : rs.getCollections()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, cohId);

                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        rs.setId(id);
        studyList.add(rs);

        return id;

    } catch (SQLException e) {
        Log.error("SQL error", e);
        throw new StudyManagementException("SQL error", e, StudyManagementException.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:uk.ac.ebi.sail.server.data.DataManager.java

public int addCollection(CollectionShadow rs) throws CollectionManagementException {
    Connection conn = null;//from   w  ww  . java 2 s  .c o  m
    ResultSet rst = null;
    try {

        Collection<Annotation> annots = null;
        if (rs.getAnnotations() != null) {
            annots = new ArrayList<Annotation>(rs.getAnnotations().size());

            for (AnnotationShadow ant : rs.getAnnotations()) {
                Tag t = tags.get(ant.getTag());

                if (t == null)
                    throw new CollectionManagementException("Invalid tag ID=" + ant.getTag(),
                            CollectionManagementException.INV_TAG_ID);

                Annotation a = new Annotation();
                a.setText(ant.getText());
                a.setTag(t);

                annots.add(a);
            }
        }

        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(insertCollectionSQL, Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, rs.getName());
        pstmt.setLong(2, System.currentTimeMillis());

        pstmt.executeUpdate();

        rst = pstmt.getGeneratedKeys();

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

        pstmt.close();

        if (rs.getAnnotations() != null) {
            pstmt = conn.prepareStatement(insertCollectionAnnotationSQL);

            for (AnnotationShadow ant : rs.getAnnotations()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, ant.getTag());
                pstmt.setString(3, ant.getText());

                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        rs.setId(id);
        collectionList.add(rs);

        SampleCollection r = rs.createCollection();
        r.setAnnotations(annots);
        collections.put(rs.getId(), r);

        return id;

    } catch (SQLException e) {
        Log.error("SQL error", e);
        throw new CollectionManagementException("SQL error", e, CollectionManagementException.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:SeedGenerator.MainForm.java

private void insertNounSynset(NounSynset[] nounSynset, int wordId, String objectName, String tableName) {
    try {//from   w  w  w  .j  a  va 2s.c  om
        for (NounSynset h : nounSynset) {
            for (String s : h.getWordForms()) {
                PreparedStatement existingTerm = con.prepareStatement(
                        "select id from recommender_" + objectName + "_" + tableName + " where term = ?;");
                existingTerm.setString(1, s);
                ResultSet rs = existingTerm.executeQuery();
                int termid = 0;
                if (rs.next()) {
                    termid = rs.getInt("id");
                } else {
                    PreparedStatement insert1 = con.prepareStatement(
                            "insert into recommender_" + objectName + "_" + tableName + " (term) values (?);",
                            Statement.RETURN_GENERATED_KEYS);
                    insert1.setString(1, s);
                    insert1.executeUpdate();

                    try (ResultSet generatedKeys = insert1.getGeneratedKeys()) {
                        if (generatedKeys.next()) {
                            termid = generatedKeys.getInt(1);
                        } else {
                            // throw new SQLException("Creating user failed, no ID obtained.");
                        }
                    }
                }
                PreparedStatement insert = con.prepareStatement("insert into recommender_" + objectName + "_"
                        + tableName + "_word " + " (termid, idfid) values (?,?);");
                insert.setInt(1, termid);
                insert.setInt(2, wordId);
                insert.executeUpdate();
                insert.close();
            }
        }
    } catch (Exception ex) {
        System.out.println(ex.getMessage());
    }
}

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

public String upgradeDB_20090720() {
    StringBuilder log = new StringBuilder();

    Connection conn = null;/*from   w ww  .ja  v a 2 s. co  m*/
    ResultSet rst = null;

    try {
        conn = dSrc.getConnection();
        Statement stmt = conn.createStatement();

        //   stmt.executeUpdate("UPDATE "+TBL_VARIANT+" SET "+FLD_PREDEFINED+"=1");
        //   log.append("Predefined flag is set for variants").append(logEOL);

        stmt.executeUpdate("DELETE FROM " + TBL_VARIANT + " WHERE " + FLD_NAME + "='@'");
        log.append("Anonymous variants have been removed").append(logEOL);

        rst = stmt.executeQuery("SELECT * FROM " + TBL_VARIANT + " ORDER BY " + FLD_PART_ID);

        IntMap<PartVariSet> variMap = new IntTreeMap<PartVariSet>();

        List<Var> varis = new ArrayList<Var>(5);
        PartVariSet partLst = null;

        while (rst.next()) {
            Var v = new Var();

            int ptid = rst.getInt(FLD_PART_ID);

            v.setId(rst.getInt(FLD_ID));
            v.setPtid(ptid);
            v.setCod(rst.getInt(FLD_VARI_CODING));
            v.setName(rst.getString(FLD_NAME));

            log.append("----- Loading variant (ID,Part,Name,Code) (" + v.getId() + ',' + ptid + ","
                    + v.getName() + ',' + v.getCod() + ") -----").append(logEOL);

            partLst = variMap.get(ptid);

            if (partLst == null) {
                partLst = new PartVariSet();
                variMap.put(ptid, partLst);
            }

            partLst.add(v);

            int cptid = 0;

            if (varis.size() > 0)
                cptid = varis.get(0).getPtid();

            if (varis.size() == 0 || cptid == ptid) {
                varis.add(v);
            } else {
                partLst.setMax(updateBlock(conn, varis));
                varis.clear();

                varis.add(v);

                log.append("Variants for Part=" + cptid + " have been precessed. Max code=" + partLst.getMax())
                        .append(logEOL);
            }
        }

        rst.close();

        if (varis.size() > 0) {
            partLst.setMax(updateBlock(conn, varis));
            log.append("Variants for Part=" + varis.get(0).getPtid() + " have been precessed. Max code="
                    + partLst.getMax()).append(logEOL);
        }

        rst = stmt.executeQuery("SELECT * FROM " + TBL_RECORD_CONTENT + " ORDER BY " + FLD_PART_ID);

        ResultSet trst = null;
        PreparedStatement selPartType = conn
                .prepareStatement("SELECT " + FLD_TYPE + " FROM " + TBL_PART + " WHERE " + FLD_ID + "=?");
        PreparedStatement updPartVal = conn.prepareStatement("UPDATE " + TBL_RECORD_CONTENT + " SET "
                + FLD_INT_VALUE + "=? WHERE " + FLD_PART_ID + "=? AND " + FLD_RECORD_ID + "=?");
        PreparedStatement insVariant = conn
                .prepareStatement(
                        "INSERT INTO " + TBL_VARIANT + " (" + FLD_PART_ID + ',' + FLD_NAME + ','
                                + FLD_VARI_CODING + ',' + FLD_PREDEFINED + ") VALUES (?,?,?,0)",
                        Statement.RETURN_GENERATED_KEYS);

        int cptID = -1;
        String partType = null;

        while (rst.next()) {
            int ptID = rst.getInt(FLD_PART_ID);

            if (cptID != ptID) {

                selPartType.setInt(1, ptID);

                trst = selPartType.executeQuery();

                if (!trst.next()) {
                    log.append("Can't find part with ID=" + ptID + " in database").append(logEOL);
                    return log.toString();
                }

                partType = trst.getString(1);

                trst.close();
            }

            if (!("QUALIFIER".equals(partType) || "ENUM".equals(partType)))
                continue;

            String value = rst.getString(FLD_ENUM_VALUE);

            log.append("Processing value for Part=" + ptID + " Value=" + value).append(logEOL);

            if (ParameterPart.SECURED_VARIANT_SIGN.equals(value)) {
                updPartVal.setInt(1, 0);
                updPartVal.setInt(2, ptID);
                updPartVal.setInt(3, rst.getInt(FLD_RECORD_ID));

                updPartVal.executeUpdate();

                log.append("Part value updated. RecordID=" + rst.getInt(FLD_RECORD_ID) + " Part=" + ptID
                        + " Variant=0").append(logEOL);
                continue;
            }

            partLst = variMap.get(ptID);

            if (partLst == null) {
                partLst = new PartVariSet();

                partLst.setMax(0);
                variMap.put(ptID, partLst);
            }

            Var matchV = null;
            for (Var v : partLst) {
                if (v.getName().equals(value)) {
                    matchV = v;
                    break;
                }
            }

            if (matchV == null) {
                log.append("There is no matching variant. Part=" + ptID + " Value=" + value).append(logEOL);

                int ncod = partLst.getNextCoding();

                insVariant.setInt(1, ptID);
                insVariant.setString(2, value);
                insVariant.setInt(3, ncod);

                insVariant.executeUpdate();

                ResultSet krst = insVariant.getGeneratedKeys();

                if (!krst.next()) {
                    log.append("Can't get generated key. Variant name' " + value + "' PartID=" + ptID)
                            .append(logEOL);
                    return log.toString();
                }

                matchV = new Var();
                matchV.setId(krst.getInt(1));
                matchV.setCod(ncod);
                matchV.setName(value);
                matchV.setPtid(ptID);

                krst.close();

                partLst.add(matchV);

                log.append("New variant added. ID=" + matchV.getId() + " Part=" + ptID + " Value=" + value
                        + " Code=" + matchV.getCod()).append(logEOL);

            }

            updPartVal.setInt(1, matchV.getId());
            updPartVal.setInt(2, ptID);
            updPartVal.setInt(3, rst.getInt(FLD_RECORD_ID));

            updPartVal.executeUpdate();

            log.append("Part value updated. RecordID=" + rst.getInt(FLD_RECORD_ID) + " Part=" + ptID
                    + " Variant=" + matchV.getId()).append(logEOL);
        }

    } catch (SQLException e) {
        log.append("SQL error" + e).append(logEOL);
    } 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 log.toString();
}

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

/**
 * //from  w w w  . j a  v  a2 s.c o  m
 */
public void convertHostTaxonId() {
    int total = BasicSQLUtils.getCountAsInt(oldDBConn,
            "SELECT COUNT(*) FROM habitat WHERE HostTaxonID IS NOT NULL");
    if (total > 0) {
        setProcess(0, total);

        String sql = "SELECT h.HabitatID, co.CollectionObjectID FROM habitat h "
                + "INNER JOIN collectingevent ce ON h.HabitatID = ce.CollectingEventID "
                + "INNER JOIN collectionobject co ON ce.CollectingEventID = co.CollectingEventID WHERE h.HostTaxonID IS NOT NULL";

        String lookupSql = "SELECT ca.CollectionObjectAttributeID FROM collectionobject co "
                + "INNER JOIN collectionobjectattribute ca ON co.CollectionObjectAttributeID = ca.CollectionObjectAttributeID WHERE co.CollectionObjectID";

        try {
            IdMapperIFace coMapper = IdMapperMgr.getInstance().get("collectionobject", "CollectionObjectID");
            IdMapperIFace txMapper = IdMapperMgr.getInstance().get("taxonname", "TaxonNameID");

            Statement stmt = oldDBConn.createStatement();
            PreparedStatement updateStmt = newDBConn.prepareStatement(
                    "UPDATE collectionobjectattribute SET RelatedTaxonID=? WHERE CollectionObjectAttributeID = ?");
            PreparedStatement insertStmt = newDBConn.prepareStatement(
                    "INSERT INTO collectionobjectattribute (RelatedTaxonID) VALUES(?)",
                    Statement.RETURN_GENERATED_KEYS);

            int count = 0;
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                setProcess(count++);

                boolean isErr = false;
                Integer newTXId = txMapper.get(rs.getInt(1));
                if (newTXId == null) {
                    String msg = "Couldn't map old Taxon Id " + rs.getInt(1);
                    log.error(msg);
                    isErr = true;
                }
                Integer newCOId = coMapper.get(rs.getInt(2));
                if (newCOId == null) {
                    String msg = "Couldn't map old CO Id " + rs.getInt(2);
                    log.error(msg);
                    isErr = true;
                }

                if (isErr) {
                    continue;
                }

                Integer coAttrId = BasicSQLUtils.getCount(newDBConn, lookupSql + newCOId);
                if (coAttrId == null) {
                    insertStmt.setInt(1, newTXId);

                    if (insertStmt.executeUpdate() != 1) {
                        String msg = "Error inserting CO Attr record for CO Id: " + newCOId + " and TxId: "
                                + newTXId;
                        log.error(msg);
                    }
                    coAttrId = BasicSQLUtils.getInsertedId(insertStmt);
                }

                updateStmt.setInt(1, newTXId);
                updateStmt.setInt(2, newCOId);

                if (updateStmt.executeUpdate() != 1) {
                    String msg = "Error updating CO RelatedTaxonId record for CO Id: " + newCOId + " and TxId: "
                            + newTXId;
                    log.error(msg);
                }
            }

            rs.close();
            stmt.close();

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