Databases

Working with Databases #

Database connection and querying is handled using Hibernate, the reference Java Persistence API (JPA) implementation.

Hibernate is a fairly complex tool with a vast API, but Diego provides some helpful utility classes that make it easy to perform common database operations.

💡 If you wish to learn more about the JPA specification, Learn JPA & Hibernate is a helpful resource.

Configuring a database #

Below is a snippet from application.conf showing how to connect to an embedded H2 database:

# Database configuration.
app.db {
  user: "sa"
  password: ""
  url: "jdbc:h2:./my-database"
  driver: "org.h2.Driver"
}

Here we specify the database driver, JDBC URL, username and password - all the fields you would need to connect to a database.

Extra settings #

The app.db.ext block allows you to set extra properties for things like database driver configuration, connection pool management and so on.

app.db.ext {
  "some.jpa.setting": "value"
}

The starter project should include some sensible defaults, but if you would like to tweak them further you can look at these resources:

The persistence framework #

You will mainly interface with your database through three main classes:

  • Database - A low level data access object. Useful for simple data retrieval and storage.
  • Service - An abstract class that you will extend to encapsulate data access logic and provide a bespoke API.
  • EntityCollection - A reference to a table represented by JPA entities.

The one you choose to use at any given time will be based on how you wish to structure your application. We will go through examples of each in this section to give you an idea of the scenarios that best suit each one.

The persistence framework relies on dependency injection for certain critical functionalities. You will learn more about this in the Dependency injection chapter, but for now just follow along to understand how the API works.

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").findRow(String)

Easy, right?

Using positional parameters #

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

Long id = 1l
String email = db.createQuery("select email from customer where id = ?1", id).findRow(String)

Using named parameters #

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

var params = [id: 1]
String email = db.createQuery("select email from customer where id = :id", params).findRow(String)

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()

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)

The Bean mapping featured here only works for simple beans and not nested objects. If you wish to use more advanced Object-Relational mapping functionalities, consider the EntityCollection class.

Column mapping conventions #

When using the SqlQuery class, all snake_case fields will be converted to camelCase such that:

db.createQuery("select id, some_attribute from some_table where id = 1").findRow()

Will return:

{
    id, someAttribute
}

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 = inject(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.

The Service class is an abstract class that provides all of the functionality of the Database class.

A Service class for managing customers might look like this:

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

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

@Inject CustomerService customerService

router.get('/customers/:id', (req, res) -> {
    Long id = Long.parseLong(req.pathParams['id'])
    var customer = customerService.findCustomerById(id)
    res.ok().sendJson(customer)
})

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 @javax.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 👍🏼

The EntityCollection class #

The Database and Service classes use regular SQL queries for interacting with your database, but the EntityCollection class is your gateway to Object-Relational mapping using the JPA with Hibernate.

Creating Entity classes #

To create an Entity class, simply add the required JPA annotations to a regular Java Bean. Our Customer class as an Entity would look like this:

@Entity
class Customer {
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id
    String name
    String email
    // Getters and setters
}
  • The javax.persistence.Entity annotation signifies that this class represents a row in the customer table.
  • The @Id annotation signifies that this field is the primary key of the table.
  • The @GeneratedValue signifies that this field will be populated with an auto_increment identifier from the database.

On the database side, the above class maps to the following table structure:

FieldTypePrimary Key
idbigint✔
namevarchar(100)
emailvarchar(100)

Before you can use the Customer class, it must be listed in your database configuration:

# List each entity
app.db.entities: ["models.Customer"]
# Or if you have more than one entity, you can specify a package name
app.db.packagesToScan: ["models"]

Naming conventions #

The following database naming conventions should be adopted in order to facilitate smooth integration with the framework:

DatabaseConvention
TableSnake case; singular (customer, shopping_cart)
FieldSnake case (created_at)
Foreign key[table_name]_[id]

You can, however, adjust this convention via the relevant JPA annotations.

Working with Entity classes #

Once you have your database configured and your models annotated, you can now use the EntityCollection class to manage your entities.

You can get a reference to an EntityCollection via the Database or Service classes:

@Inject Database db

EntityCollection<Customer, Long> customers = db.collection(Customer, Long)

Here are some examples of how to perform some common operations:

Saving a record # 

final var customer = new Customer(name: "John Doe", email: "john@mail.com")
customers.insert(customer) // Customer saved to database.

The id field will be populated with the auto generated ID from the database if you use the @GeneratedValue annotation.

Finding a record by its ID # 

var customer = customers.find(1l)

Updating a record # 

var customer = customers.find(1l) // Find the record.
customer.email = "new@mail.com" // Make a change.
customers.update(customer) // Update the database.

Deleting a record # 

customers.delete(1l)

Custom queries #

You can use JPQL expressions to query the database. The API is very similar to that of the Service and Database classes:

List<Customer> results = customers.createQuery("from Customer c where ...").setMaxRows(10).findList()

Using Data Transfer Objects (DTOs) #

Data Transfer Objects are useful when you want to leverage the ORM and validation framework to efficiently perform a partial update to an entity.

Take the following Entity model:

// Customer Entity class
@Entity
class Customer {
    @Id
    Long id
    String name
    String email
    Date lastOrderDate
    @CreationTimestamp
    LocalDateTime createdAt
    // Maybe 1,000 other fields...
}

Chances are we would not be working with this entity in its entirety, but we would be managing parts of it using smaller forms throughout the application. Each form will also have its own validation logic.

Each form could implement the DTO interface that contains the tools required to facilitate automatic data mapping to the main entity class.

Let’s look at a DTO that represents an email change form:

// Customer DTO with Validation annotations
class ChangeEmailForm implements DTO {
  Long id
  @NotBlank
  String email
}

You’d notice that:

  1. The class implements DTO :
  2. The DTO only contains the properties that we are interested in updating, and
  3. The properties of the DTO correspond with the properties of the entity class.

Now we can use the DTO as part of the data validation workflow, and as a data source for the update operation on theCustomer table:

final var db = app.get(Database)
app.post("/customer/:id/change-email", (req, res) -> {
  final var changeEmailForm = Form.create(ChangeEmailForm).parse(req)
  db.update(changeEmailForm.id, changeEmailForm) // Updates the Customer row with only the data on the form.
  res.ok().send()
})
You can read more about the DTO pattern here.

Next: Dependency injection