Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:com.amazonbird.announce.ProductMgrImpl.java

public Product addProduct(Product product) {
    Connection connection = null;
    PreparedStatement ps = null;

    ResultSet rs = null;//from  w w w . java 2s .c o m

    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.mirth.connect.server.util.DatabaseConnection.java

public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression, List<Object> parameters)
        throws SQLException {
    PreparedStatement statement = null;

    try {//  w  w  w .j ava 2 s  .c  om
        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();
        CachedRowSetImpl crs = new CachedRowSetImpl();
        crs.populate(statement.getGeneratedKeys());
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

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

@Override
public UserGroup createGroup(final UserGroup group) throws SegueDatabaseException {
    try (Connection conn = database.getDatabaseConnection()) {
        PreparedStatement pst;
        pst = conn.prepareStatement("INSERT INTO groups(group_name, owner_id, created)" + " VALUES (?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);
        pst.setString(1, group.getGroupName());
        pst.setLong(2, group.getOwnerId());

        if (group.getCreated() != null) {
            pst.setTimestamp(3, new Timestamp(group.getCreated().getTime()));
        } else {/*from w ww.  jav  a2 s .  com*/
            pst.setTimestamp(3, new Timestamp(new Date().getTime()));
        }

        if (pst.executeUpdate() == 0) {
            throw new SegueDatabaseException("Unable to save group.");
        }

        try (ResultSet generatedKeys = pst.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                Long id = generatedKeys.getLong(1);
                group.setId(id);

            } else {
                throw new SQLException("Creating group failed, no ID obtained.");
            }
        }

        return group;

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

From source file:com.vigglet.util.ModelUtilBase.java

protected T insertOrUpdate(T model, List<Object> values) {
    T result = null;//from  w w w  .  j a  v a2  s .co  m

    try {
        PreparedStatement stmt = null;
        if (model.getId() > 0) {
            stmt = getUpdateStatement();
        } else {
            stmt = getInsertStatement();
        }

        int i = 1;
        for (Object obj : values) {
            stmt.setObject(i++, obj);
        }

        if (model.getId() > 0) {
            stmt.setInt(i++, model.getId());
        }

        stmt.executeUpdate();

        int id = 0;
        if (model.getId() > 0) {
            id = model.getId();
        } else {
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            }
            rs.close();
        }

        result = findById(id);

        stmt.close();
    } catch (SQLException ex) {
        logger.log(Level.SEVERE, null, ex);
    }

    return result;
}

From source file:org.codesearch.commons.database.DBAccessImpl.java

/**
 * {@inheritDoc}//from w  w w . ja  v a  2s.  c  o m
 */
@Override
public synchronized int ensureThatRecordExists(String filePath, String repository)
        throws DatabaseAccessException {
    int fileId = getFileIdForFileName(filePath, repository);
    Connection conn = null;
    PreparedStatement statement = null;
    try {
        if (fileId == -1) {
            conn = dataSource.getConnection();
            // In case no record for this data exists
            statement = conn.prepareStatement(STMT_CREATE_FILE_RECORD, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, filePath);
            statement.setString(2, repository);
            statement.execute();
            ResultSet generatedKeys = statement.getGeneratedKeys();
            generatedKeys.first();
            fileId = generatedKeys.getInt(1);
        }
    } catch (SQLException ex) {
        throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
            }
        }
    }
    return fileId;
}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.H2Engine.java

@Override
public synchronized Long persist(String name, EntityEntry entry, boolean useAutoInc)
        throws DatabaseEngineException {

    ResultSet generatedKeys = null;
    try {/*from  w ww .  j  av  a2s  .  c  o  m*/
        getConnection();

        final MappedEntity me = entities.get(name);

        if (me == null) {
            throw new DatabaseEngineException(String.format("Unknown entity '%s'", name));
        }

        PreparedStatement ps = null;
        if (useAutoInc) {
            ps = entities.get(name).getInsert();
        } else {
            ps = entities.get(name).getInsertWithAutoInc();
        }

        entityToPreparedStatement(me.getEntity(), ps, entry, useAutoInc);
        ps.execute();

        long ret = 0;
        if (useAutoInc) {
            generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                ret = generatedKeys.getLong(1);
            }
            generatedKeys.close();
        }

        return ret == 0 ? null : ret;
    } catch (Exception ex) {
        throw new DatabaseEngineException("Something went wrong persisting the entity", ex);
    } finally {
        try {
            if (generatedKeys != null) {
                generatedKeys.close();
            }
        } catch (Exception e) {
            logger.trace("Error closing result set.", e);
        }
    }
}

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.//from w w w.java 2 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:dbutils.DbUtilsTemplate.java

