Java Programmer's Stored procedure

A stored procedure is a program that is saved in a database and executed on the database side. You can use special syntax to invoke stored procedures in a Java class. At invocation time, the name of the stored procedure and the specified parameters are sent to the DBMS through a JDBC connection, executing the stored procedure and returning the result through the 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 from many popular DBMS, and application servers are mostly expensive. This is not just a matter of license fees. The management required to use the application server, the cost of writing code, and the added complexity of the client program can all be replaced by stored procedures throughout the DBMS.

You can write stored procedures using Java,python,perl or C, but typically use the specific language that your DBMS specifies. Oracle uses PL/SQL,POSTGRESQL to use procedural SQL with PL/PGSQL,DB2. These languages are very similar. Porting stored procedures between them is no more difficult than porting session beans between different implementations of the Sun's EJB specification. Also, stored procedures are designed for embedded SQL, which makes them more user-friendly ways of expressing a database than a language such as Java or C.

Because stored procedures run on the DBMS itself, this can help reduce the wait time in the application. Instead of executing 4 or 5 SQL statements in Java code, you only need to execute 1 stored procedures on the server side. A reduction in the number of data round trips on the network can dramatically optimize performance.

Using Stored Procedures

Simple old JDBC supports the invocation of 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 that sets the age of the poet's death. The following is an old alcoholic Dylan Thomas, soak Dylan Thomas, who does not specify whether the allusion, culture, please criticize. The detailed code for the call:

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 passed to the Preparecall method is 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 for stored procedures: In order to invoke a stored procedure from an application, no stub class or configuration file is required, except for the JDBC driver of your DBMS.

When this code executes, the stored procedure for the database is called. We are not going to get the result because the stored procedure does not return a result. Execution success or failure will be known by exception. Failure can mean a failure to invoke a stored procedure (such as an incorrect type of argument provided), or an application failure (such as throwing an exception indicating that there is no "Dylan Thomas" in the Poets Database)

Combining SQL operations with stored procedures

Mapping Java objects to rows in a SQL table is fairly straightforward, but typically requires a few SQL statements, a select Lookup ID, and an insert that inserts the data for the specified ID. In a database schema that is highly normalized (conforming to higher paradigms), multiple table updates may be required, so more statements are required. Java code expands quickly, and the network overhead for each statement increases rapidly.

Moving these SQL statements into a stored procedure will greatly simplify the code, involving only one network call. All associated SQL operations can occur within the database. Also, the stored procedure language, such as PL/SQL, allows the use of syntax, which is more natural than Java code. Here are our early stored procedures, written in the PL/SQL language of Oracle:

Create PROCEDURE Set_death_age (poet VARCHAR2, poet_age number) poet_id Number;begin SELECT ID into poet_id from poets wher E name = poet; INSERT into deaths (mort_id, age) VALUES (poet_id, poet_age); end Set_death_age;

Very unique? No. I bet you expect to see an update on the Poets table. This also implies how easy it is to use a stored procedure implementation. Set_death_age can almost certainly be a rotten realization. We should add a column to the Poets table to store the age of death. The Java code does not care how the database schema is implemented because it calls only stored procedures. We can change the database schema later to improve performance, but we don't have to modify our code.

Here is the Java code that calls the stored procedure above:

public static void Setdeathage (poet Dyingbard, int.) throws sqlexception{Connection con = null; callablestatement proc = null; try {con = connectionpool.getconnection (); proc = Con.preparecall ("{Call Set_death_age (?,?)}"); Proc.setstring (1, Dyin Gbard.getname ()); Proc.setint (2, age); Proc.execute (); } finally {try {proc.close ();} catch (SQLException e) {} con.close ()}}

To ensure maintainability, it is recommended to use a static method like this here. This also causes the code that invokes the stored procedure to be 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 of the code, you can even automate the production of code that calls the stored procedure through scripting.

Functions

Stored procedures can have return values, so the CallableStatement class has a method like Getresultset to get the return value. When a stored procedure returns a value, you must use the Registeroutparameter method to tell the JDBC drive what the SQL type of the value is. You must also adjust the stored procedure tune to indicate that the procedure returns a value.

