A simple Java QueryBuilder for Play! Framework with support for SELECT, INSERT, UPDATE and DELETE statements. Useful for interface with database without ORM support (like Hibernate, JPA, etc.).

QueryBuilder.java

/**
 * Simple Java query builder for Play! Framework 
 *
 * Author: Daniele Napolitano <dnax88@gmail.com>
 *
 * Features:
 * 
 * Support for SELECT, INSERT, UPDATE and DELETE statements.
 * No support for multiple tables, JOINs, GROUP BY
 * WHEREs concatenated only with AND
 *
 * Use of base ResultSet object from JDBC API
 *
 * Examples:
 * 
 * db.insertInto("users")
 *   .addField("name", "john")
 *   .addField("password", "changeme")
 *   .addField("email", "john@example.com")
 * .query();
 * 
 * db.update("users")
 *   .addField("creation", new Date())
 *   .addWhere("name", "john")
 * .query();
 * 
 * db.delete("users")
 *   .addWhere("email", "spam@example.com")
 * .query();
 * 
 * ResultSet results = db.select("users")
 *                       .addWhere("name", "Catherine")
 *                       .addWhere("password", "123")
 *                     .query(); 
 * 
 **/

package lib.utility;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.postgresql.util.PSQLException;

import play.Logger;
import play.db.helper.SqlSelect;

import com.google.gson.JsonElement;

public class CustomJPA {
    
    public static final String FG_BLUE      = (char)27 + "[0;34m";
    public static final String FG_GREEN     = (char)27 + "[0;32m";
    public static final String FG_BROWN     = (char)27 + "[0;33m";
    public static final String FG_YELLOW    = (char)27 + "[1;33m";
    public static final String FG_DEFAULT   = (char)27 + "[0;0m";
    public static final String FG_WHITE     = (char)27 + "[1;37m";
    
    private Connection conn = null;

    private String tableName = null;

    private String operation = null;

    private String sqlQuery = "";
    
    private Object lastInseredId = 0;

    private HashMap<String, Object> params = null;

    private HashMap<String, Object> wheres = null;

    private ResultSet lastResults = null;

    private String autoIDField = "";
    
    private int limit = 0;

    public CustomJPA(Connection conn) {

        this.conn = conn;
        params = new HashMap<String, Object>();
        wheres = new HashMap<String, Object>();
    }

    public CustomJPA insertInto(String tableName) {

        if (operation == null) {
            this.operation = "insert";
            this.tableName = tableName;
        }

        return this;
    }

    public CustomJPA update(String tableName) {

        if (operation == null) {
            this.operation = "update";
            this.tableName = tableName;
        }

        return this;
    }

    public CustomJPA select(String tableName) {

        if (operation == null) {
            this.operation = "select";
            this.tableName = tableName;
        }

        return this;
    }
    
    public CustomJPA delete(String tableName) {

        if (operation == null) {
            this.operation = "delete";
            this.tableName = tableName;
        }

        return this;
    }
    
    public CustomJPA addFieldNull(String field) {

        params.put(field, null);
        return this;
    }
    
    public CustomJPA addField(String field, JsonElement value) {
        
        if(value == null) {
            addFieldNull(field);
            return this;
        }
        
        if(value.getAsJsonPrimitive().isNumber())
            addField(field, value.getAsLong());
        
        if(value.getAsJsonPrimitive().isString())
            addField(field, value.getAsString());

        return this;
    }
    
    public CustomJPA addField(String field, String value) {

        params.put(field, value);
        return this;
    }

    public CustomJPA addField(String field, long value) {

        params.put(field, Long.valueOf(value));
        return this;
    }
    
    public CustomJPA addField(String field, Date value) {

        params.put(field, value);
        return this;
    }
    
    public CustomJPA addWhereNull(String field) {

        wheres.put(field, null);
        return this;
    }
    
