com.dieselpoint.norm.Query Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of norm Show documentation
An extremely lightweight data access layer over JDBC
There is a newer version: 1.1
package com.dieselpoint.norm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.dieselpoint.norm.sqlmakers.PojoInfo;
import com.dieselpoint.norm.sqlmakers.SqlMaker;

/**
 * Holds all of the information in a query. Create a query
 * using Database.someQueryCreationMethod(), populate it using
 * a builder pattern, and execute it using either .execute() (to
 * update the database) or .results() (to get the results of a query.) 
 */
public class Query {

	private Object insertRow;
	
	private String sql;
	private String table;
	private String where;
	private String orderBy;

	private Object[] args;

	private int rowsAffected;

	private Database db;
	private SqlMaker sqlMaker;
	
	private Transaction transaction;

	public Query(Database db) {
		this.db = db;
		this.sqlMaker = db.getSqlMaker();
	}

	/**
	 * Add a where clause and some parameters to a query. Has no effect if
	 * the .sql() method is used.
	 * @param where Example: "name=?"
	 * @param args The parameter values to use in the where, example: "Bob"
	 */
	public Query where(String where, Object... args) {
		this.where = where;
		this.args = args;
		return this;
	}

	/**
	 * Create a query using straight SQL. Overrides any other methods
	 * like .where(), .orderBy(), etc.
	 * @param sql The SQL string to use, may include ? parameters.
	 * @param args The parameter values to use in the query.
	 */
	public Query sql(String sql, Object... args) {
		this.sql = sql;
		this.args = args;
		return this;
	}

	/**
	 * Create a query using straight SQL. Overrides any other methods
	 * like .where(), .orderBy(), etc.
	 * @param sql The SQL string to use, may include ? parameters.
	 * @param args The parameter values to use in the query.
	 */
	public Query sql(String sql, List args) {
		this.sql = sql;
		this.args = args.toArray();
		return this;
	}

	/**
	 * Add an "orderBy" clause to a query.
	 */
	public Query orderBy(String orderBy) {
		this.orderBy = orderBy;
		return this;
	}

	/**
	 * Returns the first row in a query in a pojo, or null if the query returns no results. 
	 * Will return it in a Map if a class that implements Map is specified.
	 */
	public  T first(Class clazz) {
		List list = results(clazz);
		if (list.size() > 0) {
			return (T) list.get(0);
		} else {
			return null;
		}
	}

	
	/**
	 * Provides the results as a list of Map objects instead of a list of pojos.
	 */
	private List resultsMap(Class> clazz) {

		List out = new ArrayList();
		Connection con = null;
		PreparedStatement state = null;

		try {
			if (sql == null) {
				sql = sqlMaker.getSelectSql(this, clazz);
			}

			Connection localCon;
			if (transaction == null) {
				localCon = db.getConnection();
				con = localCon; // con gets closed below if non-null
			} else {
				localCon = transaction.getConnection();
			}
			
			state = localCon.prepareStatement(sql);
			loadArgs(state);

			ResultSet rs = state.executeQuery();

			ResultSetMetaData meta = rs.getMetaData();
			int colCount = meta.getColumnCount();

			while (rs.next()) {
				Map map = clazz.newInstance();

				for (int i = 1; i <= colCount; i++) {
					String colName = meta.getColumnLabel(i);
					map.put(colName, rs.getObject(i));
				}
				out.add(map);
			}

		} catch (InstantiationException | IllegalAccessException | SQLException
				| IllegalArgumentException e) {
			throw new DbException(e);
		} finally {
			close(state);
			close(con);
		}

		return out;
	}	
	
	
	/**
	 * Execute a "select" query and return a list of results where each row
	 * is an instance of clazz. Returns an empty list if there are no results.
	 */
	@SuppressWarnings("unchecked")
	public  List results(Class clazz) {
		
		if (Map.class.isAssignableFrom(clazz)) {
			return (List) resultsMap((Class>) clazz);
		}

		List out = new ArrayList();
		Connection con = null;
		PreparedStatement state = null;

		try {
			if (sql == null) {
				sql = sqlMaker.getSelectSql(this, clazz);
			}

			Connection localCon;
			if (transaction == null) {
				localCon = db.getConnection();
				con = localCon; // con gets closed below if non-null
			} else {
				localCon = transaction.getConnection();
			}
			
			state = localCon.prepareStatement(sql);
			loadArgs(state);

			ResultSet rs = state.executeQuery();

			ResultSetMetaData meta = rs.getMetaData();
			int colCount = meta.getColumnCount();

			if (Util.isPrimitiveOrString(clazz)) {
				// if the receiver class is a primitive just grab the first column and assign it
				while (rs.next()) {
					Object colValue = rs.getObject(1);
					out.add((T) colValue);
				}
				
			} else {
				PojoInfo pojoInfo = sqlMaker.getPojoInfo(clazz);
				while (rs.next()) {
					T row = clazz.newInstance();

					for (int i = 1; i <= colCount; i++) {
						String colName = meta.getColumnLabel(i);
						Object colValue = rs.getObject(i);
						
						pojoInfo.putValue(row, colName, colValue);
					}
					out.add((T) row);
				}
			}

		} catch (InstantiationException | IllegalAccessException
				| SQLException	e) {
			throw new DbException(e);
		} finally {
			close(state);
			close(con);
		}

		return out;
	}

