svn commit: r754618 - in /ibatis/trunk/java/ibatis-3/ibatis-3-core/src: main/java/org/apache/ibatis/jdbc/SelectBuilder.java test/java/org/apache/ibatis/jdbc/SelectBuilderTest.java

[cb...@apache.org]

Author: cbegin
Date: Sun Mar 15 05:29:08 2009
New Revision: 754618

URL: http://svn.apache.org/viewvc?rev=754618&view=rev
Log:
Added Select builder

Added:
    ibatis/trunk/java/ibatis-3/ibatis-3-core/src/main/java/org/apache/ibatis/jdbc/SelectBuilder.java
    ibatis/trunk/java/ibatis-3/ibatis-3-core/src/test/java/org/apache/ibatis/jdbc/SelectBuilderTest.java

Added: ibatis/trunk/java/ibatis-3/ibatis-3-core/src/main/java/org/apache/ibatis/jdbc/SelectBuilder.java
URL: http://svn.apache.org/viewvc/ibatis/trunk/java/ibatis-3/ibatis-3-core/src/main/java/org/apache/ibatis/jdbc/SelectBuilder.java?rev=754618&view=auto
==============================================================================
--- ibatis/trunk/java/ibatis-3/ibatis-3-core/src/main/java/org/apache/ibatis/jdbc/SelectBuilder.java (added)
+++ ibatis/trunk/java/ibatis-3/ibatis-3-core/src/main/java/org/apache/ibatis/jdbc/SelectBuilder.java Sun Mar 15 05:29:08 2009
@@ -0,0 +1,130 @@
+package org.apache.ibatis.jdbc;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class SelectBuilder {
+  private static final String AND = ") \nAND (";
+  private static final String OR = ") \nOR (";
+
+
+  private static final ThreadLocal<Query> localQuery = new ThreadLocal<Query>();
+
+  static {
+    localQuery.set(new Query());
+  }
+
+  private static class Query {
+    List<String> select = new ArrayList<String>();
+    List<String> from = new ArrayList<String>();
+    List<String> join = new ArrayList<String>();
+    List<String> innerJoin = new ArrayList<String>();
+    List<String> outerJoin = new ArrayList<String>();
+    List<String> leftOuterJoin = new ArrayList<String>();
+    List<String> rightOuterJoin = new ArrayList<String>();
+    List<String> where = new ArrayList<String>();
+    List<String> having = new ArrayList<String>();
+    List<String> groupBy = new ArrayList<String>();
+    List<String> orderBy = new ArrayList<String>();
+    List<String> lastList = new ArrayList<String>();
+  }
+
+  private static Query query() {
+    return localQuery.get();
+  }
+
+  public static String SQL() {
+    try {
+      StringBuilder builder = new StringBuilder();
+      sqlClause(builder, "SELECT", query().select, "", "", ", ");
+      sqlClause(builder, "FROM", query().from, "", "", ", ");
+      sqlClause(builder, "JOIN", query().join, "", "", "JOIN");
+      sqlClause(builder, "INNER JOIN", query().innerJoin, "", "", "\nINNER JOIN ");
+      sqlClause(builder, "OUTER JOIN", query().outerJoin, "", "", "\nOUTER JOIN ");
+      sqlClause(builder, "LEFT OUTER JOIN", query().leftOuterJoin, "", "", "\nLEFT OUTER JOIN ");
+      sqlClause(builder, "RIGHT OUTER JOIN", query().rightOuterJoin, "", "", "\nRIGHT OUTER JOIN ");
+      sqlClause(builder, "WHERE", query().where, "(", ")", " AND ");
+      sqlClause(builder, "GROUP BY", query().groupBy, "", "", ", ");
+      sqlClause(builder, "HAVING", query().having, "(", ")", " AND ");
+      sqlClause(builder, "ORDER BY", query().orderBy, "", "", ", ");
+      return builder.toString();
+    } finally {
+      localQuery.set(new Query());
+    }
+  }
+
+  private static void sqlClause(StringBuilder builder, String keyword, List<String> parts, String open, String close, String conjunction) {
+    if (!parts.isEmpty()) {
+      if (builder.length() > 0) builder.append("\n");
+      builder.append(keyword);
+      builder.append(" ");
+      builder.append(open);
+      String last = "________";
+      for (int i = 0, n = parts.size(); i < n; i++) {
+        String part = parts.get(i);
+        if (i > 0 && !part.equals(AND) && !part.equals(OR)&& !last.equals(AND) && !last.equals(OR)) {
+          builder.append(conjunction);
+        }
+        builder.append(part);
+        last = part;
+      }
+      builder.append(close);
+    }
+  }
+
+  public static void SELECT(String columns) {
+    query().select.add(columns);
+  }
+
+  public static void FROM(String table) {
+    query().from.add(table);
+  }
+
+  public static void JOIN(String join) {
+    query().join.add(join);
+  }
+
+  public static void INNER_JOIN(String join) {
+    query().innerJoin.add(join);
+  }
+
+  public static void LEFT_OUTER_JOIN(String join) {
+    query().leftOuterJoin.add(join);
+  }
+
+  public static void RIGHT_OUTER_JOIN(String join) {
+    query().rightOuterJoin.add(join);
+  }
+
+  public static void OUTER_JOIN(String join) {
+    query().outerJoin.add(join);
+  }
+
+  public static void WHERE(String conditions) {
+    query().where.add(conditions);
+    query().lastList = query().where;
+  }
+
+  public static void OR() {
+    query().lastList.add(OR);
+  }
+
+  public static void AND() {
+    query().lastList.add(AND);
+  }
+
+  public static void GROUP_BY(String columns) {
+    query().groupBy.add(columns);
+  }
+
+  public static void HAVING(String conditions) {
+    query().having.add(conditions);
+    query().lastList = query().having;
+  }
+
+  public static void ORDER_BY(String columns) {
+    query().orderBy.add(columns);
+  }
+
+
+}

