LINQ/Hibernate killer API :D

You don't think I am serious, do you ? :P

There is nothing better than what LINQ and Hibernate/JPA technologies are (you are not crazy, you've hear me say that 'LINQ is good', because it is !)

I am a Java developer and where I am working now sometimes it is not possible to use a ORM technology (sad but normal though, considering the nature of some projects), and as it is so tedious to write SQL inserts/updates/selects and concatenate each value, single quote the strings, putting commas in a many argument insert ...

I coded in a short night a simple solution for me, I am tired and probably not everything works fine because I haven't had time to stress it, but for most of what I need is done.

SQLBuilder.java (and the ported (SQLBuilder.cs) gives you a builder object providing a fluent API to make SQL statements. The API IS FORGIVEN, so it will forgive you any errors you make and will just produce a wrong SQL string (as you would have if you hand write wrong SQL), BUT, if you already know SQL, it gives you a mechanism to easily build them.

// ok, let the code to talk !


-----------------------------------------------------SQLBuilderTest.java
package com.labspy.testcases;

import com.labspy.test.SQLBuilder;

/**
 * LICENCE: http://creativecommons.org/licenses/by-sa/3.0/
 * 
 * @author David Daniel Hofmann
 * @email hofmanndavid@gmail.com
 */
public class SQLBuilderTest {

   public static void main(String[] args) {

      String insert = SQLBuilder.insert("tableName", "col1, col2, col3")
            .values(1, 32, "this is a autoquoted string").buildSQL();

      String update = SQLBuilder.update("tablename").set("col1, col2, col3", 1,
            "'not autoquotedstring'", 25L).where("a").in(1, 2, 56).or("id")
            .equalTo("89").buildSQL();

      String delete = SQLBuilder.delete("tabletodelete").where("id").equalTo(
            "59") // receive only strings and will not autoquote
            .or("othercolumn").like("2008%").buildSQL();

      String select = SQLBuilder.select("*") // gives you a builder ( using the builder pattern )
            .from("ttpy_simchonize").where("a >", 20) // will take also just one argument ("a > 20")
            .and("b < 60")
            .andNot("a < b") // will also take ("a <", 20)
            .and("b")
            .like("TD%")
            .and("c")
            .in("one", "two") // takes varargs, array or collection
            .and("x").in(1, 2, 3, 7) // not quoted when not string
            .or("x > ", 2)
            .groupBy("a")
            .orderBy("id")
            .buildSQL(); // returns the
                                                                  // string

      // the whole code abobe produces the next:
      // INSERT INTO tableName (col1, col2, col3) VALUES (1, 32, 'this is aautoquoted string')
      // UPDATE tablename SET col1=1, col2='not autoquotedstring', col3=25 WHERE a IN (1, 2, 56) OR id = 89
      // DELETE FROM tabletodelete WHERE id = 59 OR othercolumn LIKE '2008%'
      // SELECT * FROM ttpy_simchonize WHERE a > 20 AND b < 60 AND NOT a < b AND b LIKE 'TD%' AND c IN ('one', 'two') AND x IN (1, 2, 3, 7) OR x > 2 GROUP BY a ORDER BY id

   } 

}


-----------------------------------------------------SQLBuilder.java
package com.labspy.test;

import java.util.Collection;

/**
 * LICENCE: http://creativecommons.org/licenses/by-sa/3.0/
 * 
 * @author David Daniel Hofmann
 * @email hofmanndavid@gmail.com
 */
public class SQLBuilder {

   public static SQLBuilder select(String selectStmt) {
      return new SQLBuilder("SELECT " + selectStmt.trim() + " ");
   }

   public static SQLBuilder update(String updateStmt) {
      return new SQLBuilder("UPDATE " + updateStmt.trim() + " ");
   }

   public static SQLBuilder delete(String deleteStmt) {
      return new SQLBuilder("DELETE FROM  " + deleteStmt.trim() + " ");
   }

   public static SQLBuilder insert(String insertStmt, String fieldNames) {
      SQLBuilder instance = new SQLBuilder("INSERT INTO " + insertStmt.trim()
            + " ");

      instance.sb.append("(");
      instance.sb.append(fieldNames);
      instance.sb.append(") ");

      return instance;
   }

   private StringBuilder sb = new StringBuilder();

   private SQLBuilder(String stmt) {
      sb.append(stmt);
   }

   public SQLBuilder set(String csvFieldsToUpdate, Object... respectiveValues) {
      sb.append("SET ");
      String[] split = csvFieldsToUpdate.split(",");

      sb.append(split[0].trim());
      sb.append("=");
      sb.append(respectiveValues[0]);

      for (int i = 1; i < split.length; i++) {
         sb.append(", ");
         sb.append(split[i].trim());
         sb.append("=");
         sb.append(respectiveValues[i]);
      }

      sb.append(" ");
      return this;
   }

   public SQLBuilder values(Object... values) {
      sb.append("VALUES (");

      for (int i = 0; i < values.length; i++) {
         boolean isString = values[i] instanceof String;

         if (isString)
            sb.append("'");
         sb.append(values[i]);
         if (isString)
            sb.append("'");

         if (values.length - 1 > i)
            sb.append(", ");
      }

      sb.append(") ");
      return this;
   }

   public SQLBuilder from(String fromStmt) {
      sb.append("FROM ");
      sb.append(fromStmt);
      sb.append(" ");
      return this;
   }

   public SQLBuilder where(String stmt, Object... cat) {
      sb.append("WHERE ");
      sb.append(stmt);

      if (cat.length > 0) {// we just take one parameter more
         boolean isString = cat[0] instanceof String;
         sb.append(" ");
         if (isString)
            sb.append("'");
         sb.append(cat[0]);
         if (isString)
            sb.append("'");
         sb.append(" ");
      } else
         sb.append(" ");

      return this;
   }

   public SQLBuilder equalTo(String stmt) {
      sb.append("= ");
      sb.append(stmt);
      sb.append(" ");
      return this;
   }

   public SQLBuilder or(String stmt, Object... cat) {
      sb.append("OR ");
      sb.append(stmt);

      if (cat.length > 0) {// we just take one parameter more
         boolean isString = cat[0] instanceof String;
         sb.append(" ");
         if (isString)
            sb.append("'");
         sb.append(cat[0]);
         if (isString)
            sb.append("'");
         sb.append(" ");
      } else
         sb.append(" ");

      return this;
   }

   public SQLBuilder between(Object one, Object two) {
      boolean isString = one instanceof String;
      sb.append("BETWEEN ");
      if (isString)
         sb.append("'");
      sb.append(one);
      if (isString)
         sb.append("'");
      sb.append(" AND ");
      if (isString)
         sb.append("'");
      sb.append(two);
      if (isString)
         sb.append("'");
      sb.append(" ");
      return this;
   }

   public SQLBuilder and(String stmt, Object... cat) {

      sb.append("AND ");
      sb.append(stmt);

      if (cat.length > 0) {// we just take one parameter more
         boolean isString = cat[0] instanceof String;
         sb.append(" ");
         if (isString)
            sb.append("'");
         sb.append(cat[0]);
         if (isString)
            sb.append("'");
         sb.append(" ");
      } else
         sb.append(" ");

      return this;
   }

   public SQLBuilder iLike(String pattern) {

      sb.append("ILIKE '");
      sb.append(pattern);
      sb.append("' ");

      return this;
   }

   public SQLBuilder notILike(String pattern) {

      sb.append("NOT ILIKE '");
      sb.append(pattern);
      sb.append("' ");

      return this;
   }

   public SQLBuilder like(String pattern) {

      sb.append("LIKE '");
      sb.append(pattern);
      sb.append("' ");

      return this;
   }

   public SQLBuilder notLike(String pattern) {

      sb.append("NOT LIKE '");
      sb.append(pattern);
      sb.append("' ");

      return this;
   }

   public SQLBuilder andNot(String stmt, Object... cat) {

      sb.append("AND NOT ");
      sb.append(stmt);

      if (cat.length > 0) {// we just take one parameter more
         boolean isString = cat[0] instanceof String;
         sb.append(" ");
         if (isString)
            sb.append("'");
         sb.append(cat[0]);
         if (isString)
            sb.append("'");
         sb.append(" ");
      } else
         sb.append(" ");

      return this;
   }

   public SQLBuilder in(Collection&lt;Object&gt; values) {
      return in(values.toArray());
   }

   public SQLBuilder in(Object... values) {
      boolean isString = values[0] instanceof String;
      sb.append("IN (");

      for (int i = 0; i < values.length; i++) {

         if (isString)
            sb.append("'");
         sb.append(values[i]);
         if (isString)
            sb.append("'");

         if (values.length - 1 > i)
            sb.append(", ");
      }

      sb.append(") ");
      return this;
   }

   public SQLBuilder groupBy(String groupByStmt) {
      sb.append("GROUP BY ");
      sb.append(groupByStmt);
      sb.append(" ");
      return this;
   }

   public SQLBuilder having(String havingStmt, Object... cat) {
      sb.append("HAVING ");
      sb.append(havingStmt);

      if (cat.length > 0) {// we just take one parameter more
         boolean isString = cat[0] instanceof String;
         sb.append(" ");
         if (isString)
            sb.append("'");
         sb.append(cat[0]);
         if (isString)
            sb.append("'");
         sb.append(" ");
      } else
         sb.append(" ");

      return this;
   }

   public SQLBuilder orderBy(String orderByStmt) {
      sb.append("ORDER BY ");
      sb.append(orderByStmt);
      sb.append(" ");
      return this;
   }

   public String buildSQL() {
      String sql = sb.toString();
      System.out.println(sql);
      return sql;
   }
 

}

-- I've googled for this solution but I've found nothing simple as what I need. I've made a simple tool for myself that will alleviate the work of hand coding SQL whenever I can not use a decent ORM tool, probably this is YAW (yet another wheel) but I want to know how many of you are still needing to hand write full SQL strings, and more important, will a tool like this be useful ?