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.ut.biolab.medsavant.server.serverapi.RegionSetManager.java

@Override
public void addRegionSet(String sessID, String regionSetName, int genomeID, char delim, FileFormat fileFormat,
        int numHeaderLines, int fileID)
        throws IOException, SQLException, RemoteException, SessionExpiredException {

    Connection conn = ConnectionController.connectPooled(sessID);

    try {//from  www.  j a va2s . co  m
        // TODO: Since we're using the MyISAM engine for this table, rolling back has no effect.
        conn.setAutoCommit(false);

        //add region set
        InsertQuery query = MedSavantDatabase.RegionSetTableSchema.preparedInsert(RegionSetColumns.NAME);
        PreparedStatement prep = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);
        prep.setString(1, regionSetName);
        prep.executeUpdate();

        ResultSet rs = prep.getGeneratedKeys();
        rs.next();
        int regionSetID = rs.getInt(1);

        File f = NetworkManager.getInstance().getFileByTransferID(sessID, fileID);
        Iterator<String[]> i = ImportDelimitedFile.getFileIterator(f.getAbsolutePath(), delim, numHeaderLines,
                fileFormat);

        query = MedSavantDatabase.RegionSetMembershipTableSchema.preparedInsert(GENOME_ID, REGION_SET_ID, CHROM,
                START, END, DESCRIPTION);
        prep = conn.prepareStatement(query.toString());
        while (i.hasNext() && !Thread.currentThread().isInterrupted()) {
            String[] line = i.next();
            LOG.info(StringUtils.join(line, '\t'));
            prep.setInt(1, genomeID);
            prep.setInt(2, regionSetID);
            prep.setString(3, line[0]);
            prep.setString(4, line[1]);
            prep.setString(5, line[2]);
            prep.setString(6, line[3]);
            prep.executeUpdate();
        }

        // Since this table is defined with the MyISAM engine, the rollback doesn't actually do anything.
        if (Thread.currentThread().isInterrupted()) {
            conn.rollback();
        } else {
            conn.commit();
        }
        conn.setAutoCommit(true);
    } finally {
        conn.close();
    }
}

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

