/**
* 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()
public static int updateStudy(String code, Study study) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"UPDATE study SET "
+ "name = ?, description = ?, question = ? , imageURL = ?, requestedParticipants = ?,"
+ "numOfParticipants = ?, status = ?"
+ "WHERE code = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, study.getName());
ps.setString(2, study.getDescription());
ps.setString(3, study.getQuestion());
ps.setString(4, study.getImageURL());
ps.setInt(5, study.getRequestedParticipants());
ps.setInt(6, study.getNumOfParticipants());
ps.setString(7, study.getStatus());
ps.setString(8, code);
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static List<Departamento> buscarDepartamentos() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Departamento> departamentos = new ArrayList();
cs = connection.prepareCall("{ call listaDepartamento() }");
rs = cs.executeQuery();
while (rs.next()) {
Departamento dpo =
new Departamento(rs.getInt("idDepartamento"), rs.getString("nombreDepartamento"));
departamentos.add(dpo);
}
return departamentos;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static List<Sucursal> buscarSucursales() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Sucursal> sucursales = new ArrayList();
cs = connection.prepareCall("{ call listaSucursal() }");
rs = cs.executeQuery();
while (rs.next()) {
Sucursal suc = new Sucursal(rs.getInt("idSucursal"), rs.getString("nombreSucursal"));
sucursales.add(suc);
}
return sucursales;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static List<Pago> buscarPagos() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Pago> pagos = new ArrayList();
cs = connection.prepareCall("{ call listaPago() }");
rs = cs.executeQuery();
while (rs.next()) {
Pago pag = new Pago(rs.getInt("idMetodoPago"), rs.getString("nombreMetodoPago"));
pagos.add(pag);
}
return pagos;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static List<Usuario> buscarUsuarios() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Usuario> usuas = new ArrayList();
cs = connection.prepareCall("{ call cajeroReporte() }");
rs = cs.executeQuery();
while (rs.next()) {
Usuario usa =
new Usuario(
rs.getString("nombreUsuario"),
rs.getString("apellidoPaterno"),
rs.getInt("idUsuario"));
usuas.add(usa);
}
return usuas;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static int insertarVenta(Venta v) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
Venta ven = null;
try {
cs = connection.prepareCall("{ call insertVenta(?, ?, ?, ?) }");
cs.setInt(1, v.getUsuarioVenta().getId());
cs.setDouble(2, v.getSubtotal());
cs.setInt(3, v.getPagoVenta().getIdPago());
cs.setInt(4, v.getUsuarioVenta().getSucursal().getIdSucursal());
rs = cs.executeQuery();
while (rs.next()) {
ven = new Venta(rs.getInt("idVenta"));
}
return ven.getIdVenta();
} catch (Exception ex) {
ex.printStackTrace();
return 0;
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static int addStudy(Study study) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"INSERT INTO study (name,description,creatorEmail, dateCreated, "
+ "question, imageURL, requestedParticipants, numOfParticipants, status) "
+ "VALUES (?,?,?,?,?,?,?,?,?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, study.getName());
ps.setString(2, study.getDescription());
ps.setString(3, study.getCreatorEmail());
ps.setTimestamp(4, study.getDateCreated());
ps.setString(5, study.getQuestion());
ps.setString(6, study.getImageURL());
ps.setInt(7, study.getRequestedParticipants());
ps.setInt(8, study.getNumOfParticipants());
ps.setString(9, study.getStatus());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public UserInfo getUser(String username) {
UserInfo user = null;
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement("select * from user_info where username=?");
ps.setString(1, username);
rs = ps.executeQuery();
if (rs.next()) {
user = new UserInfo();
user.setFirstName(rs.getString("firstName"));
user.setLastName(rs.getString("lastName"));
user.setAddress(rs.getString("address"));
user.setAffiliation(rs.getString("affiliation"));
user.setMemDur(rs.getString("memDur"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCardType(rs.getString("cardType"));
user.setCardNumber(rs.getString("cardNumber"));
user.setCardNumber(rs.getString("email"));
}
} catch (Exception e) {
System.out.println(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return user;
}
/**
* 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);
}
/**
* 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);
}
// Returns all existing coupons of a certain type
@Override
public Collection<Coupon> getCouponByType(CouponType couponType)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare ArrayList to return
ArrayList<Coupon> allCouponsFound = null;
// Prepare and execute statement
PreparedStatement statement = null;
ResultSet couponsFound = null;
// Prepare sql request
String sqlRequest;
try {
sqlRequest = "SELECT * FROM APP.COUPON WHERE COUPON_TYPE='" + couponType + "'";
statement = connection.prepareStatement(sqlRequest);
// Get all coupons in a ResultSet
couponsFound = statement.executeQuery();
// Prepare Collection
allCouponsFound = new ArrayList<Coupon>();
// Move all coupons from ResultSet to an ArrayList
while (couponsFound.next()) {
// Prepare temp coupon
Coupon tempCoupon = new Coupon();
tempCoupon.setId(couponsFound.getLong("ID"));
tempCoupon.setTitle(couponsFound.getString("TITLE"));
tempCoupon.setStartDate(couponsFound.getDate("START_DATE"));
tempCoupon.setEndDate(couponsFound.getDate("END_DATE"));
tempCoupon.setAmount(couponsFound.getInt("AMOUNT"));
tempCoupon.setType(CouponType.valueOf(couponsFound.getString("COUPON_TYPE")));
tempCoupon.setMessage(couponsFound.getString("MESSAGE"));
tempCoupon.setPrice(couponsFound.getDouble("PRICE"));
// Add coupon to the Collection
allCouponsFound.add(tempCoupon);
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
couponsFound.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
// returns NULL, when no coupons found
return allCouponsFound;
}
/**
* 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()
public synchronized void delete() {
// Drop main table and any other tables with the main table prefix
// First get a list of the tables to drop
ArrayList<String> tablesToDrop = new ArrayList<String>();
Connection con;
try {
con = connectionPool.getConnection();
} catch (ConnectionException e) {
throw new BeanFactoryException(e);
}
try {
Statement stmt = con.createStatement();
if (printSQL != null) printDebug("deleteTable: SHOW TABLES");
ResultSet rs = stmt.executeQuery("SHOW TABLES");
while (rs.next()) {
String s = rs.getString(1);
if (File.separatorChar == '\\') {
// It's windows...case insensitive matching
String lower = s.toLowerCase();
if (lower.equalsIgnoreCase(tableName)) tablesToDrop.add(s);
if (lower.startsWith(tableName.toLowerCase() + "_")) tablesToDrop.add(s);
} else {
// It's Unix...case counts
if (s.equals(tableName)) tablesToDrop.add(s);
if (s.startsWith(tableName + "_")) tablesToDrop.add(s);
}
}
rs.close();
stmt.close();
for (String name : tablesToDrop) {
stmt = con.createStatement();
String sql = "DROP TABLE " + name;
if (printSQL != null) printDebug("deleteTable: " + sql);
stmt.executeUpdate(sql);
stmt.close();
}
connectionPool.releaseConnection(con);
} catch (SQLException e) {
try {
con.close();
} catch (SQLException e2) {
}
throw new BeanFactoryException(e);
}
}
/**
* 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);
}
// Returns collection of all existing coupons
@Override
public Collection<Coupon> getAllCoupons()
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish db connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare and execute SELECT
Statement statement;
ArrayList<Coupon> coupons;
ResultSet rs;
try {
statement = connection.createStatement();
coupons = new ArrayList<Coupon>();
String sql = "SELECT * FROM APP.COUPON ";
rs = statement.executeQuery(sql);
while (rs.next()) {
Coupon coupon = new Coupon();
coupon.setAmount(rs.getInt("AMOUNT"));
coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
coupon.setEndDate(rs.getDate("END_DATE"));
coupon.setId(rs.getLong("ID"));
coupon.setImage(rs.getString("IMAGE"));
coupon.setMessage(rs.getString("MESSAGE"));
coupon.setPrice(rs.getDouble("PRICE"));
coupon.setTitle(rs.getString("TITLE"));
coupon.setStartDate(rs.getDate("START_DATE"));
coupons.add(coupon);
// System.out.println(coupon.toString());
coupons.add(coupon);
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
rs.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
return coupons;
}
/**
* 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);
}
@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);
}
// Returns coupon by Title
@Override
public Coupon getCoupon(String title)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare and execute coupon
Statement statement;
ResultSet rs;
String sql;
Coupon coupon = null;
;
try {
statement = connection.createStatement();
// Prepare SQL message to get the Coupon by the id
sql = "SELECT * FROM APP.COUPON WHERE TITLE='" + title + "'";
// getting the values into a result set
rs = statement.executeQuery(sql);
coupon = new Coupon();
if (rs.next()) {
coupon.setAmount(rs.getInt("AMOUNT"));
coupon.setId(rs.getLong("ID"));
coupon.setImage(rs.getString("IMAGE"));
coupon.setMessage(rs.getString("MESSAGE"));
coupon.setPrice(rs.getDouble("PRICE"));
coupon.setTitle(rs.getString("TITLE"));
coupon.setEndDate(rs.getDate("END_DATE"));
coupon.setStartDate(rs.getDate("START_DATE"));
coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
rs.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
return coupon;
}
/**
* 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);
}
private String[] getPrimaryKeyNamesFromTable() {
Connection con;
try {
con = connectionPool.getConnection();
} catch (ConnectionException e) {
throw new BeanFactoryException(e);
}
try {
Statement stmt = con.createStatement();
String sql = "DESCRIBE " + tableName;
if (printSQL != null) printDebug("getPrimaryKeyNamesFromTable: " + sql);
ResultSet rs = stmt.executeQuery(sql);
ArrayList<String> list = new ArrayList<String>();
while (rs.next()) {
String name = rs.getString(1);
// String sqlType = rs.getString(2);
// boolean nonNull = !rs.getBoolean(3);
boolean primaryKey = rs.getString(4).equalsIgnoreCase("PRI");
if (primaryKey) {
if (!name.contains(Property.META_SEPARATOR)) {
list.add(name);
} else {
String prefix = name.substring(0, name.indexOf(Property.META_SEPARATOR));
if (!list.contains(prefix)) list.add(prefix);
}
}
}
stmt.close();
connectionPool.releaseConnection(con);
// Don't check anymore now that tables with only one row and no primary key are allowed.
// if (list.size() == 0) throw new BeanFactoryException("Could not find any primary
// key in table: "+tableName);
return list.toArray(new String[list.size()]);
} catch (SQLException e) {
try {
con.close();
} catch (SQLException e2) {
}
throw new BeanFactoryException(e);
}
}
// Update existing coupon
@Override
public void updateCoupon(Coupon coupon)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
PreparedStatement preparedStatement;
// Prepare and execute the update
try {
// Prepare SQL message to remove the Coupon
String updateSQL =
"UPDATE APP.COUPON SET "
+ "AMOUNT=?,MESSAGE=?,PRICE=?,TITLE=?,END_DATE=?,START_DATE=?,IMAGE=?,COUPON_TYPE=? "
+ "WHERE ID=?";
// Prepare statement
preparedStatement = connection.prepareStatement(updateSQL);
preparedStatement.setInt(1, coupon.getAmount());
preparedStatement.setString(2, coupon.getMessage());
preparedStatement.setDouble(3, coupon.getPrice());
preparedStatement.setString(4, coupon.getTitle());
preparedStatement.setDate(5, (java.sql.Date) coupon.getEndDate());
preparedStatement.setDate(6, (java.sql.Date) coupon.getStartDate());
preparedStatement.setString(7, coupon.getImage());
preparedStatement.setString(8, coupon.getType().name());
preparedStatement.setLong(9, coupon.getId());
// update the Coupon
preparedStatement.execute();
// Log
System.out.println(coupon.toString() + " was updated");
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close Connections
try {
preparedStatement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
}
// Removes relevant rows from CUSTOMER_COUPON, COMPANY_COUPON as well as coupon itself
@Override
public void removeCoupon(Coupon coupon)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Get coupon ID from DB
Statement statement;
ResultSet idFound;
try {
statement = connection.createStatement();
String sqlRequest = "SELECT ID FROM APP.COUPON WHERE TITLE='" + coupon.getTitle() + "'";
idFound = statement.executeQuery(sqlRequest);
idFound.next();
// coupon.setId(idFound.getLong("ID"));
// Prepare message to remove from purchase history
String removeSQL = "DELETE FROM APP.CUSTOMER_COUPON WHERE COUPON_ID =" + coupon.getId();
// Remove coupon from purchase history
statement.execute(removeSQL);
// Prepare message to remove from company's coupons
removeSQL = "DELETE FROM APP.COMPANY_COUPON WHERE COUPON_ID=" + coupon.getId();
// Remove coupon from company
statement.execute(removeSQL);
// Prepare SQL message to remove the Coupon
removeSQL = "DELETE FROM APP.COUPON WHERE ID=" + coupon.getId();
// Remove the Coupon himself
statement.execute(removeSQL);
System.out.println(coupon.toString() + " was deleted");
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
idFound.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
}
/** 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 static void eliminar(int id) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
try {
cs = connection.prepareCall("{ call UsuarioBorrar(?, ?) }");
cs.setInt(1, id);
cs.registerOutParameter(2, Types.BIT);
cs.execute();
byte res = cs.getByte(2);
System.out.println("[Borrar] Resultado=" + res);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
/**
* 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);
}
public static void insertar(Usuario U) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
try {
cs = connection.prepareCall("{ call UsuarioInsertar(?, ?, ?, ?, ?) }");
cs.setString(1, U.getNombreUsuario());
cs.setString(2, U.getApellidoPaterno());
cs.setString(3, U.getApellidoMterno());
cs.execute();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
private static synchronized Connection getConnect() throws SQLException {
if (connectionPool == null) {
connectionPool = new ConnectionPool(driverClassName, url, username, password);
connectionPool.setTestTable("dual");
if (initConn > 0 && incConn > 0 && maxConn > 0 && maxConn > initConn) {
connectionPool.setInitialConnections(initConn);
connectionPool.setIncrementalConnections(incConn);
;
connectionPool.setMaxConnections(maxConn);
;
}
try {
connectionPool.createPool();
} catch (Exception e) {
logger.fatal("can not create connection pool!system will exit..", e);
System.exit(1);
}
}
return connectionPool.getConnection();
}
public static ArrayList<Study> getOpenAndNotParticipated(String emailAddress) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query =
"SELECT * FROM study WHERE NOT EXISTS(SELECT * FROM answer WHERE study.code = answer.code AND email = ?)"
+ "AND status = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, emailAddress);
ps.setString(2, "open");
rs = ps.executeQuery();
Study study = null;
ArrayList<Study> studies = new ArrayList<Study>();
while (rs.next()) {
study = new Study();
study.setName(rs.getString("name"));
study.setCode(rs.getString("code"));
study.setDescription(rs.getString("description"));
study.setCreatorEmail(rs.getString("creatorEmail"));
study.setDateCreated(rs.getTimestamp("dateCreated"));
study.setQuestion(rs.getString("question"));
study.setRequestedParticipants(rs.getInt("requestedParticipants"));
study.setNumOfParticipants(rs.getInt("numOfParticipants"));
study.setStatus(rs.getString("status"));
study.setImageURL(rs.getString("imageURL"));
studies.add(study);
}
return studies;
} catch (SQLException e) {
System.out.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
// =============================================================
public static boolean UserExists(String username) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT username FROM user_info " + "WHERE username = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, username);
rs = ps.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
