Title: Tutorial 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 similar to how the SQLTemplate was mapped, in this chapter however we'll show how to create it using Cayenne API.

We don't have too much data in the database yet, but we can still demonstrate the main principles:

Select all paintings (the code, and the log output it generates):

Main.java

SelectQuery select1 = new SelectQuery(Painting.class);
List paintings1 = context.performQuery(select1);

INFO  QueryLogger: SELECT t0.NAME, t0.ARTIST_ID, t0.GALLERY_ID, t0.ID FROM PAINTING t0
INFO  QueryLogger: === returned 2 rows. - took 20 ms.

Select paintings that start with "gi", ignoring case (read more about qualifier Expressions and ExpressionFactory here:

Main.java

Expression qualifier2 = ExpressionFactory.likeIgnoreCaseExp(
                Painting.NAME_PROPERTY,
                "gi%");
SelectQuery select2 = new SelectQuery(Painting.class, qualifier2);
List paintings2 = context.performQuery(select2);

INFO  QueryLogger: SELECT t0.NAME, t0.ARTIST_ID, t0.GALLERY_ID, t0.ID FROM PAINTING t0 
                             WHERE UPPER(t0.NAME) LIKE UPPER(?) [bind: 'gi%']
INFO  QueryLogger: === returned 1 row. - took 28 ms.

Select all paintings done by artists who were born more than a 100 years ago (demonstrating using Expression.fromString(..) instead of ExpressionFactory):

Main.java

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  QueryLogger: SELECT t0.NAME, t0.ARTIST_ID, t0.GALLERY_ID, t0.ID FROM PAINTING t0, ARTIST t1 
                             WHERE t0.ARTIST_ID = t1.ID AND (t1.DATE_OF_BIRTH < ?) [bind: '1906-01-01 00:00:00.3']
INFO  QueryLogger: === returned 2 rows. - took 31 ms.

Next Step: Tutorial Delete