Java programmer's stored procedure

Java programmer's stored procedure
--------------------------------------------------------------------------------

Original article: http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
By Nic Ferrier

This article describes how to use DBMS stored procedures. I have explained the basic and advanced features of using stored procedures, such as returning resultset. This article assumes that you are familiar with DBMS and JDBC, and that you can read the code written in other languages without any obstacles (that is, not the Java language). However, you are not required to have any stored procedure programming experience.
Stored procedures are stored in the database and executed on the database. You can use special syntax to call stored procedures in Java classes. During the call, the name of the stored procedure and the specified parameters are sent to the DBMS through a JDBC connection, the stored procedure is executed, and the result is returned through a connection (if any.
Using Stored Procedures has the same benefits as using an application server based on EJB or CORBA. The difference is that stored procedures can be used free of charge from many popular DBMS, and most application servers are very expensive. This is not just about license fees. The management and coding costs required to use the application server and the complexity of the customer program can all be replaced by the stored procedure in the DBMS.
You can use Java, Python, Perl, or C to write stored procedures, but usually use the specific language specified by your DBMS. Oracle uses PL/SQL, PostgreSQL uses PL/pgsql, and DB2 uses procedural SQL. These languages are very similar. Porting session beans between them is not more difficult than porting session beans between different implementation versions of Sun's EJB specification. In addition, stored procedures are designed to embed SQL statements, which makes them more user-friendly to express the database mechanism than Java or C.
Because stored procedures run on DBMS itself, this can help reduce the waiting time in applications. Instead of executing four or five SQL statements in Java code, you only need to execute one stored procedure on the server side. The reduction in the number of round-trips on the network can dramatically optimize the performance.

Use stored procedures

Simple old JDBC supports calling stored procedures through the callablestatement class. This class is actually a subclass of preparedstatement. Suppose we have a poets database. The database has a stored procedure for setting the age of the poet's death. The following is an old drunk Dylan Thomas (old soak Dylan Thomas) who does not specify the story or culture. Please criticize and correct him. Detailed code for calling:

Try
{
Int age = 39;
String poetname = "Dylan Thomas ";
Callablestatement proc =
Connection. preparecall ("{call set_death_age (?, ?) }");
Proc. setstring (1, poetname );
Proc. setint (2, age );
Cs.exe cute ();
}
Catch (sqlexception E)
{
//....
}

The string passed to the preparecall method is the writing specification for the stored procedure call. It specifies the name of the stored procedure ,? Represents the parameter you need to specify.
Integration with JDBC is a great convenience for stored procedures: In order to call stored procedures from applications, you do not need to use the stubs or configuration files, you don't need anything except your DBMS's JDBC driver.
When this code is executed, the stored procedure of the database is called. We didn't get the result because the stored procedure does not return the result. If the execution succeeds or fails, you will be informed by exceptions. Failure may indicate a failure in calling the stored procedure (for example, the type of a provided parameter is incorrect ), or an application failure (for example, throwing an exception indicates that "Dylan Thomas" does not exist in the poets database ")

Combined with SQL operations and stored procedures

Ing rows from a Java object to an SQL table is quite simple, but you usually need to execute several SQL statements. It may be a select lookup ID, and then an insert inserts data with the specified ID. In a database mode that is highly normalized (in line with a higher paradigm, ), multiple tables may be updated, so more statements are required. Java code expands rapidly, and the network overhead of each statement increases rapidly.
Transferring these SQL statements to a stored procedure will greatly simplify the code and only involve one network call. All associated SQL operations can occur within the database. In addition, stored procedure languages, such as PL/SQL, allow the use of SQL syntax, which is more natural than Java code. The following is an early stored procedure written in Oracle's PL/SQL language:

Create procedure set_death_age (poet varchar2, poet_age number)
Poet_id number;
Begin
Select ID into poet_id from poets where name = poet;
Insert into deaths (mort_id, age) values (poet_id, poet_age );
End set_death_age;

Very unique? No. I bet you will definitely look forward to seeing an update on a poets table. This also implies how easy it is to implement using stored procedures. Set_death_age is almost certainly a bad implementation. We should add a column in the poets table to store the age of death. Java code does not care about how the database mode is implemented, because it only calls the stored procedure. In the future, we can change the database mode to improve performance, but we do not have to modify our code.
The following is the Java code for calling the above stored procedure:

Public static void setdeathage (poet dyingbard, int age)
Throws sqlexception
{
Connection con = NULL;
Callablestatement proc = NULL;

Try
{
Con = connectionpool. getconnection ();
Proc = con. preparecall ("{call set_death_age (?, ?) }");
Proc. setstring (1, dyingbard. getname ());
Proc. setint (2, age );
Proc.exe cute ();
}
Finally
{
Try
{
Proc. Close ();
}
Catch (sqlexception e ){}
Con. Close ();
}
}

To ensure maintainability, we recommend that you use static methods like this. This also enables the code that calls the stored procedure to be concentrated in a simple template code. If you use many stored procedures, you will find that you only need to copy and paste to create a new method. Because of the template-based code, you can even use scripts to automatically produce code that calls the stored procedure.

Functions

Stored Procedures can return values, so the callablestatement class has methods like getresultset to obtain the returned values. When a stored procedure returns a value, you must use the registeroutparameter method to tell the JDBC driver what the SQL type of the value is. You must also adjust the stored procedure call to indicate that the process returns a value.
The following is an example. This time, we will query the age of Dylan Thomas when he died. This stored procedure uses PostgreSQL's PL/pgsql:

Create Function snuffed_it_when (varchar) returns integer'
Declare
Poet_id number;
Poet_age number;
Begin
-- First get the ID associated with the poet.
Select ID into poet_id from poets where name = $1;
-- Get and return the age.
Select age into poet_age from deaths where mort_id = poet_id;
Return age;
End;
'Language' PL/pgsql ';

In addition, note that PL/pgsql parameter names are referenced using the $ n Syntax of UNIX and DOS scripts. At the same time, pay attention to embedded comments, which is another advantage over Java code. Writing such annotations in Java is certainly acceptable, but it looks messy and out of line with SQL statements and must be embedded into Java strings.
The following is the Java code that calls the stored procedure:

Connection. setautocommit (false );
Callablestatement proc =
Connection. preparecall ("{? = Call snuffed_it_when (?) }");
Proc. registeroutparameter (1, types. integer );
Proc. setstring (2, poetname );
Cs.exe cute ();
Int age = Proc. getint (2 );

What if an error return value type is specified? Then, a runtimeexception will be thrown when a stored procedure is called, as you encounter when using an error type in the resultset operation.

Complex return values

Many people are familiar with the stored procedure. If this is all the functions of a stored procedure, the stored procedure is not a replacement for other remote execution mechanisms. Stored Procedure functions are much more powerful than this.
When you execute an SQL query, DBMS creates a database object called a cursor (cursor) to iterate each row in the returned results. Resultset is a cursor representation of the current time point. This is why you can only move forward in the resultset without caching or specific database support.
Some DBMS allow returning a reference to a cursor from a stored procedure. JDBC does not support this function, but the JDBC drivers of Oracle, PostgreSQL, and DB2 support opening the pointer to the cursor on the resultset ).
Imagine listing all poets who have not yet lived to retirement. The following is the Stored Procedure for completing this function, returning an open cursor, also using PostgreSQL's PL/pgsql language:

