/java-sql-dsl

A Java SQL DSL

Primary LanguageJavaApache License 2.0Apache-2.0

Java SQL DSL

java-sql-dsl is a small DSL to generate SQL queries in Java. It will be able to remove unecessary "where clauses" when given value is null. It then avoids to add if conditions to build the SQL query.

For example, a typical Select query will look like this : select("*").from("client") // .where(c("firstname")).eq(criteria.getClientFirstname()) // .and(c("lastname")).eq(criteria.getClientLastname()) ...

This query will filter result on firstname only if the criteria has a clientFirstname which is not null. Thus, it is possible to write only one "readable" query for every combinations of submitted criteria.

Usage

Instanciating queries

  • A SelectQuery is instanciated with the static method Query.select(columns/functions/"*").
  • An UpdateQuery is instanciated with the static method Query.update(table).
  • An InsertQuery is instanciated with the static method Query.insertInto(table).
  • A DeleteQuery is instanciated with the static method Query.deleteFrom(table).

Producing SQL

To get the SQL query as a string, just call the toSql() method when the query has been built. To get a parameter Map<String, Object>, call the getParams() method.

A typical usage with a Spring SimpleJdbcTemplate might look like this : getNamedParameterJdbcTemplate().query(query.toSql(), query.getParams(), myRowMapper);

Using Columns and Function

  • Columns must be specified with the Query.c(columnName) static method.
  • Functions must be specified with the Query.f(functionName, arg1, arg2, ...) static method.

Using SQL dialects

The default SQL dialect is implemented in the DefaultQueryBuilder class but other can be written by overriding the necessary visit() methods. See the OracleQueryBuilder for example.

It is then possible to replace the default query builder with : Query.setDefaultQueryBuilder(OracleQueryBuilder.class);

Optimizing requests

By default, the queries are not optimized (ie. filter clauses are not removed if the value is null). To optimize query, just add an instance of BasicQueryOptimizer to the query visitors : Query.addVisitor(new BasicQueryOptimizer());

Additional visitors are executed just before producing the SQL query as a string.

Examples

For usage examples, see the unit tests in src/main/test directory. It will demonstrate implemented features and how to use it. The whole SQL language is not (yet) implemented, feel free ;).