Stored Programs and J2EE Applications

While it is certainly possible to use JDBC inside Java to construct client/server applications or even Java applets, the most significant interaction between Java programs and a relational database often occurs with a J2EE application server environment, usually within the context of a J2EE-based web application. This application server could be a commercial J2EE implementation such as WebLogic or WebSphere orperhaps more typically in combination with MySQLan open source J2EE server such as Tomcat or JBoss.

Modern J2EE applications follow one of two major patterns with respect to database interaction:

 

Servlet pattern

In the servlet pattern, JDBC code is included within Java programs running within the application server. These programs are known as servlets . These servlets are free to communicate directly with the database through embedded JDBC code, although many applications will choose to interact with the database through an object-relational mapping interface such as Hibernate.

 

EJB pattern

In an Enterprise JavaBeans (EJB) based application, access to database objects is abstracted via entity EJB beans. Each entity bean represents either a table or a common multitable entity, and each instance of the entity bean typically represents a row in that table or result set. The EJB pattern contains methods to retrieve, update, delete, and insert rows within this logical table.

A full tutorial on J2EE database programming is beyond the scope of this book (and probably beyond the expertise of its authors). However, in this section we will take a quick look at how you might use stored programs within a J2EE application.

14.3.1. Using Stored Programs Within Java Servlets

In a servlet-based Java web application, Java code in the application or web server controls the generation of dynamic HTML content based on business logic contained within the Java code and through interaction with back-end databases via JDBC. Servlet technology actually predates J2EE (servlets were introduced in Java 1.1), and there is a wide variety of possible servlet implementation patterns.

In this section, we will use a simple servlet to render the output from a stored procedure that contains multiple and unpredictable result sets and that also contains both input and output parameters. The stored procedure generates a selection of MySQL server status information, takes as an input parameter a specific database within the server, and returns as an output parameter the MySQL version identifier. The stored procedure is shown in Example 14-23.

Example 14-23. Stored procedure to return MySQL server status information

CREATE PROCEDURE sp_mysql_info
 (in_database VARCHAR(60),
 OUT server_version VARCHAR(100))
 READS SQL DATA
BEGIN

 DECLARE db_count INT;

 SELECT @@version
 INTO server_version;

 SELECT 'Current processes active in server' as table_header;
 SHOW full processlist;

 SELECT 'Databases in server' as table_header;

 show databases;

 SELECT 'Configuration variables set in server' as table_header;
 SHOW global variables;
 SELECT 'Status variables in server' as table_header;
 SHOW global status;

 /* See if there is a matching database */
 SELECT COUNT(*)
 INTO db_count
 FROM information_schema.schemata s
 WHERE schema_name=in_database;
 IF (db_count=1) THEN
 SELECT CONCAT('Tables in database ',in_database) as table_header;
 SELECT table_name
 FROM information_schema.tables
 WHERE table_schema=in_database;
 END IF;

END;

Note that the stored procedure uses a special technique to output "heading" rows for the result sets. When a single row is returned with a column named table_header, that row represents a title or heading for the subsequent result set.

Our example is going to use an HTML page to request the user to enter specific server information, and then use a servlet within the application server to display the output of the stored procedure. The HTML for the input form is very simple and is shown in Example 14-24.

Example 14-24. HTML input form for our servlet example

MySQL Server status

Enter MySQL Server details

Host:
Port:
Username:
Password:
Database:

 

 

The HTML renders the data entry screen shown in Figure 14-3.

Example 14-25 shows the code for the Java servlet that is invoked when the user clicks the Submit button.

Figure 14-3. Data entry form for our servlet example

 

Example 14-25. Servlet code that invokes our stored procedure

MySQL Server status"); 43 out.println("

MySQL Server status and statistics

"); 44 out.println("Server: " + hostname + "
"); 45 out.println("Port: " + port + "
"); 46 out.println("Version:: " + version + "
"); 47 out.println(html.toString( )); 48 out.println("

