StringTemplate4 JDBI SQL Loader

Load SQL statements from StringTemplate template groups.

License

License

Categories

Categories

JDBI Data Databases
GroupId

GroupId

org.jdbi.v2
ArtifactId

ArtifactId

jdbi-st4
Last Version

Last Version

0.3.0
Release Date

Release Date

Type

Type

jar
Description

Description

StringTemplate4 JDBI SQL Loader
Load SQL statements from StringTemplate template groups.
Project URL

Project URL

https://github.com/jdbi/jdbi-st4
Source Code Management

Source Code Management

https://github.com/jdbi/jdbi-st4/

Download jdbi-st4

How to add to project

<!-- https://jarcasting.com/artifacts/org.jdbi.v2/jdbi-st4/ -->
<dependency>
    <groupId>org.jdbi.v2</groupId>
    <artifactId>jdbi-st4</artifactId>
    <version>0.3.0</version>
</dependency>
// https://jarcasting.com/artifacts/org.jdbi.v2/jdbi-st4/
implementation 'org.jdbi.v2:jdbi-st4:0.3.0'
// https://jarcasting.com/artifacts/org.jdbi.v2/jdbi-st4/
implementation ("org.jdbi.v2:jdbi-st4:0.3.0")
'org.jdbi.v2:jdbi-st4:jar:0.3.0'
<dependency org="org.jdbi.v2" name="jdbi-st4" rev="0.3.0">
  <artifact name="jdbi-st4" type="jar" />
</dependency>
@Grapes(
@Grab(group='org.jdbi.v2', module='jdbi-st4', version='0.3.0')
)
libraryDependencies += "org.jdbi.v2" % "jdbi-st4" % "0.3.0"
[org.jdbi.v2/jdbi-st4 "0.3.0"]

Dependencies

compile (2)

Group / Artifact Type Version
org.antlr : stringtemplate jar 4.0.2
org.jdbi : jdbi jar 2.73

test (5)

Group / Artifact Type Version
junit : junit jar 4.12
org.assertj : assertj-core jar 3.4.1
com.h2database : h2 jar 1.4.191
org.apache.commons : commons-lang3 jar 3.1
org.mockito : mockito-core jar 2.2.26

Project Modules

There are no modules declared in this project.

JDBI-ST4

Provides an easy way to externalize SQL statements for JDBI (2.x) in StringTemplate 4 StringTemplate Group Files.

Usage

Getting It

Use Maven:

<dependency>
  <groupId>org.jdbi.v2</groupId>
  <artifactId>jdbi-st4</artifactId>
  <version>...</version>
</dependency>

Latest version can be found by searching.

SQL Object API

This library is typically used to externalize SQL into StringTemplate Group Files. Take the following example Dao:

package org.jdbi.v2.st4;

@UseST4StatementLocator
public interface Dao {

    @SqlUpdate
    void createSomethingTable();

    @SqlUpdate
    int insertSomething(int id, String name);

    @SqlQuery
    @MapResultAsBean
    Something findById(int id, @Define("columns") String... columns);

    @SqlQuery("select concat('Hello, ', name, '!') from something where id = :0")
    String findGreetingFor(int id);
}

The @UseST4StatementLocator annotation tells JDBI to use the ST4StatementLocator for this object. By default, it looks for a stringtemplate group file on the classpath at /com/example/Dao.sql.stg. Basically, it looks for <ClassName>.sql.stg in the same package as <ClassName>. With a maven project, you'd achieve this by putting it in src/main/resources/com/example/<ClassName>.sql.stg.

If we look at the stringtemplate group file:

createSomethingTable() ::= <%
    create table something (id int primary key, name varchar)
%>

insertSomething() ::= <%
    insert into something (id, name) values (:0, :1)
%>

findById(columns) ::= <%
    select < columns; separator="," > from something where id = :0
%>

We can then exercise it:

DBI dbi = new DBI(h2);
Dao dao = dbi.onDemand(Dao.class);

dao.createSomethingTable();
dao.insertSomething(7, "Jan");
dao.insertSomething(1, "Brian");

Something jan = dao.findById(7, "id", "name");
assertThat(jan.getId()).as("Jan's ID").isEqualTo(7);
assertThat(jan.getName()).as("Jan's Name").isEqualTo("Jan");

Something partial = dao.findById(7, "name");
assertThat(partial.getId()).as("Jan's ID").isEqualTo(0); // default int value
assertThat(partial.getName()).as("Jan's Name").isEqualTo("Jan");

String greeting = dao.findGreetingFor(7);
assertThat(greeting).isEqualTo("Hello, Jan!");

We find a template defined for each method. You can override this by naming the template to use in the @SqlUpdate or @SqlQuery annotation. For example, using @SqlQuery("woof") would look for a template named woof in that group.

For the findById(columns) template, we need to pass in the columns we want to template into the SQL. We make them available to the template by putting them on the StatementContext in JDBI. The easiest way to do this is with the @Define annotation on another parameter, which we do here. However, values set on the Handle or DBI will also be available.

Finally, note that if a template is not found (such as for findGreetingFor the "name" (sql literal in this case)) is compiled as a string template and evaluated.

Fluent API

We can use the same mechanisms with the fluent api, though in this case we have to tell JDBI where to find the stringtemplate group, as there is no object to use as the basis:

DBI dbi = new DBI(h2);
dbi.setStatementLocator(ST4StatementLocator.fromClasspath("/org/jdbi/st4/ExampleTest.Dao.sql.stg"));

dbi.useHandle((h) -> {
    h.execute("createSomethingTable");

    int numCreated = h.createStatement("insertSomething")
                      .bind("0", 0)
                      .bind("1", "Jan")
                      .execute();
    assertThat(numCreated).as("number of rows inserted").isEqualTo(1);

    String name = h.createQuery("findById")
                  .bind("0", 0)
                  .define("columns", "name")
                  .mapTo(String.class)
                  .first();
    assertThat(name).as("Jan's Name").isEqualTo("Jan");
});

In this case we set the template group as the same one from the sql object example. There are a few ways to specify which to use, check out the factory methods and their javadocs on ST4StatementLocator to explore.

License

Apache License 2.0

org.jdbi.v2
https://jdbi.org

Versions

Version
0.3.0
0.2.0