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:com.act.lcms.db.model.ScanFile.java

public static ScanFile insertScanFile(DB db, String filename, SCAN_MODE mode, SCAN_FILE_TYPE fileType,
        Integer plateId, Integer plateRow, Integer plateColumn) throws SQLException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(QUERY_INSERT_SCAN_FILE,
            Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, filename, mode, fileType, plateId, plateRow, plateColumn);
        stmt.executeUpdate();//from w  w  w.j av a 2s . c  o  m
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
            if (resultSet.next()) {
                // Get auto-generated id.
                int id = resultSet.getInt(1);
                return new ScanFile(id, filename, mode, fileType, plateId, plateRow, plateColumn);
            } else {
                System.err.format("ERROR: could not retrieve autogenerated key for scan file %s\n", filename);
                return null;
            }
        }
    }
}

From source file:com.act.lcms.db.model.Plate.java

public static Plate insertPlate(DB db, String name, String description, String barcode, String location,
        String plateType, String solvent, Integer temperature, CONTENT_TYPE contentType) throws SQLException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(QUERY_INSERT_PLATE, Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, name, description, barcode, location, plateType, solvent,
                temperature, contentType);
        stmt.executeUpdate();/*from   w  ww  .ja  v  a2  s .  c o  m*/
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
            if (resultSet.next()) {
                // Get auto-generated id.
                int id = resultSet.getInt(1);
                return new Plate(id, name, description, barcode, location, plateType, solvent, temperature,
                        contentType);
            } else {
                System.err.format("ERROR: could not retrieve autogenerated key for plate %s\n", name);
                return null;
            }
        }
    }
}

From source file:at.becast.youploader.database.SQLite.java

public static Boolean updateTemplate(int id, Template template) throws SQLException, IOException {
    PreparedStatement prest = null;
    ObjectMapper mapper = new ObjectMapper();
    String sql = "UPDATE `templates` SET `data`=? WHERE `id`=?";
    prest = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    prest.setString(1, mapper.writeValueAsString(template));
    prest.setInt(2, id);/*from  w ww  . ja v a 2  s  .  co  m*/
    boolean res = prest.execute();
    prest.close();
    return res;
}

From source file:com.nabla.wapp.report.server.ReportManager.java

public int addReport(final Connection conn, final String reportName, @Nullable final String internalName,
        final InputStream design, final InputStream in) throws SQLException, DispatchException {
    // load and scan report design
    if (log.isDebugEnabled())
        log.debug("scanning report " + reportName);
    ReportDesign report;/*  ww  w.  ja v  a  2  s .  com*/
    try {
        report = new Persister().read(ReportDesign.class, design);
    } catch (Exception e) {
        if (log.isErrorEnabled())
            log.error("fail to load report design", e);
        throw new InternalErrorException(Util.formatInternalErrorDescription(e));
    }
    // add report record
    final Integer roleId = getRole(conn, report.getRole());
    if (roleId == null) {
        if (log.isErrorEnabled())
            log.error("invalid role '" + report.getRole() + "' defined for report '" + reportName + "'");
        throw new DispatchException(ReportErrors.REPORT_DESIGN_INVALID_ROLE);
    }
    final String category = report.getCategory();
    if (!reportCategoryValidator.isValid(category)) {
        if (log.isErrorEnabled())
            log.error("invalid category '" + category + "' defined for report ' " + reportName + "'");
        throw new DispatchException(ReportErrors.REPORT_DESIGN_INVALID_CATEGORY);
    }
    final PreparedStatement stmt = conn.prepareStatement(
            "INSERT INTO report (name,internal_name,category,role_id,content) VALUES(?,?,?,?,?);",
            Statement.RETURN_GENERATED_KEYS);
    try {
        stmt.setString(1, report.getTitle());
        if (internalName != null)
            stmt.setString(2, internalName);
        else
            stmt.setNull(2, Types.VARCHAR);
        if (category != null)
            stmt.setString(3, category);
        else
            stmt.setNull(3, Types.VARCHAR);
        stmt.setInt(4, roleId);
        stmt.setBinaryStream(5, in);
        if (log.isDebugEnabled())
            log.debug("uploading report " + reportName);
        if (stmt.executeUpdate() != 1) {
            if (log.isErrorEnabled())
                log.error("failed to add internal report '" + reportName + "'");
            throw new InternalErrorException();
        }
        final ResultSet rsKey = stmt.getGeneratedKeys();
        try {
            rsKey.next();
            return rsKey.getInt(1);
        } finally {
            rsKey.close();
        }
    } finally {
        stmt.close();
    }
}

From source file:org.apache.hadoop.raid.DBUtils.java

