program | stored Procedure | design | data | Database This article explains how to use DBMS stored procedures. Describes the basic and advanced features of using stored procedures, such as returning resultset. This article assumes that you are already familiar with the DBMS and JDBC, and that you can read the code written in other languages without any hindrance (that is, not the Java language), but it does not require you to have any memory procedure programming experience.
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 a stored procedure in a Java class. At the time of invocation, 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 via a 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 of charge from many popular DBMS, while application servers are mostly expensive. This is not just a matter of licensing fees. The management of the application server, the cost of writing the code, and the increased complexity of the client program can be overridden entirely by the stored procedures in the DBMS.
You can use Java,python,perl or c to write stored procedures, but you usually use a specific language specified by your DBMS. Oracle uses PL/SQL,POSTGRESQL to use procedural SQL using 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. Furthermore, stored procedures are designed for embedded SQL, which makes them more user-friendly in expressing the database mechanism than in languages such as Java or C.
Because stored procedures run on the DBMS itself, this can help reduce the wait time in your 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 round-trip numbers on the network can dramatically optimize performance.
Using Stored Procedures
Simple old JDBC supports the invocation of stored procedures through the CallableStatement class. The class is actually a subclass of the PreparedStatement. Let's say we have a poets database. There is a stored procedure in the database that sets the age of the poet's death. Here is the old drunkard Dylan Thomas Soak Dylan Thomas, do not specify whether the allusion, culture, please criticize. 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 of stored procedures: there is no need for stub classes or configuration files to invoke stored procedures from applications, except for the JDBC driver of your DBMS.
When this code executes, the stored procedure of the database is invoked. We did not get the result because the stored procedure did not return the result. Success or failure of execution will be known by an 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 "Dylan Thomas" does not exist in the poets database)
combining SQL operations with stored procedures
Mapping Java objects to rows in a SQL table is fairly straightforward, but usually requires several SQL statements to execute; It may be a select Lookup ID, and then an insert inserts the data for the specified ID. In a database schema that is highly normalized (in line with the higher paradigm), updates to multiple tables may be required, so more statements are required. Java code expands quickly, and the network overhead of each statement increases rapidly.
Moving these SQL statements into one stored procedure greatly simplifies the code, involving only one network call. All associated SQL operations can occur within the database. Also, stored procedure languages, such as Pl/sql, allow the use of SQL syntax, which is more natural than Java code. Here is our early stored procedure, written in Oracle's Pl/sql language:
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;
Is it unique? No. I bet you expect to see an update on a poets table. This also hints at how easy it is to use a stored procedure implementation. Set_death_age is almost certainly a lousy implementation. We should add a column to the Poets table to store the age of death. The Java code does not care about how the database schema is implemented, because it invokes only the stored procedure. We can change the database schema later to improve performance, but we don't have to modify our code.
The following is the Java code that invokes the stored procedure above:
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 ();
}
}
To ensure maintainability, a static method like this is recommended. This also causes the code that invokes the stored procedure to be centered in a simple template code. If you use a lot of stored procedures, you will find that only need to copy, paste can create new methods. Because of the template of the code, you can even automate the production of code that invokes the stored procedure through a script.
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 driver what the SQL type of the value is. You must also adjust the stored procedure to indicate that the procedure returns a value.
Here's the example above. This time we inquired about the age of Thomas Dylan when he died. 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
--The ID associated with the poet.
SELECT ID into poet_id from poets WHERE name = $;
--the "get" and "return" age.
SELECT age into 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 through the $n syntax of UNIX and DOS scripts. Also, be aware of embedded annotations, which is another advantage compared to Java code. It is certainly possible to write such annotations in Java, but it looks messy and disjointed with SQL statements and must be embedded in a Java string.
The following is the Java code that invokes 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 wrong return value type is specified? Then, when the stored procedure is invoked, a runtimeexception is thrown, just as you would encounter with an incorrect type in the resultset operation.
Complex return value
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 the stored procedure, then the stored procedure is not a replacement for other remote execution mechanisms. Stored procedures are much more powerful than this.
When you execute an SQL query, the DBMS creates a database object called cursor (cursor) that iterates through each row in the return result. ResultSet is a representation of the cursor at the current point in time. This is why there is no caching or specific database support, you can only move forward in the resultset.
Some DBMS allow you to return a reference to a cursor from a stored procedure. JDBC does not support this feature, but Oracle, PostgreSQL, and DB2 JDBC drives support a pointer to a cursor (pointer) on the resultset.
Imagine a list of all poets who have not lived to retirement age, the following is a stored procedure that completes this function, returns an open cursor, and also uses the PostgreSQL Pl/pgsql language:
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.age < 60;
return toesup;
End
' Language ' plpgsql ';
The following is a Java method that invokes the stored procedure, outputting the results to PrintWriter:
PrintWriter:
static void Sendearlydeaths (PrintWriter out)
{
Connection con = null;
CallableStatement toesup = null;
Try
{
con = connectionpool.getconnection ();
PostgreSQL needs a transaction to doing 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 type conversions on the return value.
This Java method that invokes the stored procedure is a good example of mapping. Mapping is a method of abstracting the operations on a set. Instead of returning a set on this process, we can transfer the operation into execution. In this case, the operation is to print the resultset to an output stream. This is a very common example of a worthy example, and here is 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 on resultset data without changing or replicating the method for obtaining resultset:
static void Sendearlydeaths (final printwriter out)
{
Processpoetdeaths mymapper = new Processpoetdeaths ()
{
public void Senddeath (String name, int age)
{
OUT.PRINTLN (name + ' was ' + age + ' years old. ');
}
};
Mapearlydeaths (Mymapper);
}
This method invokes mapearlydeaths using an anonymous instance of processpoetdeaths. The instance has an implementation of the Senddeath method that writes the result to the output stream in the same way as the example above. Of course, this technique is not unique to stored procedures, but it is a very powerful tool to use in conjunction with the ResultSet returned in a stored procedure.
Conclusions
Stored procedures can help you separate logic in your code, which is always beneficial. The advantages of this separation are:
• Quickly create applications that use and apply changes and improvements to the database schema.
• The database schema can be changed later without impacting the Java object, and when we're done, we can redesign the better patterns.
• 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 a number of great implementations, including free/open source and not free, so porting is not a problem. Oracle, PostgreSQL, and DB2 all have similar stored procedure languages and are well supported by online communities.
There are many stored procedure tools, such as editors, debuggers, and Ides like Toad or Tora, which provide a powerful environment for writing, maintaining Pl/sql or pl/pgsql.
Stored procedures do increase the overhead of your code, but they are much less expensive than most application servers. If your code is complex to require the use of a DBMS, I recommend the whole way of using a stored procedure.
