ebean-orm.github.io/docs/query/features.html at master · ebean-orm/ebean-orm.github.io

History

1670 lines (1385 loc) · 116 KB

Raw

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

<!DOCTYPE html>

<head>

<title>Features | Query | Ebean</title>

</head>

<body>

Toggle navigation

</button>

</a>

</div>

<li ><a href="/docs/getting-started">Get Started</a></li>

<li ><a href="/videos">Videos</a></li>

<li ><a target="_blank" href="/apidoc/13">ApiDocs</a></li>

<li ><a href="/releases">Releases</a></li>

</ul>

</div>

<li ><a href="/docs/getting-started">Get Started</a></li>

<li ><a href="/videos">Videos</a></li>

<li ><a target="_blank" href="/apidoc/13">ApiDocs</a></li>

<li ><a href="/releases">Releases</a></li>

</ul>

</div>

<h1><a href="/docs/">Documentation</a> / <a href="/docs/query/">Query</a> / Features</h1>

</div>

</div>

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

</form>

<div class="alert alert-danger">Redirecting as content is being updated. Please hold on tight ...</div>

<h1 id="queries">Query Features</h1>

<div class="syntax java"><div class="highlight"><pre>// find by id

Order order = DB.find(Order.class, 12);

</pre></div>

</div>

executes the sql:

<div class="syntax sql"><div class="highlight"><pre>select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id

from or_order o

where or.id = ?

</pre></div>

</div>

<div class="syntax java"><div class="highlight"><pre>// these are the same

Query<Order> query = DB.createQuery(Order.class);

Query<Order> query = DB.find(Order.class);

</pre></div>

</div>

These two methods do exactly the same thing. The reason both

exist is because the createQuery() style is consistent with JPA and could be argued is a

better more accurate name. And find() is more consistent with the fluid API style.

Note: In Ebean 16.x, <code>DB.createQuery(Class, eqlString)</code> was supported for EQL queries.

This has been removed in Ebean 17.x. See <a href="/docs/query/eql">EQL documentation</a> and

<a href="/docs/upgrading">upgrading guide</a> for alternatives.

</div>

<div class="syntax java"><div class="highlight"><pre>// fluid API style with find()

List<Order> list =

DB.find(Order.class)

.fetch("customer")

.where().eq("status.code", "SHIPPED")

.findList();

</pre></div>

</div>

<h2 id="where_clause">Where clause</h2>

You can specify 'predicates' for the where clause.

<div class="syntax java"><div class="highlight"><pre>// find all the orders shipped since a week ago

.where()

.eq("status", Order.Status.SHIPPED)

.gt("shipDate", lastWeek)

.findList();

</pre></div>

</div>

<h4>Example Predicates</h4>

<ul>

<li>eq(...) = equals</li>

<li>ne(...) = not equals

<li>

<li>ieq(...) = case insensitve equals</li>

<li>between(...) = between</li>

<li>gt(...) = greater than</li>

<li>ge(...) = greater than or equals</li>

<li>lt(...) = less than or equals</li>

<li>le(...) = less than or equals</li>

<li>isNull(...) = is null</li>

<li>isNotNull(...) = is not null</li>

<li>startsWith(...) = string starts with</li>

<li>endswith(...) = string ends with</li>

<li>contains(...) = string conains</li>

<li>in(...) = in a subquery, collection or array</li>

<li>exists(...) = at least one row exists in a subquery</li>

<li>notExists(...) = no row exists in a subquery</li>

</ul>

Use code completion in your favorite ide or see <a href="/apidoc/13/io.ebean.api/io/ebean/ExpressionList.html">ExpressionList</a>

class for more details.

Ebean will automatically add SQL joins if they are required for the where clause or order by clause (and the

matching joins are not explicitly included).

<div class="syntax java"><div class="highlight"><pre>List<Order> orders =

.where().ilike("customer.name", "rob%")

.findList();

</pre></div>

</div>

... in the sql below, the join to or_customer is automatically added to support the where clause.

select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id

from or_order o

join or_customer c on o.customer_id = c.id

where lower(c.name) like ?

</sql>

</pre></div>

</div>

