Components¶

The query builder has several components with their own feature set.

Query¶

Defining Arguments¶

The query supports named and indexed arguments

Instead of writing ? you can use named arguments with :argument_name.

Examples:

Indexed onlyNamed onlyMixedDuplicate

SELECT *
FROM table
WHERE id = ?
  AND name = ?;

SELECT *
FROM table
WHERE id = :id
  AND name = :name;

SELECT *
FROM table
WHERE id = :id
  AND name = ?;

Use the argument name multiple times.

SELECT *
FROM table
WHERE id = :id AND name = :name
   OR name = lower(:name);

Note

All named arguments of the same name have the same value

Formatting¶

Danger

Do not use that feature with untrusted user input!

When defining a query you can append objects which are used in a String#formatted(Object...) call. This allows you to inject row or table names in runtime to reuse queries.

Query.query("SELECT * FROM %s;", tableName)

Calls¶

Calls are used to define the amount of calls.

Note

All examples are without binding a parameter. See call for examples for binding parameter.

Singleton Call¶

A singleton call is a single execution of your query.

There are several ways to define your singleton query.

EmptySimpleAccess Storage

This is an empty call without any parameter

Query.query("SELECT * FROM table;")
        .single(Calls.empty())

or

Query.query("SELECT * FROM table;")
        .single()

This version is shorter and quicker to write

Query.query("SELECT * FROM table;")
        .single(Call.of())

In case you have saved something into your query storage you can access the storage here

Query.query("SELECT * FROM table;")
        .single(storage -> Call.of().asSingleCall());

Batch calls¶

Batch calls are similar to singleton calls.

Warning

Batch queries do not support selects

ShortListStreamAccess Storage

This version is shorter and quicker to write

query.query("INSERT INTO table(a,b) VALUES (:a, :b)")
        .batch(Call.of(),
                Call.of());

Use a list of calls

query.query("INSERT INTO table(a,b) VALUES (:a, :b)")
        .batch(List.of(Call.of(), Call.of()));

Use a stream of calls

query.query("INSERT INTO table(a,b) VALUES (:a, :b)")
        .batch(Stream.generate(Call::of).limit(5));

query.query("INSERT INTO table(a,b) VALUES (:a, :b)")
        .batch(storage -> Call.of().asBatchCall().add(Call.of()));

Call¶

A call is used to define the parameters of a query execution.

You can define a parameter by calling Call#bind(String, Object[], SqlType), Call#bind(Object[], SqlType) or some other overload of this method.

Indexed parameter¶

To bind an indexed parameter (Parameters that are defined with ?) use the Call#bind(Object[], SqlType) method.

The type of your parameter will be automatically defined.

Named parameter¶

To bind a named parameter (Parameter that are defined with :parameter_name) use the Call#bind(String, Object[], SqlType) method.

The string is the name of the parameter without :

The type of your parameter will be automatically determined.

Mixed parameter¶

When indexed and named parameter are mixed, the indexed parameters are filled independent of the named ones. Only the order in which the indexed parameters are provided matters.

Auto binding and Adapters¶

The bind method has several overloads for all common types of SQL. If there is a method lacking for a common object, or you need a specific layout you can use Adapters.

A good example for that is UUID, which can be either serialized as bytes or as a string.

To use an adapter pass the adapter after your object as second or third argument.

Write UUID as bytes

query.query("INSERT INTO table(a,b) VALUES (:a, :b)")
        .single(Call.of().bind(UUID.randomUUID(), UUIDAdapter.AS_BYTES));

Write UUID as String

query.query("INSERT INTO table(a,b) VALUES (:a, :b)")
        .single(Call.of().bind("a", UUID.randomUUID(), UUIDAdapter.AS_STRING));

Adapter¶

Adapter are used to transform a java object into a sql object and apply it on a statement.

Custom Adapter¶

To define a custom adapter you can use the Adapter#create(Class, AdapterMapping, int) method.

This method has three important parameter:

Class clazz This is the java class that the adapter will convert into a sql type.
AdapterMapping mapping The AdapterMapping maps the java type to the sql type and applies it to the statement on the provided index.
int Type The sql type to be used if the type is null.

Row Mapping¶

A row mapping is simply a mapping of a row in a sql result into a java object.

Results¶

There are four types of results, which differ between read and write operations. Results contain information of the operation results and also allow access to exceptions via the BaseResult#exceptions() method.

Read Results¶

For reading, you can either use the Reader#first() or Reader#all() methods to directly retrieve your object. If you need more information you can use Reader#firstResult() or Reader#allResults(), which return either a SingleResult or a MultiResult. This Result still allows you to access your read objects via the Result#result() method.

Write Results¶

For writing, you have two results, which differ depending on your call. You will always end up with a ManipulationResult, which provides the changed ManipulationResult#rows() and a boolean ManipulationResult#changed() to indicate that rows is not zero. If you execute a batch call you will get a ManipulationBatchResult, which has the same methods that return the sum of all rows. Additionally, you have access to all individual ManipulationResults.