Reduced Coupling of Business Logic and Data
The amount of data that is shuffled between the Java layer and the database is reduced because the stored procedure returns only the results of the operation, not the data from other tables used to produce the results. Since all of the queries reside within the stored procedure, the entire operation is wrapped in one transaction within the database. This eliminates the individual transactions that would otherwise have been created from the application server. Additionally, since the Java code no longer makes queries to the database directly, the database schema is not exposed.
Calling a stored procedure is very much like a call to a Java class: parameters are passed to a named function, and results are produced. As long as the function signature remains the same, changes to the internal format of the data (the schema) will not require changes to the Java code. Developing loosely coupled software components should be one of your goals.
Let's look at a code example to help prove these two points. Figure 2 contains a summary of a design that places SQL queries within the Java layer to perform a user login function. Let's assume that this is a Web-based application in which the following steps occur:
- Perform a SQL query to locate the user within the USER_DATA table, and retrieve the user's data (username, password, login count, and max logins).
- Check that the password entered matches the password from the database
- If the password matches, perform a query on the USER_LOGIN table to see if the user is already logged in.
- If a row is not found, it means that the user is not logged in. Go to step 3.
- If a row is found, compare the user's cookie to the cookie returned from the query. If it matches, it means that the user is already logged in, and can proceed to use the system – you are done.
- If the cookies don't match, it means that the same username is being used on another computer. In this case, you must check that the user login count has not been exceeded (a system may limit a username to one login, multiple logins, or limitless logins). If the count is not exceeded, proceed to the next step. Otherwise, fail the login.
- Insert a row into the USER_LOGIN table with the username, cookie value, and a timestamp.
- Update the USER_DATA table to increment the user's login count.
|Figure 2. SQL Queries Within the Java Layer to Perform a User Login|
The code in Listing 1 implements this design, and it contains all of the Java code and SQL queries needed to perform the login. In total, four calls are made to the database, and in all cases data is shuttled back and forth. Two of the calls execute SELECT statements; one call executes an INSERT; and the final call executes an UPDATE. In many cases, each call will start an internal database transactionnever mind the transaction the Java code must create to ensure that the four queries execute as one atomic operation.
Problems arise when you want to change your application's business logic, which may result in changes to both the database and the Java code. Additionally, since database table and column names appear throughout the Java code, changes to the database schema may also require changes to the Java code. While tools such as Hibernate may appear to insulate you from the database schema, they usually don't solve the data shuttling or multi-transaction problems. Additionally, these tools add a layer of complexity to your software, requiring an added step in the build sequence to generate the appropriate code.
Moving the logic behind user login into a stored procedure solves these problems. The stored procedure in Listing 2 performs all of the processing required for user login.
The username and password are checked; the user count is checked; an entry is placed into the USER_LOGIN table; and finally, the user login count is incremented. All of these steps are completed within a single transaction started from within the database. All of the data shuttling from the previous design has been eliminated, as well as the time it took to create the additional transactions. The result is an increase in performance, and an even greater increase in scalability. Experience with this design has shown that the removal of the data shuttling and extraneous transactions increases the number of concurrent user operations the software can handle.
Listing 3 shows how much simpler the Java code has become.
The calls to the stored procedures are made just like other method calls, albeit with different syntax. The Java code simply specifies the stored procedure name and the parameters. None of the internal database details such as tables, columns, primary keys, joins, and so on, are exposed. If a change is made to the database schema, or even to the business logic, the Java layer will not be impacted.
Additional benefits to data hiding through stored procedures include the independence of release management. As long as stored procedure signatures remain the same, releases to the database systemto optimize code or fix a bugcan be made without requiring a release of the Java code, and vice-versa.