ResultSetController.java

package com.zenquery.api;

import au.com.bytecode.opencsv.CSVWriter;
import com.hp.gagawa.java.elements.Strong;
import com.hp.gagawa.java.elements.Table;
import com.hp.gagawa.java.elements.Td;
import com.hp.gagawa.java.elements.Tr;
import com.hp.gagawa.java.elements.Th;
import com.hp.gagawa.java.elements.Div;
import com.hp.gagawa.java.elements.A;
import com.thoughtworks.xstream.XStream;
import com.zenquery.model.DatabaseConnection;
import com.zenquery.model.Query;
import com.zenquery.model.dao.DatabaseConnectionDAO;
import com.zenquery.model.dao.QueryDAO;
import com.zenquery.util.BasicDataSourceFactory;
import com.zenquery.util.MapEntryConverter;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.io.StringWriter;
import java.util.*;

@Controller
@RequestMapping("/api/v1/resultSetForQuery")
public class ResultSetController {
    private static final Logger logger = Logger.getLogger(ResultSetController.class);

    @Autowired
    private DatabaseConnectionDAO databaseConnectionDAO;

    @Autowired
    private QueryDAO queryDAO;

    @Autowired
    private BasicDataSourceFactory dataSourceFactory;

    @RequestMapping(
            value = "/{id}",
            method = RequestMethod.GET,
            produces = { "application/json; charset=utf-8" })
    public @ResponseBody
    List<Map<String, Object>> currentQuery(
            @PathVariable Integer id
    ) {
        List<Map<String, Object>> rows = getRows(id, null, null);

        return rows;
    }

    @RequestMapping(
            value = "/{id}/{variables}",
            method = RequestMethod.GET,
            produces = { "application/json; charset=utf-8" })
    public @ResponseBody
    List<Map<String, Object>> currentQuery(
            @PathVariable Integer id,
            @PathVariable String variables
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, null);

