The Java programmer's stored procedure Bruce translation (Participation: 21129, expert score: 4230) Published: 2003-9-23 5:53 pm version: 1.0 Read: 7300 times
Original: http://www.onjava.com/pub/a/onjava/2003/08/13/Stored_Procedures.htmlby 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) (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) {/ / ....} The string to the prepareCall method is the writing specification 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 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. 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. 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. 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 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.
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.setAutoCommit (false);. // Setup the call CallableStatement toesUp = connection.prepareCall ( "{=? Call list_early_deaths ()} "); TOESUP.REGISTEROUTPARAMETER (1, types.other); getResults.execute (); resultset = (r EsultSet) 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 stay protect these calls. TOESUP.CLOSE () ;con.close ();}} Because JDBC does not directly support Return the cursor during 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);}} catch (sqlexception e) {// we stay protect these calls. Toesup.close (); con.close ();}} Allow any processing on the ResultSet data without having to change or copy the method of obtaining the Resultset: Static Void SendearlyDeaths (Final PrintWriter out) {ProcessPoetde Aths mymapper = new processPoEtdeaths () {public void senddeath (String name, int agent) {Out.println (Name "WAS" Age "Years Old.");}}; mapearlydeaths (mymapper);} An anonymous instance of ProcessPoetDeaths calls 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. Conclusion The stored procedure 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. Resources • JDBC Specification • PostgreSQL • Oracle Corporation's Oracle Database Server • IBM's DB2 Database Server Authors: Nic Ferrier is a separate software consultant in web applications. Copyright Notice to the author write a letter
Is this article helpful? Vote:
Yes
No Vote:
8
1
Discuss: Javaone Participation: 6473 Experts: 2640 Posted: 2003-9-24 8:14 am Translated! But the author's recommendations are not dare to use the stored procedures. The JDO package can now be used to achieve the purpose of separation and database mode, but for multiple operations combined SQL, the stored procedure is not a good way.
Discuss: Evan Participation: 429 Experts: 400 From: Shanghai: 3:50 in 2003-9-28 10:50 in the morning
Discussant: CordialReal Participation: 30 Experts: 0 Published: 2003-9-29 Decree to TOESUP.REGISTEROUTPARAMETER (1, Types.other), showing "Invalid Column Type" Cause?
Guest: BDOY Published: 2004-4-22 12:01 PM PM Types.other replaced with Oracletypes.cursor, if it is under 9i. But now the program is still wrong when the mandatory type is converted.
