Resolve "Implement database operations"

Closes #19 (closed)

development/database/src/main/java/de/uniwue/swp/sshtool/database/DBConnectionImpl.java

 package de.uniwue.swp.sshtool.database;
 
 import com.zaxxer.hikari.HikariDataSource;
+import de.uniwue.swp.sshtool.database.utils.FileStatus;
+import de.uniwue.swp.sshtool.database.utils.SessionStatus;
+import de.uniwue.swp.sshtool.database.utils.TransactionType;
 
 import java.sql.*;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
 import java.util.List;
 
 /**
@@ -31,85 +36,272 @@ public class DBConnectionImpl implements DBConnection {
             ds.setPassword(password);
     }
 
+    //TODO: Null-Werte, Modularisierung( getFilesInTransaction)
+
     /**
-     * neue Transaction in die Datenbank mit jeweiligen Files eintragen, transaction_id ist noch null
      *
      * @param transaction
-     * @return Transaction, die jetzt die ID der Datenbank enthält
+     * @return
+     * @throws SQLException
      */
-
     @Override
     public Transaction insertTransaction(Transaction transaction) throws SQLException {
         Connection connection = dataSource.getConnection();
-        PreparedStatement statement = connection.prepareStatement("insert into transactions(starting_time,finishing_time,transaction_type,username) values(?, ?, ?, ?);");
+        PreparedStatement statement = connection.prepareStatement("insert into transactions(startingTime,transactionType,username) values(?, ?, ?);");
         statement.setTimestamp(1, Timestamp.valueOf(transaction.getStartingTime()));
-        statement.setTimestamp(2, Timestamp.valueOf(transaction.getFinishingTime()));
-        statement.setInt(3, transaction.getTransactionType().toInt());
-        statement.setString(4, transaction.getUser());
+        statement.setInt(2, transaction.getTransactionType().toInt());
+        statement.setString(3, transaction.getUser());
         statement.executeUpdate();
         statement = connection.prepareStatement("select last_insert_id()");
         ResultSet rs = statement.executeQuery();
         rs.next();
         int transactionId = rs.getInt("transactionId");
         for (File file : transaction.getFiles()) {
-            statement = connection.prepareStatement("insert into files (transaction_id,localPath,remotePath,fileStatus) VALUES (?, ?, ?, ?)");
+            statement = connection.prepareStatement("insert into files (transactionId,localPath,remotePath,fileStatus) VALUES (?, ?, ?, ?)");
             statement.setInt(1, transactionId);
             statement.setString(2, file.getLocalPath());
             statement.setString(3, file.getRemotePath());
             statement.setInt(4, file.getFileStatus().toInt());
             statement.executeUpdate();
+            statement = connection.prepareStatement("select last_insert_id()");
+            rs = statement.executeQuery();
+            rs.next();
+            int fileId=rs.getInt("fileId");
+            file.setFileId(fileId);
+            file.setTransactionId(transactionId);
         }
         transaction.setTransactionId(transactionId);
         return transaction;
     }
 
+    /**
+     *
+     * @param session
+     * @return
+     * @throws SQLException
+     */
     @Override
     public Session insertSession(Session session) throws SQLException {
-        return null;
+        Connection connection = dataSource.getConnection();
+        PreparedStatement statement = connection.prepareStatement("insert into sessions(startingTime,username,sshHostname,sshPort,sessionStatus) values(?, ?, ?, ?, ?);");
+        statement.setTimestamp(1, Timestamp.valueOf(session.getStartingTime()));
+        statement.setString(2, session.getUser());
+        statement.setString(3, session.getSshHostname());
+        statement.setInt(4,session.getSshPort());
+        statement.setInt(5,session.getSessionStatus().toInt());
+        statement.executeUpdate();
+        statement = connection.prepareStatement("select last_insert_id();");
+        ResultSet rs = statement.executeQuery();
+        rs.next();
+        int sessionId = rs.getInt("sessionId");
+        session.setSessionId(sessionId);
+        return session;
     }
 