Create procedure list_early_deaths () return refcursor'
Declare
Toesup refcursor;
Begin
Open toesup
Select poets. Name, deaths. Age
From poets, deaths
-- All entries in deaths are for poets.
-- But the table might become generic.
Where poets. ID = deaths. mort_id
And deaths. age <60;
Return toesup;
End;
'Language 'plpgsql ';

The following is the Java method that calls the stored procedure and outputs the result to printwriter:
Printwriter:

Static void sendearlydeaths (printwriter out)
{
Connection con = NULL;
Callablestatement toesup = NULL;
Try
{
Con = connectionpool. getconnection ();

// PostgreSQL needs a transaction to do this...
Con. setautocommit (false );

// Setup the call.
Callablestatement toesup
= Connection. preparecall ("{? = Call list_early_deaths ()}");
Toesup. registeroutparameter (1, types. Other );
Getresults.exe cute ();

Resultset rs = (resultset) getresults. GetObject (1 );
While (Rs. Next ())
{
String name = Rs. getstring (1 );
Int age = Rs. getint (2 );
Out. println (name + "was" + age + "years old .");
}
Rs. Close ();
}
Catch (sqlexception E)
{
// We shoshould protect these CILS.
Toesup. Close ();
Con. Close ();
}
}

Because JDBC does not directly support returning a cursor from a stored procedure, we use types. Other to indicate the type of returned data for the stored procedure, then call the GetObject () method, and perform forced type conversion on the returned value.
The Java method for calling the stored procedure is a good example of mapping. Mapping is an abstract method for operations on a set. Instead of returning a set in this process, we can transfer the operation to execute it. In this example, the operation is to print the resultset to an output stream. This is a very common example. The following is another method for calling the same stored procedure:

