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:org.traccar.database.QueryBuilder.java

private QueryBuilder(DataSource dataSource, String query, boolean returnGeneratedKeys) throws SQLException {
    this.query = query;
    this.returnGeneratedKeys = returnGeneratedKeys;
    if (query != null) {
        connection = dataSource.getConnection();
        String parsedQuery = parse(query.trim(), indexMap);
        try {//from   w ww  .j  av a 2 s  .  co  m
            if (returnGeneratedKeys) {
                statement = connection.prepareStatement(parsedQuery, Statement.RETURN_GENERATED_KEYS);
            } else {
                statement = connection.prepareStatement(parsedQuery);
            }
        } catch (SQLException error) {
            connection.close();
            throw error;
        }
    }
}

From source file:com.example.getstarted.daos.CloudSqlDao.java

@Override
public Long createBook(Book book) throws SQLException {
    final String createBookString = "INSERT INTO books5 "
            + "(author, createdBy, createdById, description, publishedDate, title, imageUrl) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?)";
    try (Connection conn = dataSource.getConnection();
            final PreparedStatement createBookStmt = conn.prepareStatement(createBookString,
                    Statement.RETURN_GENERATED_KEYS)) {
        createBookStmt.setString(1, book.getAuthor());
        createBookStmt.setString(2, book.getCreatedBy());
        createBookStmt.setString(3, book.getCreatedById());
        createBookStmt.setString(4, book.getDescription());
        createBookStmt.setString(5, book.getPublishedDate());
        createBookStmt.setString(6, book.getTitle());
        createBookStmt.setString(7, book.getImageUrl());
        createBookStmt.executeUpdate();//  w  ww  .ja va 2  s  .c o m
        try (ResultSet keys = createBookStmt.getGeneratedKeys()) {
            keys.next();
            return keys.getLong(1);
        }
    }
}

From source file:org.mskcc.cbio.portal.dao.DaoCnaEvent.java

/**
 * Add new event directly and return the auto increment value.
 * /* w  ww  .  j a  v  a  2 s . co m*/
 * @param cnaEvent
 * @return
 * @throws DaoException 
 */
private static long addCnaEventDirectly(CnaEvent cnaEvent) throws DaoException {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = JdbcUtil.getDbConnection(DaoClinicalAttribute.class);
        pstmt = con.prepareStatement(
                "INSERT INTO cna_event (" + "`ENTREZ_GENE_ID`," + "`ALTERATION` )" + " VALUES(?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pstmt.setLong(1, cnaEvent.getEntrezGeneId());
        pstmt.setShort(2, cnaEvent.getAlteration().getCode());
        pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        rs.next();
        long newId = rs.getLong(1);
        return newId;
    } catch (SQLException e) {
        throw new DaoException(e);
    } finally {
        JdbcUtil.closeAll(DaoClinicalAttribute.class, con, pstmt, rs);
    }
}

From source file:com.travelers.daos.CloudSqlDao.java

@Override
public Long createEmployee(Employee employee) throws SQLException {
    final String createEmployeeString = "INSERT INTO employees "
            + "(createdBy, createdById, employeeLastName, employeeFirstName, phoneNumber, building, location) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?)";
    try (Connection conn = dataSource.getConnection();
            final PreparedStatement createEmployeeStmt = conn.prepareStatement(createEmployeeString,
                    Statement.RETURN_GENERATED_KEYS)) {
        createEmployeeStmt.setString(1, employee.getCreatedBy());
        createEmployeeStmt.setString(2, employee.getCreatedById());
        createEmployeeStmt.setString(3, employee.getEmployeeLastName());
        createEmployeeStmt.setString(4, employee.getEmployeeFirstName());
        createEmployeeStmt.setString(5, employee.getPhoneNumber());
        createEmployeeStmt.setString(6, employee.getBuilding());
        createEmployeeStmt.setString(7, employee.getLocation());
        createEmployeeStmt.executeUpdate();
        try (ResultSet keys = createEmployeeStmt.getGeneratedKeys()) {
            keys.next();//from w ww  . j ava 2s. com
            return keys.getLong(1);
        }
    }
}

From source file:com.javacodegags.waterflooding.model.CaptionImplemented.java

