1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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;
}
}
|
