List of usage examples for java.sql Connection isClosed
boolean isClosed() throws SQLException;
Connection object has been closed. From source file:com.cws.esolutions.security.dao.usermgmt.impl.SQLUserManager.java
/** * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#searchUsers(java.lang.String) *//* www. j a va 2s. co m*/ public synchronized List<String[]> searchUsers(final String searchData) throws UserManagementException { final String methodName = SQLUserManager.CNAME + "#searchUsers(final String searchData) throws UserManagementException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("Value: {}", searchData); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<String[]> results = null; try { sqlConn = SQLUserManager.dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{ CALL getUserByAttribute(?, ?) }"); stmt.setString(1, searchData); stmt.setInt(2, 0); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (resultSet.next()) { resultSet.beforeFirst(); results = new ArrayList<String[]>(); while (resultSet.next()) { String[] userData = new String[] { resultSet.getString("cn"), resultSet.getString("uid") }; if (DEBUG) { DEBUGGER.debug("Data: {}", (Object) userData); } results.add(userData); } if (DEBUG) { DEBUGGER.debug("List: {}", results); } } } } catch (SQLException sqx) { throw new UserManagementException(sqx.getMessage(), sqx); } finally { try { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if (!(sqlConn == null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } catch (SQLException sqx) { throw new UserManagementException(sqx.getMessage(), sqx); } } return results; }
From source file:org.executequery.databaseobjects.impl.DefaultDatabaseHost.java
public boolean isConnected() { try {/*w w w .j a v a2s .c o m*/ Connection _connection = getConnection(); return (_connection != null && !_connection.isClosed()); } catch (SQLException e) { return false; } }
From source file:com.cws.esolutions.security.dao.usermgmt.impl.SQLUserManager.java
/** * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#loadUserAccount(java.lang.String) *//* www . j ava2 s .c o m*/ public synchronized List<Object> loadUserAccount(final String userGuid) throws UserManagementException { final String methodName = SQLUserManager.CNAME + "#loadUserAccount(final String guid) throws UserManagementException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("Value: {}", userGuid); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<Object> userAccount = null; try { sqlConn = SQLUserManager.dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{ CALL loadUserAccount(?) }"); stmt.setString(1, userGuid); // common name if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("ResultSet: {}", resultSet); } if (resultSet.next()) { resultSet.last(); int x = resultSet.getRow(); if (DEBUG) { DEBUGGER.debug("x: {}", x); } if ((x == 0) || (x > 1)) { throw new UserManagementException("No user account was located for the provided data."); } resultSet.first(); userAccount = new ArrayList<Object>( Arrays.asList(resultSet.getString(userAttributes.getCommonName()), resultSet.getString(userAttributes.getUserId()), resultSet.getString(securityAttributes.getLockCount()), resultSet.getString(securityAttributes.getLastLogin()), resultSet.getString(securityAttributes.getExpiryDate()), resultSet.getString(userAttributes.getSurname()), resultSet.getString(userAttributes.getGivenName()), resultSet.getString(userAttributes.getDisplayName()), resultSet.getString(userAttributes.getEmailAddr()), resultSet.getString(userAttributes.getTelephoneNumber()), resultSet.getString(userAttributes.getMemberOf()), resultSet.getString(securityAttributes.getIsSuspended()), resultSet.getString(securityAttributes.getOlrSetupReq()), resultSet.getString(securityAttributes.getOlrLocked()))); if (DEBUG) { DEBUGGER.debug("UserAccount: {}", userAccount); } } } else { throw new UserManagementException("No users were located with the provided information"); } } catch (SQLException sqx) { throw new UserManagementException(sqx.getMessage(), sqx); } finally { try { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if (!(sqlConn == null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } catch (SQLException sqx) { throw new UserManagementException(sqx.getMessage(), sqx); } } return userAccount; }
From source file:com.googlecode.datasourcetester.server.DataSourceTesterServiceImpl.java
public String[][] queryDataSource(String dataSourceJndiName, String query) { Connection conn = null; try {// w w w .j a v a 2 s . c o m InitialContext jndiContext = new InitialContext(); DataSource ds = (DataSource) jndiContext.lookup(dataSourceJndiName); conn = ds.getConnection(); PreparedStatement stmt = conn.prepareStatement(query); ResultSet rs = stmt.executeQuery(); ResultSetMetaData resMeta = rs.getMetaData(); LinkedList<String[]> rowList = new LinkedList<String[]>(); String[] colLabels = new String[resMeta.getColumnCount()]; for (int colNr = 1; colNr <= resMeta.getColumnCount(); colNr++) { colLabels[colNr - 1] = resMeta.getColumnName(colNr); } rowList.add(colLabels); while (rs.next()) { String[] rowData = new String[resMeta.getColumnCount()]; for (int colNr = 1; colNr <= resMeta.getColumnCount(); colNr++) { rowData[colNr - 1] = rs.getString(colNr); } rowList.add(rowData); } conn.close(); return rowList.toArray(new String[rowList.size()][]); } catch (Exception e) { logger.error(e.getMessage(), e); try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException sqlEx) { logger.error(sqlEx.getMessage(), sqlEx); } return null; } }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
public Vector<String> getRegisteredGatewayRegNames() { Vector<String> retVect = new Vector<String>(); java.sql.Connection conn = null; try {/*from w w w .j a va 2 s .c om*/ Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute("SELECT registeredName FROM `" + dbSchemaStr + "`.`registeredgateway` ")) { rs = stmt.getResultSet(); } if (rs != null) { while (rs.next()) { String registeredName = rs.getString("registeredName") == null ? "" : rs.getString("registeredName"); if (!registeredName.isEmpty() && !registeredName.equalsIgnoreCase("")) { retVect.add(registeredName); } } } } catch (SQLException ex) { // handle any errors System.err.println("SQLException2: " + ex.getMessage()); System.err.println("SQLState2: " + ex.getSQLState()); System.err.println("VendorError2: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } return retVect; }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
public Vector<String> getRegisteredUserRegNames() { Vector<String> retVect = new Vector<String>(); java.sql.Connection conn = null; try {/*from w w w . jav a 2s. c o m*/ Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute("SELECT login FROM `" + dbSchemaStr + "`.`users` ")) { rs = stmt.getResultSet(); } if (rs != null) { while (rs.next()) { String registeredLogin = rs.getString("login") == null ? "" : rs.getString("login"); if (!registeredLogin.isEmpty() && !registeredLogin.equalsIgnoreCase("")) { retVect.add(registeredLogin); } } } } catch (SQLException ex) { // handle any errors System.err.println("SQLException2: " + ex.getMessage()); System.err.println("SQLState2: " + ex.getSQLState()); System.err.println("VendorError2: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } return retVect; }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
synchronized public void updateRcvGatewayAdTimestamp(String pGatewayRegisteredName, boolean removeTimeStampFlag) { java.sql.Connection conn = null; try {/*from w ww . ja v a 2 s. c om*/ Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (!removeTimeStampFlag) { if (stmt.execute("UPDATE `" + dbSchemaStr + "`.`registeredgateway` SET lastadvtimestamp = UNIX_TIMESTAMP(now()) WHERE registeredName=\'" + pGatewayRegisteredName + "\'")) { rs = stmt.getResultSet(); // TODO: this is not needed here... } } else { if (stmt.execute("UPDATE `" + dbSchemaStr + "`.`registeredgateway` SET lastadvtimestamp = 0 WHERE registeredName=\'" + pGatewayRegisteredName + "\'")) { rs = stmt.getResultSet(); // TODO: this is not needed here... } } } catch (SQLException ex) { // handle any errors System.err.println("SQLException3: " + ex.getMessage()); System.err.println("SQLState3: " + ex.getSQLState()); System.err.println("VendorError3: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } // DEBUG //System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
public Vector<DBRegisteredUsers> getRegisteredUsersEntries() { Vector<DBRegisteredUsers> retVect = new Vector<DBRegisteredUsers>(); java.sql.Connection conn = null; try {//from w ww.ja v a 2s .co m Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute( "SELECT idusers, passwd, login, email, idrole, role_name, lastadvtimestamp, disabled, FROM_UNIXTIME(lastadvtimestamp, \'%d/%m/%Y %H:%i:%s\') lastdate FROM vitrofrontenddb.roles AS r JOIN (vitrofrontenddb.userinrolesmr as ur JOIN vitrofrontenddb.users AS u ON u.idusers = ur.iduser) ON r.idroles=ur.idrole ")) { rs = stmt.getResultSet(); } if (rs != null) { while (rs.next()) { int userId = rs.getInt("idusers"); String passwd = rs.getString("passwd") == null ? "" : rs.getString("passwd"); // this is the one used in registration messages String loginName = rs.getString("login") == null ? "" : rs.getString("login"); String emailAddress = rs.getString("email") == null ? "" : rs.getString("email"); String role_name = rs.getString("role_name") == null ? "" : rs.getString("role_name"); int role = rs.getInt("idrole"); int lastadvtimestampInt = rs.getInt("lastadvtimestamp"); String lastdate = rs.getString("lastdate") == null ? "N/A" : rs.getString("lastdate"); Boolean status = rs.getBoolean("disabled"); if (!loginName.isEmpty() && !loginName.equalsIgnoreCase("")) { DBRegisteredUsers entryRegisterUsers = new DBRegisteredUsers(userId, loginName, passwd, emailAddress, role, role_name, lastadvtimestampInt, lastdate, status); retVect.addElement(entryRegisterUsers); } } } } catch (SQLException ex) { // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { System.out.println("SQLException on rs close(): " + sqlEx.getMessage()); } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { System.out.println("SQLException on stmt close(): " + sqlEx.getMessage()); } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } return retVect; }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
synchronized public void deleteRegisteredGateway(String pGatewayRegisteredName) { StringBuilder tmpIgnoredOutput = new StringBuilder(); if (isRegisteredGateway(pGatewayRegisteredName, tmpIgnoredOutput)) { java.sql.Connection conn = null; try {/*from www .ja va2s .c o m*/ Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute( "DELETE FROM `" + dbSchemaStr + "`.`registeredgateway` WHERE registeredName=\'" + pGatewayRegisteredName + "\'")) { rs = stmt.getResultSet(); // TODO: this is not needed here... } } catch (SQLException ex) { // handle any errors System.err.println("SQLException3: " + ex.getMessage()); System.err.println("SQLState3: " + ex.getSQLState()); System.err.println("VendorError3: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } } }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
synchronized public boolean isRegisteredGateway(String pGatewayRegisteredName, StringBuilder out_descFromDB) { boolean retBool = false; java.sql.Connection conn = null; try {// ww w. jav a2s. co m Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute( "SELECT idregisteredgateway, registeredName, friendlyName, friendlyDescription, ip, listeningport, FROM_UNIXTIME(lastadvtimestamp, \"%d/%m/%Y %H:%i:%s\") lastdate FROM `" + dbSchemaStr + "`.`registeredgateway` WHERE registeredName=\'" + pGatewayRegisteredName + "\'")) { rs = stmt.getResultSet(); } if (rs != null) { while (rs.next()) { int gateId = rs.getInt("idregisteredgateway"); String registeredName = rs.getString("registeredName") == null ? "" : rs.getString("registeredName"); // this is the one used in registration messages String friendlyName = rs.getString("friendlyName") == null ? "" : rs.getString("friendlyName"); String friendlyDescription = rs.getString("friendlyDescription") == null ? "" : rs.getString("friendlyDescription"); out_descFromDB.append(friendlyName); String gateIp = rs.getString("ip") == null ? "" : rs.getString("ip"); String gatePort = rs.getString("listeningport") == null ? "" : rs.getString("listeningport"); String lastdate = rs.getString("lastdate") == null ? "N/A" : rs.getString("lastdate"); if (registeredName.equalsIgnoreCase(pGatewayRegisteredName)) { retBool = true; break; } } } } catch (SQLException ex) { // handle any errors System.err.println("SQLException4: " + ex.getMessage()); System.err.println("SQLState4: " + ex.getSQLState()); System.err.println("VendorError4: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } // DEBUG //System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } return retBool; }