Query Builders - rife2/rife2 GitHub Wiki

RIFE2 provides an object-oriented DSL that allows you to build SQL queries in Java. Based on the datasource used, the SQL syntax will automatically be adapted and missing capabilities (like offset and limit) will be implemented for those databases that don't offer them.

RIFE2's query builders closely follow the standard SQL syntax, so any existing SQL knowledge you have will directly apply. If you have little SQL experience, it's recommended to first get familiar with how SQL works before using the RIFE2 query builders.

TIP: The database engine is very deep and complete, allowing for most SQL and RDBMS operations. A good source of documentation are the tests that cover each aspect of the database engine in detail, as well as the javadocs.

If you get stuck wondering how to achieve a certain operation, please make sure to take a look at https://github.com/rife2/rife2/tree/main/src/test/java/rife/database and https://rife2.github.io/rife2/rife/database/package-summary.html

You can create a new database table by using the CreateTable class, for example:

CreateTable createQuery = new CreateTable(datasource)
    .table("hello")
    .column("name", String.class, 50);

This will generate the SQL to create a database table with the name hello and the datasource's most appropriate type to store a Java String with a maximum length of 50 characters.

To execute this query, RIFE2 provides a class that manages all the JDBC intricacies, called DbQueryManager.

For example:

new DbQueryManager(datasource).executeUpdate(createQuery);

Let's now update our datasource example to have an element that allows you to create the database structure from a browser.

Edit src/main/java/hello/AppSite.java and add in the new pieces:

package hello;

import rife.database.*;
import rife.database.queries.*;
import rife.engine.*;

public class AppSite extends Site {
    Datasource datasource = new Datasource(
        "org.h2.Driver", "jdbc:h2:./embedded_dbs/h2/hello", "sa", "", 5);
    DbQueryManager manager = new DbQueryManager(datasource);
    CreateTable createQuery = new CreateTable(datasource)
        .table("hello").column("name", String.class, 50);

    Route install = get("/install", c -> {
        manager.executeUpdate(createQuery);
        c.print("Installed");
    });

    public static void main(String[] args) {
        new Server().start(new AppSite());
    }
}

TIP : You can find the full database example in the RIFE2 GitHub repository.

The install route listens to the /install path, executes the createQuery to create the database table and prints out a success message. If you visit it again, an exception will trigger because the structure already exists. You'll notice that RIFE2 provides an easy-to-read default error page that makes it convenient to debug during development.

Try it out by starting the app with Gradle or bld and visiting http://localhost:8080/install in your browser.

./bld run

or

./gradlew run

During testing, you'll probably want to be able to drop the table that you just created. This can be done with the DropTable query builder.

For example:

    // ... add after createQuery ...
    DropTable dropQuery = new DropTable(datasource)
        .table(createQuery.getTable());

You'll notice that instead of using "hello" as the table name, you can retrieve the table name from the CreateTable query and always be that it's identical.

You can now also add the route that allows you to remove the database structure:

    // ... add after install ...
    Route remove = get("/remove", c -> {
        manager.executeUpdate(dropQuery);
        c.print("Removed");
    });

Visiting http://localhost:8080/remove in your browser now removes the database structure.

NOTE : Using get routes for install and remove is done to simplify this documentation. You should never use a get HTTP method that results into data changes, consider using a post route instead.

Next, let's create a little form that allows you to enter a name and submit it for insertion into the database.

First, create an insert query that stores the name into your database:

    // ... add after dropQuery ...
    Insert insertQuery = new Insert(datasource)
        .into(createQuery.getTable())
        .fieldParameter("name");

The fieldParameter method is very convenient, declaring a prepared statement parameter that you can later set by name instead of by index, contrary to JDBC.

Now, create the route and form in which you can enter the name:

    // ... add after remove ...
    Route addForm = get("/add", c -> c.print("""
        <form method='post'>
        <input name='name'/><input type='submit'/>
        </form>""")
    );