        return rows;
    }

    @RequestMapping(
            value = "/{id}/size/{size}",
            method = RequestMethod.GET,
            produces = { "application/json; charset=utf-8" })
    public @ResponseBody
    List<Map<String, Object>> currentQuery(
            @PathVariable Integer id,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, null, size);

        return rows;
    }

    @RequestMapping(
            value = "/{id}/{variables}/size/{size}",
            method = RequestMethod.GET,
            produces = { "application/json; charset=utf-8" })
    public @ResponseBody
    List<Map<String, Object>> currentQuery(
            @PathVariable Integer id,
            @PathVariable String variables,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, size);

        return rows;
    }

    @RequestMapping(
            value = "/{id}",
            method = RequestMethod.GET,
            produces = { "text/csv; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsCSV(
            @PathVariable Integer id
    ) {
        List<Map<String, Object>> rows = getRows(id, null, null);

        return getCsvResults(rows);
    }

    @RequestMapping(
            value = "/{id}/{variables}",
            method = RequestMethod.GET,
            produces = { "text/csv; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsCSV(
            @PathVariable Integer id,
            @PathVariable String variables
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, null);

        return getCsvResults(rows);
    }

    @RequestMapping(
            value = "/{id}/size/{size}",
            method = RequestMethod.GET,
            produces = { "text/csv; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsCSV(
            @PathVariable Integer id,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, null, size);

        return getCsvResults(rows);
    }

    @RequestMapping(
            value = "/{id}/{variables}/size/{size}",
            method = RequestMethod.GET,
            produces = { "text/csv; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsCSV(
            @PathVariable Integer id,
            @PathVariable String variables,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, size);

        return getCsvResults(rows);
    }

    @RequestMapping(
            value = "/{id}",
            method = RequestMethod.GET,
            produces = { "application/xml; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsXML(
            @PathVariable Integer id
    ) {
        List<Map<String, Object>> rows = getRows(id, null, null);

        XStream stream = getXMLStream();

        return stream.toXML(rows.toArray());
    }

    @RequestMapping(
            value = "/{id}/{variables}",
            method = RequestMethod.GET,
            produces = { "application/xml; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsXML(
            @PathVariable Integer id,
            @PathVariable String variables
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, null);

        XStream stream = getXMLStream();

        return stream.toXML(rows.toArray());
    }

    @RequestMapping(
            value = "/{id}/size/{size}",
            method = RequestMethod.GET,
            produces = { "application/xml; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsXML(
            @PathVariable Integer id,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, null, size);

        XStream stream = getXMLStream();

        return stream.toXML(rows.toArray());
    }

    @RequestMapping(
            value = "/{id}/{variables}/size/{size}",
            method = RequestMethod.GET,
            produces = { "application/xml; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsXML(
            @PathVariable Integer id,
            @PathVariable String variables,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, size);

        XStream stream = getXMLStream();

        return stream.toXML(rows.toArray());
    }

    @RequestMapping(
            value = "/{mode}/{complete}/{id}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable String mode,
            @PathVariable Boolean complete
    ) {
        List<Map<String, Object>> rows = getRows(id, null, null);

        String html = getHTML(mode, complete, rows);

        return html;
    }

    @RequestMapping(
            value = "/{mode}/{complete}/{id}/{variables}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable String variables,
            @PathVariable String mode,
            @PathVariable Boolean complete
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, null);

        String html = getHTML(mode, complete, rows);

        return html;
    }

    @RequestMapping(
            value = "/{id}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id
    ) {
        List<Map<String, Object>> rows = getRows(id, null, null);

        String html = getHTML("vertical", true, rows);

        return html;
    }

    @RequestMapping(
            value = "/{id}/{variables}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable String variables
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, null);

        String html = getHTML("vertical", true, rows);

        return html;
    }

    @RequestMapping(
            value = "/{mode}/{complete}/{id}/size/{size}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable String mode,
            @PathVariable Boolean complete,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, null, size);

        String html = getHTML(mode, complete, rows);

        return html;
    }

    @RequestMapping(
            value = "/{mode}/{complete}/{id}/{variables}/size/{size}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable String variables,
            @PathVariable String mode,
            @PathVariable Boolean complete,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, size);

        String html = getHTML(mode, complete, rows);

        return html;
    }

    @RequestMapping(
            value = "/{id}/size/{size}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, null, size);

        String html = getHTML("vertical", true, rows);

        return html;
    }

    @RequestMapping(
            value = "/{id}/{variables}/size/{size}",
            method = RequestMethod.GET,
            produces = { "text/html; charset=utf-8" })
    public @ResponseBody
    String currentQueryAsHTML(
            @PathVariable Integer id,
            @PathVariable String variables,
            @PathVariable Integer size
    ) {
        List<Map<String, Object>> rows = getRows(id, variables, size);

        String html = getHTML("vertical", true, rows);

        return html;
    }

    private List<Map<String, Object>> getResultRows(Integer id, String variables) {
        Query query = queryDAO.find(id);
        DatabaseConnection databaseConnection = databaseConnectionDAO.find(query.getDatabaseConnectionId());

        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        try {
            BasicDataSource dataSource = dataSourceFactory.getBasicDataSource(
                    databaseConnection.getUrl(),
                    databaseConnection.getUsername(),
                    databaseConnection.getPassword()
            );

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            if (variables != null) {
                List<Object> arguments = new ArrayList<Object>();
                String[] extractedVariables = variables.split(",");

                for (String variable : extractedVariables) {
                    try {
                        arguments.add(Long.parseLong(variable));
                    } catch(NumberFormatException noLong) {
                        try {
                            arguments.add(Double.parseDouble(variable));
                        } catch(NumberFormatException noDouble) {
                            arguments.add(variable);
                        }
                    }
                }

                rows = jdbcTemplate.queryForList(query.getContent(), arguments.toArray());
            } else {
                rows = jdbcTemplate.queryForList(query.getContent());
            }
        } catch (Exception e) {
            logger.debug(e);
        }

        return rows;
    }

    private String getCsvResults(List<Map<String, Object>> rows) {
        List<String[]> outputRows = new ArrayList<String[]>();
        Boolean first = true;

        StringWriter stringWriter = new StringWriter();
        CSVWriter csvWriter = new CSVWriter(stringWriter);

        for (Map<String, Object> row : rows) {
            if (first) {
                Set<String> keys = row.keySet();
                String[] columnTitles = new String[keys.size()];
                columnTitles = keys.toArray(columnTitles);
                outputRows.add(columnTitles);
                first = false;
            }

            Collection<Object> values = row.values();
            Object[] columnValues = new Object[values.size()];
            columnValues = values.toArray(columnValues);

            Integer numberOfValues = values.size();
            String[] columnOutputValues = new String[numberOfValues];
            for (int i = 0; i < numberOfValues; i++) {
                Object columnValue = columnValues[i];
                if (columnValue != null) {
                  columnOutputValues[i] = columnValue.toString();
                } else {
                  columnOutputValues[i] = "";
                }
            }
            outputRows.add(columnOutputValues);
        }

        csvWriter.writeAll(outputRows);

        return stringWriter.toString();
    }

    private XStream getXMLStream() {
        XStream stream = new XStream();
        stream.registerConverter(new MapEntryConverter());
        stream.alias("root", Map.class);
        return stream;
    }

    private String getHTML(String mode, Boolean complete, List<Map<String, Object>> rows) {
        String html;

        if (mode.equals("horizontal")) {
            if (complete) {
                html = getHorizontalResultListHTML(rows);
            } else {
                html = getHorizontalTableHTML(rows);
            }
        } else {
            if (complete) {
                html = getVerticalResultListHTML(rows);
            } else {
                html = getVerticalListHTML(rows);
            }
        }
        return html;
    }

    private String getHorizontalTableHTML(List<Map<String, Object>> rows) {
        Table table = new Table();
        table.setCSSClass("table table-striped table-bordered table-hover");

        Tr tableHeader = new Tr();
        Boolean firstRow = true;

        for (Map<String, Object> row : rows) {
            Tr tableRow = new Tr();

            for (String key : row.keySet()) {
                if (firstRow) {
                    Th th = new Th();
                    th.appendText(key);
                    tableHeader.appendChild(th);
                }

                Td td = new Td();
                tableRow.appendChild(td);

                Object value = row.get(key);
                if (value != null) {
                    appendValueToTd(td, value);
                }
            }

            if (firstRow) {
                table.appendChild(tableHeader);
                firstRow = false;
            }
            table.appendChild(tableRow);
        }

        return table.write();
    }

    private String getVerticalListHTML(List<Map<String, Object>> rows) {
        Div resultSetList = new Div();
        resultSetList.setCSSClass("row");

        for (Map<String, Object> row : rows) {
            Div entry = new Div();
            entry.setCSSClass("col-lg-12");
            resultSetList.appendChild(entry);

            Table entryTable = new Table();
            entryTable.setCSSClass("table table-striped table-bordered table-hover");
            entry.appendChild(entryTable);

            for (String key : row.keySet()) {
                Tr attributeRow = new Tr();

                Td tdKey = new Td();
                Strong strong = new Strong();
                tdKey.appendChild(strong);
                strong.appendText(key);
                attributeRow.appendChild(tdKey);

                Td tdValue = new Td();
                attributeRow.appendChild(tdValue);

                Object value = row.get(key);
                if (value != null) {
                    appendValueToTd(tdValue, value);
                }

                entryTable.appendChild(attributeRow);
            }
        }

        return resultSetList.write();
    }

    private String getHorizontalResultListHTML(List<Map<String, Object>> rows) {
        String html = "<!doctype html><!--[if lt IE 7]>      <html class=\"no-js lt-ie9 lt-ie8 lt-ie7\"> <![endif]--><!--[if IE 7]>         <html class=\"no-js lt-ie9 lt-ie8\"> <![endif]--><!--[if IE 8]>         <html class=\"no-js lt-ie9\"> <![endif]--><!--[if gt IE 8]><!--> <html class=\"no-js\"> <!--<![endif]--><head><meta charset=\"utf-8\"><title>禅宗 - ZenQuery</title><meta name=\"description\" content=\"\"><meta name=\"viewport\" content=\"width=device-width\"><link rel=\"stylesheet\" href=\"//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css\"><link rel=\"stylesheet\" href=\"//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap-theme.min.css\"><script src=\"//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js\"></script></head><body><div class=\"container\">";

        html += getHorizontalTableHTML(rows);

        html += "</div></body></html>";

        return html;
    }

    private String getVerticalResultListHTML(List<Map<String, Object>> rows) {
        String html = "<!doctype html><!--[if lt IE 7]>      <html class=\"no-js lt-ie9 lt-ie8 lt-ie7\"> <![endif]--><!--[if IE 7]>         <html class=\"no-js lt-ie9 lt-ie8\"> <![endif]--><!--[if IE 8]>         <html class=\"no-js lt-ie9\"> <![endif]--><!--[if gt IE 8]><!--> <html class=\"no-js\"> <!--<![endif]--><head><meta charset=\"utf-8\"><title>禅宗 - ZenQuery</title><meta name=\"description\" content=\"\"><meta name=\"viewport\" content=\"width=device-width\"><link rel=\"stylesheet\" href=\"//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css\"><link rel=\"stylesheet\" href=\"//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap-theme.min.css\"><script src=\"//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js\"></script></head><body><div class=\"container\">";

        html += getVerticalListHTML(rows);

        html += "</div></body></html>";

        return html;
    }

    private void appendValueToTd(Td td, Object value) {
        String text = value.toString();

        if (text.startsWith("/api/")) {
            A a = new A();
            a.setHref(text + ".html");
            a.appendText(text);

            text = a.write();
        }

        td.appendText(text);
    }

    private List<Map<String, Object>> getRows(Integer id, String variables, Integer size) {
        List<Map<String, Object>> rows = getResultRows(id, variables);

        if (size != null && size != 0 && size < rows.size()) {
            rows = rows.subList(0, size);
        }

        return rows;
    }
}