1 |
417c1106
|
Vojtech Danisik
|
package vldc.aswi.database;
|
2 |
|
|
|
3 |
|
|
import org.springframework.dao.DataAccessException;
|
4 |
|
|
import org.springframework.jdbc.core.JdbcTemplate;
|
5 |
|
|
import org.springframework.jdbc.core.ResultSetExtractor;
|
6 |
|
|
import org.springframework.stereotype.Component;
|
7 |
|
|
import vldc.aswi.model.table.TableColumn;
|
8 |
|
|
|
9 |
|
|
import java.sql.ResultSet;
|
10 |
|
|
import java.sql.ResultSetMetaData;
|
11 |
|
|
import java.sql.SQLException;
|
12 |
|
|
import java.util.ArrayList;
|
13 |
|
|
import java.util.HashMap;
|
14 |
|
|
import java.util.List;
|
15 |
|
|
import java.util.Map;
|
16 |
|
|
|
17 |
ba8b306a
|
Vojtěch Danišík
|
/**
|
18 |
|
|
* Class for manipulating (selecting, updating, creating, ...) database data.
|
19 |
|
|
*/
|
20 |
417c1106
|
Vojtech Danisik
|
@Component
|
21 |
|
|
public class DatabaseInterface {
|
22 |
|
|
|
23 |
ba8b306a
|
Vojtěch Danišík
|
/** JdbcTemplate for executing queries. */
|
24 |
2868bb9a
|
Vojtěch Danišík
|
private JdbcTemplate jdbcTemplate = null;
|
25 |
417c1106
|
Vojtech Danisik
|
|
26 |
ba8b306a
|
Vojtěch Danišík
|
/**
|
27 |
|
|
* Constructor.
|
28 |
|
|
* @param jdbcTemplate - JdbcTemplate for executing queries.
|
29 |
|
|
*/
|
30 |
417c1106
|
Vojtech Danisik
|
public DatabaseInterface(JdbcTemplate jdbcTemplate) {
|
31 |
|
|
this.jdbcTemplate = jdbcTemplate;
|
32 |
|
|
}
|
33 |
|
|
|
34 |
7638be72
|
Vojtěch Danišík
|
/**
|
35 |
|
|
* Get list of table column names from SQL query.
|
36 |
|
|
* @param sqlQuery - SQL query.
|
37 |
|
|
* @return List of table column names. If null is returned, then it means that SQL query is not valid.
|
38 |
|
|
*/
|
39 |
|
|
public List<String> getNameOfColumnsFromQuery(String sqlQuery) {
|
40 |
|
|
try
|
41 |
|
|
{
|
42 |
|
|
// Try extract columns from database using SQL query.
|
43 |
|
|
return jdbcTemplate.query(
|
44 |
|
|
sqlQuery,
|
45 |
|
|
new ResultSetExtractor<List<String>>() {
|
46 |
|
|
@Override
|
47 |
|
|
public List<String> extractData(ResultSet resultSet) throws SQLException {
|
48 |
|
|
List<String> tableColumnNames = new ArrayList<>();
|
49 |
|
|
|
50 |
|
|
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
|
51 |
|
|
int columnCount = resultSetMetaData.getColumnCount();
|
52 |
|
|
|
53 |
|
|
// The column count starts from 1.
|
54 |
|
|
for (int i = 1; i <= columnCount; i++ ) {
|
55 |
|
|
String columnName = resultSetMetaData.getColumnName(i);
|
56 |
ae1ff627
|
Vojtěch Danišík
|
tableColumnNames.add(columnName.toLowerCase());
|
57 |
7638be72
|
Vojtěch Danišík
|
}
|
58 |
|
|
|
59 |
|
|
return tableColumnNames;
|
60 |
|
|
}
|
61 |
|
|
}
|
62 |
|
|
);
|
63 |
|
|
|
64 |
|
|
}
|
65 |
|
|
catch (DataAccessException e)
|
66 |
|
|
{
|
67 |
|
|
// If SQL query is not valid, then JdbcTemplate returned DataAccess exception.
|
68 |
|
|
return null;
|
69 |
|
|
}
|
70 |
|
|
}
|
71 |
|
|
|
72 |
ba8b306a
|
Vojtěch Danišík
|
/**
|
73 |
|
|
* Validate given SQLQuery.
|
74 |
|
|
* @param sqlQuery - Tested SQL Query.
|
75 |
|
|
* @return true if SQL query is valid, false if not.
|
76 |
|
|
*/
|
77 |
|
|
public boolean validateSQLQuery(String sqlQuery) {
|
78 |
7638be72
|
Vojtěch Danišík
|
|
79 |
ba8b306a
|
Vojtěch Danišík
|
try
|
80 |
|
|
{
|
81 |
7638be72
|
Vojtěch Danišík
|
// Try validate SQL query.
|
82 |
ba8b306a
|
Vojtěch Danišík
|
jdbcTemplate.execute("EXPLAIN PLAN FOR " + sqlQuery);
|
83 |
|
|
return true;
|
84 |
|
|
}
|
85 |
|
|
catch (DataAccessException e)
|
86 |
|
|
{
|
87 |
7638be72
|
Vojtěch Danišík
|
// If SQL query is not valid, then JdbcTemplate returned DataAccess exception.
|
88 |
ba8b306a
|
Vojtěch Danišík
|
return false;
|
89 |
|
|
}
|
90 |
|
|
}
|
91 |
|
|
|
92 |
|
|
/**
|
93 |
|
|
* Execute given SQL Query and return results as list of TableColumn class.
|
94 |
|
|
* @param sqlQuery - SQL query.
|
95 |
|
|
* @return List of results saved as TableColumn.
|
96 |
|
|
*/
|
97 |
2868bb9a
|
Vojtěch Danišík
|
public Map<String, TableColumn> executeQueryAndReturnTableColumns(String sqlQuery) {
|
98 |
417c1106
|
Vojtech Danisik
|
return jdbcTemplate.query(
|
99 |
|
|
sqlQuery,
|
100 |
|
|
new ResultSetExtractor<Map<String, TableColumn>>() {
|
101 |
|
|
@Override
|
102 |
|
|
public Map<String, TableColumn> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
|
103 |
|
|
List<TableColumn> tableColumnsList = new ArrayList<>();
|
104 |
|
|
|
105 |
|
|
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
|
106 |
|
|
int columnCount = resultSetMetaData.getColumnCount();
|
107 |
|
|
|
108 |
|
|
// The column count starts from 1.
|
109 |
|
|
for (int i = 1; i <= columnCount; i++ ) {
|
110 |
|
|
String columnName = resultSetMetaData.getColumnName(i);
|
111 |
|
|
tableColumnsList.add(new TableColumn(columnName));
|
112 |
|
|
}
|
113 |
|
|
|
114 |
7638be72
|
Vojtěch Danišík
|
// Add values from row to every column.
|
115 |
417c1106
|
Vojtech Danisik
|
while(resultSet.next()) {
|
116 |
|
|
for (int i = 1; i <= columnCount; i++) {
|
117 |
|
|
tableColumnsList.get(i - 1).addValue(resultSet.getString(i));
|
118 |
|
|
}
|
119 |
|
|
}
|
120 |
|
|
|
121 |
|
|
Map<String, TableColumn> tableColumnMap = new HashMap<>();
|
122 |
|
|
|
123 |
7638be72
|
Vojtěch Danišík
|
// Create map, where key is tablecolumn name.
|
124 |
417c1106
|
Vojtech Danisik
|
for (TableColumn column : tableColumnsList) {
|
125 |
|
|
tableColumnMap.put(column.getName(), column);
|
126 |
|
|
}
|
127 |
|
|
|
128 |
|
|
return tableColumnMap;
|
129 |
|
|
}
|
130 |
|
|
}
|
131 |
|
|
);
|
132 |
|
|
}
|
133 |
|
|
}
|