package MVCTableDAO.dao;
import MVCTableDAO.model.Department;
import MVCTableDAO.model.Employee;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class MitarbeiterDAOToSQLite implements MitarbeiterDAOInterface {
private Connection c;
private Statement statement;
public MitarbeiterDAOToSQLite() {
this.establishConnection();
this.createDepartmentsDatabaseTable();
this.createEmployeesDatabaseTable();
}
public void establishConnection() {
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:./test3.db");
} catch (Exception e) {
e.printStackTrace();
}
}
public void createEmployeesDatabaseTable() {
try {
statement = c.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS " + "employees" +
"( id CHAR (6)," +
"vorname VARCHAR(255) NOT NULL, " +
"nachname VARCHAR(255) NOT NULL, " +
"image BLOB," +
"department_id CHAR(6) NOT NULL," +
"CONSTRAINT pk_employees PRIMARY KEY (id), " +
"CONSTRAINT fk_departments FOREIGN KEY (department_id) " +
"REFERENCES departments(id) " +
" );";
statement.execute(sql);
statement.close();
System.out.println("Tabelle angelegt");
} catch (Exception e) {
System.out.println("Tabelle existiert bereits!");
e.printStackTrace();
}
}
public void createDepartmentsDatabaseTable() {
try {
statement = c.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS " + "departments" +
"( id CHAR (6)," +
"department VARCHAR(255) NOT NULL, " +
"CONSTRAINT pk_departments PRIMARY KEY (id) " +
" );";
statement.execute(sql);
statement.close();
System.out.println("Tabelle angelegt");
} catch (Exception e) {
System.out.println("Tabelle existiert bereits!");
e.printStackTrace();
}
}
public void createDepartment(Department dep) {
if (!(dep.getId().isEmpty() && dep.getDescription().isEmpty())) {
try {
statement = c.createStatement();
String sql = "INSERT INTO departments (id, department) VALUES (" +
"'" + dep.getId() + "', " +
"'" + dep.getDescription() + "'; ";
} catch (Exception e) {
System.err.println("Error: Datenbank ablegen");
e.printStackTrace();
}
}
}
public void createMitarbeiter(Employee emp) {
try {
statement = c.createStatement();
String sql = "INSERT INTO employees (id, vorname, nachname, department_id) VALUES (" +
"'" + emp.getId() + "', " +
"'" + emp.getVorname() + "', " +
"'" + emp.getNachname() + "', " +
"'" + emp.getDepartment().getId() + "'" +
"); ";
String sql2 = "INSERT INTO departments (id, department) VALUES (" +
"'" + emp.getDepartment().getId() + "'," +
"'" + emp.getDepartment().getDescription() + "'" +
");";
statement.execute(sql);
statement.close();
statement = c.createStatement();
statement.execute(sql2);
//this.updateImageForEmployee(emp);
statement.close();
System.out.println("Eintrag angelegt");
} catch (Exception e) {
e.printStackTrace();
}
}
public Employee readMitarbeiter(String id) {
Employee ma = null;
Department dep = null;
try {
String readEmployee = "SELECT * FROM employees AS e, departments as d WHERE e.id = " + id + ";";
statement = c.createStatement();
ResultSet rs = statement.executeQuery(readEmployee);
while (rs.next()) {
dep = new Department(rs.getString("d.id"), rs.getString("d.department"));
ma = new Employee(rs.getString("id"), rs.getString("vorname"), rs.getString("nachname"), dep);
}
rs.close();
statement.close();
return ma;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public List<Employee> readAllMitarbeiter() {
ArrayList<Employee> employeesList = new ArrayList<Employee>();
Department dep = null;
try {
statement = c.createStatement();
String sql = new String("SELECT e.id AS e_id, e.vorname, e.nachname, d.id AS d_id, d.department FROM employees AS e JOIN departments AS d ON e.department_id = d_id;");
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
dep = new Department(rs.getString("d_id"), rs.getString("department"));
employeesList.add(new Employee(rs.getString(1), rs.getString(2), rs.getString(3), dep));
}
rs.close();
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
return employeesList;
}
public void updateMitarbeiter(Employee emp) {
try {
String preparedUpdate = "UPDATE employees SET vorname = ?, nachname = ? WHERE id = ?";
PreparedStatement updateEmployee = c.prepareStatement(preparedUpdate);
updateEmployee.setString(1, emp.getVorname());
updateEmployee.setString(2, emp.getNachname());
updateEmployee.setString(3, emp.getId());
updateEmployee.executeUpdate();
updateImageForEmployee(emp);
} catch (Exception e) {
e.printStackTrace();
}
}
public void updateImageForEmployee(Employee emp) {
try {
c.setAutoCommit(false);
statement = c.createStatement();
String imageUpdate = "UPDATE employees " +
"SET image=? " +
"WHERE id = '" + emp.getId() + "' ";
PreparedStatement stm = c.prepareStatement(imageUpdate);
stm.setBytes(1, emp.getImage());
stm.executeUpdate();
c.commit();
c.setAutoCommit(true);
} catch (Exception e) {
System.err.println("Image save fehlgeschlagen: updateImageForEmployee");
e.printStackTrace();
}
}
public void deleteMitarbeiter(Employee emp) {
try {
statement = c.createStatement();
String sqlDelete = "DELETE FROM employees WHERE id = '" + emp.getId() + "';";
statement.execute(sqlDelete);
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void closeDB() {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}