@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();
}
}
}