Implement Dao proxy generation with annotated sql statement execution

This functionality is inspired by JDBI but is not based on it's implementation.

14 files changed, 1587 insertions, 44 deletions

diff --git a/src/main/java/com/iciql/Dao.java b/src/main/java/com/iciql/Dao.java
new file mode 100644
index 0000000..29b42f0
--- /dev/null
+++ b/src/main/java/com/iciql/Dao.java
@@ -0,0 +1,162 @@
+/*
+ * Copyright 2014 James Moger.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package com.iciql;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+import java.util.List;
+
+/**
+ * The Dao interface defines all CRUD methods for handling SQL object operations.
+ *
+ * @author James Moger
+ *
+ */
+public interface Dao extends AutoCloseable {
+
+	/**
+	 * Insert an object into the database.
+	 *
+	 * @param t
+	 * @return true if successful
+	 */
+	<T> boolean insert(T t);
+
+	/**
+	 * Insert an object into the database and return it's primary key.
+	 *
+	 * @param t
+	 * @return
+	 */
+	<T> long insertAndGetKey(T t);
+
+	/**
+	 * Insert all objects into the database.
+	 *
+	 * @param list
+	 */
+	<T> void insertAll(List<T> list);
+
+	/**
+	 * Insert all objects into the database and return the list of primary keys.
+	 *
+	 * @param t
+	 * @return a list of primary keys
+	 */
+	<T> List<Long> insertAllAndGetKeys(List<T> t);
+
+	/**
+	 * Updates an object in the database.
+	 *
+	 * @param t
+	 * @return true if successful
+	 */
+	<T> boolean update(T t);
+
+	/**
+	 * Updates all objects in the database.
+	 *
+	 * @param list
+	 */
+	<T> void updateAll(List<T> list);
+
+	/**
+	 * Inserts or updates an object in the database.
+	 *
+	 * @param t
+	 */
+	<T> void merge(T t);
+
+	/**
+	 * Deletes an object from the database.
+	 *
+	 * @param t
+	 * @return true if successful
+	 */
+	<T> boolean delete(T t);
+
+	/**
+	 * Deletes all objects from the database.
+	 *
+	 * @param list
+	 */
+	<T> void deleteAll(List<T> list);
+
+	/**
+	 * Returns the underlying Db instance for lower-level access to database methods
+	 * or direct JDBC access.
+	 *
+	 * @return the db instance
+	 */
+	Db db();
+
+	/**
+	 * Close the underlying Db instance.
+	 */
+	@Override
+	void close();
+
+	/**
+	 * Used to specify custom names for method parameters to be used
+	 * for the SqlQuery or SqlUpdate annotations.
+	 *
+	 * You don't need to explicitly bind the parameters as each parameter
+	 * is accessible by the standard "argN" syntax (0-indexed).
+	 *
+	 * Additionally, if you are compiling with Java 8 AND specifying the
+	 * -parameters flag for javac, then you may use the parameter's name.
+	 */
+	@Retention(RetentionPolicy.RUNTIME)
+	@Target({ ElementType.PARAMETER })
+	public @interface Bind {
+		String value();
+	}
+
+	/**
+	 *
+	 */
+	@Retention(RetentionPolicy.RUNTIME)
+	@Target({ ElementType.PARAMETER })
+	public @interface BindBean {
+		String value() default "";
+	}
+
+	/**
+	 * Used to indicate that a method should execute a query.
+	 */
+	@Retention(RetentionPolicy.RUNTIME)
+	@Target({ ElementType.METHOD })
+	public @interface SqlQuery {
+		String value();
+	}
+
+	/**
+	 * Used to indicate that a method should execute a statement.
+	 */
+	@Retention(RetentionPolicy.RUNTIME)
+	@Target({ ElementType.METHOD })
+	public @interface SqlStatement {
+		String value();
+	}
+
+	public class BeanBinder {
+		public void bind(BindBean bind, Object obj) {
+
+		}
+	}
+}
diff --git a/src/main/java/com/iciql/DaoProxy.java b/src/main/java/com/iciql/DaoProxy.java
new file mode 100644
index 0000000..db5f911
--- /dev/null
+++ b/src/main/java/com/iciql/DaoProxy.java
@@ -0,0 +1,792 @@
+/*
+ * Copyright 2014 James Moger.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package com.iciql;
+
+import java.lang.annotation.Annotation;
+import java.lang.reflect.Array;
+import java.lang.reflect.Constructor;
+import java.lang.reflect.Field;
+import java.lang.reflect.InvocationHandler;
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Method;
+import java.lang.reflect.Modifier;
+import java.lang.reflect.Proxy;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.TreeMap;
+import java.util.concurrent.ConcurrentHashMap;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import com.iciql.Iciql.DataTypeAdapter;
+import com.iciql.Iciql.TypeAdapter;
+import com.iciql.util.JdbcUtils;
+import com.iciql.util.StringUtils;
+import com.iciql.util.Utils;
+
+/**
+ * DaoProxy creates a dynamic instance of the provided Dao interface.
+ *
+ * @author James Moger
+ *
+ * @param <X>
+ */
+final class DaoProxy<X extends Dao> implements InvocationHandler, Dao {
+
+	private final Db db;
+
+	private final Class<X> daoInterface;
+
+	private final char bindingDelimiter = ':';
+
+	private final Map<Method, IndexedSql> indexedSqlCache;
+
+	DaoProxy(Db db, Class<X> daoInterface) {
+		this.db = db;
+		this.daoInterface = daoInterface;
+		this.indexedSqlCache = new ConcurrentHashMap<Method, IndexedSql>();
+	}
+
+	/**
+	 * Builds a proxy object for the DAO interface.
+	 *
+	 * @return a proxy object
+	 */
+	@SuppressWarnings("unchecked")
+	X buildProxy() {
+
+		if (!daoInterface.isInterface()) {
+			throw new IciqlException("Dao {0} must be an interface!", daoInterface.getName());
+		}
+
+		ClassLoader classLoader = db.getClass().getClassLoader();
+
+		Set<Class<?>> interfaces = new HashSet<Class<?>>();
+		interfaces.add(Dao.class);
+		interfaces.add(daoInterface);
+		for (Class<?> clazz : daoInterface.getInterfaces()) {
+			interfaces.add(clazz);
+		}
+
+		Class<?>[] constructorParams = { InvocationHandler.class };
+		Class<?>[] allInterfaces = interfaces.toArray(new Class<?>[interfaces.size()]);
+
+		try {
+
+			Class<?> proxyClass = Proxy.getProxyClass(classLoader, allInterfaces);
+			Constructor<?> proxyConstructor = proxyClass.getConstructor(constructorParams);
+			return (X) proxyConstructor.newInstance(new Object[] { this });
+
+		} catch (Exception e) {
+			throw new IciqlException(e);
+		}
+	}
+
+	/**
+	 * Invoke intercepts method calls and delegates execution to the appropriate object.
+	 */
+	@Override
+	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
+		try {
+
+			if (method.getDeclaringClass() == Dao.class) {
+
+				return method.invoke(this, args);
+
+			} else if (method.isAnnotationPresent(SqlQuery.class)) {
+
+				String sql = method.getAnnotation(SqlQuery.class).value();
+				return executeQuery(method, args, sql);
+
+			} else if (method.isAnnotationPresent(SqlStatement.class)) {
+
+				String sql = method.getAnnotation(SqlStatement.class).value();
+				return executeStatement(method, args, sql);
+
+			} else {
+
+				throw new IciqlException("Can not invoke non-dao method {0}.{1}",
+						method.getDeclaringClass().getSimpleName(), method.getName());
+
+			}
+
+		} catch (InvocationTargetException te) {
+			throw te.getCause();
+		}
+	}
+
+	/**
+	 * Execute a query.
+	 *
+	 * @param method
+	 * @param methodArgs
+	 * @param sql
+	 * @return the result
+	 */
+	private Object executeQuery(Method method, Object[] methodArgs, String sql) {
+
+		/*
+		 * Determine and validate the return type
+		 */
+		Class<?> returnType = method.getReturnType();
+
+		if (void.class == returnType) {
+			throw new IciqlException("You must specify a return type for @{0} {1}.{2}!",
+					SqlQuery.class.getSimpleName(), method.getDeclaringClass().getSimpleName(), method.getName());
+		}
+
+		if (Collection.class.isAssignableFrom(returnType)) {
+			throw new IciqlException("You may not return a collection for an @{0} method, please change the return type of {1}.{2} to YourClass[]!",
+					SqlQuery.class.getSimpleName(), method.getDeclaringClass().getSimpleName(), method.getName());
+		}
+
+		boolean isArray = false;
+		if (returnType.isArray()) {
+			isArray = true;
+			returnType = returnType.getComponentType();
+		}
+
+		boolean isJavaType = returnType.isEnum()
+				|| returnType.isPrimitive()
+				|| java.lang.Boolean.class.isAssignableFrom(returnType)
+				|| java.lang.Number.class.isAssignableFrom(returnType)
+				|| java.lang.String.class.isAssignableFrom(returnType)
+				|| java.util.Date.class.isAssignableFrom(returnType)
+				|| byte[].class.isAssignableFrom(returnType);
+
+		// determine the return type adapter, if any
+		DataTypeAdapter<?> adapter = null;
+		for (Annotation annotation : method.getAnnotations()) {
+			if (annotation.annotationType() == TypeAdapter.class) {
+				TypeAdapter typeAdapter = (TypeAdapter) annotation;
+				adapter = db.getDialect().getAdapter(typeAdapter.value());
+				break;
+			} else if (annotation.annotationType().isAnnotationPresent(TypeAdapter.class)) {
+				TypeAdapter typeAdapter = annotation.annotationType().getAnnotation(TypeAdapter.class);
+				adapter = db.getDialect().getAdapter(typeAdapter.value());
+				break;
+			}
+		}
+
+		/*
+		 * Prepare & execute sql
+		 */
+		PreparedSql preparedSql = prepareSql(method, methodArgs, sql);
+
+		List<Object> objects;
+		if (!isJavaType && adapter == null) {
+
+			// query of an Iciql model
+			objects = db.executeQuery(returnType, preparedSql.sql, preparedSql.parameters);
+
+		} else {
+
+			// query of (array of) standard Java type or a DataTypeAdapter type
+			objects = Utils.newArrayList();
+			ResultSet rs = db.executeQuery(preparedSql.sql, preparedSql.parameters);
+			try {
+
+				while (rs.next()) {
+
+					Object o = rs.getObject(1);
+					Object value;
+
+					if (adapter == null) {
+						// use internal Iciql type conversion
+						value = Utils.convert(o, returnType);
+					} else {
+						// use the type adapter to convert the JDBC object to a domain type
+						value = adapter.deserialize(o);
+					}
+
+					objects.add(value);
+
+				}
+
+			} catch (SQLException e) {
+				throw new IciqlException(e);
+			} finally {
+				JdbcUtils.closeSilently(rs);
+			}
+
+		}
+
+		/*
+		 * Return the results
+		 */
+		if (objects == null || objects.isEmpty()) {
+
+			// no results
+			if (isArray) {
+				// return an empty array
+				return Array.newInstance(returnType, 0);
+			}
+
+			// nothing to return!
+			return null;
+
+		} else if (isArray) {
+
+			// return an array of object results
+			Object array = Array.newInstance(returnType, objects.size());
+			for (int i = 0; i < objects.size(); i++) {
+				Array.set(array, i, objects.get(i));
+			}
+			return array;
+
+		}
+
+		// return first element
+		return objects.get(0);
+	}
+
+
+	/**
+	 * Execute a statement.
+	 *
+	 * @param method
+	 * @param methodArgs
+	 * @param sql
+	 * @return the result
+	 */
+	private Object executeStatement(Method method, Object[] methodArgs, String sql) {
+
+		/*
+		 * Determine and validate the return type
+		 */
+		Class<?> returnType = method.getReturnType();
+
+		if (void.class != returnType && boolean.class != returnType && int.class != returnType) {
+
+			throw new IciqlException("Invalid return type '{0}' for @{1} {2}.{3}!",
+					returnType.getSimpleName(), SqlQuery.class.getSimpleName(),
+					method.getDeclaringClass().getSimpleName(), method.getName());
+		}
+
+		/*
+		 * Prepare & execute sql
+		 */
+		PreparedSql preparedSql = prepareSql(method, methodArgs, sql);
+		int rows = db.executeUpdate(preparedSql.sql, preparedSql.parameters);
+
+		/*
+		 * Return the results
+		 */
+		if (void.class == returnType) {
+
+			// return nothing
+			return null;
+
+		} else if (boolean.class == returnType) {
+
+			// return true if any rows were affected
+			return rows > 0;
+
+		} else {
+
+			// return number of rows
+			return rows;
+
+		}
+	}
+
+	/**
+	 * Prepares an sql statement and execution parameters based on the supplied
+	 * method and it's arguments.
+	 *
+	 * @param method
+	 * @param methodArgs
+	 * @param sql
+	 * @return a prepared sql statement and arguments
+	 */
+	private PreparedSql prepareSql(Method method, Object[] methodArgs, String sql) {
+
+		if (methodArgs == null || methodArgs.length == 0) {
+			// no method arguments
+			return new PreparedSql(sql, null);
+		}
+
+		IndexedSql indexedSql = indexedSqlCache.get(method);
+
+		if (indexedSql == null) {
+
+			// index the sql and method args
+			indexedSql = indexSql(method, sql);
+
+			// cache the indexed sql for re-use
+			indexedSqlCache.put(method, indexedSql);
+		}
+
+		final PreparedSql preparedSql = indexedSql.prepareSql(db, methodArgs);
+		return preparedSql;
+	}
+
+	/**
+	 * Indexes an sql statement and method args based on the supplied
+	 * method and it's arguments.
+	 *
+	 * @param method
+	 * @param sql
+	 * @return an indexed sql statement and arguments
+	 */
+	private IndexedSql indexSql(Method method, String sql) {
+
+		Map<String, IndexedArgument> parameterIndex = buildParameterIndex(method);
+
+		// build a regex to extract parameter names from the sql statement
+		StringBuilder sb = new StringBuilder();
+		sb.append(bindingDelimiter);
+		sb.append("{1}(\\?");
+		for (String name : parameterIndex.keySet()) {
+			sb.append("|");
+			// strip binding delimeter from name
+			sb.append(name);
+		}
+		sb.append(')');
+
+		// identify parameters, replace with the '?' PreparedStatement
+		// delimiter and build the PreparedStatement parameters array
+		final String regex = sb.toString();
+		final Pattern p = Pattern.compile(regex);
+		final Matcher m = p.matcher(sql);
+		final StringBuffer buffer = new StringBuffer();
+
+		List<IndexedArgument> indexedArgs = Utils.newArrayList();
+		int count = 0;
+		while (m.find()) {
+			String binding = m.group(1);
+			m.appendReplacement(buffer, "?");
+
+			IndexedArgument indexedArg;
+			if ("?".equals(binding)) {
+				// standard ? JDBC placeholder
+				indexedArg = parameterIndex.get("arg" + count);
+			} else {
+				// named placeholder
+				indexedArg = parameterIndex.get(binding);
+			}
+
+			if (indexedArg == null) {
+				throw new IciqlException("Unbound SQL parameter '{0}' in {1}.{2}",
+						binding, method.getDeclaringClass().getSimpleName(), method.getName());
+			}
+			indexedArgs.add(indexedArg);
+
+			count++;
+		}
+		m.appendTail(buffer);
+
+		final String statement = buffer.toString();
+
+		// create an IndexedSql container for the statement and indexes
+		return new IndexedSql(statement, Collections.unmodifiableList(indexedArgs));
+
+	}
+
+	/**
+	 * Builds an index of parameter name->(position,typeAdapter) from the method arguments
+	 * array. This index is calculated once per method.
+	 *
+	 * @param method
+	 * @return a bindings map of ("name", IndexedArgument) pairs
+	 */
+	private Map<String, IndexedArgument> buildParameterIndex(Method method) {
+
+		Map<String, IndexedArgument> index = new TreeMap<String, IndexedArgument>();
+
+		Annotation [][] annotationsMatrix = method.getParameterAnnotations();
+		for (int i = 0; i < annotationsMatrix.length; i++) {
+
+			Annotation [] annotations = annotationsMatrix[i];
+
+			/*
+			 * Conditionally map the bean properties of the method argument
+			 * class to Method and Field instances.
+			 */
+			BindBean bean = getAnnotation(BindBean.class, annotations);
+			if (bean != null) {
+				final String prefix = bean.value();
+				final Class<?> argumentClass = method.getParameterTypes()[i];
+				Map<String, IndexedArgument> beanIndex = buildBeanIndex(i, prefix, argumentClass);
+				index.putAll(beanIndex);
+			}
+
+			Class<? extends DataTypeAdapter<?>> typeAdapter = Utils.getDataTypeAdapter(annotations);
+			final IndexedArgument indexedArgument = new IndexedArgument(i, typeAdapter);
+
+			// :N - 1-indexed, like JDBC ResultSet
+			index.put("" + (i + 1), indexedArgument);
+
+			// argN - 0-indexed, like Reflection
+			index.put("arg" + i, indexedArgument);
+
+			// Bound name
+			Bind binding = getAnnotation(Bind.class, annotations);
+			if (binding!= null && !binding.value().isEmpty()) {
+				index.put(binding.value(), indexedArgument);
+			}
+
+			// try mapping Java 8 argument names, may overwrite argN
+			try {
+				Class<?> nullArgs = null;
+				Method getParameters = method.getClass().getMethod("getParameters", nullArgs);
+				if (getParameters != null) {
+					Object [] parameters = (Object []) getParameters.invoke(method, nullArgs);
+					if (parameters != null) {
+						Object o = parameters[i];
+						Method getName = o.getClass().getMethod("getName", nullArgs);
+						String j8name = getName.invoke(o, nullArgs).toString();
+						if (!j8name.isEmpty()) {
+							index.put(j8name, indexedArgument);
+						}
+					}
+				}
+			} catch (Throwable t) {
+			}
+		}
+
+		return index;
+	}
+
+	/**
+	 * Builds an index of parameter name->(position,method) from the method arguments
+	 * array. This index is calculated once per method.
+	 *
+	 * @param argumentIndex
+	 * @param prefix
+	 * @param beanClass
+	 * @return a bindings map of ("prefix.property", IndexedArgument) pairs
+	 */
+	private Map<String, IndexedArgument> buildBeanIndex(int argumentIndex, String prefix, Class<?> beanClass) {
+
+		final String beanPrefix = StringUtils.isNullOrEmpty(prefix) ? "" : (prefix + ".");
+		final Map<String, IndexedArgument> index = new TreeMap<String, IndexedArgument>();
+
+		// map JavaBean property getters
+		for (Method method : beanClass.getMethods()) {
+
+			if (Modifier.isStatic(method.getModifiers())
+				|| method.getReturnType() == void.class
+				|| method.getParameterTypes().length > 0
+				|| method.getDeclaringClass() == Object.class) {
+
+				// not a JavaBean property
+				continue;
+			}
+
+			final String propertyName;
+			final String name = method.getName();
+			if (name.startsWith("get")) {
+				propertyName = method.getName().substring(3);
+			} else if (name.startsWith("is")) {
+				propertyName = method.getName().substring(2);
+			} else {
+				propertyName = null;
+			}
+
+			if (propertyName == null) {
+				// not a conventional JavaBean property
+				continue;
+			}
+
+			final String binding = beanPrefix + preparePropertyName(propertyName);
+			final IndexedArgument indexedArg = new IndexedArgument(argumentIndex, method);
+
+			index.put(binding, indexedArg);
+		}
+
+		// map public instance fields
+		for (Field field : beanClass.getFields()) {
+
+			if (Modifier.isStatic(field.getModifiers())) {
+				// not a JavaBean property
+				continue;
+			}
+
+			final String binding = beanPrefix + preparePropertyName(field.getName());
+			final IndexedArgument indexedArg = new IndexedArgument(argumentIndex, field);
+
+			index.put(binding, indexedArg);
+
+		}
+
+		return index;
+	}
+
+	@SuppressWarnings("unchecked")
+	private <T> T getAnnotation(Class<T> annotationClass, Annotation [] annotations) {
+		if (annotations != null) {
+			for (Annotation annotation : annotations) {
+				if (annotation.annotationType() == annotationClass) {
+					return (T) annotation;
+				}
+			}
+		}
+		return null;
+	}
+
+	private String preparePropertyName(String value) {
+		return Character.toLowerCase(value.charAt(0)) + value.substring(1);
+	}
+
+	/*
+	 *
+	 * Standard Dao method implementations delegate to the underlying Db
+	 *
+	 */
+
+	@Override
+	public final Db db() {
+		return db;
+	}
+
+	@Override
+	public final <T> boolean insert(T t) {
+		return db.insert(t);
+	}
+
+	@Override
+	public final <T> void insertAll(List<T> t) {
+		db.insertAll(t);
+	}
+
+	@Override
+	public final <T> long insertAndGetKey(T t) {
+		return db.insertAndGetKey(t);
+	}
+
+	@Override
+	public final <T> List<Long> insertAllAndGetKeys(List<T> t) {
+		return db.insertAllAndGetKeys(t);
+	}
+
+	@Override
+	public final <T> boolean update(T t) {
+		return db.update(t);
+	}
+
+	@Override
+	public final <T> void updateAll(List<T> t) {
+		db.updateAll(t);
+	}
+
+	@Override
+	public final <T> void merge(T t) {
+		db.merge(t);
+	}
+
+	@Override
+	public final <T> boolean delete(T t) {
+		return db.delete(t);
+	}
+
+	@Override
+	public final <T> void deleteAll(List<T> t) {
+		db.deleteAll(t);
+	}
+
+	@Override
+	public final void close() {
+		db.close();
+	}
+
+	/**
+	 * Container class to hold the prepared JDBC SQL statement and execution
+	 * parameters.
+	 */
+	private class PreparedSql {
+		final String sql;
+		final Object [] parameters;
+
+		PreparedSql(String sql, Object [] parameters) {
+			this.sql = sql;
+			this.parameters = parameters;
+		}
+
+		@Override
+		public String toString() {
+			return sql;
+		}
+
+	}
+
+	/**
+	 * Container class to hold a parsed JDBC SQL statement and
+	 * IndexedParameters.
+	 * <p>
+	 * Instances of this class are cached because they are functional processing
+	 * containers as they contain Method and Field references for binding beans
+	 * and matching to method arguments.
+	 * </p>
+	 */
+	private class IndexedSql {
+		final String sql;
+		final List<IndexedArgument> indexedArgs;
+
+		IndexedSql(String sql, List<IndexedArgument> indexedArgs) {
+			this.sql = sql;
+			this.indexedArgs = indexedArgs;
+		}
+
+		/**
+		 * Prepares the method arguments for statement execution.
+		 *
+		 * @param db
+		 * @param methodArgs
+		 * @return the prepared sql statement and parameters
+		 */
+		PreparedSql prepareSql(Db db, Object [] methodArgs) {
+
+			Object [] parameters = new Object[indexedArgs.size()];
+
+			for (int i = 0; i < indexedArgs.size(); i++) {
+
+				IndexedArgument indexedArg = indexedArgs.get(i);
+				Object methodArg = methodArgs[indexedArg.index];
+
+				Object value = methodArg;
+				Class<? extends DataTypeAdapter<?>> typeAdapter = indexedArg.typeAdapter;
+
+				if (indexedArg.method != null) {
+
+					// execute the bean method
+					try {
+
+						value = indexedArg.method.invoke(methodArg);
+						typeAdapter = Utils.getDataTypeAdapter(indexedArg.method.getAnnotations());
+
+					} catch (Exception e) {
+						throw new IciqlException(e);
+					}
+
+				} else if (indexedArg.field != null) {
+
+					// extract the field value
+					try {
+
+						value = indexedArg.field.get(methodArg);
+						typeAdapter = Utils.getDataTypeAdapter(indexedArg.field.getAnnotations());
+
+					} catch (Exception e) {
+						throw new IciqlException(e);
+					}
+
+				} else if (typeAdapter == null) {
+
+					// identify the type adapter for the argument class
+					typeAdapter = Utils.getDataTypeAdapter(methodArg.getClass().getAnnotations());
+				}
+
+				// prepare the parameter
+				parameters[i] = prepareParameter(db, value, typeAdapter);
+
+			}
+
+			return new PreparedSql(sql, parameters);
+
+		}
+
+		/**
+		 * Prepares a method argument to an sql parameter for transmission to a
+		 * database.
+		 *
+		 * @param db
+		 * @param arg
+		 * @param typeAdapter
+		 * @return a prepared parameter
+		 */
+		private Object prepareParameter(Db db, Object arg, Class<? extends DataTypeAdapter<?>> typeAdapter) {
+
+			if (typeAdapter != null) {
+
+				// use a type adapter to serialize the method argument
+				Object o = db.getDialect().serialize(arg, typeAdapter);
+				return o;
+
+			} else {
+
+				// use the method argument
+				return arg;
+
+			}
+
+		}
+
+		@Override
+		public String toString() {
+			return sql;
+		}
+	}
+
+	/**
+	 * IndexedArgument holds cached information about how to process an method
+	 * argument by it's index in the method arguments array.
+	 * <p>
+	 * An argument may be passed-through, might be bound to a bean property,
+	 * might be transformed with a type adapter, or a combination of these.
+	 * </p>
+	 */
+	private class IndexedArgument {
+		final int index;
+		final Class<? extends DataTypeAdapter<?>> typeAdapter;
+		final Method method;
+		final Field field;
+
+		IndexedArgument(int index, Class<? extends DataTypeAdapter<?>> typeAdapter) {
+			this.index = index;
+			this.typeAdapter = typeAdapter;
+			this.method = null;
+			this.field = null;
+		}
+
+		IndexedArgument(int methodArgIndex, Method method) {
+			this.index = methodArgIndex;
+			this.typeAdapter = null;
+			this.method = method;
+			this.field = null;
+		}
+
+		IndexedArgument(int methodArgIndex, Field field) {
+			this.index = methodArgIndex;
+			this.typeAdapter = null;
+			this.method = null;
+			this.field = field;
+		}
+
+		@Override
+		public String toString() {
+
+			String accessor;
+			if (method != null) {
+				accessor = "M:" + method.getDeclaringClass().getSimpleName() + "." + method.getName();
+			} else if (field != null) {
+				accessor = "F:" + field.getDeclaringClass().getSimpleName() + "." + field.getName();
+			} else {
+				accessor = "A:arg";
+			}
+
+			return index + ":" + accessor + (typeAdapter == null ? "" : (":" + typeAdapter.getSimpleName()));
+		}
+
+	}
+
+}
diff --git a/src/main/java/com/iciql/Db.java b/src/main/java/com/iciql/Db.java
index ca43e63..13c9260 100644
--- a/src/main/java/com/iciql/Db.java
+++ b/src/main/java/com/iciql/Db.java
@@ -194,7 +194,17 @@ public class Db implements AutoCloseable {
 		return new Db(conn);
 	}
 
