Example usage for java.sql Connection prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql, String columnNames[]) throws SQLException;

Source Link

Document

Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array.

Usage

From source file:AutoGenKeys.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con = null;
    PreparedStatement pstmt;//from w w  w . java  2 s  . co  m
    String insert = "INSERT INTO COFFEES VALUES ('HYPER_BLEND', " + "101, 10.99, 0, 0)";
    String update = "UPDATE COFFEES SET PRICE = ? WHERE KEY = ?";

    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        pstmt = con.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

        pstmt.executeUpdate();
        ResultSet keys = pstmt.getGeneratedKeys();

        int count = 0;

        keys.next();
        int key = keys.getInt(1);

        pstmt = con.prepareStatement(update);
        pstmt.setFloat(1, 11.99f);
        pstmt.setInt(2, key);
        pstmt.executeUpdate();

        keys.close();
        pstmt.close();
        con.close();

    } catch (SQLException e) {
        e.printStackTrace();
    }

}

---

From source file:edu.pitt.sis.infsci2730.finalProject.dao.AddressDao.java

/**
 * add new AddressDBModel/*from www.j  a v a  2 s  . c  o  m*/
 *
 * @param para
 * @return
 */
//    public static int addAddress(final String[] para) throws SQLException {
//        String sql = "insert into Address (city,street,state_,zipCode) values (?,?,?,?)";
//        return jdbcTemplate.update(sql,
//                para,
//                new int[]{java.sql.Types.VARCHAR, java.sql.Types.VARCHAR, java.sql.Types.VARCHAR, java.sql.Types.VARCHAR});
//    }

public static Long addAddress(final String[] para) throws SQLException {

    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        String sql = "insert into Address (city,street,state_,zipCode) values (?,?,?,?)";

        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, para[0]);
            ps.setString(2, para[1]);
            ps.setString(3, para[2]);
            ps.setString(4, para[3]);
            return ps;
        }
    }, holder);

    Long newPersonId = holder.getKey().longValue();

    return newPersonId;
}

---

From source file:edu.pitt.sis.infsci2730.finalProject.dao.TransactionDao.java

public static TransactionDBModel InsertTransactionByID(final String[] para) throws SQLException {

    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        String sql = "insert into Transactions (transaction_date, customer_id) "
                + "values (CURRENT_TIMESTAMP,?)";

        @Override//w  ww  . j a  v a2 s  .c o  m
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, para[0]);

            return ps;
        }
    }, holder);

    int newId = holder.getKey().intValue();

    return jdbcTemplate.queryForObject("select * from Transactions where TRANSACTION_ID=" + newId,
            new TransactionRowMapper());
}

---

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

public static PreparedStatement prepare(final Connection conn, int autoGeneratedKeys, final String sql,
        Object... parameters) throws SQLException {
    Assert.argumentNotNull(conn);/*from   ww  w . j a v a  2 s.c o m*/

    if (parameters == null)
        return conn.prepareStatement(sql, autoGeneratedKeys);
    StringBuilder actualSql = new StringBuilder(sql);
    int i = 1;
    for (Object parameter : parameters) {
        Assert.notNull(parameter);
        i += getSetter(parameter.getClass()).prepare(actualSql, i, parameter);
    }
    final PreparedStatement stmt = conn.prepareStatement(actualSql.toString(), autoGeneratedKeys);
    try {
        return format(stmt, parameters);
    } catch (SQLException e) {
        Database.close(stmt);
        throw e;
    }
}

---

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

