K86Jvjsb

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