Resolve "Implement database operations"

Closes #19 (closed)

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

@@ -56,7 +56,6 @@ public class DBConnectionImpl implements DBConnection {
         ResultSet rs = statement.executeQuery();
         rs.next();
         int transactionId = rs.getInt("transactionId");
-        List<File> files=transaction.getFiles();
         for (File file : transaction.getFiles()) {
             statement = connection.prepareStatement("insert into files (transactionId,localPath,remotePath,fileStatus) VALUES (?, ?, ?, ?)");
             statement.setInt(1, transactionId);
@@ -70,10 +69,8 @@ public class DBConnectionImpl implements DBConnection {
             int fileId=rs.getInt("fileId");
             file.setFileId(fileId);
             file.setTransactionId(transactionId);
-            files.add(file);
         }
         transaction.setTransactionId(transactionId);
-        transaction.setFiles(files);
         return transaction;
     }
 
@@ -169,19 +166,8 @@ public class DBConnectionImpl implements DBConnection {
     public List<Session> getSessions() throws SQLException {
         Connection connection= dataSource.getConnection();
         Statement statement= connection.createStatement();
-        ResultSet rs=statement.executeQuery("select (sessionId,startingTime,finishingTime,username,sshHostname,sshPort,sessionStatus) from sessions;");
-        ArrayList<Session> sessions = new ArrayList<>();
-        while (rs.next()){
-            int sessionId= rs.getInt("sessionId");
-            LocalDateTime startingTime= rs.getTimestamp("startingTime").toLocalDateTime();
-            LocalDateTime finishingTime= rs.getTimestamp("finishingTime").toLocalDateTime();
-            String username= rs.getString("username");
-            String sshHostname= rs.getString("sshHostname");
-            int sshPort = rs.getInt("sshPort");
-            SessionStatus sessionStatus = SessionStatus.values()[rs.getInt("sessionStatus")];
-            sessions.add(new Session(sessionId,startingTime,finishingTime,username,sshHostname,sshPort,sessionStatus));
-        }
-        return sessions;
+        ResultSet resultSet=statement.executeQuery("select (sessionId,startingTime,finishingTime,username,sshHostname,sshPort,sessionStatus) from sessions;");
+        return resultSetToSession(resultSet);
     }
 
     /**
@@ -193,28 +179,8 @@ public class DBConnectionImpl implements DBConnection {
     public List<Transaction> getTransactions() throws SQLException {
         Connection connection= dataSource.getConnection();
         Statement statement= connection.createStatement();
-        ResultSet rs=statement.executeQuery("select (transactionId,startingTime,finishingTime,transactionType,username) from transactions;");
-        ArrayList<Transaction> transactions = new ArrayList<>();
-        while (rs.next()){
-            int transactionId= rs.getInt("transactionId");
-            LocalDateTime startingTime= rs.getTimestamp("startingTime").toLocalDateTime();
-            LocalDateTime finishingTime= rs.getTimestamp("finishingTime").toLocalDateTime();
-            TransactionType transactionType= TransactionType.values()[rs.getInt("transactionType")];
-            String username= rs.getString("username");
-            PreparedStatement fileStatement=connection.prepareStatement("select (fileId,localPath,remotePath, fileStatus) from files where transactionId=?;");
-            fileStatement.setInt(1,transactionId);
-            ArrayList<File> files = new ArrayList<>();
-            rs=fileStatement.executeQuery();
-            while (rs.next()){
-                int fileId= rs.getInt("fileId");
-                String localPath= rs.getString("localPath");
-                String remotePath= rs.getString("remotePath");
-                FileStatus fileStatus= FileStatus.values()[rs.getInt("fileStatus")];
-                files.add(new File(fileId,localPath,remotePath,fileStatus,transactionId));
-            }
-            transactions.add(new Transaction(transactionId,startingTime,finishingTime,transactionType,username,files));
-        }
-        return transactions;
+        ResultSet resultSet=statement.executeQuery("select (transactionId,startingTime,finishingTime,transactionType,username) from transactions;");
+        return resultSetToTransactions(resultSet);
     }
 
     /**
@@ -229,28 +195,8 @@ public class DBConnectionImpl implements DBConnection {
         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 rs=statement.executeQuery();
-        ArrayList<Transaction> transactions = new ArrayList<>();
-        while (rs.next()){
-            int transactionId= rs.getInt("transactionId");
-            LocalDateTime startingTime= rs.getTimestamp("startingTime").toLocalDateTime();
-            LocalDateTime finishingTime= rs.getTimestamp("finishingTime").toLocalDateTime();
-            TransactionType transactionType= TransactionType.values()[rs.getInt("transactionType")];
-            String username= rs.getString("username");
-            PreparedStatement fileStatement=connection.prepareStatement("select (fileId,localPath,remotePath, fileStatus) from files where transactionId=?;");
-            fileStatement.setInt(1,transactionId);
-            ArrayList<File> files = new ArrayList<>();
-            rs=fileStatement.executeQuery();
-            while (rs.next()){
-                int fileId= rs.getInt("fileId");
-                String localPath= rs.getString("localPath");
-                String remotePath= rs.getString("remotePath");
-                FileStatus fileStatus= FileStatus.values()[rs.getInt("fileStatus")];
-                files.add(new File(fileId,localPath,remotePath,fileStatus,transactionId));
-            }
-            transactions.add(new Transaction(transactionId,startingTime,finishingTime,transactionType,username,files));
-        }
-        return transactions;
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToTransactions(resultSet);
     }
 
     /**
@@ -264,28 +210,8 @@ public class DBConnectionImpl implements DBConnection {
         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 rs=statement.executeQuery();
-        ArrayList<Transaction> transactions = new ArrayList<>();
-        while (rs.next()){
-            int transactionId= rs.getInt("transactionId");
-            LocalDateTime startingTime= rs.getTimestamp("startingTime").toLocalDateTime();
-            LocalDateTime finishingTime= rs.getTimestamp("finishingTime").toLocalDateTime();
-            TransactionType transactionType= TransactionType.values()[rs.getInt("transactionType")];
-            String username= rs.getString("username");
-            PreparedStatement fileStatement=connection.prepareStatement("select (fileId,localPath,remotePath, fileStatus) from files where transactionId=?;");
-            fileStatement.setInt(1,transactionId);
-            ArrayList<File> files = new ArrayList<>();
-            rs=fileStatement.executeQuery();
-            while (rs.next()){
-                int fileId= rs.getInt("fileId");
-                String localPath= rs.getString("localPath");
-                String remotePath= rs.getString("remotePath");
-                FileStatus fileStatus= FileStatus.values()[rs.getInt("fileStatus")];
-                files.add(new File(fileId,localPath,remotePath,fileStatus,transactionId));
-            }
-            transactions.add(new Transaction(transactionId,startingTime,finishingTime,transactionType,username,files));
-        }
-        return transactions;
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToTransactions(resultSet);
     }
 
     /**
@@ -299,19 +225,83 @@ public class DBConnectionImpl implements DBConnection {
         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 rs=statement.executeQuery();
+        ResultSet resultSet=statement.executeQuery();
+        return resultSetToSession(resultSet);
+    }
+
+    /**
+     * 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 (rs.next()){
-            int sessionId= rs.getInt("sessionId");
-            LocalDateTime startingTime= rs.getTimestamp("startingTime").toLocalDateTime();
-            LocalDateTime finishingTime= rs.getTimestamp("finishingTime").toLocalDateTime();
-            String username= rs.getString("username");
-            String sshHostname = rs.getString("sshHostname");
-            int sshPort = rs.getInt("sshPort");
-            SessionStatus sessionStatus = SessionStatus.values()[rs.getInt("sessionStatus")];
+        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;
     }
+
 }