base.query.SqlQuery Maven / Gradle / Ivy

Go to download
/**
 * Creative commons Attribution-NonCommercial license.
 *
 * http://creativecommons.org/licenses/by-nc/2.5/au/deed.en_GB
 *
 * NO WARRANTY IS GIVEN OR IMPLIED, USE AT YOUR OWN RISK.
 */
package base.query;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import base.MapQueryResult;
import base.Query;
import base.security.PermissionException;

/**
 * Report on each of the current system settings.
 */
public class SqlQuery extends Query {

	private DataSource ds;
	private String sqlQuery;

	/**
	 * Unused constructor for the ServiceLoader
	 */
	public SqlQuery() {
	}

	/**
	 *
	 * @param ds
	 * @throws SQLException
	 */
	public SqlQuery(DataSource ds, String sqlQuery) throws SQLException {
		this.ds = ds;
		this.sqlQuery = sqlQuery;
	}

	public List execute() throws IOException {
		List results = null;
		Connection c = null;

		try {
			c = ds.getConnection();
			c.setAutoCommit(false);

			results = executeQuery(c);

			c.rollback();
			c.close();
		} catch (SQLException e) {
			if(c != null) {
				try { c.rollback(); } catch (SQLException e1) { }
				try { c.close(); } catch (SQLException e1) { }
			}
			throw new IOException(e);
		}

		return results;
	}

	/**
	 * Return each setting in the form of key value pairs.
	 */
	public List executeQuery(Connection c) throws SQLException {
		List list = new ArrayList<>();
		List header = new LinkedList<>();

		PreparedStatement s = null;
		ResultSet r = null;
		int columns = 0;
		int count = 0;
		try {
			s = c.prepareStatement(sqlQuery);
			r = s.executeQuery();
			while(r.next()) {
				if(count == 0) {
					// Get result set meta data
					ResultSetMetaData rsmd = r.getMetaData();
					columns = rsmd.getColumnCount();
					header = new LinkedList<>();
					for(int i = 0; i < columns; i++) {
						header.add(rsmd.getColumnName(i+1).toString());
					}
				}
				MapQueryResult row = new MapQueryResult();
				for(int i = 0; i < columns; i++) {
					row.setValue(header.get(i), r.getObject(i+1));
				}
				list.add(row);
				count++;
			}
			r.close();
			r = null;
			s.close();
			s = null;
		} finally {
			if(r != null) { try { r.close(); } catch(Exception f) {} }
			if(s != null) { try { s.close(); } catch(Exception f) {} }
		}

		return list;
	}

	@Override
	public String getJsonParameters() {
		return "{\"query\":\""+sqlQuery+"\"}";
	}

	@Override
	public Query newWithParameters(Map parameters) throws IOException, PermissionException {
		throw new IllegalArgumentException("SqlQuery may not be instantiated with a parameter map.");
	}

}