CINXE.COM

<!doctype html> <html lang="en"> <head> <title>ORM query | Intro | Ebean</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <link rel="shortcut icon" href="/images/favicon.ico"> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Source+Sans+Pro|Ubuntu&display=swap"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.1.0/css/all.css" integrity="sha384-lKuwvrZot6UHsBSfcMvOkWwlCMgc0TaWr+30HWe3a4ltaBwTZhyTEggF5tJv8tbt" crossorigin="anonymous"> <link rel="stylesheet" href="/css/reset3.css"> <link rel="stylesheet" href="/css/site3.css"> <link rel="stylesheet" href="/css/pygments3.css"> </head> <body> <div id="main"> <div id="banner"> <header> <nav id="top"> <h1 id="breadcrumb"> <a class="nav-logo" href="/"><img src="/images/logo-200.png" height="35"></a>  <a href="/docs">Documentation</a> / <a href="/docs/intro/">Introduction</a> / <a href="/docs/intro/queries">Queries</a> / ORM query </h1> <ul> <li><a onclick="toggleTheme();" title="switch dark light theme"></a></li> </ul> </nav> </header> </div> <div class="grid grid-docs"> <aside> <nav class="side"> <ul> <li class="nav0 "> <a href="/docs/getting-started">Getting started</a> </li> <li class="nav0 active"> <a class="active" href="/docs/intro">Introduction</a> <ul> <li class="nav1 "> <a href="/docs/intro/database">Database & DB</a> </li> <li class="nav1 "> <a href="/docs/intro/configuration">Configuration</a> </li> <li class="nav1 "> <a href="/docs/intro/first-entity">First entity</a> </li> <li class="nav1 active"> <a class="active" href="/docs/intro/queries">Queries</a> <ul> <li class="active"> <a class="active" href="/docs/intro/queries/orm-query">ORM query</a> </li> <li > <a href="/docs/intro/queries/dto-query">DTO query</a> </li> <li > <a href="/docs/intro/queries/sql-query">SQL query</a> </li> <li > <a href="/docs/intro/queries/jdbc-query">JDBC query</a> </li> </ul> </li> <li class="nav1 "> <a href="/docs/intro/logging">Logging</a> </li> <li class="nav1 "> <a href="/docs/intro/db-migrations">DB Migrations</a> </li> <li class="nav1 "> <a href="/docs/intro/limitations">Limitations</a> </li> <li class="nav1 "> <a href="/docs/trouble-shooting">Trouble shooting</a> </li> </ul> </li> <li class="nav0 "> <a href="/docs">Documentation</a> </li> <li class="nav0 "> <a href="/support">Getting help</a> </li> <li class="nav0 "> <a target="_blank" href="/apidoc/13">API Javadoc</a> </li> <li class="nav0 "> <a href="/videos">Videos</a> </li> <li class="nav0 "> <a href="/docs/upgrading">Upgrading</a> </li> <li class="nav0 "> <a href="/releases">Releases</a> </li> </ul> </nav> </aside> <article> <form action="https://www.google.com/search" method="get" class="inline-form"> <input type="hidden" name="as_sitesearch" value="ebean.io"> <div id="page-search"> <div class="input-group"> <input class="frm" name="q" id="searchinput" type="text" placeholder="Search... (press 's' to focus)" data-placeholder-focus="Search... (use '↑', '↓' and '⏎' to select results)" data-placeholder-blur="Search... (press 's' to focus)" autocomplete="off"> <div class="input-group-btn"> <button class="frm" type="submit"></button> </div> </div> <div id="page-search-results" style="display: none;"> <ul id="search-results-container" class="search-results"><li class=" active"><a href="/docs" title="Docs">Docs Documentation </a></li><li class="">And 101 more...</li></ul> </div> </div> </form> <h2>ORM Query</h2> There are several forms of ORM query. For detailed information on queries goto <a href="/docs/query">docs / query</a> <h3 id="pure">"Pure" ORM Query</h3> Below are examples of pure ORM queries. We specify no SQL functions explicitly and all the SQL is generated. In typical recent applications around <code>85%</code> of queries were "pure" ORM queries. <ul class="code nav nav-tabs mytabs"> <li role="presentation" class="javaActive active"><a onclick="setLang('java');">java</a></li> <li role="presentation" class="kotlinActive"><a onclick="setLang('kt');">kotlin</a></li> </ul> <div class="code-java"> <div class="syntax java"><div class="highlight"><pre>Customer rob = new QCustomer() .name.equalTo("Rob") .findOne(); List<Customer> customers = new QCustomer() .status.equalTo(Status.NEW) .billingAddress.city.equalTo("Auckland") .findList(); </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>val rob = QCustomer() .name.equalTo("Rob") .findOne() val customers = QCustomer() .status.equalTo(Status.NEW) .billingAddress.city.equalTo("Auckland") .findList() </pre></div> </div> </div> <h3 id="sql-select">ORM Query with SQL in Select</h3> We can use SQL in the select clause. Some examples of this type of query are: <ul class="code nav nav-tabs mytabs"> <li role="presentation" class="javaActive active"><a onclick="setLang('java');">java</a></li> <li role="presentation" class="kotlinActive"><a onclick="setLang('kt');">kotlin</a></li> </ul> <div class="code-java"> <div class="syntax java"><div class="highlight"><pre>// using sql functions in the select clause List<String> names = new QContact() .select("concat(lastName,', ',firstName)") .lastName.startsWith("A") .findSingleAttributeList(); BigDecimal routeDistance = new QTrip() .select("ST_Distance(ST_StartPoint(route), ST_EndPoint(route))::BigDecimal") .id.equalTo(tripId) .findSingleAttribute(); </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>// using sql functions in the select clause var names: List<String> = QContact() .select("concat(lastName,' ',firstName)") .lastName.startsWith("A") .findSingleAttributeList() val routeDistance: BigDecimal = QTrip() .select("ST_Distance(ST_StartPoint(route), ST_EndPoint(route))::BigDecimal") .id.equalTo(tripId) .findSingleAttribute() </pre></div> </div> </div> <h3 id="sql-where">ORM Query with SQL in Where</h3> We can also use SQL in the where clause. Frequently this is for specifying sql sub-queries. <ul class="code nav nav-tabs mytabs"> <li role="presentation" class="javaActive active"><a onclick="setLang('java');">java</a></li> <li role="presentation" class="kotlinActive"><a onclick="setLang('kt');">kotlin</a></li> </ul> <div class="code-java"> <div class="syntax java"><div class="highlight"><pre>// sql functions used in predicates List<Order> orders = new QOrder() .raw("add_days(orderDate, 10) < ?", someDate) .findList(); // SQL sub-query - often easiest to specify the subquery in sql form String subQuery = "t0.customer_id in " + "(select customer_id from customer_group where group_id = any(?::uuid[]))"; List<Order> orders = new QOrder() .status.equalTo(Status.NEW) .raw(subQuery, groupIds) // use raw SQL in where clause .findList(); </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>// sql functions used in predicates var orders = QOrder() .raw("add_days(orderDate, 10) < ?", someDate) .findList() // SQL sub-query - often easiest to specify the subquery in sql form val subQuery = "t0.customer_id in " + "(select customer_id from customer_group where group_id = any(?::uuid[]))" var orders = QOrder() .status.equalTo(Status.NEW) .raw(subQuery, groupIds) // use raw SQL in where clause .findList() </pre></div> </div> </div> In typical recent applications around <code>5%</code> of queries were ORM queries with some SQL (mostly subqueries). <h3 id="dto">ORM to DTO Query</h3> We can define an ORM query and then use <code>asDto</code> to turn it into a DTO query. We are using the ORM to generate the SQL but then want that mapped directly into a DTO bean. <ul class="code nav nav-tabs mytabs"> <li role="presentation" class="javaActive active"><a onclick="setLang('java');">java</a></li> <li role="presentation" class="kotlinActive"><a onclick="setLang('kt');">kotlin</a></li> </ul> <div class="code-java"> <div class="syntax java"><div class="highlight"><pre>// ContactDto is a plain bean with email and fullName properties List<ContactDto> contacts = new QContact() .select("email, concat(lastName, ', ', firstName) as fullName") .lastName.startsWith("A") .orderBy() .lastName.asc() .setMaxRows(10) .asDto(ContactDto.class) .findList(); </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>// ContactDto is a plain bean with email and fullName properties val contacts = new QContact() .select("email, concat(lastName, ', ', firstName) as fullName") .lastName.startsWith("A") .orderBy() .lastName.asc() .setMaxRows(10) .asDto(ContactDto::class.java) .findList() </pre></div> </div> </div> <h3 id="native">SQL - aka find native</h3> We can also specify the query in SQL and have that automatically mapped to entity beans (like this) or <a href="dto-query">DTO beans</a>. <ul class="code nav nav-tabs mytabs"> <li role="presentation" class="javaActive active"><a onclick="setLang('java');">java</a></li> <li role="presentation" class="kotlinActive"><a onclick="setLang('kt');">kotlin</a></li> </ul> <div class="code-java"> <div class="syntax java"><div class="highlight"><pre>String sql = "select id, name from customer where name like ?"; Customer customer = DB.findNative(Customer.class, sql) .setParameter(1, "Jo%") .findOne(); </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>String sql = "select id, name from customer where name like ?"; val customer = DB.findNative(Customer::class.java, sql) .setParameter(1, "Jo%") .findOne() </pre></div> </div> </div> <nav class="next"> <a href="https://github.com/ebean-orm/website-source/blob/master/docs/intro/queries/orm-query.html"> Edit Page</a> <a href="dto-query" class="btn btn-info">Next: DTO Query</a> </nav> </article> </div> </div> <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script> <script src="/js/site3.js"></script> <script src="/js/search3.js"></script> <script async src="https://www.googletagmanager.com/gtag/js?id=UA-75181644-1"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-75181644-1'); </script> </body> </html>