Public class processpoetdeaths
{
Public abstract void senddeath (string name, int age );
}

Static void mapearlydeaths (processpoetdeaths Mapper)
{
Connection con = NULL;
Callablestatement toesup = NULL;
Try
{
Con = connectionpool. getconnection ();
Con. setautocommit (false );

Callablestatement toesup
= Connection. preparecall ("{? = Call list_early_deaths ()}");
Toesup. registeroutparameter (1, types. Other );
Getresults.exe cute ();

Resultset rs = (resultset) getresults. GetObject (1 );
While (Rs. Next ())
{
String name = Rs. getstring (1 );
Int age = Rs. getint (2 );
Mapper. senddeath (name, age );
}
Rs. Close ();
}
Catch (sqlexception E)
{
// We shoshould protect these CILS.
Toesup. Close ();
Con. Close ();
}
}

This allows arbitrary processing on the resultset data without changing or copying the method for obtaining the resultset:

Static void sendearlydeaths (final printwriter out)
{
Processpoetdeaths mymapper = new processpoetdeaths ()
{
Public void senddeath (string name, int age)
{
Out. println (name + "was" + age + "years old .");
}
};
Mapearlydeaths (mymapper );
}

This method uses an anonymous instance of processpoetdeaths to call mapearlydeaths. This instance has an implementation of the senddeath method, and writes the result to the output stream in the same way as in the preceding example. Of course, this technique is not unique to stored procedures, but it is used in conjunction with the resultset returned in stored procedures. It is a very powerful tool.

Conclusion

Stored procedures can help you separate logic in code, which is basically beneficial. The advantages of this separation are:
& #8226; quickly create an application and change and improve the database mode with the application.
& #8226; the database mode can be changed in the future without affecting Java objects. When we finish the application, we can re-design a better mode.
& #8226; the stored procedure makes complex SQL easier to understand through better SQL embedding.
& #8226; writing stored procedures has better tools than writing embedded SQL statements in Java-most editors provide syntax highlighting!
& #8226; stored procedures can be tested in any SQL command line, making debugging easier.

Not all databases support stored procedures, but there are many great implementations, including free/open-source and non-free, so porting is not a problem. Oracle, PostgreSQL, and DB2 both have similar stored procedure languages and are well supported by online communities.
There are many stored procedure tools, such as the toad or Tora Editor, debugger, and Ide. It provides a powerful environment for compiling and maintaining PL/SQL or PL/pgsql.
Stored Procedures increase the overhead of your code, but they are much less overhead than most application servers. If your code is complex enough to use DBMS, I suggest using the entire stored procedure.

Resources

& #8226; JDBC Specification
& #8226; PostgreSQL
& #8226; Oracle Corporation's Oracle Database Server
& #8226; IBM's DB2 database server

About the author: Nic Ferrier is an independent software consultant for Web applications.