ost software applications require a persistence layer, such as a relational database, to store user data for later retrieval. As a Java developer, you have many choices for your application's persistence layer. You can use entity beans with either container-managed persistence (CMP) or bean-managed persistence (BMP), or you can execute SQL queries from within your Java code through the Java Database Connectivity (JDBC) API.

Typically, those not familiar enough with SQL will choose CMP entity beans or frameworks such as TopLink and Hibernate, which generate queries and help to isolate them from the Java code. Regardless of which you choose, the resulting code will still contain Java-based queries. This article advises against this approach and proposes an alternative form of persistence that uses database stored procedures.

Using stored procedures to encapsulate data manipulation helps to solve the following problems often found when this logic—the associated queries—exists within the Java layer:

• Client/server data shuttling: When queries are made from Java code, potentially large amounts of data are shuttled back and forth between the application server and the database. This can greatly impact performance.
• Transactions opened from outside the database: A transaction is often created for each query executed from the Java code within an application server. When a single logical operation requires multiple queries, many individual transactions may be created.
• Tight coupling of database schema and Java code: The database structure is exposed to the Java layer. Hence, future changes to database structure may require changes to the Java code. As with Java class structure and member data, the database structure should be abstracted from other layers in the software.
• Software release coordination: Because the Java code depends on the database structure, database releases that contain schema changes must be coordinated with releases of the dependant Java code. It's often difficult to coordinate such releases. Furthermore, if one system must be rolled-back due to a problem, dependant systems must be rolled back also.

The Proposed Solution

The use of stored procedures helps to solve the problems listed above. For operations that require the lookup and/or update of data across multiple tables, the resulting queries are placed in a stored procedure that encapsulates the details. The advantage of this design is that it places the data-related business logic (the knowledge of where data resides, and how to manipulate it) where it belongs: in the database. The design goal for every application should be to place as much of the business logic into the database layer as stored procedures and limit the amount of Java code in use (see Figure 1). The proposed architecture moves the business logic further into the center of the diagram, out of the Java layer, and into the database layer.

Figure 1. Move Business Logic into the Database Layer as Stored Procedures

As a Java developer, this architecture may alarm you as it appears to reduce your development role. In my experience, three reasons explain why this is not the case:

1. Many Java developers write their own queries anyway, and this architecture simply moves them into stored procedures.
2. You'll always have more work to do in the Java layer than time and resources allow. Handing off the SQL work to a database team takes pressure off the Java developer.
3. The gain in performance and scalability this architecture brings helps reduce the likelihood that a project will fail.

Let's explore in more detail why this architecture is better.