@Override
public int insertCaption(final String name) {
    final String sql = "INSERT INTO caption (name,argument) VALUES (?,1);";
    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override/*from ww  w . j  a  va2 s  .c o  m*/
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, name);
            return ps;
        }
    }, holder);
    return holder.getKey().intValue();
}

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

@Override
public int saveUser(final TUserBO user) throws SQLException {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final String sql = "insert into T_SCS_USER(" + "ID,ACCOUNT,PWD,NAME," + "DEPT_ID,ROLE_ID,EMAIL,PHONE,"
            + "MOBILE,FAX,POSITION,STATE," + "COMMENT,CHECK_CODE,IS_AUTO_APPROVE,CREATOR_USER_ID,"
            + "CREATE_DT,LASTUPDATE_DT,COMP_ID) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
    try {//  w  w  w.ja  v  a  2  s  . c om
        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.getPhone());
                ps.setString(i++, user.getMobile());
                ps.setString(i++, user.getFax());
                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());
                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.device.mgt.core.dao.impl.DeviceDAOImpl.java

@Override
public int addDevice(int typeId, Device device, int tenantId) throws DeviceManagementDAOException {
    Connection conn;/*from  ww  w.ja v  a2s  . c  om*/
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int deviceId = -1;
    try {
        conn = this.getConnection();
        String sql = "INSERT INTO DM_DEVICE(DESCRIPTION, NAME, DEVICE_TYPE_ID, DEVICE_IDENTIFICATION, TENANT_ID, GROUP_ID) "
                + "VALUES (?, ?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, device.getDescription());
        stmt.setString(2, device.getName());
        stmt.setInt(3, typeId);
        stmt.setString(4, device.getDeviceIdentifier());
        stmt.setInt(5, tenantId);
        if (device.getGroupId() == 0) {
            stmt.setNull(6, java.sql.Types.INTEGER);
        } else {
            stmt.setInt(6, device.getGroupId());
        }
        stmt.executeUpdate();

        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            deviceId = rs.getInt(1);
        }
        return deviceId;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException(
                "Error occurred while enrolling device '" + device.getName() + "'", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:com.rplt.studioMusik.dataPersewaan.PersewaanStudioMusikDAO.java

@Override
public void simpanData(PersewaanStudioMusik pPersewaanStudioMusik) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    //        String kode = getGeneratedKodeSewa();

    String sql = "insert into PERSEWAAN_STUDIO_MUSIK "
            + "values(?, ?, ?, ?, to_date(?, 'dd-MON-yyyy HH24:MI'), to_date(?, 'dd-MON-yyyy HH24:MI'), ?)";

    final String sqlmysql = "INSERT INTO `studiomusik`.`persewaan_studio_musik` "
            + "(`KODE_STUDIO`, `NAMA_PENYEWA`, `NOMOR_TELEPON`, `MULAI_SEWA`, `SELESAI_SEWA`, `BIAYA_PELUNASAN`) "
            + "VALUES (?, ?, ?, STR_TO_DATE(?, '%d-%b-%Y %k:%S'), " + "STR_TO_DATE(?, '%d-%b-%Y %k:%S'), ?)";

    System.out.println(sql);//  w w  w .j a  v a  2  s .  c  o  m

    jdbcTemplate.update(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection cnctn) throws SQLException {
            PreparedStatement ps = cnctn.prepareStatement(sqlmysql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, pPersewaanStudioMusik.getmKodeStudio());
            ps.setString(2, pPersewaanStudioMusik.getmNamaPenyewa());
            ps.setString(3, pPersewaanStudioMusik.getmNomorTeleponPenyewa());
            ps.setString(4, pPersewaanStudioMusik.getmMulaiSewa());
            ps.setString(5, pPersewaanStudioMusik.getmSelesaiSewa());
            ps.setInt(6, pPersewaanStudioMusik.getmBiayaPelunasan());
            return ps;
        }
    }, keyHolder);

    //        jdbcTemplate.update(sql,
    //                new Object[]{
    //                    kode,
    //                    pPersewaanStudioMusik.getmKodeStudio(),
    //                    pPersewaanStudioMusik.getmNamaPenyewa(),
    //                    pPersewaanStudioMusik.getmNomorTeleponPenyewa(),
    //                    pPersewaanStudioMusik.getmMulaiSewa(),
    //                    pPersewaanStudioMusik.getmSelesaiSewa(),
    //                    pPersewaanStudioMusik.getmBiayaPelunasan()
    //                });
    System.out.println(pPersewaanStudioMusik.getmMulaiSewa() + "sd" + pPersewaanStudioMusik.getmSelesaiSewa());

    pPersewaanStudioMusik.setmKodeSewa(keyHolder.getKey().toString());

    System.out.println("KEYHOLDER : " + pPersewaanStudioMusik.getmKodeSewa());

    System.out.println(pPersewaanStudioMusik.getmKodeSewa());

}