	private void loadArgs(PreparedStatement state) throws SQLException {
		if (args != null) {
			for (int i = 0; i < args.length; i++) {
				state.setObject(i + 1, args[i]);
			}
		}
	}
	
	private void close(AutoCloseable ac) {
		if (ac == null) {
			return;
		}
		try {
			ac.close();
		} catch (Exception e) {
			// bury it
		}
	}
	

	
	/**
	 * Insert a row into a table. The row pojo can have a @Table annotation to
	 * specify the table, or you can specify the table with the .table() method.
	 */
	public Query insert(Object row) {

		insertRow = row;

		sql = sqlMaker.getInsertSql(this, row);
		args = sqlMaker.getInsertArgs(this, row);

		execute();

		return this;
	}

	/**
	 * Upsert a row into a table.
	 * See http://en.wikipedia.org/wiki/Merge_%28SQL%29
	 */
	public Query upsert(Object row) {

		insertRow = row;

		sql = sqlMaker.getUpsertSql(this, row);
		args = sqlMaker.getUpsertArgs(this, row);

		execute();

		return this;
	}
	
	
	/**
	 * Update a row in a table. It will match an existing row based on the
	 * primary key.
	 */
	public Query update(Object row) {

		sql = sqlMaker.getUpdateSql(this, row);
		args = sqlMaker.getUpdateArgs(this, row);

		if (execute().getRowsAffected() <= 0) {
			throw new DbException("Row not updated because the primary key was not found");
		}
		return this;
	}

	/**
	 * Execute a sql command that does not return a result set. The sql should previously have
	 * been set with the sql(String) method. Returns this Query object. To see how the command did, call
	 * .rowsAffected().
	 */
	public Query execute() {
		
		Connection con = null;
		PreparedStatement state = null;

		try {
			
			Connection localCon;
			if (transaction == null) {
				localCon = db.getConnection();
				con = localCon; // con gets closed below if non-null
			} else {
				localCon = transaction.getConnection();
			}

			/*
			 * This is a hack to deal with an error in the Postgres driver.
			 * Postgres blindly appends "RETURNING *" to any query that includes
			 * Statement.RETURN_GENERATED_KEYS. This is a bug. See:
			 * http://www.postgresql.org/message-id/[email protected]
			 * So, as a workaround, we only add that flag if the query contains
			 * "insert". Yuck.
			 */
			String lowerSql = sql.toLowerCase();
			if (lowerSql.contains("insert")) {
				state = localCon.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			} else {
				state = localCon.prepareStatement(sql);
			}
			
			if (args != null) {
				for (int i = 0; i < args.length; i++) {
					state.setObject(i + 1, args[i]);
				}
			}

			rowsAffected = state.executeUpdate();
			
			// Set auto generated primary key. The code assumes that the primary
			// key is the only auto generated key.
			if (insertRow != null) {
				ResultSet generatedKeys = state.getGeneratedKeys();
				if (generatedKeys.next()) {
					sqlMaker.populateGeneratedKey(generatedKeys, insertRow);
				}			
			}

		} catch (SQLException | IllegalArgumentException e) {
			throw new DbException(e);
		} finally {
			close(state);
			close(con);
		}

		return this;
	}

	/**
	 * Simple, primitive method for creating a table based on a pojo. 
	 */
	public Query createTable(Class clazz) {
		sql = sqlMaker.getCreateTableSql(clazz);
		execute();
		return this;
	}

	
	/**
	 * Delete a row in a table. This method looks for an @Id annotation to find
	 * the row to delete by primary key, and looks for a @Table annotation to
	 * figure out which table to hit.
	 */
	public Query delete(Object row) {

		sql = sqlMaker.getDeleteSql(this, row);
		args = sqlMaker.getDeleteArgs(this, row);

		execute();
		return this;
	}

	/**
	 * Delete multiple rows in a table. Be sure to specify the 
	 * table with the .table() method and limit the rows to delete
	 * using the .where() method.
	 */
	public Query delete() {
		String table = getTable();
		if (table == null) {
			throw new DbException("You must specify a table name with the table() method.");
		}
		sql = "delete from " + table;
		if (where != null) {
			sql += " where " + where;
		}
		execute();
		return this;
	}
	
	/**
	 * Specify the table to operate on.
	 */
	public Query table(String table) {
		this.table = table;
		return this;
	}
	
	/**
	 * For queries that affect the database in some way, this method returns the 
	 * number of rows affected. Call it after you call .execute(), .update(), .delete(), etc.:
	 * .table("foo").where("bar=bah").delete().rowsAffected();
	 */
	public int getRowsAffected() {
		return rowsAffected;
	}

	/**
	 * Specify that this query should be a part of the specified transaction.
	 */
	public Query transaction(Transaction trans) {
		this.transaction = trans;
		return this;
	}

	public String getOrderBy() {
		return orderBy;
	}

	public String getWhere() {
		return where;
	}

	public String getTable() {
		return table;
	}
	
}