@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;
}
/**
* Test importing an unsupported field e.g. datetime, the program should continue and assign null
* value to the field.
*
* @throws Exception
*/
@Test
public void testImportUnsupportedField() throws Exception {
ConnectionProperties p = getConnectionProperties();
Connection cn = DatabaseConnection.getConnection(p);
try {
List<Field> verifiedFields = new Vector<Field>();
String[] fields = "ListingId, Title".split(",");
String tableName = "Listings";
DatabaseConnection.verifyTable(p, tableName, fields, verifiedFields);
Collection<JsonNode> importNodes = new LinkedList<JsonNode>();
JsonNodeFactory f = JsonNodeFactory.instance;
int listingId = 1559350;
ObjectNode n;
n = new ObjectNode(f);
n.put("ListingId", listingId);
importNodes.add(n);
JsonArrayImporter importer = new JsonArrayImporter(p);
importer.doImport(tableName, verifiedFields, importNodes);
Statement st = cn.createStatement();
ResultSet rs = st.executeQuery("SELECT ListingId, Title FROM Listings");
assertTrue("Expected result set to contain a record", rs.next());
assertEquals(listingId, rs.getInt("ListingId"));
assertEquals(null, rs.getString("Title"));
} finally {
cn.close();
}
}
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;
}
}
}
/**
* Get organisation's nomination rater status
*
* @param iOrgID
* @return
* @throws SQLException
* @throws Exception
* @author Desmond
*/
public boolean getNomRater(int iOrgID) throws SQLException, Exception {
String query = "SELECT NominationModule FROM tblOrganization WHERE PKOrganization =" + iOrgID;
boolean iNomRater = true;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iNomRater = rs.getBoolean(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getNomRater - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iNomRater;
} // End getNomRater()
/**
* Get Company ID by OrganisationID
*
* @param OrgID
* @return PKCompany
* @throws SQLException
* @throws Exception
*/
public int getCompanyID(int OrgID) throws SQLException, Exception {
String query = "Select FKCompanyID from tblOrganization WHERE PKOrganization = " + OrgID;
/*db.openDB();
Statement stmt = db.con.createStatement();
ResultSet rs = stmt.executeQuery(query);
if(rs.next())
return rs.getInt(1);*/
int iCompanyID = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iCompanyID = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getCompanyID - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iCompanyID;
}
/* Method Name : isConsulting
* Checks whether login organisation is a Consulting Company
* @param sOrgName
* @param orgCode
* @author Mark Oei
* @since v.1.3.12.63 (09 Mar 2010)
*/
public boolean isConsulting(String orgName) {
String sOrgName = "";
orgName = "\'" + orgName + "\'";
String querySql = "SELECT * FROM tblConsultingCompany WHERE CompanyName = " + orgName;
// Change to disable print statement. Used for debugging only
// Mark Oei 19 Mar 2010
// System.out.println("testing " + orgName);
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(querySql);
if (rs.next()) sOrgName = rs.getString("CompanyName");
} catch (Exception E) {
System.err.println("Organization.java - isConsulting - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
// Change to disable print statement. Used for debugging only
// Mark Oei 19 Mar 2010
// System.out.println("testing " + sOrgName);
if ((sOrgName == null) || (sOrgName == "")) return false;
else return true;
} // End of isConsulting
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);
}
}
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 long nextTSecGroupMemberIdGen(
CFSecurityAuthorization Authorization, CFSecurityTenantPKey PKey) {
final String S_ProcName = "nextTSecGroupMemberIdGen";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Not in a transaction");
}
Connection cnx = schema.getCnx();
long Id = PKey.getRequiredId();
CallableStatement stmtSelectNextTSecGroupMemberIdGen = null;
try {
String sql = "{ call sp_next_tsecgroupmemberidgen( ?" + ", " + "?" + " ) }";
stmtSelectNextTSecGroupMemberIdGen = cnx.prepareCall(sql);
int argIdx = 1;
stmtSelectNextTSecGroupMemberIdGen.registerOutParameter(argIdx++, java.sql.Types.BIGINT);
stmtSelectNextTSecGroupMemberIdGen.setLong(argIdx++, Id);
stmtSelectNextTSecGroupMemberIdGen.execute();
long nextId = stmtSelectNextTSecGroupMemberIdGen.getLong(1);
return (nextId);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (stmtSelectNextTSecGroupMemberIdGen != null) {
try {
stmtSelectNextTSecGroupMemberIdGen.close();
} catch (SQLException e) {
}
stmtSelectNextTSecGroupMemberIdGen = null;
}
}
}
/** Entity Capabilities */
public void setEntityCapsManager(EntityCapsManager manager) {
capsManager = manager;
if (connection.getCapsNode() != null && connection.getHost() != null) {
capsManager.addUserCapsNode(connection.getHost(), connection.getCapsNode());
}
capsManager.addPacketListener(connection);
}
/**
* Returns the discovered items of a given XMPP entity addressed by its JID and note attribute.
* Use this message only when trying to query information which is not directly addressable.
*
* @param entityID the address of the XMPP entity.
* @param node the attribute that supplements the 'jid' attribute.
* @return the discovered items.
* @throws XMPPException if the operation failed for some reason.
*/
public DiscoverItems discoverItems(String entityID, String node) throws XMPPException {
// Discover the entity's items
DiscoverItems disco = new DiscoverItems();
disco.setType(IQ.Type.GET);
disco.setTo(entityID);
disco.setNode(node);
// Create a packet collector to listen for a response.
PacketCollector collector =
connection.createPacketCollector(new PacketIDFilter(disco.getPacketID()));
connection.sendPacket(disco);
// Wait up to 5 seconds for a result.
IQ result = (IQ) collector.nextResult(SmackConfiguration.getPacketReplyTimeout());
// Stop queuing results
collector.cancel();
if (result == null) {
throw new XMPPException("No response from the server.");
}
if (result.getType() == IQ.Type.ERROR) {
throw new XMPPException(result.getError());
}
return (DiscoverItems) result;
}
/**
* Sets the user to run as. This is for the case where the request was generated by the user and
* so the worker must set this value later.
*
* @param cq_id id of this entry in the queue
* @param user user to run the jobs as
*/
public void setRunAs(long cq_id, String user) throws MetaException {
try {
Connection dbConn = getDbConn();
try {
Statement stmt = dbConn.createStatement();
String s = "update COMPACTION_QUEUE set cq_run_as = '" + user + "' where cq_id = " + cq_id;
LOG.debug("Going to execute update <" + s + ">");
if (stmt.executeUpdate(s) != 1) {
LOG.error("Unable to update compaction record");
LOG.debug("Going to rollback");
dbConn.rollback();
}
LOG.debug("Going to commit");
dbConn.commit();
} catch (SQLException e) {
LOG.error("Unable to update compaction queue, " + e.getMessage());
try {
LOG.debug("Going to rollback");
dbConn.rollback();
} catch (SQLException e1) {
}
detectDeadlock(e, "setRunAs");
} finally {
closeDbConn(dbConn);
}
} catch (DeadlockException e) {
setRunAs(cq_id, user);
} finally {
deadlockCnt = 0;
}
}
public void closeConnections() throws SQLException {
if (itemPreparedStatement != null) {
itemPreparedStatement.close();
}
if (itemConnection != null) {
itemConnection.close();
}
if (holdingsPreparedStatement != null) {
holdingsPreparedStatement.close();
}
if (holdingsConnection != null) {
holdingsConnection.close();
}
if (bibResultSet != null) {
bibResultSet.close();
}
if (bibStatement != null) {
bibStatement.close();
}
if (bibConnection != null) {
bibConnection.close();
}
if (connection != null) {
connection.close();
}
}
public void saveSentMessage(COutgoingMessage message) throws Exception {
Statement sqlCmd;
if (connection != null) {
sqlCmd =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
sqlCmd.executeUpdate(
"insert into sms_out (recipient, text, dispatch_date, flash_sms, status_report, src_port, dst_port, validity_period) values ('"
+ message.getRecipient()
+ "', '"
+ message.getText().replaceAll("'", "''")
+ "', "
+ escapeDate(message.getDate(), true)
+ ", "
+ (message.getFlashSms() ? 1 : 0)
+ ", "
+ (message.getStatusReport() ? 1 : 0)
+ ", "
+ message.getSourcePort()
+ ", "
+ message.getDestinationPort()
+ ", "
+ message.getValidityPeriod()
+ ")");
connection.commit();
sqlCmd.close();
}
}
SqlDialect get(DataSource dataSource) {
Connection connection = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
String productName = metaData.getDatabaseProductName();
String productVersion = metaData.getDatabaseProductVersion();
List key = Arrays.asList(productName, productVersion);
SqlDialect dialect = map.get(key);
if (dialect == null) {
final SqlDialect.DatabaseProduct product =
SqlDialect.getProduct(productName, productVersion);
dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString());
map.put(key, dialect);
}
connection.close();
connection = null;
return dialect;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// ignore
}
}
}
}
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);
}
}
}
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 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;
}
}
}
private ImmutableMap<String, JdbcTable> computeTables() {
Connection connection = null;
ResultSet resultSet = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
resultSet = metaData.getTables(catalog, schema, null, null);
final ImmutableMap.Builder<String, JdbcTable> builder = ImmutableMap.builder();
while (resultSet.next()) {
final String tableName = resultSet.getString(3);
final String catalogName = resultSet.getString(1);
final String schemaName = resultSet.getString(2);
final String tableTypeName = resultSet.getString(4);
// Clean up table type. In particular, this ensures that 'SYSTEM TABLE',
// returned by Phoenix among others, maps to TableType.SYSTEM_TABLE.
// We know enum constants are upper-case without spaces, so we can't
// make things worse.
final String tableTypeName2 = tableTypeName.toUpperCase().replace(' ', '_');
final TableType tableType = Util.enumVal(TableType.class, tableTypeName2);
final JdbcTable table = new JdbcTable(this, catalogName, schemaName, tableName, tableType);
builder.put(tableName, table);
}
return builder.build();
} catch (SQLException e) {
throw new RuntimeException("Exception while reading tables", e);
} finally {
close(connection, null, resultSet);
}
}
@SuppressWarnings("unchecked")
@Test
public void testFindCustomersWithConnection() throws Exception {
CustomerDAO dao =
EasyMock.createMockBuilder(CustomerDAO.class)
.addMockedMethod("readNextCustomer")
.addMockedMethod("getCustomerQuery")
.createStrictMock();
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Connection connection = EasyMock.createStrictMock(Connection.class);
Statement statement = EasyMock.createStrictMock(Statement.class);
List<SearchConstraint> constraints = new LinkedList<SearchConstraint>();
EasyMock.expect(dao.getCustomerQuery(constraints)).andReturn("aQuery");
EasyMock.expect(connection.createStatement()).andReturn(statement);
EasyMock.expect(statement.executeQuery("aQuery")).andReturn(resultSet);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
.andReturn(true);
EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
.andReturn(true);
EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
.andReturn(false);
resultSet.close();
EasyMock.expectLastCall();
statement.close();
}
public String getOrganisationName(int iFKOrg) {
String sOrgName = "";
String querySql = "SELECT * FROM tblOrganization WHERE PKOrganization = " + iFKOrg;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(querySql);
if (rs.next()) sOrgName = rs.getString("OrganizationName");
} catch (Exception E) {
System.err.println("Organization.java - getOrganizationName - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return sOrgName;
}
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);
}
}
}
/** Get Organisation ID by User email */
public int getOrgIDbyEmail(String UserEmail) throws SQLException, Exception {
String query = "Select COUNT(*) as TotRecord from tblEmail";
int count = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - editRecord - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return count;
}
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);
}
}
}
/**
* Get organisation's name sequence
*
* @param iOrgID
* @return
* @throws SQLException
* @throws Exception
* @author Maruli
*/
public int getNameSeq(int iOrgID) throws SQLException, Exception {
String query = "SELECT NameSequence FROM tblOrganization WHERE PKOrganization =" + iOrgID;
int iNameSeqe = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iNameSeqe = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getNameSeq - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iNameSeqe;
}
private void getPokemon() {
Connection con = null;
try {
con = pool.getConnection(timeout);
try (Statement s = con.createStatement()) {
ResultSet rs;
s.executeQuery("SELECT `name` FROM `pokemon`");
rs = s.getResultSet();
this.pokemon.clear();
while (rs.next()) {
this.pokemon.add(rs.getString("name"));
}
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);
}
}
}
/**
* 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) {
}
}
}
private void getIgnoreList() {
Connection con = null;
try {
con = pool.getConnection(timeout);
Statement s = con.createStatement();
s.executeQuery("SELECT `name`, `type` FROM `ignores`");
ResultSet rs = s.getResultSet();
this.ignore_list.clear();
this.soft_ignore_list.clear();
while (rs.next()) {
if (rs.getString("type").equals("hard")) {
this.ignore_list.add(rs.getString("name").toLowerCase());
} else {
this.soft_ignore_list.add(rs.getString("name").toLowerCase());
}
}
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);
}
}
}
private void handleKeepAlive() {
Connection c = null;
try {
netCode = ois.readInt();
c = checkConnectionNetCode();
if (c != null && c.getState() == Connection.STATE_CONNECTED) {
oos.writeInt(ACK_KEEP_ALIVE);
oos.flush();
System.out.println("->ACK_KEEP_ALIVE"); // $NON-NLS-1$
} else {
System.out.println("->NOT_CONNECTED"); // $NON-NLS-1$
oos.writeInt(NOT_CONNECTED);
oos.flush();
}
} catch (IOException e) {
System.out.println("handleKeepAlive: " + e.getMessage()); // $NON-NLS-1$
if (c != null) {
c.setState(Connection.STATE_DISCONNECTED);
System.out.println(
"Connection closed with "
+ c.getRemoteAddress()
+ //$NON-NLS-1$
":"
+ c.getRemotePort()); // $NON-NLS-1$
}
}
}
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;
}