SQL data types are a tricky thing, especially when considering mapping them to Java.

1. Supported types and names are different between vendors.
2. Some types have variable length, scale and precision. There is actually a countless number of sub types.
3. Finally, different projects choose to map database types differently. Even worse, in the same project same SQL type can be mapped to Java type differently depending on the context.

There are 2 primary use cases involving data types:

1. CASTs - we want to be able to use SQL CAST operator.
2. Literals - we want to be able to write a string constant, e.g. 2016-03-04, and treat it as a Date type in SQL. Intervals are commonly used as hard-coded literals, see usage examples here.

In both cases, when used in FluentJPA, we want that after CASTing or using a literal, the result would be of the correct Java type, making the compiler happy.

FluentJPA aims support all the use cases and creates a statically typed dynamic type system. The following explains how this is achieved and how to use it.

1. Per vendor FluentJPA defines an enum DataTypeNames that contains all the vendor type names and provides convenient create() methods (more on them below).
2. Defines a DataType<T> interface:

• T is the mapped Java type, e.g. Integer.
• This interface defines cast() and of() (type literal) methods, which both return T in Java and produce the typed SQL during translation. Also there is a raw() method which outputs the expression as is, letting the Database to decide about the required conversion.

SQL Server does not support string literals. Use cast() or raw() methods instead.

Per vendor there is DataTypes interface, containing declarations like these:

// Java type Timestamp is mapped to SQL type TIMESTAMP (cast and literal work correctly)
final DataType<Timestamp> TIMESTAMP = DataTypeNames.TIMESTAMP.create();
// Java type BigDecimal is mapped to SQL type NUMERIC
final DataType<BigDecimal> DECIMAL = DataTypeNames.NUMERIC.create();

It contains the most obvious type declarations with naming convention following the vendor names. Of course any "varying" types are missing.
All this is a preparation for creating your own, project specific types set! E.g.:

// this is supposed to be written in the customer project
public interface MyProjectDataTypes {
    final DataType<String> SHORT_STRING = DataTypeNames.VARCHAR.create(255);
    final DataType<String> MEDIUM_STRING = DataTypeNames.VARCHAR.create(1024);
    final DataType<String> LONG_STRING = DataTypeNames.VARCHAR.create(4096);

    // refer a "standard type"
    final DataType<Timestamp> TIMESTAMP = DataTypes.TIMESTAMP;

    // refer a "standard type" and provide a domain specific name
    final DataType<Integer> INTERVAL = DataTypes.INT;
    ...
}

Now, provided MyProjectDataTypes is added to static imports, we can write a query like this:

FluentQuery query = FluentJPA.SQL((CrunchbaseCompany c) -> {

    Date foundedAgo = subtract(NOW(), TIMESTAMP.cast(c.getFoundedAtClean()));
    // foundedAtClean is a String -------------------^^^^^^^^^^^^^^^^^^^
    SELECT(c.getPermalink(), c.getFoundedAtClean(), foundedAgo);
    FROM(c);
    WHERE(c.getFoundedAtClean() != null);

});

Or

SELECT DATE_ADD(c.getFoundedAt(), INTERVAL.of(1, TimeUnit.DAY)); // MySQL
SELECT add(c.getFoundedAt(), INTERVAL.of(1, DatePart.WEEK)); // PostgreSQL