Revize 417c1106

re #7779 Convert TableColumn -> ContingencyTableRow and send it to frontend. SQL query is now executed in specific class DatabaseInterface via SqlQueryManager.

src/main/java/vldc/aswi/database/DatabaseInterface.java

@@ +1 @@
+package vldc.aswi.database;
+
+import org.springframework.context.annotation.Bean;
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.ResultSetExtractor;
+import org.springframework.jdbc.core.RowMapper;
+import org.springframework.stereotype.Component;
+import vldc.aswi.model.table.TableColumn;
+
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+@Component
+public class DatabaseInterface {
+
+    private static JdbcTemplate jdbcTemplate = null;
+
+    public DatabaseInterface(JdbcTemplate jdbcTemplate) {
+        this.jdbcTemplate = jdbcTemplate;
+    }
+
+    public static Map<String, TableColumn> executeQueryAndReturnTableColumns(String sqlQuery) {
+        return jdbcTemplate.query(
+                sqlQuery,
+                new ResultSetExtractor<Map<String, TableColumn>>() {
+                    @Override
+                    public Map<String, TableColumn> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
+                        List<TableColumn> tableColumnsList = new ArrayList<>();
+
+                        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+                        int columnCount = resultSetMetaData.getColumnCount();
+
+                        // The column count starts from 1.
+                        for (int i = 1; i <= columnCount; i++ ) {
+                            String columnName = resultSetMetaData.getColumnName(i);
+                            tableColumnsList.add(new TableColumn(columnName));
+                        }
+
+                        while(resultSet.next()) {
+                            for (int i = 1; i <= columnCount; i++) {
+                                tableColumnsList.get(i - 1).addValue(resultSet.getString(i));
+                            }
+                        }
+
+                        Map<String, TableColumn> tableColumnMap = new HashMap<>();
+
+                        for (TableColumn column : tableColumnsList) {
+                            tableColumnMap.put(column.getName(), column);
+                        }
+
+                        return tableColumnMap;
+                    }
+                }
+        );
+    }
+}

src/main/java/vldc/aswi/service/SqlQueryManager.java

@@ +1 @@
+package vldc.aswi.service;
+
+import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
+
+import java.util.List;
+
+public interface SqlQueryManager {
+
+    List<ContingencyTableRow> getContingencyTableRow(String sql);
+}

src/main/java/vldc/aswi/service/SqlQueryManagerImpl.java

@@ +1 @@
+package vldc.aswi.service;
+
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+import vldc.aswi.database.DatabaseInterface;
+import vldc.aswi.model.table.TableColumn;
+import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
+import vldc.aswi.utils.Converter;
+
+import javax.sql.DataSource;
+import java.util.List;
+import java.util.Map;
+
+@Service
+@Slf4j
+public class SqlQueryManagerImpl implements SqlQueryManager{
+
+    @Autowired
+    private DataSource dataSource;
+
+    @Autowired
+    private DatabaseInterface databaseInterface;
+
+    @Override
+    public List<ContingencyTableRow> getContingencyTableRow(String sqlQuery) {
+        Map<String, TableColumn> tableColumns = databaseInterface.executeQueryAndReturnTableColumns(sqlQuery);
+        return Converter.convertTableColumnsToContingencyTableRows(tableColumns);
+    }
+}

src/main/java/vldc/aswi/utils/Converter.java

@@ +1 @@
+package vldc.aswi.utils;
+
+import vldc.aswi.model.table.TableColumn;
+import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
+import vldc.aswi.model.table.contingencyTable.ContingencyTableRowCell;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+public class Converter {
+
+    public static List<ContingencyTableRow> convertTableColumnsToContingencyTableRows(Map<String, TableColumn> tableColumns) {
+        List<ContingencyTableRow> contingencyTableRows = new ArrayList<>();
+
+        ContingencyTableRow contingencyTableRowHeader = new ContingencyTableRow(true, 0);
+        for (String columnName : tableColumns.keySet()) {
+            contingencyTableRowHeader.addTableRowCell(new ContingencyTableRowCell(columnName, 0));
+        }
+        contingencyTableRows.add(contingencyTableRowHeader);
+
+        boolean listExpanded = false;
+
+        for (TableColumn tableColumn : tableColumns.values()) {
+            List<String> values = tableColumn.getValues();
+
+            if (!listExpanded) {
+                for (int j = 0; j < values.size(); j++) {
+                    ContingencyTableRow contingencyTableRow = new ContingencyTableRow(false, 0);
+                    contingencyTableRows.add(contingencyTableRow);
+                }
+                listExpanded = true;
+            }
+
+            for (int j = 0; j < values.size(); j++) {
+                ContingencyTableRowCell contingencyTableRowCell = new ContingencyTableRowCell(values.get(j), 0);
+                contingencyTableRows.get(j + 1).addTableRowCell(contingencyTableRowCell);
+            }
+        }
+
+        return contingencyTableRows;
+    }
+}

