Example usage for java.sql Statement 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:com.amazonbird.announce.ProductMgrImpl.java

public Product addProduct(Product product) {
    Connection connection = null;
    PreparedStatement ps = null;/*ww w.  j a va  2s.  c  om*/

    ResultSet rs = null;

    try {
        connection = dbMgr.getConnection();
        ps = connection.prepareStatement(ADD_PRODUCT, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, product.getName());
        ps.setDouble(2, product.getPrice());
        ps.setString(3, product.getDestination());
        ps.setString(4, product.getAlternativeDestionation());
        ps.setString(5, product.getLocale());
        ps.setLong(6, product.getAnnouncerId());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();
        if (rs.next()) {
            long productId = rs.getLong(1);
            product.setId(productId);
        }

        logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString());
    } catch (MySQLIntegrityConstraintViolationException e) {

        logger.error("Error: " + e.getMessage() + "\nProduct:" + product.toString());
    } catch (SQLException ex) {
        logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex);
    } finally {
        dbMgr.closeResources(connection, ps, rs);
    }
    return product;
}

---

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

public TaxDTO addTax(TaxDTO tax) throws BusinessException {

    Connection con = null;/*  ww  w.j  a  va  2 s .co  m*/
    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:org.wandora.modules.GenericDatabaseInterface.java

public int update(String query, Rows[] generatedKeys) throws SQLException {
    Connection connection = connectionPool.getConnection();
    if (connection == null)
        throw new SQLException("Couldn't get connection from the connection pool");
    Statement stmt = null;/*from   www  . j  ava2 s  .c  om*/
    ResultSet rs = null;
    try {
        stmt = connection.createStatement();
        if (generatedKeys != null && generatedKeys.length > 0) {
            int ret = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            rs = stmt.getGeneratedKeys();
            generatedKeys[0] = makeRows(rs);
            return ret;
        } else {
            return stmt.executeUpdate(query);
        }

    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException ignore) {
            }
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException ignore) {
            }
        if (connection != null)
            try {
                connection.close();
            } catch (SQLException ignore) {
            }
    }

    /*
    int retries=connectionRetries;
    while(true){
    try{
        Statement stmt=connection.createStatement();
        try{
            if(generatedKeys!=null && generatedKeys.length>0){
                int ret=stmt.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
                ResultSet rs=stmt.getGeneratedKeys();
                generatedKeys[0]=makeRows(rs);
                return ret;
            }
            else {
                return stmt.executeUpdate(query);
            }
        }
        finally{ stmt.close(); }
    }catch(SQLException sqle){
        retries--;
        if(retries<0) throw sqle;
        else {
            if(retries<connectionRetries-1) try{Thread.sleep(10000);}catch(InterruptedException ie){}
            reconnect();
        }
    }
    }
    */
}

---

From source file:utils.DBManager.java

public int newGroup(String title, String[] users, int owner, boolean isPrivate) throws SQLException {

    String sql = "INSERT into GROUPS(ownerid,groupname,creationdate,private)" + "VALUES (?,?,?,?)";
    PreparedStatement stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    stm.setInt(1, owner);// w  ww.  j  a  va  2  s .  c  om
    stm.setString(2, title);
    stm.setString(3, "" + new Date().getTime());
    stm.setInt(4, isPrivate ? 0 : 1); //se e' privato setta a 1 altrimenti 0
    stm.executeUpdate();
    PreparedStatement stmaux = con.prepareStatement("SELECT last_insert_rowid()");
    int groupid = -1;
    ResultSet res = stmaux.executeQuery();
    if (res.next()) {
        groupid = res.getInt(1);
    }
    res.close();
    stm.close();

    String sql2;

    if (users != null) {
        sql2 = "INSERT INTO user_groups(userid,groupid,status) VALUES (?,?,2)";
        for (String mUser : users) {
            int aux = getIdFromUser(mUser);
            PreparedStatement stm2 = con.prepareStatement(sql2);
            stm2.setInt(1, aux);
            stm2.setInt(2, groupid);
            stm2.executeUpdate();
            stm2.close();
        }
    }
    sql2 = "INSERT INTO user_groups(userid,groupid,status) VALUES (?,?,0)";
    PreparedStatement stm2 = con.prepareStatement(sql2);
    stm2.setInt(1, owner);
    stm2.setInt(2, groupid);
    stm2.executeUpdate();
    stm2.close();

    String g = StringEscapeUtils.escapeHtml(title);
    insertPost(owner, groupid, "Creation of group " + g);

    return groupid;

}