<h2 id="query_language">Query language</h2>

Ebean has it's own query language. Prior to this decision JPQL (the JPA query language)

was investigated to see if it would meet the desired goals of Ebean and it did not.

Specifically the desire for Ebean is to support "Partial Objects" via the query language and it is

difficult to

see how JPQL will evolve to support this (specifically difficulties around its select clause).

Apart from "Partial Object" support there was also a desire to simplify the join syntax,

specifically Ebean will automatically determine the type of join (outer join etc) for you and

also automatically add joins to support predicates and order by clauses.

JPQL is more powerful with the ability to mix entity beans with scalar values returning

Object[]. However, this feature also could be a major stumbling block for it to evolve

support for partial objects for any node in the object graph.

In summary you could say the Ebean query language is much simplier that JPQL with the

benefit of proper support for "Partial Objects" for any node in the object graph (this is not

possible with JPQL in it's current form).

"Partial Object" support in Ebean is important for design reasons and performance

reasons. From a performance perspective your queries are more performant if they fetch

less data back from the database. From a design perspective you do not need to model

using secondary tables but instead use partial objects at any depth in the query.

For example, to build an object graph for an Order you may want some product

information for each orderDetail.

Examples

<div class="syntax groovy"><div class="highlight"><pre>// find all the orders fetching all the properties of order

find order

// find all the orders fetching all the properties of order

// ... this is the same as the first query

find order (*)

// find all the orders fetching the id, orderDate and shipDate

// ... This is described as a "partial object query"

// ... the ID property is *ALWAYS* fetched

find order (orderDate, shipDate)

// find all the orders (and orderDetails)

// ... fetching all the properties of order

// ... and all the properties of orderDetails

// ... the type of fetch(Outer etc) is determined automatically

find order

fetch orderDetails

// find all the orders (with their orderDetails)

// ... fetching all the properties of order

// ... and all the properties of orderDetails

find order (*)

fetch orderDetails (*)

// find all the orders (with orderDetails and products)

// ... fetching the order id, orderDate and shipDate

// ... fetching all the properties for orderDetail

// ... fetching the product id, sku and name

find order (orderDate, shipDate)

fetch orderDetails (*)

fetch orderDetails.product (sku, name)

</pre></div>

</div>

<div class="syntax java"><div class="highlight"><pre>String query = "find order where status.code=:status and shipDate > :shipped";

.setQuery(query)

.setParameter("status", Order.Status.SHIPPED)

.setParameter("shipped", lastWeek)

.findList();

</pre></div>

</div>

select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id

from or_order o

where o.status_code = ? and o.ship_date > ?

</sql>

</pre></div>

</div>

Every object in the object graph can be a partial object. This is what you can't do in JPQL

currently.

These Partially populated objects are will lazy load as required and are fully updatable etc.

You can treat them just like fully populated objects.

Autofetch can use partial objects to only fetch the properties that the application actually

uses. In this way you can get the performance of partial objects without any work on your

part (Autofetch determines the joins and properties to fetch for you).

<h2 id="partial_objects">Partial objects</h2>

The Query object has select() and fetch() methods and these allow you to specify

the properties that should be fetched.

This can be a very significant performance benefit by only fetching the properties you need. If

the properties are in DB indexes then the DB doesn't have to read Index Blocks. This is also a

design benefit in that it removes the "fixed" design constraints of using secondary table

properties.

Note that <a href="#autofetch">Autofetch</a> can use profiling to specify the properties to

select rather than you manually doing so (which saves you the work).

<h4>Select</h4>

With select you specify the properties that should be fetched on the root level type.

Partially loading a bean or object graph using select() and fetch().

<div class="syntax java"><div class="highlight"><pre>Customer customer = Customer.find

.select("name")

.where().idEq(1L)

.findOne();

</pre></div>

</div>

from customer t0

where t0.id = ? ; --bind(1)

</pre></div>

</div>

<h4>Fetch</h4>

With fetch you specify the properties that should be fetched on the associated beans.

*ANY* node of the object graph can be a partial object. This example shows several ways partial

objects can be fetched.

<div class="syntax java"><div class="highlight"><pre>Order order = Order.find

.select("status, orderDate, shipDate") // 3 fields from order

.fetch("details") // all fields from details

.fetch("details.product", "sku") // just sku field from product

.where().idEq(1L)

.findOne();

</pre></div>

</div>

<div class="syntax sql"><div class="highlight"><pre>select t0.id c0, t0.status c1, t0.order_date c2, t0.ship_date c3,

t1.id c4, t1.name c5, -- customer name

t2.id c6, t2.order_qty c7, t2.ship_qty c8, t2.unit_price c9, t2.version c10,

t2.when_created c11, t2.when_updated c12, t2.order_id c13,

t3.id c14, t3.sku c15 -- product sku

from orders t0

join customer t1 on t1.id = t0.customer_id

left outer join order_detail t2 on t2.order_id = t0.id

left outer join product t3 on t3.id = t2.product_id

where t0.id = ?

order by t0.id, t2.id asc; --bind(1)

</pre></div>

</div>

<h4>Saving a partial object</h4>

You can save or delete a Partial Object.

<div class="syntax java"><div class="highlight"><pre>// find customer 1

// ... just fetch the customer id, name and version property

Customer customer = DB.find(Customer.class)

.select("name")

.where().idEq(1)

.findOne();

customer.setName("CoolName");

DB.save(customer);

</pre></div>

</div>

The query generates the following SQL...

select c.id, c.name

from or_customer c

where c.id = ?

</sql>

</pre></div>

</div>

and in the transaction logs we can find the update dml and bind values.

<div class="syntax xml"><div class="highlight"><pre>... update or_customer set name=?, updtime=? where id=? and name=?

... Binding Update [or_customer] set[name=CoolName, updtime=2008-11-19 10:58:08.598, ] where[id=1,

name=Ford, ]

... Updated [app.data.test.Customer] [1]

</pre></div>

</div>

<h2 id="query_joins">Query joins</h2>

<h4>

Controlling eager loading of the object graph using query joins.

</h4>

You can use fetch() to explicitly state which additional paths you want to fetch. You would do

this to reduce "lazy loading" of those beans later.

When more than 1 OneToMany or ManyToMany relationship is eagerly fetched

then Ebean will automatically convert one of those into a 'Query Join'. Ebean does this so that it

avoids

generating a cartesian product query. Note that Ebean determines the type of SQL join for you

based on the cardinality and optionality of the relationship.

<h4>Example 1</h4>

It this example both "customer.contacts" and "details" are OneToMany relationships.

.select("status")

.fetch("customer")

.fetch("customer.contacts") // contacts is a @OneToMany

.fetch("details") // details is a @OneToMany

.orderBy("customer.name")

.findList();

</pre></div>

</div>

... results in the "left outer join contact" and related t2.* columns being included in the

query (so they won't be lazy loaded later).

<div class="syntax sql"><div class="highlight"><pre>-- This first query includes the customer contacts

-- but does not include the order details

select t0.id c0, t0.status c1,

t1.id c2, t1.inactive c3, t1.name c4, ... -- truncated

t2.id c12, t2.first_name c13, t2.last_name c14, ... -- truncated

t2.when_updated c19, t2.customer_id c20

from orders t0

left outer join contact t2 on t2.customer_id = t1.id

order by t1.name;

</pre></div>

</div>

<h4>The 'Query Join' query ...</h4>

<div class="syntax sql"><div class="highlight"><pre>-- This second query fetchs the order details associated

-- with the orders that were returned by the first query

select t0.order_id c0, t0.id c1, t0.order_qty c2, ...

from order_detail t0

where (t0.order_id) in (?,?,?,?,?,?,?,?,?,?)

order by t0.order_id, t0.id;

--bind(2,1,1,1,3,3,3,4,4,4)

</pre></div>

</div>

<h4>Example 2</h4>

In this example contacts is a @OneToMany and then off from each contact notes is a @OneToMany.

<div class="syntax java"><div class="highlight"><pre>List<Customer> customers = Customer.find

.fetch("contacts") // contacts is a OneToMany

.fetch("contacts.notes") // notes is a OneToMany

.orderBy("name")

.findList();

</pre></div>

</div>

<div class="syntax sql"><div class="highlight"><pre>select t0.id c0, t0.inactive c1, t0.name c2, ...

t0.when_updated c7, t0.billing_address_id c8, ...

t1.id c10, t1.first_name c11, t1.last_name c12, ...

from customer t0

left outer join be_contact t1 on t1.customer_id = t0.id

order by t0.name, t0.id

</pre></div>

</div>

<h4>The 'Query Join' query ...</h4>

<div class="syntax sql"><div class="highlight"><pre>-- fetch the contact notes for all the contacts

select t0.contact_id c0, t0.id c1, t0.contact_id c2, t0.title c3, ...

from contact_note t0

where (t0.contact_id) in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

</pre></div>

</div>

<h2 id="fetchconfig">FetchConfig</h2>

When you specify a Query with Ebean it can result in more than 1 SQL query. Sometimes

you want explicit control over this (what the secondary queries are, batch size used, eager

or lazily invoked)

FetchConfig gives you the ability to specify these "secondary queries" and let them

executed lazily ("lazy loading join") or eagerly ("query join").

Note that explicitly using FetchConfig is not a requirement. Ebean is able to automatically

convert some joins

to "query joins" when it is needed such as when building object graphs with multiple *ToMany

relationships or when limit offset

is used with a *ToMany relationship.

You use FetchConfig to define that you want to use a separate SQL query to fetch that part of

the object graph (to use a "query join" rather than a "fetch join"). What this means is that

Ebean will use 2 SQL queries rather than 1 to build the object graph.

Note that you do not need to expicitly use FetchConfig if you don't want to. In that case Ebean

will automatically convert any fetch paths over to use FetchConfig if it needs to (multiple

*ToMany relationships etc).

FetchConfig defines the configuration options for a "query fetch" or a "lazy loading fetch".

This gives you the ability to use multiple smaller queries to populate an object graph as

opposed to a single large query. The primary goal is to provide efficient ways of loading

complex object graphs avoiding SQL Cartesian product and issues around populating object graphs

that have multiple *ToMany relationships. It also provides the ability to control the lazy

loading queries (batch size, selected properties and fetches) to avoid N+1 queries etc.

There can also be cases loading across a single OneToMany where 2 SQL queries using Ebean

FetchConfig.query() can be more efficient than one SQL query. When the "One" side is wide (lots

of columns) and the cardinality difference is high (a lot of "Many" beans per "One" bean) then

this can be more efficient loaded as 2 SQL queries.

The reason for using "Query Joins" as opposed to "Fetch joins" is that there are some

cases where using multiple queries is more efficient that a single query.

Any time you want to load multiple OneToMany associations it will likely be more

performant as multiple SQL queries. If a single SQL query was used that would result in a

Cartesian product.

There can also be cases loading across a single OneToMany where 2 SQL queries (using

Ebean "query join") can be more efficient than one SQL query (using Ebean "fetch join").

When the "One" side is wide (lots of columns) and the cardinality difference is high (a lot

of "Many" beans per "One" bean) then this can be more efficient loaded as 2 SQL queries.

Example: Find Orders join details using a single SQL query

<div class="syntax java"><div class="highlight"><pre>// Normal fetch join results in a single SQL query

List<Order> list = Ebean.find(Order.class).fetch("details").findList();

</pre></div>

</div>

Example: Using a "query join" instead of a "fetch join" we instead use 2 SQL queries

<div class="syntax java"><div class="highlight"><pre>// This will use 2 SQL queries to build this object graph.

.fetch("details", new FetchConfig().query())

.findList();

</pre></div>

</div>

queries:

<ol>

<li>find order</li>

<li>find orderDetails where order.id in (?,?...) // first 100 order id's</li>

</ol>

Example: Using 2 "query joins"

<div class="syntax java"><div class="highlight"><pre>// This will use 3 SQL queries to build this object graph

List<Order> list =

.fetch("customer", new FetchConfig().queryFirst(5))

.findList();

</pre></div>

</div>

queries:

<ol>

<li>find order</li>

<li>find orderDetails where order.id in (?,?...) // first 100 order id's</li>

<li>find customer where id in (?,?,?,?,?) // first 5 customers</li>

</ol>

Example: Using "query joins" and partial objects

<div class="syntax java"><div class="highlight"><pre>// This will use 3 SQL queries to build this object graph

List<Order> list =

.select("status, shipDate")

.fetch("details", "quantity, price", new FetchConfig().query())

.fetch("customer", "name", new FetchConfig().queryFirst(5))

.fetch("customer.contacts")

.fetch("customer.shippingAddress")

.findList();

</pre></div>

</div>

queries:

<ol>

<li>find order (status, shipDate)</li>

<li>find orderDetail (quantity, price) fetch product (sku, name) where order.id in (?,?

...)

</li>

<li>find customer (name) fetch contacts (*) fetch shippingAddress (*) where id in

(?,?,?,?,?)

</li>

</ol>

Note: the fetch of "details.product" is automatically included into the fetch of "details"

Note: the fetch of "customer.contacts" and "customer.shippingAddress" are automatically

included in the fetch of "customer"

You can use query() and lazy together on a single join. The query is executed immediately

and the lazy defines the batch size to use for further lazy loading (if lazy loading is

invoked).

.fetch("customer", new FetchConfig().query(10).lazy(5))

.findList();

</pre></div>

</div>

queries:

<ol>

<li>find order</li>

<li>find customer where id in (?,?,?,?,?,?,?,?,?,?) // first 10 customers</li>

<li>then if lazy loading of customers is invoked, use a batch size of 5 to load the

customers

</li>

</ol>

Example of controlling the lazy loading query:

This gives us the ability to optimise the lazy loading query for a given use case.

List<|Order> list = DB.find(Order.class)

.fetch("customer","name", new FetchConfig().lazy(5))

.fetch("customer.contacts","contactName, phone, email")

.fetch("customer.shippingAddress")

.where().eq("status",Order.Status.NEW)

.findList();

queries:

<ol>

<li>find order where status = Order.Status.NEW</li>

<li>if lazy loading of customers is invoked, use a batch size of 5 to load the

customers.

</ol>

Note: when the laxy loading of customers is performed, it perform like:

<div class="syntax xml"><div class="highlight"><pre>find customer (name)

fetch customer.contacts (contactName, phone, email)

fetch customer.shippingAddress (*)

where id in (?,?,?,?,?)

</pre></div>

</div>

Example of wo "Query Joins" results in 3 SQL queries used to build an object graph

<div class="syntax java"><div class="highlight"><pre>// A more advanced example with multiple query joins

List<Order> l0 = DB.find(Order.class)

.select("status, shipDate")

.fetch("details", "orderQty, unitPrice", new FetchConfig().query())

.fetch("customer", "name", new FetchConfig().query(10))

.fetch("customer.contacts","firstName, lastName, mobile")

.fetch("customer.shippingAddress","line1, city")

.findList();

</pre></div>

</div>

The resulting 3 sql queries are:

Query 1 - the main query - Note: customer_id was automatically added to support query join.

<div class="syntax xml"><div class="highlight"><pre>// query 1 … the main query

select o.id c0, o.status c1, o.ship_date c2, o.customer_id c3

from orders o

</sql>

</pre></div>

</div>

Query 2 - query join on customer - fetching the first 10 customers referenced (batch:10) but

there where actually only 2 to fetch (actual:2).

<div class="syntax xml"><div class="highlight"><pre><sql mode='+query' summary='Customer, shippingAddress

y:contacts' load='path:customer batch:10 actual:2'>

select c.id c0, c.name c1

, cs.id c2, cs.line_1 c3, cs.city c4

, cc.id c5, cc.first_name c6, cc.last_name c7, cc.mobile c8

from customer c

left outer join address cs on cs.id = c.shipping_address_id

left outer join contact cc on cc.customer_id = c.id

where c.id in (?,?,?,?,?,?,?,?,?,?)

order by c.id

</sql>

</pre></div>

</div>

Query 3 – query join on details - fetching the order details for the first 100 orders

(batch:100).

load='path:details batch:100 actual:3'>

select o.id c0

, od.id c1, od.order_qty c2, od.unit_price c3

, odp.id c4, odp.sku c5, odp.name c6

from orders o

left outer join order_detail od on od.order_id = o.id

left outer join product odp on odp.id = od.product_id

where o.id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

order by o.id

</sql>

</pre></div>

</div>

<h4>FetchConfig.lazy() - "Lazy Joins"</h4>

If a join is not defined at all (neither a fetch join or a query join) – then lazy loading

will by

default just fetch all the properties for that entity.

FetchConfig.lazy() allows you to control that lazy loading query – define the batch size,

properties to select and also fetch paths to include on the lazy load query.

This is very similar to a "query join" except that the loading occurs on demand (when the

property is requested and not already loaded).

The reason you would want to control the lazy loading query is to optimise performance

for further lazy loading (avoid N+1 queries, define joins that should be included for lazy

loading queries, load only the properties required and no more).

Example: Control the query used to lazy load

<div class="syntax java"><div class="highlight"><pre>// control the lazy loading of customers ...

.fetch("customer","name", new FetchConfig().lazy(5))

.fetch("customer.contacts","contactName, phone, email")

.fetch("customer.shippingAddress")

.where().eq("status",Order.Status.NEW)

.findList();

</pre></div>

</div>

In the example above the orders are loaded. Only when the application requests a

customer property (that is not the customer's id) then the lazy loading of the customer is

invoked. At that point the customer name is loaded, with the contacts and

shippingAddress – this is done in batch of 5 customers.

Note that if the customer status is requested (rather than the customer name) and that

invokes the lazy loading then all the customer's properties are loaded (rather than just the

customers name).

<div class="syntax java"><div class="highlight"><pre>Order order = list.get(0);

Customer customer = order.getCustomer();

// this invokes the lazy loading of 5 customers

String name = customer.getName();

</pre></div>

</div>

The resulting lazy loading query is …

<div class="syntax xml"><div class="highlight"><pre><sql mode='+lazy' summary='Customer, shippingAddress +many:contacts'

load='path:customer batch:5 actual:2'>

select c.id c0, c.name c1, cs.id c2, cs.line_1 c3, cs.line_2 c4, cs.city c5,

cs.cretime c6, cs.updtime c7, cs.country_code c8,

cc.id c9, cc.phone c10, cc.email c11

from customer c

left outer join address cs on cs.id = c.shipping_address_id

left outer join contact cc on cc.customer_id = c.id

where c.id in (?,?,?,?,?)

order by c.id

</sql>

</pre></div>

</div>

<h4>Using both</h4>

You can use both queryFirst() and lazy() on a single join. The queryFirst() part defines

the

number of beans that will be loaded eagerly via an additional query and then lazy defines

the batch size of the lazy loading that occurs after than (if there is any).

<div class="syntax java"><div class="highlight"><pre>new FetchQuery.queryFirst(100).lazy(10);

</pre></div>

</div>

<h4>+query and +lazy – query language syntax</h4>

To define "query joins" and "lazy joins" in the query language you can use +query and

+lazy. Optionally you can specify the batch size for both.

<div class="syntax groovy"><div class="highlight"><pre>find order

join customers (+query )

where status = :status

</pre></div>

</div>

<div class="syntax groovy"><div class="highlight"><pre>find order (status, shipDate)

join customers (+lazy(10) name, status)

where status = :orderStatus

</pre></div>

</div>

<h2 id="lazy_loading">Lazy loading</h2>

<h4>

Fine grained control over lazy loading.

</h4>

A Partial Object will lazy load the rest of the data on demand when you get or set a

property it does not have.

<div class="syntax java"><div class="highlight"><pre>// find order 12

// ... fetching the order id, orderDate and version property

// .... nb: the Id and version property are always fetched

.select("orderDate")

.where().idEq(12)

.findOne();

// shipDate is not in the partially populated order

// ... so it will lazy load all the missing properties

Date shipDate = order.getShipDate();

// similarly if we where to set the shipDate

// ... that would also trigger a lazy load

order.setShipDate(new Date());

</pre></div>

</div>

Lazy loading occurs automatically when you set or get a property that the partially

populated bean does not have.

<h2 id="named_queries">Named Queries</h2>

⚠️ Ebean 16.x primarily: Named Queries using EQL syntax were a feature of Ebean 16.x.

The EQL syntax within <code>@NamedQuery</code> annotations is no longer actively developed in Ebean 17.x.

For new code, use <a href="/docs/query/query-beans">Query Beans</a> or <a href="/docs/query/where">standard Query API</a> directly.

See <a href="/docs/query/eql">EQL documentation</a> and <a href="/docs/upgrading">upgrading guide</a>.

</div>

<div class="syntax java"><div class="highlight"><pre>import jakarata.persistence.NamedQueries;

import jakarata.persistence.NamedQuery;

...

@NamedQueries(value={

@NamedQuery(

name="bugsSummary",

query="find (name, email) fetch loggedBugs (title, status) where id=:id "),

@NamedQuery(

name="bugStatus",

query="fetch loggedBugs where loggedBugs.status = :bugStatusorder by name")

})

@Entity

@Table(name="s_user")

public class User implements Serializable {

...

</pre></div>

</div>

You can have named queries, where you define the query. Note that the names of the

queries are per entity type (not global as they are in JPA).

Once you get a named query you set any named parameters and then execute it – in the

case below we use findOne() as we expect only one object graph returned.

<div class="syntax java"><div class="highlight"><pre>User u = DB.createNamedQuery(User.class, "bugsSummary")

.setParameter("id", 1)

.findOne();

</pre></div>

</div>

<h4>Named Queries are Modifyable</h4>

Named queries are parsed early and returned as query objects to you that you can

modify. This means that you can get a named query and then modify the query by adding

to the where clause, setting the order by, limits etc.

This is an intentional feature and means that you can use Named Queries as a "starting

point" to then modify via code and execute.

<div class="syntax java"><div class="highlight"><pre>// you can treat namedQueries as starting points...

// ... in that you can modify them via code

// ... prior to executing the query

// you can modify a named query...

Set<User> users = DB.createQuery(User.class, "bugStatus")

.setParameter("bugStatus", "NEW")

// you can add to the where clause

.where().ilike("name", "rob%")

// you can set/override the order by

.orderBy("id desc")

// you can set/override limits (max rows, first row)

.setMaxRows(20)

.findSet();

</pre></div>

</div>

<h2 id="large_queries">Large queries</h2>

Large query results can take up a lot of memory if using findList() since loads all the

results in memory at once.

Ebean provides functionality for streaming the results. With this functionality, you

process the results rows one at a time.

Query.findIterate() - Execute the query iterating over the results. Requires

calling QueryIterator.close(), typically in a finally block, to prevent resource leakage.

Query.findEach(QueryEachConsumer

<T> consumer)

- Execute the query consuming each bean one at a time.

<o>

This method is appropriate to process very large query results as the beans are consumed

one at a time and do not need to be held in memory (unlike #findList #findSet etc)

Note that internally Ebean can inform the JDBC driver that it is expecting larger resultSet and

specifically for MySQL this hint is required to stop it's JDBC driver from buffering the entire

resultSet. As such, for smaller resultSets findList() is generally preferable.

Compared with findEachWhile() this will always process all the beans where as findEachWhile()

provides a way to stop processing the query result early before all the beans have been read.

This method is functionally equivalent to findIterate() but instead of using an iterator uses

the QueryEachConsumer (SAM) interface which is better suited to use with Java8 closures.

<div class="syntax java"><div class="highlight"><pre>database.find(Customer.class)

.where().eq("status", Status.NEW)

.order().asc("id")

.findEach((Customer customer) -> {

// do something with customer

System.out.println("-- visit " + customer);

});

</pre></div>

</div>

Query.findEachWhile(Predicate<T> consumer)

- >Execute the query using callbacks to a visitor to process the resulting beans one at a

time.

This is similar to .findEach(...) except that you return boolean true to continue processing

beans and return false to stop processing early.

View remainder of file in raw view

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

features.html

Latest commit

History

features.html

File metadata and controls