- A One-to-Many Unidirectional Collection
- Testing the Bidirectional Department-Person Relationship
- Updating JPA Models / Stored Procedures
- Running the Code / Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Updating JPA Models
You can see from Part 1 in the article series that creating a basic JPA model isn't too difficult. The important thing is to understand the underlying data model. If I can offer any advice in this area, it's to not start coding too soon. Spend as much time as possible in understanding the needs of the data model.
I've seen cases where a data model was not fully understood and premature recourse was made to tools to auto-generate the data model. While this seems to represent a step in the right direction, the end result can be an overly complex model. This model will follow you throughout the project and beyond!
If you start coding up the JPA model too soon (before fully understanding your data needs), you may end up with an unnecessarily complex model. This complexity becomes increasingly expensive as you start to add the software layers around the data model.
So, as far as is practicable, take your time designing your data model! This time is a good investment, and will ultimately help you in building a clear and simple solution to your business problem.
Let's leave the client JPA side of things and now look at an interesting server-side database technology: stored procedures.
Stored Procedures
A stored procedure is a piece of SQL code that is stored in the database catalog. Stored procedures can be invoked by a program, a trigger, or another stored procedure. There are a number of reasons for using stored procedures:
- Security
- Speed
- Simple interface
- Client code simplification
- Business logic platform
I'll discuss these attributes after we take a look at a few examples of MySQL stored procedures. To get started, let's see the workflows for stored procedure development.
Creating a MySQL Stored Procedure
It's easy to create a stored procedure. Just open the MySQL command console as illustrated in Listing 7 and type in the following (substituting your configured password):
mysql -u root -p ENTER YOUR PASSWORD HERE
Listing 7—Creating Your First Stored Procedure
mysql> use quickstart; mysql> DELIMITER // CREATE PROCEDURE exampleStoredProcedure() BEGIN SELECT Avg(DEPARTMENT_DEPT_ID) AS averagedeptid FROM PERSON; END // DELIMITER ;
If you see no messages other than:
Query OK, 0 rows affected (0.00 sec)
then your procedure has been successfully installed. To call the stored procedure, just type in the commands in Listing 8.
Listing 8—Invoking the Stored Procedure
call exampleStoredProcedure(); +--------------+ | priceaverage | +--------------+ | 1.3333 | +--------------+ 1 row in set (0.00 sec)
Notice in Listing 8 that the result of the stored procedure computation is 1.3333. It's not hard to figure out where this value comes from. The procedure has simply added up and averaged the values of the individual DEPARTMENT_DEPT_ID columns from the PERSON table—i.e., 1, 1, and 2 respectively. Adding these gives 4 and dividing that by 3 yields 1.3333: the result in Listing 8.
To remove a stored procedure from the server, just type the following command:
DROP PROCEDURE exampleStoredProcedure;
This command deletes the stored procedure and is useful during development if you make a mistake in the code. As you can see, the syntax is similar to that for dropping a table or indeed an entire database.
So, you now know how to create, invoke, and delete a stored procedure. What about using parameters?
Stored Procedures with Output Parameters
Listing 9 illustrates a stored procedure that returns a single parameter called pl.
Listing 9—A Stored Procedure That Returns a Parameter
DELIMITER //
mysql> CREATE PROCEDURE exampleStoredProcedure(
-> OUT pl DECIMAL(8,2)
-> )
-> BEGIN
-> SELECT Avg(DEPARTMENT_DEPT_ID)
-> INTO pl
-> FROM PERSON;
-> END //
Invoking the stored procedure is again straightforward with the following commands in Listing 10:
Listing 10—Retrieving a Stored Procedure Output Parameter
mysql> call exampleStoredProcedure(@avgidvalue); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select @avgidvalue; +-------------+ | @avgidvalue | +-------------+ | 1.33 | +-------------+ 1 row in set (0.00 sec)
In Listing 10, I invoke the stored procedure by passing in the parameter avgidvalue. The stored procedure executes, and I then simply select the value of the parameter, which is of course our old friend 1.3333.
Summing Up Stored Procedures
From the previous examples of stored procedures, you can see that they are relatively simple to write. The heavy lifting is done on the server side, which helps to reduce the client side complexity. Clearly, if the interface between the stored procedure caller and the server is secure, then this is also a secure way of executing server-side code.
It's a short jump from this last statement to realize that stored procedures are potentially quite a secure mechanism. The bulk of your business logic can be designed to reside in stored procedures rather than in client code.
So, stored procedures provide a convenient mechanism for dividing client code and the underlying business logic. For these reasons, it's not hard to see why stored procedures are a popular technology with many financial and telecoms organizations.
Some disadvantages to stored procedures are that they are perhaps a little hard to write. Also, their use necessarily ties your code to a specific database engine—e.g., in this case, MySQL or if you use PL/SQL, then that ties you to Oracle database products. Many organizations have invested heavily in writing stored procedure code for closed-source products. With the trend for using open-source products, it can be very costly indeed to migrate stored procedure code from closed-source to open-source products.
However, the major merit of stored procedures (in my opinion) is a substantial simplification in client code complexity. This makes stored procedures at least worthy of consideration before embarking on a very complex JPA approach.