/** * Handles the HTTP <code>POST</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { RequestDispatcher disp = request.getRequestDispatcher("index.jsp?page=games"); Connection con = null; // HTML5 datetime format is jjjj-mm-ddThh:mmZ User user = (User) request.getSession().getAttribute("user"); DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm'Z'"); if (user != null && (user.getAccessLevel().equals("admin"))) { try { if (!request.getParameter("awayteam").equals(request.getParameter("hometeam"))) { con = Connection.getConnection(); con.startConnection(); PreparedStatement pstmt = con.prepareStatement( "insert into game (hometeam, awayteam, date) " + " values(?, ?, ?)"); pstmt.setInt(1, Integer.valueOf(request.getParameter("hometeam"))); pstmt.setInt(2, Integer.valueOf(request.getParameter("awayteam"))); // Lots of conversions to get the time into the database Date date = df.parse((String) request.getParameter("datetime")); Calendar d = Calendar.getInstance(); d.setTime(date); pstmt.setDate(3, new java.sql.Date(d.getTimeInMillis())); pstmt.execute(); request.setAttribute("success", "The game has been added."); } else { request.setAttribute("error", "A team can't play against itself"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); request.setAttribute("error", "The game could not be added."); } catch (ParseException e) { e.printStackTrace(); request.setAttribute("error", "The date is in the wrong format."); } finally { try { // Connection might not be initialized, if the user has entered the same team for home and // away team if (con != null) con.closeConnection(); } catch (SQLException e) { // If it can't be closed just continue. } } } else { request.setAttribute("error", "You don't have sufficient rights to perform this operation."); } disp.forward(request, response); }
public User(Integer userID) throws Exception { this.Connect(); Connection c = this.getConn(); ResultSet resultset; PreparedStatement preparedset = null; String sqlOption = "SELECT * FROM users WHERE id=? LIMIT 1"; preparedset = c.prepareStatement(sqlOption); preparedset.setInt(1, userID); resultset = preparedset.executeQuery(); if (resultset.next()) { this.setLogin(resultset.getString("login")); this.setRole(resultset.getInt("role_id")); this.setUserID(resultset.getInt("id")); } }
/** * Handles the HTTP <code>POST</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { RequestDispatcher disp = request.getRequestDispatcher("GameDetails?gameid=" + request.getParameter("gameid")); Connection con = null; HttpSession session = request.getSession(); User user = (User) request.getSession().getAttribute("user"); if (user != null && (user.getAccessLevel().equals("admin") || (user.getAccessLevel().equals("manager") && user.getTeamID() == Integer.valueOf((String) session.getAttribute("hometeamid"))))) { try { con = Connection.getConnection(); con.startConnection(); PreparedStatement pstmt = con.prepareStatement("delete from score " + "where scoreid = ?"); pstmt.setInt(1, Integer.valueOf(request.getParameter("goalid"))); pstmt.execute(); request.setAttribute("success", "The goal has been deleted."); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); request.setAttribute("error", "Error deleting the goal."); } finally { try { con.closeConnection(); } catch (SQLException e) { // If it can't be closed just continue. } } } else { request.setAttribute("error", "You don't have sufficient rights to perform this operation."); } disp.forward(request, response); }
public Standings() { Connection con = null; try { con = Connection.getConnection(); con.startConnection(); HashMap<Integer, Team> teamshelper = new HashMap<Integer, Team>(); // Make sure all teams are in the list, even if no games are played PreparedStatement pstmt = con.prepareStatement("select teamid, teamname from team"); pstmt.execute(); ResultSet rs = pstmt.getResultSet(); while (rs.next()) { Team team = new Team(); team.setTeamID(rs.getInt("teamid")); team.setTeamName(rs.getString("teamname")); teamshelper.put(rs.getInt("teamid"), team); } // Separate queries for home team wins, away team wins, home team draw and awayteamdraws // Home team wins pstmt = con.prepareStatement( "select hometeam as teamid, teamname, count(*) as score " + "from (select h.teamid as hometeam, h.teamname as teamname, a.teamid as awayteam, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore " + "from game g1 join game g2 on g1.gameid = g2.gameid " + "join team h on g1.hometeam = h.teamid " + "join team a on g2.awayteam = a.teamid " + "full join score s1 on s1.gameid = g1.gameid " + "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) " + "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) " + "where g1.date < now() " + "group by h.teamid, h.teamname, a.teamid, g1.gameid, g1.date " + "order by g1.date desc) game " + "where htscore > atscore " + "group by hometeam, awayteam, teamname"); System.out.println(pstmt.toString()); pstmt.execute(); rs = pstmt.getResultSet(); while (rs.next()) { Team team = null; if (teamshelper.containsKey(rs.getInt("teamid"))) { team = teamshelper.get(rs.getInt("teamid")); team.addPoints(rs.getInt("score") * 2); } else { // May only happen, if game is changed while the code is executing. Very unlikely. team = new Team(); team.setTeamID(rs.getInt("teamid")); team.setTeamName(rs.getString("teamname")); team.addPoints(rs.getInt("score") * 2); // A win counts 2 points } teamshelper.put(rs.getInt("teamid"), team); } // Away team wins pstmt = con.prepareStatement( "select awayteam as teamid, teamname, count(*) as score " + "from (select h.teamid as hometeam, a.teamid as awayteam, a.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore " + "from game g1 join game g2 on g1.gameid = g2.gameid " + "join team h on g1.hometeam = h.teamid " + "join team a on g2.awayteam = a.teamid " + "full join score s1 on s1.gameid = g1.gameid " + "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) " + "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) " + "where g1.date < now() " + "group by h.teamid, a.teamname, a.teamid, g1.gameid, g1.date " + "order by g1.date desc) game " + "where atscore > htscore " + "group by hometeam, awayteam, teamname"); pstmt.execute(); rs = pstmt.getResultSet(); while (rs.next()) { Team team = null; if (teamshelper.containsKey(rs.getInt("teamid"))) { team = teamshelper.get(rs.getInt("teamid")); team.addPoints(rs.getInt("score") * 2); } else { // May only happen, if game is changed while the code is executing. Very unlikely. team = new Team(); team.setTeamID(rs.getInt("teamid")); team.setTeamName(rs.getString("teamname")); team.addPoints(rs.getInt("score") * 2); // A win counts 2 points } teamshelper.put(rs.getInt("teamid"), team); } // Home team draws pstmt = con.prepareStatement( "select hometeam as teamid, teamname, count(*) as score " + "from (select h.teamid as hometeam, a.teamid as awayteam, h.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore " + "from game g1 join game g2 on g1.gameid = g2.gameid " + "join team h on g1.hometeam = h.teamid " + "join team a on g2.awayteam = a.teamid " + "full join score s1 on s1.gameid = g1.gameid " + "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) " + "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) " + "where g1.date < now() " + "group by h.teamid, h.teamname, a.teamid, g1.gameid, g1.date " + "order by g1.date desc) game " + "where htscore = atscore " + "group by hometeam, awayteam, teamname"); pstmt.execute(); rs = pstmt.getResultSet(); while (rs.next()) { Team team = null; if (teamshelper.containsKey(rs.getInt("teamid"))) { team = teamshelper.get(rs.getInt("teamid")); team.addPoints(rs.getInt("score")); } else { // May only happen, if game is changed while the code is executing. Very unlikely. team = new Team(); team.setTeamID(rs.getInt("teamid")); team.setTeamName(rs.getString("teamname")); team.addPoints(rs.getInt("score")); // A draw counts 1 point } teamshelper.put(rs.getInt("teamid"), team); } // Away team draws pstmt = con.prepareStatement( "select awayteam as teamid, teamname, count(*) as score " + "from (select h.teamid as hometeam, a.teamid as awayteam, a.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore " + "from game g1 join game g2 on g1.gameid = g2.gameid " + "join team h on g1.hometeam = h.teamid " + "join team a on g2.awayteam = a.teamid " + "full join score s1 on s1.gameid = g1.gameid " + "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) " + "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) " + "where g1.date < now() " + "group by h.teamid, a.teamname, a.teamid, g1.gameid, g1.date " + "order by g1.date desc) game " + "where htscore = atscore " + "group by hometeam, awayteam, teamname"); pstmt.execute(); rs = pstmt.getResultSet(); while (rs.next()) { Team team = null; if (teamshelper.containsKey(rs.getInt("teamid"))) { team = teamshelper.get(rs.getInt("teamid")); team.addPoints(rs.getInt("score")); } else { // May only happen, if game is changed while the code is executing. Very unlikely. team = new Team(); team.setTeamID(rs.getInt("teamid")); team.setTeamName(rs.getString("teamname")); team.addPoints(rs.getInt("score")); // A draw counts 1 point } teamshelper.put(rs.getInt("teamid"), team); } Collection<Team> c = teamshelper.values(); Iterator<Team> i = c.iterator(); teams = new ArrayList<Team>(); while (i.hasNext()) teams.add(i.next()); Collections.sort(teams); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { con.closeConnection(); } catch (SQLException e) { // If it can't be closed just continue. } catch (NullPointerException e) { // Connection was never initialized, just do nothing. e.printStackTrace(); } } }
void profedit(String cid) throws Exception { try { Connection conn = DBConnection.ConnectDB(); while (true) { // System.out.println(cid); // list all exercises in this course PreparedStatement stmt0 = conn.prepareStatement("SELECT eid, hwname FROM exercise WHERE cid = ?"); stmt0.setString(1, cid); ResultSet rs0 = stmt0.executeQuery(); int i = 1; String[] profselect = new String[20]; int[] alleid = new int[20]; while (rs0.next()) { String hwname = rs0.getString("hwname"); alleid[i] = rs0.getInt("eid"); profselect[i] = hwname; // professor's selection is stored System.out.printf(" %d. %s\n", i, hwname); i++; } System.out.println(" 0. Back"); Scanner scan = new Scanner(System.in); int Choice = -1; // get user choice of which Exercise System.out.println("Please enter the corresponding number:"); while (true) { Choice = scan.nextInt(); if (checkint(Choice, 0, i - 1)) break; } if (Choice == 0) { System.out.print('\u000C'); break; // go back } else { // display chosen Exercise System.out.print('\u000C'); String chosename = profselect[Choice]; System.out.println("You chose to update " + chosename); int eid = alleid[Choice]; PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM Exercise WHERE eid=?"); stmt1.setInt(1, eid); ResultSet rs1 = stmt1.executeQuery(); rs1.next(); String startdate = rs1.getString("startdate"); String enddate = rs1.getString("enddate"); int newallowattempt = rs1.getInt("allowattempt"); int newscorescheme = rs1.getInt("scorescheme"); int newnumofquestion = rs1.getInt("numofquestion"); int newcorrectpoint = rs1.getInt("correctpoint"); int newincorrectpoint = rs1.getInt("incorrectpoint"); int newbasedon = rs1.getInt("basedon"); while (true) { // display options the professor can modify System.out.println( "Choose what to update:\n 1. Start date\n 2. End date\n 3. Number of attempts\n " + "4. Score selection\n 5. Question numbers\n 6. Correct answer points\n 7. Incorrect answer points\n 8. Based on\n 0. Back"); Choice = -1; while (true) { Choice = scan.nextInt(); if (checkint(Choice, 0, 8)) break; } /* 1. Start date 2. End date 3. Number of attempts 4. Score selection 5. Question numbers 6. Correct answer points 7. Incorrect answer points 8. Based on */ Scanner scan1 = new Scanner(System.in); if (Choice == 0) { System.out.print('\u000C'); break; } else if (Choice == 1) { System.out.printf( "Origionally Start Date:%s\nPlease enter new Start Date(yyyy-mm-dd):", startdate); while (true) { startdate = scan1.nextLine(); if (checkdate(startdate)) break; } startdate = startdate + " 00:00:00.0"; } else if (Choice == 2) { System.out.printf( "Origionally End Date:%s\nPlease enter new End Date(yyyy-mm-dd):", enddate); while (true) { enddate = scan1.nextLine(); if (checkdate(enddate)) break; } enddate = enddate + " 00:00:00.0"; } else if (Choice == 3) { System.out.printf( "Origionally Number of attempts:%d\nPlease enter new Number of attempts:", newallowattempt); newallowattempt = scan1.nextInt(); } else if (Choice == 4) { String[] sele = new String[5]; sele[1] = "first attempt"; sele[2] = "last attempt"; sele[3] = "average"; sele[4] = "max"; System.out.printf( "Origionally Score selection:%s\nPlease enter new Score selection(1.first attempt 2.last attempt 3.average 4.max):", sele[newscorescheme]); while (true) { newscorescheme = scan.nextInt(); if (checkint(newscorescheme, 1, 4)) { // give professor choice whether to update all the scores of students System.out.println( "Do you want to update all the students' scores? 1. Yes 2. No"); int updatechoice = scan.nextInt(); if (checkint(updatechoice, 1, 2)) { if (updatechoice == 1) { ProfUpdateScore pupdate = new ProfUpdateScore(); pupdate.profupdate(eid, newscorescheme); break; } } break; } } } else if (Choice == 5) { System.out.printf( "Origionally Question numbers:%d\nPlease enter new Question numbers:", newnumofquestion); newnumofquestion = scan1.nextInt(); } else if (Choice == 6) { System.out.printf( "Origionally Correct answer points:%d\nPlease enter new Correct answer points:", newcorrectpoint); newcorrectpoint = scan1.nextInt(); } else if (Choice == 7) { System.out.printf( "Origionally Incorrect answer points:%d\nPlease enter new Incorrect answer points:", newincorrectpoint); newincorrectpoint = scan1.nextInt(); } else if (Choice == 8) { String[] basedt = new String[4]; if (cid.equals("CSC440")) { basedt[1] = "Database Fundamentals"; basedt[2] = "ER Design"; basedt[3] = "Security and Authorization"; System.out.printf( "Origionally Based on: %s\nPlease enter new Base on:\n 1. Database Fundamentals\n 2. ER Design\n 3. Security and Authorization\n", basedt[newbasedon]); } else { basedt[1] = "Binary search trees and Btrees"; basedt[2] = "Hashing"; basedt[3] = "Files and indexing and other topics"; System.out.printf( "Origionally Based on: %s\nPlease enter new Base on:\n 1. Binary search trees and Btrees\n 2.Hashing 3.Files and indexing and other topics\n", basedt[newbasedon]); } while (true) { newbasedon = scan.nextInt(); if (checkint(newbasedon, 1, 3)) break; } } // System.out.println(enddate); String query = "UPDATE Exercise SET startdate = timestamp'" + startdate + "', enddate = timestamp'" + enddate + "'," + " correctpoint = " + newcorrectpoint + ",incorrectpoint = " + newincorrectpoint + ", scorescheme = " + newscorescheme + ", " + "allowattempt = " + newallowattempt + ", numofquestion = " + newnumofquestion + ", basedon = " + newbasedon + " WHERE eid = " + eid + " "; // edit homework Statement stmt3 = conn.createStatement(); // debug // System.out.println("query:"+query); if (stmt3.executeUpdate(query) == 1) { // System.out.print('\u000C'); System.out.println("Update Successful!"); } else { // System.out.print('\u000C'); System.out.println("Update Failure!Please try again."); } } } } conn.close(); } catch (Exception e) { System.out.println("" + e.getMessage()); } }