public static PreparedStatement getPreparedStatement(Connection conn, String sql, List<Object> sqlParams,
        boolean getGeneratedKeys) throws IOException, SQLException {
    PreparedStatement pstmt = getGeneratedKeys ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
            : conn.prepareStatement(sql);
    int pos = 1;//from  w w  w . ja v a2 s . c  om
    for (Object param : sqlParams) {
        if (param instanceof Integer) {
            pstmt.setInt(pos++, ((Integer) param).intValue());
        } else {
            pstmt.setString(pos++, (String) param);
        }
    }
    return pstmt;
}

From source file:com.jfinal.plugin.activerecord.Model.java

/**
 * Save model.//  ww w  . j a  va 2s. c  om
 */
public boolean save() {
    TableInfo tableInfo = tableInfoMapping.getTableInfo(getClass());

    StringBuilder sql = new StringBuilder();
    List<Object> paras = new ArrayList<Object>();
    DbKit.dialect.forModelSave(tableInfo, attrs, sql, paras);
    // if (paras.size() == 0)   return false;   // The sql "insert into tableName() values()" works fine, so delete this line

    // --------
    Connection conn = null;
    PreparedStatement pst = null;
    int result = 0;
    try {
        conn = DbKit.getConnection();
        if (DbKit.dialect.isOracle())
            pst = conn.prepareStatement(sql.toString(), new String[] { tableInfo.getPrimaryKey() });
        else
            pst = conn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);

        DbKit.dialect.fillStatement(pst, paras);
        // for (int i=0, size=paras.size(); i<size; i++) {
        // pst.setObject(i + 1, paras.get(i));
        // }

        result = pst.executeUpdate();
        // if (isSupportAutoIncrementKey)
        getGeneratedKey(pst, tableInfo); // getGeneratedKey(pst, tableInfo.getPrimaryKey());
        getModifyFlag().clear();
        return result >= 1;
    } catch (Exception e) {
        throw new ActiveRecordException(e);
    } finally {
        DbKit.close(pst, conn);
    }
}

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

/**
 * Executes a prepared INSERT/UPDATE statement on the database and returns a CachedRowSet
 * containing any generated keys.// w w  w. j a  v  a2 s .c  o m
 * 
 * @param expression
 *            The prepared statement to be executed.
 * @param parameters
 *            The parameters for the prepared statement.
 * @return A CachedRowSet containing any generated keys.
 * @throws SQLException
 */
public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression, List<Object> parameters)
        throws SQLException {
    PreparedStatement statement = null;

    try {
        statement = connection.prepareStatement(expression, Statement.RETURN_GENERATED_KEYS);
        logger.debug("executing prepared statement:\n" + expression);

        ListIterator<Object> iterator = parameters.listIterator();

        while (iterator.hasNext()) {
            int index = iterator.nextIndex() + 1;
            Object value = iterator.next();
            logger.debug("adding parameter: index=" + index + ", value=" + value);
            statement.setObject(index, value);
        }

        statement.executeUpdate();
        CachedRowSet crs = new MirthCachedRowSet();
        crs.populate(statement.getGeneratedKeys());
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void create(final Form24 form24) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    final StringBuilder sql = new StringBuilder();
    sql.append(/*from w  w w . j a  v a 2  s.c  o m*/
            "INSERT INTO `form24`(`form24_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,step,industry_name,tax_no,factory_name) ")
            .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?)");

    logger.info("SQL : " + sql.toString());

    String user = "";
    try {
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;

    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            Timestamp currentDate = new Timestamp(System.currentTimeMillis());
            final Industry industry = form24.getIndustry();
            final Factory factory = form24.getFactory();

            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, industry.getIndustryId());
            if (factory.getFactoryId() != null) {
                ps.setLong(2, factory.getFactoryId());
            } else {
                ps.setNull(2, java.sql.Types.BIGINT);
            }
            ps.setTimestamp(3, currentDate);
            ps.setString(4, userName);
            ps.setTimestamp(5, currentDate);
            ps.setString(6, userName);
            ps.setString(7, form24.getStep());
            ps.setString(8, industry.getIndustryName());
            ps.setString(9, industry.getTaxNo());
            ps.setString(10, factory.getFactoryName());
            return ps;
        }

    }, keyHolder);

    final Long returnidform24 = keyHolder.getKey().longValue();
    form24.setForm24Id(returnidform24);
    logger.info("returnidform24 : " + returnidform24);

    //ID PRODUCT
    List<Product> products = form24.getProductList();
    if (products != null) {
        final StringBuilder psql = new StringBuilder();
        psql.append(
                "INSERT INTO `form24_product`(`form24_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
                .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

        logger.info("SQL : " + psql.toString());

        for (final Product p : products) {

            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    Timestamp currentDate = new Timestamp(System.currentTimeMillis());
                    PreparedStatement ps = connection.prepareStatement(psql.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, returnidform24);
                    ps.setString(2, p.getSeq());
                    ps.setString(3, p.getProductName());
                    ps.setString(4, p.getSize());
                    ps.setString(5, p.getBandColor());
                    ps.setString(6, p.getBackgroudColor());
                    ps.setString(7, p.getLicenseNo());
                    ps.setBigDecimal(8, p.getGrossnumber200());
                    ps.setBigDecimal(9, p.getGrossnumber400());
                    ps.setBigDecimal(10, p.getCorkScrap());
                    ps.setBigDecimal(11, p.getTotalScrap());

                    ps.setTimestamp(12, currentDate);
                    ps.setString(13, userName);
                    ps.setTimestamp(14, currentDate);
                    ps.setString(15, userName);
                    return ps;
                }

            }, keyHolder);

            long returnidproduct = keyHolder.getKey().longValue();
            p.setProcuctId(returnidproduct);
            logger.info("returnidproduct : " + returnidproduct);
        }
    }
}

