Make your own persistence FW (Java)

If you make full use of the template engine, Isn't it easy to create a persistent FW like MyBatis or Doma that has SQL in an external file? I thought and made it.

You can now get the data for ʻuser_mst` with just the following code.

selectById.sql

select *
from user_mst
where id = {{ p.bind(id) }}

user_mst Java code to get

SqlTemplate sqlTemplate = new SqlTemplate();
DbAccess dbAccess = new DbAccess(connection);//java.sql.Connection

Map<String, Object> context = new HashMap<>();
context.put("id", id);
QueryBuilder query = sqlTemplate.evaluate( "selectById.sql", context);
UserMst userMst = dbAccess.selectOne( query, UserMst.class );

This time the template engine uses Pebble. But I think you can probably do it with ʻApache Velocity, FreeMarker, or Mustache.java`. I think you can use your favorite template engine. The point is that you can call the method in the template. Working directly with JDBC is tedious, so use Apache Commons DBUtils. The value is set automatically by making full use of reflection.

build.groovy

// https://mvnrepository.com/artifact/com.mitchellbosecke/pebble
compile group: 'com.mitchellbosecke', name: 'pebble', version: '2.4.0'
// https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils
compile group: 'commons-dbutils', name: 'commons-dbutils', version: '1.7'

Template engine for SQL

/**Template engine for SQL*/
import com.mitchellbosecke.pebble.PebbleEngine;
import com.mitchellbosecke.pebble.template.PebbleTemplate;
import java.io.StringWriter;

public class SqlTemplate{
	private static PebbleEngine engine;
	/**Get template engine*/
	private PebbleEngine getEngine(){
		if( null == engine){
			engine = new PebbleEngine.Builder()
					.autoEscaping(false)//Remove html escape because it is superfluous
					.strictVariables(true)//Setting to raise an exception when trying to use a variable that is not in the context
					.build();
		}
		return engine;
	}

	/**Get query based on sql template name and template value*/
	public QueryBuilder evaluate(String templateFileName, Map<String, Object> context) throws Exception{
		SqlParametors sqlParametors = new SqlParametors();
		context.put( "p", sqlParametors );
		String result = simpleEvaluateTemplate(templateFileName, context );
		QueryBuilder query = new QueryBuilder( result, sqlParametors.getParams() );
		return query;
	}

	/**A method that just evaluates the template and returns the resulting string*/
	private String simpleEvaluateTemplate(String templateFileName, Map<String, Object> context) throws Exception{
		StringWriter writer = new StringWriter();
		PebbleTemplate template = getEngine().getTemplate(templateFileName);
		template.evaluate(writer, context);
		return writer.toString();
	}
}

sql parameters

import java.util.List;

/**sql parameter storage class.Access within the template engine.*/
public class SqlParametors {
	/**parameter*/
	private List<Object> params = new ArrayList<>();
	/**Bind parameters*/
	public String bind(Object param){
		params.add(param);
		return "?";
	}
	/**Parameter acquisition*/
	public List<Object> getParams(){
		return params;
	}
}

Query storage class

import java.util.List;
import java.util.Arrays;

/**Query storage class*/
public class QueryBuilder {
	/**sql statement*/
	private StringBuilder sql = new StringBuilder();
	/**parameter*/
	private List<Object> params = new ArrayList<>();

	public QueryBuilder(){
	}

	public QueryBuilder(CharSequence sql, List<Object>params){
		this.sql.append(sql);
		this.params.addAll(params);
	}

	public StringBuilder getSql() {
		return sql;
	}

	public void setSql(CharSequence sql) {
		this.sql = new StringBuilder( sql.toString() );
	}

	public Object[] getParams() {
		return params.toArray();
	}

	public void setParams(Object[] params) {
		this.params = Arrays.asList(params);
	}


}

DB access

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**DB access class. DB operation with QueryBuilder object as an argument*/
class DbAccess{
	/**Query execution(dbutils) */
	private QueryRunner queryRunner = new QueryRunner();

	/**connection*/
	private java.sql.Connection conn;

	/**constructor*/
	public DbAccess(java.sql.Connection conn){
		this.conn = conn;
	}

	/**For insert and update statements*/
	public int update(QueryBuilder query ) throws Exception{
		int rows = queryRunner.update(conn, query.getSql().toString(), query.getParams() );
		return rows;
	}

	/**For when you want to select and get the result in a list*/
	public <T> List<T>  selectList(QueryBuilder query, Class<T> clazz) throws Exception{
		ResultSetHandler<List<Map<String, Object>>> h = new MapListHandler();
		List<Map<String, Object>> mapList = queryRunner.query(conn, query.getSql().toString(), h, query.getParams() );
		List<T> beanList = convMapList2BeanList(mapList, clazz);
		return beanList;
	}

	/**For when you want to select and get only one line of the result*/
	public <T> T selectOne(QueryBuilder query, Class<T> clazz) throws Exception{
		ResultSetHandler<Map<String, Object>> h = new MapHandler();
		Map<String, Object> map = queryRunner.query(conn, query.getSql().toString(), h ,query.getParams());
		if( null == map )
			return null;
		T bean = convMap2Bean(map, clazz );
		return bean;
	}

	/**For when you want to select and get only one column of the result*/
	public <T> T selectOneColumn(QueryBuilder query, Class<T> clazz) throws Exception{
		Object value = queryRunner.query(conn, query.getSql().toString(), new ScalarHandler<T>(), query.getParams() );
		return clazz.cast(value);
	}
	
	/**
	 *Set Map object list to Bean list.
	 * @param mapList List of Map objects
	 * @param clazz Set destination Bean class
	 * @throws Exception
	 */
	private <T> List<T> convMapList2BeanList(List<Map<String, Object>> mapList, Class<T> clazz) throws Exception{
		List<T> list = new ArrayList<>();
		for( int i = 0; i < mapList.size(); i++ ){
			list.add( convMap2Bean(mapList.get(i), clazz) );
		}
		return list;
	}

	/**
	 *Set Map object to Bean.
	 * @param map Map object
	 * @param clazz Set destination Bean class
	 * @throws Exception
	 */
	private <T> T convMap2Bean(Map<String, ?> map, Class<T> clazz) throws Exception{
		T bean = clazz.newInstance();
		//Get field list
		Field[] fields = clazz.getDeclaredFields();

		for( Field f : fields ){
			f.setAccessible(true);
			f.set(bean, map.get(f.getName()) );//TODO Snake case => It is okay to include conversion processing to camel.
		}
		return bean;
	}
}