ORMLite JOINs, or rawQuery auto mapping

Question

I am looking for a way to do a query that requires a JOIN. Is there any way to do this in a prepared statement, or is the rawQuery the only option that I have. If rawQuery is the only option, then is there some way to automatically map the returned objects to the objects of the Dao being implemented.

I've dug through the documents and examples but cannot find anything that will allow me to map the raw database result to an ORM object class.

Answer 1

I am looking for a way to do a query that requires a JOIN.

ORMLite supports simple JOIN queries. You can also use raw-queries to accomplish this.

You can use the Dao.getRawRowMapper() to map the queries as you found or you can create a custom mapper. The documentation has the following sample code which shows how to map the String[] into your object:

GenericRawResults<Foo> rawResults =
  orderDao.queryRaw(
    "select account_id,sum(amount) from orders group by account_id",
    new RawRowMapper<Foo>() {
            public Foo mapRow(String[] columnNames,
              String[] resultColumns) {
                return new Foo(Long.parseLong(resultColumns[0]),
                    Integer.parseInt(resultColumns[1]));
        }
    });

Answer 2

I've found a way to auto map a result set to a model object.

// return the orders with the sum of their amounts per account
GenericRawResults<Order> rawResults = 
    orderDao.queryRaw(query, orderDao.getRawRowMapper(), param1)

// page through the results
for (Order order : rawResults) {
  System.out.println("Account-id " + order.accountId + " has "
    + order.totalOrders + " total orders");
}

rawResults.close();

The key is to pull the row mapper from your object Dao using getRawRowMapper(), which will handle the mapping for you. I hope this helps anyone who finds it.

I still would love the ability to do joins within the QueryBuilder but until that is supported, this is the next best thing in my opinion.

Answer 3

Raw query auto mapping

I had problem of mapping fields from custom SELECT which return columns that are not present in any table model. So I made custom RawRowMapper which can map fields from custom query to custom model. This is useful when you have query which has fields that doesn't corresponds to any table maping model.

This is RowMapper which performs query auto mapping:

public class GenericRowMapper<T> implements RawRowMapper<T> {

private Class<T> entityClass;
private Set<Field> fields = new HashSet<>();
private Map<String, Field> colNameFieldMap = new HashMap<>();

public GenericRowMapper(Class<T> entityClass) {
    this.dbType = dbType;
    this.entityClass = entityClass;
    Class cl = entityClass;
    do {
        for (Field field : cl.getDeclaredFields()) {
            if (field.isAnnotationPresent(DatabaseField.class)) {
                DatabaseField an = field.getAnnotation(DatabaseField.class);
                fields.add(field);
                colNameFieldMap.put(an.columnName(), field);
            }
        }
        cl = cl.getSuperclass();
    } while (cl != Object.class);
}

@Override
public T mapRow(String[] columnNames, String[] resultColumns) throws SQLException {
    try {
        T entity = entityClass.newInstance();
        for (int i = 0; i < columnNames.length; i++) {
            Field f = colNameFieldMap.get(columnNames[i]);
            boolean accessible = f.isAccessible();
            f.setAccessible(true);
            f.set(entity, stringToJavaObject(f.getType(), resultColumns[i]));
            f.setAccessible(accessible);
        }
        return entity;
    } catch (InstantiationException e) {
        throw new RuntimeException(e);
    } catch (IllegalAccessException e) {
        throw new RuntimeException(e);
    }
}

public Object stringToJavaObject(Class cl, String result) {
    if (result == null){
        return null;
    }else if (cl == Integer.class || int.class == cl) {
        return Integer.parseInt(result);
    } else if (cl == Float.class || float.class == cl) {
        return Float.parseFloat(result);
    } else if (cl == Double.class || double.class == cl) {
        return Double.parseDouble(result);
    } else if (cl == Boolean.class || cl == boolean.class) {
        try{
            return Integer.valueOf(result) > 0;   
        }catch (NumberFormatException e){
            return Boolean.parseBoolean(result); 
        }
    } else if (cl == Date.class) {
        DateLongType lType = DateLongType.getSingleton();
        DateStringType sType = DateStringType.getSingleton();
        try {
            return lType.resultStringToJava(null, result, -1);
        } catch (NumberFormatException e) {
            try {
                return sType.resultStringToJava(null, result, -1);
            } catch (SQLException e2) {
                throw new RuntimeException(e);
            }
        }
    } else {
        return result;
    }
}
}

And here is the usage:

class Model{
   @DatabaseField(columnName = "account_id")
   String accId;
   @DatabaseField(columnName = "amount")
   int amount;
}

String sql = "select account_id,sum(amount) amount from orders group by account_id"
return queryRaw(sql,new GenericRowMapper<>(Model.class)).getResults()

This will return List<Model> with mapped result rows to Model if query column names and @DatabaseField(columnName are the same