CXsh4Apz

1package com.example;
2
3import java.sql.*;
4
5public class MyClass {
6    // JDBC driver name and database URL
7    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
8    static final String DB_URL = "jdbc:mysql://localhost:3306/uetcodehub";
9
10    //  Database credentials
11    static final String USER = "homestead";
12    static final String PASS = "secret";
13
14    //delay time between updates
15    static int DELAY = 10; //in minutes
16
17    static int RunTimes = 0;
18    static Connection conn = null;
19    static Statement stmt = null;
20
21    static String sql = "SELECT tab1.problemId, tab1.numOfUser AS submittedUser, tab2.numOfUser AS finishedUser " +
22            "FROM (SELECT problemId, count(userId) as numOfUser from( " +
23            "	select problems.problemId, submissions.userId, max(submissions.resultScore) as userScore, submissions.courseId, problems.defaultScore " +
24            "	from problems left join submissions on problems.problemId = submissions.problemId " +
25            "	group by problems.problemId, submissions.userId) as s " +
26            "group by problemId) as tab1 " +
27            "LEFT JOIN " +
28            "(select problems.problemId, count(s.userId) as numOfUser from( " +
29            "	select problems.problemId, submissions.userId, max(submissions.resultScore) as userScore, submissions.courseId, problems.defaultScore " +
30            "	from problems left join submissions on problems.problemId = submissions.problemId " +
31            "	group by problems.problemId, submissions.userId having userScore = defaultScore) as s  " +
32            "	right join problems on s.problemId = problems.problemId " +
33            "group by problems.problemId) as tab2 " +
34            "ON tab1.problemId=tab2.problemId";
35
36    public static void main(String[] args) {
37        if (args.length != 0) {
38            System.out.println("timed="+args[0]);
39            DELAY = Integer.valueOf(args[0]);
40        }
41
42        //STEP 2: Register JDBC driver
43        try {
44            Class.forName("com.mysql.jdbc.Driver");
45            //STEP 3: Open a connection
46            System.out.println("Connecting to database...");
47            conn = DriverManager.getConnection(DB_URL,USER,PASS);
48
49            //STEP 4: Execute a query
50            System.out.println("Creating statement...");
51            stmt = conn.createStatement();
52
53            String sql_create;
54            String sql;
55            //Create table if not existed
56            sql_create = "create table if not exists problemsolvingresult(" +
57                    "problemId int PRIMARY KEY," +
58                    " submittedUser int," +
59                    " finishedUser int);";
60            PreparedStatement pst =  conn.prepareStatement(sql_create);
61            int numRowsChanged = pst.executeUpdate();
62
63            updateTable();
64        } catch (ClassNotFoundException e) {
65            e.printStackTrace();
66        } catch (SQLException e) {
67            e.printStackTrace();
68        } finally{
69            //finally block used to close resources
70            try{
71                if(stmt!=null)
72                    stmt.close();
73            }catch(SQLException se2){
74            }// nothing we can do
75            try{
76                if(conn!=null)
77                    conn.close();
78            }catch(SQLException se){
79                se.printStackTrace();
80            }//end finally try
81        }//end try
82    }
83
84    public static void updateTable() {
85        RunTimes++;
86        try{
87            ResultSet rs = stmt.executeQuery(sql);
88            PreparedStatement pst_del =  conn.prepareStatement(
89                    "DELETE FROM problemsolvingresult"
90            );
91            pst_del.executeUpdate();
92
93            //STEP 5: Extract data from result set
94            while(rs.next()){
95                //Retrieve by column name
96                int pid  = rs.getInt("problemId");
97                int submit  = rs.getInt("submittedUser");
98                int done  = rs.getInt("finishedUser");
99
100                //Display values
101                System.out.print("pid: " + pid);
102                System.out.print(", submit: " + submit);
103                System.out.println(", done: " + done);
104
105                //insert
106                PreparedStatement pstt =  conn.prepareStatement(
107                        "INSERT INTO problemsolvingresult (" +
108                                "problemId, submittedUser, finishedUser) VALUES" +
109                                "(" + pid + "," + submit + "," + done + ")"
110                );
111                pstt.executeUpdate();
112            }
113        }catch(SQLException se){
114            //Handle errors for JDBC
115            se.printStackTrace();
116        }catch(Exception e){
117            //Handle errors for Class.forName
118            e.printStackTrace();
119        }
120        System.out.println("Runned: "+RunTimes);
121        try {
122            Thread.sleep(DELAY * 60000);
123            updateTable();
124        } catch (InterruptedException e) {
125            e.printStackTrace();
126        }
127    }
128}