· 6 years ago · Oct 29, 2019, 11:04 AM
1package LRA.Backend.Utils;
2
3import LRA.Backend.DBObjectTemplates.DBObject;
4import LRA.Backend.DBObjectTemplates.Job;
5import LRA.Backend.DBObjectTemplates.LoadTestResultStatus;
6import LRA.Backend.DBObjectTemplates.Project;
7import LRA.Backend.Misc.CustomTypes.ObjectType;
8import LRA.Backend.Misc.CustomTypes.Setup;
9import LRA.Backend.Misc.CustomTypes.WorkProcess;
10import LRA.Backend.ObjectHub;
11
12import java.io.File;
13import java.sql.*;
14import java.util.*;
15
16import static org.apache.commons.dbutils.DbUtils.closeQuietly;
17
18public class DBUtil {
19
20 private static ObjectHub OBJECTHUB = ObjectHub.getInstance();
21
22 public static void wipeDataOfProject(Project project) {
23
24 executeSQLStatement("WHILE EXISTS (SELECT 1 FROM Jobs) DELETE TOP(10000) FROM Jobs where belongsToProject='"
25 + project.getName() + "'", false);
26 executeSQLStatement(
27 "WHILE EXISTS (SELECT 1 FROM LoadTestResultStatus) DELETE TOP(10000) FROM LoadTestResultStatus where belongsToProject='"
28 + project.getName() + "'",
29 false);
30 executeSQLStatement("WHILE EXISTS (SELECT 1 FROM Tags) DELETE TOP(10000) FROM Tags where belongsToProject='"
31 + project.getName() + "'", false);
32 }
33
34 public static void executeSQLStatement(String sQLExpression, boolean withRawConnection) {
35 Statement dSStatement = null;
36
37 if (withRawConnection) {
38 Setup setup = OBJECTHUB.getSetup();
39 try {
40 String dbURL = "jdbc:sqlserver://" + setup.getPathToDB() + ";user=" + setup.getDbUsername()
41 + ";password=" + setup.getdBPw();
42 Connection con = DriverManager.getConnection(dbURL);
43 dSStatement = con.createStatement();
44 dSStatement.execute(sQLExpression);
45 con.close();
46 dSStatement.close();
47 } catch (SQLException e) {
48 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
49 }
50 } else {
51 dSStatement = OBJECTHUB.getDSStatementOrNull();
52 try {
53
54 boolean results = dSStatement.execute(sQLExpression);
55
56 if (results) {
57 ResultSet rs = OBJECTHUB.getDSStatementOrNull().getResultSet();
58 closeQuietly(dSStatement.getConnection(), dSStatement, rs);
59 }
60
61 } catch (Exception e) {
62 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
63 }
64 }
65 }
66
67 static List<String> getAppropriateKeyList(ObjectType objectType) {
68 List<String> keylist = null;
69 switch (objectType) {
70 case Jobs:
71 break;
72 case LoadTestResultStatus:
73 keylist = LoadTestResultStatus.getKeyList();
74 case Tags:
75 break;
76 case Comment:
77 break;
78 case Project:
79 break; // TODO die Typen ausf�llen
80 }
81 return keylist;
82 }
83
84 public static List<List<Map<String, Object>>> splitListsToWrite(List<Map<String, Object>> inputList){
85 List<List<Map<String, Object>>> seperateLists = new ArrayList<>();
86 int executorMaxThreadsCount = OBJECTHUB.getSetup().getNumberOfThreads();
87 int sizeOfSeperateLists = (inputList.size() / executorMaxThreadsCount) + 1;
88 int seperateListIndex = 0;
89 int inputListIndex = 0;
90
91 for(int i = 0;i < executorMaxThreadsCount; i++){
92 seperateLists.add(new ArrayList<Map<String, Object>>());
93 for(int j = 0;j < sizeOfSeperateLists; j++) {
94 seperateLists.get(seperateListIndex).add(inputList.get(inputListIndex));
95 inputListIndex++;
96 if(inputListIndex == inputList.size()){
97 break;
98 }
99 }
100 seperateListIndex++;
101 }
102 return seperateLists;
103 }
104
105 public static void writeListOfMapToDB(ObjectType objectType, List<Map<String, Object>> inputList) throws Exception {
106 switch (objectType) {
107 case LoadTestResultStatus:
108
109 List<String> keylist = getAppropriateKeyList(objectType);
110 int index = 0;
111 PreparedStatement preparedStatement = OBJECTHUB
112 .getPreparedStatementOrNull(ObjectType.LoadTestResultStatus);
113
114 for (Map<String, Object> inputMap : inputList) {
115 if (WorkProcess.getInstance().getIsActive()) {
116 if (inputMap != null) {
117 int indexOfKey = 1;
118 if (OBJECTHUB.getGuiMainGuiReporter() != null) {
119 try {
120 OBJECTHUB.getGuiMainGuiReporter()
121 .setProjectBarLabelText("Writing to DB (" + inputMap.get("buildNumber")
122 + inputMap.get("relatedFile") + ")");
123 } catch (NullPointerException e) {
124 e.printStackTrace();
125
126 OBJECTHUB.getLogUtil()
127 .errorLog(new String[] {
128 "Not written to DB: preparedStatement:"
129 + preparedStatement.toString(),
130 "indexOfKey:" + indexOfKey }, e);
131 }
132 }
133 for (String key : keylist) {
134 try {
135 determineTypeAndWriteValue(preparedStatement, indexOfKey, inputMap.get(key));
136 } catch (NullPointerException e) {
137 e.printStackTrace();
138
139 OBJECTHUB.getLogUtil()
140 .errorLog(new String[] {
141 "Not written to DB: preparedStatement:"
142 + preparedStatement.toString(),
143 "indexOfKey:" + indexOfKey }, e);
144 }
145 indexOfKey++;
146 }
147
148 try {
149 preparedStatement.addBatch();
150 preparedStatement.clearParameters();
151 index++;
152
153 } catch (SQLException e) {
154 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
155 }
156 if (index > 125 && WorkProcess.getInstance().getIsActive()) {
157 try {
158 preparedStatement.executeBatch();
159 preparedStatement.clearBatch();
160 index = 0;
161 } catch (SQLException e) {
162 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
163 }
164 }
165 if (OBJECTHUB.getGuiMainGuiReporter() != null) {
166 OBJECTHUB.getGuiMainGuiReporter().progressbarAddStep();
167 }
168 }
169 }
170 try {
171 preparedStatement.executeBatch();
172 preparedStatement.clearBatch();
173 } catch (SQLException e) {
174 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
175 }
176 }
177 break;
178 }
179 }
180
181 public static void writeNewMapToDB(ObjectType objectType, Map<String, Object> inputMap) {
182 PreparedStatement preparedStatement = null;
183
184 try (Connection connection = OBJECTHUB.getCon()) {
185
186 List<String> keyList = new ArrayList<>();
187 keyList.addAll(inputMap.keySet());
188 StringBuilder insertKeys = new StringBuilder();
189 StringBuilder insertValues = new StringBuilder();
190
191 int lastIndex = keyList.size() - 1;
192
193 for (int i = 0; i < keyList.size(); i++) {
194 if (inputMap.get(keyList.get(i)) != null) {
195 insertKeys.append(keyList.get(i));
196 insertValues.append("?");
197 if (i != lastIndex) {
198 insertKeys.append(", ");
199
200 insertValues.append(", ");
201 }
202 }
203 }
204
205 OBJECTHUB.getProfiler().getProfileReporter().profile("Statement building");
206 String statementString = "INSERT INTO " + objectType + " (" + insertKeys.toString() + ") VALUES ("
207 + insertValues.toString() + ")";
208 OBJECTHUB.getProfiler().getProfileReporter().profile("Statement String");
209 preparedStatement = connection.prepareStatement(statementString);
210
211 int index = 1;
212
213 for (String key : keyList) {
214 if (inputMap.get(key) != null) {
215 determineTypeAndWriteValue(preparedStatement, index, inputMap.get(key));
216 index++;
217 OBJECTHUB.getProfiler().getProfileReporter().profile("determine and write");
218 }
219 }
220 preparedStatement.execute();
221 OBJECTHUB.getProfiler().getProfileReporter().profile("Statement execute");
222 } catch (Exception e) {
223 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
224 }
225 }
226
227 public static int count(String tableName, String column1OrNull, String column1ParamOrNull, String column2OrNull,
228 String column2ParamOrNull, String column3OrNull, String column3ParamOrNull) {
229 String param1 = "";
230 String param2 = "";
231 String param3 = "";
232 if (column1OrNull != null) {
233 param1 = " where " + column1OrNull + "='" + column1ParamOrNull + "'";
234
235 if (column2OrNull != null) {
236 param2 = " AND " + column2OrNull + "='" + column2ParamOrNull + "'";
237 }
238 if (column3OrNull != null) {
239 param3 = " AND " + column3OrNull + "='" + column3ParamOrNull + "'";
240 }
241 }
242
243 int out = 0;
244
245 try {
246 Statement dSStatement = OBJECTHUB.getDSStatementOrNull();
247 ResultSet results = dSStatement
248 .executeQuery("SELECT COUNT(*) AS total FROM " + tableName + param1 + param2 + param3);
249 results.next();
250 out = results.getInt("total");
251 closeQuietly(dSStatement.getConnection(), dSStatement, results);
252
253 } catch (Exception e) {
254 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
255 }
256 return out;
257 }
258
259 public static List<Job> getJobsWithoutLTRS() {
260 List<Project> allProjects = DBUtil.getProjectListFromDB(null);
261
262 List<Job> allJobs = DBUtil.getJobListFromDB(null);
263
264 List<Job> jobsWithoutLtrs = new ArrayList<>();
265 OBJECTHUB.getLogUtil().log("Collecting Jobs without LoadTest data...");
266 allJobs.forEach(job -> {
267 if (!Job.ltrsPresentForJob(job)) {
268 jobsWithoutLtrs.add(job);
269 }
270 });
271
272 return jobsWithoutLtrs;
273 }
274
275 static void determineTypeAndWriteValue(PreparedStatement preparedStatement, int index, Object object) {
276 if (object != null) {
277 try {
278 if (object.getClass() == Integer.class) {
279
280 preparedStatement.setInt(index, (Integer) object);
281 } else if (object.getClass() == Long.class) {
282
283 preparedStatement.setLong(index, (Long) object);
284 } else if (object.getClass() == Short.class) {
285
286 preparedStatement.setShort(index, (Short) object);
287 } else if (object.getClass() == String.class || object.getClass() == File.class) {
288
289 preparedStatement.setString(index, object.toString());
290 } else if (object.getClass() == Boolean.class) {
291
292 preparedStatement.setBoolean(index, (Boolean) object);
293 } else {
294 throw new RuntimeException("No valid Type found.");
295 }
296
297 } catch (SQLException e) {
298 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
299 }
300 }
301 }
302
303 public static List<DBObject> getDBObjectListFromDB(String SQLStatementOrNull) {
304 List<Map<String, Object>> listOfMap = new ArrayList<>();
305 Statement dSStatement = OBJECTHUB.getDSStatementOrNull();
306
307 try {
308 ResultSet rs = dSStatement.executeQuery(SQLStatementOrNull);
309 Map<String, String> mapLayout = new HashMap<>();
310
311 for (int i = 1; i < rs.getMetaData().getColumnCount() + 1; i++) {
312 mapLayout.put(rs.getMetaData().getColumnLabel(i), rs.getMetaData().getColumnTypeName(i));
313 }
314
315 while (rs.next()) {
316 Map<String, Object> mapForInputList = new HashMap<>();
317 for (String key : mapLayout.keySet()) {
318 if (mapLayout.get(key).equals("bit")) {
319 mapForInputList.put(key, rs.getBoolean(key));
320 }
321 if (mapLayout.get(key).equals("varchar")) {
322 mapForInputList.put(key, rs.getString(key));
323 }
324 if (mapLayout.get(key).equals("nvarchar")) {
325 mapForInputList.put(key, rs.getString(key));
326 }
327 if (mapLayout.get(key).equals("int")) {
328 mapForInputList.put(key, rs.getInt(key));
329 }
330 if (mapLayout.get(key).equals("bigint")) {
331 mapForInputList.put(key, rs.getLong(key));
332 }
333 }
334 listOfMap.add(mapForInputList);
335 }
336 } catch (Exception e) {
337 OBJECTHUB.getLogUtil().errorLog(new String[] { "Couldn't get Objects From DB!" }, e);
338 }
339
340 List<DBObject> objectList = new ArrayList<>();
341 listOfMap.forEach(map -> objectList.add(DBObject.createDBObjectFromListOfMapOrNull(map)));
342
343 return objectList;
344 }
345
346 public static List<Map<String, Object>> getListOfMapFromDB(ObjectType objectType, String SQLStatementOrNull) {
347 List<Map<String, Object>> listOfMap = new ArrayList<>();
348 Statement dSStatement = OBJECTHUB.getDSStatementOrNull();
349 try {
350
351 ResultSet rs = dSStatement.executeQuery(SQLStatementOrNull);
352
353 switch (objectType) {
354 case Project:
355 while (rs.next()) {
356
357 Map<String, Object> map = new HashMap<>();
358 map.put("name", rs.getString("name"));
359 map.put("pathToJenkinsBuildFiles", rs.getString("pathToJenkinsBuildFiles"));
360 listOfMap.add(map);
361 }
362 break;
363 case Jobs:
364 while (rs.next()) {
365 Map<String, Object> putMap = new HashMap<>();
366 putMap.put("number", rs.getInt("number"));
367 putMap.put("comment", rs.getString("comment"));
368 putMap.put("pointInTime", rs.getString("pointInTime"));
369 putMap.put("belongsToProject", rs.getString("belongsToProject"));
370 putMap.put("jobFolderName", rs.getString("jobFolderName"));
371 putMap.put("relatedFile", new File(rs.getString("relatedFile")));
372
373 listOfMap.add(putMap);
374 }
375 break;
376 case LoadTestResultStatus:
377 while (rs.next()) {
378 Map<String, Object> putMap = new HashMap<>();
379 putMap.put("t", rs.getInt("elapsedTime"));
380 putMap.put("it", rs.getInt("idleTime"));
381 putMap.put("lt", rs.getInt("latency"));
382 putMap.put("ct", rs.getInt("connectTimeInMs"));
383 putMap.put("ts", rs.getLong("timeStamp"));
384 putMap.put("s", rs.getBoolean("successFlag"));
385 putMap.put("lb", rs.getString("label"));
386 putMap.put("rc", rs.getInt("responseCode"));
387 putMap.put("rm", rs.getString("responseMessage"));
388 putMap.put("tn", rs.getString("threadName"));
389 putMap.put("dt", rs.getString("dataType"));
390 putMap.put("by", rs.getLong("bytes"));
391 putMap.put("sby", rs.getInt("sentBytes"));
392 putMap.put("ng", rs.getInt("numberActiveThreadsThisGroup"));
393 putMap.put("na", rs.getInt("numberActiveThreadsAllGroups"));
394 putMap.put("relatedFile", rs.getString("relatedFile"));
395 putMap.put("buildNumber", rs.getInt("buildNumber"));
396 putMap.put("belongsToProject", rs.getString("belongsToProject"));
397
398 listOfMap.add(putMap);
399 }
400 break;
401 }
402 closeQuietly(dSStatement.getConnection());
403 } catch (Exception e) {
404 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
405 }
406 return listOfMap;
407 }
408
409 public static List<LoadTestResultStatus> getLTRSListFromDB(String specifyWithSQLStatementOrNull) {
410 List<LoadTestResultStatus> ltrsList = new ArrayList<>();
411 if (specifyWithSQLStatementOrNull == null) {
412 getDBObjectListFromDB("select * from LoadTestResultStatus")
413 .forEach(dbObject -> ltrsList.add((LoadTestResultStatus) dbObject));
414 } else {
415 getDBObjectListFromDB(specifyWithSQLStatementOrNull)
416 .forEach(dbObject -> ltrsList.add((LoadTestResultStatus) dbObject));
417 }
418 return ltrsList;
419 }
420
421 public static List<Project> getProjectListFromDB(String specifyWithSQLStatementOrNull) {
422 List<Project> projectList = new ArrayList<>();
423 if (specifyWithSQLStatementOrNull == null) {
424 getDBObjectListFromDB("select * from Project").forEach(dbObject -> projectList.add((Project) dbObject));
425 } else {
426 getDBObjectListFromDB(specifyWithSQLStatementOrNull)
427 .forEach(dbObject -> projectList.add((Project) dbObject));
428 }
429 return projectList;
430 }
431
432 public static List<Job> getJobListFromDB(String specifyWithSQLStatementOrNull) {
433 List<Job> jobList = new ArrayList<>();
434 if (specifyWithSQLStatementOrNull == null) {
435 getDBObjectListFromDB("select * from Jobs").forEach(dbObject -> jobList.add((Job) dbObject));
436 } else {
437 getDBObjectListFromDB(specifyWithSQLStatementOrNull).forEach(dbObject -> jobList.add((Job) dbObject));
438 }
439 return jobList;
440 }
441
442 public static Set<Job> getJobSetFromDBForProject(Project project) {
443 List<Map<String, Object>> jobListOfMap = new ArrayList<>();
444 try {
445 jobListOfMap = getListOfMapFromDB(ObjectType.Jobs,
446 "select * from Jobs where belongsToProject='" + project.getName() + "'");
447 }catch (Exception e){
448 OBJECTHUB.getLogUtil().errorLog(new String[]{""}, e);
449 }
450
451
452 Set<Job> jobSet = new HashSet<>();
453
454 for (Map<String, Object> map : jobListOfMap) {
455 OBJECTHUB.getExecutor().submit(new Runnable() {
456
457 @Override
458 public void run() {
459 try {
460 jobSet.add(Job.getJobfromMap(map, project));
461 } catch (Exception e) {
462 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
463 }
464 }
465 });
466 }
467
468 ExecutorUtil.blockUntillExecutorServiceIsDone();
469 return jobSet;
470 }
471
472 public static void createCommentInJob(Job job, String value) {
473 try {
474 boolean results = OBJECTHUB.getDSStatementOrNull()
475 .execute("UPDATE Jobs Set comment='" + value + "' Where number='" + job.getNumber() + "';");
476 } catch (Exception e) {
477 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
478 }
479 }
480
481 public static void createNewDb(boolean createDatabase) {
482 if (createDatabase) {
483 executeSQLStatement("create database " + OBJECTHUB.getSetup().getNameOfDB(), true);
484 }
485 String recreationScript = "CREATE TABLE Jobs (\n" + " number int,\n" + " comment varchar(MAX),\n"
486 + " pointInTime varchar(100),\n" + "\tbelongsToProject varchar(50),\n"
487 + " jobFolderName varchar(150),\n" + "relatedFile varchar(150)\n" + ");\n"
488 + "CREATE TABLE LoadTestResultStatus (\n" + " elapsedTime int,\n" + " idleTime int,\n"
489 + " latency int,\n" + "\tconnectTimeInMs int,\n" + " successFlag bit,\n"
490 + " label varchar(MAX),\n" + "\tresponseCode int,\n" + " responseMessage varchar(MAX),\n"
491 + " threadName varchar(50),\n" + "\tdataType varchar(50),\n" + " bytes bigint,\n"
492 + " sentBytes int,\n" + "\tnumberActiveThreadsThisGroup int,\n"
493 + " numberActiveThreadsAllGroups int,\n" + " relatedFile varchar(MAX),\n" + "\tbuildNumber int,\n"
494 + " belongsToProject varchar(50),\n" + "\ttimeStamp bigint\n" + ");\n" + "CREATE TABLE Tags (\n"
495 + " tagString varchar(MAX),\n" + " belongsToJob int ,\n" + "\tbelongsToProject varchar(50),\n"
496 + " );\n" + "CREATE TABLE Project (\n" + " name nvarchar(50),\n"
497 + " \tpathToJenkinsBuildFiles nvarchar(MAX));";
498
499 executeSQLStatement(recreationScript.replace("\t", "").replace(" ", "").replace(" ", "")
500 .replace(" ", "").replace("\n", ""), false);
501 }
502 // GETTER SETTER
503
504 public static String getTagsStringOfJob(Job job) {
505 StringBuilder tagStringBuilder = new StringBuilder();
506 Statement dSStatement = OBJECTHUB.getDSStatementOrNull();
507 try {
508
509 boolean results = dSStatement
510 .execute("select tagString from Tags where belongsToJob like '" + job.getNumber() + "'");
511
512 if (results) {
513 ResultSet rs = dSStatement.getResultSet();
514
515 while (rs.next()) {
516 tagStringBuilder.append(rs.getString("tagString") + ", ");
517 }
518 rs.close();
519 job.setTag(tagStringBuilder.toString());
520 }
521 dSStatement.getConnection().close();
522 dSStatement.close();
523 } catch (Exception e) {
524 OBJECTHUB.getLogUtil().errorLog(new String[] { "" }, e);
525 }
526 return tagStringBuilder.toString();
527 }
528}