The JVM Fanboy Blog 

Entries tagged [stored-procedures]

Apache Derby part 3: Stored Procedures

by vincent

Posted on Tuesday Aug 04, 2015 at 12:54AM in Relational Database

In the previous part, we created a small database to store movie titles and the media type. In this part we will create and load a Stored Procedure into that database to make it really easy to add movies using the "ij" command-line tool with one simple statement.

First a warning. Most dedicated DBA's will probably hate you when you ask them to enable Java stored procedures support in their database. I don't think it's really considered common practice, but many enterprise-grade database systems offer stored procedures written in Java. I can think of many reasons not to do this, but there are probably cases where it can be a good solution. Since Derby is a database management system implemented fully in Java anyway, it's make a bit more sense here. There's a nice article on the official Oracle Nashorn team blog about this subject.

We will create a simple stored procedure that can be used to easily add a movie using a single CALL AddMovie('Movie Title', 'Media Name') statement. What it will do is simply look up the media record by name, then create the specified movie record. So it saves you from looking up the media id manually or using a subquery to do it with one query. I will never recommend to use stored procedures like this in an application (in this case for performance reasons, applications should look up the id of the media once and cache that), but if I'd plan to add many records manually using SQL and a command-line tool like "ij", then I'd seriously consider adding a utility stored procedure like this for convenience. That is, if the table contained more fields :)

Read the long full article for all sample code

Read More