Table of Contents
This chapter shows how to select objects from the database using SelectQuery.
It was shown before how to persist new objects. Cayenne queries are used to access already saved objects. The primary query type used for selecting objects is SelectQuery. It can be mapped in CayenneModeler or created via the API. We'll use the later approach in this section. We don't have too much data in the database yet, but we can still demonstrate the main principles below.
Select all paintings (the code, and the log output it generates):
SelectQuery select1 = new SelectQuery(Painting.class); List paintings1 = context.performQuery(select1);
INFO: SELECT t0.GALLERY_ID, t0.ARTIST_ID, t0.NAME, t0.ID FROM PAINTING t0 INFO: === returned 2 rows. - took 18 ms.
Select paintings that start with "gi", ignoring case:
Expression qualifier2 = ExpressionFactory.likeIgnoreCaseExp(
Painting.NAME_PROPERTY,
"gi%");
SelectQuery select2 = new SelectQuery(Painting.class, qualifier2);
List paintings2 = context.performQuery(select2);INFO: SELECT t0.GALLERY_ID, t0.NAME, t0.ARTIST_ID, t0.ID FROM PAINTING t0 WHERE UPPER(t0.NAME) LIKE UPPER(?)
[bind: 1->NAME:'gi%'] - prepared in 6 ms.
INFO: === returned 1 row. - took 18 ms.Select all paintings done by artists who were born more than a 100 years ago (demonstrating using Expression.fromString(..) instead of ExpressionFactory):
Calendar c = new GregorianCalendar(); c.set(c.get(Calendar.YEAR) - 100, 0, 1, 0, 0, 0); Expression qualifier3 = Expression.fromString("artist.dateOfBirth < $date"); qualifier3 = qualifier3.expWithParameters(Collections.singletonMap("date", c.getTime())); SelectQuery select3 = new SelectQuery(Painting.class, qualifier3); List paintings3 = context.performQuery(select3);
INFO: SELECT t0.GALLERY_ID, t0.NAME, t0.ARTIST_ID, t0.ID FROM PAINTING t0 JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ID)
WHERE t1.DATE_OF_BIRTH < ? [bind: 1->DATE_OF_BIRTH:'1911-01-01 00:00:00.493'] - prepared in 7 ms.
INFO: === returned 2 rows. - took 25 ms.