Mappers

The main responsibility of a Mapper is to make sure that your SQL ResulSet is converted into your domain objects. However, since you tell them about the columns in your database, they can also be used to automatically create the SQL to perform CRUD operations.

For now, we'll write Mappers for our example domain objects manually.

public class Mappers {
    public static final Mapper<Country> countryMapper = new Mapper(Country.class)
        .tablename("country")
        .id("id", "id", "country_id", INTEGER)
        .property("name", "name", VARCHAR);

    public static final Mapper<Address> addressMapper = new Mapper(Address.class)
        .tablename("address")
        .id("id", "id", "address_id", INTEGER)
        .property("street", "street", VARCHAR)
        .property("zip", "zip", VARCHAR)
        .property("city", "city", VARCHAR)
        .join("country", countryMapper, "country");

    public static final Mapper<Customer> customerMapper = new Mapper(Customer.class)
        .tablename("customer")
        .id("id", "id", "customer_id", INTEGER)
        .property("name", "name", VARCHAR)
        .property("email", "email", VARCHAR)
        .join("deliveryAddress", addressMapper, "delivery_address")
        .join("billingAddress", addressMapper, "billing_address");
}

Tornado Query can auto-create Mapper objects by trying to match column names to domain object properties. It will even figure out how domain objects fit together based on your database foreign keys. After you have auto-created a Mapper, you are free to alter it manually, or you can ofcourse write your mapper objects manually.

The Mapper#generate() method is used to auto-create Mappers. The generator creates one Java class for each domain object, and adds a static final field for the actual Mapper. So, the Mapper for a Customer object would be accessed via CustomerMapper.FULL. You are free to store the Mappers any way to like, infact in our example we'll create a single class called Mappers, and put our Mappers in there.

Country mapper

Let's examine the countryMapper first. The constructor takes the domain object class as it's argument. Next, we supply the name of the table (country). The id method maps the Country domain object's id to the id column in the country database table. The third argument (country_id) tells the Mapper that the id should be auto-generated by incrementing the country_id sequence. The last argument is the java.sql.Types type of the database column. You actually don't have to supply this argument, Tornado Query can figure it out, and will cache the result. If you supply it, the first query will be a couple of milliseconds faster.

As you probably guessed by now, the property method maps the name field to the name column.

Address mapper

The addressMapper starts out in much the same way as the countryMapper, except it has more fields, mapped with property mappings. The interesting bit however, is the join method. This single line will merge the countryMapper into the country property of our Address domain object, so that all fields joined in from country will be populated into our Address object.

Instead of referencing the countryMapper, we could also have mentioned the fields from Country directly in the addressMapper. The following example gives the exact same mapping possibilities, but with more/explicit code:

    public static final TableJoin countryJoin = new TableJoin("country", "country")
        .on("address.country = country.id");

    public static final Mapper<Address> addressMapper = new Mapper(Address.class)
        .tablename("address")
        .id("id", "id", "address_id", INTEGER)
        .property("street", "street", VARCHAR)
        .property("zip", "zip", VARCHAR)
        .property("city", "city", VARCHAR)
        .property("country.id", "country", INTEGER);
        .join("country.name", countryJoin, "name");

Here we created a TableJoin manually. The contructor takes the table name and an alias to use in the SQL as arguments. Then we give it information about how to perform the join in the on chaining method.

The countryJoin TableJoin is then used as the second argument to the join method, telling the Mapper that the country.name nested property of our Customer object should be filled with the name column from the joined in country table.

For more information about TableJoin and join types, please see the JavaDoc.

As you can see, a lot was given to us for free by referencing the countryMapper, but sometimes you want more control of how you configure your mappings. Under the hood, Tornado Query will create a similar join table on-the-fly when you use the simple approach.

Customer mapper

The customerMapper references the addressMapper two times, for the deliveryAddress and billingAddress properties. Since the addressMapper references the countryMapper, our Customer will automatically know how to both map and join all the way to deliveryAddress.country.name.

NOTE: It is important to understand that you can still write your query by hand, and still use the Mapper to convert your ResultSet into a domain object. You don't need to use the automatically generated join, but it will save you a lot of boiler plate SQL code.

For completeness, this is how the customerMapper would be written if you didn't reference the addressMapper:

TableJoin deliveryAddress = new TableJoin("address", "delivery_address")
    .on("customer.delivery_address = delivery_address.id");

TableJoin deliveryAddressCountry = new TableJoin("country", "delivery_address_country")
    .on("delivery_address.country = delivery_address_country.id");

TableJoin billingAddress = new TableJoin("address", "billing_address")
    .on("customer.billing_address = billing_address.id");

TableJoin billingAddressCountry = new TableJoin("country", "billing_address_country")
    .on("billing_address.country = billing_address_country.id");

public static final Mapper<Customer> customerMapper = new Mapper(Customer.class)
    .tablename("customer")
    .id("id", "id", "customer_id", INTEGER)
    .property("name", "name", VARCHAR)
    .property("email", "email", VARCHAR)
    .property("deliveryAddress.id", "delivery_address", INTEGER)
    .join("deliveryAddress.street", deliveryAddress, "street", VARCHAR)
    .join("deliveryAddress.zip", deliveryAddress, "zip", VARCHAR)
    .join("deliveryAddress.city", deliveryAddress, "city", VARCHAR)
    .join("deliveryAddress.country.id", deliveryAddress, "country", VARCHAR)
    .join("deliveryAddress.country.name", deliveryAddressCountry, "name", VARCHAR)
    .property("billingAddress.id", "billing_address", INTEGER)
    .join("billingAddress.street", billingAddress, "street", VARCHAR)
    .join("billingAddress.zip", billingAddress, "zip", VARCHAR)
    .join("billingAddress.city", billingAddress, "city", VARCHAR)
    .join("billingAddress.country.id", billingAddress, "country", VARCHAR)
    .join("billingAddress.country.name", billingAddressCountry, "name", VARCHAR);

Executing: 

Query.select(customerMapper).rows();
Gives you this SQL for free, and maps your result to a List<Customer>: 
SELECT  customer.id,
        customer.name,
        customer.email,
        customer.delivery_address,
        delivery_address.street AS delivery_address_street,
        delivery_address.zip AS delivery_address_zip,
        delivery_address.city AS delivery_address_city,
        delivery_address.country AS delivery_address_country,
        delivery_address_country.name AS delivery_address_country_name,
        customer.billing_address,
        billing_address.street AS billing_address_street,
        billing_address.zip AS billing_address_zip,
        billing_address.city AS billing_address_city,
        billing_address.country AS billing_address_country,
        billing_address_country.name AS billing_address_country_name
FROM    customer
        JOIN address AS delivery_address
            ON customer.delivery_address = delivery_address.id
        JOIN country AS delivery_address_country
            ON delivery_address.country = delivery_address_country.id
        JOIN address AS billing_address
            ON customer.billing_address = billing_address.id
        JOIN country AS billing_address_country
            ON billing_address.country = billing_address_country.id

I'm pretty sure you realize the potential savings by using the full power of Mappers by now :)

RowConverters - native ResultSet to domain objects mapping

The Mapper uses reflection to convert a ResultSet into domain objects. It takes care of instantiation objects in deep object graphs to avoid null pointer exceptions, and it is very fast. However, nothing can beat the speed of explicitly converting a ResultMap to a domain object, so you can choose to add a RowConverter to your Mapper to do exactly this.

NOTE: Using a RowConverter is very seldom needed, and the speed of the default reflection based mapping should be adequate for most uses cases. However, it might be worth the extra code in cases where you need to convert ResultMap entries into exotic domain members, or to map to a domain object that can only be created correctly through calling a specific constructor method for example.

Let's add a RowConverter to our CountryMapper:

countryMapper.rowConverter(new RowConverter<Country>() {
    public Country convert(ResultSet rs) throws SQLException {
        Country country = new Country();
        country.setId(rs.getInt("id"));
        country.setName(rs.getString("name"));
        return country;
    }
});

Auto increment / sequences

The id method is just a shortcut for the following:

String property = "id";
String column = "id";
int sqlType = java.sql.Types.INTEGER;
Mapping mapping = new Mapping(property, column, MapType.PRIMARY_KEY)
                    .sqlType(sqlType).sequence("id");
// Add to mapper
.property(column, mapping);

By setting the MapType to PRIMARY_KEY we tell the Mapper about what column to join on, and what fields to use in byId queries. The sequence property is again a shortcut to automatically retrieving a sequence value for the id property of the domain object. This can also be done manually in an INSERT statement, see INSERT usage for more information.