-
+	/**
+	 * Returns a new DAO instance for the specified class.
+	 *
+	 * @param daoClass
+	 * @return
+	 * @throws Exception
+	 */
+	@SuppressWarnings("resource")
+	public <X extends Dao> X open(Class<X> daoClass) {
+		return new DaoProxy<X>(this, daoClass).buildProxy();
+	}
 
 	/**
 	 * Convenience function to avoid import statements in application code.
@@ -675,7 +685,7 @@ public class Db implements AutoCloseable {
 	 */
 	public ResultSet executeQuery(String sql, Object... args) {
 		try {
-			if (args.length == 0) {
+			if (args == null || args.length == 0) {
 				return conn.createStatement().executeQuery(sql);
 			} else {
 				PreparedStatement stat = conn.prepareStatement(sql);
@@ -717,7 +727,7 @@ public class Db implements AutoCloseable {
 	public <T> List<T> executeQuery(Class<? extends T> modelClass, String sql, Object... args) {
 		ResultSet rs = null;
 		try {
-			if (args.length == 0) {
+			if (args == null || args.length == 0) {
 				rs = conn.createStatement().executeQuery(sql);
 			} else {
 				PreparedStatement stat = conn.prepareStatement(sql);
@@ -748,7 +758,7 @@ public class Db implements AutoCloseable {
 		Statement stat = null;
 		try {
 			int updateCount;
-			if (args.length == 0) {
+			if (args == null || args.length == 0) {
 				stat = conn.createStatement();
 				updateCount = stat.executeUpdate(sql);
 			} else {
diff --git a/src/site/dao.mkd b/src/site/dao.mkd
new file mode 100644
index 0000000..660c7af
--- /dev/null
+++ b/src/site/dao.mkd
@@ -0,0 +1,137 @@
+## Data Access Object (DAO)

+

+[JDBI](http://jdbi.org) brings an interesting feature to the table with dynamic generation of an annotation-based, partially type-safe DAO.  This is a great idea and one that Iciql has absorbed into it's featureset.

+

+The Iciql implementation is quite different, but the usage is very similar.  Iciql does not aim to recreate all features and capabilities of JDBI's DAO.

+

+### Instantiating a DAO

+

+Once you have a Db instance, you may generate a dynamic DAO instance which is backed by it.

+

+---JAVA---

+Db db = Db.open("jdbc:h2:mem:iciql");

+db.open(MyDao.class);

+---JAVA---

+

+A minimal DAO is an *interface* that extends the `Dao` interface. This gives your DAO instance access to the standard Iciql CRUD methods for interacting with your database models, the `db()` method to retrieve the underlying db instance, and the `close()` method for closing the underlying JDBC connection.

+

+---JAVA---

+public interface MyDao extends Dao {

+}

+---JAVA---

+

+Your `Dao` instance is also auto-closable so you may use the Java 7 try-with-resources syntax.

+

+**Note:** You never implement the DAO methods - that is taken care of for you through the magic of `java.lang.reflect.Proxy` and `com.iciql.DaoProxy`.

+

+### @SqlQuery

+

+DAO queries are method declarations annotated with `@SqlQuery`.

+

+#### Return types

+

+1. An `@SqlQuery` method must specify a non-void return a type.

+2. The return type may not be a `java.util.Collection`, but it may be an array [] type. This is due to generic type erasure by javac whereas arrays preserve their component type information.

+**NOTE:** Iciql will always return a 0-length array instead of a null when there are no results so you won't have to worry about null checks.

+3. An `@SqlQuery` method may specify a data type adapter using the `@TypeAdapter` annotation if the returned value is a field, not a row.

+

+##### Returning a field with @TypeAdapter

+

+Normally, Iciql will map the fields in a query ResultSet to your return type object.  However, if you are querying a single field from a table then you may specify a `@TypeAdapter` on an `@SqlQuery` method allowing you to deserialize complex data into an object.

+

+For example, if you are using the Postgres JSON/JSONB column type in your table then you might want to directly deserialize the raw JSON stored in Postgres into an object rather than just retrieving the JSON document and manually transforming it.  You can use a `@TypeAdapter` to perform this work for you.

+

+#### Method Argument->Statement Parameter mapping

+

+`@SqlQuery` supports 6 techniques for mapping method arguments to statement parameters.

+

+1. `:?` where the method argument order implicitly determines statement parameter order. This is similar to a PreparedStatement.

+2. `:arg0` where you specify the 0-based index of the method argument.

+3. `:1` where you specify the 1-based index of the method argument.

+4. `:name` automatic Java 8 method parameter naming, assuming you are compiling on Java 8 with the `-parameters` javac flag.

+5. `@Bind("name") + :name` argument annotation where you explicitly name the statement parameter.

+6. `@BindBean("prefix") + :prefix.property` argument annotation which flags the argument as a JavaBean. This allows you to access JavaBean properties from your statement.

+**NOTE:** If the prefix is empty, your JavaBean properties will be directly accessible. (e.g. `:property` not `:p.property`)

+

+#### Example @SqlQuery usage

+

+---JAVA---

+public interface MyDao extends Dao {

+

+    @SqlQuery("select * from Product")

+    Product [] getAllProducts();

+    

+    // Named parameters

+    @SqlQuery("select * from Product where productId = :id")

+    Product getProduct(@Bind("id") long id);

+    

+    // Reflection-style 0-indexed args

+    @SqlQuery("select * from Product where productId = :arg0")

+    Product getProduct2(long id);

+

+    // JDBC-style 1-indexed parameters

+    @SqlQuery("select * from Product where productId = :1")

+    Product getProduct2(long id);

+

+    // If you are compiling on Java 8 with -parameters

+    @SqlQuery("select * from Product where productId = :id")

+    Product getProduct2(long id);

+    

+    // demonstrates how to use bean binding

+    @SqlQuery("select productId from Product where category = :p.category and unitsInStock >= :p.unitsInStock")

+    long [] getSimilarInStockItemIds(@BindBean("p") Product p);

+

+    // You can extract a field with full standard type mapping

+    @SqlQuery("select orderDate from Orders order by orderDate desc limit 1")

+    Date getMostRecentOrderDate();

+    

+    // You can extract a field that requires a data type adapter (e.g. a Postgres JSON/JSONB, BLOB, etc)

+    @SqlQuery("select invoice from Invoices order by received desc limit 1")

+    @TypeAdapter(InvoiceAdapterImpl.class)

+    Invoice getMostRecentInvoice();

+

+}

+---JAVA---

+

+### @SqlStatement

+

+DAO statements are method declarations annotated with `@SqlStatement`.

+

+#### Return types

+

+Statements to now return a ResultSet so `@SqlStatement` methods have three acceptable return types:

+

+1. *void*

+2. *boolean*, if the affected row count is non-zero, true is returned, otherwise false

+3. *int*, returns the affected row count

+

+`@TypeAdapter` may not be annotated on a `@SqlStatement` method.  However it may be used on the method arguments.

+

+#### Method Argument->Statement Parameter mapping

+

+The parameter mapping rules are exactly the same as for `@SqlQuery`.

+

+#### Example @SqlStatement usage

+

+---JAVA---

+public interface MyDao extends Dao {

+

+    // this statement does not return anything

+    @SqlStatement("update Product set productName = :name where productId = :id")

+    void setProductName(@Bind("id") long id, @Bind("name") String name);

+

+    // this statement returns true if at least one row was affected

+    @SqlStatement("update Product set productName = :name where productId = :id")

+    boolean renameProduct(@Bind("id") long id, @Bind("name") String name);

+

+    // this statement returns the number of affected rows

+    @SqlStatement("update Product set category = :new where category = :old")

+    int renameProductCategory(@Bind("old") String oldCategory, @Bind("new") String newCategory);

+    

+    // You can update a field that requires a data type adapter

+    @SqlStatement("update Invoices set invoice = :2 where id = :1")

+    boolean setInvoice(long id, @TypeAdapter(InvoiceAdapterImpl.class) Invoice invoice);

+

+}

+---JAVA---

+

diff --git a/src/site/examples.mkd b/src/site/examples.mkd
index d8d3dfd..21dd773 100644
--- a/src/site/examples.mkd
+++ b/src/site/examples.mkd
@@ -1,4 +1,8 @@
-## Select Statements

+## SQL DSL Examples

+

+Here are some examples of using the Iciql SQL DSL.

+

+### Select Statements

 

 ---JAVA---

 // select * from products

@@ -25,7 +29,7 @@ List<ProductPrice> productPrices =
     }});

 ---JAVA---

 

-## Insert Statements

+### Insert Statements

 

 ---JAVA---

 // single record insertion

@@ -41,7 +45,7 @@ db.insertAll(myProducts);
 List<Long> myKeys = db.insertAllAndGetKeys(list);

 ---JAVA---

 

-## Update Statements

+### Update Statements

 

 ---JAVA---

 // single record update

@@ -61,8 +65,9 @@ String q = db.from(p).set(p.productName).toParameter().where(p.productId).is(1).
 db.executeUpdate(q, "Lettuce");

 ---JAVA---

 

-## Merge Statements

-Merge statements currently generate the [H2 merge syntax](http://h2database.com/html/grammar.html#merge).

+### Upsert/Merge Statements

+

+The Upsert or Merge methods will insert a new object if the primary key does not already exist or will update the record for the primary key.

  

 ---JAVA---

 Product pChang = db.from(p).where(p.productName).is("Chang").selectFirst();

@@ -71,7 +76,7 @@ pChang.unitsInStock = 16;
 db.merge(pChang);

 ---JAVA---

 

-## Delete Statements

+### Delete Statements

 

 ---JAVA---

 // single record deletion

@@ -84,7 +89,7 @@ db.deleteAll(myProducts);
 db.from(p).where(p.productId).atLeast(10).delete();

 ---JAVA---

 

-## Inner Join Statements

+### Inner Join Statements

 

 ---JAVA---

 final Customer c = new Customer();

@@ -108,7 +113,7 @@ List<CustOrder> orders =
     }});

 ---JAVA---

 

-## View Statements

+### View Statements

 

 ---JAVA---

 // the view named "ProductView" is created from the "Products" table

@@ -162,7 +167,7 @@ db.from(p).where(p.id).atLeast(250L).and(p.id).lessThan(350L).replaceView(Produc
 db.dropView(ProductViewInherited.class);

 ---JAVA---

 

-## Dynamic Queries

+### Dynamic Queries

 

 Dynamic queries skip all field type checking and, depending on which approach you use, may skip model class/table name checking too.

 

diff --git a/src/site/index.mkd b/src/site/index.mkd
index bddebd7..a79233c 100644
--- a/src/site/index.mkd
+++ b/src/site/index.mkd
@@ -5,9 +5,9 @@ iciql **is**...
 - a model-based, database access wrapper for JDBC

 - for modest database schemas and basic statement generation

 - for those who want to write code, instead of SQL, using IDE completion and compile-time type-safety

-- small (200KB) with debug symbols and no runtime dependencies

+- small (225KB) with debug symbols and no runtime dependencies

 - pronounced *icicle* (although it could be French: *ici ql* - here query language)

-- a friendly fork of the H2 [JaQu][jaqu] project

+- a friendly fork of the H2 [JaQu][jaqu] subproject

 

 iciql **is not**...

 

@@ -15,26 +15,73 @@ iciql **is not**...
 - designed to compete with more powerful database query tools like [jOOQ][jooq] or [QueryDSL][querydsl]

 - designed to compete with enterprise [ORM][orm] tools like [Hibernate][hibernate] or [mybatis][mybatis]

 

-### Example Usage

-<table class="table">

-<tr>

-<th>iciql</th><th>sql</th>

-</tr>

-<tr>

-<td>

+### fluent, type-safe SQL DSL with rich object mapping

+

+Born from the unfinished [JaQu][jaqu] subproject of H2 in August 2011, Iciql has advanced the codebase & DSL greatly.  It supports more SQL syntax, more SQL data types, and all standard JDBC object types.

+

 ---JAVA---

-Product p = new Product();

-List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();

-List<Product> all = db.executeQuery(Product.class, "select * from products");

+try (Db db = Db.open("jdbc:h2:mem:iciql")) {

+

+    db.insertAll(Product.getList());

+    Product p = new Product();

+    List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();

+    List<Product> all = db.executeQuery(Product.class, "select * from products");

+    

+}

 ---JAVA---

 

-</td><td>

-<br/>

-select * from products p where p.unitsInStock = 0<br/>

-select * from products

-</td>

-</tr>

-</table>

+### dynamic, annotated DAO with standard crud operations

+

+Inspired by [JDBI](http://jdbi.org), Iciql offers a similar Dao feature.  There are some clear benefits to using SQL directly rather than SQL-through-a-DSL so use them both where it makes the most sense for your need.

+

+---JAVA---

+// Define your DAO with SQL annotations and optional type adapters

+public interface MyDao extends Dao {

+

+    @SqlQuery("select * from Product where unitsInStock = 0")

+    Product[] getProductsOutOfStock();

+

+    @SqlQuery("select * from Product where productId = :id")

+    Product getProduct(@Bind("id") long id);

+ 

+    // retrieve a custom type from the matched row in the Invoices table

+    @SqlQuery("select invoice from Invoices where id = :arg0")

+    @InvoiceAdapter

+    Invoice getInvoice(long id);

+

+    // retrieve a custom type from the matched row in the Invoices table

+    @SqlQuery("select invoice from Invoices where id = :p.invoiceId")

+    @InvoiceAdapter

+    Invoice getInvoice(@BindBean("p") Product product);

+

+    // update a custom type for the matched row in the Invoices table

+    @SqlStatement("update Invoices set invoice = :2 where id = :1")

+    boolean updateInvoice(long id, @InvoiceAdapter Invoice invoice);

+

+}

+

+// Define a type adapter annotation for the Invoice object

+@Retention(RetentionPolicy.RUNTIME)

+@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.PARAMETER })

+@TypeAdapter(InvoiceAdapterImpl.class)

+public @interface InvoiceAdapter { }

+

+// Crate a DAO instance with your Db and work more clearly

+try (Db db = Db.open("jdbc:h2:mem:iciql")) {

+

+    MyDao dao = db.open(MyDao.class);

+    dao.insertAll(Product.getList());

+    Product[] outofstock = dao.getProductsOutOfStock();

+    Product p = dao.getProduct(1);

+    Invoice i123 = dao.getInvoice(123);

+    i123.approved = true;

+    dao.updateInvoice(123, i123);

+    

+    // use the underlying Db instance for full-power

+    dao.db().dropTable(Product.class);

+

+}

+---JAVA---

 

 ### Supported Databases (Unit-Tested)

 - [H2](http://h2database.com) ${h2.version}

diff --git a/src/site/jaqu_comparison.mkd b/src/site/jaqu_comparison.mkd
index e7afbf8..2740517 100644
--- a/src/site/jaqu_comparison.mkd
+++ b/src/site/jaqu_comparison.mkd
@@ -20,6 +20,7 @@ This is an overview of the fundamental differences between the original JaQu pro
 <tr><td>BETWEEN</td><td>syntax for specifying a BETWEEN x AND y clause</td><td>--</td></tr>

 <tr><td>(NOT) IN</td><td>syntax (oneOf, noneOf) for specifying a (NOT) IN clause</td><td>--</td></tr>

 <tr><td>compound nested conditions</td><td>WHERE (x = y OR x = z) AND (y = a OR y = b)</td><td>--</td></tr>

+<tr><td>dynamic DAOs</td><td>DAO interfaces with annotated statements may be declared and dynamically generated</td><td>--</td></tr>

 <tr><th colspan="3">types</th></tr>

 <tr><td>primitives</td><td>fully supported</td><td>--</td></tr>

 <tr><td>enums</td><td>fully supported</td><td>--</td></tr>

diff --git a/src/site/model_classes.mkd b/src/site/model_classes.mkd
index 747c094..8c29782 100644
--- a/src/site/model_classes.mkd
+++ b/src/site/model_classes.mkd
@@ -1,4 +1,4 @@
-## Model Classes

+## Table Model Classes

 A model class represents a single table within your database.  Fields within your model class represent columns in the table.  The object types of your fields are reflectively mapped to SQL types by iciql at runtime.

 

 Models can be manually written using one of three approaches: *annotation configuration*, *interface configuration*, or *POJO configuration*.  All approaches can be used within a project and all can be used within a single model class, although that is discouraged.

@@ -13,7 +13,7 @@ Alternatively, model classes can be automatically generated by iciql using the m
 4. Only the specified types are supported.  Any other types are not supported.

 5. Triggers, views, and other advanced database features are not supported.

 

-### Supported Data Types

+### Standard Supported Data Types

 

 ---NOMARKDOWN---

 <table class="table">

diff --git a/src/site/table_versioning.mkd b/src/site/table_versioning.mkd
index 2e95aaa..480dd22 100644
--- a/src/site/table_versioning.mkd
+++ b/src/site/table_versioning.mkd
@@ -26,4 +26,34 @@ both of which allow for non-linear upgrades.  If the upgrade method call is succ
 The actual upgrade procedure is beyond the scope of iciql and is your responsibility to implement.  This is simply a mechanism to automatically identify when an upgrade is necessary.

 

 **NOTE:**<br/>

-The database entry of the *iq_versions* table is specified as SCHEMANAME='' and TABLENAME=''.
\ No newline at end of file
+The database entry of the *iq_versions* table is specified as SCHEMANAME='' and TABLENAME=''.

+

+### Effective use of Versioning with a DAO

+

+When Iciql identifies that a version upgrade is necessary it will call the appropriate method and give you a `Db` instance.  With the `Db` instance you may open a version-specific [DAO](dao.html) instance that could give you a clean way to define all your upgrade commands.

+

+---JAVA---

+public interface V2Upgrade extends Dao {

+

+    @SqlStatement("ALTER TABLE PRODUCT ADD COLUMN TEST INT DEFAULT 0")

+    void updateProductTable();

+    

+    @SqlStatement("UPDATE PRODUCT SET CATEGORY = :new WHERE CATEGORY = :old"")

+    void renameCategory(@Bind("old") String oldName, @Bind("new") String newName);

+}

+

+public class MyUpgrader implements DbUpgrader {

+

+    public boolean upgradeDatabase(Db db, int fromVersion, int toVersion) {

+    

+        if (2 == toVersion) {

+            V2Upgrade dao = db.open(V2Upgrade.class);

+            dao.updateProductTable();

+            dao.renameCategory("Condiments", "Dressings");

+            return true;

+        }

+        

+        return false;

+    }

+

+---JAVA---
\ No newline at end of file
diff --git a/src/site/usage.mkd b/src/site/usage.mkd
index 21c262e..b714512 100644
--- a/src/site/usage.mkd
+++ b/src/site/usage.mkd
@@ -1,15 +1,17 @@
-## Usage

+## SQL DSL Usage

 

-Aside from this brief usage guide, please consult the [examples](examples.html), the [javadoc](javadoc.html) and the [source code](${project.scmUrl}).

+Aside from this brief usage guide, please consult the [DSL examples](examples.html), the [javadoc](javadoc.html) and the [source code](${project.scmUrl}).

 

 ### Instantiating a Db

 

 Use one of the static utility methods to instantiate a Db instance:

 

-    Db.open(String url, String user, String password);

-    Db.open(String url, String user, char[] password);

-    Db.open(Connection conn);

-    Db.open(DataSource dataSource);

+---JAVA---

+Db.open(String url, String user, String password);

+Db.open(String url, String user, char[] password);

+Db.open(Connection conn);

+Db.open(DataSource dataSource);

+---JAVA---

  

 ### Compile-Time Statements

 

@@ -144,11 +146,12 @@ products = db.from(view).select();
 

 ### Natural Syntax

 

-<span class="warning">work-in-progress</span>

+<span class="alert alert-warning">Not Actively Developed</span>

 

 The original JaQu source offers partial support for Java expressions in *where* clauses.

 

 This works by decompiling a Java expression, at runtime, to an SQL condition.  The expression is written as an anonymous inner class implementation of the `com.iciql.Filter` interface.

+

 A proof-of-concept decompiler is included, but is incomplete.

 

 The proposed syntax is:

diff --git a/src/test/java/com/iciql/test/IciqlSuite.java b/src/test/java/com/iciql/test/IciqlSuite.java
index 4800b9a..c088ff9 100644
--- a/src/test/java/com/iciql/test/IciqlSuite.java
+++ b/src/test/java/com/iciql/test/IciqlSuite.java
@@ -95,7 +95,7 @@ import com.iciql.util.Utils;
 		ConcurrencyTest.class, EnumsTest.class, ModelsTest.class, PrimitivesTest.class, OneOfTest.class,

 		RuntimeQueryTest.class, SamplesTest.class, UpdateTest.class, UpgradesTest.class, JoinTest.class,

 		UUIDTest.class, ViewsTest.class, ForeignKeyTest.class, TransactionTest.class, NestedConditionsTest.class,

-		DataTypeAdapterTest.class })

+		DataTypeAdapterTest.class, ProductDaoTest.class })

 public class IciqlSuite {

 	private final static File baseFolder = new File(System.getProperty("user.dir"), "/testdbs");

 	private static final TestDb[] TEST_DBS = {

diff --git a/src/test/java/com/iciql/test/ProductDaoTest.java b/src/test/java/com/iciql/test/ProductDaoTest.java
new file mode 100644
index 0000000..78987fd
--- /dev/null
+++ b/src/test/java/com/iciql/test/ProductDaoTest.java
@@ -0,0 +1,346 @@
+/*
+ * Copyright 2014 James Moger.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.iciql.test;
+
+import java.sql.Date;
+import java.util.Arrays;
+import java.util.List;
+
+import org.junit.After;
+import org.junit.Assert;
+import org.junit.Before;
+import org.junit.Test;
+
+import com.iciql.Dao;
+import com.iciql.Db;
+import com.iciql.IciqlException;
+import com.iciql.test.DataTypeAdapterTest.SerializedObjectTypeAdapterTest;
+import com.iciql.test.DataTypeAdapterTest.SupportedTypesAdapter;
+import com.iciql.test.models.Order;
+import com.iciql.test.models.Product;
+import com.iciql.test.models.SupportedTypes;
+
+/**
+ * Tests DAO dynamic proxy mechanism.
+ *
+ * @author James Moger
+ */
+public class ProductDaoTest extends Assert {
+
+	private Db db;
+
+	@Before
+	public void setUp() throws Exception {
+		db = IciqlSuite.openNewDb();
+		db.insertAll(Product.getList());
+		db.insertAll(Order.getList());
+	}
+
+	@After
+	public void tearDown() {
+		db.close();
+	}
+
+	@Test
+	public void testQueryVoidReturnType() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		try {
+			dao.getWithIllegalVoid();
+			assertTrue("void return type on a query should fail", false);
+		} catch (IciqlException e) {
+			assertTrue(true);
+		}
+	}
+
+	@Test
+	public void testQueryCollectionReturnType() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		try {
+			dao.getWithIllegalCollection();
+			assertTrue("collection return types on a query should fail", false);
+		} catch (IciqlException e) {
+			assertTrue(true);
+		}
+	}
+
+	@Test
+	public void testQueryIgnoreDoubleDelimiter() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		try {
+			dao.getWithDoubleDelimiter();
+			assertTrue("the double delimiter should have been ignored", false);
+		} catch (IciqlException e) {
+			assertTrue(true);
+		}
+
+	}
+
+	@Test
+	public void testQueryReturnModels() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		Product[] products = dao.getAllProducts();
+		assertEquals(10, products.length);
+	}
+
+	@Test
+	public void testQueryNamedOrIndexedParameterBinding() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		Product p2 = dao.getProduct(2);
+		assertEquals("Chang", p2.productName);
+
+		Product p3 = dao.getProductWithUnusedBoundParameters(true, 3, "test");
+		assertEquals("Aniseed Syrup", p3.productName);
+
+		Product p4 = dao.getProductWithUnboundParameters(true, 4, "test");
+		assertEquals("Chef Anton's Cajun Seasoning", p4.productName);
+
+		Product p5 = dao.getProductWithUnboundParameters(true, 5, "test");
+		assertEquals("Chef Anton's Gumbo Mix", p5.productName);
+
+		// test re-use of IndexedSql (manual check with debugger)
+		Product p6 = dao.getProduct(6);
+		assertEquals("Grandma's Boysenberry Spread", p6.productName);
+
+	}
+
+	@Test
+	public void testJDBCPlaceholderParameterBinding() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		Product p2 = dao.getProductWithJDBCPlaceholders(2);
+		assertEquals("Chang", p2.productName);
+
+	}
+
+	@Test
+	public void testQueryBeanBinding() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		Product p4 = dao.getProduct(4);
+
+		long [] products = dao.getSimilarInStockItemIds(p4);
+
+		assertEquals("[6]", Arrays.toString(products));
+
+	}
+
+	@Test
+	public void testQueryReturnField() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		String n5 = dao.getProductName(5);
+		assertEquals("Chef Anton's Gumbo Mix", n5);
+
+		int u4 = dao.getUnitsInStock(4);
+		assertEquals(53, u4);
+
+	}
+
+	@Test
+	public void testQueryReturnFields() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		long [] ids = dao.getProductIdsForCategory("Condiments");
+		assertEquals("[3, 4, 5, 6, 8]", Arrays.toString(ids));
+
+		Date date = dao.getMostRecentOrder();
+		assertEquals("2007-04-11", date.toString());
+
+	}
+
+	@Test
+	public void testUpdateIllegalReturnType() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		try {
+			dao.setWithIllegalReturnType();
+			assertTrue("this should have been an illegal return type", false);
+		} catch (IciqlException e) {
+			assertTrue(true);
+		}
+
+	}
+
+	@Test
+	public void testUpdateStatements() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		Product p1 = dao.getProduct(1);
+		assertEquals("Chai", p1.productName);
+
+		String name = "Tea";
+		dao.setProductName(1, name);
+
+		Product p2 = dao.getProduct(1);
+
+		assertEquals(name, p2.productName);
+
+	}
+
+	@Test
+	public void testUpdateStatementsReturnsSuccess() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		boolean success = dao.setProductNameReturnsSuccess(1, "Tea");
+		assertTrue(success);
+
+	}
+
+	@Test
+	public void testUpdateStatementsReturnsCount() {
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		int rows = dao.renameProductCategoryReturnsCount("Condiments", "Garnishes");
+		assertEquals(5, rows);
+
+	}
+
+	@Test
+	public void testQueryWithDataTypeAdapter() {
+
+		// insert our custom serialized object
+		SerializedObjectTypeAdapterTest row = new SerializedObjectTypeAdapterTest();
+		row.received = new java.util.Date();
+		row.obj = SupportedTypes.createList().get(1);
+		db.insert(row);
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		// retrieve our object with automatic data type conversion
+		SupportedTypes obj = dao.getCustomDataType();
+		assertNotNull(obj);
+		assertTrue(row.obj.equivalentTo(obj));
+	}
+
+	@Test
+	public void testUpdateWithDataTypeAdapter() {
+
+		// insert our custom serialized object
+		SerializedObjectTypeAdapterTest row = new SerializedObjectTypeAdapterTest();
+		row.received = new java.util.Date();
+		row.obj = SupportedTypes.createList().get(1);
+		db.insert(row);
+
+		ProductDao dao = db.open(ProductDao.class);
+
+		final SupportedTypes obj0 = dao.getCustomDataType();
+		assertNotNull(obj0);
+		assertTrue(row.obj.equivalentTo(obj0));
+
+		// update the stored object
+		final SupportedTypes obj1 = SupportedTypes.createList().get(1);
+		obj1.myString = "dta update successful";
+		dao.setSupportedTypes(1, obj1);
+
+		// retrieve and validate the update took place
+		final SupportedTypes obj2 = dao.getCustomDataType();
+
+		assertNotNull(obj2);
+		assertEquals("dta update successful", obj2.myString);
+
+		assertTrue(obj1.equivalentTo(obj2));
+	}
+
+	/**
+	 * Define the Product DAO interface.
+	 */
+	public interface ProductDao extends Dao {
+
+		@SqlQuery("select * from Product")
+		void getWithIllegalVoid();
+
+		@SqlQuery("select * from Product")
+		List<Product> getWithIllegalCollection();
+
+		@SqlQuery("select * from Product where ::id = 1")
+		Product getWithDoubleDelimiter();
+
+		@SqlQuery("select * from Product")
+		Product[] getAllProducts();
+
+		@SqlQuery("select * from Product where productId = :id")
+		Product getProduct(@Bind("id") long id);
+
+		@SqlQuery("select * from Product where productId = :id")
+		Product getProductWithUnusedBoundParameters(
+				@Bind("irrelevant") boolean whocares,
+				@Bind("id") long id,
+				@Bind("dontmatter") String something);
+
+		@SqlQuery("select * from Product where productId = :arg1")
+		Product getProductWithUnboundParameters(
+				boolean whocares,
+				long id,
+				String something);
+
+		@SqlQuery("select * from Product where productId = :?")
+		Product getProductWithJDBCPlaceholders(long id);
+
+		@SqlQuery("select productId from Product where unitsInStock > :p.unitsInStock and category = :p.category")
+		long[] getSimilarInStockItemIds(@BindBean("p") Product p);
+
+		@SqlQuery("select productName from Product where productId = :?")
+		String getProductName(long id);
+
+		@SqlQuery("select unitsInStock from Product where productId = :?")
+		int getUnitsInStock(long id);
+
+		@SqlQuery("select productId from Product where category = :category")
+		long[] getProductIdsForCategory(@Bind("category") String cat);
+
+		@SqlQuery("select orderDate from Orders order by orderDate desc limit 1")
+		Date getMostRecentOrder();
+
+		@SqlStatement("update Product set productName = 'test' where productId = 1")
+		String setWithIllegalReturnType();
+
+		@SqlStatement("update Product set productName = :name where productId = :id")
+		void setProductName(@Bind("id") long id, @Bind("name") String name);
+
+		@SqlStatement("update Product set productName = :name where productId = :id")
+		boolean setProductNameReturnsSuccess(@Bind("id") long id, @Bind("name") String name);
+
+		@SqlStatement("update Product set category = :newCategory where category = :oldCategory")
+		int renameProductCategoryReturnsCount(@Bind("oldCategory") String oldCategory, @Bind("newCategory") String newCategory);
+
+		@SqlQuery("select obj from dataTypeAdapters limit 1")
+		@SupportedTypesAdapter
+		SupportedTypes getCustomDataType();
+
+		@SqlStatement("update dataTypeAdapters set obj=:2 where id=:1")
+		boolean setSupportedTypes(long id, @SupportedTypesAdapter SupportedTypes obj);
+
+	}
+}
diff --git a/src/test/java/com/iciql/test/models/Product.java b/src/test/java/com/iciql/test/models/Product.java
index 241a3d3..7feb998 100644
--- a/src/test/java/com/iciql/test/models/Product.java
+++ b/src/test/java/com/iciql/test/models/Product.java
@@ -51,6 +51,15 @@ public class Product implements Iciql {
 		this.unitsInStock = unitsInStock;

 	}

 

+	public String getName() {

+		return productName;

+	}

+

+	public int getId() {

+		return productId;

+	}

+

+	@Override

 	public void defineIQ() {

 		tableName("Product");

 		primaryKey(productId);

@@ -78,6 +87,7 @@ public class Product implements Iciql {
 		return Arrays.asList(list);

 	}

 

+	@Override

 	public String toString() {

 		return productName + ": " + unitsInStock;

 	}

diff --git a/src/test/java/com/iciql/test/models/SupportedTypes.java b/src/test/java/com/iciql/test/models/SupportedTypes.java
index d9a6405..0383d7f 100644
--- a/src/test/java/com/iciql/test/models/SupportedTypes.java
+++ b/src/test/java/com/iciql/test/models/SupportedTypes.java
@@ -90,7 +90,7 @@ public class SupportedTypes implements Serializable {
 	private BigDecimal myBigDecimal;
 
 	@IQColumn(length = 40)
-	private String myString;
+	public String myString;
 
 	@IQColumn
 	private java.util.Date myUtilDate;