Fork me on GitHub

SqlBuilder Example

Setup schema objects

  1. // create default schema
  2. DbSpec spec = new DbSpec();
  3. DbSchema schema = spec.addDefaultSchema();
  4.  
  5. // add table with basic customer info
  6. DbTable customerTable = schema.addTable("customer");
  7. DbColumn custIdCol = customerTable.addColumn("cust_id", "number", null);
  8. DbColumn custNameCol = customerTable.addColumn("name", "varchar", 255);
  9.  
  10. // add order table with basic order info
  11. DbTable orderTable = schema.addTable("order");
  12. DbColumn orderIdCol = orderTable.addColumn("order_id", "number", null);
  13. DbColumn orderCustIdCol = orderTable.addColumn("cust_id", "number", null);
  14. DbColumn orderTotalCol = orderTable.addColumn("total", "number", null);
  15. DbColumn orderDateCol = orderTable.addColumn("order_date", "timestamp", null);
  16.  
  17. // add a join from the customer table to the order table (on cust_id)
  18. DbJoin custOrderJoin = spec.addJoin(null, "customer",
  19. null, "order",
  20. "cust_id");

Create initial schema in database

  1. String createCustomerTable =
  2. new CreateTableQuery(customerTable, true)
  3. .validate().toString();
  4. System.out.println(createCustomerTable);
  5. // => CREATE TABLE customer (cust_id number,name varchar(255))
  6.  
  7. String createOrderTable =
  8. new CreateTableQuery(orderTable, true)
  9. .validate().toString();
  10. System.out.println(createOrderTable);
  11. // => CREATE TABLE order (order_id number,cust_id number,total number,order_date timestamp)

Populate database

  1. String insertCustomerQuery =
  2. new InsertQuery(customerTable)
  3. .addColumn(custIdCol, 1)
  4. .addColumn(custNameCol, "bob")
  5. .validate().toString();
  6. System.out.println(insertCustomerQuery);
  7. // => INSERT INTO customer (cust_id,name)
  8. // VALUES (1,'bob')
  9.  
  10. String preparedInsertCustomerQuery =
  11. new InsertQuery(customerTable)
  12. .addPreparedColumns(custIdCol, custNameCol)
  13. .validate().toString();
  14. System.out.println(preparedInsertCustomerQuery);
  15. // => INSERT INTO customer (cust_id,name)
  16. // VALUES (?,?)
  17. String insertOrderQuery =
  18. new InsertQuery(orderTable)
  19. .addColumn(orderIdCol, 37)
  20. .addColumn(orderCustIdCol, 1)
  21. .addColumn(orderTotalCol, 37.56)
  22. .addColumn(orderDateCol, JdbcEscape.timestamp(new Date()))
  23. .validate().toString();
  24. System.out.println(insertOrderQuery);
  25. // => INSERT INTO order (order_id,cust_id,total,order_date)
  26. // VALUES (37,1,37.56,{ts '2008-04-01 14:39:00.914'})

Run some queries

  1. ////
  2. // find a customer name by id
  3. String query1 =
  4. new SelectQuery()
  5. .addColumns(custNameCol)
  6. .addCondition(BinaryCondition.equalTo(custIdCol, 1))
  7. .validate().toString();
  8. System.out.println(query1);
  9.  
  10. // => SELECT t0.name FROM customer t0
  11. // WHERE (t0.cust_id = 1)
  12.  
  13. ////
  14. // find all the orders for a customer, given name, order by date
  15. String query2 =
  16. new SelectQuery()
  17. .addAllTableColumns(orderTable)
  18. .addJoins(SelectQuery.JoinType.INNER, custOrderJoin)
  19. .addCondition(BinaryCondition.equalTo(custNameCol, "bob"))
  20. .addOrderings(orderDateCol)
  21. .validate().toString();
  22. System.out.println(query2);
  23.  
  24. // => SELECT t1.*
  25. // FROM customer t0 INNER JOIN order t1 ON (t0.cust_id = t1.cust_id)
  26. // WHERE (t0.name = 'bob')
  27. // ORDER BY t1.order_date
  28.  
  29. ////
  30. // find the totals of all orders for people named bob who spent over $100
  31. // this year, grouped by name
  32. String query3 =
  33. new SelectQuery()
  34. .addCustomColumns(
  35. custNameCol,
  36. FunctionCall.sum().addColumnParams(orderTotalCol))
  37. .addJoins(SelectQuery.JoinType.INNER, custOrderJoin)
  38. .addCondition(BinaryCondition.like(custNameCol, "%bob%"))
  39. .addCondition(BinaryCondition.greaterThan(
  40. orderDateCol,
  41. JdbcEscape.date(new Date(108, 0, 1)), true))
  42. .addGroupings(custNameCol)
  43. .addHaving(BinaryCondition.greaterThan(
  44. FunctionCall.sum().addColumnParams(orderTotalCol),
  45. 100, false))
  46. .validate().toString();
  47. System.out.println(query3);
  48.  
  49. // => SELECT t0.name,SUM(t1.total)
  50. // FROM customer t0 INNER JOIN order t1 ON (t0.cust_id = t1.cust_id)
  51. // WHERE ((t0.name LIKE '%bob%') AND (t1.order_date >= {d '2008-01-01'}))
  52. // GROUP BY t0.name
  53. // HAVING (SUM(t1.total) > 100)

Use schema objects not predefined (use custom SQL)

  1. ////
  2. // find addresses for customers from PA,NJ,DE from table:
  3. // address(cust_id, street, city, state, zip)
  4. String customQuery1 =
  5. new SelectQuery()
  6. .addCustomColumns(
  7. custNameCol,
  8. new CustomSql("a1.street"),
  9. new CustomSql("a1.city"),
  10. new CustomSql("a1.state"),
  11. new CustomSql("a1.zip"))
  12. .addCustomJoin(SelectQuery.JoinType.INNER, customerTable,
  13. "address a1",
  14. BinaryCondition.equalTo(custIdCol,
  15. new CustomSql("a1.cust_id")))
  16. .addCondition(new InCondition("a1.state",
  17. "PA", "NJ", "DE"))
  18. .validate().toString();
  19. System.out.println(customQuery1);
  20.  
  21. // => SELECT t0.name,a1.street,a1.city,a1.state,a1.zip
  22. // FROM customer t0 INNER JOIN address a1 ON (t0.cust_id = a1.cust_id)
  23. // WHERE ('a1.state' IN ('PA','NJ','DE') )