/*
* Datenbank.java
*
* Created on 15. Januar 2008, 14:38
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package db;
import data.Duell;
import data.User;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
/**
*
* @author nadine
*/
public class Datenbank extends Database
{
private static Datenbank theInstance = null;
public static Datenbank getInstance()
throws ClassNotFoundException
{
if (theInstance == null)
{
theInstance = new Datenbank();
}
return theInstance;
}
/** Creates a new instance of Datenbank */
private Datenbank()
throws ClassNotFoundException
{
super("org.postgresql.Driver",
"jdbc:postgresql://127.0.0.1/jschafa", "jschafa", "jschafa");
tryToCreateTables();
}
// SQL-Statement zum Tabellen erstellen
private static String[] sql =
{
"CREATE TABLE benutzer" +
" ( nick TEXT PRIMARY KEY," +
" nachname TEXT NOT NULL," +
" vorname TEXT NOT NULL," +
" klasse TEXT NOT NULL," +
" passwort TEXT NOT NULL," +
" kooperieren INT," +
" nichtkooperieren INT," +
" punktegesamt INT," +
" punkte5 INT," +
" punkte10 INT," +
" punkte20 INT," +
" punkte50 INT," +
" online BOOLEAN NOT NULL," +
" ip TEXT NOT NULL" +
" )",
"CREATE TABLE duell" +
" ( duellid SERIAL PRIMARY KEY," +
" nick1 TEXT REFERENCES benutzer(nick)," +
" nick2 TEXT REFERENCES benutzer(nick)," +
" pg1 INT," +
" pg2 INT," +
" kk INT," +
" kn INT," +
" nk INT," +
" nn INT," +
" rn INT," +
" w1 BOOLEAN," +
" w2 BOOLEAN," +
" p1 INT," +
" p2 INT," +
" fertig BOOLEAN NOT NULL" +
" )"
};
// Verbinden, SQL-Statement (Tabellen erstellen) ausführen, Verbindung beenden
private void tryToCreateTables()
{
try
{
connect();
for (String sqlCommand : sql)
{
try
{
executeUpdate(sqlCommand);
}
catch (Exception ex)
{
ex.printStackTrace();
}
;
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
disconnect();
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}
// Benutzer hinzufügen
public void addUser(User user)
throws Exception
{
final String sql = "INSERT INTO benutzer" +
" (nick,nachname,vorname,klasse,passwort,kooperieren,nichtkooperieren," +
" punktegesamt,punkte5,punkte10,punkte20,punkte50,online,ip)" +
" VALUES (" +
" '" + user.getNick() + "', " +
" '" + user.getNachname() + "'," +
" '" + user.getVorname() + "'," +
" '" + user.getKlasse() + "'," +
" '" + user.getPwHash() + "'," +
" " + user.getKoop() + "," +
" " + user.getNKoop() + "," +
" " + user.getPunkteG() + "," +
" " + user.getPunkte5() + "," +
" " + user.getPunkte10() + "," +
" " + user.getPunkte20() + "," +
" " + user.getPunkte50() + "," +
" " + user.getOnline() + "," +
" '" + user.getIp() + "')";
Statement stmt = null;
try
{
connect();
stmt = getConnection().createStatement();
stmt.executeUpdate(sql);
}
finally
{
if (stmt != null)
{
stmt.close();
}
disconnect();
}
}
// Benutzer ändern für Onlinestatus
public void updateOnlineAndIp(User user)
throws Exception
{
final String sql = "UPDATE benutzer" +
" SET online = " + user.getOnline() + ", " +
" ip = '" + user.getIp() + "' " +
" WHERE nick = '" + user.getNick() + "'";
super.executeUpdate(sql);
}
// Benutzer ändern für Punkte und koop/nKoop
public void updateUser(User user)
throws Exception
{
final String sql = "UPDATE benutzer" +
" SET kooperieren = " + user.getKoop() + ", " +
" nichtkooperieren = " + user.getNKoop() + ", " +
" punktegesamt = " + user.getPunkteG() + ", " +
" punkte5 = " + user.getPunkte5() + ", " +
" punkte10 = " + user.getPunkte10() + ", " +
" punkte20 = " + user.getPunkte20() + ", " +
" punkte50 = " + user.getPunkte50() + " " +
" WHERE nick = '" + user.getNick() + "'";
super.executeUpdate(sql);
}
// User herausfinden, der sich gerade eingeloggt hat
public User getLoggedUser(String nick, String pwHash)
throws Exception
{
final String sql = "SELECT * FROM benutzer WHERE nick = '" + nick +
"' AND passwort = '" + pwHash + "'";
User loggedUser = null;
Statement stmt = null;
ResultSet res = null;
try
{
connect();
stmt = getConnection().createStatement();
res = stmt.executeQuery(sql);
if (res.next())
{
loggedUser = new User(res);
}
}
finally
{
if (res != null)
{
res.close();
}
if (stmt != null)
{
stmt.close();
}
disconnect();
}
return loggedUser;
}
// IP des zweiten Spielers herausfinden
public String getIpFromUser(String nick)
throws SQLException
{
final String sql = "SELECT ip FROM benutzer WHERE nick = '" + nick + "'";
Statement stmt = null;
ResultSet res = null;
try
{
connect();
stmt = getConnection().createStatement();
res = stmt.executeQuery(sql);
res.next();
return res.getString("ip");
}
finally
{
if (res != null)
{
res.close();
}
if (stmt != null)
{
stmt.close();
}
disconnect();
}
}
// Alle Benutzer aus Datenbank holen
public List<User> getUsers()
throws SQLException
{
final List<User> users = new LinkedList<User>();
Statement stmt = null;
ResultSet res = null;
try
{
connect();
stmt = getConnection().createStatement();
res = stmt.executeQuery("SELECT * FROM benutzer ORDER BY nick");
while (res.next())
{
users.add(new User(res));
}
return users;
}
finally
{
if (res != null)
{
res.close();
}
if (stmt != null)
{
stmt.close();
}
disconnect();
}
}
// Alle Benutzer die online sind aus der Datenbank holen
public List<User> getOnlineUsers()
throws SQLException
{
final List<User> users = new LinkedList<User>();
Statement stmt = null;
ResultSet res = null;
try
{
connect();
stmt = getConnection().createStatement();
res = stmt.executeQuery("SELECT * FROM benutzer WHERE online=true");
while (res.next())
{
users.add(new User(res));
}
return users;
}
finally
{
if (res != null)
{
res.close();
}
if (stmt != null)
{
stmt.close();
}
disconnect();
}
}
// Duell hinzufügen und laufende Nummer generieren
public void addDuell(Duell duell)
throws Exception
{
final String sql = "INSERT INTO duell" +
" (nick1,nick2,pg1,pg2,kk,kn,nk,nn,rn,w1,w2,p1,p2,fertig)" +
" VALUES (" +
" '" + duell.getNick1() + "', " +
" '" + duell.getNick2() + "', " +
" " + duell.getPg1() + ", " +
" " + duell.getPg2() + ", " +
" " + duell.getKk() + ", " +
" " + duell.getKn() + ", " +
" " + duell.getNk() + ", " +
" " + duell.getNn() + ", " +
" " + duell.getRn() + ", " +
" " + duell.getW1() + ", " +
" " + duell.getW2() + ", " +
" " + duell.getP1() + ", " +
" " + duell.getP2() + ", " +
" " + duell.getFertig() + ")";
Statement stmt = null;
ResultSet res = null;
try
{
connect();
stmt = getConnection().createStatement();
stmt.executeUpdate(sql);
res = stmt.executeQuery("SELECT currval('duell_duellid_seq')");
if (res.next())
{
int id = res.getInt(1);
duell.setDuellid(id);
}
}
finally
{
if (res != null)
{
res.close();
}
if (stmt != null)
{
stmt.close();
}
disconnect();
}
}
/* Duell updaten für Punkte, Varianten und fertig-Status
* fertig-Status wird benötigt für ein Spiel, genauere Informationen gibt es
* bei mir (Naddl)
*/
public void updateDuell(Duell duell)
throws Exception
{
final String sql = "UPDATE duell" +
" SET pg1 = " + duell.getP1() + ", " +
" pg2 = " + duell.getP2() + ", " +
" kk = " + duell.getKk() + ", " +
" kn = " + duell.getKn() + ", " +
" nk = " + duell.getNk() + ", " +
" nn = " + duell.getNn() + ", " +
" fertig = " + duell.getFertig() + " " +
" WHERE nick1 = '" + duell.getNick1() + "' AND nick2 = '" +
duell.getNick2() + "'";
super.executeUpdate(sql);
}
/* Jede einzelne Runde wird immer von neuem überschrieben (für Spieler1
* und Spieler2 getrennt aber gleich implementiert
*/
public void updateRundeSpieler1(Duell duell)
throws Exception
{
final String sql = "UPDATE duell" +
" SET w1 = " + duell.getW1() + ", " +
" p1 = " + duell.getP1() + " " +
" WHERE nick1 = '" + duell.getNick1() + "' AND nick2 = '" +
duell.getNick2() + "' AND rn = " + duell.getRn();
super.executeUpdate(sql);
}
public void updateRundeSpieler2(Duell duell)
throws Exception
{
final String sql = "UPDATE duell" +
" SET w2 = " + duell.getW2() + ", " +
" p2 = " + duell.getP2() + " " +
" WHERE nick1 = '" + duell.getNick1() + "' AND nick2 = '" +
duell.getNick2() + "' AND rn = " + duell.getRn();
super.executeUpdate(sql);
}
/* Datenbankversion?! Evtl. nicht benötigt^^
*/
public List<Duell> getYourDuell(User user)
throws SQLException
{
final List<Duell> duelle = new LinkedList<Duell>();
Statement stmt = null;
ResultSet res = null;
try
{
connect();
stmt = getConnection().createStatement();
res = stmt.executeQuery("SELECT * FROM duell WHERE fertig=false AND " +
"nick2 = " + user.getNick() + "");
while (res.next())
{
duelle.add(new Duell(res));
}
return duelle;
}
finally
{
if (res != null)
{
res.close();
}
if (stmt != null)
{
stmt.close();
}
disconnect();
}
}
}