model angefangen

- user testen/erzeugen
- abfrage von kategorien

3 files changed, 92 insertions, 3 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;
+		}
+	}
+	
+	
+	
+}
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<String>(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<String> validCategoriesForUsers(ArrayList<String> users);
 }