blob: 07abeccce1a212bab9c52e46d5829467c6714b4a (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
package jeopardy;
import java.sql.*;
import java.util.ArrayList;
public class DBModel implements Model {
private Connection con;
private PreparedStatement createUserStmnt;
private PreparedStatement checkUserStmnt;
private PreparedStatement getValidCategoriesStmnt;
public DBModel(String url) {
try {
con = DriverManager.getConnection(url);
createUserStmnt = con.prepareStatement("INSERT INTO `users` (`name`) VALUES (?)");
checkUserStmnt = con.prepareStatement("SELECT COUNT(*) FROM `users` WHERE `name` = ?");
getValidCategoriesStmnt = con.prepareStatement( // TODO: this has to support more than 6 players
"SELECT categories.name FROM aqpairs, categories WHERE aqpairs.used = 0 AND aqpairs.category = categories.id AND aqpairs.id NOT IN "
+ "(SELECT DISTINCT aqpairs.id FROM aqpairs, authors, users WHERE aqpairs.used = 0 AND users.name IN (?, ?, ?, ?, ?, ?) AND users.id = authors.author AND authors.aqpair = aqpairs.id )"
+ " GROUP BY category HAVING COUNT(aqpairs.id) >= 2");
}
catch (SQLException e) {
e.printStackTrace();
System.exit(1);
}
}
public void createUser(String name) {
try {
createUserStmnt.setString(1, name);
createUserStmnt.execute();
}
catch (SQLException e) {
e.printStackTrace();
System.exit(1);
}
}
public boolean userExists(String name) {
try {
checkUserStmnt.setString(1, name);
ResultSet r = checkUserStmnt.executeQuery();
r.next();
return (r.getInt(1) == 1);
}
catch (SQLException e) {
e.printStackTrace();
System.exit(1);
return false;
}
}
public ArrayList<String> validCategoriesForUsers(ArrayList<String> users)
{
try {
for(int i = 0; i < 6; i++) {
getValidCategoriesStmnt.setString(i + 1, users.size() <= i ? "" : users.get(i));
}
ResultSet r = getValidCategoriesStmnt.executeQuery();
ArrayList<String> result = new ArrayList<String>();
while(r.next()) {
result.add(r.getString(1));
}
return result;
}
catch (SQLException e) {
e.printStackTrace();
System.exit(1);
return null;
}
}
}
|