Following is the example above. This time we inquire about the age of Dylan Thomas's death. This time the 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 = $; --Get and return the age. SELECT age to Poet_age from deaths WHERE mort_id = poet_id; return age;end; ' language ' pl/pgsql ';

Also, note that the Pl/pgsql parameter name is referenced by the $n syntax of UNIX and DOS scripts. Also, note the embedded annotations, which is another advantage over Java code. It is certainly possible to write such comments in Java, but it looks messy and disjointed with SQL statements and must be embedded in a Java string.

Here is the Java code that calls this stored procedure:

Connection.setautocommit (FALSE); callablestatement proc = Connection.preparecall ("{? = Call Snuffed_it_when (?)} "); Proc.registeroutparameter (1, Types.integer);p roc.setstring (2, Poetname); Cs.execute (); int age = Proc.getint (2);

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

Complex return values

As for the knowledge of stored procedures, many people seem to be familiar with what we are talking about. If this is the full functionality of a stored procedure, then the stored procedure is not a replacement for other remote execution mechanisms. The functionality of the stored procedure is much more powerful than this.

When you execute an SQL query, the DBMS creates a database object called the cursor (cursor) that iterates through each row in the returned result. ResultSet is a representation of the cursor at the current point in time. This is why there is no cache or support for a particular database, you can only move forward in resultset.

Some DBMS allow a reference to a cursor to be returned from a stored procedure. JDBC does not support this feature, but the JDBC drivers for Oracle, PostgreSQL, and DB2 support pointers (pointer) that are opened to cursors on resultset.

Imagine a list of all the poets who did not live to retirement age, here is the stored procedure that completes this function, returns an open cursor, and also uses the Pl/pgsql language of PostgreSQL:

CREATE PROCEDURE list_early_deaths () return refcursor as "declare toesup Refcursor;begin open toesup for SELECT poets.na Me, deaths.age from poets, deaths – all entries in deaths is for poets. -but the table might become generic. WHERE poets.id = deaths.mort_id and Deaths.age < 60; return toesup;end; ' language ' plpgsql ';

Here is the Java method that calls the stored procedure, outputting 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 ();  con.close (); }} 

Because JDBC does not directly support returning cursors from stored procedures, we use Types.other to indicate the return type of the stored procedure, and then call the GetObject () method and force the type conversion on the return value.

This Java method of calling a stored procedure is a good example of mapping. Mapping is the method of abstracting an operation on a set. Instead of returning a set on this process, we can send the operation in to execute. In this case, the operation is to print the resultset to an output stream. This is a very common example that deserves an example, and here's another way to invoke 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.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 ();  con.close (); }} 

This allows arbitrary processing to be performed on resultset data without the need to change or replicate the method of acquiring ResultSet:

static void Sendearlydeaths (final printwriter out) {processpoetdeaths mymapper = new Processpoetdeaths () {public void Sen Ddeath (String name, int age) {out.println (name + ' was ' + age + ' years old ');}}; Mapearlydeaths (mymapper);}

This method calls mapearlydeaths using an anonymous instance of processpoetdeaths. The instance has an implementation of the Senddeath method that writes the results to the output stream in the same way as we did above. Of course, this technique is not unique to the stored procedure, but it is a very powerful tool to use in conjunction with the ResultSet returned in the stored procedure.

Conclusion

Stored procedures can help you separate logic in your code, which is basically always beneficial. The benefits of this separation are:

• Quickly create applications, use and apply changes and improvements to the database schema.

• The database schema can be changed later without affecting the Java object, and when we have finished the application, we can redesign the better schema.

• Stored procedures make complex SQL easier to understand with better SQL embedding.

• Writing stored procedures has better tools than writing Embedded SQL in Java-most editors provide syntax highlighting!

• Stored procedures can be tested on 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 porting is not a problem. Oracle, PostgreSQL, and DB2 all have similar stored procedure languages and are well supported by the online community.

There are many stored procedure tools, such as Toad or Tora editors, debuggers, and Ides that provide a powerful environment for writing, maintaining PL/SQL, or Pl/pgsql.

Stored procedures do increase the cost of your code, but they are much less expensive than most application servers. If your code is so complex that you need to use a DBMS, I recommend the whole approach to using stored procedures.

Java Programmer's Stored procedure