From source file:com.tinyhydra.botd.sql.SQLInterface.java

private boolean AddShop(String id, String name, String url, String vicinity, String reference) {
    boolean addSuccess = false;

    PreparedStatement insertShopStmt = null;

    String insertUserQuery = qINSERT + qINTO + shopsTable + " (" + shopsIdField + "," + shopsNameField + ","
            + shopsUrlField + "," + shopsVicinityField + "," + shopsReferenceField + ") VALUES(?,?,?,?,?)" + qON
            + qDUPLICATE + qKEY + qUPDATE + shopsNameField + qEQUALS_VALUE + "," + shopsUrlField + qEQUALS_VALUE
            + "," + shopsVicinityField + qEQUALS_VALUE + "," + shopsReferenceField + qEQUALS_VALUE;

    try {// w w  w.ja v a 2 s  .  c  om
        if (con == null || con.isClosed())
            con = new SQLConnector().getConnection();
        insertShopStmt = con.prepareStatement(insertUserQuery, Statement.RETURN_GENERATED_KEYS);

        insertShopStmt.setString(1, id);
        insertShopStmt.setString(2, name);
        insertShopStmt.setString(3, url);
        insertShopStmt.setString(4, vicinity);
        insertShopStmt.setString(5, reference);
        insertShopStmt.setString(6, name);
        insertShopStmt.setString(7, url);
        insertShopStmt.setString(8, vicinity);
        insertShopStmt.setString(9, reference);

        insertShopStmt.execute();

        addSuccess = true;

    } catch (SQLException sqe) {
        addSuccess = false;
        System.out.println(sqe);
    } finally {
        closeStatement(insertShopStmt);
    }

    return addSuccess;
}

From source file:org.jenkinsci.plugins.pipeline.maven.dao.PipelineMavenPluginH2Dao.java

protected long getOrCreateBuildPrimaryKey(String jobFullName, int buildNumber) {
    try (Connection cnn = jdbcConnectionPool.getConnection()) {
        cnn.setAutoCommit(false);/* w w  w . j  a v a 2s . c  o m*/

        Long jobPrimaryKey = null;
        try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID FROM JENKINS_JOB WHERE FULL_NAME=?")) {
            stmt.setString(1, jobFullName);
            try (ResultSet rst = stmt.executeQuery()) {
                if (rst.next()) {
                    jobPrimaryKey = rst.getLong(1);
                }
            }
        }
        if (jobPrimaryKey == null) {
            try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO JENKINS_JOB(FULL_NAME) VALUES (?)",
                    Statement.RETURN_GENERATED_KEYS)) {
                stmt.setString(1, jobFullName);
                stmt.execute();
                try (ResultSet rst = stmt.getGeneratedKeys()) {
                    if (rst.next()) {
                        jobPrimaryKey = rst.getLong(1);
                    } else {
                        throw new IllegalStateException();
                    }
                }
            }
        }
        Long buildPrimaryKey = null;
        try (PreparedStatement stmt = cnn
                .prepareStatement("SELECT ID FROM JENKINS_BUILD WHERE JOB_ID=? AND NUMBER=?")) {
            stmt.setLong(1, jobPrimaryKey);
            stmt.setInt(2, buildNumber);
            try (ResultSet rst = stmt.executeQuery()) {
                if (rst.next()) {
                    buildPrimaryKey = rst.getLong(1);
                }
            }
        }

        if (buildPrimaryKey == null) {
            try (PreparedStatement stmt = cnn.prepareStatement(
                    "INSERT INTO JENKINS_BUILD(JOB_ID, NUMBER) VALUES (?, ?)",
                    Statement.RETURN_GENERATED_KEYS)) {
                stmt.setLong(1, jobPrimaryKey);
                stmt.setInt(2, buildNumber);
                stmt.execute();
                try (ResultSet rst = stmt.getGeneratedKeys()) {
                    if (rst.next()) {
                        buildPrimaryKey = rst.getLong(1);
                    } else {
                        throw new IllegalStateException();
                    }
                }
            }
        }
        cnn.commit();
        return buildPrimaryKey;
    } catch (SQLException e) {
        throw new RuntimeSqlException(e);
    }
}