Embedded SQL vs. Stored Procedures

I've been working on a small pet project lately that involves storing and retrieving data from a database. I have about 10 - 12 different queries that have been written to handle all of the data. Initially I began embedding all of the queries into my code and using the normal JDBC PreparedStatements to make all of the calls. Then I needed to make a few changes to data model and corresponding DB schema. I had to go back into each piece of code that contained an embedded SQL statement and make updates to account for my data changes. This was a serious pain in the @$$ as I spent a couple hours updating code due to an external change. It was then I realized that my application had become too coupled with the database schema with the embedded SQL. I decided it would be better to move my SQL queries into stored procedures and simply call the stored procedures from my code.

I learned the following lessons from this:

• If an application needs to execute more than a few queries it is better to have the app call a stored procedure rather than embed SQL. This way the application does not need to know any information about the database tables and can interact with a group of stored procedures that create a pseudo-interface to the database.
• By calling the stored procedures in the code, I was able to switch to different types of databases as long as I loaded the stored procedures. I was able to quickly test my application against SQL Server Express and MySQL with no problems.
• To make things even simpler in the code, I created constants for the stored procedures names. This allowed me to not have to keep track of the exact names of the stored procedures and gave me a central place to update stored procedure names to be used by my code.

By no means do I consider myself an expert, but I just thought I would share what I found as it made things much easier for me in the development of my app.