---

From source file:org.wso2.carbon.social.db.adapter.GenericQueryAdapter.java

@Override
public PreparedStatement getInsertCommentActivityPreparedStatement(Connection connection, String json,
        String targetId, String userId, String tenantDomain, int totalLikes, int totalUnlikes, int timeStamp)
        throws SQLException {
    PreparedStatement commentStatement;

    if (log.isDebugEnabled()) {
        log.debug(preparedStatementMsg + INSERT_COMMENT_SQL + " with following parameters, json: " + json
                + " targetId: " + targetId + " userId: " + userId + " tenantDomain: " + tenantDomain);
    }/*from ww  w . j  a  va 2s . c o m*/

    commentStatement = connection.prepareStatement(INSERT_COMMENT_SQL, Statement.RETURN_GENERATED_KEYS);
    commentStatement.setString(1, json);
    commentStatement.setString(2, targetId);
    commentStatement.setString(3, userId);
    commentStatement.setString(4, tenantDomain);
    commentStatement.setInt(5, totalLikes);
    commentStatement.setInt(6, totalUnlikes);
    commentStatement.setInt(7, timeStamp);

    return commentStatement;

}

---

From source file:Crawler.CrawlerClass.java

public void InsertToContentDb(DBConnection Conn, String url, String Description, String Title, String Content,
        String Extension) throws SQLException, IOException {

    String sql = "select * from contentdb where URL = '" + url + "'";
    ResultSet rs = Conn.executeStatement(sql);

    if (!rs.next()) {
        //store the URL to database to avoid parsing again
        //sql = "INSERT INTO  `contentdb` " + "(`URL`, ) VALUES " + "(?);";
        sql = "INSERT INTO `contentdb`(`URL`, `Description`, `Title`, `Content_description`, `Page_extension`) "
                + "VALUES(?,?,?,?,?);";
        PreparedStatement stmt = Conn.conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, url);//from w w  w.j  a  va 2  s  .c o m
        stmt.setString(2, Description);
        stmt.setString(3, Title);
        stmt.setString(4, Content);
        stmt.setString(5, Extension);
        stmt.execute();
    }
}

---

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

