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. A simple JDBC using a stored procedure supports the call of 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, does not specify whether all the allusions, culture, 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) {// ....} The string to the prepareCall method is a writing specification that stored procedure calls. 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 Maping the Java object in the SQL table is quite simple to the stored procedure, but it usually needs 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: CREATE Procedure set_death_age (poet varcha2, poet_age number) Poet_id Number;
Begin Select ID INTO POET_ID from POTS Where Name = Poet
INSERT INTO Deaths (Mort_ID, AGE) VALUES (POET_ID, POET_AGE);
END SET_DEATH_AGE; very 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 a Java code called above stored procedures: 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.execute ();
}
Finally {
Try {proc.close ();
Catch (SQLEXCEPTION E) {}
C. close ();
}
} In order to ensure maintenanceability, 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. The Functions 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's PL / PGSQL: Create Function Snuffed_it_when (varchar) returns integer 'declarePOET_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 '; additional, note that the PL / PGSQL parameter name is referenced by 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. Below is a 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 if the error is specified? Then, when the stored procedure will throw a RuntimeException, just as you have used in the RESULTSET operation. Complex return values about the knowledge of the stored procedure, many people seem to be familiar with what we discussed. 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 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 uses PostgreSQL PL / PGSQL language: create procedure list_early_deaths () RETURN REFCURSOR AS 'DECLARESUP REFCURSOR;
Begin
Open toesup for select poets.name, Deaths.age from Poets, Deaths - All Entries in Deaths area for poets. - But The Table Might Become Generic.
WHERE POTS.ID = deaths.mort_id and deaths.And;
Return TOESUP;
End; 'Language' PLPGSQL '; the following is the Java method that calls the stored procedure, outputs the result to PrintWriter: PrintWriter: static void sendeardeaths (PrintWriter out) {
Connection con = NULL;
Callablestatement toesup = NULL;
Try {
Con = ConnectionPool.getConnection ();
// PostgreSQL Needs a Transaction to do this ... conce.
SetAutocommit (false); // setup the call.
CallableStatement TOESUP = Connection.PrepareCall ("{? = Call list_early_deaths ()}");
TOESUP.REGISTEROUTPARAMETER (1, Types.other);
TOESUP.EXECUTE ();
ResultSet RS = (ResultSet) TOESUP.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 () ;con.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 common way. The following is another method of 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);
TOESUP.EXECUTE ();
ResultSet RS = (ResultSet) TOESUP.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 stay protect these calls. TOESUP.CLOSE ();
C. close ();
}
} This allows any process to be performed on the ResultSet data without having to change or copy the method of obtaining the resulting result: static void sendwriterlyDeaths (Final PrintWriter Out) {
ProcessPoetDeaths mymapper = new processPoetDeaths () {
Public void senddeath (String name, int agent) {
Out.println (Name "WAS" AGE "Years Old.");
}
}
MapearlyDeaths (mymapper);
