9.4. Named SQL Events

9.4.1.1. Retrieving Information from the Database

Executing arbitrary queries through DataQueries is easy. You can retrieve them in the same way that you retrieve an existing data object, and you can execute the query and loop through the results in the same way that you use a DataAssociationCursor.

To begin, you retrieve a query through the Session object using its model and name. Then, you can use next() to loop through the results.

For example, if you want all paragraphs that show up in the magazine with issue number 5A. The first step is to define the query within the PDL file. A DataQuery definition has four sections. It begins with the declaration of the name of the query followed by data type mappings for each returned attribute. It concludes with two code blocks. The first block, the DO block, contains the actual SQL that will be executed. The second block, the MAP block, allows the developer to map database columns to attribute names. The attributes are the values that can be accessed from the Java code.

To accomplish the task of retrieving the paragraphs as mentioned above, you could declare the following DataQuery in your PDL file:

model tutorial;

// the first line indicates that it is a query and the name of the query
query paragraphMagazines {
   // the next section maps the attributes to the java type so that the
   // same type is returned regardless of which database driver is used.
   BigDecimal magazineID;
   BigDecimal paragraphID;
   String issueNumber;
   String text;
   do {
       select m.magazine_id, p.paragraph_id, issue_number, text
       from magazines m, a, magazine_article_map ma, paragraphs p
       where ma.magazine_id = m.magazine_id
       and p.article_id = ma.article_id
   } map { 
       magazineID = m.magazine_id;
       paragraphID = p.paragraph_id;
       issueNumber = m.issue_number;
       text = p.text;
   }
}   

With this PDL definition, it should be easy to see how the following code does what is desired (remove the "\" and make it all one line).

DataQuery query = SessionManager.getSession().retrieveQuery\
("tutorial.paragraphMagazines");
query.addEqualsFilter("issueNumber", "5A");
while (query.next()) {
     System.out.println((String)query.get("text"));
}

9.4.1.2. Creating Data Objects

The method discussed for retrieving arbitrary information from the database is sufficient to do most of what is needed. However, it is not very convenient since most Java code is written around using DataObjects. Therefore, most developers want to be able to retrieve DataObjects directly from the DataQuery. One way to do this is to create a new DataObject for each row returned by the query and then populate that DataObject with the information retrieved. While this works, it is inefficient and inelegant.

To solve this problem, the DataQuery allows the developer to create DataObjects directly from the query. The objects can be defined within the query statement in a fashion similar to Attribute declarations.

Suppose you want to get all magazines that have authors of articles whose last name starts with a given sequence of characters. This is not a standard association because you are actually going through two separate mapping tables. This could be done by getting all articles with authors that match the criteria and then getting all magazines that contain the articles. However, this option would require two separate database hits. Another option is to perform a query and then for every row create the corresponding data object. A third option is to have the persistence layer create the data objects for you. The following example shows how you can allow the persistence layer to perform the work for you.

The PDL is simply a Data Query with extra Attribute definitions (remove the "\" and make it all one line).

query MagazineToAuthorMapping {
    // the next two lines are declaring that objects will be returned
    Magazine magazine;
	Author author;

    do {
        select publications.name, issue_number, publication_id,
               authors.first_name, authors.last_name, author_id
          from magazines, publications, articles, authors,
               magazine_article_map, article_author_map
         where publications.publication_id = magazines.magazine_id
           and magazine_article_map.magazine_id = magazines.magazine_id
           and maagazine_article_map.article_id = article_author_map.\
article_id
           and article_author_map.author_id = authors.author_id
    } map {
        // here we map the attributes of the objects to columns returned
        // by the query.
        magazine.name = publications.name;
        magazine.issueNumber = magazines.issue_number;
        magazine.id = publications.publication_id;
        author.authorID = authors.author_id;
        author.firstName = authors.first_name;
        author.lastName = authors.last_name;
    }
}

This can then be accessed with the Java like most other queries. The \ marks where the line has been wrapped for printing purposes.

DataQuery query = SessionManager.getSession().retrieveQuery\
("tutorial.MagazineToAuthorMapping");

// the next line adds the filter so that we only get author's whose last
// name begins with the letter "s".  Note that we are using a stanard filter
// because we need to perform a function on the column and we are positive
// that the value is not null.
Filter filter = query.addFilter("lower(lastName) like \
'%' || :lastNamePrefix");
filter.set("lastNamePrefix", "s");

while (query.next()) {
    DataObject myAuthor = query.get("author");
    DataObject myMagazine = query.get("magazine");
    System.out.println("the author I retrieved is " + myAuthor);
    System.out.println("the magazine I retrieved is " + myMagazine);
}

9.4.2.1. Executing Arbitrary DML

Data Operations are similiar to DataQueries in both structure and use. However, while they are retrieved in a fashion similar to DataQueries, they are executed differently. After the query is retrieved, the program can set bind variables, after which it is executed. Suppose you want to create a magazine with ID 4 using all articles in the system that are not yet currently in a magazine. To do this, you could create a new Magazine DataObject, give it an ID of 4, use a DataQuery to get all articles not already in a magazine, add those articles to the magazine through the use of associations, and then save the magazine. Alternately, you can use a DataOperation and execute a single query.

The DataOperation to execute the above query is structured in almost the same way as a DataQuery. In fact, it can even have an OPTIONS block (although it does not yet have any valid values for the options block). However, since it does not return many different rows of results, it does not allow attribute mappings before the first do block. The PDL can be defined as follows:

data operation createMagazine {
   do {
      insert into magazine_article_map (magazine_id, article_id)
      select :magazineID, article_id from articles where not exists
      (select 1 from magazine_article_map 
      where magazine_article_map.article_id = articles.article_id)
   }
}

Now that the operation is defined, you can set the value of the bind variable magazineID to the correct value and then execute the operation. This can be done with code such as the following (the \ marks where the line has been wrapped for printing purposes):

DataOperation operation = getSession().retrieveDataOperation\
("tutorial.createMagazine");
// we have to pass in an Integer instead of an int so that JDBC can
// handle it correctly
operation.setParameter("magazineID", new Integer(4));
operation.execute();

9.4.2.2. Executing PL/SQL

Developers often need to execute PL/SQL procedures and functions. Therefore, it is possible to execute both using a DataOperation with additional syntax. Arguments can be passed to functions ans procedures using Parameter Binding.

	Note
	The methods described below do not allow users to return cursors from their PL/SQL functions. If this is required, the recommended workaround is to use a CallableStatement directly and bypass the persistence layer entirely.

9.4.2.2.1. PL/SQL Procedures

Suppose you want to execute the following PL/SQL procedure:

create or replace function myPLSQLProc(v_priority in integer) as begin insert into magazines (magazine_id, title) select nvl(max(magazine_id), 0) + 1, :title from magazine_id; end; / show errors

To do this, first include the above statement in your SQL file, so that it will be defined in the database when your package is installed. Next, declare it in your PDL file using a DataOperation:

data operation DataOperationWithPLSQLAndArgs { do { select myPLSQLProc(:title) from dual } }

You can then execute this data operation just like any other data operation, after binding the title variable.

It is also possible to use OUT parameters within the DataOperation. To do this, the only additional requirement is for the developer to specify the JDBC type of all of the parameters within the query.

Suppose you want to copy the article with the highest ID into a new row with the ID that you pass into the procedure, and you want back the ID for the row that was copied. You can declare a PL/SQL procedure such as the following:

create or replace procedure DataOperationProcWithInOut( v_new_id IN Integer, v_copied_id OUT Integer) as begin select max(article_id) into v_copied_id from articles; insert into articles (article_id, title) select v_new_id, title from articles where article_id = v_copied_id; insert into article_author_map (article_id, author_id) select v_new_id, author_id from article_author_map where article_id = v_copied_id; end; / show errors

Adding the JDBC type, the PDL to access this procedure appears as follows:

data operation DataOperationProcWithInOut { do call { DataOperationProcWithInOut(:newID, :copiedID) } map { newID : INTEGER; copiedID : INTEGER; } }

To execute this in Java, you simply need to bind the variable and then retrieve the variable using the get(String) method in a fashion similar to retrieving a value from a DataQuery. For instance, to print out the value of the copiedID variable, the following code can be executed:

DataOperation operation = getSession().retrieveDataOperation ("tutorial.DataOperationProcWithInOut"); operation.set("newID", new Integer(4)); operation.execute(); Integer copiedID = (Integer)operation.get("copiedID"); System.out.println("The copied ID was [" + copiedID.toString() + "]");

	Note
	The do call and OUT parameters are not available for Postgres because Postgres has not yet implemented CallableStatements or OUT parameters.

create or replace function DataQueryPLSQLFunction(v_article_id in integer) 
return number
is
   v_title varchar(700);
begin
   select title into v_title from articles
    where article_id = v_article_id;
   return v_title;
end;
/
show errors

data operation DataOperationWithPLSQLAndArgsAndReturnInPDL {
    do call {
       :title = DataQueryPLSQLFunction(:articleID)
    } map {
        title : VARCHAR(700);
        articleID : Integer;
    }
}