original:
Http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
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 for all the allusions, culture, please criticize. Translation): 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 specification for the stored procedure call. 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. Here is our early stored procedure written 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] is 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. The following is a procedure call Java code stored thereon: 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) {} CON.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. 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. The stored procedure uses the 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 age. SELECT AGE INTO POET_AGE From Deaths where mort_id = poet_id; retURN AGE; End; 'Language' PL / PGSQL '; [/ pre]] Align PL / PGSQL parameter name The $ n syntax references to the DOS script. 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.get (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 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. Idea to list 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: [pre] crete 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.age <60; RETURN TOESUP; END; 'Language' PLPGSQL '; [/ pre] The following is the Java method that calls the stored procedure, outputs the result to PrintWriter: printwriter: static void sendeardeaths (PrintWriter out) {connection connes (Callablestatement) TOESUP = NULL; Try {Con = ConnectionPool.getConnection (); // PostgreSQL Needs a Transaction To do this ... con.sertoCommit (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 prothet (); con.close ();}} Because JDBC does not directly support returns 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. It is a worthy example of a very common example, the following is calling another method with a stored procedure implementation: 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.Execute (); ResultSet RS = (Resultset) getResults.GetObject (1); while (rs.next ()) {string name = rs.getstring (1); int Age = rs.getint (2); mappper.senddeath (name, age);} rs.close ();} catch (sqlexception e) {// We Should Protect these Calls.
TOESUP.CLOSE (); con.close ();}} This allows any processing to be performed on the ResultSet data without having to change or copy acquisition of ResultSet: static void sendearlyDeaths (Final PrintWriter out) {processPoetDeaths mymapper = new processPoetDeaths () {Public void senddeath (String Name, Int Age) {OUT.Println (Name "WAS" Age "Years");}}; mapearlydeaths (mymapper);} This method uses ProcessPoetDeaths an anonymous instance 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. This separation has: • 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.
