com.aoindustries.dao.dbc.QueryReport Maven / Gradle / Ivy

Go to download
/*
 * ao-dao-dbc - Simple data access objects framework implementation leveraging ao-dbc.
 * Copyright (C) 2011, 2013, 2015, 2016  AO Industries, Inc.
 *     [email protected]
 *     7262 Bull Pen Cir
 *     Mobile, AL 36695
 *
 * This file is part of ao-dao-dbc.
 *
 * ao-dao-dbc is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * ao-dao-dbc is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with ao-dao-dbc.  If not, see .
 */
package com.aoindustries.dao.dbc;

import com.aoindustries.dao.Report;
import com.aoindustries.dbc.Database;
import com.aoindustries.dbc.DatabaseConnection;
import com.aoindustries.sql.WrappedSQLException;
import com.aoindustries.util.AoCollections;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * A report that is obtained from a SQL query database.
 */
public abstract class QueryReport
	implements Report
{

	public static class QueryColumn implements Report.Column {

		private final QueryReport report;
		private final String name;
		private final Report.Alignment alignment;

		QueryColumn(QueryReport report, String name, Report.Alignment alignment) {
			this.report = report;
			this.name = name;
			this.alignment = alignment;
		}

		@Override
		public String getName() {
			return name;
		}

		/**
		 * Gets the label from the report.
		 *
		 * @see Report#getColumnLabel
		 */
		@Override
		public String getLabel() {
			return report.getColumnLabel(this);
		}

		@Override
		public Report.Alignment getAlignment() {
			return alignment;
	   }
	}

	public static class ReportResult implements Report.Result {

		private final List columns;
		private final List> tableData;

		ReportResult(List columns, List> tableData) {
			this.columns = columns;
			this.tableData = tableData;
		}

		@Override
		public List getColumns() {
			return columns;
		}

		@Override
		public List> getTableData() {
			return tableData;
		}
	}

	private final Database database;
	private final String name;
	private final String sql;
	private final Object[] params;

	/**
	 * @param params to substitute a parameter, provide the Parameter object.
	 */
	public QueryReport(Database database, String name, String sql, Object... params) {
		this.database = database;
		this.name = name;
		this.sql = sql;
		this.params = params;
	}

	/**
	 * @param params to substitute a parameter, provide the Parameter object.
	 */
	public QueryReport(Database database, String name, String sql, Collection params) {
		this(database, name, sql, params.toArray());
	}

	@Override
	public String getName() {
		return name;
	}

	/**
	 * Defaults to calling getTitle().
	 */
	@Override
	public String getTitle(Map parameterValues) {
		return getTitle();
	}

	/**
	 * Defaults to calling getDescription().
	 */
	@Override
	public String getDescription(Map parameterValues) {
		return getDescription();
	}

	/**
	 * Gets the label for the provided column.  Defaults to column name.
	 *
	 * @see QueryColumn#getLabel()
	 */
	public String getColumnLabel(QueryColumn column) {
		return column.name;
	}

	/**
	 * Get default, no parameters are required.
	 */
	@Override
	public Iterable getParameters() {
		return Collections.emptyList();
	}

	/**
	 * Checks if this is a read-only report.  No temp tables or views may be created on
	 * a read-only report.  Defaults to true.
	 */
	public boolean isReadOnly() {
		return true;
	}

	@Override
	public ReportResult executeReport(Map parameterValues) throws SQLException {
		Connection conn = database.getConnection(Connection.TRANSACTION_READ_COMMITTED, isReadOnly(), 1);
		try {
			try {
				beforeQuery(parameterValues, conn);
				try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
					try {
						/**
						 * Substitute any parameters with the values provided.
						 */
						Object[] sqlParams = new Object[params.length];
						for(int i=0; i columns = new ArrayList<>();
							for(int columnIndex=1; columnIndex<=numColumns; columnIndex++) {
								final Alignment alignment;
								switch(meta.getColumnType(columnIndex)) {
									case Types.BIGINT :
									case Types.DECIMAL :
									case Types.DOUBLE :
									case Types.FLOAT :
									case Types.INTEGER :
									case Types.NUMERIC :
									case Types.REAL :
									case Types.SMALLINT :
									case Types.TINYINT :
										alignment = Alignment.right;
										break;
									case Types.BOOLEAN :
									case Types.BIT :
										alignment = Alignment.center;
										break;
									default :
										alignment = Alignment.left;
								}
								columns.add(new QueryColumn(this, meta.getColumnName(columnIndex), alignment));
							}
							List> tableData = new ArrayList<>();
							while(results.next()) {
								List