public static CuratedChemical insertCuratedChemical(DB db, String name, String inchi, Double mPlusHPlusMass,
        Integer expectedCollisionVoltage, String referenceUrl) throws SQLException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(QUERY_INSERT_CURATED_CHEMICAL,
            Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, name, inchi, mPlusHPlusMass, expectedCollisionVoltage, referenceUrl);
        stmt.executeUpdate();//www.j av a2 s . c o  m
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
            if (resultSet.next()) {
                // Get auto-generated id.
                int id = resultSet.getInt(1);
                return new CuratedChemical(id, name, inchi, mPlusHPlusMass, expectedCollisionVoltage,
                        referenceUrl);
            } else {
                System.err.format("ERROR: could not retrieve autogenerated key for curated chemical %s\n",
                        name);
                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();/*  www.jav  a  2 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:com.tethrnet.manage.db.UserDB.java

/**
 * inserts new user/* w w  w. ja  va  2s.co  m*/
 * 
 * @param con DB connection 
 * @param user user object
 */
public static Long insertUser(Connection con, User user) {

    Long userId = null;

    try {
        PreparedStatement stmt = con.prepareStatement(
                "insert into users (email, username, auth_type, user_type, password, salt) values (?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, user.getEmail());
        stmt.setString(2, user.getUsername());
        stmt.setString(3, user.getAuthType());
        stmt.setString(4, user.getUserType());
        if (StringUtils.isNotEmpty(user.getPassword())) {
            String salt = EncryptionUtil.generateSalt();
            stmt.setString(5, EncryptionUtil.hash(user.getPassword() + salt));
            stmt.setString(6, salt);
        } else {
            stmt.setString(5, null);
            stmt.setString(6, null);
        }
        stmt.execute();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            userId = rs.getLong(1);
        }
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    return userId;

}

---

From source file:com.keybox.manage.db.UserDB.java

/**
 * inserts new user/*from   www.  j  a v  a2s.  c om*/
 * 
 * @param con DB connection 
 * @param user user object
 */
public static Long insertUser(Connection con, User user) {

    Long userId = null;

    try {
        PreparedStatement stmt = con.prepareStatement(
                "insert into users (first_nm, last_nm, email, username, auth_type, user_type, password, salt) values (?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, user.getFirstNm());
        stmt.setString(2, user.getLastNm());
        stmt.setString(3, user.getEmail());
        stmt.setString(4, user.getUsername());
        stmt.setString(5, user.getAuthType());
        stmt.setString(6, user.getUserType());
        if (StringUtils.isNotEmpty(user.getPassword())) {
            String salt = EncryptionUtil.generateSalt();
            stmt.setString(7, EncryptionUtil.hash(user.getPassword() + salt));
            stmt.setString(8, salt);
        } else {
            stmt.setString(7, null);
            stmt.setString(8, null);
        }
        stmt.execute();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            userId = rs.getLong(1);
        }
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    return userId;

}

---

From source file:com.keybox.manage.db.SessionAuditDB.java

/**
 * insert new session record for user/*from w ww  .  j  ava2 s  .co m*/
 *
 * @param con    DB connection
 * @param userId user id
 * @return session id
 */
public static Long createSessionLog(Connection con, Long userId) {
    Long sessionId = null;
    try {

        //insert
        PreparedStatement stmt = con.prepareStatement("insert into session_log (user_id) values(?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setLong(1, userId);
        stmt.execute();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            sessionId = rs.getLong(1);
        }

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return sessionId;

}

---

From source file:edu.jhu.pha.vospace.DbPoolServlet.java

/** Helper function to setup and teardown SQL connection & statement. */
public static <T> T goSql(String context, String sql, SqlWorker<T> goer, int genKeys) {
    //logger.debug(context);
    Connection conn = null;
    PreparedStatement stmt = null;
    try {/* w w w  . j  a  v a 2s.c o m*/
        conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:dbPool");
        int tries = 30; // number of repitions when a transaction fails due to a deadlock
        while (true) {
            try {
                if (sql != null)
                    stmt = conn.prepareStatement(sql, genKeys);
                T result = goer.go(conn, stmt);
                return result;
            } catch (MySQLTransactionRollbackException transactionEx) {
                if (tries > 0)
                    tries--;
                else {
                    logger.error("Exceeded limit of transaction tries.");
                    goer.error(context, transactionEx);
                    throw transactionEx;
                }
            }
        }
    } catch (SQLException e) {
        goer.error(context, e);
        return null;
    } finally {
        close(stmt);
        close(conn);
    }
}

---

• «
• 1
• 2
• »