Added: ibatis/trunk/java/ibatis-3/ibatis-3-core/src/test/java/org/apache/ibatis/jdbc/SelectBuilderTest.java
URL: http://svn.apache.org/viewvc/ibatis/trunk/java/ibatis-3/ibatis-3-core/src/test/java/org/apache/ibatis/jdbc/SelectBuilderTest.java?rev=754618&view=auto
==============================================================================
--- ibatis/trunk/java/ibatis-3/ibatis-3-core/src/test/java/org/apache/ibatis/jdbc/SelectBuilderTest.java (added)
+++ ibatis/trunk/java/ibatis-3/ibatis-3-core/src/test/java/org/apache/ibatis/jdbc/SelectBuilderTest.java Sun Mar 15 05:29:08 2009
@@ -0,0 +1,102 @@
+package org.apache.ibatis.jdbc;
+
+import org.junit.Test;
+import static org.junit.Assert.*;
+import static org.apache.ibatis.jdbc.SelectBuilder.*;
+
+public class SelectBuilderTest {
+
+  @Test
+  public void shouldProduceExpectedSimpleSelectStatement() {
+    String expected =
+        "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
+        "FROM PERSON P\n" +
+        "WHERE (P.ID like #id# AND P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
+        "ORDER BY P.LAST_NAME";
+    assertEquals(expected, example2("a","b","c"));
+  }
+
+  @Test
+  public void shouldProduceExpectedSimpleSelectStatementMissingFirstParam() {
+    String expected =
+        "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
+        "FROM PERSON P\n" +
+        "WHERE (P.FIRST_NAME like #firstName# AND P.LAST_NAME like #lastName#)\n" +
+        "ORDER BY P.LAST_NAME";
+    assertEquals(expected, example2(null,"b","c"));
+  }
+
+  @Test
+  public void shouldProduceExpectedSimpleSelectStatementMissingFirstTwoParams() {
+    String expected =
+        "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
+        "FROM PERSON P\n" +
+        "WHERE (P.LAST_NAME like #lastName#)\n" +
+        "ORDER BY P.LAST_NAME";
+    assertEquals(expected, example2(null,null,"c"));
+  }
+
+  @Test
+  public void shouldProduceExpectedSimpleSelectStatementMissingAllParams() {
+    String expected =
+        "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME\n" +
+        "FROM PERSON P\n" +
+        "ORDER BY P.LAST_NAME";
+    assertEquals(expected, example2(null,null,null));
+  }
+
+  @Test
+  public void shouldProduceExpectedComplexSelectStatement() {
+    String expected =
+        "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
+            "FROM PERSON P, ACCOUNT A\n" +
+            "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
+            "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
+            "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
+            "OR (P.LAST_NAME like ?)\n" +
+            "GROUP BY P.ID\n" +
+            "HAVING (P.LAST_NAME like ?) \n" +
+            "OR (P.FIRST_NAME like ?)\n" +
+            "ORDER BY P.ID, P.FULL_NAME";
+    assertEquals(expected, example1());
+  }
+
+  private static String example1() {
+    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
+    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
+    FROM("PERSON P");
+    FROM("ACCOUNT A");
+    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
+    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
+    WHERE("P.ID = A.ID");
+    WHERE("P.FIRST_NAME like ?");
+    OR();
+    WHERE("P.LAST_NAME like ?");
+    GROUP_BY("P.ID");
+    HAVING("P.LAST_NAME like ?");
+    OR();
+    HAVING("P.FIRST_NAME like ?");
+    ORDER_BY("P.ID");
+    ORDER_BY("P.FULL_NAME");
+    return SQL();
+  }
+
+  private static String example2(String id, String firstName, String lastName) {
+    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
+    FROM("PERSON P");
+    if (id != null) {
+      WHERE("P.ID like #id#");
+    }
+    if (firstName != null) {
+      WHERE("P.FIRST_NAME like #firstName#");
+    }
+    if (lastName != null) {
+      WHERE("P.LAST_NAME like #lastName#");
+    }
+    ORDER_BY("P.LAST_NAME");
+    return SQL();
+  }
+
+
+
+}