The TradeaccountbeanModel class consists of a beanInterface class member and Select(), Insert(), Delete(), and Update() methods.

The Select(), Insert(), Delete(), and Update() methods are described in the introductory article. You can add user-defined functions to the model helper if you like.

The beanInterface class member is a collection of bean references as a result of a query. This is intended to cache the bean references for further manipulation.

We'll describe the implementation of each SQL method in the upcoming sections.

Back to top

Insert method

A call to the bean creation method results in an insert to the underlying database. Consider Listing 2 for a typical implementation of insert.

Listing 2. A typical implementation of insert

 
public void Insert(String userid, Double balance, Integer transactions) 
	throws javax.ejb.CreateException
{
	TradeaccountbeanFactory factory = new TradeaccountbeanFactory();
	try
	{
		 Tradeaccountbean bean = factory.create(userid);
		 bean.setUserid(userid);
		 bean.setTransactions(transactions);
 
		 beanInterface.add( bean );
	}
	catch (java.rmi.RemoteException re)
	{
	}
}

EJB factory is an access bean that simplifies the creating or finding of an enterprise bean instance. In our example above, the TradeaccountbeanFactory is used to create an instance of Tradeaccountbean entity bean with the corresponding primary key. The default setter methods in the entity bean are used to initialize the container-managed persistent (CMP) fields userid and transactions of the bean. However, this implementation does not only result in one insert statement but also has the side effect of two additional update statements.

Consider Listing 3 for an example of more efficient implementation of insert.

Listing 3. A more efficient insert

 
public void Insert(String userid, Double balance, Integer transactions) 
	throws javax.ejb.CreateException 
{ 
	TradeaccountbeanFactory factory = new TradeaccountbeanFactory(); 
 
	try 
	{ 
		beanInterface.add(factory.create(userid, balance, transactions)); 
		System.out.println("Insert"); 
	} 
	catch (java.rmi.RemoteException re) 
	{ 
		throw new EJBException(re); 
	} 
} 

In this implementation, we use an EJB factory create method that passes in all the CMP fields in one shot. Only a single insert statement is generated as desired.

To be able to use the create method above, you must add the corresponding signature with all the CMP fields in the home class. See Listing 4 for an example.

Listing 4. Add an ejbCreate() to TradeaccountbeanBean.java

 public sample.TradeaccountbeanKey ejbCreate( 
	java.lang.String userid, 
	java.lang.Double balance, 
	java.lang.Integer transactions) 
	throws javax.ejb.CreateException 
{ 
	setUserid(userid); 
	setBalance(balance); 
	setTransactions(transactions); 
	return null; 
} 

Then you must promote this method to the home class as shown in Listing 5.

Listing 5. Add a create() to TradeaccountbeanHome.java

 
public sample.Tradeaccountbean create( 
	java.lang.String userid, 
	Double balance, 
	Integer transactions) 
	throws javax.ejb.CreateException, java.rmi.RemoteException; 

Back to top

Select method

The Select() described in the coding technique is a select filter template. It generates a frequently used SELECT-WHERE-AND query. Consider Listing 6 for an implementation of select.

Listing 6. A traditional implementation of select

public Vector Select(String userid, Double balance, Integer transactions) 
	throws javax.ejb.FinderException 
{ 
	Enumeration bean = null; 
	Vector queryResult = new Vector(); 
 
	try 
	{ 
		bean = findByWhereClause(userid, balance, transactions); 
		while ( e.hasMoreElements() ) 
{ 
			beanInterface.add(bean); 
			queryResult.add(bean.getTradeaccountbeanData()); 
		} 
       } 
	catch (java.rmi.RemoteException re) 
	{ 
	} 
	return queryResult; 
 
} 

One way to implement a select filter is to dynamically compose a JDBC statement based on the parameters passed in by the client. This result set will provides the actual data values. To obtain references to the beans, we can issue a findByPrimaryKey() default EJB finder for each individual bean. If you look at the source code provided, this is exactly what the findByWhereClause() method above is doing. This way, any call to the Select() method will result in N+1 selects to the database for each call, where N is the size of the result set.

For applications that we do not know the query search criteria (such as a select filter) until application runtime, we may want to use IBM Dynamic Query Services (DQS) [1]. See Listing 7 for a more efficient implementation of select leveraging the advantages of DQS.

Listing 7. A DQS implementation of select

 
public Vector Select(String userid, Double balance, Integer transactions) 
	throws javax.ejb.FinderException 
{ 
	String query = "SELECT OBJECT(a) FROM Tradeaccountbean a "; 
	Vector queryResult = new Vector(); 
	 
	//Generate the EJBQL query 
	try 
	{	 
		int count = 0; 
		Object[] parms = new Object[3];  
		 
		if (userid != null || balance != null || transactions != null) 
		{ 
			int markPos = 0; 
			 
			query = query + " WHERE "; 
			 
			if (userid != null) 
			{ 
				query += "a.userid = ?1"; 
				parms[count] = new String (userid); 
				count++; 
			} 
				 
			if (balance != null) 
			{ 
				if (count == 0) 
				{ 
					query += "a.balance = ?1"; 
				} 
				else 
				{ 
					markPos = count + 1; 
					query += " AND a.balance = ?" + markPos; 
				} 
					 
				parms[count] = balance; 
				count++; 
			} 
				 
			if (transactions != null) 
			{ 
				if (count == 0) 
				{ 
					query += "a.transactions = ?1"; 
				} 
				else 
				{ 
					markPos = count + 1; 
					query += " AND a.transactions = ?" + markPos; 
				} 
		 
				parms[count] = transactions; 
				count++; 
			} 
		} 
				 
		System.out.println("Query: " + query); 
			 
		Query qb = QueryBeanFactory.getQuery();		 
		QueryIterator it; 
			 
		Object[] newParms; 
			 
		if (count == 0) 
			newParms = null; 
		else 
		{ 
			newParms = new Object[count]; 
			for (int i = 0; i < count; i++) 
				newParms[i] = parms[i]; 
		} 
				 
		//Query Database 
		it = qb.executeQuery(query, newParms, null, 0, 1000); 
			 
		qb.remove(); 
			 
		//Get the query result			 
		while (it.hasNext()) 
		{ 
			IQueryTuple t = (IQueryTuple) it.next(); 
			Tradeaccountbean accountbean = (Tradeaccountbean)t.getObject(1); 
			beanInterface.add(accountbean); 
			queryResult.add(accountbean.getTradeAccountBeanData()); 
		} 
			 
		return queryResult; 
			 
	} 
	catch (Exception e) 
	{ 
		e.printStackTrace(); 
		throw new EJBException (e); 
	}						 
 
} 