+    /**
+     *
+     * @param session
+     * @param transaction
+     * @throws SQLException
+     */
     @Override
     public void insertTransactionSessionConn(Session session, Transaction transaction) throws SQLException {
-
+        Connection connection = dataSource.getConnection();
+        PreparedStatement statement = connection.prepareStatement("insert into sessionsWithTransactions(sessionId,transactionId) values(?, ?);");
+        statement.setInt(1, session.getSessionId());
+        statement.setInt(2,transaction.getTransactionId());
+        statement.executeUpdate();
     }
 
+    /**
+     *
+     * @param transaction
+     * @throws SQLException
+     */
     @Override
     public void updateTransaction(Transaction transaction) throws SQLException {
-
+        Connection connection = dataSource.getConnection();
+        PreparedStatement statement = connection.prepareStatement("update transactions set startingTime=?,finishingTime=?,transactionType=?,username=? where transactionId=?;");
+        statement.setTimestamp(1, Timestamp.valueOf(transaction.getStartingTime()));
+        statement.setTimestamp(2, Timestamp.valueOf(transaction.getFinishingTime()));
+        statement.setInt(3, transaction.getTransactionType().toInt());
+        statement.setString(4, transaction.getUser());
+        statement.setInt(5, transaction.getTransactionId());
+        statement.executeUpdate();
+        for (File file : transaction.getFiles()) {
+            statement = connection.prepareStatement("update files set localPath=?,remotePath=?,fileStatus=? where fileId=?;");
+            statement.setString(1, file.getLocalPath());
+            statement.setString(2, file.getRemotePath());
+            statement.setInt(3, file.getFileStatus().toInt());
+            statement.setInt(4,file.getFileId());
+            statement.executeUpdate();
+        }
     }
 
+    /**
+     *
+     * @param session
+     * @throws SQLException
+     */
     @Override
     public void updateSession(Session session) throws SQLException {
-
+        Connection connection = dataSource.getConnection();
+        PreparedStatement statement = connection.prepareStatement("update sessions set startingTime=?, finishingTime=?,username=?,sshHostname=?,sshPort=?,sessionStatus=? where sessionId=?;");
+        statement.setTimestamp(1, Timestamp.valueOf(session.getStartingTime()));
+        statement.setTimestamp(1, Timestamp.valueOf(session.getFinishingTime()));
+        statement.setString(2, session.getUser());
+        statement.setString(3, session.getSshHostname());
+        statement.setInt(4,session.getSshPort());
+        statement.setInt(5,session.getSessionStatus().toInt());
+        statement.setInt(6, session.getSessionId());
+        statement.executeUpdate();
     }
 
+    /**
+     *
+     * @return
+     * @throws SQLException
+     */
     @Override
     public List<Session> getSessions() throws SQLException {
-        return null;
+        Connection connection= dataSource.getConnection();
+        Statement statement= connection.createStatement();
+        ResultSet resultSet=statement.executeQuery("select (sessionId,startingTime,finishingTime,username,sshHostname,sshPort,sessionStatus) from sessions;");
+        return resultSetToSession(resultSet);
     }
 
+    /**
+     *
+     * @return
+     * @throws SQLException
+     */
     @Override
     public List<Transaction> getTransactions() throws SQLException {
-        return null;
+        Connection connection= dataSource.getConnection();
+        Statement statement= connection.createStatement();
+        ResultSet resultSet=statement.executeQuery("select (transactionId,startingTime,finishingTime,transactionType,username) from transactions;");
+        return resultSetToTransactions(resultSet);
     }
 
+    /**
+     *
+     * @param user
+     * @return
+     * @throws SQLException
+     */
     @Override
