Mapping

When it comes to mapping, DbOom tries its best to match database types with Java types of POJO properties (i.e. mapped columns). DbOom knows how to convert between various SQL types and common Java types, including enums. SQL types in DbOom are actually implementations of SqlType, that defines how to convert values between SQL and Java types.

Custom Mapping

It is possible to define custom SQL types, i.e. custom type mappings. They can be defined in two ways:

• globally: when custom SqlType implementations are registered in SqlTypeManager. These SQL types are then available for all Java properties of the same type across the application.

• locally: defined in @DbColumn annotation by setting sqlType element, then this custom type applies only on annotated property.

SQL types defined in annotation are always used, even if java type of a property has its own SQL type already registered.

Naming strategies

For successful mapping and DbOom functionality, table and column naming strategies must match how JDBC driver of destination database works.

This is very important to understand! DbOom has table and column name naming strategies that define how entity/column names are converted to and from the mapped class/property names. Notice that these naming strategies are used in both directions: when converting from table/column name to class/property and vice-versa (i.e. mapping); and when converting from class/property to table/column name (i.e.resolving).

For example, if you have a table JJ_FOO_BAR (prefix and uppercase) and column value_data (lowercase), it may be mapped to class FooBar (camel-case strategy) and property valueData (again, camel-case). The opposite mapping also has to match: class UserData may be resolved to e.g. table EX_USER_DATA_N (uppercase with both prefix and suffix); property valueData may be resolved to column value_date (lowercase).

Here are the possible naming strategies options (defined in DbOomManager):

• splitCamelCase - if camel case words should be split with separatorChar.
• separatorChar - simple char used when splitCamelCase is true, by default its _
• changeCase - when true (default) table or column names will be changed to upper or lowercase.
• uppercase or lowercase - defines it table or column name should be converted to upper case or lowercase.
• prefix and suffix - table names may have prefix and/or a suffix.

Why is this important? Naming strategies are important since JDBC drivers works differently then you expect.

For example, you may define your database using uppercases name, but JDBC driver simply returns lowercase values. Therefore, by setting correct naming strategy you match how JDBC driver works and DbOom will work flawlessly.

Wrong naming strategy is the most common configuration mistake when using DbOom!

Therefore, when working with DbOom, please use uniform naming convention across the whole database and please match it with how JDBC drivers work! One thing that can help is to enable logging. If you see WARN message like this:

 [WARN] Column SQL type not available: DbEntity: TESTER2.TIME

then it is a sign that mapping or naming conventions might be wrong.

Example

The best thing to explain all possibilities is the following example:

@DbTable
public class Foo {

    @DbId
    public long id;

    @DbColumn
    public MutableInteger number;

    @DbColumn(
        sqlType = IntegerSqlType.class)
    public String string;

    @DbColumn
    public String string2;

    @DbColumn
    public Boo boo;

    @DbColumn
    public FooColor color;

    @DbColumn(
        sqlType = FooWeigthSqlType.class)
    public FooWeight weight;

    @DbColumn
    public Timestamp timestamp;

    @DbColumn
    public Clob clob;

    @DbColumn
    public Blob blob;

    @DbColumn
    public BigDecimal decimal;

    @DbColumn
    public BigDecimal decimal2;

    @DbColumn
    public JDateTime jdt1;

    @DbColumn
    public JDateTime jdt2;
}

create table FOO (


    ID          integer     not null,


    NUMBER      integer     not null,


    STRING      integer     not null,



    STRING2     integer     not null,


    BOO         integer     not null,


    COLOR       varchar     not null,



    WEIGHT      integer     not null,


    TIMESTAMP   timestamp   not null,


    CLOB        longvarchar not null,


    BLOB        longvarbinary not null,


    DECIMAL     decimal     not null,


    DECIMAL2    varchar     not null,


    JDT1        bigint      not null,


    JDT2        varchar     not null,

    primary key (ID)
)

Most of above mappings are straightforward: number fields are mapped to number java types, varchars to strings, etc. There are some useful additional mappings, like mapping String values to integer columns - of course, it is assumed that string contains only digits. In this example you can see two explicit local mapping, when SQL type is defined in @DbColumn annotation.

Custom mappings

Now something interesting: property boo has a custom type Boo, and it is also mapped to database. Of course, this mapping can't be done automatically. We must provide custom SqlType that explains how to convert database value to and from Boo type. Since we want to use this mapping everywhere, we might register it globally:

    SqlTypeManager.register(Boo.class, BooSqlType.class);

and BooSqlType may look like:

    public class BooSqlType extends SqlType<Boo> {

        @Override
        public void set(PreparedStatement st, int index, Boo value)
                throws SQLException {
            st.setInt(index, value.value);
        }

        @Override
        public Boo get(ResultSet rs, int index) throws SQLException {
            Boo boo = new Boo();
            boo.value = rs.getInt(index);
            return boo;
        }
    }

In this simple example, Boo is stored as an integer in database; however, you can create a more complex SQL type and conversion.

Enum mappings

Lets see how simple enumeration (FooColor) can be stored to database. Enumerations, by default, are stored as strings (varchars…).

Now, enumeration may be stored as other SQL type, but it is necessary to define custom SqlType for mapping conversion. One such implementation may look like:

    public class FooWeigthSqlType extends SqlType<FooWeight> {

        @Override
        public void set(PreparedStatement st, int index, FooWeight value)
                throws SQLException {
            st.setInt(index, value.getValue());
        }

        @Override
        public FooWeight get(ResultSet rs, int index) throws SQLException {
            return FooWeight.valueOf(rs.getInt(index));
        }
    }

If you have enumerations that are mapped to an integer, you don't even have to write custom SQL types! So above SqlType is NOT needed if you design your enumeration like this:

    public enum Status {
        PENDING(0),
        ACTIVE(1),
        COMPLETED(99);

        final int status;
        final String statusString;

        private Status(int status) {
            this.status = status;
            this.statusString = String.valueOf(status);
        }

        public int value() {
            return status;
        }

        @Override
        public String toString() {
            return statusString;
        }
    }

The key thing here is toString() that returns int value as a String. When you map such enum to a column of some int type, everything will work out of box! This is because of behavior of BeanUtil tool. Note that we have cached int value for better performances, to avoid string conversion on every access.

Other mappings

Other mappings from the example are also straightforward. It is interesting to notice that JDateTime is stored as number of milliseconds (compatible with System.currentTimeMillis()).