/**
 * ??// www . j  a  va2  s. c om
 *
 * @param sql    sql?
 * @param params ?
 * @return (?, -1)
 * @throws SQLException
 */
public long insert(String sql, Object[] params) throws SQLException {
    long result = -1L;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(sql);
        fillStatement(stmt, params);
        int affectCount = stmt.executeUpdate();
        if (affectCount <= 0)
            return -1L;
        rs = stmt.getGeneratedKeys();
        result = rs.next() ? rs.getLong(1) : -1;
        conn.commit();
    } catch (SQLException e) {
        LOG.error("Error occured while attempting to insert data", e);
        if (conn != null) {
            conn.rollback();
        }
        throw e;
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }
    return result;
}

From source file:org.wso2.carbon.device.mgt.core.operation.mgt.dao.impl.OperationDAOImpl.java

public int addOperation(Operation operation) throws OperationManagementDAOException {
    PreparedStatement stmt = null;
    ResultSet rs = null;//w w  w  . jav  a  2 s  .  c o  m
    try {
        Connection connection = OperationManagementDAOFactory.getConnection();
        String sql = "INSERT INTO DM_OPERATION(TYPE, CREATED_TIMESTAMP, RECEIVED_TIMESTAMP, OPERATION_CODE)  "
                + "VALUES (?, ?, ?, ?)";
        stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, operation.getType().toString());
        stmt.setTimestamp(2, new Timestamp(new Date().getTime()));
        stmt.setTimestamp(3, null);
        stmt.setString(4, operation.getCode());
        stmt.executeUpdate();

        rs = stmt.getGeneratedKeys();
        int id = -1;
        if (rs.next()) {
            id = rs.getInt(1);
        }
        return id;
    } catch (SQLException e) {
        throw new OperationManagementDAOException("Error occurred while adding operation metadata", e);
    } finally {
        OperationManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:de.static_interface.sinksql.SqlDatabase.java

@Override
public <T extends Row> T insert(AbstractTable<T> abstractTable, T row) {
    Validate.notNull(row);/*from  w w w.j  a  v a 2  s.  c o m*/
    String columns = "";
    char bt = getBacktick();
    int i = 0;
    List<Field> fields = ReflectionUtil.getAllFields(abstractTable.getRowClass());
    Map<Field, String> autoIncrements = new HashMap<>();
    for (Field f : fields) {
        Column column = FieldCache.getAnnotation(f, Column.class);
        if (column == null) {
            continue;
        }

        String name = StringUtil.isEmptyOrNull(column.name()) ? f.getName() : column.name();

        if (column.autoIncrement()) {
            autoIncrements.put(f, name);
        }

        name = bt + name + bt;
        if (i == 0) {
            columns = name;
            i++;
            continue;
        }
        columns += ", " + name;
        i++;
    }

    if (i == 0) {
        throw new IllegalStateException(
                abstractTable.getRowClass().getName() + " doesn't have any public fields!");
    }

    String valuesPlaceholders = "";
    for (int k = 0; k < i; k++) {
        if (k == 0) {
            valuesPlaceholders = "?";
            continue;
        }
        valuesPlaceholders += ",?";
    }

    String sql = "INSERT INTO `{TABLE}` (" + columns + ") " + "VALUES(" + valuesPlaceholders + ")";
    List<Object> values = new ArrayList<>();
    for (Field f : fields) {
        try {
            values.add(f.get(row));
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    PreparedStatement ps = abstractTable.createPreparedStatement(sql, Statement.RETURN_GENERATED_KEYS,
            values.toArray(new Object[values.size()]));
    try {
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

    ResultSet rs;
    try {
        rs = ps.getGeneratedKeys();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

    try {
        rs.next();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    for (Field f : autoIncrements.keySet()) {
        abstractTable.setFieldFromResultSet(row, rs, f, autoIncrements.get(f));
    }

    try {
        rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return row;
}