/**
* Read SQL query.
*
* @param sql SQL query to read
* @return the rows in this SQL query
*/
public HashMap<Integer, ArrayList<String>> read(String sql) {
ResultSet resultSet;
HashMap<Integer, ArrayList<String>> rows = new HashMap<Integer, ArrayList<String>>();
if (checkConnected()) {
try {
PreparedStatement statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
ArrayList<String> column = new ArrayList<String>();
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
column.add(resultSet.getString(i));
}
rows.put(resultSet.getRow(), column);
}
statement.close();
} catch (SQLException ex) {
printErrors(ex);
}
}
return rows;
}
/**
* Attempt to write the SQL query.
*
* @param sql Query to write.
* @return true if the query was successfully written, false otherwise.
*/
public boolean write(String sql) {
if (checkConnected()) {
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.executeUpdate();
return true;
} catch (SQLException ex) {
printErrors(ex);
return false;
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
printErrors(e);
return false;
}
}
}
}
return false;
}
/**
* Get the Integer. Only return first row / first field.
*
* @param sql SQL query to execute
* @return the value in the first row / first field
*/
public int getInt(String sql) {
ResultSet resultSet;
int result = 0;
if (checkConnected()) {
try {
PreparedStatement statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
if (resultSet.next()) {
result = resultSet.getInt(1);
} else {
result = 0;
}
statement.close();
} catch (SQLException ex) {
printErrors(ex);
}
}
return result;
}
/**
* Check connection status and re-establish if dead or stale.
*
* <p>If the very first immediate attempt fails, further attempts will be made in progressively
* larger intervals up to MAX_WAIT intervals.
*
* <p>This allows for MySQL to time out idle connections as needed by server operator, without
* affecting McMMO, while still providing protection against a database outage taking down
* Bukkit's tick processing loop due to attemping a database connection each time McMMO needs the
* database.
*
* @return the boolean value for whether or not we are connected
*/
public static boolean checkConnected() {
boolean isClosed = true;
boolean isValid = false;
boolean exists = (connection != null);
// If we're waiting for server to recover then leave early
if (nextReconnectTimestamp > 0 && nextReconnectTimestamp > System.nanoTime()) {
return false;
}
if (exists) {
try {
isClosed = connection.isClosed();
} catch (SQLException e) {
isClosed = true;
e.printStackTrace();
printErrors(e);
}
if (!isClosed) {
try {
isValid = connection.isValid(VALID_TIMEOUT);
} catch (SQLException e) {
// Don't print stack trace because it's valid to lose idle connections
// to the server and have to restart them.
isValid = false;
}
}
}
// Leave if all ok
if (exists && !isClosed && isValid) {
// Housekeeping
nextReconnectTimestamp = 0;
reconnectAttempt = 0;
return true;
}
// Cleanup after ourselves for GC and MySQL's sake
if (exists && !isClosed) {
try {
connection.close();
} catch (SQLException ex) {
// This is a housekeeping exercise, ignore errors
}
}
// Try to connect again
connect();
// Leave if connection is good
try {
if (connection != null && !connection.isClosed()) {
// Schedule a database save if we really had an outage
if (reconnectAttempt > 1) {
plugin
.getServer()
.getScheduler()
.scheduleSyncDelayedTask(plugin, new SQLReconnect(plugin), 5);
}
nextReconnectTimestamp = 0;
reconnectAttempt = 0;
return true;
}
} catch (SQLException e) {
// Failed to check isClosed, so presume connection is bad and attempt later
e.printStackTrace();
printErrors(e);
}
reconnectAttempt++;
nextReconnectTimestamp =
(long)
(System.nanoTime()
+ Math.min(MAX_WAIT, (reconnectAttempt * SCALING_FACTOR * MIN_WAIT)));
return false;
}
