public void purgeConnections() {
synchronized (connectionPools) {
for (ConnectionPool cp : connectionPools.values()) {
cp.purgeConnections();
}
}
}
public static int update(User user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"UPDATE customer SET "
+ "first_name = ?, "
+ "last_name = ?, "
+ "phone_number = ?, "
+ "address = ?, "
+ "city = ?, "
+ "state = ?, "
+ "zip_code = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
ps.setString(5, user.getCity());
ps.setString(6, user.getState());
ps.setString(7, user.getZipCode());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static int insert(User user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"INSERT INTO customer (first_name, last_name, phone_number, address, city, state, zip_code) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
ps.setString(5, user.getCity());
ps.setString(6, user.getState());
ps.setString(7, user.getZipCode());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
/**
* Creates a connection pool (2 connections) and execututes queries on each. It then tries to get
* a third connection.
*/
public static void testConnectionPooling() {
ConnectionPool conPool = new ConnectionPool(2, driverName, connURL, username, password);
conPool.resizeConnectionPool(3);
String out = "";
try {
SQLExecutor sqlExec1 = new SQLExecutor(conPool);
SQLResults res1 = sqlExec1.runQuery("select dd * from JDBC_TEST where CODE < 'E'");
res1.setToStringFormatWidth(11);
out += res1.toString() + "\n\n";
SQLExecutor sqlExec2 = new SQLExecutor(conPool);
SQLResults res2 = sqlExec2.runQuery("select * from JDBC_TEST where CODE > 'E'");
out += res2.toString() + "\n\n";
// try to get a third connection via getConnection(). there are no available
// connections so the ConnectionPool will create a new connection, add it the
// pool, and return the new connection
SQLExecutor sqlExec3 = new SQLExecutor(conPool);
SQLResults res3 = sqlExec2.runQuery("select * from JDBC_TEST where CODE > 'E'");
out += res3.toString();
} finally {
conPool.closeAllConnections();
}
System.out.println(out);
}
/**
* Consulta as fatos do prestador passado em um determinado periodo
*
* @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as fatos
* @param strNumPeriodo - o periodo de referencia do qual se deseja obter os fatos
* @return um array de fatos do prestador fornecido como paramentro
*/
public static final ResumoFato[] buscaResumoFato(String strCdContrato, String strNumPeriodo)
throws Exception {
Connection con = ConnectionPool.getConnection();
ResumoFato[] fatos = null;
PreparedStatement pst;
ResultSet rset;
int qtdeFatos = 0;
try {
pst = con.prepareStatement(CONSULTA_RESUMO_FATO);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
pst.setString(3, strCdContrato);
pst.setString(4, strNumPeriodo);
pst.setString(5, strCdContrato);
pst.setString(6, strNumPeriodo);
pst.setString(7, strCdContrato);
pst.setString(8, strNumPeriodo);
pst.setString(9, strCdContrato);
pst.setString(10, strNumPeriodo);
rset = pst.executeQuery();
if (!rset.next()) {
return null;
} // if ( ! rset.next() )
do {
qtdeFatos += 1;
} while (rset.next());
System.out.println("qtdeFatos -> " + qtdeFatos);
fatos = new ResumoFato[qtdeFatos];
rset = pst.executeQuery();
qtdeFatos = 0;
while (rset.next()) {
fatos[qtdeFatos] = montaResumoFato(rset);
qtdeFatos++;
} // while
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new Exception(
"Não foi possivel estabelecer conexão com a base de "
+ "dados.Erro:\n"
+ sqle.getMessage());
} finally { // catch()
if (con != null) {
con.close();
System.out.println("Fechou a conexao");
} // if
}
return fatos;
} // buscaResumoFato()
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// get a connection
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
String sqlStatement = request.getParameter("sqlStatement");
String sqlResult = "";
try {
// create a statement
Statement statement = connection.createStatement();
// parse the SQL string
sqlStatement = sqlStatement.trim();
if (sqlStatement.length() >= 6) {
String sqlType = sqlStatement.substring(0, 6);
if (sqlType.equalsIgnoreCase("select")) {
// create the HTML for the result set
ResultSet resultSet = statement.executeQuery(sqlStatement);
sqlResult = SQLUtil.getHtmlTable(resultSet);
resultSet.close();
} else {
int i = statement.executeUpdate(sqlStatement);
if (i == 0) {
sqlResult = "<p>The statement executed successfully.</p>";
} else { // an INSERT, UPDATE, or DELETE statement
sqlResult = "<p>The statement executed successfully.<br>" + i + " row(s) affected.</p>";
}
}
}
statement.close();
connection.close();
} catch (SQLException e) {
sqlResult = "<p>Error executing the SQL statement: <br>" + e.getMessage() + "</p>";
} finally {
pool.freeConnection(connection);
}
HttpSession session = request.getSession();
session.setAttribute("sqlResult", sqlResult);
session.setAttribute("sqlStatement", sqlStatement);
String url = "/index.jsp";
getServletContext().getRequestDispatcher(url).forward(request, response);
}
/** checks database for this user */
private boolean realAuthentication(HttpServletRequest request, ConnectionPool conPool)
throws SQLException {
// user authentication is required!
User user = (User) request.getSession().getAttribute(StringInterface.USERATTR);
if (user == null) {
return false;
}
Connection con = conPool.getConnection();
boolean authenticated = false;
try {
authenticated = userUtils.confirmUserWithEncrypted(user.getID(), user.getEncrypted(), con);
} catch (Exception e) {
throw new SQLException(e.getMessage());
} finally {
conPool.free(con);
con = null;
}
return authenticated;
} // end realAuthentication
/**
* Public method that deletes rows which match a whereClause from a given table
*
* @param table Table from where the rows will be deleted
* @param whereClause Where clause to be matched
* @return <b>True</b> if delete successful.
* @throws SQLException
*/
public static boolean delete(String table, String whereClause) throws SQLException {
init();
Connection con = pool.getConnection();
String query = "DELETE FROM " + table;
if (!whereClause.isEmpty()) {
query += " WHERE " + whereClause;
}
Statement st = con.createStatement();
return (st.executeUpdate(query) != 0);
}
/**
* Consulta as glosas do prestador passado em um determinado periodo
*
* @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas
* @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas
* @return um array de glosas do prestador fornecido como paramentro
*/
public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo)
throws Exception {
Connection con = ConnectionPool.getConnection();
GlosaPrestador[] glosas = null;
PreparedStatement pst;
ResultSet rset;
int qtdeGlosas = 0;
try {
pst = con.prepareStatement(CONSULTA_GLOSA);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
rset = pst.executeQuery();
if (!rset.next()) {
return null;
} // if ( ! rset.next() )
do {
qtdeGlosas += 1;
} while (rset.next());
System.out.println("qtdeGlosas -> " + qtdeGlosas);
glosas = new GlosaPrestador[qtdeGlosas];
pst = con.prepareStatement(CONSULTA_GLOSA);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
rset = pst.executeQuery();
qtdeGlosas = 0;
while (rset.next()) {
glosas[qtdeGlosas] = montaGlosaPrestador(rset);
qtdeGlosas++;
} // while
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new Exception(
"Não foi possivel estabelecer conexão com a base de "
+ "dados.Erro:\n"
+ sqle.getMessage());
} finally { // catch()
if (con != null) {
con.close();
System.out.println("Fechou a conexao");
} // if
}
return glosas;
} // consultaGlosaPrest()
/**
* Method that does a INSERT INTO <tt>table</tt> VALUES (<tt>values</tt>)
*
* @param values Array of strings containing the values to be inserted.
* @param table Table where the values should be inserted into.
* @return <b>True</b> if successful.
* @throws SQLException
*/
public static boolean insert(String[] values, String table) throws SQLException {
init();
Connection con = pool.getConnection();
String query = "INSERT INTO " + table + " VALUES ('" + values[0] + "'";
for (int i = 1; i < values.length; i++) {
query += ", '" + values[i] + "'";
}
query += ")";
Statement st = con.createStatement();
return (st.executeUpdate(query) != 0);
}
/**
* static method testing jdbc framework with a select for update statement (that locks a record)
* and an update statement that generates a deadlock.
*/
public static void testDeadlockException() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec1 = new SQLExecutor(conPool);
try {
// lock record with a select for update statement
sqlExec1.setAutoCommit(false);
sqlExec1.addParam(new Integer(2));
sqlExec1.runQuery("SELECT CODE FROM JDBC_TEST WHERE TEST_ID = ? FOR UPDATE");
System.out.println("Attempting to update a record locked by another connection...");
SQLExecutor sqlExec2 = new SQLExecutor(getConnectionPool());
sqlExec2.setTimeoutInSec(5); // timeout if deadlocked
sqlExec2.addParam(new Integer(2));
sqlExec2.runQueryCloseCon("UPDATE JDBC_TEST SET CODE = 'X' WHERE TEST_ID = ?");
} catch (DatabaseException e) {
System.out.println("Error code=" + e.getSQLErrorCode() + ", " + e.getMessage());
if (e.isRowlockOrTimedOut()) System.out.println("Rowlock exception!");
} finally {
conPool.closeAllConnections();
}
}
/**
* Method that executes an update on a group of fields.
*
* @param fields String array which should contain the field and values to be updated as a key
* pair.
* @param table The table to be updated.
* @param whereClause Should the update use a where clause, it should be in the format <b>ID = 1
* AND SecondID = 2</b> and so on, regular SQL operations apply.
* @return <b>True</b> if the update is successful.
* @throws SQLException
*/
public static boolean update(String[][] fields, String table, String whereClause)
throws SQLException {
init();
Connection con = pool.getConnection();
String query = "UPDATE " + table + " SET " + fields[0][0] + "='" + fields[0][1] + "'";
for (int i = 1; i < fields.length; i++) {
query += ", " + fields[i][0] + "='" + fields[i][1] + "'";
}
if (!whereClause.isEmpty()) {
query += " WHERE " + whereClause;
}
Statement st = con.createStatement();
return (st.executeUpdate(query) != 0);
}
private void withConnection(URI uri, Properties props, ConnectionCallback cb)
throws SQLException {
ConnectionPool cp;
synchronized (connectionPools) {
ConnectionKey key = new ConnectionKey(uri, props);
cp = connectionPools.get(key);
if (cp == null) {
cp = new ConnectionPool(uri, props);
connectionPools.put(key, cp);
}
}
// If this thread is already inside withConnection(), re-use the
// same PooledConnection as the outermost withConnection() call
// returned.
PerThreadConnection ptc = perThreadConnection.get();
if (ptc.refCounter == 0) {
++ptc.refCounter;
ptc.pooledConnection = cp.getConnection();
}
try {
cb.execute(ptc.pooledConnection);
} finally {
--ptc.refCounter;
if (ptc.refCounter == 0) {
cp.releaseConnection(ptc.pooledConnection);
ptc.pooledConnection = null;
}
}
}
/**
* Method that selects and specific <tt>field</tt> on a <tt>table</tt> according to if present a
* <tt>whereClause</tt>.
*
* @param fields <tt>String</tt> array which should contain the fields to be SELECTED.
* @param table The table to be selected.
* @param whereClause Should the SELECT use a where clause, it should be in the format <b>ID = 1
* AND SecondID = 2</b> and so on, regular SQL operations apply.
* @return A <tt>ResultSet</tt> containing the result of the SELECT
* @throws SQLException
*/
public static ResultSet select(String[] fields, String table, String whereClause)
throws SQLException {
init();
Connection con = pool.getConnection();
String query = "SELECT " + fields[0];
for (int i = 1; i < fields.length; i++) {
query += ", " + fields[i];
}
query += " FROM " + table;
if (!whereClause.isEmpty()) {
query += " WHERE " + whereClause;
}
Statement st = con.createStatement();
System.out.println(query);
return st.executeQuery(query);
}
/**
* Method that does a INSERT INTO <tt>table</tt> VALUES (<tt>values</tt>)
*
* @param fields Array of strings containing the key pairs {{COLUMN, VALUE},{COLUMN, VALUE}} to be
* inserted.
* @param table Table where the values should be inserted into.
* @return <b>True</b> if successful.
* @throws SQLException
*/
public static boolean insert(String[][] fields, String table) throws SQLException {
init();
Connection con = pool.getConnection();
String query = "INSERT INTO " + table + "";
String columns = " (" + fields[0][0] + "";
String values = " ('" + fields[0][1] + "'";
for (int i = 1; i < fields.length; i++) {
columns += ", " + fields[i][0];
values += ", '" + fields[i][1] + "'";
}
columns += ")";
values += ")";
query += columns + " VALUES" + values;
Statement st = con.createStatement();
System.out.println(query);
return (st.executeUpdate(query) != 0);
}
/**
* Method that does a INSERT INTO <tt>table</tt> VALUES (<tt>values</tt>) and returns the new
* generated key for the values inserted.
*
* @param fields Array of strings containing the key pairs {{COLUMN, VALUE},{COLUMN, VALUE}} to be
* inserted.
* @param table Table where the values should be inserted into.
* @return a ResultSet containing the generated keys.
* @throws SQLException
*/
public static int insertAndGetKey(String[][] fields, String table) throws SQLException {
init();
Connection con = pool.getConnection();
String query = "INSERT INTO `" + table + "`";
String columns = "(`" + fields[0][0] + "`";
String values = " (" + fields[0][1] + "";
for (int i = 1; i < fields.length; i++) {
columns += ", `" + fields[i][0] + "`";
values += "," + fields[i][1] + "";
}
columns += ")";
values += ")";
query += columns + " VALUES" + values;
PreparedStatement st = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();
ResultSet rs = st.getGeneratedKeys();
rs.next();
return rs.getInt(1);
}
/**
* Method that initiates a Transaction and returns the open connection.
*
* @return Connection
* @throws SQLException
*/
public static Connection initTransaction() throws SQLException {
init();
Connection con = pool.getConnection();
con.setAutoCommit(false);
return con;
}
public void doGet(HttpServletRequest request, HttpServletResponse response) {
response.setContentType("text/html");
PrintWriter webPageOutput = null;
try {
webPageOutput = response.getWriter();
} catch (IOException error) {
Routines.writeToLog(servletName, "getWriter error : " + error, false, context);
}
HttpSession session = request.getSession();
session.setAttribute("redirect", request.getRequestURL() + "?" + request.getQueryString());
Connection database = null;
try {
database = pool.getConnection(servletName);
} catch (SQLException error) {
Routines.writeToLog(servletName, "Unable to connect to database : " + error, false, context);
}
if (Routines.loginCheck(true, request, response, database, context)) {
return;
}
String server = context.getInitParameter("server");
boolean liveSever = false;
if (server == null) {
server = "";
}
if (server.equals("live")) {
response.setHeader("Refresh", "60");
}
Routines.WriteHTMLHead(
"View System Log", // title
false, // showMenu
13, // menuHighLight
false, // seasonsMenu
false, // weeksMenu
false, // scores
false, // standings
false, // gameCenter
false, // schedules
false, // previews
false, // teamCenter
false, // draft
database, // database
request, // request
response, // response
webPageOutput, // webPageOutput
context); // context
webPageOutput.println("<CENTER>");
webPageOutput.println(
"<IMG SRC=\"../Images/Admin.gif\"" + " WIDTH='125' HEIGHT='115' ALT='Admin'>");
webPageOutput.println("</CENTER>");
pool.returnConnection(database);
webPageOutput.println(Routines.spaceLines(1));
Routines.tableStart(false, webPageOutput);
Routines.tableHeader("System Log", 0, webPageOutput);
Routines.tableDataStart(true, false, false, true, true, 0, 0, "scoresrow", webPageOutput);
boolean firstLine = true;
int numOfLines = 0;
try {
String file = context.getRealPath("/");
FileReader logFile = new FileReader(file + "/Data/log.txt");
BufferedReader logFileBuffer = new BufferedReader(logFile);
boolean endOfFile = false;
while (!endOfFile) {
String logFileText = logFileBuffer.readLine();
if (logFileText == null) {
endOfFile = true;
} else {
if (firstLine) {
firstLine = false;
} else {
webPageOutput.println(Routines.spaceLines(1));
}
numOfLines++;
webPageOutput.println(logFileText);
}
}
logFileBuffer.close();
} catch (IOException error) {
Routines.writeToLog(servletName, "Problem with log file : " + error, false, context);
}
Routines.tableDataEnd(false, true, true, webPageOutput);
Routines.tableEnd(webPageOutput);
if (numOfLines < 20) {
webPageOutput.println(Routines.spaceLines(20 - numOfLines));
}
Routines.WriteHTMLTail(request, response, webPageOutput);
}
private String processRequest(
HttpServletRequest request, HttpServletResponse response, ConnectionPool conPool)
throws Exception {
// getting id parameters
ParameterParser parameter = new ParameterParser(request);
String bookID = parameter.getString(bookInterface.FIELD_ID, null);
String sellerID = parameter.getString(bookInterface.FIELD_SELLERID, null);
String message = parameter.getString(FIELD_MESSAGE, null);
int codeID = parameter.getInt(bookInterface.FIELD_HIDDENID, 0);
// get buyer's user object
User user = (User) request.getSession().getAttribute(StringInterface.USERATTR);
// security feauture:
// if one of ids is missing or incorrect return false
if (bookID == null
|| sellerID == null
|| codeID == 0
|| bookID.length() != booksTable.ID_LENGTH
|| sellerID.length() != usersTable.ID_LENGTH
|| codeID != Math.abs(bookID.hashCode())) {
return "We were unable to find the book you specified! Please make sure that the book id is correct.";
}
if (user.getID().equals(sellerID)) {
return "You may not purchase an item from yourself!";
}
// get connection
Connection con = conPool.getConnection();
try {
booksTable book = generalUtils.getBook(bookID, con);
/*security feauture:
*check seller id == passed hidden id
*book != null
*/
if (book == null || !book.getSellerID().equals(sellerID)) {
return "We were unable to find the book you specified! Please make sure that the book id is correct.";
}
usersTable sellerInfo = userUtils.getUserInfo(sellerID, con);
usersTable buyerInfo = userUtils.getUserInfo(user.getID(), con);
collegeTable college = getCollege(book.getCollegeID() + "", con);
// if still here continue
if (message == null) {
request.setAttribute(ATTR_BOOK, book);
request.setAttribute(ATTR_SELLER, sellerInfo);
request.setAttribute(ATTR_BUYER, buyerInfo);
request.setAttribute(ATTR_COLLEGE, college.getFull());
RequestDispatcher rd = getServletContext().getRequestDispatcher(PATH_BUY_CONFIRM);
rd.include(request, response);
return null;
} else if (buy(book, user, con)) {
// sending email to buyer
request.setAttribute(mailInterface.USERATTR, buyerInfo.getUsername());
request.setAttribute(mailInterface.EMAILATTR, buyerInfo.getEmail());
request.setAttribute(mailInterface.BOOKATTR, book);
request.setAttribute("book_id", bookID);
request.setAttribute("seller_id", sellerID);
RequestDispatcher rd = getServletContext().getRequestDispatcher(PATHBIDCONFIRMATION);
rd.include(request, response);
// sending email to seller
request.setAttribute(ATTR_COLLEGE, college.getFull());
request.setAttribute(mailInterface.USERATTR, sellerInfo.getUsername());
request.setAttribute(mailInterface.EMAILATTR, sellerInfo.getEmail());
request.setAttribute(mailInterface.MESSAGEATTR, message);
request.setAttribute(mailInterface.BOOKATTR, book);
request.setAttribute(mailInterface.MOREATTR, buyerInfo);
request.setAttribute("book_id", bookID);
request.setAttribute("buyer_id", user.getID());
rd = getServletContext().getRequestDispatcher(PATHBOOKUPDATE);
rd.include(request, response);
// showing success message
rd = getServletContext().getRequestDispatcher(PATH_BUY_SUCCESS);
rd.include(request, response);
return null;
} else {
throw new Exception("failed to process with buy");
}
} catch (Exception e) {
throw e;
} finally {
// recycle
conPool.free(con);
con = null;
}
}