Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

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

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

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

public static boolean updatePlate(DB db, Plate plate) throws SQLException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(QUERY_UPDATE_PLATE_BY_ID)) {
        bindInsertOrUpdateParameters(stmt, plate);
        stmt.setInt(UPDATE_STATEMENT_FIELDS_AND_BINDINGS.size() + 1, plate.getId());
        return stmt.executeUpdate() > 0;
    }/*w ww . java  2 s .c om*/
}

From source file:com.flexive.core.security.FxDBAuthentication.java

/**
 * Increase the number of failed login attempts for the given user
 *
 * @param con    an open and valid connection
 * @param userId user id//from   w  ww  .  j  a  va2 s .  c  om
 * @throws SQLException on errors
 */
private static void increaseFailedLoginAttempts(Connection con, long userId) throws SQLException {
    PreparedStatement ps = null;
    try {
        ps = con.prepareStatement(
                "UPDATE " + TBL_ACCOUNT_DETAILS + " SET FAILED_ATTEMPTS=FAILED_ATTEMPTS+1 WHERE ID=?");
        ps.setLong(1, userId);
        if (ps.executeUpdate() == 0) {
            ps.close();
            ps = con.prepareStatement("INSERT INTO " + TBL_ACCOUNT_DETAILS
                    + " (ID,APPLICATION,ISLOGGEDIN,LAST_LOGIN,LAST_LOGIN_FROM,FAILED_ATTEMPTS,AUTHSRC) "
                    + "VALUES (?,?,?,?,?,?,?)");
            ps.setLong(1, userId);
            ps.setString(2, FxContext.get().getApplicationId());
            ps.setBoolean(3, false);
            ps.setLong(4, System.currentTimeMillis());
            ps.setString(5, FxContext.get().getRemoteHost());
            ps.setLong(6, 1); //one failed attempt
            ps.setString(7, AuthenticationSource.Database.name());
            ps.executeUpdate();
        }
    } finally {
        if (ps != null)
            ps.close();
    }
}

From source file:com.magnet.mmx.server.plugin.mmxmgmt.api.user.MMXUsersResourceTest.java

@AfterClass
public static void cleanupDatabase() {
    final String statementStr1 = "DELETE FROM mmxApp WHERE appName LIKE '%" + "usersresourcetestapp" + "%'";
    final String statementStr3 = "DELETE FROM ofUser WHERE username LIKE '%" + "MMXUsersResourceTestUser"
            + "%'";
    final String statementStr4 = "DELETE FROM mmxTag WHERE tagname LIKE '%" + "tag" + "%'";

    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt3 = null;
    PreparedStatement pstmt4 = null;

    try {/*from   w ww .j av  a2s  .  c  om*/
        conn = UnitTestDSProvider.getDataSource().getConnection();
        pstmt1 = conn.prepareStatement(statementStr1);
        pstmt3 = conn.prepareStatement(statementStr3);
        pstmt4 = conn.prepareStatement(statementStr4);
        pstmt1.execute();
        pstmt3.execute();
        pstmt4.execute();
    } catch (SQLException e) {
        LOGGER.error("cleanupDatabase : {}", e);
    } finally {
        CloseUtil.close(LOGGER, pstmt1, conn);
        CloseUtil.close(LOGGER, pstmt3);
        CloseUtil.close(LOGGER, pstmt4);
    }
}

From source file:com.hangum.tadpole.rdb.core.editors.main.utils.plan.OracleExecutePlanUtils.java

/**
 * oracle query plan? . /*  w  ww  .  ja v  a 2s.  com*/
 * 
 * @param userDB
 * @param reqQuery
 * @param planTableName
 * @throws Exception
 */
public static void plan(UserDBDAO userDB, RequestQuery reqQuery, String planTableName,
        java.sql.Connection javaConn, String statement_id) throws Exception {

    PreparedStatement pstmt = null;
    try {
        String query = PartQueryUtil.makeExplainQuery(userDB, reqQuery.getSql());
        query = StringUtils.replaceOnce(query, PublicTadpoleDefine.STATEMENT_ID, statement_id);
        query = StringUtils.replaceOnce(query, PublicTadpoleDefine.DELIMITER, planTableName);

        pstmt = javaConn.prepareStatement(query);
        if (reqQuery.getSqlStatementType() == SQL_STATEMENT_TYPE.PREPARED_STATEMENT) {
            final Object[] statementParameter = reqQuery.getStatementParameter();
            for (int i = 1; i <= statementParameter.length; i++) {
                pstmt.setObject(i, statementParameter[i - 1]);
            }
        }
        pstmt.execute();
    } finally {
        try {
            if (pstmt != null)
                pstmt.close();
        } catch (Exception e) {
        }
    }

}

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

