Ormlite inner join on three tables

Question

i want to create an inner join on three tables like this one for example:

SELECT C.Description, D.ItemDescription
  FROM OrderDetailStatement AS D 
 INNER JOIN OrderHeaderStatement AS H 
    ON H.OrderHeaderStatementRefID = D.OrderHeaderStatementRefID 
 INNER JOIN customers AS C 
    ON H.CustomerRefID = C.CustomerRefID
 WHERE (D.MixedValue > 1000)

but i'm a little bit confused, could you please provide me a walkthrough?

thanks in advance

Answer 1

ORMLite now supports simple JOIN statements. You can do something like the following:

// start the order header query
QueryBuilder<OrderHeader, Integer> orderHeaderQb = orderHeaderDao.queryBuilder();
QueryBuilder<Customer, Integer> customerQb = customerDao.queryBuilder();
// join with the order query
orderHeaderQb.join(customerQb);
// start the order statement query
QueryBuilder<OrderStatement, Integer> orderStatementQb =
    orderStatementDao.queryBuilder();
orderStatementQb.where().gt("mixedvalue", 100);
// join with the order-header query
orderStatementQb.join(orderHeaderQb);
List<OrderStatement> orderStatementQb.query();

Notice, however, that you can only get entities from the query builder using this mechanism. If you want to get your two description fields from different objects then you would have to still use a raw-query.

There is support for "raw queries" including the Dao.queryRaw() method where you can use your own SQL. I suspect you've found them already. Here are the docs for raw queries.