-    public List<File> getFilesInTransaction(int transactionId) throws SQLException {
-        return null;
+    public List<Transaction> getIncompleteTransactions(String user) throws SQLException {
+        Connection connection= dataSource.getConnection();
+        PreparedStatement statement= connection.prepareStatement("select t.* from transactions t where exists(select null from files f where f.transactionId = t.transactionId and f.fileStatus=? and t.username=?);");
+        statement.setInt(1,FileStatus.STARTED.toInt());
+        statement.setString(2,user);
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToTransactions(resultSet);
     }
 
+    /**
+     *
+     * @param session
+     * @return
+     * @throws SQLException
+     */
     @Override
-    public List<Transaction> getIncompleteTransactions(String username) throws SQLException {
-        return null;
+    public List<Transaction> getTransactionsInSession(Session session) throws SQLException {
+        Connection connection= dataSource.getConnection();
+        PreparedStatement statement= connection.prepareStatement("select t.* from transactions as t inner join transactionsWithSessions as ts on t.transactionId = ts.transactionId where ts.sessionId = ?;");
+        statement.setInt(1,session.getSessionId());
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToTransactions(resultSet);
     }
 
+    /**
+     *
+     * @param transaction
+     * @return
+     * @throws SQLException
+     */
     @Override
-    public List<Transaction> getTransactionsInSession(int sessionId) throws SQLException {
-        return null;
+    public List<Session> getSessionsInTransaction(Transaction transaction) throws SQLException{
+        Connection connection= dataSource.getConnection();
+        PreparedStatement statement= connection.prepareStatement("select s.* from sessions as s inner join transactionsWithSessions as ts on s.sessionId = ts.sessionId where ts.transactionId = ?;");
+        statement.setInt(1,transaction.getTransactionId());
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToSession(resultSet);
     }
 
-    @Override
-    public List<Session> getSessionsInTransaction(int transactionId) {
-        return null;
+    /**
+     * helper method
+     * @param transactionId
+     * @return
+     * @throws SQLException
+     */
+    public List<File> getFilesInTransaction(int transactionId) throws SQLException{
+        Connection connection = dataSource.getConnection();
+        PreparedStatement statement=connection.prepareStatement("select (fileId,localPath,remotePath, fileStatus) from files where transactionId=?;");
+        statement.setInt(1,transactionId);
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToFiles(resultSet);
+    }
+
+    /**
+     * helper method
+     * @param resultSet
+     * @return
+     * @throws SQLException
+     */
+    public List<File> resultSetToFiles(ResultSet resultSet) throws SQLException{
+        ArrayList<File> files = new ArrayList<>();
+        while (resultSet.next()){
+            int fileId= resultSet.getInt("fileId");
+            String localPath= resultSet.getString("localPath");
+            String remotePath= resultSet.getString("remotePath");
+            FileStatus fileStatus= FileStatus.values()[resultSet.getInt("fileStatus")];
+            int transactionId = resultSet.getInt("transactionId");
+            files.add(new File(fileId,localPath,remotePath,fileStatus,transactionId));
+        }
+        return files;
     }
+
+    /**
+     * helper method
+     * @param resultSet
+     * @return
+     * @throws SQLException
+     */
+    public List<Transaction> resultSetToTransactions(ResultSet resultSet) throws SQLException{
+        ArrayList<Transaction> transactions = new ArrayList<>();
+        while (resultSet.next()){
+            int transactionId= resultSet.getInt("transactionId");
+            LocalDateTime startingTime= resultSet.getTimestamp("startingTime").toLocalDateTime();
+            LocalDateTime finishingTime= resultSet.getTimestamp("finishingTime").toLocalDateTime();
+            TransactionType transactionType= TransactionType.values()[resultSet.getInt("transactionType")];
+            String username= resultSet.getString("username");
+            List<File> files = this.getFilesInTransaction(transactionId);
+            transactions.add(new Transaction(transactionId,startingTime,finishingTime,transactionType,username,files));
+        }
+        return transactions;
+    }
+
+    /**
+     * helper method
+     * @param resultSet
+     * @return
+     * @throws SQLException
+     */
+    public List<Session> resultSetToSession(ResultSet resultSet) throws SQLException{
+        ArrayList<Session> sessions = new ArrayList<>();
+        while (resultSet.next()){
+            int sessionId= resultSet.getInt("sessionId");
+            LocalDateTime startingTime= resultSet.getTimestamp("startingTime").toLocalDateTime();
+            LocalDateTime finishingTime= resultSet.getTimestamp("finishingTime").toLocalDateTime();
+            String username= resultSet.getString("username");
+            String sshHostname = resultSet.getString("sshHostname");
+            int sshPort = resultSet.getInt("sshPort");
+            SessionStatus sessionStatus = SessionStatus.values()[resultSet.getInt("sessionStatus")];
+            sessions.add(new Session(sessionId,startingTime,finishingTime,username,sshHostname,sshPort,sessionStatus));
+        }
+        return sessions;
+    }
+
 }
+