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