List of usage examples for java.sql Connection prepareStatement
PreparedStatement prepareStatement(String sql) throws SQLException;
PreparedStatement object for sending parameterized SQL statements to the database. From source file:com.sql.Audit.java
/** * Removes old audits based on specific time frame. *//*from w ww .j a va 2 s. co m*/ public static void removeOldAudits() { Connection conn = null; PreparedStatement ps = null; try { conn = DBConnection.connectToDB(); String sql = "DELETE FROM Audit WHERE " + "date < dateadd(" + Global.getAuditTimeFrame() + ", -" + Global.getAuditTimeAmount() + ", getdate())"; ps = conn.prepareStatement(sql); ps.executeUpdate(); } catch (SQLException ex) { ExceptionHandler.Handle(ex); } finally { DbUtils.closeQuietly(conn); DbUtils.closeQuietly(ps); } }
From source file:fll.db.NonNumericNominees.java
/** * Clear the nominees for the specified category at the tournament. * /*from w w w . ja v a 2s . co m*/ * @param connection database connection * @param tournamentId the tournament * @param category the category * @throws SQLException */ public static void clearNominees(final Connection connection, final int tournamentId, final String category) throws SQLException { PreparedStatement delete = null; try { delete = connection.prepareStatement("DELETE FROM non_numeric_nominees"// + " WHERE tournament = ?"// + " AND category = ?"); delete.setInt(1, tournamentId); delete.setString(2, category); delete.executeUpdate(); } finally { SQLFunctions.close(delete); } }
From source file:com.keybox.manage.db.ProfileDB.java
/** * returns profile based on id/*from ww w . j a va 2s . c o m*/ * * @param con db connection object * @param profileId profile id * @return profile */ public static Profile getProfile(Connection con, Long profileId) { Profile profile = null; try { PreparedStatement stmt = con.prepareStatement("select * from profiles where id=?"); stmt.setLong(1, profileId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { profile = new Profile(); profile.setId(rs.getLong("id")); profile.setNm(rs.getString("nm")); profile.setDesc(rs.getString("desc")); profile.setHostSystemList(ProfileSystemsDB.getSystemsByProfile(con, profileId)); } DBUtils.closeRs(rs); DBUtils.closeStmt(stmt); } catch (Exception e) { log.error(e.toString(), e); } return profile; }
From source file:net.codjo.dataprocess.server.treatmenthelper.TreatmentHelper.java
public static void insertRepository(Connection con, int repositoryId, String repositoryName) throws SQLException { PreparedStatement pStmt = con .prepareStatement("insert into PM_REPOSITORY (REPOSITORY_ID, REPOSITORY_NAME) values (?, ?)"); try {//from w w w . j a va2 s .c om pStmt.setInt(1, repositoryId); pStmt.setString(2, repositoryName); pStmt.executeUpdate(); } finally { pStmt.close(); } }
From source file:com.concursive.connect.web.modules.activity.utils.ProjectHistoryUtils.java
public static int queryAdditionalCommentsCount(Connection db, ProjectHistory projectHistory) throws SQLException { int count = 0; int topId = projectHistory.getTopId(); if (topId == -1) { topId = projectHistory.getId();/*from w w w . j a v a 2 s .co m*/ } PreparedStatement pst = db.prepareStatement("SELECT count(*) AS comment_count " + "FROM project_history " + "WHERE top_id = ? AND position > ? "); pst.setInt(1, topId); pst.setInt(2, projectHistory.getPosition()); ResultSet rs = pst.executeQuery(); if (rs.next()) { count = rs.getInt("comment_count"); } rs.close(); pst.close(); return count; }
From source file:com.sql.Activity.java
/** * Gathers a list of tiles that are awaiting a timestamp * //ww w.java 2s.com * @return List (ActivityModel) */ public static List<ActivityModel> getFilesToStamp() { List<ActivityModel> list = new ArrayList(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = DBConnection.connectToDB(); String sql = "SELECT * FROM Activity WHERE awaitingTimestamp = 1"; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { ActivityModel type = new ActivityModel(); type.setId(rs.getInt("id")); type.setCaseYear(rs.getString("caseYear")); type.setCaseType(rs.getString("caseType")); type.setCaseMonth(rs.getString("caseMonth")); type.setCaseNumber(rs.getString("caseNumber")); type.setDate(rs.getTimestamp("date")); type.setFileName(rs.getString("fileName")); list.add(type); } } catch (SQLException ex) { ExceptionHandler.Handle(ex); } finally { DbUtils.closeQuietly(conn); DbUtils.closeQuietly(ps); DbUtils.closeQuietly(rs); } return list; }
From source file:gridool.db.DBInsertOperation.java
private static void executeInsertQuery(@Nonnull final Connection conn, @Nonnull final String sql, @Nonnull final DBRecord[] records) throws SQLException { final PreparedStatement stmt = conn.prepareStatement(sql); try {/*from ww w. j a v a 2 s . co m*/ for (final DBRecord rec : records) { rec.writeFields(stmt); stmt.addBatch(); } stmt.executeBatch(); } finally { stmt.close(); } }
From source file:com.concursive.connect.web.modules.upgrade.utils.UpgradeUtils.java
/** * Queries the database to see if the script has already been executed * * @param db Description of the Parameter * @param version Description of the Parameter * @return The installed value/*ww w . jav a 2 s . c o m*/ * @throws java.sql.SQLException Description of the Exception */ public static boolean isInstalled(Connection db, String version) throws SQLException { boolean isInstalled = false; // Query the installed version PreparedStatement pst = db.prepareStatement( "SELECT script_version " + "FROM database_version " + "WHERE script_version = ? "); pst.setString(1, version); ResultSet rs = pst.executeQuery(); if (rs.next()) { isInstalled = true; } rs.close(); pst.close(); return isInstalled; }
From source file:com.keybox.manage.db.SystemStatusDB.java
/** * updates the status table to keep track of key placement status * * @param con DB connection * @param hostSystem systems for authorized_keys replacement * @param userId user id/*w w w. j a v a 2 s . c om*/ */ public static void updateSystemStatus(Connection con, HostSystem hostSystem, Long userId) { try { PreparedStatement stmt = con.prepareStatement("update status set status_cd=? where id=? and user_id=?"); stmt.setString(1, hostSystem.getStatusCd()); stmt.setLong(2, hostSystem.getId()); stmt.setLong(3, userId); stmt.execute(); DBUtils.closeStmt(stmt); } catch (Exception e) { log.error(e.toString(), e); } }
From source file:com.wso2telco.core.mnc.resolver.dao.OperatorDAO.java
public static String getOperatorByMCCMNC(String mcc, String mnc) throws MobileNtException { Connection conn = null; PreparedStatement ps = null;/*from w w w .ja va 2s .c o m*/ ResultSet rs = null; String operator = null; try { String sql = "SELECT operatorname FROM operators WHERE mcc = ? AND mnc = ?"; conn = getDBConnection(); ps = conn.prepareStatement(sql); ps.setString(1, mcc); ps.setString(2, mnc); rs = ps.executeQuery(); while (rs.next()) { operator = rs.getString("operatorname"); log.debug("operator in getOperatorByMCCMNC: " + operator); } } catch (SQLException e) { log.error("database operation error in getOperatorByMCCMNC : ", e); handleException("Error occured while getting operator for mcc : " + mcc + " and mnc : " + mnc + "from the database", e); } catch (Exception e) { log.error("error in getOperatorByMCCMNC : ", e); handleException("Error occured while getting operator for mcc : " + mcc + " and mnc : " + mnc + "from the database", e); } finally { DbUtils.closeAllConnections(ps, conn, rs); } return operator; }