Now that we've got a feel of how DataContext works and how to create and save objects, let's take a small diversion and implement a piece of code that will delete all data from the database every time the tutorial application is started. We'll learn how to map a raw SQL query with parameters using the modeler and then execute it in the code.

  • Open CayenneModeler, go to "File > Recent Files" and choose the tutorial mapping file.
  • When the project opens, select the DataMap, and click on "Create Query" button (or select "Project > Create Query" from the menu).
  • For the query type select "Raw SQL" and click "Create".
  • For "Query Name" enter "DeleteAll" - this will be the key used to reference query later in the code:

Ignore "Select Properties" settings as the query will not select anything. Rather it will delete all rows from the table with name specified as a parameter.

  • Go to "SQL Scripts" tab and enter the following in the right panel:
delete from $table

Note that "$table" is a variable that will be dynamically substituted by Cayenne in runtime with the table name. (Detailed discussion of SQLTemplate scripting options is provided here).

  • Query mapping is complete. Save the project in the Modeler and go back to Eclipse.
  • In Eclipse, refresh the mapping by right-clicking on cayenne-tutorial project and selecting "Refresh".
  • Now add the following code inserting it after the DataContext creation line and before any other code:
Main.java
QueryChain chain = new QueryChain();
chain.addQuery(new NamedQuery("DeleteAll", Collections.singletonMap(
                "table",
                "PAINTING")));
chain.addQuery(new NamedQuery("DeleteAll", Collections.singletonMap(
                "table",
                "ARTIST")));
chain.addQuery(new NamedQuery("DeleteAll", Collections.singletonMap(
                "table",
                "GALLERY")));

context.performGenericQuery(chain);

Note that since we need to run the same query three times with different sets of parameters, first we created a QueryChain that can hold other queries. Then we added three NamedQueries (the order is important - painting has to be deleted before artist or gallery due to the foreign key constraints). NamedQuery is a query that is a reference to another query mapped via CayenneModeler (in this case - a query called "DeleteAll" that we mapped above).

Now we can re-run the application and see the following output in the beginning of the log:

INFO  QueryLogger: delete from PAINTING
INFO  QueryLogger: === updated 2 rows.
INFO  QueryLogger: delete from ARTIST
INFO  QueryLogger: === updated 1 row.
INFO  QueryLogger: delete from GALLERY
INFO  QueryLogger: === updated 1 row.

Note that raw sql queries above (aka SQLTemplates) are run directly against the database bypassing Cayenne object layer. If instead of bulk delete you needed to delete individual objects, you should do so using DataContext.deleteObject(..) as discussed here.

Next Step: Tutorial SelectQuery