@Override public Set<EmpVO> getEmpsByDeptno(Integer deptno) { Set<EmpVO> set = new LinkedHashSet<EmpVO>(); EmpVO empVO = null; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userid, passwd); pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT); pstmt.setInt(1, deptno); rs = pstmt.executeQuery(); while (rs.next()) { empVO = new EmpVO(); empVO.setEmpno(rs.getInt("empno")); empVO.setEname(rs.getString("ename")); empVO.setJob(rs.getString("job")); empVO.setHiredate(rs.getDate("hiredate")); empVO.setSal(rs.getDouble("sal")); empVO.setComm(rs.getDouble("comm")); empVO.setDeptno(rs.getInt("deptno")); set.add(empVO); // Store the row in the vector } // Handle any driver errors } catch (ClassNotFoundException e) { throw new RuntimeException("Couldn't load database driver. " + e.getMessage()); // Handle any SQL errors } catch (SQLException se) { throw new RuntimeException("A database error occured. " + se.getMessage()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (con != null) { try { con.close(); } catch (Exception e) { e.printStackTrace(System.err); } } } return set; }
@Override public int size(final DatabaseTable table) throws DatabaseException { preOperationCheck(); final StringBuilder sb = new StringBuilder(); sb.append("SELECT COUNT(" + KEY_COLUMN + ") FROM ").append(table.toString()); PreparedStatement statement = null; ResultSet resultSet = null; try { statement = connection.prepareStatement(sb.toString()); resultSet = statement.executeQuery(); if (resultSet.next()) { return resultSet.getInt(1); } } catch (SQLException e) { final ErrorInformation errorInformation = new ErrorInformation( PwmError.ERROR_DB_UNAVAILABLE, "size operation failed: " + e.getMessage()); lastError = errorInformation; throw new DatabaseException(errorInformation); } finally { close(statement); close(resultSet); } updateStats(true, false); return 0; }
public void deleteUser(String username) { if (isReadOnly()) { // Reject the operation since the provider is read-only throw new UnsupportedOperationException(); } Connection con = null; PreparedStatement pstmt = null; boolean abortTransaction = false; try { // Delete all of the users's extended properties con = DbConnectionManager.getTransactionConnection(); pstmt = con.prepareStatement(DELETE_USER_PROPS); pstmt.setString(1, username); pstmt.execute(); pstmt.close(); // Delete the actual user entry pstmt = con.prepareStatement(DELETE_USER); pstmt.setString(1, username); pstmt.execute(); } catch (Exception e) { Log.error(e); abortTransaction = true; } finally { DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction); } }
/** * Method called by the Form panel to delete existing data. * * @param persistentObject value object to delete * @return an ErrorResponse value object in case of errors, VOResponse if the operation is * successfully completed */ public Response deleteRecord(ValueObject persistentObject) throws Exception { PreparedStatement stmt = null; try { EmpVO vo = (EmpVO) persistentObject; // delete from WORKING_DAYS... stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?"); stmt.setString(1, vo.getEmpCode()); stmt.execute(); stmt.close(); // delete from EMP... stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?"); stmt.setString(1, vo.getEmpCode()); stmt.execute(); gridFrame.reloadData(); frame.getGrid().clearData(); return new VOResponse(vo); } catch (SQLException ex) { ex.printStackTrace(); return new ErrorResponse(ex.getMessage()); } finally { try { stmt.close(); conn.commit(); } catch (SQLException ex1) { } } }
@Override public void delete(Integer deptno) { int updateCount_EMPs = 0; Connection con = null; PreparedStatement pstmt = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userid, passwd); // 1●設定於 pstm.executeUpdate()之前 con.setAutoCommit(false); // 先刪除員工 pstmt = con.prepareStatement(DELETE_EMPs); pstmt.setInt(1, deptno); updateCount_EMPs = pstmt.executeUpdate(); // 再刪除部門 pstmt = con.prepareStatement(DELETE_DEPT); pstmt.setInt(1, deptno); pstmt.executeUpdate(); // 2●設定於 pstm.executeUpdate()之後 con.commit(); con.setAutoCommit(true); System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除"); // Handle any driver errors } catch (ClassNotFoundException e) { throw new RuntimeException("Couldn't load database driver. " + e.getMessage()); // Handle any SQL errors } catch (SQLException se) { if (con != null) { try { // 3●設定於當有exception發生時之catch區塊內 con.rollback(); } catch (SQLException excep) { throw new RuntimeException("rollback error occured. " + excep.getMessage()); } } throw new RuntimeException("A database error occured. " + se.getMessage()); } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (con != null) { try { con.close(); } catch (Exception e) { e.printStackTrace(System.err); } } } }
public void init(int startIndex, int endIndex, String updateDate) throws SQLException { if (connection == null || connection.isClosed()) { connection = getConnection(); } if (startIndex != 0 && endIndex != 0) { bibQuery = "SELECT * FROM OLE_DS_BIB_T WHERE BIB_ID BETWEEN " + startIndex + " AND " + endIndex + " ORDER BY BIB_ID"; } else if (StringUtils.isNotEmpty(updateDate)) { updateDate = getDateStringForOracle(updateDate); bibQuery = "SELECT * FROM OLE_DS_BIB_T where DATE_UPDATED > '" + updateDate + "'"; } else { bibQuery = "SELECT * FROM OLE_DS_BIB_T ORDER BY BIB_ID"; } if (!isStaffOnly) { bibQuery = bibStaffOnly; holdingsQuery = holdingsQuery + staffOnlyHoldings; itemQuery = itemQuery + staffOnlyItem; } fetchCallNumberType(); fetchReceiptStatus(); fetchAuthenticationType(); fetchItemType(); fetchItemStatus(); fetchStatisticalSearchCode(); fetchExtentOfOwnershipType(); bibConnection = getConnection(); holdingsConnection = getConnection(); itemConnection = getConnection(); bibConnection.setAutoCommit(false); bibStatement = bibConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); if (dbVendor.equalsIgnoreCase("oracle")) { bibStatement.setFetchSize(1); } else if (dbVendor.equalsIgnoreCase("mysql")) { bibStatement.setFetchSize(Integer.MIN_VALUE); } bibResultSet = bibStatement.executeQuery(bibQuery); holdingsPreparedStatement = holdingsConnection.prepareStatement(holdingsQuery); itemPreparedStatement = itemConnection.prepareStatement(itemQuery); String insertQuery = "INSERT INTO OLE_DS_BIB_INFO_T(BIB_ID, BIB_ID_STR, TITLE, AUTHOR, PUBLISHER, ISXN) VALUES (?,?,?,?,?,?)"; bibInsertPreparedStatement = connection.prepareStatement(insertQuery); String updateQuery = "UPDATE OLE_DS_BIB_INFO_T SET TITLE=?, AUTHOR=?, PUBLISHER=?, ISXN=?, BIB_ID_STR=? WHERE BIB_ID=?"; bibUpdatePreparedStatement = connection.prepareStatement(updateQuery); }
/** * Consulta as glosas do prestador passado em um determinado periodo * * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas * @return um array de glosas do prestador fornecido como paramentro */ public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo) throws Exception { Connection con = ConnectionPool.getConnection(); GlosaPrestador[] glosas = null; PreparedStatement pst; ResultSet rset; int qtdeGlosas = 0; try { pst = con.prepareStatement(CONSULTA_GLOSA); pst.setString(1, strCdContrato); pst.setString(2, strNumPeriodo); rset = pst.executeQuery(); if (!rset.next()) { return null; } // if ( ! rset.next() ) do { qtdeGlosas += 1; } while (rset.next()); System.out.println("qtdeGlosas -> " + qtdeGlosas); glosas = new GlosaPrestador[qtdeGlosas]; pst = con.prepareStatement(CONSULTA_GLOSA); pst.setString(1, strCdContrato); pst.setString(2, strNumPeriodo); rset = pst.executeQuery(); qtdeGlosas = 0; while (rset.next()) { glosas[qtdeGlosas] = montaGlosaPrestador(rset); qtdeGlosas++; } // while } catch (SQLException sqle) { sqle.printStackTrace(); throw new Exception( "Não foi possivel estabelecer conexão com a base de " + "dados.Erro:\n" + sqle.getMessage()); } finally { // catch() if (con != null) { con.close(); System.out.println("Fechou a conexao"); } // if } return glosas; } // consultaGlosaPrest()
private static Map saveSites(ExchSite3[] sites, int siteType, int sourceID, Connection c) throws SQLException { PreparedStatement insert = null; PreparedStatement delete = null; try { insert = c.prepareStatement( "insert into dat_customer_sites (site_id, site_type, source_id, internal_name, display_name) " + "values (?, ?, ?, ?, ?)"); delete = c.prepareStatement("delete from dat_customer_sites where site_id = ?"); Map siteIDs = new HashMap(sites.length * 2 + 1); for (int i = 0; i < sites.length; i++) { ExchSite3 site = sites[i]; int siteID = queryLookupSiteId(sourceID, site.getInternalName(), siteType, c); if (siteID == 0) { // if we couldn't find an existing siteID, grab the next one from the sequence siteID = getNextFromSequence("seq_site_id", c); } else { // if there is an existing siteID, delete it so we can insert the changes delete.setInt(1, siteID); int deleted = delete.executeUpdate(); if (deleted != 1) { throw new SQLException("Delete for siteID " + siteID + " returned " + deleted); } } siteIDs.put(site.getInternalName(), siteID); insert.setInt(1, siteID); insert.setInt(2, siteType); insert.setInt(3, sourceID); insert.setString( 4, DirectoryUtils.truncateString(site.getInternalName(), DB_SITE_INTERNALNAME_LENGTH)); insert.setString( 5, DirectoryUtils.truncateString(site.getDisplayName(), DB_SITE_DISPLAYNAME_LENGTH)); insert.executeUpdate(); } return siteIDs; } finally { if (delete != null) { delete.close(); } if (insert != null) { insert.close(); } } }
String getSetting(String key) { Connection con = null; String value = ""; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement("SELECT `value` FROM `settings` WHERE `key` = ?"); s.setString(1, key); s.executeQuery(); ResultSet rs = s.getResultSet(); while (rs.next()) { value = rs.getString("value"); } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } return value; }
public void addToChannelGroup(String group, ChannelInfo channel) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "REPLACE INTO `channel_groups` SET `name` = ?, `channel_id` = (SELECT `id` FROM `channels` WHERE `channel` = ?)"); s.setString(1, group.toLowerCase()); s.setString(2, channel.channel.toLowerCase()); s.executeUpdate(); s.close(); // Will do nothing if the channel is not in the list. if (!this.channel_groups.containsKey(group.toLowerCase())) { this.channel_groups.put(group.toLowerCase(), new HashSet<>()); } this.channel_groups.get(group.toLowerCase()).add(channel); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
public void removeFromChannelGroup(String group, ChannelInfo channel) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "DELETE `channel_groups`.* FROM `channel_groups` INNER JOIN `channels` ON (`channel_groups`.`channel_id` = `channels`.`id`) WHERE `channels`.`channel` = ? AND `channel_groups`.`name` = ?"); s.setString(1, channel.channel); s.setString(2, group); s.executeUpdate(); s.close(); // Will do nothing if the channel is not in the list. this.channel_groups.get(group.toLowerCase()).remove(channel); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
// metoda za pretragu po broju stanovnika public ArrayList<Country> SearchCountryPopulation(long Population) { ArrayList<Country> countries = new ArrayList<Country>(); try { Connection connection = getConnected("world"); PreparedStatement statement = connection.prepareStatement( "SELECT * FROM country WHERE Population <= " + Population + ";"); ResultSet result = statement.executeQuery(); while (result.next()) { countries.add( new Country( result.getString("Code"), result.getString("Name"), result.getString("Continent"), result.getString("Region"), result.getDouble("SurfaceArea"), result.getInt("IndepYear"), result.getLong("Population"), result.getDouble("LifeExpectancy"), result.getDouble("GNP"), result.getDouble("GNPOld"), result.getString("LocalName"), result.getString("GovernmentForm"), result.getString("HeadOfState"), result.getInt("Capital"), result.getString("Code2"))); } connection.close(); } catch (Exception e) { System.out.println(e.toString()); return null; } return countries; }
public int update(ValueObject obj) throws SQLException { if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) { throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : update() "); } PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj; Connection conn = null; PreparedStatement ps = null; int result = 0; StringBuffer sb = new StringBuffer(); sb.append("update PT_KICA_ERR_LOG set ") .append("U_D_FLAG = ") .append(toDB(entity.getU_D_FLAG())) .append(",") .append("YYYYMMDD = ") .append(toDB(entity.getYYYYMMDD())) .append(",") .append("TRANSHOUR = ") .append(toDB(entity.getTRANSHOUR())) .append(",") .append("FILENAME = ") .append(toDB(entity.getFILENAME())) .append(",") .append("ERRLOG = ") .append(toDB(entity.getERRLOG())) .append(",") .append("RESULT_FLAG = ") .append(toDB(entity.getRESULT_FLAG())) .append(",") .append("UPD_DT = ") .append(toDB(entity.getUPD_DT())) .append(",") .append("INS_DT = ") .append(toDB(entity.getINS_DT())) .append(" where 1=1 "); sb.append(" and SEQ = ").append(toDB(entity.getSEQ())); KJFLog.sql(sb.toString()); try { conn = this.getConnection(); ps = conn.prepareStatement(sb.toString()); int i = 1; result = ps.executeUpdate(); } catch (SQLException e) { throw e; } finally { if (ps != null) ps.close(); this.release(conn); } return result; }
public List getBooksByCost(String cost) { List al = new ArrayList(); try { con = JdbcUtil.getMysqlConnection(); ps = con.prepareStatement("select *from jlcbooks where cost=?"); ps.setString(1, cost); rs = ps.executeQuery(); while (rs.next()) { Book bo = new Book(); bo.setBid(rs.getString(1)); bo.setBname(rs.getString(2)); bo.setAuthor(rs.getString(3)); bo.setPub(rs.getString(4)); bo.setCost(rs.getString(5)); bo.setEdi(rs.getString(6)); bo.setIsbn(rs.getString(7)); al.add(bo); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.cleanup(ps, con); } return al; }
void update_war(int db_id, long duration, long remaining, long time_to_start, int current_chain) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "UPDATE `wars` SET `duration` = ? ,`remaining` = ?, `time_to_start` = ?, `current_chain` = ? WHERE id = ?"); s.setLong(1, duration); s.setLong(2, remaining); s.setLong(3, time_to_start); s.setInt(4, current_chain); s.setInt(5, db_id); s.executeUpdate(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
public CFSecuritySecDeviceBuff[] readBuffByUserIdx( CFSecurityAuthorization Authorization, UUID SecUserId) { final String S_ProcName = "readBuffByUserIdx"; ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_read_secdev_by_useridx( ?, ?, ?, ?, ?" + ", " + "?" + " )"; if (stmtReadBuffByUserIdx == null) { stmtReadBuffByUserIdx = cnx.prepareStatement(sql); } int argIdx = 1; stmtReadBuffByUserIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByUserIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtReadBuffByUserIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtReadBuffByUserIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByUserIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtReadBuffByUserIdx.setString(argIdx++, SecUserId.toString()); try { resultSet = stmtReadBuffByUserIdx.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } List<CFSecuritySecDeviceBuff> buffList = new LinkedList<CFSecuritySecDeviceBuff>(); while ((resultSet != null) && resultSet.next()) { CFSecuritySecDeviceBuff buff = unpackSecDeviceResultSetToBuff(resultSet); buffList.add(buff); } int idx = 0; CFSecuritySecDeviceBuff[] retBuff = new CFSecuritySecDeviceBuff[buffList.size()]; Iterator<CFSecuritySecDeviceBuff> iter = buffList.iterator(); while (iter.hasNext()) { retBuff[idx++] = iter.next(); } return (retBuff); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
/** * Returns the siteId for the site with the given source, name, and type. If no such site is * found, this method returns 0; */ private static int queryLookupSiteId( int sourceID, String siteInternalName, int siteType, Connection c) throws SQLException { PreparedStatement ps = null; ResultSet rs = null; try { ps = c.prepareStatement( "select site_id from dat_customer_sites " + "where source_id = ? and internal_name = ? and site_type = ?"); int siteID = 0; ps.setInt(1, sourceID); ps.setString(2, siteInternalName); ps.setInt(3, siteType); rs = ps.executeQuery(); if (rs.next()) { siteID = rs.getInt(1); } return siteID; } finally { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } } }
private static int queryLookupServerId(int sourceID, String serverInternalName, Connection c) throws SQLException { PreparedStatement idSelect = null; ResultSet rs = null; try { int serverId = 0; idSelect = c.prepareStatement( "select server_id from dat_customer_servers " + "where source_id = ? and internal_name = ?"); idSelect.setInt(1, sourceID); idSelect.setString(2, serverInternalName); rs = idSelect.executeQuery(); if (rs.next()) { serverId = rs.getInt(1); } return serverId; } finally { if (rs != null) { rs.close(); } if (idSelect != null) { idSelect.close(); } } }
static void task1() throws FileNotFoundException, IOException, SQLException { // Read Input System.out.println("Task1 Started..."); BufferedReader br = new BufferedReader(new FileReader(inputFile)); br.readLine(); String task1Input = br.readLine(); br.close(); double supportPercent = Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim()); if (supportPercent >= 0) { System.out.println("Task1 Support Percent :" + supportPercent); // Prepare query String task1Sql = "select temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent" + " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i" + " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*" + supportPercent / 100 + " from trans)" + ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent"; PreparedStatement selTask1 = con.prepareStatement(task1Sql); ResultSet rsTask1 = selTask1.executeQuery(); BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1")); while (rsTask1.next()) { bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%"); bw.newLine(); } rsTask1.close(); bw.close(); System.out.println("Task1 Completed...\n"); } else System.out.println("Support percent should be a positive number"); }
public String getCustomerByCard(String cardId) throws ClassNotFoundException, SQLException, Exception { String sql = ""; Connection conn = null; PreparedStatement stm = null; ResultSet result = null; try { conn = getConnection(); try { sql = " select t1.* from t_cif_customer t1,t_pif_card t2 " + " where t1.cut_id = t2.cosumer_id and t2.card_id=" + cardId; stm = conn.prepareStatement(sql); result = stm.executeQuery(); result.next(); CustomerDTO temp = new CustomerDTO(); return result.getString("stuemp_no").toString(); } finally { if (stm != null) { stm.close(); } } } catch (SQLException e) { logger.error("²éѯÊý¾Ý¿âʧ°Ü"); e.printStackTrace(); return null; } catch (Exception e) { logger.error("²éѯÊý¾Ý¿âʧ°Ü"); e.printStackTrace(); return null; } }
void joinChannel(Channel channel) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement("INSERT INTO `channels` (`channel`) VALUES (?)"); s.setString(1, channel.getName().toLowerCase()); s.executeUpdate(); s.close(); if (!this.channel_data.containsKey(channel.getName().toLowerCase())) { ChannelInfo new_channel = new ChannelInfo(channel.getName().toLowerCase()); new_channel.setDefaultOptions(); this.channel_data.put(channel.getName().toLowerCase(), new_channel); } this.saveChannelSettings(this.channel_data.get(channel.getName().toLowerCase())); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
/** * Get waiting User name; * * @return An waiter ArrayList */ public static ArrayList<String> getWaiter() throws SQLException { ArrayList<String> waiterArray = new ArrayList<String>(); Connection conn = DBConn.GetConnection(); PreparedStatement st = conn.prepareStatement("SELECT * FROM cs_conversion where isstart=?"); st.setInt(1, 1); ResultSet rs = st.executeQuery(); while (rs.next()) { if (rs.getString("username").length() != 0) { if (waiterArray.size() == 0) { waiterArray.add(rs.getString("username")); } else { for (int i = 0; i < waiterArray.size(); i++) { if (!rs.getString("username").equals(waiterArray.get(i).toString())) { waiterArray.add(rs.getString("username")); } } } } } rs.close(); st.close(); conn.close(); return waiterArray; }
String getRandomChannelWithVelociraptors(String exclude) { Connection con = null; String value = ""; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "SELECT `channel` FROM `channels` WHERE `channel` != ? AND `active_velociraptors` > 0 ORDER BY (RAND() * active_velociraptors) DESC LIMIT 1;"); s.setString(1, exclude); s.executeQuery(); ResultSet rs = s.getResultSet(); while (rs.next()) { value = rs.getString("channel"); } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } return value; }
public void deleteTSecGroupByTenantVisIdx( CFSecurityAuthorization Authorization, long TenantId, boolean IsVisible) { final String S_ProcName = "deleteTSecGroupByTenantVisIdx"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "exec sp_delete_tsecgrp_by_tenantvisidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?"; if (stmtDeleteByTenantVisIdx == null) { stmtDeleteByTenantVisIdx = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByTenantVisIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByTenantVisIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByTenantVisIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByTenantVisIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByTenantVisIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByTenantVisIdx.setLong(argIdx++, TenantId); if (IsVisible) { stmtDeleteByTenantVisIdx.setString(argIdx++, "Y"); } else { stmtDeleteByTenantVisIdx.setString(argIdx++, "N"); } Object stuff = null; boolean moreResults = stmtDeleteByTenantVisIdx.execute(); while (stuff == null) { try { moreResults = stmtDeleteByTenantVisIdx.getMoreResults(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } if (moreResults) { try { stuff = stmtDeleteByTenantVisIdx.getResultSet(); } catch (SQLException e) { } } else if (-1 == stmtDeleteByTenantVisIdx.getUpdateCount()) { break; } } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public int delete(ValueObject obj) throws SQLException { if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) { throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : delete() "); } PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj; Connection conn = null; PreparedStatement ps = null; int result = 0; StringBuffer sb = new StringBuffer(); sb.append("delete from PT_KICA_ERR_LOG where 1=1") .append(" and SEQ = ") .append(toDB(entity.getSEQ())); KJFLog.sql(sb.toString()); try { conn = this.getConnection(); ps = conn.prepareStatement(sb.toString()); result = ps.executeUpdate(); } catch (SQLException e) { throw e; } finally { if (ps != null) ps.close(); this.release(conn); } return result; }
/** * Returns the messages which ID starts with the given ID * * @param ID message ID * @return text and ID of the message */ public synchronized Hashtable getMessagesWithId(String ID) { // System.out.println("getMessagesWithId:"+ID); Hashtable res = new Hashtable(); Enumeration enm = dict.keys(); while (enm.hasMoreElements()) { String key = "" + enm.nextElement(); if (key.startsWith(ID)) res.put(key, dict.get(key)); } if (res.size() == 0) { Connection connection = null; try { connection = getMessageDBConnection(); PreparedStatement proc = connection.prepareStatement("SELECT tkey,txt FROM messages_texts WHERE tkey like ?"); proc.setString(1, ID + "%"); ResultSet rs = proc.executeQuery(); while (rs.next()) { res.put(rs.getString(1), rs.getString(2)); } connection.close(); } catch (Exception e) { /*not message connection*/ e.printStackTrace(); } } return res; }
public int updateBook(Book bo) { int x = 0; try { con = JdbcUtil.getMysqlConnection(); System.out.println("update 2"); ps = con.prepareStatement( "update jlcbooks set bname=?,author=?,pub=?,cost=?,edition=?,isbn=? where bid=?"); System.out.println("update 3"); System.out.println(bo.getBid()); ps.setString(7, bo.getBid()); ps.setString(1, bo.getBname()); ps.setString(2, bo.getAuthor()); ps.setString(3, bo.getPub()); ps.setString(4, bo.getCost()); ps.setString(5, bo.getEdi()); ps.setString(6, bo.getIsbn()); x = ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.cleanup(ps, con); } return x; }
public void deleteISOTimezone( CFSecurityAuthorization Authorization, CFSecurityISOTimezoneBuff Buff) { final String S_ProcName = "deleteISOTimezone"; ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); short ISOTimezoneId = Buff.getRequiredISOTimezoneId(); String sql = "SELECT " + schema.getLowerDbSchemaName() + ".sp_delete_isotz( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ) as DeletedFlag"; if (stmtDeleteByPKey == null) { stmtDeleteByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByPKey.setShort(argIdx++, ISOTimezoneId); stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision()); ; resultSet = stmtDeleteByPKey.executeQuery(); if (resultSet.next()) { boolean deleteFlag = resultSet.getBoolean(1); if (resultSet.next()) { throw CFLib.getDefaultExceptionFactory() .newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response"); } } else { throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Expected 1 record result set to be returned by delete, not 0 rows"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public CFSecurityTSecGroupBuff lockBuff( CFSecurityAuthorization Authorization, CFSecurityTSecGroupPKey PKey) { final String S_ProcName = "lockBuff"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); long TenantId = PKey.getRequiredTenantId(); int TSecGroupId = PKey.getRequiredTSecGroupId(); String sql = "SELECT * FROM " + schema.getLowerDbSchemaName() + ".sp_lock_tsecgrp( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " )"; if (stmtLockBuffByPKey == null) { stmtLockBuffByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtLockBuffByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtLockBuffByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtLockBuffByPKey.setLong(argIdx++, TenantId); stmtLockBuffByPKey.setInt(argIdx++, TSecGroupId); resultSet = stmtLockBuffByPKey.executeQuery(); if (resultSet.next()) { CFSecurityTSecGroupBuff buff = unpackTSecGroupResultSetToBuff(resultSet); if (resultSet.next()) { throw CFLib.getDefaultExceptionFactory() .newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response"); } return (buff); } else { return (null); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public void UpdateCustomer(CustomerDTO customer) throws ClassNotFoundException, SQLException, Exception { String sql = ""; Connection conn = null; PreparedStatement stm = null; try { conn = getConnection(); String stuempid = customer.getStuemp_no(); sql = "update ykt_cur.t_cif_customer set password='******' where stuemp_no='" + stuempid + "'"; stm = conn.prepareStatement(sql); stm.executeUpdate(); } catch (SQLException e) { logger.error("²éѯÊý¾Ý¿âʧ°Ü"); throw (e); } catch (Exception e) { e.printStackTrace(); throw (e); } finally { if (stm != null) { stm.close(); } } }