    public CustomJPA addWhere(String field, JsonElement value) {
        
        if(value == null) {
            addWhereNull(field);
            return this;
        }
        
        if(value.getAsJsonPrimitive().isNumber())
            addWhere(field, value.getAsLong());
        
        if(value.getAsJsonPrimitive().isString())
            addWhere(field, value.getAsString());

        return this;
    }

    public CustomJPA addWhere(String field, String value) {

        wheres.put(field, value);
        return this;
    }

    public CustomJPA addWhere(String field, long value) {

        wheres.put(field, Long.valueOf(value));
        return this;
    }

    public CustomJPA setAutoIDField(String field) {

        autoIDField = field;
        return this;
    }
    
    public CustomJPA limit(int value) {
        limit = value;
        return this;
    }
    
    public void startTransaction() throws SQLException {
       
        conn.setAutoCommit(false);
        
        Statement statement = conn.createStatement();
        statement.execute("START TRANSACTION");
    }
    
    public void commit() throws SQLException {
        
        //conn.commit();
        Statement statement = conn.createStatement();
        statement.execute("COMMIT");
    }
    
    public void rollback() throws SQLException {
        
        //conn.rollback();
        Statement statement = conn.createStatement();
        statement.execute("ROLLBACK");
    }

    public ResultSet query() throws SQLException {
        
        lastInseredId = 0;
        
        /*if(lastResults != null) {
            lastResults.close();
        }*/
        
        lastResults = null;
        
        Statement statement = conn.createStatement();

        if (operation.equals("insert")) {

            sqlQuery = makeInsertSQL();
            Logger.debug(FG_GREEN + "SQL: " + sqlQuery + FG_DEFAULT);
            statement.executeUpdate(sqlQuery, Statement.RETURN_GENERATED_KEYS);

        } else if (operation.equals("update")) {

            sqlQuery = makeUpdateSQL();
            Logger.debug(FG_GREEN + "SQL: " + sqlQuery + FG_DEFAULT);
            statement.executeUpdate(sqlQuery, Statement.RETURN_GENERATED_KEYS);

        } else if (operation.equals("select")) {

            sqlQuery = makeSelectSQL();
            Logger.debug(FG_GREEN + "SQL: " + sqlQuery + FG_DEFAULT);
            lastResults = statement.executeQuery(sqlQuery);

        } else if (operation.equals("delete")) {

            sqlQuery = makeDeleteSQL();
            Logger.debug(FG_GREEN + "SQL: " + sqlQuery + FG_DEFAULT);
            statement.execute(sqlQuery);
            lastResults = null;
        }
        
        if (operation.equals("insert")) {
            ResultSet keys = statement.getGeneratedKeys();
            
            if(keys.next()) {
                
                try {
                    lastInseredId = Long.valueOf(keys.getLong(1));
                } catch (PSQLException e) {
                    lastInseredId = keys.getString(1);
                }
            }
            
            keys.close();
        }
        
        tableName = null;
        operation = null;
        
        params.clear();
        wheres.clear();

        return lastResults;
    }
    
    private String getSqlFieldString(Object field) {
        
        String str = "";
        
        if (field == null)
            str = "NULL";
            
        else if (field.getClass() == String.class)
            str = SqlSelect.inlineParam(field);
        
        else if (field.getClass() == Date.class) {
            
            DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            str = "'" + formatter.format((Date) field) + "'";
            
        } else
            str = ((Long) field).toString();
        
        return str;
    }
    
    private String getSqlFieldOperatorString(Object field) {
        
        String str = "";
        
        if (field == null) {
            str = " IS ";
        } else {
            str = " = ";
        }
            
        return str;
    }

