CINXE.COM

<!doctype html> <html lang="en"> <head> <title>SQL 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> / SQL 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 > <a href="/docs/intro/queries/orm-query">ORM query</a> </li> <li > <a href="/docs/intro/queries/dto-query">DTO query</a> </li> <li class="active"> <a class="active" 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>SQL Query</h2> Sometimes it is useful to not use entity beans or dto beans but instead execute sql queries producing <code>SqlRow</code> objects or reading directly from JDBC ResultSet. In typical recent applications around <code>2%</code> of queries were SqlQuery type queries. <h3>SqlQuery and SqlRow</h3> Note that with SqlQuery we can get Ebean to add <code>limit/offset</code> clause appropriate to the database platform and we can bind parameters using all the mapped types (Enums, java.time, array types etc). For more information goto <a href="/docs/query/sqlquery">docs / query / sqlquery</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, when_active from customer where status = ?"; List<SqlRow> rows = DB.sqlQuery(sql) .setParameter(1, "NEW") .setMaxRows(100) .findList(); for (SqlRow sqlRow : rows) { Long id = row.getLong("id") String name = row.getString("name") Timestamp whenActivated = row.getTimestamp("when_activated") } </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>val sql = """ select id, name, when_activated from customer where name like ? """.trimIndent() val rows = DB.sqlQuery(sql) .setParameter(1, "Rob%") .setMaxRows(100) .findList() for (row in rows) { val id = row.getLong("id") val name = row.getString("name") val whenActivated = row.getTimestamp("when_activated") } </pre></div> </div> </div> <h3>SqlQuery and RowMapper</h3> Alternatively we can use a RowMapper to read from the JDBC ResultSet. <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, status from customer order by name desc"; DB.sqlQuery(sql) .findEachRow((resultSet, rowNum) -> { // read directly from ResultSet long id = resultSet.getLong(1); String name = resultSet.getString(2); // do something interesting with the data }); </pre></div> </div> </div> <div class="code-kt"> <div class="syntax kotlin"><div class="highlight"><pre>val sql = "select id, name, status from customer order by name desc" DB.sqlQuery(sql) .findEachRow { resultSet, rowNum -> // read directly from ResultSet val id = resultSet.getLong(1) val name = resultSet.getString(2) // do something interesting with the data } </pre></div> </div> </div> <nav class="next"> <a href="https://github.com/ebean-orm/website-source/blob/master/docs/intro/queries/sql-query.html"> Edit Page</a> <a href="/docs/intro/queries/jdbc-query" class="btn btn-info">Next: JDBC 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>