@Override
protected void create(Connection connection, Domain d) {
    ArgumentNotValid.checkNotNull(d, "d");
    ArgumentNotValid.checkNotNullOrEmpty(d.getName(), "d.getName()");

    if (exists(connection, d.getName())) {
        String msg = "Cannot create already existing domain " + d;
        log.debug(msg);//from  ww  w. j av a 2  s.  co m
        throw new PermissionDenied(msg);
    }

    PreparedStatement s = null;
    log.debug("trying to create domain with name: " + d.getName());
    try {
        connection.setAutoCommit(false);
        s = connection.prepareStatement(
                "INSERT INTO domains " + "(name, comments, defaultconfig, crawlertraps, edition,"
                        + " alias, lastaliasupdate ) " + "VALUES ( ?, ?, -1, ?, ?, ?, ? )",
                Statement.RETURN_GENERATED_KEYS);
        // Id is autogenerated
        // defaultconfig cannot exist yet, so we put in -1
        // until we have configs
        DBUtils.setName(s, 1, d, Constants.MAX_NAME_SIZE);
        DBUtils.setComments(s, 2, d, Constants.MAX_COMMENT_SIZE);
        s.setString(3, StringUtils.conjoin("\n", d.getCrawlerTraps()));
        long initialEdition = 1;
        s.setLong(4, initialEdition);
        AliasInfo aliasInfo = d.getAliasInfo();
        DBUtils.setLongMaybeNull(s, 5,
                aliasInfo == null ? null
                        : DBUtils.selectLongValue(connection, "SELECT domain_id FROM domains WHERE name = ?",
                                aliasInfo.getAliasOf()));
        DBUtils.setDateMaybeNull(s, 6, aliasInfo == null ? null : aliasInfo.getLastChange());
        s.executeUpdate();

        d.setID(DBUtils.getGeneratedID(s));
        s.close();

        Iterator<Password> passwords = d.getAllPasswords();
        while (passwords.hasNext()) {
            Password p = passwords.next();
            insertPassword(connection, d, p);
        }

        Iterator<SeedList> seedlists = d.getAllSeedLists();
        if (!seedlists.hasNext()) {
            String msg = "No seedlists for domain " + d;
            log.debug(msg);
            throw new ArgumentNotValid(msg);
        }
        while (seedlists.hasNext()) {
            SeedList sl = seedlists.next();
            insertSeedlist(connection, d, sl);
        }

        Iterator<DomainConfiguration> dcs = d.getAllConfigurations();
        if (!dcs.hasNext()) {
            String msg = "No configurations for domain " + d;
            log.debug(msg);
            throw new ArgumentNotValid(msg);
        }
        while (dcs.hasNext()) {
            DomainConfiguration dc = dcs.next();
            insertConfiguration(connection, d, dc);

            // Create xref tables for seedlists referenced by this config
            createConfigSeedlistsEntries(connection, d, dc);

            // Create xref tables for passwords referenced by this config
            createConfigPasswordsEntries(connection, d, dc);
        }

        // Now that configs are defined, set the default config.
        s = connection.prepareStatement("UPDATE domains SET defaultconfig = "
                + "(SELECT config_id FROM configurations " + "WHERE configurations.name = ? "
                + "AND configurations.domain_id = ?) " + "WHERE domain_id = ?");
        DBUtils.setName(s, 1, d.getDefaultConfiguration(), Constants.MAX_NAME_SIZE);
        s.setLong(2, d.getID());
        s.setLong(3, d.getID());
        s.executeUpdate();
        s.close();
        for (Iterator<HarvestInfo> hi = d.getHistory().getHarvestInfo(); hi.hasNext();) {
            insertHarvestInfo(connection, d, hi.next());
        }

        for (DomainOwnerInfo doi : d.getAllDomainOwnerInfo()) {
            insertOwnerInfo(connection, d, doi);
        }

        saveExtendedFieldValues(connection, d);

        connection.commit();
        d.setEdition(initialEdition);
    } catch (SQLException e) {
        String message = "SQL error creating domain " + d + " in database" + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        DBUtils.rollbackIfNeeded(connection, "creating", d);
    }
}

---

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

@Override
public int saveSelfcaerUserInfo(final TUserBO user) throws SQLException {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final String sql = "insert into T_SCS_COMPANY_USER(" + "ID,ACCOUNT,PWD," + "DEPT_ID,ROLE_ID,EMAIL,"
            + "POSITION,STATE," + "COMMENT,CHECK_CODE,IS_AUTO_APPROVE,CREATOR_USER_ID,"
            + "CREATE_DT,LASTUPDATE_DT) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
    try {//from ww w  .ja v  a 2 s . co m
        this.getJdbcTemplate().update(new PreparedStatementCreator() {
            int i = 1;

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(i++, user.getId());
                ps.setString(i++, user.getAccount());
                ps.setString(i++, user.getPwd());
                //               ps.setString(i++, user.getName());
                ps.setInt(i++, user.getDeptId());
                ps.setInt(i++, user.getRoleId());
                ps.setString(i++, user.getEmail());
                ps.setString(i++, user.getPosition());
                ps.setInt(i++, user.getState());
                ps.setString(i++, user.getComment());
                ps.setString(i++, user.getCheckCode());
                ps.setInt(i++, user.getIsAutoApprove());
                ps.setInt(i++, user.getCreatorUserId());
                ps.setTimestamp(i++, new Timestamp(user.getCreateDt().getTime()));
                //update by CQ
                ps.setTimestamp(i++, new Timestamp(user.getLastupdateDt().getTime()));
                return ps;
            }
        }, keyHolder);
    } catch (Exception e) {
        throw new SQLException("??" + user.getComment() + " ID"
                + user.getCreatorUserId() + " " + user.getCreateDt() + " "
                + e.getMessage());
    }
    return keyHolder.getKey().intValue();
}

