Example usage for java.sql Statement RETURN_GENERATED

Introduction

In this page you can find the example usage for java.sql Statement RETURN_GENERATED_KEYS.

Prototype

int RETURN_GENERATED_KEYS

To view the source code for java.sql Statement RETURN_GENERATED_KEYS.

Click Source Link

Document

The constant indicating that generated keys should be made available for retrieval.

Usage

From source file:uk.ac.cam.cl.dtg.segue.dao.users.PgUserGroupPersistenceManager.java

@Override
public void addUserToGroup(final Long userId, final Long groupId) throws SegueDatabaseException {
    try (Connection conn = database.getDatabaseConnection()) {
        PreparedStatement pst;//w  w w.j  a  v a  2 s  .c  o  m
        pst = conn.prepareStatement(
                "INSERT INTO group_memberships(group_id, user_id, created) VALUES (?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);
        pst.setLong(1, groupId);
        pst.setLong(2, userId);
        pst.setTimestamp(3, new Timestamp(new Date().getTime()));

        int affectedRows = pst.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("Creating linked account record failed, no rows changed");
        }

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

From source file:Database.Handler.java

public int insertQuery(String sql, String[] para, Class outputClass) {
    PreparedStatement ps;//from   www .  ja  va  2  s  .c o m
    try {
        Connection c = DriverManager.getConnection(DbConfig.connectionString, DbConfig.dbUserName,
                DbConfig.dbPassword);
        ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < para.length; i++) {
            ps.setString(i + 1, para[i]);
        }
        System.out.println(ps);
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return -1;
    } catch (SQLException ex) {
        System.out.println("Handler insertQuery error:" + ex.getMessage());
        return -1;
    }
}

From source file:com.adanac.module.blog.dao.CommentDao.java

public Integer save(final Integer articleId, final String visitorIp, final Date commentDate,
        final String content, final String username, final String resourceUsername, final String resourceId,
        final Integer referenceCommentId) {
    return execute(new TransactionalOperation<Integer>() {
        @Override/*  w  ww . j a v a 2s .c o m*/
        public Integer doInConnection(Connection connection) {
            try {
                PreparedStatement statement = null;
                if (referenceCommentId == null) {
                    statement = connection.prepareStatement(
                            "insert into comments (visitor_ip,city,content,article_id,"
                                    + "create_date,username,resource_username,resource_id) values (?,?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                } else {
                    statement = connection.prepareStatement(
                            "insert into comments (visitor_ip,city,content,article_id,"
                                    + "create_date,username,resource_username,resource_id,reference_comment_id) values (?,?,?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                }
                statement.setString(1, visitorIp);
                statement.setString(2,
                        Configuration.isProductEnv() ? HttpApiHelper.getCity(visitorIp) : "?");
                statement.setString(3, content);
                statement.setInt(4, articleId);
                Date finalCommentDate = commentDate;
                if (commentDate == null) {
                    finalCommentDate = new Date();
                }
                statement.setTimestamp(5, new Timestamp(finalCommentDate.getTime()));
                statement.setString(6, username);
                statement.setString(7, resourceUsername);
                statement.setString(8, resourceId);
                if (referenceCommentId != null) {
                    statement.setInt(9, referenceCommentId);
                }
                int result = statement.executeUpdate();
                if (result > 0) {
                    ResultSet resultSet = statement.getGeneratedKeys();
                    if (resultSet.next()) {
                        return resultSet.getInt(1);
                    }
                }
            } catch (SQLException e) {
                error("save comments failed ...", e);
            }
            return null;
        }
    });
}

From source file:utils.DBManager.java

public int updateGroup(int group, String newTitle, List<Integer> users, int owner, boolean chiuso,
        boolean privato) throws SQLException {
    int rowChanged;
    String sql = "UPDATE groups SET groupname = ? WHERE groupid = ?";
    PreparedStatement stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    stm.setString(1, newTitle);// www .  jav  a  2s  . c om
    stm.setInt(2, group);
    stm.executeUpdate();

    stm.close();

    sql = "UPDATE groups SET private = ? WHERE groupid = ?";
    stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    int priv = calculatePrivateColumnInt(chiuso, privato);
    stm.setInt(1, priv);
    stm.setInt(2, group);
    stm.executeUpdate();

    stm.close();

    ArrayList<Integer> usersInDb = getAllUserIDs();
    PreparedStatement stm2;

    String sql2;
    for (int id : users) {
        //int id = getIdFromUser(user);
        if (!isPending(id, group) && !isKicked(id, group) && !isInGroup(id, group)) {
            sql2 = "INSERT INTO user_groups(userid,groupid,status) VALUES (?,?,2)";
            stm2 = con.prepareStatement(sql2);
            stm2.setInt(1, id);
            stm2.setInt(2, group);
            stm2.executeUpdate();
            stm2.close();
        } else if (isKicked(id, group)) {
            sql2 = "UPDATE user_groups SET status = 2 WHERE groupid = ? AND userid = ?";
            stm2 = con.prepareStatement(sql2);
            stm2.setInt(1, group);
            stm2.setInt(2, id);
            stm2.executeUpdate();
            stm2.close();
        }
    }

    usersInDb.removeAll(users);

    for (int id : usersInDb) {
        sql2 = "UPDATE user_groups SET status = 1 WHERE groupid = ? AND userid = ?";
        stm2 = con.prepareStatement(sql2);
        stm2.setInt(1, group);
        stm2.setInt(2, id);
        stm2.executeUpdate();
        stm2.close();
    }

    String sql4 = "UPDATE user_groups SET status = 0 WHERE groupid = ? AND userid = ?";
    PreparedStatement stm4 = con.prepareStatement(sql4);
    stm4.setInt(1, group);
    stm4.setInt(2, owner);
    rowChanged = stm4.executeUpdate();
    stm4.close();
    return rowChanged;

}

From source file:com.adanac.module.blog.dao.RecordDao.java

public Integer saveOrUpdate(String id, String title, String username, String html, String content) {
    return execute(new TransactionalOperation<Integer>() {
        @Override/*from www.j  a  v  a2  s.c o m*/
        public Integer doInConnection(Connection connection) {
            String insertSql = "insert into records (title,username,create_date,"
                    + "record,content) values (?,?,?,?,?)";
            String updateSql = "update records set title=?,username=?,create_date=?,record=?,content=? where id=?";
            try {
                PreparedStatement statement = null;
                if (StringUtils.isBlank(id)) {
                    statement = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                    statement.setString(1, title);
                    statement.setString(2, username);
                    statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
                    statement.setString(4, html);
                    statement.setString(5, content);
                } else {
                    statement = connection.prepareStatement(updateSql);
                    statement.setString(1, title);
                    statement.setString(2, username);
                    statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
                    statement.setString(4, html);
                    statement.setString(5, content);
                    statement.setInt(6, Integer.valueOf(id));
                }
                int result = statement.executeUpdate();
                if (result > 0 && StringUtils.isBlank(id)) {
                    ResultSet keyResultSet = statement.getGeneratedKeys();
                    if (keyResultSet.next()) {
                        return keyResultSet.getInt(1);
                    }
                }
                if (result > 0) {
                    return Integer.valueOf(id);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return null;
        }
    });
}

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

@Override
public int saveSelfcaerUser(final TUserBO user) throws SQLException {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final String sql = "insert into T_SCS_USER(" + "ID,ACCOUNT,PWD," + "DEPT_ID,ROLE_ID,EMAIL,"
            + "POSITION,STATE," + "COMMENT,CHECK_CODE,IS_AUTO_APPROVE,CREATOR_USER_ID,"
            + "CREATE_DT,LASTUPDATE_DT,COMP_ID,MOBILE) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
    try {/*from   www .  ja va 2 s. com*/
        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()));
                ps.setInt(i++, user.getCompId());
                ps.setString(i++, user.getMobile());
                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:org.wso2.carbon.la.database.internal.LADatabaseService.java

@Override
public int createLogGroup(LogGroup logGroup) throws DatabaseHandlerException {
    Connection connection = null;
    PreparedStatement createLogGroupStatement = null;
    int tenantId = logGroup.getTenantId();
    String username = logGroup.getUsername();
    String logGroupName = logGroup.getName();

    if (getLogGroup(logGroup.getName(), tenantId, username) != null) {
        throw new DatabaseHandlerException(String.format("Log Group [name] %s already exists.", logGroupName));
    }//from w w  w.jav a2  s  .c o m
    try {
        connection = dbh.getDataSource().getConnection();
        connection.setAutoCommit(false);
        createLogGroupStatement = connection.prepareStatement(SQLQueries.CREATE_LOG_GROUP,
                Statement.RETURN_GENERATED_KEYS);
        createLogGroupStatement.setString(1, logGroupName);
        createLogGroupStatement.setInt(2, tenantId);
        createLogGroupStatement.setString(3, username);
        int affectedRow = createLogGroupStatement.executeUpdate();
        connection.commit();

        if (affectedRow == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = createLogGroupStatement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                if (logger.isDebugEnabled()) {
                    logger.debug("Successfully created log group: " + logGroupName);
                }
                return generatedKeys.getInt(1);
            } else {
                throw new SQLException("Log Group creation failed, no ID obtained.");
            }
        }
    } catch (SQLException e) {
        LADatabaseUtils.rollBack(connection);
        throw new DatabaseHandlerException("Error occurred while inserting details of log group: "
                + logGroupName + " to the database: " + e.getMessage(), e);
    } finally {
        // enable auto commit
        LADatabaseUtils.enableAutoCommit(connection);
        // close the database resources
        LADatabaseUtils.closeDatabaseResources(connection, createLogGroupStatement);
    }
}

From source file:fr.paris.lutece.portal.business.mail.MailItemQueueDAO.java

/**
 * Insert a new mail item in the table.//  w  ww  .j av  a2 s . c o  m
 * 
 * @param mailItemQueue
 *            the mail item
 */
@Override
public synchronized void insert(MailItemQueue mailItemQueue) {
    try {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ObjectOutputStream objectOutputStream;
        objectOutputStream = new ObjectOutputStream(byteArrayOutputStream);
        objectOutputStream.writeObject(mailItemQueue.getMailItem());
        objectOutputStream.close();
        byteArrayOutputStream.close();

        TransactionManager.beginTransaction(null);

        try {
            DAOUtil daoUtil = new DAOUtil(SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS);
            daoUtil.executeUpdate();
            daoUtil.nextGeneratedKey();
            int nNewPrimaryKey = daoUtil.getGeneratedKeyInt(1);
            mailItemQueue.setIdMailItemQueue(nNewPrimaryKey);
            daoUtil.free();
            daoUtil = new DAOUtil(SQL_QUERY_INSERT_MAIL_ITEM);
            daoUtil.setInt(1, nNewPrimaryKey);
            daoUtil.setBytes(2, byteArrayOutputStream.toByteArray());
            daoUtil.executeUpdate();
            daoUtil.free();
            TransactionManager.commitTransaction(null);
        } catch (Exception e) {
            TransactionManager.rollBack(null);
            AppLogService.error(e);
        }
    } catch (Exception e) {
        AppLogService.error(e);
    }
}

From source file:org.dcache.chimera.H2FsSqlDriver.java

@Override
long createTagInode(int uid, int gid, int mode, byte[] value) {
    final String CREATE_TAG_INODE_WITH_VALUE = "INSERT INTO t_tags_inodes (imode, inlink, iuid, igid, isize, "
            + "ictime, iatime, imtime, ivalue) VALUES (?,1,?,?,?,?,?,?,?)";

    Timestamp now = new Timestamp(System.currentTimeMillis());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int rc = _jdbc.update(con -> {
        PreparedStatement ps = con.prepareStatement(CREATE_TAG_INODE_WITH_VALUE,
                Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, mode | UnixPermission.S_IFREG);
        ps.setInt(2, uid);/*w w w.ja v a2 s  .  c o  m*/
        ps.setInt(3, gid);
        ps.setLong(4, value.length);
        ps.setTimestamp(5, now);
        ps.setTimestamp(6, now);
        ps.setTimestamp(7, now);
        ps.setBinaryStream(8, new ByteArrayInputStream(value), value.length);
        return ps;
    }, keyHolder);
    if (rc != 1) {
        throw new JdbcUpdateAffectedIncorrectNumberOfRowsException(CREATE_TAG_INODE_WITH_VALUE, 1, rc);
    }
    /* H2 uses weird names for the column with the auto-generated key, so we cannot use the code
     * in the base class.
     */
    return (Long) keyHolder.getKey();
}

From source file:com.sf.ddao.factory.DefaultStatementFactory.java

public PreparedStatement createStatement(Context context, boolean returnGeneratedKeys)
        throws StatementFactoryException {
    String stmt = null;/*ww w .  ja  v  a 2  s.  c o m*/
    try {
        stmt = createText(context);
        log.debug("Created statement:{}, applying parameters: {}", stmt, refParametersList);
        final Connection connection = ConnectionHandlerHelper.getConnection(context);
        PreparedStatement preparedStatement;
        if (returnGeneratedKeys) {
            preparedStatement = connection.prepareStatement(stmt, Statement.RETURN_GENERATED_KEYS);
        } else {
            preparedStatement = connection.prepareStatement(stmt);
        }
        int i = 1;
        for (ParameterHandler parameter : refParametersList) {
            i += parameter.bindParam(preparedStatement, i, context);
        }
        return preparedStatement;
    } catch (Exception e) {
        if (stmt == null) {
            stmt = stmtTokens.toString();
        }
        throw new StatementFactoryException("Failed to prepare statement '" + stmt + "'", e);
    }
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage