Tornado Query - SELECT

SELECT without a Mapper and no domain object

Even when you don't use a Mapper, one is automatically created behind the scenes. The Mapper is then primarily used for caching information so that subsequent operations can be performed faster. That being said, it is always recommended that you use a predefined, static final Mapper for best performance.

If you don't supply a Mapper, and maybe you don't even have a domain object, you can get the results from the database as either a HashMap or a List<HashMap>. This can be OK for small operations:

Map country = Query.create(HashMap.class, "SELECT * FROM country WHERE id = 1").first();

The first() method will retrieve the first row that matches your query and turn the ResultSet into a HashMap. You can also obtain and use a new Query element this way:

Map country = new Query<HashMap>().select("SELECT * FROM country WHERE id = 1").first()

It is recommended to use the static create approach, as it has more convenience methods. Let's send in the id value as a parameter, and return a list of Countries using the rows() method:

Map country = Query.create(HashMap.class)
    .add("SELECT * FROM country WHERE id < 100")
    .param("id", 100)
    .rows();

SELECT without a Mapper, but with domain object

Even without a Mapper, Tornado Query can still convert the ResultSet into your domain object. You will only get columns mapped to fields where the name is the same, or where underscores in the columns can be swapped out for camelCase syntax in the domain object. So you can write:

Country country = Query.create(Country.class,
    "SELECT * FROM country WHERE id = 1").first();

Though this works, it is highly recommended to create a Mapper for your domain objects.

SELECT with a Mapper

This is where Tornado Query shines! The basic select is then turned into:

Country country = Query.select(countryMapper).where("id = :id").param("id", 1).first();

There is also a shortcut to select by id:

Country country = Query.byId(countryMapper, 1).first();

Joins

Let's query for a Customer object instead. First we'll insert a country and an address, along with a customer. Then we'll perform some queries against this customer.

Country usa = new Country(1, 'USA');
Query.insert(countryMapper, usa);

Address address = new Address("Sesame Street", "10001", "New York", usa);
Query.insert(addressMapper, address);
                
Customer customer = new Customer("Edvin Syse", "my@email.addr");
customer.setDeliveryAddress(address);
customer.setBillingAddress(address);
Query.insert(customerMapper, customer);

First we insert the country. No surprises there. Next up is the address, which references the country. Lastly, we insert a customer, which references the address two times, for the deliveryAddress and billingAddress fields.

Now let's select a customer. Since our customerMapper references the addressMapper , and the addressMapper references our countryMapper, we'll get automatic joins if we select using the customerMapper.

Customer customer = Query.byId(customerMapper, 1).first();

The above select query will turn into the same SQL sentence you saw in the Mappers introduction.

Dynamic mutators

Sometimes, part of the mutators are only supposed to be applied depending on certain conditions. Concider the following query:

Customer customer = Query.select(customerMapper)
    .where("id = :id")
    .and("name = :name")
    .param(...)
    .first();

Firstly, the and("name = :name") is just a shortcut for writing add("AND name = :name"). Let's suppose that this query was part of a DAO query, where both id and name was optional parameters. Then it would make sense to only add the mutators that was actually sent to the method:

public Customer getCustomer(Integer id, String name) {
    return Query.select(customerMapper)
        .where()
        .addIf(id != null, "id = :id")
        .addIf(name != null, "name = :name")
        .param("id", id)
        .param("name", name)
        .first();
}

The where() method will make sure that a WHERE expression is added to the statement if any additional mutators are added. Second, the addIf() will only add the following sentence to the query if the expression is true. There is an addUnless method as well, to make your code more readable.

NOTE: If you dislike this approach, you can create your query any way you like, using any programmatic approach you feel comfortable with, as long as the named parameters you mentioned in your SQL sentence is added as param()'s to your query.

This makes sure that even the corner cases can be handled elegantly. The Query class accepts normal SQL, there is no magic, so you should never need to drop out to a plain old Connection. If you need it though, it's still available via Query.connection.get().

Repeaters

Especially when performing "search" queries, we often repeat part of the SQL sentence multiple times. Tornado Query can express this elegantly. Concider the following SQL query:

SELECT  *
FROM    users
WHERE   UPPER(username) LIKE UPPER('%min')
OR      UPPER(username) LIKE UPPER('%mon%')
OR      UPPER(username) LIKE UPPER('man')

This is how you would write it using Tornado Query:

List<String> usernames = Arrays.asList("%min", "%mon%", "man");

List<User> users = Query.create(User.class)
    .select().from("users").where()
    .repeat("OR", "UPPER(username) LIKE UPPER(:usernames[])")
    .param("usernames", usernames)
    .rows();

Here we didn't use a resultMap, and selected straight from a fictive table called users. The repeat() method made sure that each entry in the usernames list would result in another LIKE query for that particular username, glued together with an OR.

Want more?

We have just scratched the surface of what you can do with Tornado Query. Consult the JavaDoc for more information or email me at es@syse.no. If you have any questions or would like to see additional stuff covered in this tutorial.