---

From source file:oscar.form.FrmONAREnhancedRecord.java

int addRecord(Properties props, String table, List<String> namesA, Integer id) throws SQLException {
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO " + table + " (");
    for (String name : namesA) {
        sb.append(name.split("\\|")[0] + ",");
    }//  www . j av  a  2  s .  com
    sb.deleteCharAt(sb.length() - 1);

    sb.append(") VALUES (");

    for (String name : namesA) {
        sb.append("?,");
    }
    sb.deleteCharAt(sb.length() - 1);
    sb.append(");");

    PreparedStatement preparedStmt = null;
    try {
        preparedStmt = DbConnectionFilter.getThreadLocalDbConnection().prepareStatement(sb.toString(),
                Statement.RETURN_GENERATED_KEYS);

        for (int x = 0; x < namesA.size(); x++) {
            String t = namesA.get(x);
            String theName = t.split("\\|")[0];
            String type = t.split("\\|")[1];

            if (theName.equals("ID")) {
                if (id == null) {
                    preparedStmt.setNull(x + 1, Types.INTEGER);
                } else {
                    preparedStmt.setInt(x + 1, id.intValue());
                }
                continue;
            }

            if (type.equals("VARCHAR") || type.equals("CHAR")) {
                String value = props.getProperty(theName);
                if (value == null) {
                    preparedStmt.setNull(x + 1, getType(type));
                } else {
                    preparedStmt.setString(x + 1, value);
                }
            } else if (type.equals("INT") || type.equals("TINYINT")) {
                String value = props.getProperty(theName);
                if (value != null && value.isEmpty()) {
                    MiscUtils.getLogger().info("empty value for " + theName);
                }
                if (value == null || value.isEmpty()) {
                    value = "0";
                } else if (value.equalsIgnoreCase("on") || value.equalsIgnoreCase("checked='checked'")) {
                    value = "1";
                }
                preparedStmt.setInt(x + 1, Integer.parseInt(value));
            } else if (type.equals("DATE")) {
                String value = props.getProperty(theName);
                Date d = null;

                if (theName.equalsIgnoreCase("formEdited")) {
                    d = new Date();
                } else {
                    if ((value == null) || (value.indexOf('/') != -1))
                        d = UtilDateUtilities.StringToDate(value, dateFormat);
                    else
                        d = UtilDateUtilities.StringToDate(value, _newDateFormat);
                }
                if (d == null)
                    preparedStmt.setNull(x + 1, Types.DATE);
                else
                    preparedStmt.setDate(x + 1, new java.sql.Date(d.getTime()));

            } else if (type.equals("TIMESTAMP")) {
                Date d;
                if (theName.equalsIgnoreCase("formEdited")) {
                    d = new Date();
                } else {
                    d = UtilDateUtilities.StringToDate(props.getProperty(theName), "yyyyMMddHHmmss");
                }
                if (d == null)
                    preparedStmt.setNull(x + 1, Types.TIMESTAMP);
                else
                    preparedStmt.setTimestamp(x + 1, new java.sql.Timestamp(d.getTime()));

            } else {
                MiscUtils.getLogger().error("missing type handler for this column " + theName, new Exception());
            }

        }

        preparedStmt.executeUpdate();

        if (id == null) {
            ResultSet rs = null;
            try {
                rs = preparedStmt.getGeneratedKeys();

                if (rs.next()) {
                    id = rs.getInt(1);
                }
            } finally {
                if (rs != null)
                    rs.close();
            }

        }
    } finally {
        if (preparedStmt != null) {
            preparedStmt.close();
        }
    }

    return id;
}

---

From source file:com.mirth.connect.server.util.DatabaseConnection.java

public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression) throws SQLException {
    Statement statement = null;//from www.  ja  va2s.  c o  m

    try {
        statement = connection.createStatement();
        logger.debug("executing update:\n" + expression);
        statement.executeUpdate(expression, Statement.RETURN_GENERATED_KEYS);
        CachedRowSetImpl crs = new CachedRowSetImpl();
        crs.populate(statement.getGeneratedKeys());
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• »