/** All calls to connect must have a corresponding call to disconnect() in a finally block. */
protected void connect() throws IOException, SQLException {
boolean needToConnect = false;
if (connection == null) {
needToConnect = true;
} else if (connection.isClosed()) {
needToConnect = true;
} else if (lastConnected < System.currentTimeMillis() - 10000) {
// If we connected more than 10 seconds ago, we should re-test
// the connection explicitely, because isClosed may return false,
// even if the connection will fail. The only real way to test
// if the connection is actually open is to run a test query, but
// doing that too often will cause unneccessary delay, so we
// wait an arbitrary amount, in this case, 10 seconds.
try {
if (!connection.isValid(3)) {
needToConnect = true;
}
} catch (AbstractMethodError ex) {
// isValid was added later, some connection types may not have that method.
try {
connection.createStatement().execute(getTestQuery());
} catch (SQLException e) {
needToConnect = true;
}
}
}
if (needToConnect) {
connection = DriverManager.getConnection(getConnectionString());
}
}
@Inject
public DBAccessImpl() {
Connection testConnection = null;
try {
InitialContext ic = new InitialContext();
Context evtContext = (Context) ic.lookup("java:comp/env/");
dataSource = (DataSource) evtContext.lookup("jdbc/codesearch");
if (dataSource != null) {
testConnection = dataSource.getConnection();
if (testConnection.isValid(3)) {
LOG.info("Successfully connected to database");
}
} else {
LOG.error("Database is not configured, code analysis will not be available");
}
} catch (SQLException ex) {
LOG.error("Error accessing database, code analysis will not be available:\n", ex);
} catch (NamingException ex) {
LOG.error("Error accessing database, code analysis will not be available:\n", ex);
} finally {
if (testConnection != null) {
try {
testConnection.close();
} catch (Exception ex) {
LOG.warn("Could not close test database connection:\n", ex);
}
}
}
}
/**
*
* @throws Exception
*/
public void testBug56122() throws Exception {
for (final Connection testConn : new Connection[] { this.conn, getFailoverConnection(), getLoadBalancedConnection(),
getMasterSlaveReplicationConnection() }) {
testConn.createClob();
testConn.createBlob();
testConn.createNClob();
testConn.createSQLXML();
testConn.isValid(12345);
testConn.setClientInfo(new Properties());
testConn.setClientInfo("NAME", "VALUE");
testConn.getClientInfo();
testConn.getClientInfo("CLIENT");
assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
public Void call() throws Exception {
testConn.createArrayOf("A_TYPE", null);
return null;
}
});
assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
public Void call() throws Exception {
testConn.createStruct("A_TYPE", null);
return null;
}
});
}
}
public boolean isConnectionValid(int timeoutInSeconds) {
long startTime = System.currentTimeMillis();
try {
if (connection_ == null) {
return false;
} else if (!connection_.isValid(timeoutInSeconds)) {
connection_.close();
return false;
} else {
return true;
}
} catch (Exception e) {
try {
connection_.close();
} catch (Exception e2) {
}
long timeElapsed = System.currentTimeMillis() - startTime;
logger.error(
"Method=IsConnectionValid"
+ ", TimeElapsed="
+ timeElapsed
+ ", Exception="
+ e.toString()
+ System.lineSeparator()
+ StackTrace.getStringFromStackTrace(e));
return false;
}
}
private Connection getValidConnection2(long time, long timeoutTime) {
long rtime = Math.max(1, timeoutTime - time);
Connection conn;
try {
conn = getConnection2(rtime);
} catch (SQLException e) {
return null;
}
rtime = timeoutTime - System.currentTimeMillis();
int rtimeSecs = Math.max(1, (int) ((rtime + 999) / 1000));
try {
if (conn.isValid(rtimeSecs)) {
return conn;
}
} catch (SQLException e) {
}
// This Exception should never occur. If it nevertheless occurs, it's because of an error in the
// JDBC driver which we ignore and assume that the connection is not valid.
// When isValid() returns false, the JDBC driver should have already called
// connectionErrorOccurred()
// and the PooledConnection has been removed from the pool, i.e. the PooledConnection will
// not be added to recycledConnections when Connection.close() is called.
// But to be sure that this works even with a faulty JDBC driver, we call purgeConnection().
purgeConnection(conn);
return null;
}
public void populateProdTypeValues() {
try {
Connection con = Main.dbConnection;
if (!con.isValid(0)) {
con = Main.reconnect();
}
productTypeValues.clear();
PreparedStatement stmt =
con.prepareStatement(
"select value, code, seq, lov_lookup_id from lov_lookup where code='PRODUCT_TYPE' order by seq");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
productTypeValues.add(rs.getString(1));
}
typeTF.getItems().addAll(productTypeValues);
} catch (SQLException e) {
Main._logger.debug("Error :", e);
e.printStackTrace();
} catch (Exception e) {
Main._logger.debug("Error :", e);
e.printStackTrace();
}
}
public void populateBillCategoryValues() {
try {
Connection con = Main.dbConnection;
if (!con.isValid(0)) {
con = Main.reconnect();
}
billCategoryValues = FXCollections.observableArrayList();
PreparedStatement stmt =
con.prepareStatement(
"select distinct bill_category from point_name order by bill_category");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
if (billCategoryValues != null && !billCategoryValues.contains(rs.getString(1)))
billCategoryValues.add(rs.getString(1).toLowerCase());
}
billCategoryTF.setItems(billCategoryValues);
new AutoCompleteComboBoxListener<>(billCategoryTF);
} catch (SQLException e) {
Main._logger.debug("Error :", e);
e.printStackTrace();
} catch (Exception e) {
Main._logger.debug("Error :", e);
e.printStackTrace();
}
}
private boolean productExistsInCategory(String name, String category) {
try {
Connection con = Main.dbConnection;
if (!con.isValid(0)) {
con = Main.reconnect();
}
String query =
"select count(*) from products where lower(name)=? and lower(bill_category)=? and product_id<>?";
PreparedStatement stmt = con.prepareStatement(query);
stmt.setString(1, name.toLowerCase());
stmt.setString(2, category.toLowerCase());
stmt.setLong(3, productRow.getProductId());
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
if (rs.getInt(1) > 0) return true;
}
} catch (SQLException e) {
Main._logger.debug("Error :", e);
e.printStackTrace();
} catch (Exception e) {
Main._logger.debug("Error :", e);
e.printStackTrace();
}
return false;
}
/**
* * Negative test isValid() method
*
* @throws Exception
*/
@Test
public void testIsValidNeg() throws Exception {
miniHiveKdc.loginUser(MiniHiveKdc.HIVE_TEST_SUPER_USER);
hs2Conn = DriverManager.getConnection(miniHS2.getJdbcURL());
hs2Conn.close();
assertFalse(hs2Conn.isValid(1000));
}
/**
* Are we connected to the database?
*
* @return Connected
* @throws SQLException
*/
public boolean isConnected() {
try {
return connection.isValid(5);
} catch (SQLException ex) {
this.logger.log(Level.SEVERE, "isConnected error!", ex);
}
return false;
}
public boolean isConnectionValid() {
try {
return conn.isValid(0);
} catch (SQLException e) {
printSQLException(e);
return false;
}
}
protected Connection getConnection() {
try {
if (connection != null && connection.isValid(6)) return connection;
} catch (SQLException e) {
e.printStackTrace();
}
return connection = createConnection();
}
public boolean isValid(int timeout) throws SQLException {
String methodCall = "isValid(" + timeout + ")";
try {
return reportReturn(methodCall, realConnection.isValid(timeout));
} catch (SQLException s) {
reportException(methodCall, s);
throw s;
}
}
/** {@inheritDoc} */
@Override
public boolean isValid() {
try {
return connection.isValid(0);
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
/** Attempts to restore connection to the database. */
private void restoreConnection() {
try {
if (conn.isClosed() || !conn.isValid(3)) {
PcBuilder.LOG.log(Level.INFO, "Trying to restore MySQL connection.");
close();
conn = DriverManager.getConnection(args[0], args[1], args[2]);
}
} catch (SQLException e) {
PcBuilder.LOG.log(Level.SEVERE, "Failed to restore MySQL connection.", e);
}
}
public static boolean isConnected() {
try {
if (connection != null && connection.isValid(1)) {
return true;
}
} catch (SQLException ex) {
Bukkit.getConsoleSender().sendMessage("§cMySQL could not check for connection!");
ex.printStackTrace();
}
return false;
}
public static SQLTx beginTransaction(Connection conn) throws SQLException {
SQLTx helper = new SQLTx();
if (conn != null && conn.isValid(0)) {
helper.setConnection(conn);
conn.setAutoCommit(false);
} else {
throw new IllegalArgumentException(
"Cannot begin transaction. Connection is null or not valid!");
}
return helper;
}
/**
* Returns the globally unique instance of Connection.
*
* <p>If the instance is null or the connection is not valid anymore, {@link #initConnection()} is
* being called before returning.
*
* @return The globally unique instance of Connection
*/
public static Connection getConnection() {
if (connection != null) {
try {
if (connection.isValid(2)) {
return connection;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return initConnection();
}
@Test
public void testConnection() throws Exception {
Connection conn = getConnection();
assertNull(conn.getMetaData());
assertFalse(conn.getAutoCommit());
conn.setAutoCommit(true);
assertTrue(conn.getAutoCommit());
conn.setAutoCommit(false);
assertTrue(conn.isReadOnly());
conn.setReadOnly(true);
assertTrue(conn.isReadOnly());
assertEquals(Connection.TRANSACTION_NONE, conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
assertEquals(Connection.TRANSACTION_READ_UNCOMMITTED, conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
assertNull(conn.getWarnings());
conn.clearWarnings();
assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability());
conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability());
conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
conn.commit();
conn.rollback();
assertTrue(conn.isValid(0));
conn.setClientInfo("prop1", "value1");
Properties props = new Properties();
props.setProperty("prop2", "value2");
props.setProperty("prop3", "value3");
conn.setClientInfo(props);
assertEquals("value1", conn.getClientInfo("prop1"));
assertEquals("value2", conn.getClientInfo("prop2"));
assertEquals("value3", conn.getClientInfo("prop3"));
Properties props2 = conn.getClientInfo();
assertEquals("value1", props2.getProperty("prop1"));
assertEquals("value2", props2.getProperty("prop2"));
assertEquals("value3", props2.getProperty("prop3"));
assertNotNull(((JcrJdbcConnection) conn).getJcrSession());
assertFalse(conn.isClosed());
conn.close();
assertTrue(conn.isClosed());
}
public synchronized Connection getConnection() {
if (connection == null) {
createConnection();
} else {
try {
if (connection.isClosed()) createConnection();
if (!connection.isValid(0)) createConnection();
} catch (SQLException ex) {
createConnection();
}
}
return connection;
}
private boolean validateConnection() {
if (MyConnection == null) return false;
else
try {
if (MyConnection.isValid(0)) return true;
else {
return false; // it would work when created connection looses it's validity because of
// MySQL being turned off during the usage of this application or any
// connection kill (by administrator)
}
} catch (SQLException e) {
return false; // see the manual of isValid function of
}
}
public void removeGuest(int id) throws SQLException {
conn = DBTool.getInstance();
System.out.println("Removing guest...");
if (conn.isValid(10000)) {
Statement s = conn.createStatement();
s.execute("DELETE FROM guest WHERE idguest = " + id + ";");
System.out.println("Remove complete!");
s.close();
conn.close();
} else {
System.out.println("Connection not valid");
conn.close();
}
}
/**
* 测试一个连接是否可用，如果不可用，关掉它并返回 false
*
* <p>否则可用返回 true
*
* @param conn 需要测试的数据库连接
* @return 返回 true 表示此连接可用， false 表示不可用
*/
private boolean isValid(Connection conn) {
try {
return conn.isValid(3000);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
// try {
//
// // 判断测试表是否存在
//
// if (testTable.equals("")) {
//
// // 如果测试表为空，试着使用此连接的 setAutoCommit() 方法
//
// // 来判断连接否可用（此方法只在部分数据库可用，如果不可用 ,
//
// // 抛出异常）。注意：使用测试表的方法更可靠
//
// conn.setAutoCommit(true);
//
// } else {// 有测试表的时候使用测试表测试
//
// // check if this connection is valid
//
// Statement stmt = conn.createStatement();
//
// stmt.execute("select count(*) from " + testTable);
//
// }
//
// } catch (SQLException e) {
//
// // 上面抛出异常，此连接己不可用，关闭它，并返回 false;
//
// closeConnection(conn);
//
// return false;
//
// }
//
// // 连接可用，返回 true
//
// return true;
}
public Connection getDbConnection() {
Connection conn = null;
int retry = 0;
do {
try {
conn = pool.poll(10000, TimeUnit.MILLISECONDS);
if (conn == null || !conn.isValid(0)) {
conn = DriverManager.getConnection(url);
}
} catch (Exception ex) {
_logger.warn(ex);
}
++retry;
} while (conn == null && retry < 3);
return conn;
}
@Override
public boolean isValid(Connection c) {
boolean valid = false;
try {
valid = c.isValid(getValidateTimeOutS());
// See https://github.com/brettwooldridge/HikariCP/wiki/Pool-Analysis
// SQL warnings should be cleared.
if (valid) {
c.clearWarnings();
}
} catch (SQLException e) {
log.info("Database connection invalid.", e);
}
return valid;
}
private void jButtonTestActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButtonTestActionPerformed
try {
String driverlib = jtxtDbDriverLib.getText();
String driver = jtxtDbDriver.getText();
String url = jtxtDbURL.getText();
String user = jtxtDbUser.getText();
String password = new String(jtxtDbPassword.getPassword());
ClassLoader cloader = new URLClassLoader(new URL[] {new File(driverlib).toURI().toURL()});
DriverManager.registerDriver(
new DriverWrapper((Driver) Class.forName(driver, true, cloader).newInstance()));
Session session = new Session(url, user, password);
Connection connection = session.getConnection();
boolean isValid;
isValid = (connection == null) ? false : connection.isValid(1000);
if (isValid) {
JOptionPane.showMessageDialog(
this,
AppLocal.getIntString("message.databasesuccess"),
"Connection Test",
JOptionPane.INFORMATION_MESSAGE);
} else {
JMessageDialog.showMessage(
this, new MessageInf(MessageInf.SGN_WARNING, "Connection Error"));
}
} catch (InstantiationException
| IllegalAccessException
| MalformedURLException
| ClassNotFoundException e) {
JMessageDialog.showMessage(
this,
new MessageInf(
MessageInf.SGN_WARNING, AppLocal.getIntString("message.databasedrivererror"), e));
} catch (SQLException e) {
JMessageDialog.showMessage(
this,
new MessageInf(
MessageInf.SGN_WARNING, AppLocal.getIntString("message.databaseconnectionerror"), e));
} catch (Exception e) {
JMessageDialog.showMessage(
this, new MessageInf(MessageInf.SGN_WARNING, "Unknown exception", e));
}
} // GEN-LAST:event_jButtonTestActionPerformed
public void deletePerson(Person p) throws SQLException {
conn = DBTool.getInstance();
System.out.println("Deleting person person...");
if (conn.isValid(10000)) {
Statement s = conn.createStatement();
int idperson = p.getId();
s.execute("DELETE FROM guest WHERE idperson = " + idperson + ";");
s.execute("DELETE FROM person WHERE idperson = " + idperson + ";");
System.out.println("Deletion complete!");
s.close();
conn.close();
} else {
System.out.println("Connection not valid");
conn.close();
}
}
@Override
public Connection getConnection() throws SQLException {
Connection mockConnection = super.getConnection();
when(mockConnection.isValid(anyInt())).thenReturn(!shouldFail);
doAnswer(
new Answer<Void>() {
@Override
public Void answer(InvocationOnMock invocation) throws Throwable {
if (shouldFail) {
TimeUnit.SECONDS.sleep(2);
}
return null;
}
})
.when(mockConnection)
.close();
return mockConnection;
}
public static int ExecUpdateQuery(String query) {
int retValue = 0;
try {
if (myConnection == null || (myConnection != null && !myConnection.isValid(0))) {
Class.forName(mysJDBCDriver).newInstance();
myConnection = DriverManager.getConnection(url, username, password);
}
myPreparedStatement = myConnection.prepareStatement(query);
retValue = myPreparedStatement.executeUpdate();
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
} catch (InstantiationException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
}
return retValue;
}
/**
* desc: 获取Neo4j Connection实例<br>
* date: Oct 31, 2014 4:39:04 PM<br>
*
* @author 开发者真实姓名[HuPeng]
* @return
*/
private static Connection getConn() {
try {
if (conn != null) {
if (!conn.isClosed() || conn.isValid(5)) {
return conn;
} else {
conn.close();
conn = null;
}
}
Properties p = new Properties();
p.put("legacy", new Object());
conn = new Driver().connect(url, p);
} catch (SQLException e) {
conn = null;
LOG.error("NEO4J START ERROR, PLEASE CHECK NEO4J SERVER:" + e.getMessage());
}
return conn;
}