aswi2020vldc-gitlab/src/main/java/vldc/aswi/database/DatabaseInterface.java @ f30c9e9e

package vldc.aswi.database;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
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;

/**
 * Class for manipulating (selecting, updating, creating, ...) database data.
 */
@Component
public class DatabaseInterface {

    /** JdbcTemplate for executing queries. */
    private JdbcTemplate jdbcTemplate = null;

    /**
     * Constructor.
     * @param jdbcTemplate - JdbcTemplate for executing queries.
     */
    public DatabaseInterface(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Get list of table column names from SQL query.
     * @param sqlQuery - SQL query.
     * @return List of table column names. If null is returned, then it means that SQL query is not valid.
     */
    public List<String> getNameOfColumnsFromQuery(String sqlQuery) {
        try
        {
            // Try extract columns from database using SQL query.
            return jdbcTemplate.query(
                sqlQuery,
                new ResultSetExtractor<List<String>>() {
                    @Override
                    public List<String> extractData(ResultSet resultSet) throws SQLException {
                        List<String> tableColumnNames = 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);
                            tableColumnNames.add(columnName.toLowerCase());
                        }

                        return tableColumnNames;
                    }
                }
            );

        }
        catch (DataAccessException e)
        {
            // If SQL query is not valid, then JdbcTemplate returned DataAccess exception.
            return null;
        }
    }

    /**
     * Validate given SQLQuery.
     * @param sqlQuery - Tested SQL Query.
     * @return true if SQL query is valid, false if not.
     */
    public boolean validateSQLQuery(String sqlQuery) {

        try
        {
            // Try validate SQL query.
            jdbcTemplate.execute("EXPLAIN PLAN FOR " + sqlQuery);
            return true;
        }
        catch (DataAccessException e)
        {
            // If SQL query is not valid, then JdbcTemplate returned DataAccess exception.
            return false;
        }
    }

    /**
     * Execute given SQL Query and return results as list of TableColumn class.
     * @param sqlQuery - SQL query.
     * @return List of results saved as TableColumn.
     */
    public Map<String, TableColumn> executeQueryAndReturnTableColumns(String sqlQuery) {
        return jdbcTemplate.query(
                sqlQuery,
                resultSet -> {
                    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));
                    }

                    // Add values from row to every column.
                    while(resultSet.next()) {
                        for (int i = 1; i <= columnCount; i++) {
                            tableColumnsList.get(i - 1).addValue(resultSet.getString(i));
                        }
                    }

                    Map<String, TableColumn> tableColumnMap = new HashMap<>();

                    // Create map, where key is tablecolumn name.
                    for (TableColumn column : tableColumnsList) {
                        tableColumnMap.put(column.getName(), column);
                    }

                    return tableColumnMap;
                }
        );
    }
}