Skip to content

Latest commit

 

History

History
150 lines (121 loc) · 5.74 KB

AdvancedUsage.md

File metadata and controls

150 lines (121 loc) · 5.74 KB
Raw

Advanced Usage

Here we're going to go through OrmElf and SqlClosureElf method by method, with examples.

Going back to the SQL on the main page, and adding some here:

CREATE TABLE customer (
   customer_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
   last_name VARCHAR(255),
   first_name VARCHAR(255),
   email VARCHAR(255)
);

CREATE TABLE product (
   product_number VARCHAR(64),
   description VARCHAR(255),
   weight_grams DECIMAL(7,2)
);

CREATE TABLE order (
   order_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
   customer_id INTEGER NOT NULL,
   order_dt DATE NOT NULL,
   CONSTRAINT order_cust_fk FOREIGN KEY (customer_num) REFERENCES customer (customer_num)
);

CREATE TABLE order_items (
   order_id INTEGER NOT NULL,
   product_number VARCHAR(64),
   item_count INTEGER NOT NULL,
   CONSTRAINT item_order_fk FOREIGN KEY (order_id) REFERENCES order (order_id),
   CONSTRAINT item_product_fk FOREIGN KEY (product_key) REFERENCES product (product_key)
);

One of the basic tenents of SansOrm is SQL-first development. If your application has any hope of scaling, it is essential that the database schema be correct and completely ignore whatever you might end up doing on the Java-side to map objects onto it.

OrmElf.statementToObject and OrmElf.resultSetToObject

Full signature: T resultSetToObject(ResultSet resultSet, T target) throws SQLException
Full signature: T statementToObject(PreparedStatement stmt, Class<T> clazz, Object... args) throws SQLException

You don't necessarily have to have a one-to-one mapping between Java classes and tables, in fact it may be rare that you do. You can synthesize a class completely from whole cloth. Take for example a class used to display an order summary. Let's pull a few more tricks here...

public class OrderSummary {
   @Column(name = "order_id")
   private int orderId;

   @Column(name = "full_name")
   private String fullName;

   @Column(name = "total_items")
   private int itemCount;
}

Notice this class has no @Table annotation because it does not map to a specific table. Also, notice that full_name does not map to any existing column name in the tables, neither total_items. Using the OrmElf we can populate it like so:

public OrderSummary getOrderSummary(final int orderId) {
   return SqlClosure.execute(connection -> {
      PreparedStatement pstmt = connection.prepareStatement(
         "SELECT order_id, first_name + ' ' + last_name AS full_name, SUM(oi.item_count) AS total_items " +
         "FROM order o, customer c, order_items oi " +
         "WHERE c.customer_id = o.customer_id AND oi.order_id = o.order_id AND o.order_id = ?");
      return OrmElf.statementToObject(pstmt, OrderSummary.class, orderId);
   });
}

The OrmElf will take the column names returned from the query and find the matching class members by their annotations, and populate the object for you.

It is important to note that OrderSummary as an entity cannot be inserted or updated to the database. And we think it's a positive, not a negative, that you can see the SQL right in the code; and developers maintaining and changing your code will appreciate it too.

OrmElf.objectById

Full signature: T objectById(Connection connection, Class<T> clazz, Object... args) throws SQLException

// unfinished documentation... sorry

Conversion

In some cases the default conversion from JDBC type to Java type might not cut it. An enum, for example, can be saved to a Types.VARCHAR column, but would not automatically be marshaled back to an enum when retrieved.

In other cases, there may not be a way to set the field from the JDBC type. For example, JSR-310 java.time.* classes cannot be automagically set from the JDBC types. Enums also cannot be set from the JDBC types.

In these cases, you need to help provide conversion hints.

This is accomplished by adding the @Convert annotation to the field and identifying an AttributeConverter class that can handle the conversion.

Let's tackle using a java.time.LocalDate field that will be stored as a Types.DATE column.

First we need an AttributeConverter implementation:

import javax.persistence.AttributeConverter;
import java.sql.Date;
import java.time.LocalDate;

/**
 * class LocalDateAttributeConverter: An attribute converter for LocalDate &lt;-&gt; Date conversion.
 */
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {
    @Override
    public Date convertToDatabaseColumn(LocalDate localDate) {
        return localDate == null ? null : Date.valueOf(localDate);
    }

    @Override
    public LocalDate convertToEntityAttribute(Date date) {
        return date == null ? null : date.toLocalDate();
    }
}

This class implements the JPA'a AttributeConverter interface and covers marshaling to/from LocalDate and Date objects. The implementation is pretty simple as the conversion is handled by Java classes themselves.

For our field, we will just need to decorate with the @Convert annotation. Using the Order class, we must add:

import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Id;
import javax.persistence.Table;
...
@Table(name = "order")
class Order {
   ...
   @Column(name = "order_dt")
   @Convert(converter = LocalDateAttributeConverter.class)
   LocalDate ordered;
   ...
}

Now when the Order class is persisted or retrieved, SansOrm will be able to translate the Types.DATE column to/from the LocalDate field.

You can apply a similar process to Enums that cannot be handled with integer @Enumerated handling (i.e. persisted as String representations rather than ints), conversion between other types (i.e. a string column parsed to a javax.money.MonetaryAmount, etc.).