public Integer save(final Integer questionId, final String visitorIp, final Date answerDate,
        final String answer, final String username, final Integer referenceAnswerId) {
    return execute(new TransactionalOperation<Integer>() {
        @Override//  ww  w  . j a va  2s . co m
        public Integer doInConnection(Connection connection) {
            try {
                PreparedStatement statement = null;
                if (referenceAnswerId == null) {
                    statement = connection.prepareStatement(
                            "insert into answers (visitor_ip,city,answer,question_id,"
                                    + "answer_date,username) values (?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                } else {
                    statement = connection.prepareStatement(
                            "insert into answers (visitor_ip,city,answer,question_id,"
                                    + "answer_date,username,reference_answer_id) values (?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                }
                statement.setString(1, visitorIp);
                statement.setString(2,
                        Configuration.isProductEnv() ? HttpApiHelper.getCity(visitorIp) : "?");
                statement.setString(3, answer);
                statement.setInt(4, questionId);
                Date finalCommentDate = answerDate;
                if (answerDate == null) {
                    finalCommentDate = new Date();
                }
                statement.setTimestamp(5, new Timestamp(finalCommentDate.getTime()));
                statement.setString(6, username);
                if (referenceAnswerId != null) {
                    statement.setInt(7, referenceAnswerId);
                }
                int result = statement.executeUpdate();
                if (result > 0) {
                    ResultSet resultSet = statement.getGeneratedKeys();
                    if (resultSet.next()) {
                        return resultSet.getInt(1);
                    }
                }
            } catch (SQLException e) {
                error("save answers failed ...", e);
            }
            return null;
        }
    });
}

From source file:edu.ku.brc.web.ParsePaleo.java

/**
 * /*from   w w  w. ja v  a  2s.  c o m*/
 */
private void startUp() {
    try {
        dbConn = DBConnection.getInstance();
        dbConn.setUsernamePassword("root", "root");
        dbConn.setDriver("com.mysql.jdbc.Driver");
        dbConn.setConnectionStr("jdbc:mysql://localhost/");
        dbConn.setDatabaseName("test1");

        dbConn.getConnection().setCatalog("digitalatlas");

        Connection conn = dbConn.getConnection();
        BasicSQLUtils.setDBConnection(conn);

        File srcFile = new File("/Users/rods/databases/digitalatlas.sqlite");
        File dstFile = new File("/Users/rods/databases/daal.sqlite");
        FileUtils.copyFile(srcFile, dstFile, true);

        Class.forName("org.sqlite.JDBC");
        dbS3Conn = DriverManager.getConnection("jdbc:sqlite:" + dstFile.getAbsolutePath());
        dbS3Conn.setAutoCommit(false);

        String pStr = "INSERT INTO taxon (name, commonname, parentId, rankId) VALUES(?,?,?,?)";
        taxonInsertStmt = dbS3Conn.prepareStatement(pStr, Statement.RETURN_GENERATED_KEYS);

        isInitialized = true;

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

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

public static int addUpload(File file, Video data, VideoMetadata metadata, Date startAt)
        throws SQLException, IOException {
    PreparedStatement prest = null;
    ObjectMapper mapper = new ObjectMapper();
    String sql = "INSERT INTO `uploads` (`account`, `file`, `lenght`, `data`,`enddir`, `metadata`, `status`,`starttime`) "
            + "VALUES (?,?,?,?,?,?,?,?)";
    prest = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    prest.setInt(1, metadata.getAccount());
    prest.setString(2, file.getAbsolutePath());
    prest.setLong(3, file.length());/*  w ww .ja  v  a  2 s  . c  o m*/
    prest.setString(4, mapper.writeValueAsString(data));
    prest.setString(5, metadata.getEndDirectory());
    prest.setString(6, mapper.writeValueAsString(metadata));
    prest.setString(7, UploadManager.Status.NOT_STARTED.toString());
    if (startAt == null) {
        prest.setString(8, "");
    } else {
        prest.setDate(8, new java.sql.Date(startAt.getTime()));
    }
    prest.execute();
    ResultSet rs = prest.getGeneratedKeys();
    prest.close();
    if (rs.next()) {
        int id = rs.getInt(1);
        rs.close();
        return id;
    } else {
        return -1;
    }
}

From source file:com.nabla.wapp.server.database.SqlInsert.java

public PreparedStatement prepareStatement(final Connection conn) throws SQLException {
    Assert.argumentNotNull(conn);//from  w  w w  . ja v  a 2 s  .  co m

    return conn.prepareStatement(sql,
            autoGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
}

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

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

    Timestamp now = new Timestamp(System.currentTimeMillis());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int rc = _jdbc.update(con -> {
        PreparedStatement ps = con.prepareStatement(CREATE_TAG_INODE_WITHOUT_VALUE,
                Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, mode | UnixPermission.S_IFREG);
        ps.setInt(2, uid);/*from  w w  w  .  j a  v  a2  s  .c  o m*/
        ps.setInt(3, gid);
        ps.setTimestamp(4, now);
        ps.setTimestamp(5, now);
        ps.setTimestamp(6, now);
        return ps;
    }, keyHolder);
    if (rc != 1) {
        throw new JdbcUpdateAffectedIncorrectNumberOfRowsException(CREATE_TAG_INODE_WITHOUT_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:net.bhira.sample.api.dao.ContactInfoDaoImpl.java

/**
 * @see net.bhira.sample.api.dao.ContactInfoDao#save(net.bhira.sample.model.ContactInfo)
 *//*from  w w  w.j a  v  a2  s  .c om*/
@Override
public void save(ContactInfo contactInfo) {
    if (contactInfo == null) {
        return;
    }

    if (contactInfo.isNew()) {
        // for new address, use SQL insert statement

        KeyHolder keyHolder = new GeneratedKeyHolder();
        int count = jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pstmt = connection.prepareStatement(SQL_INSERT,
                        Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, contactInfo.getPhone());
                pstmt.setString(2, contactInfo.getFax());
                pstmt.setString(3, contactInfo.getEmail());
                pstmt.setString(4, contactInfo.getWebsite());
                return pstmt;
            }
        }, keyHolder);

        // fetch the newly created auto-increment ID
        contactInfo.setId(keyHolder.getKey().longValue());
        LOG.debug("inserted contactInfo, count = {}, id = {}", count, contactInfo.getId());

    } else {
        // for existing address, construct the SQL update statement
        int count = jdbcTemplate.update(SQL_UPDATE, new Object[] { contactInfo.getPhone(), contactInfo.getFax(),
                contactInfo.getEmail(), contactInfo.getWebsite(), contactInfo.getId() });
        LOG.debug("updated contactInfo, count = {}, id = {}", count, contactInfo.getId());
    }
}

From source file:org.primeframework.persistence.jdbc.Insert.java

/**
 * Performs the insert./*from  ww w.  j  ava  2  s  .  c o  m*/
 *
 * @param handler The generated keys handler.
 * @return The result.
 * @throws InsertException If the insert fails.
 */
public <T> InsertResult<T> go(GeneratedKeyHandler<T> handler) throws InsertException {
    PreparedStatement ps = null;
    try {
        ps = c.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
        setParams(ps);

        int results = ps.executeUpdate();
        if (results != 1) {
            throw new InsertException("Inserting a single row failed completely");
        }

        ResultSet keysRS = ps.getGeneratedKeys();
        List<T> keys = new ArrayList<T>();
        while (keysRS.next()) {
            keys.add(handler.handle(keysRS));
        }

        return new InsertResult<T>(results, keys);
    } catch (SQLException e) {
        throw new InsertException(e);
    } finally {
        close(ps);
    }
}

From source file:com.softberries.klerk.dao.CompanyDao.java

@Override
public void create(Company c) throws SQLException {
    try {//  w  w  w .ja v  a  2 s . c om
        init();
        st = conn.prepareStatement(SQL_INSERT_COMPANY, Statement.RETURN_GENERATED_KEYS);
        st.setString(1, c.getName());
        st.setString(2, c.getVatid());
        st.setString(3, c.getTelephone());
        st.setString(4, c.getTelephone());
        st.setString(5, c.getTelephone());
        st.setString(6, c.getTelephone());
        // run the query
        int i = st.executeUpdate();
        System.out.println("i: " + i);
        if (i == -1) {
            System.out.println("db error : " + SQL_INSERT_COMPANY);
        }
        generatedKeys = st.getGeneratedKeys();
        if (generatedKeys.next()) {
            c.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Creating company failed, no generated key obtained.");
        }
        // if the company creation was successfull, add addresses
        AddressDao adrDao = new AddressDao();
        for (Address adr : c.getAddresses()) {
            adr.setCompany_id(c.getId());
            adrDao.create(adr, run, conn, generatedKeys);
            if (adr.isMain()) {
                c.setAddress(adr);
            }
        }
        conn.commit();
    } catch (Exception e) {
        // rollback the transaction but rethrow the exception to the caller
        conn.rollback();
        e.printStackTrace();
        throw new SQLException(e);
    } finally {
        close(conn, st, generatedKeys);
    }
}

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

@Override
public boolean updateDevice(int typeId, Device device, int tenantId) throws DeviceManagementDAOException {
    Connection conn;//w w w  .  j av  a  2 s.co m
    PreparedStatement stmt = null;
    boolean status = false;
    int rows;
    try {
        conn = this.getConnection();
        String sql = "UPDATE DM_DEVICE SET DESCRIPTION = ?, NAME = ?, GROUP_ID = ? WHERE DEVICE_IDENTIFICATION = ? AND "
                + "DEVICE_TYPE_ID = ? AND TENANT_ID = ?";
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, device.getDescription());
        stmt.setString(2, device.getName());
        if (device.getGroupId() == 0) {
            stmt.setNull(3, java.sql.Types.INTEGER);
        } else {
            stmt.setInt(3, device.getGroupId());
        }
        stmt.setString(4, device.getDeviceIdentifier());
        stmt.setInt(5, typeId);
        stmt.setInt(6, tenantId);
        rows = stmt.executeUpdate();
        if (rows > 0) {
            status = true;
        }
        return status;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException(
                "Error occurred while enrolling device '" + device.getName() + "'", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage