Changing Entity Values

Using EJB-QL and ejbSelect()

If you ran the example in the previous section or read through the code, you probably realize that having just a single finder method may not be enough. An application developer using the login entity bean probably wants more ways to query the table. In this section, we look at two ways to add query capabilities to your bean: adding a finder method and using ejbSelect().

Adding Finder Methods

As we saw earlier, you declare finder methods in the local home interface in order for them to be used in a manner that will return an actual Login entity bean. The findByPrimaryKey() finder is recognized by the server and takes advantage of the <primkey-field> element defined in the deployment descriptor to generate the necessary SQL.

Although easy to do, adding additional finder methods requires that you let the server know how to build the SQL using a language called Enterprise JavaBeans Query Language (EJB-QL). Two steps are involved when adding a new finder method:

1. Add the appropriate signature to the local home interface.

2. Add an EJB-QL entry to the deployment descriptor for the entity bean.

In step 1, the new signature typically starts with the string find. You include one or more parameters that match the fields in the table for the bean. The parameters are used to query specific fields of the database to find the result row you are after. The result of the new finder method is one of two types. If the result of the finder method is guaranteed to be a single row, you use the local interface Login as the return type (just as you did for the findByPrimaryKey() method). However, many queries return more than one result, therefore, you need to provide a result type that can handle a set of rows. For these results, you use the Java data-type Collection and an iterator that moves through the results. We show examples of this result a little later.

In step 2, you let the server know how it should query the database table for all of your new finder methods. The process uses a language called EJB-QL, which looks just like normal SQL but uses placeholders for the tables and parameters in the WHERE clause.

Finding All Logins

Now, let's add a query that returns a result with all of the rows in the login database. Call the finder method findAll(). No parameters are required; however, since the query returns more than one row, use the Collection type. Here's the code for the local home interface:

 package entitlements; import javax.ejb.*; import java.util.*; public interface LoginHome extends EJBLocalHome {   Login findByPrimaryKey(String id)    throws FinderException;   Collection findAll()    throws FinderException; } 

In your new local home interface, you've added a new method called findAll(), which returns all of the rows in your login table in a Collection object. The real work behind this query takes place in an entry called <query> (which you add to the <entity> element of your Login bean). Here's what the <query> entry looks like for your findAll() method:

     <query>       <query-method>         <method-name>findAll</method-name>       </query-method>       <ejb-ql>SELECT o FROM logins o</ejb-ql>     </query> 

Let's take a close look at two subelements of the <query> element. The first, <method-name>, tells the server which finder method defined in the local home interface this entry corresponds to. Use a value of findAll since this is the method name. The second element, called <ejb-ql>, has a value representing the SQL to be executed when the finder method is executed. The EJB-QL looks like standard SQL and has two placeholders to be handled by the server. We won't go into detail about EJB-QL; for a good explanation see http://java.sun.com/j2ee/tutorial.1_3-fcs/doc/EJBQL.html.

Of particular importance is the FROM clause. Use the <abstract-schema-name> value so that the server pulls the right value when determining which table the query should be used against.

Using findAll() in a Servlet

When changes are made to the deployment descriptor and local home interface, Resin-EE automatically compiles them and makes the entity bean ready for use. You need to change your servlet code to use the new finder method, as shown here:

 package entitlements; import java.io.*; import javax.servlet.*; import javax.servlet.http.*; import javax.naming.*; import javax.ejb.*; import java.util.*; public class LoginServlet extends HttpServlet {   private LoginHome home = null;   public void init()     throws ServletException {     try {       Context cmp = (Context)         new InitialContext().lookup("java:comp/env/cmp");       home = (LoginHome) cmp.lookup("Login ");     } catch (NamingException e) {       throw new ServletException(e);     }   }   public void doGet(HttpServletRequest req,                     HttpServletResponse res)     throws IOException, ServletException   {     PrintWriter out = res.getWriter();     res.setContentType("text/html");     out.println("<h3>All Current Logins</h3>");     try {       Collection c = home.findAll();       Iterator iter = c.iterator();       while (iter.hasNext()) {         Login row = (Login)iter.next();         out.println('Login: ' + row.getLogin() + '<br>');         out.println('Opendate: ' + row.getOpendate()           + '<br><hr>');       }     }     catch (FinderException e) {       throw new ServletException(e);     }   } } 

In this new servlet, you have a call to the home.findAll() method; the results are set to the variable c. An iterator is obtained from the Collection and a loop runs through all of the rows displaying the login and opendate of the account. Figure 6.3 shows the resulting output.

Figure 6.3: findAll()'s output.

Finding Logins by Open Date

Let's add another finder method that uses a parameter, the open date, for an account. The new finder method is called findByOpenDate() and its signature defined as

 Collection findByOpenDate(String d)    throws FinderException; 

