SQL Builder is a Java library for dealing with dynamic SQL. SQL Builder offers three groups of functionality:
Builders simplify the creation of SQL strings. They know a little bit
about SQL syntax, and make the creation of dynamic SQL a little nicer in
Java. Like Java’s StringBuilder, they use chainable calls. Here’s an
example of using SelectBuilder.
1 new SelectBuilder()
2 .column("name")
3 .column("age")
4 .from("Employee")
5 .where("dept = 'engineering'")
6 .where("salary > 100000")
7 .toString();This produces the SQL string select name, age from Employee where dept
= 'engineering' and salary > 100000. Note how SelectBuilder knows to
join the columns with a comma and to join the where clauses with and.
In addition to SelectBuilder, SQL Builder provides builders for
insert, update, and delete statements.
WARNING: builders DO NOT attempt to perform any SQL escaping. You have to be careful to escape any externally-provided strings or your application may be vulnerable to a SQL injection attack.
For example, suppose you implement the following:
1 String sql = new SelectBuilder()
2 .column("firstName")
3 .from("Employee")
4 .where("id = " + id);
5
6 // execute the SQL on a database connectionIf the value for id came from a URL, an attacker could provide a
corrupted value like this: 0; drop table Employee;. Depending on how
you execute the SQL, you could find yourself with no more employee
records.
Fortunately, SQL Builder provides a mechanism called a creator that neatly solves this problem.
Each builder class has a corresponding Creator class that implements
the PreparedStatmentCreator interface. SQL Builder repackages versions
of the PreparedStatementCreator and JdbcTemplate from Spring JDBC to
make it simple to execute dynamically-created SQL statements when given
a data source:
1 PreparedStatementCreator psc =
2 new UpdateCreator("Employee")
3 .setValue("name", "Bob");
4
5 new JdbcTemplate(dataSource).update(psc);One big advantage of creators over builders is that you can inject data
into the executed SQL in a way that is safe from SQL injection.
The unsafe example from the builders section can be re-implemented
safely using a SelectCreator:
1 SelectCreator sc = new SelectCreator()
2 .column("firstName")
3 .from("Employee")
4 .where("id = :id")
5 .setParameter("id", id);
6
7 return new JdbcTemplate(dataSource).query(sc);In this example, the creator creates the prepared statement select
firstName from Employee where id = ?, and uses the JDBC driver to
safely escape the value of id.
One nice thing about creators is that you can call their methods in any order you like, and they’ll still produce the correct SQL. For example, consider the common case where different queries can be performed based on the presence of requested parameters.
1 SelectCreator sc = new SelectCreator()
2 .column("name")
3 .column("age")
4 .from("Employee e");
5
6 if (deptId != null) {
7 sc.where(eq("e.deptId", deptId));
8 }You can even conditionally join other tables:
1 if (deptName != null) {
2 sc
3 .column("d.name as deptName")
4 .join("Dept d on e.deptId = d.id")
5 .where(eq("d.name", deptName));
6 }A predicate is a small class that encapsulates adding an expression to
a creator’s where clause and setting the appropriate parameters. SQL
Builder provides a number of predicates in the Predicates class.
The most commonly used predicate is the equality predicate, eq:
1 import static ca.krasnay.sqlbuilder.Predicates.eq;
2
3 new SelectCreator()
4 .column("firstName")
5 .from("Employee")
6 .where(eq("id", id));Here, the two step process of adding a string to the where clause and
separately calling setParameter from the previous example is replaced
with a neat invocation of the eq predicate.
Below is another example where a predicate can save a tricky bit of work, especially when the passed string of department IDs can vary in length:
1 import static ca.krasnay.sqlbuilder.Predicates.in;
2
3 List<Integer> deptIds = //...
4
5 new SelectCreator()
6 .column("firstName")
7 .from("Employee")
8 .where(in("deptId", deptIds));You can easily implement your own predicates. Here is the implementation
of the Predicates.eq method as an example:
1 public static Predicate eq(final String expr, final Object value) {
2 return new Predicate() {
3 private String param;
4 public void init(AbstractSqlCreator creator) {
5 param = creator.allocateParameter();
6 creator.setParameter(param, value);
7 }
8 public String toSql() {
9 return String.format("%s = :%s", expr, param);
10 }
11 };
12 }SQL Builder implements a simple object-relational mapping tool. Unlike some more complicated ORM tools such as Hibernate and JPA, SQL Builder’s ORM makes some simplifying assumptions:
Add the following dependency to your POM:
1 <dependency>
2 <groupId>ca.krasnay</groupId>
3 <artifactId>sqlbuilder</artifactId>
4 <version>1.2</version>
5 </dependency>SQL Builder depends on slf4j and Spring JDBC, so be sure to add the
desired versions of these to your <dependencyManagement> section.