The dynamic query API is actually a Query stateless session bean. Before we can execute a query, we have to obtain a reference to the Query session bean either through JNDI or factory. As shown in the code above, a Query bean is obtained from the QueryBeanFactory.

A dynamic query is passed in as a String and the input parameters are passed in the form of an array of java.lang.Object values. So, the query and the parameters can be dynamically composed based on the search criteria provided by the Select() method client.

Calling the next() method of the QueryIterator returns an instance of IQueryTuple, which contains the actual data values and object references. For our purpose, we store the object references for later use and returns the data classes from the object as the query result.

This implementation of Select() template will be translated to a single select statement to the underlying database. Very clearly, DQS provides high flexibility of implementing the Select() filter. For more information on DQS, please refer to the WebSphere InfoCenter [4].

Back to top

Delete method

The following is an implementation of a delete.

Listing 8. An implementation of delete

 
public void Delete() throws javax.ejb.RemoveException 
{ 
 
	System.out.println("Delete"); 
 
	try 
	{ 
		for (int i = 0; i < beanInterface.size(); i++) 
			 ((Tradeaccountbean) beanInterface.get(i)).remove(); 
	} 
	catch (java.rmi.RemoteException re) 
	{ 
		throw new EJBException(re); 
	} 
} 

Code the beans resulted from the previous select are removed by calling the remove() method in the bean interfaces one-by-one.

Back to top

Update method

In EJB programming model, update is relatively transparent to the programmer. Any change to the CMP fields will result in update statements to the database. Consider Listing 9 for a typical implementation of update.

Listing 9. A typical implementation of update

 
public void Update(String userid, Double balance, Integer transaction) 
{ 
	Tradeaccountbean b = null; 
	try 
	{ 
		for (int i = 0; i < beanInterface.size(); i++) 
		{ 
			b.setBalance(balance); 
			b.setTransactions(transaction);			 
		} 
	} 
	catch (java.rmi.RemoteException re) 
	{ 
	} 
} 

What we need to do is to call the default setter methods provided by WebSphere Studio. Note that each call to the setter is an individual update. In the example above, we will have two update statements, one for updating the balance and one for updating the transaction.

Consider Listing 10 for a more efficient implementation of update.

Listing 10. A more efficient implementation of update

 
public void Update(String userid, Double balance, Integer transaction) 
{ 
 
	Tradeaccountbean b = null; 
	TradeaccountbeanData d = null; 
	try 
	{ 
		for (int i = 0; i < beanInterface.size(); i++) 
		{ 
			// Get data class of each bean 
			b = ((Tradeaccountbean) beanInterface.get(i)); 
			d = b.getTradeaccountbeanData(); 
			// Update each data class 
			d.setBalance(balance); 
			d.setTransactions(transaction); 
			b.syncTradeaccountbeanData(d); 
		} 
 
	} 
	catch (java.rmi.RemoteException re) 
	{ 
		throw new EJBException(re); 
	} 
 
} 

A data class access bean provides the data storage and access methods for cacheable enterprise bean properties. The syncXXXData() method causes any changes you have made to the data class to be written back to the enterprise bean. In our example, we update the balance and transaction through the setter of the TradeaccountbeanData data class. The syncTradeaccountbeanData() method is called to synchronize the change. Only one update statement is issued.

Back to top

Conclusion

This article described a sample implementation of a SQL EJB Wrapper. The SQL wrapper supports the concept of model helper. The wrapper also works very well with Access Bean in WebSphere Studio. One important aspect of implementing wrappers is to ensure that the wrappers are leveraging best practices for EJB performance. In this article, we also go through the best practices in terms of database access.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents.

IBM certified Systems Expert

Back to top

Downloads

Information about download methods

Resources

• [ 1] Tim Francis, Eric Herness, et al, Professional WebSphere Application Server V5.0, WROX, 2003.
  
  
• [2] Tony Lau, "Using a Generic SQL EJB Wrapper to Simplify EJB Development," at http://www-106.ibm.com/developerworks/db2/library/techarticle/0306lau/0306lau.html
  
  
• [3] Tim Biernat, "Dynamic EJB Finders," at http://www-106.ibm.com/developerworks/websphere/techjournal/0111_biernat/biernat.html
  
  
• [ 4] WebSphere Application Server InfoCenter, http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp
  
  

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions