Moving from Stored Procedures to Hibernate, Spring & Java

Engineer: Ljubisa

This project relates to the task of switching to a combination of Hibernate, Spring and Java from the current use of stored procedures. Not only is Hibernate easier to work with, the current use of stored procedures ties Project.net to an Oracle database. This is the first step towards opening the door to an Open Source database alternative. Here are the global tasks for performing this migration. Of course, I'll create a more detailed plan in Project.net, with apropriate timelines for each task.

In our application there are 28 packages which contains 264 stored procedures; eliminating them will be a huge task. During my investigation, I migrated several stored procedures, however, I did not save those changes in the source tree because it was not tested well, and I did not take the time to see if there were any other calls to the procedure I moved from the database. All that can be seen in net.project.hibernate package, in the dao and service subpackages.

STEP 1

First of all, we'll have to see which of all those packages are indepentent, which means they don't call procedures/functions from other packages. As far as I can see, the BASE and SECURITY packages are called from almost every other package, so they have to be migrated first. Note, during the migration the orginal stored procedures will be still present in the database. Only after we have completed testing, can we remove those migrated database objects from database.

STEP 2

Procedure migration. I already explained in great detail how Hibernate+Spring should be used and that informations can be found on our wiki (http://dev.project.net/trac/pnet-community/wiki/tech-hibernate-DAO). If you follow that pattern it shouldn't be hard to migrate a stored procedure's logic into Java. It is very important to understand transactions in Spring+Hibernate and how they work because the logic of a stored procedures must be performed within one transaction. As I said, I have completed some examples.

STEP 3

The next step is to locate every place within Project.net where the stored procedure was called. If none exist, that means the stored procedure is only called by another stored procedure.

STEP 4

If the procedure is called from Project.net you will need to modify the code in the existing Java classes to call the new business logic. This is a cruical part and we must be extra careful because are we attaching new code to existing code; this part can be time consuming.

STEP 5

Testing, testing, testing - this is another crucical part. The first and basic way to test new logic is from the application. To do this, you need to know where the new logic is called from within the application; there must exist a test cast for every call to the new code. To help, the Project.net application has a good logging mechanism, which can be used to log all database calls. My sugesstion is to set log level to debug and then monitor the log file or console to see when the migrated stored procedure is called. Of course, the logs can also be used to help track down problems in the application.

I'll have to think about testing a little more to see if there is a better or easier way to test the logic besides from the application. There is one parametter in Hibernate (hibernate.show_sql) which shows all SQL queries when it is set to true. This can be used to see all queries performed in some step. We should also compare the same steps done with stored procedures and with our new logic to ensure the two results are identical.

There are a lot of unanswered questions here, but this is a start. Any comments/questions are welcome.