Accessing JDBC from EJBs

We have already learned a little about the EJB’s environment.  We saw how linked EJB’s are available at JNDI names starting with the string java:comp/env/
Other resources are similarly available.  A very useful such resource is JDBC.  Of course, you can access JDBC as you have always been doing.  But EJB offers a standard method of accessing JDBC.  Using this method has several advantages.  It allows the EJB container to add connection pooling automatically to your JDBC access.  Furthermore, it allows you to add several EJBs within a “transaction”, as we will see in the next tutorial.
In creating an entity bean, we added a JDBC “data source” to the ejb initialization file.  This JDBC data source will automatically be available to all EJBs at the JNDI name java:comp/env/jdbc/propsDataSource

(The actual naming is EJB container dependent, the above string will work for Blazix.  The “jdbc/” is only recommended by the EJB 1.1 standard, so it may or may not be a part of the JNDI name.)

What you get at this JNDI name is a object of type javax.sql.DataSource.  From this, you can call the method getConnection() or getConnection( username, password ) to get the familiar connection object of type java.sql.Connection.
Now what we will do is modify our stocks’ EJB to maintain a number of stocks of different types at different prices, and use a database to keep track of them.  We will use the stateful version of the EJB.

Create a database table “Stocks” with columns “StockName”, “StockPrice” and “NumberAvailable”.  You can use the same database you used for “propsDataSource”, or preferably use a different database “stocksDataSource” and configure it in the ejb.ini file.  Initialize this table with various numbers of stocks at various prices (the same stock can have multiple entries, at different prices.)

Add the following methods to the remote interface.

int getNumber() throws  java.rmi.RemoteException,
java.sql.SQLException,
javax.naming.NamingException;
boolean buy( float maxPrice, int maxAmount  )
throws java.rmi.RemoteException,
java.sql.SQLException,
javax.naming.NamingException;
int  getNumPurchased() throws java.rmi.RemoteException;
float getPurchasePrice()  throws java.rmi.RemoteException;

We already have a “getPrice” function, its remote interface signature needs to be modified to include java.sql.SQLException and javax.naming.NamingException in its “throws” clause.  We will be modifying its implementation to do a SELECT from the database, and return the MIN price.  The “getNumber” method will return the number of stocks available at the MIN price.  The buy method will be used to purchase a number of stocks at a given price or lower.  If not enough stocks are available, it will purchase as many as available.  The actual number of stocks purchased, and the actual purchase price, can be retrieved later via the getNumPurchased and getPurchasePrice methods (that’s why this bean has to be stateful!)
Try implementing these methods (don’t worry about transaction management issues, we will be covering that in the very next tutorial). The sample file assumes a data source called “stocksDataSource” (at JNDI name java:comp/env/jdbc/stocksDataSource) has been configured in the ejb.ini file and a table called Stocks as outlined above exists in the database.

Exercise:  Modify your accounts EJB also to use a database.  Write test programs for both the stocks and the accounts EJBs and test the EJBs.