1 public class StatusServlet extends HttpServlet
2 {
3 public void doPost(HttpServletRequest request, HttpServletResponse response)
4 throws ServletException, IOException
5 {
6 String hostname = request.getParameter("mhost");
7 String port = request.getParameter("mport");
8 String username = request.getParameter("muser");
9 String password = request.getParameter("mpass");
10 String database = request.getParameter("mdb");
11 StringBuffer html = new StringBuffer( );
12
13 response.setContentType("text/html");
14 PrintWriter out = response.getWriter( );
15
16 try {
17 Class.forName("com.mysql.jdbc.Driver").newInstance( );
18 String connString = "jdbc:mysql://" + hostname + ":" + port + "/" +
19 database + "?user=" + username + "&password=" + password;
20 Connection connection = DriverManager.getConnection(connString);
21
22 CallableStatement myproc =
23 connection.prepareCall("{CALL sp_mysql_info(?,?)}");
24 myproc.registerOutParameter(2, Types.VARCHAR);
25 myproc.setString(1, database);
26
27 boolean moreResultSets = myproc.execute( );
28 while (moreResultSets) {
29 ResultSet rs = myproc.getResultSet( );
30 ResultSetMetaData rsmd = rs.getMetaData( );
31 if (rsmd.getColumnName(1).equals("table_header")) {
32 rs.next( );
33 html.append("

").append(rs.getString(1)) 34 .append("

"); 35 } else { 36 makeTable(rs, rsmd, html); 37 } 38 moreResultSets = myproc.getMoreResults( ); 39 } 40 String version = myproc.getString(2); 41 42 out.println("

"); 49 } catch (SQLException e) { 50 out.println(e.getErrorCode() + " " + e.getMessage( )); 51 e.printStackTrace(out); 52 } catch (InstantiationException e) { 53 e.printStackTrace(out); 54 } catch (IllegalAccessException e) { 55 e.printStackTrace(out); 56 } catch (ClassNotFoundException e) { 57 e.printStackTrace(out); 58 } finally { 59 out.flush( ); 60 out.close( ); 61 } 62 } 63 64 private void makeTable(ResultSet rs, ResultSetMetaData rsmd, StringBuffer html) 65 throws SQLException 66 { 67 html.append(""); 68 69 for (int i = 1; i <= rsmd.getColumnCount( ); i++) 70 html.append(""); 72 html.append(""); 73 74 while (rs.next( )) { 75 html.append(""); 76 for (int i = 1; i <= rsmd.getColumnCount( ); i++) 77 html.append(""); 78 html.append(" "); 79 } 80 81 html.append("

").append(rsmd.getColumnName(i)) 71 .append("

").append(rs.getString(i)).append("

"); 82 } 83 }

Let's examine this servlet code:

Line(s)	Explanation
6-10	Retrieve the server connection details as entered by the user on the calling HTML form.
11	Create a StringBuffer object for building the HTML text to avoid churning lots of throwaway String objects.
13 and 14	Initialize an output stream to return HTML output.
17-20	Create a connection to the MySQL server using the connection details supplied by the user.
22-25	Prepare the stored procedure shown in Example 14-23. On line 24 we register our output parameter, and on line 25 we supply the input parameterthe name of a database within the serverprovided by the user in the HTML form.
27	Execute the stored procedure.
28-39	This loop executes once for each result set returned by the stored procedure.
29 and 30	Retrieve a result set andon line 30a ResultSetMetaData object for that result set.
31-37	If the first column in the result set is called "table_header", then the result set represents a heading row for a subsequent result set, so we create an HTML header tag. Otherwise, we pass the result set to the makeTable( ) method, which returns an HTML table formatted from the result set (see below for a description of the makeTable( ) method).
37	Call the getMoreResults( ) method to see if there are further result sets. If there are, then moreResultSets will be set to true and the loop will continue. Otherwise, it will be set to false and the loop will terminate.
40	Now that all result sets have been processed, retrieve the value of the output parameter, which contains the MySQL version string.
42-48	Write our formatted HTML report to the print stream.
49-57	Catch any exceptions and print a stack trace to the print stream.
58-61	Whether there is an exception or not, we must flush and close the print stream to send our output back to the calling session.
64-82	Define the private makeTable( ) method that takes ResultSet and ResultSetMetaData objects and appends an HTML table representation of that result set to the specified StringBuffer.
69-72	Loop through the column names for the result set and format HTML to create the heading row for the table.
74-79	Loop through the rows returned by the result set andin lines 76-77append the columns in each row. We generate HTML to create an HTML table cell for each row returned in the result set.

 

Figure 14-4 shows the output generated by the servlet and stored procedure.

14.3.2. Using Stored Programs from EJB

Enterprise JavaBeans (EJB) is a feature of the J2EE specification that provides for distributed server-side Java components intended for enterprise systems development. Entity EJBs provide a way to represent persistent datausually data from an RDBMSin the EJB component model.

Figure 14-4. Output from our stored procedure/servlet example

 

In most J2EE applications, EJBs represent a mapping of relational data to Java objects. In a very simple case, an EJB may represent a database table, and each instance of the EJB might represent a row in that table. However, the relationships between EJBs and relational tables can be as complex as the developer chooses, and an EJB may represent a complex business object that is represented across many database tables.

Each EJB includes various methods that allow the application to interact with the underlying data. Some of these methods are listed in Table 14-1.

Table 14-1. Some of the methods of an entity EJB

Method or method type	Description
ejbFind find_type	Various "finder" methods allow the application to find a particular instance of an EJB (perhaps a specific row in a table). There will always be at least an ejbFindByPrimaryKey() method.
ejbCreate	Creates a new instance of an entity bean. This is roughly equivalent to inserting a row into the database.
ejbStore	Applies the in-memory contents of the entity bean to the database. It usually involves one or more UPDATE statements.
ejbRemove	Permanently removes an instance of an entity beanusually associated with deleting one or more database rows.
ejbLoad	Loads a particular instance of an EJB. This is equivalent to reading a certain table row into memory.

 

Entity EJBs in a J2EE application are responsible for representing all persistent data in the application, where persistent means that the data will continue to exist when the current thread, process, or application ceases to run. There are two styles of persistence management in entity EJBs:

 

Bean-Managed Persistence (BMP)

In this mode, the interaction with the underlying data source is controlled by code that is contained within the EJB. In most cases, this means that the programmer includes JDBC code within the bean to query and update the underlying tables, or uses an abstraction layer such as Hibernate or Spring to generate the JDBC calls.

 

Container-Managed Persistence (CMP)

In this mode, the interaction with the underlying data source is controlled by the EJB container itself. The container generates SQL to retrieve and maintain data based on deployment data that defines the relationship between the data represented by the entity bean and the data held in the relational database.

In CMP, the SQL is issued by the EJB container itself and is not under developer control. Consequently it is not really feasible to use stored programs in conjunction with a CMP EJB. It's fair to say that CMP is the recommended method of implementing entity bean persistence, since it reduces the effort involved in implementing the bean and since (somewhat surprisingly) CMP implementations can outperform BMP implementations. Most J2EE experts recommend using BMP only when there is a very complex relationship between beans and the underlying tables or when some special SQL coding is required for performance or security reasons.

Note also that the J2EE specification does not forbid accessing the database from session beans, and the programmer is free to implement JDBC within a session bean framework in order to retrieve and maintain persistent data. In this model, JDBC calls would be embedded in the session bean much as we embedded JDBC within a Java servlet in the "Using Stored Programs Within Java Servlets" section earlier in this chapter.

However, in the case in which our database logic is contained in a BMP-based entity bean, we can certainly use a stored program implementation if we choose.

For instance, Example 14-26 shows a typical EJB method that we might use to locate an EJB representing a particular customer using the customer's phone number. The bean method accepts the phone number and returns the primary key of the relevant customer (the customer_id). This customer_id would later be used by the ejbLoad() method to load the relevant bean.

Example 14-26. EJB method to find a customer by phone number

public int ejbFindByPhoneNo(String phoneNo) throws FinderException
{
 try {
 Connection connection = getConnection( );
 PreparedStatement statement = connection.prepareStatement
 ("SELECT customer_id FROM customers WHERE phoneno=?");
 statement.setString(1, phoneNo);
 ResultSet resultSet = statement.executeQuery( );
 if (!resultSet.next( ))
 {
 statement.close( );
 connection.close( );
 throw new FinderException("Could not find: " + phoneNo);
 }
 statement.close( );
 connection.close( );
 return resultSet.getInt(1);
 }
 catch(SQLException e) {
 throw new EJBException ("Could not find: " + phoneNo, e);
 }
}

The SQL within a BMP entity bean can be implemented as a stored program. Example 14-27 shows such a finder method. The finder method calls the stored procedure GetCustomerIdByPhoneno, which returns a customer_id that matches a particular customer name.

Example 14-27. EJB finder method that uses a stored procedure

public int ejbFindByPhoneNoSP(String phoneNo) throws FinderException
{
 try {
 Connection connection = getConnection( );
 String sqlText = "{call getcustomeridbyphoneno(?,?,?)}";

 CallableStatement custStmt = connection.prepareCall(sqlText);
 custStmt.registerOutParameter(2, Types.INTEGER);
 custStmt.registerOutParameter(3, Types.INTEGER);

 custStmt.setString(1, phoneNo);
 custStmt.execute( );
 if (custStmt.getInt(3) == 1) // Not Found indicator
 throw new FinderException("Could not find: " + phoneNo);

 return custStmt.getInt(2);
 }
 catch(SQLException e) {
 throw new EJBException("Could not find: " + phoneNo, e);
 }
}