package database;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
import data_model.card;
import data_model.gambler;
public class DatabaseConnection implements DataProvider
{
private static DatabaseConnection instance;
private Logger logger = Logger.getLogger(DatabaseConnection.class.getName());
private FileHandler fh;
private String data;
private String user;
private String password;
private Connection con;
private PreparedStatement ps_insertGambler;
private PreparedStatement ps_updateGambler;
private PreparedStatement ps_deleteGambler;
private PreparedStatement ps_getGambler;
private PreparedStatement ps_getAllGamblers;
private PreparedStatement ps_getCards;
private PreparedStatement ps_insertCard;
private PreparedStatement ps_updateCard;
private PreparedStatement ps_getAllStyles;
private PreparedStatement ps_getStyleByName;
private PreparedStatement ps_getStyleByInt;
private DatabaseConnection()
{
logger.setLevel(Level.ALL);
try
{
fh = new FileHandler( "log.txt" );
logger.addHandler(fh);
fh.setFormatter(new SimpleFormatter());
}
catch (SecurityException exception)
{
logger.log(Level.WARNING, "Erzeugung des FileHandlers fehlgeschlagen", exception);
}
catch (IOException exception)
{
logger.log(Level.WARNING, "Erzeugung des FileHandlers fehlgeschlagen", exception);
}
try
{
Properties prop = new Properties();
FileInputStream fis = new FileInputStream("./src/model/config.properties");
prop.load(fis);
this.data = prop.getProperty("data");
this.user = prop.getProperty("user");
this.password = prop.getProperty("password");
logger.log(Level.INFO, "Datenbankeinstellungen erfolgreich geladen");
}
catch (IOException exception)
{
logger.log(Level.WARNING, "Einstellungen konnten nicht aus der Config-Datei ermittelt werden", exception);
}
}
public static DatabaseConnection getInstance()
{
if (instance == null)
{
instance = new DatabaseConnection();
}
return instance;
}
public void connect() throws ProblemwithDataApplication
{
try
{
Class.forName("org.hsqldb.jdbcDriver");
logger.log(Level.INFO, "Datenbanktreiber erfolgreich geladen");
}
catch (ClassNotFoundException exception)
{
handleProblem(exception, "HSQLSB-Treiber nicht gefunden");
}
try
{
con = DriverManager.getConnection(data, user, password);
con.setAutoCommit(true);
prepareStatements();
logger.log(Level.INFO, "Datenbankverbindung und Statements erfolgreich aufgebaut");
}
catch (SQLException exception)
{
handleProblem(exception, "Verbindung zur Datenbank konnte nicht aufgebaut werden");
}
}
public void shutdown() throws ProblemwithDataApplication
{
try
{
Statement stmt = con.createStatement();
stmt.execute("SHUTDOWN");
stmt.close();
ps_insertGambler.close();
ps_updateGambler.close();
ps_deleteGambler.close();
ps_getGambler.close();
ps_getAllGamblers.close();
ps_getCards.close();
ps_insertCard.close();
ps_updateCard.close();
ps_getAllStyles.close();
ps_getStyleByName.close();
ps_getStyleByInt.close();
con.close();
logger.log(Level.INFO, "Verbindung zur Datenbank erfolgreich geschlossen");
}
catch (SQLException exception)
{
handleProblem(exception, "Shutdown fehlgeschlagen");
}
}
private void prepareStatements() throws ProblemwithDataApplication
{
try
{
ps_insertGambler = con.prepareStatement("INSERT INTO Gambler (gambler_id, name, money, style) VALUES (NEXT VALUE FOR gambler_id_seq, ?, ?, ?)");
ps_updateGambler = con.prepareStatement("UPDATE Gambler SET money = ?, style = ? WHERE gambler_id = ?");
ps_deleteGambler = con.prepareStatement("DELETE FROM Gambler WHERE name = ?");
ps_getGambler = con.prepareStatement("SELECT * FROM Gambler WHERE name = ?");
ps_getAllGamblers = con.prepareStatement("SELECT name FROM Gambler");
ps_getCards = con.prepareStatement("SELECT * FROM GamblerCards WHERE gambler_id = ? AND (imDeck = ? OR imDeck = ?)");
ps_insertCard = con.prepareStatement("INSERT INTO GamblerCards (gambler_id, id, north, south, east, west, price, name, picture, imDeck) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
ps_updateCard = con.prepareStatement("UPDATE GamblerCards SET north = ?, south = ?, east = ?, west = ?, imDeck = ? WHERE gambler_id = ? AND id = ? ");
ps_getAllStyles = con.prepareStatement("SELECT name FROM Field");
ps_getStyleByName = con.prepareStatement("SELECT id FROM Field where name = ?");
ps_getStyleByInt = con.prepareStatement("SELECT picture FROM Field where id = ?");
}
catch (SQLException exception)
{
handleProblem(exception, "Fehler beim Erzeugen der Statements");
}
}
public void insertGambler(gambler g) throws ProblemwithDataApplication
{
try
{
ps_insertGambler.setString(1, g.getName());
ps_insertGambler.setInt(2, g.getMoney());
ps_insertGambler.setInt(3, g.getStyle());
ps_insertGambler.executeUpdate();
logger.log(Level.INFO, "Spieler mit dem Namen " + g.getName() + " erfolgreich angelegt");
}
catch (SQLException exception)
{
handleProblem(exception, "Spieler konnte auf der Datenbank nicht angelegt werden");
}
}
public void updateGambler(gambler g) throws ProblemwithDataApplication
{
try
{
ps_updateGambler.setInt(1, g.getMoney());
ps_updateGambler.setInt(2,g.getStyle());
ps_updateGambler.setInt(3, g.getId());
ps_updateGambler.executeUpdate();
ArrayList<card> allCards = g.getAllCards();
int x = allCards.size();
for (int i=0; i<x; i++)
{
updateCard(allCards.get(i));
}
logger.log(Level.INFO, "Update f�r den Spieler mit dem Namen " + g.getName() + " erfolgreich");
}
catch (SQLException exception)
{
handleProblem(exception, "Update des Spielers auf der Datenbank fehlgeschlagen");
}
}
public void deleteGambler(String name) throws ProblemwithDataApplication
{
try
{
ps_deleteGambler.setString(1,name);
System.out.print(name);
ps_deleteGambler.executeUpdate();
logger.log(Level.INFO, "Spieler mit dem Namen " + name + " erfolgreich gel�scht");
}
catch (SQLException exception)
{
handleProblem(exception, "Probleme beim L�schen des Spielers");
}
}
public gambler getGambler(String gambler) throws ProblemwithDataApplication
{
try
{
ps_getGambler.setString(1, gambler);
ResultSet rs = ps_getGambler.executeQuery();
rs.next();
int id = rs.getInt(1);
logger.log(Level.INFO, "Spielerinformation des Spielers mit dem Namen " + gambler + " erfolgreich ausgelesen");
return new gambler(id, rs.getString(2), rs.getInt(3), rs.getInt(4), getCards(id, true, false), getCards(id, true, true),null);
}
catch (SQLException exception)
{
handleProblem(exception, "Spielerdaten konnte nicht gelesen werden");
}
return null;
}
public ArrayList<card> getCards(int id, boolean x, boolean y) throws ProblemwithDataApplication
{
try
{
ps_getCards.setInt(1, id);
ps_getCards.setBoolean(2, x);
ps_getCards.setBoolean(3, y);
ResultSet rs = ps_getCards.executeQuery();
ArrayList<card> Cards = new ArrayList<card>();
while (rs.next())
{
Cards.add(new card(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6), rs.getInt(7), rs.getString(8), rs.getString(9), rs.getBoolean(10)));
}
logger.log(Level.INFO, "Karteninformation des Spieler mit der ID " + id + " erfolgreich ausgelesen");
return Cards;
}
catch (SQLException exception)
{
handleProblem(exception, "Karten des Spielers konnten nicht gelesen werden");
}
return null;
}
public void insertCard(card c) throws ProblemwithDataApplication
{
try
{
ps_insertCard.setInt(1, c.getGamblerId());
ps_insertCard.setInt(2, c.getId());
ps_insertCard.setInt(3, c.getNorth());
ps_insertCard.setInt(4, c.getSouth());
ps_insertCard.setInt(5, c.getEast());
ps_insertCard.setInt(6, c.getWest());
ps_insertCard.setInt(7, c.getPrice());
ps_insertCard.setString(8, c.getName());
ps_insertCard.setString(9, c.getPicture());
ps_insertCard.setBoolean(10, c.getImDeck());
ps_insertCard.executeUpdate();
logger.log(Level.INFO, "Die Karte mit dem Namen " + c.getName() + " wurde erfolgreich in der DB angelegt");
}
catch (SQLException exception)
{
handleProblem(exception, "Karte konnte nicht hinzugef�gt werden");
}
}
public void insertCardCollection(ArrayList<card> collection) throws ProblemwithDataApplication
{
for (card v : collection)
insertCard(v);
}
public void updateCard(card c) throws ProblemwithDataApplication
{
try
{
ps_updateCard.setInt(1, c.getNorth());
ps_updateCard.setInt(2, c.getSouth());
ps_updateCard.setInt(3, c.getEast());
ps_updateCard.setInt(4, c.getWest());
ps_updateCard.setBoolean(5, c.getImDeck());
ps_updateCard.setInt(6, c.getGamblerId());
ps_updateCard.setInt(7, c.getId());
logger.log(Level.INFO, "Die Karte mit dem Namen " + c.getName() + " wurde erfolgreich in der DB aktualisiert");
}
catch (SQLException exception)
{
handleProblem(exception, "Kartenupdate fehlgeschlagen");
}
}
public ArrayList<String> getAllGamblers() throws ProblemwithDataApplication
{
return getInformation("allGamblers");
}
public ArrayList<String> getAllStyles() throws ProblemwithDataApplication
{
return getInformation("allStyles");
}
private ArrayList<String> getInformation(String x) throws ProblemwithDataApplication
{
ArrayList<String> information = new ArrayList<String>();
try
{
ResultSet rs = null;
if (x.equals("allStyles"))
{
rs = ps_getAllStyles.executeQuery();
}
if (x.equals("allGamblers"))
{
rs = ps_getAllGamblers.executeQuery();
}
while (rs.next())
{
information.add(rs.getString(1));
}
logger.log(Level.INFO, "Information " + x + " erfolgreich aus der DB gelesen");
return information;
}
catch (SQLException exception)
{
handleProblem(exception, "Fehler beim Auslesen der gew�nschten Information " + x);
}
return null;
}
public int getStyleByName(String name) throws ProblemwithDataApplication
{
try
{
ps_getStyleByName.setString(1, name);
ResultSet rs = ps_getStyleByName.executeQuery();
rs.next();
logger.log(Level.INFO, "Styleinformation anhand des Namen " + name + " erfolgreich ausgelesen");
return rs.getInt(1);
}
catch (SQLException exception)
{
handleProblem(exception, "Fehler beim Auslesen der Styleinformation anhand des Namen " + name);
}
return 0;
}
public String getStyleByInt(int id) throws ProblemwithDataApplication
{
try
{
ps_getStyleByInt.setInt(1, id);
ResultSet rs = ps_getStyleByInt.executeQuery();
rs.next();
logger.log(Level.INFO, "Styleinformation anhand der ID: " + id + " erfolgreich ausgelesen");
return rs.getString(1);
}
catch (SQLException exception)
{
handleProblem(exception, "Fehler beim Auslesen der Styleinformation anhand der ID: " + id);
}
return null;
}
protected void finalize() throws Throwable
{
shutdown();
}
// Diese Methode wirft immer eine Exception
private void handleProblem(Exception exception, String error) throws ProblemwithDataApplication
{
logger.log(Level.WARNING, error, exception);
throw new ProblemwithDataApplication(exception, error);
}
}