From ce547052ee1949bd31af8b940348e91861d7184e Mon Sep 17 00:00:00 2001 From: "Jannis (jix) Harder" Date: Fri, 21 Aug 2009 15:41:19 +0200 Subject: model angefangen - user testen/erzeugen - abfrage von kategorien --- src/jeopardy/DBModel.java | 78 ++++++++++++++++++++++++++++++++++++++++++++++ src/jeopardy/Jeopardy.java | 11 +++++-- src/jeopardy/Model.java | 6 +++- 3 files changed, 92 insertions(+), 3 deletions(-) create mode 100644 src/jeopardy/DBModel.java (limited to 'src') 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 validCategoriesForUsers(ArrayList users) + { + try { + for(int i = 0; i < 6; i++) { + getValidCategoriesStmnt.setString(i + 1, users.size() <= i ? "" : users.get(i)); + } + ResultSet r = getValidCategoriesStmnt.executeQuery(); + ArrayList result = new ArrayList(); + while(r.next()) { + result.add(r.getString(1)); + } + return result; + } + catch (SQLException e) { + e.printStackTrace(); + System.exit(1); + return null; + } + } + + + +} diff --git a/src/jeopardy/Jeopardy.java b/src/jeopardy/Jeopardy.java index 7b5f3db..341a5c5 100644 --- a/src/jeopardy/Jeopardy.java +++ b/src/jeopardy/Jeopardy.java @@ -1,13 +1,20 @@ package jeopardy; +import java.util.ArrayList; +import java.util.Arrays; + +import jeopardy.Controller; +import jeopardy.DBModel; +import jeopardy.Model; + public class Jeopardy { /** * @param args */ public static void main(String[] args) { - // TODO Auto-generated method stub - + Model mod = new DBModel("jdbc:mysql://localhost:3306/jeopardy?user=jeopardy&password=j3opardy"); + System.out.println(mod.validCategoriesForUsers(new ArrayList(Arrays.asList("Jannis","du!")))); } } diff --git a/src/jeopardy/Model.java b/src/jeopardy/Model.java index 8c6fcbc..f290abf 100644 --- a/src/jeopardy/Model.java +++ b/src/jeopardy/Model.java @@ -1,5 +1,9 @@ package jeopardy; +import java.util.ArrayList; + public interface Model { - + public boolean userExists(String name); + public void createUser(String name); + public ArrayList validCategoriesForUsers(ArrayList users); } -- cgit v1.2.3