/**
 * updates existing user//from  w  w  w . j a va2  s  .  co  m
 * @param user user object
 */
public static void updateUserNoCredentials(User user) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con
                .prepareStatement("update users set  email=?, username=?, user_type=? where id=?");
        stmt.setString(1, user.getEmail());
        stmt.setString(2, user.getUsername());
        stmt.setString(3, user.getUserType());
        stmt.setLong(4, user.getId());
        stmt.execute();
        DBUtils.closeStmt(stmt);
        if (User.ADMINISTRATOR.equals(user.getUserType())) {
            PublicKeyDB.deleteUnassignedKeysByUser(con, user.getId());
        }

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

}

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

/**
 * updates shared secret based on auth token
 *
 * @param secret    OTP shared secret// w  w w .  j  a  v  a  2 s  . c  o  m
 * @param authToken auth token
 */
public static void updateSharedSecret(String secret, String authToken) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("update users set otp_secret=? where auth_token=?");
        stmt.setString(1, EncryptionUtil.encrypt(secret));
        stmt.setString(2, authToken);
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

From source file:net.codjo.dataprocess.server.treatmenthelper.TreatmentHelper.java

public static void deleteRepository(Connection con, int repositoryId) throws SQLException {
    String sql = "delete PM_REPOSITORY_CONTENT where REPOSITORY_ID = ? "
            + " delete PM_REPOSITORY where REPOSITORY_ID = ?";
    PreparedStatement pStmt = con.prepareStatement(sql);
    try {/* w  w w  . j av a  2 s.  c  o m*/
        pStmt.setInt(1, repositoryId);
        pStmt.setInt(2, repositoryId);
        pStmt.executeUpdate();
    } finally {
        pStmt.close();
    }
}

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

/**
 * returns user base on id/*from   w  w w .ja  va  2  s .c o m*/
 * @param con DB connection
 * @param userId user id
 * @return user object
 */
public static User getUser(Connection con, Long userId) {

    User user = null;
    try {
        PreparedStatement stmt = con.prepareStatement("select * from  users where id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setAuthType(rs.getString("auth_type"));
            user.setUserType(rs.getString("user_type"));
            user.setSalt(rs.getString("salt"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, userId));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

From source file:com.clustercontrol.commons.util.JdbcBatchExecutor.java

/**
 * ?/* w w w . j  av a2 s.  c  om*/
 * 
 * @param query
 *            insert???update
 */
public static void execute(List<JdbcBatchQuery> queryList) {
    Connection conn = null;
    long start = HinemosTime.currentTimeMillis();
    JpaTransactionManager tm = null;
    try {
        tm = new JpaTransactionManager();
        conn = tm.getEntityManager().unwrap(java.sql.Connection.class);
        conn.setAutoCommit(false);
        for (JdbcBatchQuery query : queryList)
            try (PreparedStatement pstmt = conn.prepareStatement(query.getSql())) {
                query.addBatch(pstmt);
                pstmt.executeBatch();
            }
        if (!tm.isNestedEm()) {
            conn.commit();
        }
    } catch (Exception e) {
        log.warn(e);
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.warn(e1);
            }
        }
    } finally {
        if (tm != null) {
            tm.close();
        }
    }
    long time = HinemosTime.currentTimeMillis() - start;
    String className = "";
    if (queryList.size() != 0) {
        className = queryList.get(0).getClass().getSimpleName();
    }
    String message = String.format("Execute [%s] batch: %dms. size=%d", className, time, queryList.size());
    if (time > 3000) {
        log.warn(message);
    } else if (time > 1000) {
        log.info(message);
    } else {
        log.debug(message);
    }
}

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

/**
 * returns a list of system ids for a given profile
 *
 * @param con       DB con/*w  w w .  j a  v  a  2 s  . co m*/
 * @param profileId profile id
 * @return list of host systems
 */
public static List<Long> getSystemIdsByProfile(Connection con, Long profileId) {

    List<Long> systemIdList = new ArrayList<Long>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from  system s, system_map m where s.id=m.system_id and m.profile_id=? order by display_nm asc");
        stmt.setLong(1, profileId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            systemIdList.add(rs.getLong("id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return systemIdList;
}