src/main/java/vldc/aswi/web/controller/IndexController.java

@@ -1 +1 @@
 package vldc.aswi.web.controller;
 
 import org.springframework.beans.factory.annotation.Autowired;
-import org.springframework.jdbc.core.JdbcTemplate;
-import org.springframework.jdbc.core.RowMapper;
 import org.springframework.stereotype.Controller;
 import org.springframework.ui.ModelMap;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.servlet.ModelAndView;
+import vldc.aswi.database.DatabaseInterface;
+import vldc.aswi.model.table.TableColumn;
+import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
+import vldc.aswi.model.table.contingencyTable.ContingencyTableRowCell;
+import vldc.aswi.service.SqlQueryManager;
+import vldc.aswi.utils.Converter;
 
 import javax.sql.DataSource;
-import java.sql.*;
 import java.util.ArrayList;
 import java.util.List;
+import java.util.Map;
 
 @Controller
 public class IndexController {
 
 	@Autowired
-	protected DataSource dataSource;
+	private SqlQueryManager sqlQueryManager;
 
 	@GetMapping("/")
 	public ModelAndView index() throws Exception {
@@ -25 +29 @@
 
 		ModelMap modelMap = modelAndView.getModelMap();
 
-		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
-		String sql = "SELECT SP.fakulta_SP as fakulta_studia, " +
+		String sqlQuery = "SELECT SP.fakulta_SP as fakulta_studia, " +
 				"SP.kodSP_i as program_studia, " +
 				"fn_meaning2(SP.typ,'TYP_OBORU') as typ_studia, " +
 				"fn_meaning2(SP.forma,'FORMA_OBORU_NEW') as forma_studia, " +
 				"decode(SP.vykazovan,'A',ST.vykazovan,'N') as vykazovan_na_SIMS, " +
 				"ST.os_cislo as studium, fn_meaning2(ST.stav,'STAV_STUDENTA') as stav_studia, " +
-				"OS.prijmeni || ' ' || OS.jmeno as osoba, trunc((sysdate - OS.datum_naroz) / 365) as vek, " +
+				"OS.prijmeni || ' ' || OS.jmeno as osoba, " +
+				"trunc((sysdate - OS.datum_naroz) / 365) as vek, " +
 				"1 as pocet " +
 				"FROM studijni_programy SP, studenti ST, osoby OS " +
-				"WHERE SP.stpridno = ST.stpridno AND ST.osobidno = OS.osobidno AND ROWNUM <= 20";
-
-		String resultHeader = "fakulta_studia;typ_studia;forma_studia;vykazovan_na_SIMS;stav_studia;vek;pocet";
-		List<String> sqlResults = new ArrayList<String>();
-		sqlResults.add(resultHeader);
-		 sqlResults.addAll(jdbcTemplate.query(
-				sql,
-				new RowMapper<String>() {
-					public String mapRow(ResultSet rs, int rowNum) throws SQLException {
-						String str =
-								rs.getString("fakulta_studia") +
-								";" +
-								rs.getString("typ_studia") +
-								";" +
-								rs.getString("forma_studia") +
-								";" +
-								rs.getString("vykazovan_na_SIMS") +
-								";" +
-								rs.getString("stav_studia") +
-								";" +
-								rs.getString("vek") +
-								";" +
-								rs.getString("pocet")
-								;
-						return str;
-					}
-				})
-		 );
+				"WHERE SP.stpridno = ST.stpridno AND ST.osobidno = OS.osobidno AND ROWNUM <= 500";
 
 		modelMap.addAttribute("message", "Hello World!");
-		modelMap.addAttribute("sqlResults", sqlResults);
+		modelMap.addAttribute("contingencyTableRows", sqlQueryManager.getContingencyTableRow(sqlQuery));
 
 		return modelAndView;
 	}

src/main/webapp/WEB-INF/templates/index.html

@@ -10 +10 @@
 	<body>
 		<nav th:replace="fragments/headers :: nav"></nav>
 		<div th:text="${message}"></div>
-		<p th:each="result : ${sqlResults}" th:text="${result}"><p/>
+		<table>
+			<tr th:each="contingencyTableRow : ${contingencyTableRows}">
+				<div th:if="${contingencyTableRow.isHeader()}">
+					<th th:each="contingencyTableRowCell : ${contingencyTableRow.getCells()}">
+						<span th:text="${contingencyTableRowCell.getValue()}"></span>
+					</th>
+				</div>
+				<div th:unless="${contingencyTableRow.isHeader()}">
+					<td th:each="contingencyTableRowCell : ${contingencyTableRow.getCells()}">
+						<span th:text="${contingencyTableRowCell.getValue()}"></span>
+					</td>
+				</div>
+			</tr>
+		</table>
 	</body>
 </html>