When the form is submitted, you'll need a route that handles the post request:

    // ... add after addForm ...
    Route add = post("/add", c -> {
        var name = c.parameter("name");
        manager.executeUpdate(insertQuery,
            statement -> statement.setString("name", name));
        c.print("Added " + name + "<br><br>");
        c.print("<a href='" + c.urlFor(addForm) + "'>Add more</a><br>");
    });

NOTE : In order to simplify the example, the HTML construction is done with strings. In reality you will likely use templates instead.

The executeUpdate method takes insertQuery as the first argument, and an instance of PreparedStatementHandler as the second. Most often, this can be a lambda to simply set the named statement parameters, but creating an anonymous inner class that extends DbPreparedStatementHandler provides more features, should you need those.

The lambda implementing PreparedStatementHandler gets the active statement as an argument, and you can then simply set each parameter as necessary, for instance: statement.setString("name", name).

After adding these snippets to your example app, visit http://localhost:8080/add to fill in a few names.

NOTE : If your database structure is not installed, you'll get an error when adding a name, make sure to install it first as explained above.

Finally, you'll want to display all the names you entered, a SelectQuery is exactly what you want:

    // ... add after insertQuery ...
    Select selectQuery = new Select(datasource)
        .from(createQuery.getTable())
        .orderBy("name");

The element that uses this could be:

    // ... add after addForm (NOT after add) ...
    Route list = get("/list", c -> {
        manager.executeFetchAll(selectQuery,
            resultSet -> c.print(resultSet.getString("name") + "<br>"));
        c.print("<br><a href='" + c.urlFor(addForm) + "'>Add more</a><br>");
    });

You'll notice the executeFetchAll method, it executes the selectQuery and then iterates through all the results and calls the lambda that implements RowProcessor for each row. This lambda gets the resultSet of the row, which you can use to retrieve the value of each column that was returned by the query. Alternatively, you can also use an anonymous inner class and extend DbRowProcessor to access additional features, should you need those.

Finally, let's improve the add route and create a link towards listing the names for convenience:

    Route add = post("/add", c -> {
        // ...
        c.print("<a href='" + c.urlFor(list) + "'>List names</a><br>");
    });

When you now visit http://localhost:8080/list, you can see all the names you added before, and you have links to conveniently navigation between the add and list pages.

You've seen four of the query builder classes, RIFE2 provides a complete set of classes that represent standard SQL. Each class has carefully designed methods to represent the syntax of each SQL statement in Java as closely as possible, and often adds convenience features that are not available when expressing SQL as a string.

new CreateSequence(datasource);
new CreateTable(datasource);
new Delete(datasource);
new DropSequence(datasource);
new DropTable(datasource);
new Insert(datasource);
new Select(datasource);
new SequenceValue(datasource);
new Update(datasource);

The DbQueryManager is a convenience class to make it easy to control the queries that handle the retrieval, storage, update and removal of data in a database. All queries will be executed in a connection of the Datasource that's provided to the constructor of the DbQueryManager.

The javadoc of the DbQueryManager class describes all its methods.

The list below summarizes some of the most frequently used ones:

m.executeUpdate(Query);                                                 // execute an update query directly
m.executeUpdate(Query, PreparedStatementHandler);                       // execute a customizable update query
m.executeUpdate(String);                                                // execute update SQL directly
m.executeQuery(ReadQuery, PreparedStatementHandler);                    // execute a customizable select query
m.executeHasResultRows(ReadQuery, PreparedStatementHandler);            // check result rows of a customizable select query
m.executeGetFirstString(ReadQuery, PreparedStatementHandler);           // fetch first string of a customizable select query
m.executeFetchFirst(ReadQuery, RowProcessor, PreparedStatementHandler); // fetch first row of a customizable select query
m.executeFetchFirstBean(ReadQuery, Class, PreparedStatementHandler);    // fetch first bean of a customizable select query
m.executeFetchAll(ReadQuery, RowProcessor, PreparedStatementHandler);   // fetch all rows of a customizable select query
m.executeFetchAllBeans(ReadQuery, Class, PreparedStatementHandler);     // fetch all beans of a customizable select query
m.inTransaction(TransactionUser);                                       // execute enclosed queries in transaction and commit

// ... and much more ...

Next learn more about Transactions