Update QueryBuilder and associated tests.

gk-brown · gk-brown · commit b94908ced87c · 2020-12-12T07:48:45.000-05:00
diff --git a/README.md b/README.md
@@ -32,6 +32,7 @@ Classes provided by the HTTP-RPC framework include:
 * [TemplateEncoder](#templateencoder) - encodes an object hierarchy using a [template document](template-reference.md)
 * [BeanAdapter](#beanadapter) - map adapter for Java beans
 * [ResultSetAdapter and Parameters](#resultsetadapter-and-parameters) - iterable adapter for JDBC result sets/applies named parameter values to prepared statements
+* [QueryBuilder](#querybuilder) - programmatically constructs a SQL query
 * [ElementAdapter](#elementadapter) - map adapter for XML elements
 * [ResourceBundleAdapter](#resourcebundleadapter) - map adapter for resource bundles
 * [StreamAdapter](#streamadapter) - iterable adapter for streams
@@ -786,6 +787,9 @@ Once applied, the statement can be executed:
 ResultSetAdapter resultSetAdapter = new ResultSetAdapter(statement.executeQuery());    
 ```
 
+## QueryBuilder
+TODO
+
 ## ElementAdapter
 The `ElementAdapter` class provides access to the contents of an XML DOM `Element` via the `Map` interface. The resulting map can then be transformed to another representation via a template document or accessed via a strongly typed interface proxy, as described earlier. 
 
diff --git a/httprpc-client/src/main/java/org/httprpc/sql/QueryBuilder.java b/httprpc-client/src/main/java/org/httprpc/sql/QueryBuilder.java
@@ -15,7 +15,7 @@
 package org.httprpc.sql;
 
 /**
- * Class for dynamically constructing a SQL query.
+ * Class for programmatically constructing a SQL query.
  */
 public class QueryBuilder {
     private StringBuilder sqlBuilder = new StringBuilder();
@@ -25,9 +25,13 @@ private QueryBuilder(String operation) {
     }
 
     /**
-     * TODO
+     * Creates a "select" query.
+     *
      * @param columns
+     * The column names.
+     *
      * @return
+     * The new {@link QueryBuilder} instance.
      */
     public static QueryBuilder select(String... columns) {
         if (columns == null) {
@@ -38,10 +42,16 @@ public static QueryBuilder select(String... columns) {
     }
 
     /**
-     * TODO
+     * Creates an "insert into" query.
+     *
      * @param table
+     * The table name.
+     *
      * @param columns
+     * The column names.
+     *
      * @return
+     * The new {@link QueryBuilder} instance.
      */
     public static QueryBuilder insertInto(String table, String... columns) {
         if (table == null) {
@@ -56,9 +66,13 @@ public static QueryBuilder insertInto(String table, String... columns) {
     }
 
     /**
-     * TODO
+     * Creates an "update" query.
+     *
      * @param table
+     * The table name.
+     *
      * @return
+     * The new {@link QueryBuilder} instance.
      */
     public static QueryBuilder update(String table) {
         if (table == null) {
@@ -69,9 +83,13 @@ public static QueryBuilder update(String table) {
     }
 
     /**
-     * TODO
+     * Creates a "delete from" query.
+     *
      * @param table
+     * The table name.
+     *
      * @return
+     * The new {@link QueryBuilder} instance.
      */
     public static QueryBuilder deleteFrom(String table) {
         if (table == null) {
@@ -81,10 +99,34 @@ public static QueryBuilder deleteFrom(String table) {
         return new QueryBuilder("delete from " + table);
     }
 
+    private static String encode(Object value) {
+        if (value instanceof String) {
+            String string = (String)value;
+
+            if (string.startsWith(":") || string.equals("?")) {
+                return string;
+            } else {
+                StringBuilder stringBuilder = new StringBuilder();
+
+                stringBuilder.append("'");
+                stringBuilder.append(string.replace("'", "''"));
+                stringBuilder.append("'");
+
+                return stringBuilder.toString();
+            }
+
+        } else {
+            return String.valueOf(value);
+        }
+    }
+
     /**
-     * TODO
+     * Appends a "from" clause to a query.
+     *
      * @param tables
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder from(String... tables) {
         if (tables == null) {
@@ -98,9 +140,13 @@ public QueryBuilder from(String... tables) {
     }
 
     /**
-     * TODO
+     * Appends a "join" clause to a query.
+     *
      * @param table
+     * The table name.
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder join(String table) {
         if (table == null) {
@@ -114,9 +160,13 @@ public QueryBuilder join(String table) {
     }
 
     /**
-     * TODO
+     * Appends an "on" clause to a query.
+     *
      * @param predicate
+     * The predicate.
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder on(String predicate) {
         if (predicate == null) {
@@ -130,9 +180,13 @@ public QueryBuilder on(String predicate) {
     }
 
     /**
-     * TODO
+     * Appends a "where" clause to a query.
+     *
      * @param predicate
+     * The predicate.
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder where(String predicate) {
         if (predicate == null) {
@@ -146,9 +200,13 @@ public QueryBuilder where(String predicate) {
     }
 
     /**
-     * TODO
+     * Appends an "order by" clause to a query.
+     *
      * @param columns
+     * The column names.
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder orderBy(String... columns) {
         if (columns == null) {
@@ -162,9 +220,13 @@ public QueryBuilder orderBy(String... columns) {
     }
 
     /**
-     * TODO
+     * Appends a "values" clause to a query.
+     *
      * @param values
+     * The column values.
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder values(Object... values) {
         if (values == null) {
@@ -178,7 +240,7 @@ public QueryBuilder values(Object... values) {
                 sqlBuilder.append(", ");
             }
 
-            sqlBuilder.append(values[i]);
+            sqlBuilder.append(encode(values[i]));
         }
 
         sqlBuilder.append(")");
@@ -187,16 +249,22 @@ public QueryBuilder values(Object... values) {
     }
 
     /**
-     * TODO
+     * Appends a "set" command to a query.
+     *
      * @param column
+     * The column name.
+     *
      * @param value
+     * The column value.
+     *
      * @return
+     * The {@link QueryBuilder} instance.
      */
     public QueryBuilder set(String column, Object value) {
         sqlBuilder.append(" set ");
         sqlBuilder.append(column);
         sqlBuilder.append(" = ");
-        sqlBuilder.append(value);
+        sqlBuilder.append(encode(value));
 
         return this;
     }
diff --git a/httprpc-client/src/test/java/org/httprpc/sql/QueryBuilderTest.java b/httprpc-client/src/test/java/org/httprpc/sql/QueryBuilderTest.java
@@ -37,11 +37,11 @@ public void testSelect() {
 
     @Test
     public void testInsertInto() {
-        String sql = QueryBuilder.insertInto("A", "a", "b", "c")
-            .values(1, true, "hello")
+        String sql = QueryBuilder.insertInto("A", "a", "b", "c", "d", "e")
+            .values(1, true, "hello", ":a", "?")
             .toString();
 
-        System.out.println(sql);
+        assertEquals("insert into A (a, b, c, d, e) values (1, true, 'hello', :a, ?)", sql);
     }
 
     @Test
@@ -50,10 +50,12 @@ public void testUpdate() {
             .set("a", 1)
             .set("b", true)
             .set("c", "hello")
+            .set("d", ":d")
+            .set("e", "?")
             .where("a is not null")
             .toString();
 
-        System.out.println(sql);
+        assertEquals("update A set a = 1 set b = true set c = 'hello' set d = :d set e = ? where a is not null", sql);
     }
 
     @Test
diff --git a/httprpc-test/src/main/java/org/httprpc/test/PetService.java b/httprpc-test/src/main/java/org/httprpc/test/PetService.java
@@ -116,7 +116,7 @@ public double getAverageAge() throws SQLException {
         double averageAge;
         try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
-            ResultSetAdapter resultSetAdapter = new ResultSetAdapter(statement.executeQuery("SELECT birth FROM pet"))) {
+            ResultSetAdapter resultSetAdapter = new ResultSetAdapter(statement.executeQuery("select birth from pet"))) {
             Date now = new Date();
 
             Stream<Pet> pets = resultSetAdapter.stream().map(result -> BeanAdapter.adapt(result, Pet.class));