From source file:net.pms.database.TableThumbnails.java

/**
 * Attempts to find a thumbnail in this table by MD5 hash. If not found,
 * it writes the new thumbnail to this table.
 * Finally, it writes the ID from this table as the THUMBID in the FILES
 * table./* www.j  a  v a2s  .com*/
 *
 * @param thumbnail
 * @param fullPathToFile
 */
public static void setThumbnail(final DLNAThumbnail thumbnail, final String fullPathToFile) {
    String query;
    String md5Hash = DigestUtils.md5Hex(thumbnail.getBytes(false));
    try (Connection connection = database.getConnection()) {
        query = "SELECT * FROM " + TABLE_NAME + " WHERE MD5 = " + sqlQuote(md5Hash) + " LIMIT 1";
        LOGGER.trace("Searching for thumbnail in {} with \"{}\" before update", TABLE_NAME, query);

        TABLE_LOCK.writeLock().lock();
        try (PreparedStatement statement = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE, Statement.RETURN_GENERATED_KEYS)) {
            connection.setAutoCommit(false);
            try (ResultSet result = statement.executeQuery()) {
                if (result.next()) {
                    LOGGER.trace(
                            "Found existing file entry with ID {} in {}, setting the THUMBID in the FILES table",
                            result.getInt("ID"), TABLE_NAME);

                    // Write the existing thumbnail ID to the FILES table
                    PMS.get().getDatabase().updateThumbnailId(fullPathToFile, result.getInt("ID"));
                } else {
                    LOGGER.trace("File entry \"{}\" not found in {}", md5Hash, TABLE_NAME);
                    result.moveToInsertRow();
                    result.updateString("MD5", md5Hash);
                    result.updateObject("THUMBNAIL", thumbnail);
                    result.updateTimestamp("MODIFIED", new Timestamp(System.currentTimeMillis()));
                    result.insertRow();

                    try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                        if (generatedKeys.next()) {
                            // Write the new thumbnail ID to the FILES table
                            LOGGER.trace(
                                    "Inserting new thumbnail with ID {}, setting the THUMBID in the FILES table",
                                    generatedKeys.getInt(1));
                            PMS.get().getDatabase().updateThumbnailId(fullPathToFile, generatedKeys.getInt(1));
                        }
                    }
                }
            } finally {
                connection.commit();
            }
        } finally {
            TABLE_LOCK.writeLock().unlock();
        }
    } catch (SQLException e) {
        LOGGER.error("Database error while writing \"{}\" to {}: {}", md5Hash, TABLE_NAME, e.getMessage());
        LOGGER.trace("", e);
    }
}

From source file:com.app.dao.SearchQueryDAO.java

@Caching(evict = { @CacheEvict(value = "searchQueries", key = "#searchQuery.userId + 'true'"),
        @CacheEvict(value = "searchQueries", key = "#searchQuery.userId + 'false'") })
public int addSearchQuery(SearchQuery searchQuery) throws DatabaseConnectionException, SQLException {

    _log.debug("Adding new searchQuery with keywords: {}", searchQuery.getKeywords());

    try (Connection connection = DatabaseUtil.getDatabaseConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(_ADD_ADVANCED_SEARCH_QUERY_SQL,
                    Statement.RETURN_GENERATED_KEYS)) {

        _populateAddSearchQueryPreparedStatement(preparedStatement, searchQuery);

        preparedStatement.executeUpdate();

        ResultSet resultSet = preparedStatement.getGeneratedKeys();

        resultSet.next();/*from  w  ww.  j av  a 2s. c o  m*/

        return resultSet.getInt(1);
    }
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage