package trader.data;
import java.sql.*;
import java.util.ArrayList;
import trader.model.Person;
/**
* Connection with databases via JDBC and SQL.
*
* @author Tobias Mahncke
*
*/
public class SQLAdapter {
/**
* Creates a Connection with the HSQL-database.
*
* @author Tobias Mahncke
*
*/
private static Connection connectHSQLDB() {
try {
// load the drivers
Class.forName("org.hsqldb.jdbcDriver");
} catch (ClassNotFoundException e) {
System.err.println("No driver class!");
return null;
}
Connection con = null;
try {
// create the connection
con = DriverManager.getConnection("jdbc:hsqldb:file:data/test;shutdown=true");
// update the database to the current version if needed
update(con);
return con;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* Updates the database to the current version if needed. The current database version is 4.
*
* @author Tobias Mahncke
*
*/
private static void update(Connection con) {
try {
Statement statement = con.createStatement();
// Checks the version of the database
ResultSet rs = statement.executeQuery("SELECT * FROM Version");
if (!rs.next()) {
rs.close();
statement.close();
throw new IllegalArgumentException("The version is not given.");
} else {
int version = rs.getInt("version");
if (version == 1) {
// database is in the first version
// add the row 'id' to the table 'Person'
statement
.executeUpdate("ALTER TABLE Person ADD id VARCHAR(12) DEFAULT 'P-NULL' BEFORE firstname");
// the id must not be null
statement.executeUpdate("ALTER TABLE Person ALTER id SET NOT NULL");
// get all entries in the table 'Person'
rs = statement.executeQuery("SELECT * FROM Person");
while (rs.next()) {
Person person =
new Person(rs.getString("firstname"), rs.getString("surname"),
rs.getBoolean("sex"));
// add the hashCode as id
statement.executeUpdate("UPDATE Person SET id = '" + person.getId()
+ "' WHERE id = 'P-NULL' AND firstname = '" + person.getFirstname()
+ "' AND surname = '" + person.getSurname() + "' AND sex = "
+ person.getSex());
}
// Update the version number
version = 2;
}
if (version == 2) {
// database is in the second version
// add the rows 'motherId' and 'fatherId' to the table 'Person'
statement
.executeUpdate("ALTER TABLE Person ADD fatherId VARCHAR(12) DEFAULT 'P-NULL'");
statement
.executeUpdate("ALTER TABLE Person ADD motherId VARCHAR(12) DEFAULT 'P-NULL'");
// Update the version number
version = 3;
}
if (version == 3) {
// database is in the second version
// add the rows 'motherId' and 'fatherId' to the table 'Person'
statement
.executeUpdate("ALTER TABLE Person ADD children VARCHAR(12) ARRAY[15]");
// Update the version number
version = 4;
}
// write the version number into the database
statement.executeUpdate("UPDATE Version SET version = " + version);
}
rs.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* Adds the given person to the Database.
*
* @author Tobias Mahncke
*
* @param person
* The person which will be added to the database.
*/
public static void addPerson(Person person) {
Connection con = connectHSQLDB();
try {
Statement statement = con.createStatement();
// Checks if the person already exists in the database by using the id.
ResultSet rs =
statement.executeQuery("SELECT * FROM Person WHERE id = '" + person.getId()
+ "'");
if (!rs.next()) {
StringBuilder childrenSQL = new StringBuilder();
if (person.getChildren() > 0) {
childrenSQL.append("ARRAY['");
for (int i = 0; i < person.getChildren(); i++) {
childrenSQL.append(person.getChild(i).getId());
childrenSQL.append("']['");
}
childrenSQL.substring(0, childrenSQL.length() - 2);
} else {
childrenSQL.append("NULL");
}
// Adds the person to the database
String executionString =
"INSERT INTO Person VALUES('"
+ person.getId()
+ "','"
+ person.getFirstname()
+ "','"
+ person.getSurname()
+ "',"
+ person.getSex()
+ ",'"
+ (person.getFather() != null ? person.getFather().getId()
: "P-NULL")
+ "','"
+ (person.getMother() != null ? person.getMother().getId()
: "P-NULL") + "'," + childrenSQL + ")";
statement.executeUpdate(executionString);
}
rs.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
*
* @author Tobias Mahncke
*
* @param person
*/
public static void updatePerson(Person person) {
Connection con = connectHSQLDB();
try {
Statement statement = con.createStatement();
// Checks if the person already exists in the database by using the id.
ResultSet rs =
statement.executeQuery("SELECT * FROM Person WHERE id = '" + person.getId()
+ "'");
if (rs.next()) {
con.setAutoCommit(false);
String childrenSQL;
if (person.getChildren() > 0) {
StringBuilder childrenSQLBuilder = new StringBuilder("ARRAY['");
for (int i = 0; i < person.getChildren(); i++) {
childrenSQLBuilder.append(person.getChild(i).getId());
childrenSQLBuilder.append("']['");
}
childrenSQL = childrenSQLBuilder.substring(0, childrenSQLBuilder.length() - 2);
} else {
childrenSQL = "NULL";
}
// Updates the person in the database
statement.addBatch("UPDATE Person SET firstname = '" + person.getFirstname()
+ "' WHERE id = '" + person.getId() + "'");
statement.addBatch("UPDATE Person SET surname = '" + person.getSurname()
+ "' WHERE id = '" + person.getId() + "'");
statement.addBatch("UPDATE Person SET sex = " + person.getSex() + " WHERE id = '"
+ person.getId() + "'");
statement.addBatch("UPDATE Person SET motherId = '"
+ (person.getMother() != null ? person.getMother().getId() : "P-NULL")
+ "' WHERE id = '" + person.getId() + "'");
statement.addBatch("UPDATE Person SET fatherId = ' "
+ (person.getFather() != null ? person.getFather().getId() : "P-NULL")
+ "' WHERE id = '" + person.getId() + "'");
statement.addBatch("UPDATE Person SET children = " + childrenSQL + " WHERE id = '"
+ person.getId() + "'");
statement.executeBatch();
}
rs.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null)
try {
con.setAutoCommit(true);
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Finds a person by the given id.
*
* @author Tobias Mahncke
*
* @param id
* The id of the searched person.
* @return The searched person if existing, otherwise {@code null}.
*/
public static Person getPerson(String id) {
Connection con = connectHSQLDB();
try {
Statement statement = con.createStatement();
// Checks if the person already exists in the database by using the id.
String sql = "SELECT * FROM Person WHERE id = '" + id + "'";
ResultSet rs = statement.executeQuery(sql);
Person person = null;
if (rs.next()) {
// finds the person int the database
Person father = null;
String fatherId = rs.getString("fatherId");
if (!fatherId.trim().equals("P-NULL")) {
father = getPerson(fatherId);
}
Person mother = null;
String motherId = rs.getString("fatherId");
if (!motherId.trim().equals("P-NULL")) {
mother = getPerson(motherId);
}
person =
new Person(father, mother, rs.getString("firstname"),
rs.getString("surname"), rs.getBoolean("sex"));
StringBuilder childrenSQL = new StringBuilder();
String string = rs.getString("children");
if (string != null) {
childrenSQL.append(string);
while (childrenSQL.indexOf("P") != -1) {
int start = childrenSQL.indexOf("'") + 1;
int end = childrenSQL.indexOf("'", start);
// leads to a stack-overflow
// person.addChild(getPerson(childrenSQL.substring(start, end)));
childrenSQL = new StringBuilder(childrenSQL.substring(end + 1));
}
}
}
rs.close();
statement.close();
return person;
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/**
* @author Tobias Mahncke
*
* @return An {@link ArrayList} containing alle the {@link Person}s that exist in the database.
*/
public static ArrayList<Person> getPersons() {
Connection con = connectHSQLDB();
ArrayList<Person> persons = new ArrayList<Person>();
try {
Statement statement = con.createStatement();
// Checks if the person already exists in the database by using the id.
String sql = "SELECT * FROM Person";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
// finds the person int the database
Person father = null;
String fatherId = rs.getString("fatherId");
if (!fatherId.trim().equals("P-NULL")) {
father = getPerson(fatherId);
}
Person mother = null;
String motherId = rs.getString("fatherId");
if (!motherId.trim().equals("P-NULL")) {
mother = getPerson(motherId);
}
persons.add(new Person(father, mother, rs.getString("firstname"), rs
.getString("surname"), rs.getBoolean("sex")));
StringBuilder childrenSQL = new StringBuilder();
String string = rs.getString("children");
if (string != null) {
childrenSQL.append(string);
while (childrenSQL.indexOf("P") != -1) {
int start = childrenSQL.indexOf("'") + 1;
int end = childrenSQL.indexOf("'", start);
// leads to a stack-overflow
// person.addChild(getPerson(childrenSQL.substring(start, end)));
childrenSQL = new StringBuilder(childrenSQL.substring(end + 1));
}
}
}
rs.close();
statement.close();
return persons;
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/**
* @author Tobias Mahncke
*
* @return The ids of all the persons in the database.
*/
public static ArrayList<String> getPersonIDs() {
Connection con = connectHSQLDB();
ArrayList<String> ids = new ArrayList<String>();
try {
Statement statement = con.createStatement();
// selects all the ids in the database
String sql = "SELECT id FROM Person";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
ids.add(rs.getString(1));
}
rs.close();
statement.close();
return ids;
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}