Solve 1st test case

1 files changed, 88 insertions, 0 deletions

diff --git a/src/main/java/com/mavlushechka/a1qa/utils/Database.java b/src/main/java/com/mavlushechka/a1qa/utils/Database.java
new file mode 100644
index 0000000..1926618
--- /dev/null
+++ b/src/main/java/com/mavlushechka/a1qa/utils/Database.java
@@ -0,0 +1,88 @@
+package com.mavlushechka.a1qa.utils;
+
+import com.mavlushechka.a1qa.models.User;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.time.LocalDate;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+public class Database {
+
+    private static final String name = JsonParser.parseData("config", "database.name");
+    private static final Connection connection;
+    private static final User user = new User(JsonParser.parseData("config", "database.user.name"),
+            JsonParser.parseData("config", "database.user.password"));
+
+
+    static {
+        try {
+            Class.forName(JsonParser.parseData("config", "database.driver"));
+            connection = DriverManager.getConnection(JsonParser.parseData("config", "database.url") + "/" + name, user.name(), user.password());
+        } catch (SQLException | ClassNotFoundException e) {
+            throw new RuntimeException(e);
+        }
+    }
+
+
+    private Database() {
+    }
+
+    public static List<String> getTests() {
+        return executeQuery("SELECT project.name, test.name, TIMESTAMPDIFF(MICROSECOND, test.start_time, test.end_time) as min_working_time"
+                + " FROM project INNER JOIN test ON project.id = test.project_id ORDER BY project.name, test.name;",
+                Map.of("project.name", String.class,"test.name", String.class,"min_working_time", Long.class));
+    }
+
+    public static List<String> getTestsCount() {
+        return executeQuery("SELECT project.name, (SELECT COUNT(*) FROM test WHERE test.project_id = project.id) AS `tests_count` FROM project;",
+                Map.of("project.name", String.class, "tests_count", Long.class));
+    }
+
+    public static List<String> getTestsByLowerDate(LocalDate localDate) {
+        return executeQuery("SELECT project.name, test.name, test.start_time FROM project JOIN test ON project.id = test.project_id"
+                + " WHERE test.start_time >= '%s' ORDER BY project.name, test.name;".formatted(localDate),
+                Map.of("project.name", String.class, "test.name", String.class, "start_time", LocalDate.class));
+    }
+
+    public static List<String> getBrowserTestsCount() {
+        return executeQuery("SELECT test.browser, COUNT(*) as count FROM test WHERE test.browser = 'firefox'"
+                + " UNION"
+                + " SELECT test.browser, COUNT(*) as count FROM test WHERE test.browser = 'chrome';",
+                Map.of("browser", String.class, "count", Long.class));
+    }
+
+    private static List<String> executeQuery(String sql, Map<String, Class<?>> expectedData) {
+        ArrayList<String> foundData = new ArrayList<>();
+
+        try (PreparedStatement preparedStatement = connection.prepareStatement(sql);
+             ResultSet resultSet = preparedStatement.executeQuery()) {
+            while (resultSet.next()) {
+                StringBuilder dataStringRepresentationBuilder = new StringBuilder();
+
+                dataStringRepresentationBuilder.append("{");
+                for (String expectedDataKey : expectedData.keySet()) {
+                    dataStringRepresentationBuilder
+                            .append(expectedDataKey)
+                            .append("=")
+                            .append(resultSet.getObject(expectedDataKey, expectedData.get(expectedDataKey)))
+                            .append(",");
+                }
+                dataStringRepresentationBuilder.deleteCharAt(dataStringRepresentationBuilder.toString().length() - 1);
+                dataStringRepresentationBuilder.append("}");
+
+                foundData.add(dataStringRepresentationBuilder.toString());
+            }
+        } catch (SQLException sqlException) {
+            sqlException.printStackTrace();
+        }
+
+        return foundData;
+    }
+
+}