* named parameters
* prepared statements
* code can be tested via db tools

Snippetory SQL - prepared statements made simple

The module snippetory-sql allows to manage your sql statements in library files and handle them with a simple API. The features include:

Use of prepared statements with named parameters
Support for managing different SQL dialects in the same file
Syntax for intact templates
Streamed loading - when processing large result sets processing can be done during the fetch.

Getting started

To get started use the dependency:

  <groupId>org.jproggy</groupId>
  <artifactId>snippetory-sql</artifactId>
  <version>0.9.7</version>

Put your statements in a file like statement-repo.sql:

-- $stmt1{
  SELECT *
  FROM Table1
  WHERE field1 = :mandatory
  /*${*/ OR field2 = :opt1/*}$*/
  /*${*/ OR field3 = :opt2/*}$*/
-- }$

-- $startsWith{
  SELECT *
  FROM Table2
  WHERE description like :prefix || '%'
-- }$

Load the statements file into a Repository using a SqlContext:

  Repository repo = new SqlContext()
  .uriResolver(UriResolver.resource())
  .connections(() -> connection)
  .getRepository("statement-repo.sql");

Mabe we want to load some data into a record like :

record Data(int id, String name) {}

We'd do that like this:

  Statement stmt1 = repo.get("stmt1").set("mandatory", 12).set("opt1", "name");
  try (Cursor<Test> cursor = stmt1.cursor(rs -> new Data(rs.getInt(1), rs.getString(2)))) {
    for (Data d: cursor) {
      // handle the data...
    }
  }

Handling with intact templates

When dealing with databases it's often necessary to deal with complex statements. As such complex statements tend to be expensive it often makes sense to be able to optimize them often, to gain the best performance with a growing dataset. Optimizations can be done in the UI of the database, but there's a problem to gain the right statement from your code, and afterward getting the changes back to code. That's where intact templates come handy.

-- $variables{
set :currency = 'EUR';
set :catId = 17;
-- }$
SELECT products.*, prices.*
FROM products
LEFT OUTER JOIN prices ON ( products.id = prices.productId AND prices.currency = :currency )
WHERE products.id IN (
    SELECT prodCat.productId
    FROM prodCat
    WHERE prodCat.categoryId IN (:catId/*delimiter=', '*/)
  )

This makes it very simple to copy the template back and forth. Having the variables in a region makes sure, they don't end up in the output. The comment coating hides the template markup from the SQL tooling. And everyone is happy.

Complex statements tend to require variants. Maybe not all search fields are fields, maybe users have different access rights. Lots of different requirements can be implemented via variants. And building stuff with complex logic is exactly what Snippetory was build for.