Talking to MySQL with Entity Beans

I l @ ve RuBoard

If all EJB let you do was talk to transient objects, it wouldn't have much use. Of course, EJB lets you do a lot more ”including talking to databases.

You'll see now how to use container-managed persistence (CMP) to get at your data for customers and addresses using the JAWS CMP feature of JBoss.

JAWS is the JBoss implementation of container-managed persistence. Each vendor has its own technologies under development for CMP (it's still a new concept, and the standards are in a state of flux).

Setting up JAWS is just a matter of editing a few configuration files. The configuration files that you will need to alter are in the conf\default subdirectory of JBoss. The first, called jboss.jcml, is used to define the data sources (among other things). Find the section marked JDBC and change it to read as follows in Listing 17.15.

Listing 17.15 Changes to jboss.jcml

 <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">      <attribute name="Drivers">org.gjt.mm.mysql.Driver</attribute>   </mbean>    <mbean code="org.jboss.jdbc.XADataSourceLoader"        name="DefaultDomain:service=XADataSource,name=mySQLDB">        <attribute name="DataSourceClass">            org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attribute>        <attribute name="PoolName">mySQLDS</attribute>        <attribute name="URL">jdbc:mysql://localhost/BFG</attribute>        <attribute name="JDBCUser">bfguser</attribute>        <attribute name="Password">bfg</attribute>      </mbean> 

The additions to jboss.jcml define the MySQL driver as the default database driver and supply the connection information needed to get to your database instance. They also set up a connection pool, similar to the one that you used in Turbine. You can also set up values such as timeouts and the maximum number of connections, but just go with the defaults for now.

You also need to edit standardjaws.xml and change the datasource and type-mapping entries. The top of the file will then look like this:

 <?xml version="1.0" encoding="UTF-8"?> <jaws>     <datasource>java:/mySQLDS</datasource>     <type-mapping>mySQL</type-mapping>     <debug>false</debug> 

If you restart JBoss, you should see signs that things were done right. Look for the following entries in the console output:

 . . . [JdbcProvider] Initializing [JdbcProvider] Loaded JDBC-driver:org.gjt.mm.mysql.Driver [JdbcProvider] Initialized . . . [XADataSourceLoader] Starting [mySQLDS] XA Connection pool mySQLDS bound to java:/mySQLDS [XADataSourceLoader] Started 

You should also copy the mm.mysql.jar file from the Tomcat lib subdirectory to the jboss lib/ext subdirectory so that JBoss will know how to communicate with MySQL.

With JAWS configuration out of the way, it's on to actually writing classes that use it. To begin, define two sets of bean classes for addresses and customers that look mostly like the ones that you created before. Now, however, you're including many set and get methods , just as if the beans were normal beans running under JSP (shown in Listings 17.16 and 17.17).

Listing 17.16 Customer.java

 package com.bfg.ejb.customer; import javax.ejb.EJBObject; import java.rmi.RemoteException; public interface Customer extends EJBObject {     public String getEmail() throws RemoteException;     public void setEmail(String em) throws RemoteException;     public String getPassword() throws RemoteException;     public void setPassword(String pw) throws RemoteException;     public int getCustomerId() throws RemoteException;     public void setCustomerId(int id) throws RemoteException; } 

Remember, this defines the methods that can be used against an instance of the Customer class returned from the Home object.

Listing 17.17 Address.java

 package com.bfg.ejb.customer; import javax.ejb.EJBObject; import java.rmi.RemoteException; public interface Address extends EJBObject {     public String getFirstName() throws RemoteException;     public void setFirstName(String val) throws RemoteException;     public String getLastName() throws RemoteException;     public void setLastName(String val) throws RemoteException;     public String getStreet1() throws RemoteException;     public void setStreet1(String val) throws RemoteException;     public String getStreet2() throws RemoteException;     public void setStreet2(String val) throws RemoteException;     public String getCity() throws RemoteException;     public void setCity(String val) throws RemoteException;     public String getState() throws RemoteException;     public void setState(String val) throws RemoteException;     public String getPostalCode() throws RemoteException;     public void setPostalCode(String val) throws RemoteException;     public Integer getAddressId() throws RemoteException;     public void setAddressId(Integer val) throws RemoteException; } 

Address is similar to Customer , except that there are more methods to code. They all have to throw RemoteException because EJB throws it if something goes wrong during invocation on the EJB server.

Listing 17.18 CustomerHome.java

 package com.bfg.ejb.customer; import javax.ejb.EJBHome; import javax.ejb.CreateException; import javax.ejb.FinderException; import java.rmi.RemoteException; import java.util.Collection; public interface CustomerHome extends EJBHome {     public Customer create(String Email, String Password)       throws RemoteException, CreateException;     public Customer findByPrimaryKey (String Email)       throws RemoteException, FinderException;     public Collection findAll()       throws RemoteException, FinderException; } 

The Home class (see Listing 17.19) defines the operations that can be used to create or retrieve a copy of the class. In addition to the create method that you saw for session beans, entity beans must at least define findByPrimaryKey , which is used to return an instance of the object that matches the unique primary key for the database. You may also define findAll , which returns all instances in a collection.

Listing 17.19 AddressHome.java

 package com.bfg.ejb.customer; import javax.ejb.EJBHome; import javax.ejb.CreateException; import javax.ejb.FinderException; import java.rmi.RemoteException; import java.util.Collection; public interface AddressHome extends EJBHome {     public Address create(String firstName, String lastName,                          String street1, String street2,                          String city, String state,                          String postalCode)       throws RemoteException, CreateException;     public Address findByPrimaryKey (Integer id)       throws RemoteException, FinderException;     public Collection findByAddressBookOwner (Integer id)       throws RemoteException, FinderException;     public Collection findAll()       throws RemoteException, FinderException; } 

The Home class for addresses (see Listing 17.19) also has findByPrimaryKey and findAll , but it adds findByAddressBookOwner , which takes a customer ID as an argument and returns all the addresses in the customer's address book. This requires a little special handling, as you will soon see.

Listing 17.20 CustomerBean.java

 package com.bfg.ejb.customer; import javax.ejb.EntityBean; import javax.ejb.EntityContext; import java.rmi.RemoteException; public class CustomerBean implements EntityBean {     EntityContext ctx;     public int id;     public String email;     public String password;     public String ejbCreate (String Email, String Password) {      email = Email;      password = Password;      return null;     }     public void ejbPostCreate(String Email, String Password) { }     public String getEmail () {       return email;     }     public void setEmail (String Email) {       email = Email;     }     public String getPassword() {       return password;     }     public void setPassword(String Password) {       password = Password;     }     public int getCustomerId() {       return id;     }     public void setCustomerId(int Id) {      id = Id;     }     public void setEntityContext(EntityContext ctx) { this.ctx = ctx; }     public void unsetEntityContext() { ctx = null; }     public void ejbActivate() { }     public void ejbPassivate() { }     public void ejbLoad() { }     public void ejbStore() { }     public void ejbRemove() { } } 

Entity beans such as CustomerBean.java (see Listing 17.20) need to store their context, so you need to have it stored in the bean's local properties. You'll also notice that the properties variables for the state ( email and password ) need to be public, which is the opposite of what you would do in a normal bean.

Because the creation is actually handled by JAWS, you just need to set the state variables. JAWS takes care of the database for you. Normal get and set methods are used for the instance variables.

Again, because you're coding an entity bean, you need it to be able to store and flush its context. This is boilerplate code.

AddressBean.java is so identical to CustomerBean.java that it's not worth including; it's the same, except that it has different instance variables. The only thing to note is that the create always returns the type of the primary key, so it returns an Integer in this case rather than a String .

Listing 17.21 Additions to ejb-jar.xml

 <entity>       <ejb-name>Customer</ejb-name>       <home>com.bfg.ejb.customer.CustomerHome</home>       <remote>com.bfg.ejb.customer.Customer</remote>       <persistence-type>Container</persistence-type>      <ejb-class>com.bfg.ejb.customer.CustomerBean</ejb-class>       <prim-key-class>java.lang.String</prim-key-class>       <reentrant>False</reentrant>       <cmp-field><field-name>id</field-name></cmp-field>       <cmp-field><field-name>email</field-name></cmp-field>       <cmp-field><field-name>password</field-name></cmp-field>       <primkey-field>email</primkey-field>   </entity> 

Now you do all the configurations to make this work. First, you add the entities to your ejb-jar.xml. Start by creating an entity entry for Customer , which defines the home, remote, and ejb-class classes, just as you did for a session bean (see Listing 17.21).

Next , you define this entity as using container persistence rather than bean persistence, which tells JBoss to use JAWS with this entity.

You have to declare the type of the primary key. It must match the value returned by the create method and also must match what you declare to be the type of the variable used to hold the primary key in the object.

The reentrant field refers to whether the bean can call itself (in other words, if it can loop back during a call and make another call to the same bean).

Now you have to declare all the container-managed fields. Note that you declare the variable name, not the accessor. It should now be clear why they had to be public. Finally, declare the field that acts as the primary key.

The address entity is declared in the same manner (see Listing 17.22).

Listing 17.22 Adding Address to ejb-jar.xml

 <entity>       <ejb-name>Address</ejb-name>       <home>com.bfg.ejb.customer.AddressHome</home>       <remote>com.bfg.ejb.customer.Address</remote>       <ejb-class>com.bfg.ejb.customer.AddressBean</ejb-class>       <persistence-type>Container</persistence-type>       <prim-key-class>java.lang.Integer</prim-key-class>       <reentrant>False</reentrant>       <cmp-field>         <field-name>addressid</field-name>       </cmp-field>       <cmp-field>         <field-name>firstname</field-name>       </cmp-field>       <cmp-field>         <field-name>lastname</field-name>       </cmp-field>       <cmp-field>         <field-name>street1</field-name>       </cmp-field>       <cmp-field>         <field-name>street2</field-name>       </cmp-field>       <cmp-field>         <field-name>city</field-name>       </cmp-field>       <cmp-field>         <field-name>state</field-name>       </cmp-field>       <cmp-field>         <field-name>postalcode</field-name>       </cmp-field>       <primkey-field>addressid</primkey-field>     </entity>   </enterprise-beans> 

This section (shown in Listing 17.23) follows the end of the enterprise bean section and is used to assign security and transaction control to the methods of the beans defined previously. Entity beans are required to declare the transaction control for certain methods ”for yours, you can just require transaction control for all the methods. In a more advanced application, transaction control could be used to make sure that all parts of an operation are complete for the transaction to be recorded.

Listing 17.23 More Additions to ejb-jar.xml

 <assembly-descriptor>     <container-transaction>       <method>         <ejb-name>Customer</ejb-name>         <method-name>*</method-name>       </method>       <method>         <ejb-name>Address</ejb-name>         <method-name>*</method-name>       </method>       <trans-attribute>Required</trans-attribute>     </container-transaction>   </assembly-descriptor> 

Next, you have to set up the JNDI mappings in jboss.xml (see Listing 17.24).

Listing 17.24 Additions to jboss.xml

 <entity>       <ejb-name>Customer</ejb-name>       <jndi-name>bfg/Customer</jndi-name>     </entity>     <entity>       <ejb-name>Address</ejb-name>       <jndi-name>bfg/Address</jndi-name>     </entity> 

The additions to jboss.xml look the same as the other entries you made, except that you use an entity tag rather than a session tag.

Finally (on the JBoss side), you need to tell JAWS how to map between the database tables and the variables in the classes. This is done with a new file called jaws.xml, which is also bundled in the .jar file under the META-INF directory (see Listing 17.25).

Listing 17.25 jaws.xml

 <jaws>       <enterprise-beans>                        <ejb-name>Customer</ejb-name>                        <table-name>CUSTOMER</table-name>               <entity>                        <create-table>false</create-table>                        <cmp-field>                                <field-name>id</field-name>                                <column-name>CUSTOMER_ID</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>email</field-name>                                <column-name>EMAIL_ADDRESS</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>password</field-name>                                <column-name>PASSWORD</column-name>                        </cmp-field>                </entity>                <entity>                        <ejb-name>Address</ejb-name>                        <table-name>ADDRESS</table-name>                        <create-table>false</create-table>                        <cmp-field>                                <field-name>addressid</field-name>                                <column-name>ADDRESS_ID</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>firstname</field-name>                                <column-name>FIRST_NAME</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>lastname</field-name>                                <column-name>LAST_NAME</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>street1</field-name>                                <column-name>STREET_1</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>street2</field-name>                                <column-name>STREET_2</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>city</field-name>                                <column-name>CITY</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>state</field-name>                                <column-name>STATE</column-name>                        </cmp-field>                        <cmp-field>                                <field-name>postalcode</field-name>                                <column-name>POSTAL_CODE</column-name>                        </cmp-field>                          <finder>                                <name>findByAddressBookOwner</name>                               <query>,ADDRESS_BOOK                               WHERE ADDRESS_BOOK.CUSTOMER_KEY={0}  AND                               ADDRESS_BOOK.ADDRESS_KEY=ADDRESS.ADDRESS_ID                               </query>                               <order></order>                          </finder>              </entity>      </enterprise-beans> <debug>True</debug> </jaws> 

Each entity begins with a matchup against the ejb-name that you declared in ejb-tar.xml. Then you need to tell JAWS which table the query will be run against.

If the table doesn't exist in the database and the create-table parameter is set to true , JAWS automatically creates it, which is not something that you want in this case.

The cmp-field entries map between individual fields and the variable that hold them in the class.

The entries for Address are the same until the very end.

Because you're defining a customer findBy that joins against another table, you need to tell JAWS what SQL statements to use. You can figure out what to put in the query field by imagining that JAWS will place the text SELECT <TABLE>.* FROM <TABLE> directly in front of the SQL that you place there. That's why it starts with a "," ”because you need to separate the second table from the first.

The debug directive causes the SQL statements generated by JAWS to appear in the JBoss log, which is useful when you are first developing your code and you want to make sure that the SQL statements generated by JAWS are correct.

OH, NO! CAN'T CREATE!

Unfortunately, you can't use the JBoss/JAWS create methods for your nice new EJB classes. Why? JAWS doesn't know how to deal with serial or auto-increment fields. That means that when you do the insert, JAWS includes the ID value, which will be 0, in the insert. This means that you'll always be inserting 0 into the table.

According to the JBoss support forum, lots of workarounds exist, but most of them are uglier than doing it without EJB at all ”and some are downright unlikely to be bulletproof at all.

Until the JBoss folks work this out, it will seriously limit the practical use of the JAWS interface for CMP implementations . You can always go ahead and do a BMP implementation instead, but it's a lot more work.

To test the example, cook up another JSP file that puts your new classes through their paces (see Listing 17.25).

Listing 17.26 TestCustomer.jsp

 <%@ page import="javax.naming.*" %> <%@ page import="javax.naming.directory.*" %> <%@ page import="java.util.Hashtable" %> <%@ page import="java.util.Collection" %> <%@ page import="java.util.Iterator" %> <%@ page import="com.bfg.ejb.customer.CustomerHome" %> <%@ page import="com.bfg.ejb.customer.Customer" %> <%@ page import="com.bfg.ejb.customer.AddressHome" %> <%@ page import="com.bfg.ejb.customer.Address" %> <%@ page import="javax.rmi.PortableRemoteObject" %> <% Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY,      "org.jnp.interfaces.NamingContextFactory"); env.put(Context.PROVIDER_URL,      "jnp://localhost:1099"); env.put(Context.URL_PKG_PREFIXES,      "org.jboss.naming:org.jnp.interfaces"); DirContext ctx = new InitialDirContext(env); Object custref = ctx.lookup("bfg/Customer"); Object addrref = ctx.lookup("bfg/Address"); CustomerHome custhome =      (CustomerHome) PortableRemoteObject.narrow(custref, CustomerHome.class); AddressHome addrhome =      (AddressHome) PortableRemoteObject.narrow(addrref, AddressHome.class); Customer cust = custhome.findByPrimaryKey("[e-mail]"); Address addr = addrhome.findByPrimaryKey(new Integer(1)); %> Found Customer!<BR> ID = <%= cust.getCustomerId() %><BR> email = <%= cust.getEmail() %><P> Found Address!<BR> <%= addr.getFirstName() %> <%= addr.getLastName() %> <BR> <%= addr.getStreet1() %><BR> <%= addr.getStreet2() %><BR> <%= addr.getCity() %> <%= addr.getState() %> <%= addr.getPostalCode() %><P> <TABLE><TR><TD>ID</TD></TR> <% Collection coll = custhome.findAll(); Iterator it = coll.iterator(); while (it.hasNext()) {     cust = (Customer) it.next(); %> <TR><TD><%= cust.getCustomerId() %></TR><TD><%= cust.getEmail() %></TD></TR> <% }  %> </TABLE> <% coll = addrhome.findByAddressBookOwner(new Integer(cust.getCustomerId())); it = coll.iterator(); while (it.hasNext()) {     addr = (Address) it.next(); %><HR> Wallet Entry <%= addr.getAddressId() %><BR> <%= addr.getFirstName() %> <%= addr.getLastName() %> <BR> <%= addr.getStreet1() %><BR> <%= addr.getStreet2() %><BR> <%= addr.getCity() %> <%= addr.getState() %> <%= addr.getPostalCode() %><P> <% }  %> 

Even though you're using only one EJB server, you need to look up two different contexts because you're using two different entity beans.

First try looking up customers and addresses by their primary keys. That worked, so use the findAll method on the customer bean to get a list of all your customers in a table. Finally, get the address book for the last customer and display the addresses. The results can be seen in Figure 17.3.

Figure 17.3. Entity beans on parade.

I l @ ve RuBoard