original:
Http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
By Nic Ferrier
This article describes how to use the DBMS stored procedure. I explained the basic and advanced features of the stored procedure, such as returning the resultset. This article assumes that you are very familiar with DBMS and JDBC, and assume that you can read code written in other languages without obstacles, but do not ask you to have any stored procedures.
The stored procedure refers to a program saved in the database and executed in the database. You can use a special syntax to call the stored procedure in the Java class. When calling, the name of the stored procedure and the specified parameters are sent to DBMS through the JDBC connection, perform the stored procedure and return the result by connection (if any).
Use the stored procedure to have and use the application server based on EJB or CORBA. The difference is that the stored procedure can be used free of use in many popular DBMs, and the application server is very expensive. This is not just a problem of license costs. The use of the application server needs to be expected, the cost of writing code, and the complexity of the customer program increases, can be replaced through the stored procedure in DBMS.
You can write a stored procedure using Java, Python, Perl, or C, but usually use the specific language specified by your DBMS. Oracle uses PL / SQL, PostgreSQL uses PL / PGSQL, DB2 uses Procedural SQL. These languages are very similar. The transplantation stored procedure between them is not difficult to transplant the session bean between the EJB specifications of the Sun's EJB. Also, the stored procedure is designed to embed SQL, which makes them more friendly to represent the mechanism of the database than Java or C.
Because the stored procedure runs in DBMS itself, this can help reduce the waiting time in the application. Not 4 or 5 SQL statements in the Java code, but only need to perform 1 stored procedure on the server side. The reduction in the number of data round trips on the network can be dramatically optimized.
Use stored procedures
Simple old JDBC supports the call to store the stored procedure through the CallableStatement class. This class is actually a subclass of PreparedStatement. Suppose we have a Poets database. There is a stored procedure for setting the poet's death age. Here is the detailed code for the old alcohol Dylan Thomas (Old SOAK DYLAN THOMAS (not specified whether all the allusions, cultures, please criticize the correct code:
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.execute ();
}
Catch (SQLException E)
{
// ....
}
Strings passing to the PrepareCall method are the writing specifications called by the stored procedure. It specifies the name of the stored procedure ,? Represents the parameters you need to specify.
And JDBC integration is a great convenience of the stored procedure: In order to call the stored procedure from the application, there is no need to set up the root class or configuration file, except for your DBMS JDBC driver, no need.
When this code is executed, the stored procedure of the database is called. We didn't get results because the stored procedure did not return the result. Performing success or failure will be known from exceptions. Failure may mean failing to call the stored procedure (such as the type of parameter provided), or an application failure (such as throwing an exception indication) in the POTS database does not exist "Dylan Thomas") combined with SQL operation With storage procedure
Mapping the Java object to the SQL table is quite simple, but you usually need to perform several SQL statements; it may be a SELECT lookup ID, then an insert is inserted into the specified ID. In a database mode of height-specific (in compliance with higher paradigm, translation), multiple tables may be needed, so more statements are required. The Java code will expand very quickly, and the network overhead of each statement is also increased.
Transfer these SQL statements to a stored procedure will greatly simplify the code, and only involve a network call. All associated SQL operations can occur inside the database. Also, store procedure languages, such as PL / SQL, allowing SQL syntax, which is more natural than Java code. Below is our early stored procedure, using Oracle's PL / SQL language:
[pre] 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; [/ pre]
Unique? Do not. I bet you must look forward to seeing Update on a Poets table. This also suggests how easy it is to use the stored procedure. Set_death_age can almost certainly be a very bad implementation. We should add a list to the Poets table to store the dead age. The Java code is not concerned about how the database mode is implemented because it only calls the stored procedure. We will change the database mode in the future to improve performance, but we don't have to modify our code.
Below is the Java code called above stored procedures:
public
Static
Void setDeathage (Poet Dyingbard,
Int ag
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.execute ();
}
Finally
{
Try
{
proc.close ();
}
Catch (SQLEXCEPTION E) {}
C. close ();
}
}
To ensure maintainability, it is recommended to use a Static method like here. This also makes the code to call the stored procedure in a simple template code. If you use a lot of stored procedures, you will find that you only need to copy and paste you can create a new method. Because of the template of the code, the code for calling the stored procedure can be automatically produced by script.
Functions
The stored procedure can have a return value, so the CallableStatement class has a method similar to getResultSet to get the return value. When the stored procedure returns a value, you must use the RegisterOutParameter method to tell the JDBC drive that 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 example is followed. This time we query the age of Dylan Thomas died. This stored procedure uses PostgreSQL PL / PGSQL:
[pre] 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 agn.
SELECT AGE INTO POET_AGE From Deaths where Mort_ID = Poet_ID;
Return Age;
END;
'Language' PL / PGSQL '; [/ pre]
Also, pay attention to the $ n syntax references of the PL / PGSQL parameter name through UNIX and DOS scripts. At the same time, pay attention to embedded annotations, which is another superiority than Java code. Write such a note in Java is of course ok, but it looks very messy, and and the SQL statement is detached, it must be embedded in Java String.
Here is the Java code called this stored procedure:
Connection.SetAutocommit (
FALSE);
CallableStatement Proc =
Connection.prepareCall (
"{? = CALL SNUFFED_IT_WHEN (?)}");
Proc.registerOutparameter (1, Types.integer);
Proc.setstring (2, poetname);
cs.execute ();
INT AGE = Proc.getInt (2);
What happens if the end of the error is specified? Then, when the stored procedure will throw a RuntimeException, just as you have used in the RESULTSET operation.
Complex return value
About the knowledge of the stored procedure, many people seem to be familiar with the above. If this is the full function of the stored procedure, the stored procedure is not an alternative to other remote execution mechanisms. The function of the stored procedure is much larger than this.
When you perform a SQL query, DBMS creates a database object called Cursor (cursor), used to iterate each row in return results. ResultSet is a representation of the cursor at the current time point. That's why there is no support for caching or specific databases, you can only move forward in the ResultSet.
Some DBMS allows a reference to the cursor from the stored procedure. JDBC does not support this feature, but Oracle, PostgreSQL, and DB2 JDBC drivers are supported on the RESULTSET to open to the cursor.
Imagine that all poets that have not lived to retirement age, the following is the stored procedure that completes this function, returns an open cursor, and also use PostgreSQL PL / PGSQL language:
[pre] Create Procedure List_early_deaths () Return Refcursor AS '
Declare
TOESUP REFCURSOR;
Begin
Open TOESUP for
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.An;
Return TOESUP;
END;
'Language' PLPGSQL '; [/ pre]
Below is a 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.execute ();
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 Should Protect these Calls.
TOESUP.CLOSE ();
C. close ();
}
}
Because JDBC does not directly support the return cursor from the stored procedure, we use Types.other to indicate the return type of the stored procedure, then call the getObject () method and mandatory type conversion to the return value.
This Java method for calling the stored procedure is a good example of mapping. Mapping is a method of abstracting a set of operations. Not returns a set on this process, we can transfer the operation into the execution. In this case, the operation is to print the resultSet to an output stream. This is a very common example of a very commonly used example. Here is another method implementation to call the same stored procedure:
public
Class ProcessPoetDeaths
{
public
Abstract
Void Senddeath (String Name,
Int agn);
}
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.execute ();
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 Should Protect these Calls.
TOESUP.CLOSE ();
C. close ();
}
}
This allows any processing to be performed on the ResultSet data without having to change or copy the method of obtaining the resultSet:
Static
Void SendearlyDeaths
Final PrintWriter OUT
{
ProcessPoetDeaths mymapper =
New processPoetDeaths ()
{
public
Void Senddeath (String Name,
Int ag
{
Out.println (Name
"WAS" AGE
"YEARS OLD.");
}
}
MapearlyDeaths (mymapper);
}
This method uses an anonymous instance of ProcessPoetDeaths to call MAPEARLYDEATHS. This example has an implementation of the SendDeath method, writes the result to the output stream like the example above. Of course, this skill is not unique, but it is a very powerful tool with the RESULTSET returned from the stored procedure.
in conclusion
Storage procedures can help you separate logic in your code, which is basically more beneficial. The benefits of this separation are:
• Quickly create applications, use and apply database modes together with the application.
• Database mode can be changed later without affecting Java objects. When we complete the app, you can redesign a better mode.
• The stored procedure is easier to understand by better SQL embedding makes complex SQL.
• Writing a stored procedure is better than the SQL written in Java - most editors provide syntax highlight!
• The stored procedure can be tested in any SQL command line, which makes debugging easier.
Not all databases support stored procedures, but there are many great implementations, including free / open source and non-free, so transplant is not a problem. Oracle, PostgreSQL, and DB2 have similar storage procedures, and online communities are well supported.
There are many stored procedure tools, with editors such as toad or TORA, debuggers, and IDEs provide a powerful environment for writing, maintaining PL / SQL or PL / PGSQL.
The stored procedure does increase your code overhead, but they have much smaller than most application servers. If your code is complex to use DBMS, I recommend the entire way of adopting the stored procedure.
Resource
•
JDBC Specification
•
PostgreSQL
•
Oracle Corporation's Oracle Database Server
•
IBM's DB2 Database Server
About the Author:
NIC Ferrier is an independent software consultant in web applications.
