* 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.