/*
* This file is part of aion-emu <aion-emu.com>.
*
* aion-emu is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* aion-emu is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with aion-emu. If not, see <http://www.gnu.org/licenses/>.
*/
package com.aionemu.commons.database;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;
/**
* <b>DB Documentation</b>
* <p>
* This class is used for making SQL query's utilizing the database connection defined in database.properties<br>
* <br>
* Here are the functions that one may use to utilize this class in creating an ease of access to database information.
* </p>
* <hr>
* <b>SELECT (select method)</b>
* <p>
* Parameters:
* <ul>
* <li><b>Required: String query</b> - Query that will be utilized in select statement.</li>
* <li><b>Required: ReadStH reader</b> - Interface implementation used to read output ResultSet from select statement.</li>
* <li><i>Optional: String errMsg</i> - Custom error message that will be logged if query fails.</li>
* </ul>
* Returns:(<b>boolean </b>) Returns true if the query ran successfully.<br>
* <br>
* Purpose:<br>
* The select function one to grab data from the database with ease. Utilizing the ReadStT, one may set up query
* parameters (then use ParamReadStH and set params in <code>setParams()</code>) and read the replied data from the
* query easily.<br>
* <br>
* Best practices is to create custom classes that implement ReadStT in order to throw the data around.<br>
* <br>
* After the function is called, it automatically closes and recycles the SQL Connection.<br>
* <br>
* Example:
*
* <pre>
* DB.select("SELECT name FROM test_table WHERE id=?", new ParamReadStH(){
* public void setParams(PreparedStatement stmt) throws SQLException
* {
* stmt.setInt(1, 50);
* }
*
* public void handleRead(ResultSet rset) throws SQLException
* {
* while(rset.next())
* {
* // Usually here in the custom class you would set it to your needed var.
* var = rset.getString("name");
* }
* }
* });
* </pre>
*
* </p>
* <hr>
* <b>INSERT / UPDATE (insertUpdate method)</b>
* <p>
* Parameters:
* <ul>
* <li><b>Required: String query</b> - Query that will be executed in insert/update statement.</li>
* <li><b>Required: IUStT batch</b> - Util used to modify query parameters OR add add batches.</li>
* <li><i>Optional: String errMsg</i> - Custom error message that will be logged if query fails.</li>
* </ul>
* Returns:(<b>boolean</b>) Returns true if the query ran successfully.<br>
* <br>
* Purpose:<br>
* The insertUpdate function allows one to insert and update database entries. One may utilize it without needing to
* modify the query at all or provide a IUStH interface implementation to add parameters to statement and/or gather them
* in batch.<br>
* <br>
* <b> If the IUStH util IS provided in the function's parameters - The coder MUST call the functions
* stmt.executeBatch() OR stmt.executeUpdate() in order to successfully run the query.<br>
* If IUSth util is NOT provided, the query will execute as it is. </b><br>
* <br>
* Best practices is to create custom classes that implement IUStT in order to modify the query in proficient manners.<br>
* <br>
* After the function is called, it automatically closes and recycles the SQL Connection.<br>
* <br>
* Example:<br>
*
* <pre>
* DB.insertUpdate("UPDATE test_table SET some_column=1");
* </pre>
*
* <br>
*
* <pre>
* DB.insertUpdate("INSERT INTO test_table VALUES (?)", new IUStH(){
* public void handleInsertUpdate(PreparedStatement stmt)
* {
* // Usually this would be data from the custom class that implements IUSth
* String[] batchTestVars = { "bob", "mike", "joe" };
*
* for(String n : batchTestVars)
* {
* stmt.setString(1, n);
* stmt.addBatch();
* }
*
* // REQUIRED
* stmt.executeBatch();
* }
* });
*
* </pre>
*
* <br>
*
* <pre>
* DB.insertUpdate("UPDATE test_table SET some_column=? WHERE other_column=?", new IUStH(){
* public void handleInsertUpdate(PreparedStatement stmt)
* {
* stmt.setString(1, "xxx");
* stmt.setInt(2, 10);
* stmt.executeUpdate();
* }
* });
*
* </pre>
*
* </p>
*
* @author Disturbing
*/
public final class DB
{
/** Logger */
protected static final Logger log = Logger.getLogger(DB.class);
/**
* Empty Constructor
*/
private DB()
{
}
/**
* Executes Select Query. Uses ReadSth to utilize params and return data. Recycles connection after competion.
*
* @param query
* @param reader
* @return boolean Success
*/
public static boolean select(String query, ReadStH reader)
{
return select(query, reader, null);
}
/**
* Executes Select Query. Uses ReadSth to utilize params and return data. Recycles connection after completion.
*
* @param query
* @param reader
* @param errMsg
* @return boolean Success
*/
public static boolean select(String query, ReadStH reader, String errMsg)
{
Connection con = null;
PreparedStatement stmt = null;
ResultSet rset;
try
{
con = DatabaseFactory.getConnection();
stmt = con.prepareStatement(query);
if(reader instanceof ParamReadStH)
((ParamReadStH) reader).setParams(stmt);
rset = stmt.executeQuery();
reader.handleRead(rset);
}
catch(Exception e)
{
if(errMsg == null)
log.warn("Error executing select query " + e, e);
else
log.warn(errMsg + " " + e, e);
return false;
}
finally
{
try
{
if(con != null)
con.close();
if(stmt != null)
stmt.close();
}
catch(Exception e)
{
log.warn("Failed to close DB connection " + e, e);
}
}
return true;
}
/**
* Call stored procedure
*
* @param query
* @param reader
* @return
*/
public static boolean call(String query, ReadStH reader)
{
return call(query, reader, null);
}
/**
* Call stored procedure
*
* @param query
* @param reader
* @param errMsg
* @return
*/
public static boolean call(String query, ReadStH reader, String errMsg)
{
Connection con = null;
CallableStatement stmt = null;
ResultSet rset;
try
{
con = DatabaseFactory.getConnection();
stmt = con.prepareCall(query);
if(reader instanceof CallReadStH)
((CallReadStH) reader).setParams(stmt);
rset = stmt.executeQuery();
reader.handleRead(rset);
}
catch(Exception e)
{
if(errMsg == null)
log.warn("Error calling stored procedure " + e, e);
else
log.warn(errMsg + " " + e, e);
return false;
}
finally
{
try
{
if(con != null)
con.close();
if(stmt != null)
stmt.close();
}
catch(Exception e)
{
log.warn("Failed to close DB connection " + e, e);
}
}
return true;
}
/**
* Executes Insert or Update Query not needing any further modification or batching. Recycles connection after
* completion.
*
* @param query
* @return boolean Success
*/
public static boolean insertUpdate(String query)
{
return insertUpdate(query, null, null);
}
/**
* Executes Insert or Update Query not needing any further modification or batching. Recycles connection after
* completion.
*
* @param query
* @param errMsg
* @return success
*/
public static boolean insertUpdate(String query, String errMsg)
{
return insertUpdate(query, null, errMsg);
}
/**
* Executes Insert / Update Query. Utilizes IUSth for Batching and Query Editing. MUST MANUALLY EXECUTE QUERY /
* BATACH IN IUSth (No need to close Statement after execution)
*
* @param query
* @param batch
* @return boolean Success
*/
public static boolean insertUpdate(String query, IUStH batch)
{
return insertUpdate(query, batch, null);
}
/**
* Executes Insert or Update Query. Utilizes IUSth for Batching and Query Editing. Defines custom error message if
* error occurs. MUST MANUALLY EXECUTE QUERY / BATACH IN IUSth (No need to Statement after execution) Recycles
* connection after completion
*
* @param query
* @param batch
* @param errMsg
* @return boolean Success
*/
public static boolean insertUpdate(String query, IUStH batch, String errMsg)
{
Connection con = null;
PreparedStatement stmt = null;
try
{
con = DatabaseFactory.getConnection();
stmt = con.prepareStatement(query);
if(batch != null)
batch.handleInsertUpdate(stmt);
else
stmt.executeUpdate();
}
catch(Exception e)
{
if(errMsg == null)
log.warn("Failed to execute IU query " + e, e);
else
log.warn(errMsg + " " + e, e);
return false;
}
finally
{
try
{
if(con != null)
con.close();
if(stmt != null)
stmt.close();
}
catch(Exception e)
{
log.warn("Failed to close DB connection " + e, e);
}
}
return true;
}
/**
* Begins new transaction
*
* @return new Transaction object
* @throws java.sql.SQLException
* if was unable to create transaction
*/
public static Transaction beginTransaction() throws SQLException
{
Connection con = DatabaseFactory.getConnection();
return new Transaction(con);
}
/**
* Creates PreparedStatement with given sql string.<br>
* Statemens are created with {@link java.sql.ResultSet#TYPE_FORWARD_ONLY} and
* {@link java.sql.ResultSet#CONCUR_READ_ONLY}
*
* @param sql
* SQL querry
* @return Prepared statement if ok or null if error happend while creating
*/
public static PreparedStatement prepareStatement(String sql)
{
return prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
}
/**
* Creates {@link java.sql.PreparedStatement} with given sql<br>
*
* @param sql
* SQL querry
* @param resultSetType
* a result set type; one of <br>
* <code>ResultSet.TYPE_FORWARD_ONLY</code>,<br>
* <code>ResultSet.TYPE_SCROLL_INSENSITIVE</code>, or <br>
* <code>ResultSet.TYPE_SCROLL_SENSITIVE</code>
* @param resultSetConcurrency
* a concurrency type; one of <br>
* <code>ResultSet.CONCUR_READ_ONLY</code> or <br>
* <code>ResultSet.CONCUR_UPDATABLE</code>
* @return Prepared Statement if ok or null if error happened while creating
*/
public static PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
{
Connection c = null;
PreparedStatement ps = null;
try
{
c = DatabaseFactory.getConnection();
ps = c.prepareStatement(sql, resultSetType, resultSetConcurrency);
}
catch(Exception e)
{
log.error("Can't create PreparedStatement for querry: " + sql, e);
if(c != null)
{
try
{
c.close();
}
catch(SQLException e1)
{
log.error("Can't close connection after exception", e1);
}
}
}
return ps;
}
/**
* Executes PreparedStatement
*
* @param statement
* PreparedStatement to execute
* @return returns result of {@link java.sql.PreparedStatement#executeQuery()} or -1 in case of error
*/
public static int executeUpdate(PreparedStatement statement)
{
try
{
return statement.executeUpdate();
}
catch(Exception e)
{
log.error("Can't execute update for PreparedStatement", e);
}
return -1;
}
/**
* Executes PreparedStatement and closes it and it's connection
*
* @param statement
* PreparedStatement to close
*/
public static void executeUpdateAndClose(PreparedStatement statement)
{
executeUpdate(statement);
close(statement);
}
/**
* Executes Querry and returns ResultSet
*
* @param statement
* preparedStement to execute
* @return ResultSet or null if error
*/
public static ResultSet executeQuerry(PreparedStatement statement)
{
ResultSet rs = null;
try
{
rs = statement.executeQuery();
}
catch(Exception e)
{
log.error("Error while executing querry", e);
}
return rs;
}
/**
* Closes PreparedStatemet, it's connection and last ResultSet
*
* @param statement
* statement to close
*/
public static void close(PreparedStatement statement)
{
try
{
if(statement.isClosed())
{
// noinspection ThrowableInstanceNeverThrown
log.warn("Attempt to close PreparedStatement that is closes already", new Exception());
return;
}
Connection c = statement.getConnection();
statement.close();
c.close();
}
catch(Exception e)
{
log.error("Error while closing PreparedStatement", e);
}
}
}