// 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; }
// 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; }
// 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; }
// 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); }
private static synchronized void returnConnect(Connection connection) { 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); } } connectionPool.returnConnection(connection); ; }
// Adds coupon to a coupons list @Override public void createCoupon(Coupon coupon) throws FailedToCreateCouponException, WaitingForConnectionInterrupted { // Get connection Connection connection; try { connection = pool.getConnection(); } catch (GetConnectionWaitInteruptedException e1) { throw new WaitingForConnectionInterrupted(); } // Prepare SQL message to insert new company String insertSQL = "INSERT INTO APP.COUPON (IMAGE,PRICE,MESSAGE,COUPON_TYPE,AMOUNT,END_DATE,START_DATE,TITLE) VALUES" + "(?,?,?,?,?,?,?,?)"; PreparedStatement preparedStatement; try { preparedStatement = connection.prepareStatement(insertSQL); preparedStatement.setInt(5, coupon.getAmount()); preparedStatement.setString(3, coupon.getMessage()); preparedStatement.setDouble(2, coupon.getPrice()); preparedStatement.setString(8, coupon.getTitle()); preparedStatement.setDate(6, (java.sql.Date) coupon.getEndDate()); preparedStatement.setDate(7, (java.sql.Date) coupon.getStartDate()); preparedStatement.setString(1, coupon.getImage()); preparedStatement.setString(4, coupon.getType().name()); // Execute prepared Statement preparedStatement.executeUpdate(); // Close statement connection preparedStatement.close(); } catch (SQLException e) { throw new FailedToCreateCouponException(); } finally { // close connection pool.returnConnection(connection); } System.out.println(coupon.toString() + " was added to the table"); }
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); }
public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { Connection con = null; // res.setContentType("text/html"); // res.setHeader("Cache-Control","no-store"); // PrintWriter out=res.getWriter(); HttpSession session = req.getSession(false); String ad_user; ad_user = (String) session.getValue("aduser"); java.util.Date time_comp = new java.util.Date(System.currentTimeMillis() - 20 * 60 * 1000); java.util.Date accessed = new java.util.Date(session.getLastAccessedTime()); if (session == null || ad_user == null || accessed.before(time_comp)) { session.invalidate(); // out.println("<H2>Your Session has expired </H2>"); // out.println("<a href='admin.htm'>Click Here</a> To Re-Login"); return; } String temp_list_id = req.getParameter("list_id"); int list_id; try { list_id = Integer.parseInt(temp_list_id); } catch (Exception e) { // out.println("<H2>NO List Found</H2>"); // out.println("<a href='javascript:history.go(-1)'>Click Here</a> to go back to previous // page & try again"); return; } try { con = pool.getConnection(); Statement stmt = con.createStatement(); File file = new File("temp.csv"); FileWriter fout = new FileWriter(file); BufferedWriter bw = new BufferedWriter(fout); // String line=""; bw.write("Name,Email\r\n"); ResultSet rs = stmt.executeQuery("Select * from list_member where list_id=" + list_id); while (rs.next()) { String name = rs.getString("member_name"); String email = rs.getString("member_email"); bw.write(name + "," + email + "\r\n"); } bw.close(); fout.close(); rs.close(); // String fname=file.getName(); // String contentType = getServletContext().getMimeType(fname); // System.out.println(contentType); res.setContentType("application/csv"); ServletOutputStream out = res.getOutputStream(); FileInputStream fis = new FileInputStream("temp.csv"); byte[] buf = new byte[4 * 1024]; // 4K buffer int bytesRead; while ((bytesRead = fis.read(buf)) != -1) { out.write(buf, 0, bytesRead); } out.close(); } catch (Exception e) { try { // out.println("<H2>An Error has occured: "+e.getMessage()+"</H2>"); e.printStackTrace(); // out.println("<br><br><a href='javascript:history.go(-1)'>Click Here</a> to go back to // previous page & Try Again"); con.rollback(); } catch (Exception ignored) { } } finally { if (con != null) pool.returnConnection(con); // out.close(); } }