Native SQL ( findNative )
findNative we supply the SQL. The SQL can contain positioned parameters with
or named parameters like
With findNative the columns are automatically mapped to bean properties.
String sql = "select id, name from customer where name like ?"; Customer customer = DB.findNative(Customer.class, sql) .setParameter(1, "Jo%") .findOne();
An example using named parameters.
String sql = "select id, name from customer where name like :name order by name desc"; List<Customer> customers = DB.findNative(Customer.class, sql) .setParameter("name", "Jo%") .findList();
With the above examples we are selecting some columns and as a result getting partially populated entity beans (which is good - we should only fetch things out of the DB that we need).
We can use
select * if we desire all the columns.
String sql = "select * from customer where name like :name order by name desc"; List<Customer> customers = DB.findNative(Customer.class, sql) .setParameter("name", "Jo%") .findList();
The way this works is that when a query is first run the JDBC meta data is read for the columns in the resultSet and then these columns are automatically mapped to bean properties.
This only needs to be done for the first execution of the query. How the query is mapped to beans is cached so we only need to read the JDBC resultSet meta data once per query.
The limitation with using findNative (excluding Oracle) is that it can only map multiple tables when the path for a given table is unique.
For example, lets say we have Customer that maps both a billingAddress and shippingAddress to the Address. When ebean tries to map a column for the address table it does not know which path (billingAddress or shippingAddress) the column maps to.
When we hit this limitation we need to use RawSql instead where we can map the columns more explicitly to bean paths/properties.
Excluding Oracle (see oracle limitation below) our SQL can select from multiple tables and these can automatically be mapped.
For example, we can fetch and populate 2 related beans like Customer and Contact.
// Contacts + Customer String sql = "select con.id, con.first_name, con.last_name, cust.id, cust.name " + " from contact con " + " join customer cust on cust.id = con.customer_id " + " order by con.first_name desc"; List<Contact> contacts = nativeSql(sql) .findList();
There is a limitation with the Oracle JDBC driver in that the meta data currently does not include the table that a column relates to. This means that with Oracle we can only map a single table and not multiple tables (and would have to use RawSql instead).
Using findNative is preferred over RawSql as the way to execute
SQL queries with
entity beans because it is nice and simple as the columns are mapped to properties
automatically for us.
However, findNative has some limitations when we want the select clause to include columns from multiple tables (limitations described above). When we hit those limitations we then need to use RawSql with more explicit mapping of columns to properties.