Native SQL / findNative | Query

Native SQL - findNative

With findNative we supply the SQL. The SQL can contain positioned parameters with ? or named parameters like :foo

Example - positioned parameters

String sql = "select id, name from customer where name like ?";

Customer customer = DB.findNative(Customer.class, sql)
    .setParameter("Jo%")
    .findOne();

Example - 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 findNative the columns are automatically mapped to bean properties.

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 but this is not generally recommended.

Example - select *

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();

Column mapping

The way column mapping 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 using the naming convention.

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.

For columns that do not conform to the naming convention we use sql column alias.

Example - column alias

// using sql column alias
String sql = "select id, fname as first_name, lname as last_name " +
             "from contact where lname like ?";

List<Contact> contacts = DB.findNative(Contact.class, sql)
  .setParameter(1, "B%")
  .findList();

Unmapped columns

Sometimes we want to specify columns in the resultSet for the order by clause that can not be mapped to a bean property. In this case these properties are ignored.

Example - an unmapped column for order by

String sql = "select id, name, " +
  "case when anniversary >= ? then 1 when anniversary < ? then 2 end as order_column_1 " +
  "from o_customer " +
  "order by order_column_1";

List<Customer> result = DB.findNative(Customer.class, sql)
  .setParameter(LocalDate.now())
  .setParameter(LocalDate.now())
  .findList();

firstRow / maxRows

We can specify firstRow and maxRows and the SQL will be modified appropriately using limit/offset or equivalent clause.

Example - using maxRows

String sql = "select id, name from o_customer order by name, id";

List<Customer> result = DB.findNative(Customer.class, sql)
  .setMaxRows(50)
  .findList();

fetchQuery

When using findNative we can think of it as effectively supplying the SQL for "root" or "origin" query and can use fetchQuery to additionally fetch other parts of the graph.

In the example below we are using findNative with supplied SQL to fetch Customers and additionally using fetchQuery to fetch the related orders and contacts for those customers.

Example - using fetchQuery

List<Customer> result = DB.findNative(Customer.class, sql)
  .fetchQuery("orders")
  .fetchQuery("contacts")
  .findList();

Multiple tables

Excluding Oracle (see oracle limitation below) our SQL can select from multiple tables and these can automatically be mapped into related entity beans.

For example, we can fetch and populate 2 related beans like Contact and Customer. The contact entity beans returned will include customer beans with the customer id and name populated.

Example - contacts and their associated customer

// 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 = DB.findNative(Contact.class, sql)
    .findList();

Example - order lines and their associated product

String sql = "select line.*, p.* " +
  " from order_line line " +
  " join product p on p.id = line.product_id " +
  " where line.order_id = ?";

List<OrderLine> lines = DB.findNative(OrderLine.class, sql)
  .setParameter(order.getId())
  .findList();

Limitations

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 same address table. 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.

Oracle limitation

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).

RawSql

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.