CINXE.COM

<!doctype html> <html lang="en"> <head> <title>Mapping | 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><span class="sep"> / </span><a href="/docs/mapping/">Mapping</a><span class="sep"> / </span><a href="/docs/mapping/extension">Extension</a><span class="sep"> / </span><span class="last">@DbArray</span> </h1> <ul> <li><a onclick="toggleTheme();" title="switch dark light theme"><i class="fas fa-adjust"></i></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 "> <a href="/docs/intro">Introduction</a> </li> <li class="nav0 active"> <a class="active" href="/docs">Documentation</a> <ul> <li class="nav1 "> <a href="/docs/best-practice">Best practice</a> </li> <li class="nav1 "> <a href="/docs/query">Query</a> </li> <li class="nav1 "> <a href="/docs/persist">Persist</a> </li> <li class="nav1 "> <a href="/docs/transactions">Transactions</a> </li> <li class="nav1 active"> <a class="active" href="/docs/mapping">Mapping</a> <ul class="nav"> <li class="nav1 "> <a href="/docs/mapping/type">Types</a> </li> <li class="nav1 "> <a href="/docs/mapping/jpa">JPA</a> </li> <li class="nav1 active"> <a class="active" href="/docs/mapping/extensions">Extensions</a> <ul class="nav"> <li > <a href="/docs/mapping/extensions/identity">@Identity</a> </li> <li > <a href="/docs/mapping/extensions/dbindex">@Index</a> </li> <li > <a href="/docs/mapping/extensions/dbenumvalue">@DbEnumValue</a> </li> <li > <a href="/docs/mapping/extensions/dbjson">@DbJson</a> </li> <li class="active"> <a class="active" href="/docs/mapping/extensions/dbarray">@DbArray</a> </li> <li > <a href="/docs/mapping/extensions/dbmap">@DbMap</a> </li> <li > <a href="/docs/mapping/extensions/dbforeignkey">@DbForeignKey</a> </li> <li > <a href="/docs/mapping/extensions/dbcomment">@DbComment</a> </li> <li > <a href="/docs/mapping/extensions/dbpartition">@DbPartition</a> </li> <li > <a href="/docs/mapping/extensions/when-created">@WhenCreated</a> </li> <li > <a href="/docs/mapping/extensions/when-modified">@WhenModified</a> </li> <li > <a href="/docs/mapping/extensions/who-created">@WhoCreated</a> </li> <li > <a href="/docs/mapping/extensions/who-modified">@WhoModified</a> </li> <li > <a href="/docs/mapping/extensions/soft-delete">@SoftDelete</a> </li> <li > <a href="/docs/mapping/extensions/encrypted">@Encrypted</a> </li> <li > <a href="/docs/mapping/extensions/formula">@Formula</a> </li> <li > <a href="/docs/mapping/extensions/view">@View</a> </li> <li > <a href="/docs/mapping/extensions/history">@History</a> </li> <li > <a href="/docs/mapping/extensions/change-log">@ChangeLog</a> </li> <li > <a href="/docs/mapping/extensions/read-audit">@ReadAudit</a> </li> </ul> </li> </ul> </li> <li class="nav1 "> <a href="/docs/ddl-generation">DDL & Migrations</a> </li> <li class="nav1 "> <a href="/docs/logging">Logging</a> </li> <li class="nav1 "> <a href="/docs/testing">Testing</a> </li> <li class="nav1 "> <a href="/docs/read-replicas">Read Replicas</a> </li> <li class="nav1 "> <a href="/docs/database">Database platforms</a> </li> <li class="nav1 "> <a href="/docs/multi-database">Multiple databases</a> </li> <li class="nav1 "> <a href="/docs/kotlin">Kotlin</a> </li> <li><a href="/docs/tuning">Tuning</a></li> <li class="nav1 "> <a href="/docs/features">Features</a> </li> </ul> </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"><i class="fas fa-search"></i></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"><span style="color:#777;">Docs</span> Documentation </a></li><li class=""><small style="color:#999;">And 101 more...</small></li></ul> </div> </div> </form> <h2 id="postgres">Postgres</h2> <p> The primary target database for <code>@DbArray</code> is Postgres as single dimension array. In time, we could also map this to Oracle nested tables but at this stage that will be done on demand (you can ask and sponsor the development effort etc). </p> <h2 id="fallback">Fallback</h2> <p> For non-Postgres the fallback is to simply store the data in JSON form in VARCHAR (ala @DbJson). </p> <h2>Example: uuid[]</h2> <div class="syntax java"><div class="highlight"><pre><span></span><span class="nd">@DbArray</span> <span class="n">List</span><span class="o"><</span><span class="n">UUID</span><span class="o">></span> <span class="n">uids</span> <span class="o">=</span> <span class="k">new</span> <span class="n">ArrayList</span><span class="o"><</span><span class="n">UUID</span><span class="o">>();</span> </pre></div> </div> <p> The example above will map to a database column type <code>uuid[]</code>. </p> <h2>Example: integer[]</h2> <div class="syntax java"><div class="highlight"><pre><span></span><span class="nd">@DbArray</span> <span class="n">List</span><span class="o"><</span><span class="n">Long</span><span class="o">></span> <span class="n">someIds</span> <span class="o">=</span> <span class="k">new</span> <span class="n">ArrayList</span><span class="o"><</span><span class="n">Long</span><span class="o">>();</span> </pre></div> </div> <p> The example above will map to a database column type <code>integer[]</code>. </p> <h2>Example: varchar[]</h2> <div class="syntax java"><div class="highlight"><pre><span></span><span class="nd">@DbArray</span> <span class="n">List</span><span class="o"><</span><span class="n">String</span><span class="o">></span> <span class="n">phoneNumbers</span> <span class="o">=</span> <span class="k">new</span> <span class="n">ArrayList</span><span class="o"><</span><span class="n">String</span><span class="o">>();</span> </pre></div> </div> <p> The example above will map to a database column type <code>varchar[]</code>. </p> <h2>Fallback varchar length</h2> <p> The <code>length</code> attribute is used with non-Postgres databases and defines the size of the varchar column used in the fallback case. </p> <div class="syntax java"><div class="highlight"><pre><span></span><span class="c1">// fallback to varchar(300) for non-Postgres databases</span> <span class="nd">@DbArray</span><span class="o">(</span><span class="nx">length</span> <span class="o">=</span> <span class="mi">300</span><span class="o">)</span> <span class="n">List</span><span class="o"><</span><span class="n">String</span><span class="o">></span> <span class="n">phNums</span> <span class="o">=</span> <span class="k">new</span> <span class="n">ArrayList</span><span class="o"><</span><span class="n">String</span><span class="o">>();</span> </pre></div> </div> <h2>Expressions</h2> <p> Ebean "Query Beans" and ExpressionList have 4 expressions added to handle the common ARRAY expressions we want to execute. </p> <p> The following examples use a <code>phNums</code> property that maps to array of varchar. </p> <h4>contains()</h4> <div class="syntax java"><div class="highlight"><pre><span></span><span class="n">List</span><span class="o"><</span><span class="n">Contact</span><span class="o">></span> <span class="n">contacts</span> <span class="o">=</span> <span class="n">Contact</span><span class="o">.</span><span class="na">find</span> <span class="o">.</span><span class="na">where</span><span class="o">()</span> <span class="o">.</span><span class="na field">phNums</span><span class="o">.</span><span class="na">contains</span><span class="o">(</span><span class="s">"2314"</span><span class="o">)</span> <span class="o">.</span><span class="na">findList</span><span class="o">();</span> </pre></div> </div> <p> contains() uses the <code>@></code> contains operator and results in the following predicate: </p> <div class="syntax sql"><div class="highlight"><pre><span></span><span class="k">where</span> <span class="n">t0</span><span class="p">.</span><span class="n">ph_nums</span> <span class="o">@></span> <span class="nb">array</span><span class="p">[</span><span class="o">?</span><span class="p">];</span> <span class="c1">--bind(2314)</span> </pre></div> </div> <h4>notContains()</h4> <div class="syntax java"><div class="highlight"><pre><span></span><span class="n">List</span><span class="o"><</span><span class="n">Contact</span><span class="o">></span> <span class="n">contacts</span> <span class="o">=</span> <span class="n">Contact</span><span class="o">.</span><span class="na">find</span> <span class="o">.</span><span class="na">where</span><span class="o">()</span> <span class="o">.</span><span class="na field">phNums</span><span class="o">.</span><span class="na">notContains</span><span class="o">(</span><span class="s">"2314"</span><span class="o">)</span> <span class="o">.</span><span class="na">findList</span><span class="o">();</span> </pre></div> </div> <p> notContains() uses the <code>@></code> contains operator and results in the following predicate: </p> <div class="syntax sql"><div class="highlight"><pre><span></span><span class="k">where</span> <span class="k">not</span> <span class="p">(</span><span class="n">t0</span><span class="p">.</span><span class="n">ph_nums</span> <span class="o">@></span> <span class="nb">array</span><span class="p">[</span><span class="o">?</span><span class="p">]);</span> <span class="c1">--bind(2314)</span> </pre></div> </div> <h4>isEmpty()</h4> <div class="syntax java"><div class="highlight"><pre><span></span><span class="n">List</span><span class="o"><</span><span class="n">Contact</span><span class="o">></span> <span class="n">contacts</span> <span class="o">=</span> <span class="n">Contact</span><span class="o">.</span><span class="na">find</span> <span class="o">.</span><span class="na">where</span><span class="o">()</span> <span class="o">.</span><span class="na field">phNums</span><span class="o">.</span><span class="na">isEmpty</span><span class="o">()</span> <span class="o">.</span><span class="na">findList</span><span class="o">();</span> </pre></div> </div> <p> isEmpty() uses the cardinality function and results in the following predicate: </p> <div class="syntax sql"><div class="highlight"><pre><span></span><span class="k">where</span> <span class="n">coalesce</span><span class="p">(</span><span class="k">cardinality</span><span class="p">(</span><span class="n">t0</span><span class="p">.</span><span class="n">ph_nums</span><span class="p">),</span><span class="mi">0</span><span class="p">)</span> <span class="o">=</span> <span class="mi">0</span> </pre></div> </div> <h4>isNotEmpty()</h4> <div class="syntax java"><div class="highlight"><pre><span></span><span class="n">List</span><span class="o"><</span><span class="n">Contact</span><span class="o">></span> <span class="n">contacts</span> <span class="o">=</span> <span class="n">Contact</span><span class="o">.</span><span class="na">find</span> <span class="o">.</span><span class="na">where</span><span class="o">()</span> <span class="o">.</span><span class="na field">phNums</span><span class="o">.</span><span class="na">isNotEmpty</span><span class="o">()</span> <span class="o">.</span><span class="na">findList</span><span class="o">();</span> </pre></div> </div> <p> isNotEmpty() uses the cardinality function and results in the following predicate: </p> <div class="syntax sql"><div class="highlight"><pre><span></span><span class="k">where</span> <span class="n">coalesce</span><span class="p">(</span><span class="k">cardinality</span><span class="p">(</span><span class="n">t0</span><span class="p">.</span><span class="n">ph_nums</span><span class="p">),</span><span class="mi">0</span><span class="p">)</span> <span class="o"><></span> <span class="mi">0</span> </pre></div> </div> </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>