Title: Tutorial SelectQuery

Tutorial SelectQuery

This section shows how to select objects from the database using SelectQuery.

Introducing 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 (read more about qualifier Expressions and ExpressionFactory here:

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.ARTIST_ID, t0.NAME, t0.ID FROM PAINTING t0 
WHERE UPPER(t0.NAME) LIKE UPPER(?) [bind: 1->NAME:'gi%']
INFO: === returned 1 row. - took 12 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);

SELECT t0.GALLERY_ID, t0.ARTIST_ID, t0.NAME, t0.ID FROM PAINTING t0 
JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ID) WHERE t1.DATE_OF_BIRTH < ? 
[bind: 1->DATE_OF_BIRTH:'1909-01-01 00:00:00.378']
INFO: === returned 2 rows. - took 19 ms.

Next Step: Tutorial Delete