    private String makeInsertSQL() throws SQLException {
        
        Logger.debug("Inserting " + FG_YELLOW + "%s" + FG_DEFAULT + "...", this.tableName);

        String q = "";

        q = "INSERT INTO " + tableName + " (";
        q = q.concat(StringUtils.join(params.keySet(), ", "));

        if (!autoIDField.isEmpty())
            q = q.concat(", " + autoIDField);

        q = q.concat(") VALUES (");

        Iterator<Object> i = params.values().iterator();

        Object obj = i.next();

        q = q.concat(getSqlFieldString(obj));

        while (i.hasNext()) {

            obj = i.next();
            
            q = q.concat(", ");
            q = q.concat(getSqlFieldString(obj));
        }

        if (!autoIDField.isEmpty()) {

            Statement statement = conn.createStatement();
            
            Logger.debug("SELECT MAX(" + autoIDField + ") FROM " + tableName);

            statement.execute("SELECT MAX(" + autoIDField + ") FROM " + tableName);
            statement.getResultSet().next();
            
            q = q.concat(", " + (statement.getResultSet().getLong(1) + 1));
            
            lastInseredId = statement.getResultSet().getLong(1) + 1;
            autoIDField = "";
        }

        q = q.concat(");");
        
        return q;
    }

    private String makeUpdateSQL() {
        
        Logger.debug("Updating " + FG_YELLOW + "%s" + FG_DEFAULT + "...", this.tableName);

        String q = "";

        q = "UPDATE " + tableName + " SET ";

        Iterator<Map.Entry<String, Object>> i = params.entrySet().iterator();

        Map.Entry<String, Object> entry = i.next();
        
        q = q.concat(entry.getKey() + " = ");
        q = q.concat(getSqlFieldString(entry.getValue()));

        while (i.hasNext()) {

            entry = i.next();
            
            q = q.concat(", " + entry.getKey() + " = ");
            q = q.concat(getSqlFieldString(entry.getValue()));
        }

        // WHERE

        i = wheres.entrySet().iterator();

        Map.Entry<String, Object> where = i.next();

        q = q.concat(" WHERE " + where.getKey() + getSqlFieldOperatorString(where.getValue()));
        q = q.concat(getSqlFieldString(where.getValue()));

        while (i.hasNext()) {

            where = i.next();

            q = q.concat(" AND " + where.getKey() + getSqlFieldOperatorString(where.getValue()));
            q = q.concat(getSqlFieldString(where.getValue()));
        }

        q = q.concat(";");

        return q;

    }

    private String makeSelectSQL() {

        String q = "";

        q = "SELECT * FROM " + tableName;

        if (!wheres.isEmpty()) {

            Iterator<Map.Entry<String, Object>> i = wheres.entrySet().iterator();

            Map.Entry<String, Object> where = i.next();

            q = q.concat(" WHERE " + where.getKey() + getSqlFieldOperatorString(where.getValue()));
            q = q.concat(getSqlFieldString(where.getValue()));

            while (i.hasNext()) {

                where = i.next();

                q = q.concat(" AND " + where.getKey() + getSqlFieldOperatorString(where.getValue()));
                q = q.concat(getSqlFieldString(where.getValue()));
            }

        }
        
        if(limit > 0)
            q = q.concat(" LIMIT " + limit);

        q = q.concat(";");

        return q;
    }
    
    private String makeDeleteSQL() {

        String q = "";

        q = "DELETE FROM " + tableName;

        if (!wheres.isEmpty()) {

            Iterator<Map.Entry<String, Object>> i = wheres.entrySet().iterator();

            Map.Entry<String, Object> where = i.next();

            q = q.concat(" WHERE " + where.getKey() + getSqlFieldOperatorString(where.getValue()));
            q = q.concat(getSqlFieldString(where.getValue()));

            while (i.hasNext()) {

                where = i.next();

                q = q.concat(" AND " + where.getKey() + getSqlFieldOperatorString(where.getValue()));
                q = q.concat(getSqlFieldString(where.getValue()));
            }

        }
        
        if(limit > 0)
            q = q.concat(" LIMIT " + limit);

        q = q.concat(";");

        return q;
    }

    public ResultSet getResults() {
        return lastResults;
    }

    public String getSqlQuery() {
        return sqlQuery;
    }
    
    public Object getLastInseredId() {
        return lastInseredId;
    }

}