Connecting to Databases with JDBC #

Diego provides a lightweight wrapper around plain JDBC to make working with databases a simple affair.

Enabling the JDBC plugin #

To enable the JDBC plugin, mark it as a dependency on your main application class:

@Uses(JDBCPlugin.class)
class MyApp extends WebMvcConfiguration { ... }

The Database class #

The Database class is best suited for simple data operations. Here’s an example of how it can be used to execute a query and return a result:

@Inject Database db

String email = db.createQuery("select email from Customer where id = 1").value();

Easy, right?

The type of object returned from the value() method is determined by your JDBC driver and the internal mapping logic for mapping java.sql types to “usable” types (explained later).

Using positional parameters #

You can use positional parameters in your query to guard against SQL injection:

Integer id = 1;
String email = db.createQuery("select email from customer where id = ?", id).value();

Using named parameters #

Named parameters are also supported. Simply pass a Map that contains the values for all of the parameters:

Map<String, Object> params = Map.of("id", 1);
String email = db.createQuery("select email from customer where id = :id").params(params).value();

Named parameters can also be stored in a regular Java Bean. Just make sure that getters are present for the relevant properties:

/* Our Java Bean */
class Customer {
    private String name, email
    // getters and setters for name and email
}

var customer = new Customer("Donald Love", "donald@lovemedia.com")
db.createQuery("insert into Customer (name, email) values (:name, :email)").bind(customer).executeUpdate();

Insert and return generated keys #

If you are inserting a record that has an auto generated primary key, you can retrieve that key by calling the insertAndReturnKey() method on your query.

String sql = "insert into Customer (name, email) values (?,?)";
Integer id = db.createQuery(sql, "Donald Love", "donald@lovemedia.com").insertAndReturnKey();

Basic Bean mapping #

In addition to Maps, Lists of Maps and simple scalar types, you can return a Bean or a list of Beans from a query. Just ensure that getters and setters are present for the properties you wish to populate:

Customer customer = db.createQuery("select name, email from Customer where id = 1").findRow(Customer);

Cleaning up #

If you use the Database class outside of the request/response lifecycle, you should invoke the close() method to close any open sessions. You can do this automatically in a try-with-resources block:

try(Database db = load(Database)) {
    // .. use the database
}

The Service class #

The Service class helps you to organize your data access logic. In a more complex application, such as an online book store, you may have tables for books, customers and purchases. You’d probably want to create a Service for each of those tables to properly encapsulate data access logic.

A Service class for managing customers might look like this:

class CustomerService extends Service {
    
    public Customer findCustomerById(Long id) { 
        // A Service class has access to the same createQuery() methods as the Database class.
    	createQuery("select * from customer where id = ?", id).findRow(Customer);
    }
    
    public void changePassword(Long id, String newPassword) {
        createQuery("update customer set password_hash = ? where id = ?", hashPassword(newPassword), id).executeUpdate();
    }
    
    // Other methods
}

To use your service class, inject it into your application:

@Inject CustomerService customerService

@GET
public Result show(Long id) {
    var customer = customerService.findCustomerById(id);
    return json(customer);
}

Reading query data from files #

Sometimes, you may have an especially long query that would be cumbersome to manage in your application code. The Service class provides a convenient file() method for returning the content of any *.sql file in your {diego.resourceBase}/sql directory. Here is an example of how to use it:

Integer createCustomer(Customer customer) {
	return createQuery(file("create-customer")).bind(customer).insertAndReturnKey()   
}

Assuming the value of diego.resourceBase in your configuration file is set to src/main/resources, the above example reads the content of the file src/main/resources/sql/create-customer.sql and binds its named parameters to the properties of the Customer object.

If the file isn’t found, an exception is thrown.

Caching behaviour #

During development, the file content is reloaded each time the file() method is called. You can, therefore, make changes and call your method again without restarting your application.

In production, the file content is read once and cached, and subsequent calls to the file() method for the same file will be served from the cache.

Using the @Transactional annotation #

By default, each interaction with the database - select, update, insert, etc. - takes place within the context of a single transaction which is committed immediately when the method returns.

In the following example, the doTransfer() method of our service class will create and commit separate transactions each time the executeUpdate() method is called:

void doTransfer(Double amount, Account sender, Account receiver) {
    sender.balance -= amount
    receiver.balance += amount
    createQuery("update account ...").bind(sender).executeUpdate() // Transaction committed
    createQuery("update account ...").bind(receiver).executeUpdate() // Transaction committed
}

This looks fine, but if an error occurred while trying to update the receiver’s account, we would have a case where we debited the sender but never credited the receiver 😱

To address this, you should mark the doTransfer() method with the @jakarta.transaction.Transactional annotation to have all the database updates take place within the context of a single transaction.

@Transactional
void doTransfer(Double amount, Account sender, Account receiver) {
    sender.balance -= amount
    receiver.balance += amount
    /* Everything now takes place within a single transaction. */
   	createQuery("update account ...").bind(sender).executeUpdate()
    createQuery("update account ...").bind(receiver).executeUpdate()
}

Notes on the @Transactional annotation #

• If there is an error in any of the database operations, the Service class will attempt to roll back the transaction to preserve consistency in the database.
• If a @Transactional method (parent) calls another @Transactional method (child), all the operations within the child method will take place within the context of the parent’s transaction. ✨
• Automatic transaction management only works if your Service instance was created by the dependency container:

final var reportService = new ReportService() // Bad 👎🏼

@Inject ReportService service // Good 👍🏼

Data mapping #

Given that java.sql types are hardly ever used directly when executing business logic or building views, the JDBC wrapper does some internal mapping to convert these types to their most appropriate counterparts. See the table below:

With the exception of BigInteger, the mapping goes the opposite way when setting parameters in queries i.e. a query parameter of LocalDate will be converted to java.sql.Date and so on.

Different JDBC drivers can return different Java types for the same column types. This link shows how MySQL does its mappings: MySQL Java, JDBC and MySQL Types.

This information is particularly important when using the value() method of the Query class. For example, you will need to know that a MySQL TIMESTAMP column, will be returned as a java.sql.Timestamp which will be converted to a java.time.LocalDateTime. Therefore, the following expression will probably throw a type casting error:

/* value() here will return a LocalDateTime */
Timestamp createdAt = database.createQuery("select created_at from user where id = 1").value()

It’s a bit of extra memorization, but it:

1. Adds a measure of predictability; and
2. Saves you from having to do convert between these types manually when shuttling data in and out of your database.

Configuration #

These are the available configuration settings for the Database functionality.

# The JDBC connection URL
diego.jdbc.url = <JDBC_URL>
# The JDBC driver name
diego.jdbc.driverClassName = <Driver_Class_Name>
# The size of the connection pool
diego.jdbc.maxPoolSize = 10
# Connection timeout
diego.jdbc.connectionTimeOut = 10s
# Specify your DataSource name (Optional)
diego.jdbc.dataSourceClassName = <Class Name>
# The name of the database schema (in cases where you cannot supply the schema name in the URL)
diego.jdbc.schema = <Schema_name>

Notes:

• Depending on your database, you may not need to specify the driverClassName setting
• Aside from url, see the official HikariCP documentation for explanations of the other properties
• Uncomment the schema property if the schema cannot be specified in the JDBC URL.

Next: Sending Emails