ORMLite JOINs or automatic rawQuery matching

Question

I am looking for a way to make a request requiring a JOIN. Is there a way to do this in a prepared statement, or is rawQuery only option I have. If rawQuery is the only option, then there is some way to automatically match the returned objects with the objects of the implemented Tao.

I dug through documents and examples, but I can not find anything that will allow me to compare the result of the original database with the class of ORM objects.

Answer 1

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

You can use Dao.getRawRowMapper() to match queries as you found them, or you can create your own matchmaker. The documentation contains the following code example that shows how to map String[] to 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 found a way to automatically bind the 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 string converter from your Dao object using getRawRowMapper() , which will handle the mapping for you. Hope this helps anyone who finds it.

I will still like the ability to make connections inside QueryBuilder , but until this is supported, this will be the next best thing, in my opinion.

Answer 3

Automatically match raw requests

I am having a problem with displaying fields from a custom SELECT that return columns that are missing from any table model. So I created a custom RawRowMapper that can map fields from a user request to a user model. This is useful if you have a query that has fields that do not match any table mapping model.

This is RowMapper , which performs automatic query matching:

 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 use :

 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 a List<Model> with the result rows displayed in Model if the query column names and @DatabaseField(columnName are the same