Place the signature in the local home interface and put a new <query> entry into the deployment descriptor, as shown here:

 <query>       <query-method>         <method-name>findByOpenDate</method-name>       </query-method>       <ejb-ql>SELECT o FROM logins o WHERE o.opendate=?1       </ejb-ql> </query> 

Note the addition of a WHERE clause. The WHERE clause allows you to narrow the results found in the database based on the fields and a specific value. In this case, you will be using the opendate field, as shown by the o.opendate text. The o will be replaced with an alias name for your database table. The opendate field is checked against a value denoted by ?1. The value ?1 is a placeholder for the first parameter to the findByOpenDate() method.

At this point, modify the code in your servlet to use the new finder method:

     try {       Collection c = home.findByOpenDate('2002-02-12');       Iterator iter = c.iterator();       while (iter.hasNext()) {         Login row = (Login)iter.next();         out.println('Login: ' + row.getLogin() + '<br>');         out.println('Opendate: ' + row.getOpendate()           + ' <br><hr>' );       }     }     catch (FinderException e) {       throw new ServletException(e);     } 

All of the rows in the table where the opendate field is 2002-02-12 will be returned by the findByOpenDate() method and displayed to the user.

Finding Logins by Open and Closed Date

Let's add one more finder method to illustrate how you can have more than one parameter in the method and EJB-QL query. The new method is called findByOpenCloseDate(String open, String closed). The full signature is:

 Collection findByOpenCloseDate(String open, String closed)    throws FinderException; 

Again, the important work occurs in the deployment descriptor:

 <query>       <query-method>         <method-name>findByOpenCloseDate</method-name>       </query-method>       <ejb-ql>SELECT o FROM logins o WHERE o.opendate=?1          and o.closedate=?2</ejb-ql>     </query> 

The method call findByOpenClosedDate has two parameters that you must place in the query. Use standard SQL to build the WHERE clause (but number the parameters ?1 and ?2, which relate to the open and close date parameters, respectively). The following snippet shows how to use the method:

     try {       Collection c = home.findByOpenCloseDate('2002-02-12',         '2002-12-30');       Iterator iter = c.iterator();       while (iter.hasNext()) {         Login row = (Login)iter.next();         out.println('Login: ' + row.getLogin() + '<br>');         out.println('Opendate: ' + row.getOpendate()           + '<br><hr>' );       }     }     catch (FinderException e) {       throw new ServletException(e);     } 

Using ejbSelect()

Note that all of the finder methods return either a single instance of the Login class or a collection of Login classes. This is a requirement of the finder methods. It is possible to create methods that return a single field or a collection of fields from one or more tables based on a query of one or more tables.

The downside to the ejbSelect() method is that its scope is based within the bean itself. This is because you created its abstract signature in the bean implementation and not the local home interface. Business methods defined at the local interface scope typically use the ejbSelect() methods to provide intermediate results to the application.

To see how this works, let's create a select method that returns just the logins available in the table. The first step is to create a method in the bean implementation class:

 public abstract Collection ejbSelectJustLogins()   throws FinderException; 

The ejbSelectJustLogins() method doesn't include any parameters, but it could if needed. You define the SQL to be executed by the select method in the deployment descriptor. Here's an example query for your new method:

 <query>       <query-method>         <method-name>ejbSelectJustLogins</method-name>       </query-method>       <ejb-ql>SELECT o.login FROM logins o</ejb-ql>     </query> 

Now the only methods that can use ejbSelectJustLogins() are those business methods exposed by the local interface. So you need to create a new method signature and place it in the local interface class:

 package entitlements; import javax.ejb.*; import java.util.*; public interface Login extends EJBLocalObject {   String       getLogin();   String       getTs();   String       getRole();   String       getDescription();   Date         getOpendate();   Date         getClosedate();   void          setTs(String t);   void          setRole(String s);   void          setDescription(string s);   void          setOpendate(Date d);   private void  setClosedate(Date d);   void          changeClosedate(Date d);   Collection    pullLogins(); } 

Here you've added a new business method called pullLogins() that returns a Collection containing all of the logins found in the system. Now you need to write the code behind the business method and place that code in the bean implementation:

   public Collection pullLogins() {     Collection c = null;     try {       c = ejbSelectJustLogins();     } catch(FinderException e) {};     return c;   } 

After changing all of the bean code, you can make a call to the pullLogins() method from a servlet and get the output passed from the ejbSelectJustLogins() method. Here's the servlet snippet:

     try {       Login e = (Login)home.findByPrimaryKey("johnd");       Collection c = e.pullLogins();       Iterator iter = c.iterator();       while (iter.hasNext()) {         String row = (String)iter.next();         out.println('Login: ' + row + '<br>');       }     }     catch (FinderException e) {       throw new ServletException(e); }