Raw SQL with ActiveJDBC

Question

it seems that using ActiveJDBC is very limiting

I have three tables a, b and c they all have text column I want the union of of all these table

I tried

LazyList<A> list = A.findBySQL("(select name as text from a) union (select version as text from b) union (select id as text from c)

and I tried

LazyList<A> list = A.findBySQL("select a.* from ((select name as text from a) union (select version as text from b) union (select id as text from c)) as a");

neither worked. Is there any other way to get the union of those three tables?

Answer 1

Please, remember that ActiveJDBC is an ORM, and all attributes returned by a query must match table schema for each model. Please, see here: Model#findBySQL()

Specifically, the JavaDoc states: "Ensure that the query returns all columns associated with this model, so that the resulting models could hydrate itself properly"

This means that you cannot select willy-nilly anything you want, but the query must select only columns related to table it fronts. If you need to use just any raw SQL and return whatever you need, you may need to use a class Base or DB. Please see this JavaDoc for more information:

Base#findAll() and Base#find().

Read the docs to determine which method works best for you.

Answer 2

I have the same problem here. My solution is:

String query = "select a.* from ((select name as text from a) union (select version as text from b) union (select id as text from c)) as a"; List<Map> result = new DB("default").all(query);