diff options
author | Jannis (jix) Harder <jix@method-missing.org> | 2009-08-21 15:41:19 +0200 |
---|---|---|
committer | Jannis (jix) Harder <jix@method-missing.org> | 2009-08-21 15:41:19 +0200 |
commit | ce547052ee1949bd31af8b940348e91861d7184e (patch) | |
tree | 293a577abcefc7b2def81d7e56e757bbcdc1cc11 /src/jeopardy/DBModel.java | |
parent | 1c5cbb8b51492c4d9e5528526f8a5664599a1a12 (diff) | |
download | jeopardy-ce547052ee1949bd31af8b940348e91861d7184e.tar jeopardy-ce547052ee1949bd31af8b940348e91861d7184e.zip |
model angefangen
- user testen/erzeugen
- abfrage von kategorien
Diffstat (limited to 'src/jeopardy/DBModel.java')
-rw-r--r-- | src/jeopardy/DBModel.java | 78 |
1 files changed, 78 insertions, 0 deletions
diff --git a/src/jeopardy/DBModel.java b/src/jeopardy/DBModel.java new file mode 100644 index 0000000..07abecc --- /dev/null +++ b/src/jeopardy/DBModel.java @@ -0,0 +1,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; + } + } + + + +} |