package jgradebook.server.database;
import java.io.IOException;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.mysql.jdbc.Statement;
import jgradebook.data.GradeSystem;
import jgradebook.data.IdFactory;
import jgradebook.data.WithID;
import jgradebook.tests.StoreTest;
import jgradebook.tests.utils;
/**
* @author cornelik
*
*/
public class DBconnection {
private static final boolean verboseSave = false;
private static final boolean verboseLoad = false;
private static boolean creatNew = false;
private static final String NULL_ESCAPE = "__none__";
final private boolean DEBUG = true;
final private boolean INFO = true;
final private boolean ssl = false;
private Connection con;
private Statement statement;
private String dbAddress = "";
private String dbUser = "";
private String dbName = "";
private String dbPassword = "";
String statementstring = "";
/**
* erstmal zu testzwecken
*
*/
public static GradeSystem gradeSystem;
private static final String DEFAULT_FILENAME = "gradebook.marks";
private static String filename = DEFAULT_FILENAME;
/**
*
* @param address
* Die Addresse unter der die Datenbank liegt
* @param name
* Name der Datenbank
* @param user
* Benutzerlogin der Datenbank
* @param password
* Das Password für den Benutzerlogin Dieser Konstrucktor sorgt
* für den Kontakt
*/
public DBconnection(String address, String name, String user,
String password) {
dbAddress = address;
dbUser = user;
dbName = name;
dbPassword = password;
connect();
}
/**
*
* @param address
* Die Addresse unter der die Datenbank liegt
* @param name
* Name der Datenbank
* @param newDBname
* Name der neuen daten Bank
* @param user
* Rootlogin der Datenbank
* @param password
* Root Password
* @param creatnew
* soll neue erstellt werden
* @param newUser
* Der Benutzerlogin der Datenbank
* @param newPassword
* Password für Benutzerlogin Dieser Konstrucktor sorgt für den
* Kontakt dann für das Anlegen einer neuen Datenbank sowie fuer
* den Reconnect unter neuem Namen an die neue Datenbank
*
*
*/
public DBconnection(String address, String name, String newDBname,
String user, String password, Boolean creatnew, String newUser,
String newPassword) {
this(address, name, user, password);
creatNew = creatnew;
createNewDB(newDBname, newUser, newPassword);
}
public String getDBAddress() {
return this.dbAddress;
}
public String getDBUser() {
return this.dbUser;
}
public String getDBName() {
return this.dbName;
}
/**
* zu testzwecken zum laden der alten Datensätze
*
* @throws
*
*/
public void loadDatafromFile() {
// Lae die Datenbank nur zu Testzwecken
try {
//die Lade Funktion ist statisch in einer Datei
StoreTest.load(filename);
if (DEBUG) {
System.out.println(" Data from file loaded");
}
} catch (IOException e) {
if (DEBUG) {
printExceptions(e);
}
} catch (ClassNotFoundException e) {
if (DEBUG) {
printExceptions(e);
}
}
}
/**
*
* @param dbNewame
* neuer Name der Datenbank
* @param newUser
* Userlogin der Datenbank
* @param newPassword
* Userpassword der Datenbank In dieser Funktion wird eine neue
* Datenbank angelegt dazu wird ein Reconect zur neu Angelegten
* Datenbank unter einem neuen User angelegt
*/
public void createNewDB(String dbNewName, String newUser, String newPassword) {
loadDatafromFile();
if (creatNew) {
connect();
createBase(dbNewName);
creatNew = false;
dbName = dbNewName;
dbUser = newUser;
dbPassword = newPassword;
}
connect(dbNewName, newUser, newPassword);
createDataBase();
}
/**
*
* @param driver
* @throws Exception
* Diese Funktion laed nur den Treiber
*
*/
private void loadDriver(String driver) throws Exception {
Class.forName(driver).newInstance();
}
/**
* Diese Funktion erledigt den Kontakt zur Datenbank
*
*/
public void connect() {
Boolean state;
try {
loadDriver("com.mysql.jdbc.Driver");
} catch (Exception e) {
if (DEBUG) {
printExceptions(e);
}
}
try {
if (ssl) {
con = DriverManager.getConnection("jdbc:mysql://" + dbAddress
+ ":3306/" + dbName + "?useSSL=true", dbUser,
dbPassword);
} else {
con = DriverManager.getConnection("jdbc:mysql://" + dbAddress
+ "/" + dbName, dbUser, dbPassword);
}
if (con != null) {
statement = (Statement) con.createStatement();
}
if (DEBUG || INFO) {
System.out.print("Connection to database " + dbAddress
+ " as User " + dbUser + " established\n");
}
} catch (SQLException s) {
if (DEBUG) {
printExceptions(s);
}
} /*
* finally { if (con != null) try { con.close(); } catch
* (SQLException e) { printExceptions(e); } }
*/
}
/**
*
* @param dbNewName
* Diese Funktion erledigt den Reconnect
*
*/
public void connect(String dbNewName, String newUser, String newPassword) {
Boolean state;
try {
loadDriver("com.mysql.jdbc.Driver");
} catch (Exception e) {
if (DEBUG) {
printExceptions(e);
}
}
try {
if (ssl) {
con = DriverManager.getConnection("jdbc:mysql://" + dbAddress
+ "/" + dbNewName + "?useSSL=true", newUser,
newPassword);
} else {
con = DriverManager.getConnection("jdbc:mysql://" + dbAddress
+ "/" + dbNewName, dbUser, dbPassword);
}
if (con != null) {
statement = (Statement) con.createStatement();
}
if (DEBUG || INFO) {
System.out.print("Reconnection to database " + dbAddress
+ " as User " + dbUser + " established\n");
}
} catch (SQLException s) {
if (DEBUG) {
printExceptions(s);
}
} /*
* finally { if (con != null) try { con.close(); } catch
* (SQLException e) { printExceptions(e); } }
*/
}
/**
*
* @param baseName
* Hier wird eine neue Datenbank erzeugt dazu benötigt man "root
* Rechte"
*
*/
private void createBase(String newDBname) {
Boolean state;
try {
state = statement.execute("DROP DATABASE" + " " + newDBname);
state = statement.execute("CREATE DATABASE IF NOT EXISTS" + " "
+ newDBname);
if (DEBUG || INFO) {
System.out.println("Create Database: " + newDBname
+ " and print state: " + state);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
printExceptions(e);
} /*
* finally { if (con != null) try { con.close(); } catch
* (SQLException e) { printExceptions(e); } }
*/
}
/**
* @param s
* Diese Funktion dient der Ausgabe von Debug informationen
*/
public void printExceptions(Exception s) {
if (s instanceof SQLException) {
int stop = 0;
while (s != null) {
stop++;
SQLException sql = (SQLException) s;
System.out
.println("Contact failed \n" + sql.getMessage());
System.out.println("ANSI-92 Code: " + sql.getSQLState());
System.out.println("DB-Code: " + sql.getErrorCode());
if (stop >= 5)
break;
}
} else if (s instanceof Exception) {
System.out.println("Somthing wrong of IO " + s.getMessage());
s.printStackTrace();
} else if (s instanceof ClassNotFoundException) {
System.out.println("Somthing wrong of File not found "
+ s.getMessage());
s.printStackTrace();
}else if (s instanceof BatchUpdateException) {
System.out.println("Somthing wrong, roll back " + s.getMessage());
}else {
s.printStackTrace();
System.out.println(s.getMessage());
s.getStackTrace();
}
}
private void createDataBase() {
try {
String classname;
/*
* Das nachfolgende stammt aus der Klass "store" JGradebook
* aus der IdFactory alle registrierten Objekte holen und sortieren
* neg Zahl kleiner, 0 gleich, 1 groesser Objekte nach ID sortieren
* die kleinste Zahl zuerst
*/
List<WithID> objects = new ArrayList<WithID>(IdFactory.getObjects());
Collections.sort(objects, new Comparator<WithID>() {
public int compare(WithID withID, WithID withID1) {
return withID.getId() - withID1.getId();
}
});
/*
* Tuppel mit Klasse und listen zu den Klassen./*
*/
Map<Class, List<WithID>> map = new HashMap<Class, List<WithID>>();
/*
* Vorsortierte withid den Klassen zuordnen
*/
for (WithID withID : objects) {
/*
* aus einer Withid die Klasse holen
*/
Class c = withID.getClass();
/*
* prüfen ObjektKlasse(z.B.Student)noch nicht in der liste ist
*/
if (!map.containsKey(c)) {
// Wenn das der Fall ist erstelle einen neuen Tuppel
map.put(c, new ArrayList<WithID>());
}
/*
* wenn Objektklasse schon vorhanden dann addiere WithidObjekt
* hinzu
*/
map.get(c).add(withID);
}
/*
* sort the classes alphabetical eine Liste mit Klassen und
* zugehorigen Objekten sortiert
*/
List<Class> classes = new ArrayList<Class>(map.keySet());
Collections.sort(classes, new Comparator<Class>() {
public int compare(Class aClass, Class aClass1) {
return aClass.getName().compareTo(aClass1.getName());
}
}); /* bis hier hin aus der Klasse store aus JGradebook */
/*
* Die Model-Klassen und deren Attribute herausholen
*
*/
for (Class aClass : classes) {
if (con != null) {
List<WithID> bucket;
List<String> attributeofaClass;
List<Object> classofClasses;
classname = aClass.getSimpleName();
// XXX
bucket = map.get(aClass);
attributeofaClass = bucket.get(0).getAttributes();
attributeofaClass.add(0, "id");
for (String attribute : attributeofaClass) {
if (attribute.equals("id")) {
String string = "CREATE TABLE " + "" + classname
+ " (" + attribute + " " + " VARCHAR(64) "
+ " PRIMARY KEY " + ")"; // 1
System.out.println(string);
statement.execute(string);
} else if (attribute.equals("comment")
|| attribute.equals("description")) {
String alter = "ALTER TABLE " + "" + classname
+ " ADD " + " (" + attribute + " "
+ " TEXT " + ")"; // 20
System.out.println(alter);
statement.execute(alter);
} else {
String alter = "ALTER TABLE " + ""
+ classname.trim() + " ADD " + " ("
+ attribute + " " + " VARCHAR(64) " + ")"; // 30
System.out.println(alter);
statement.execute(alter);
}
}
classofClasses = new ArrayList<Object>(attributeofaClass);
classofClasses.add(0, aClass.getSimpleName());
}
}
} catch (BatchUpdateException e) {
// con.rollback();
} catch (SQLException s) {
printExceptions(s);
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
printExceptions(e);
}
}
}
private void finalConnect() {
try {
con.close();
if (DEBUG || INFO) {
System.out.println("Connection closed");
}
} catch (SQLException e) {
if (DEBUG) {
printExceptions(e);
}
}
}
public static void main(String arg[]) throws IOException,
ClassNotFoundException {
/*
* Alles zu testen der Klasse.
*/
ClassLoader.getSystemClassLoader().setDefaultAssertionStatus(true);
DBconnection dbC = new DBconnection("127.0.1", "", "swp", "root",
"trexi", true, "cornelik", "tomtom");
// utils.printGradeSystem(dbC.gradeSystem);
// dbC.finalConnect();
}
}
