Java Call stored Procedures (JDBC & hibernate) __ Storage

First, JDBC calls stored procedures

creat proc Proc_select

@pid varchar (20)

@address varchar (output)

As

Select @address =address from UserInfo where pid= @pid

Go

Invoke in Java:

Class.forName (/"sun.jdbc.odbc.jdbcodbcdriver/"); Load Driver

Connection con=drivermanager.getconnection (/"jdbc:odbc:test/",/"sa/",/"/"); Get a connection

String call=/"{call Proc_select (?,?)};/"//Calling statement

CallableStatement Proc=con.preparecall (call); Calling stored procedures

Proc.setstring (1,/"12345678/"); Passing values to input parameters

Proc.registeroutparameter (2,type.varchar); Declares what type of output parameter is

Proc.execute (); Perform

String address=proc.getstring (2); Get output parameters

Java Call stored Procedures

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

--first get the ID associated with the poet.

SELECT ID into poet_id from poets WHERE name = $;

--get and return to the age.

SELECT age into Poet_age from deaths WHERE mort_id = poet_id; [Page]

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 happens if you specify the wrong return value type? Then, when the stored procedure is invoked, a runtimeexception is thrown, just as you would have encountered in a resultset operation using a wrong type.

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;

Second, hibernate call stored Procedures

Java code
1.CREATE TABLE ' Proctab ' (
2. ' id ' int (one) not NULL auto_increment,
3. ' Name ' varchar (20),
4. ' Age ' int (11),
5.PRIMARY KEY (' id ')
6.)
CREATE TABLE ' Proctab ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (20),
' Age ' int (11),
PRIMARY KEY (' id ')
)

Simple stored Procedures

Java code
1.create PROCEDURE proc ()
2. Begin
3. Select * from Proctab;
4. End;
Create PROCEDURE proc ()
Begin
SELECT * from Proctab;
End;

One way to do this is by hibernate the traditional XML mapping method to call

Java code
1.<class name= "Com.test.User" table= "Proctab" >
2. <id name= "id" column= "id" >
3. <generator class= "native"/>
4. </id>
5. <property name= "name" column= "name" type= "string"/>
6. <property name= "Age" column= "Age" type= "integer"/>
7.</class>
8. <sql-query name= "GetUser" callable= "true" >
9. <return alias= "user" class= "Com.test.User" >
<return-property name= "id" column= "id"/>
<return-property name= "name" column= "name"/>
<return-property name= "Age" column= "age"/>
</return>
{Call proc ()}
</sql-query>
<class name= "Com.test.User" table= "Proctab" >
<id name= "id" column= "id" >
<generator class= "native"/>
</id>
<property name= "name" column= "name" type= "string"/>
<property name= "Age" column= "Age" type= "integer"/>
</class>
<sql-query name= "GetUser" callable= "true" >
<return alias= "user" class= "Com.test.User" >
<return-property name= "id" column= "id"/>
<return-property name= "name" column= "name"/>
<return-property name= "Age" column= "age"/>
</return>
{Call proc ()}
</sql-query>

Call method

Java code
1.Session ss= hibernatesessionfactory.getsession ()
2.List li=ss.getnamedquery ("GetUser"). List ();
3.ss.close ();
Session ss= Hibernatesessionfactory.getsession ()
List Li=ss.getnamedquery ("GetUser"). List ();
Ss.close ();

and its JDBC-like approach

Java code
1.Session session =hibernatesessionfactory.getsession ();
2.Connection conn = Session.connection ();
3.ResultSet RS =null;
4.CallableStatement call = Conn.preparecall ("{Call proc ()}");
5.rs = Call.executequery ();
6.rs.close ();
7.session.close ();
Session session =hibernatesessionfactory.getsession ();
Connection conn = Session.connection ();
ResultSet RS =null;
CallableStatement call = Conn.preparecall ("{Call proc ()}");
rs = Call.executequery ();
Rs.close ();
Session.close ();

This approach is basically JDBC, but it's simple, it's always weird.

And it's done through a powerful createsqlquery.
O (∩_∩) o ... Haha personally prefer this method

Java code
1.Session session =hibernatesessionfactory.getsession ();
2.SQLQuery query = Session.createsqlquery ("{Call proc ()}");
3.List List =query.list ();
4.session.close ();
Session session =hibernatesessionfactory.getsession ();
SQLQuery query = Session.createsqlquery ("{Call proc ()}");
List List =query.list ();
Session.close ();

If you don't have a return value, use the Execute method directly.
(*^__^*) Forget an important problem is the problem of the parameters of the stored procedure

Java code
1.CallableStatement call = Conn.preparecall ("{Call proc (?)}");
2.call.setstring (1, parameter);
3.rs = Call.executequery ();
CallableStatement call = Conn.preparecall ("{Call proc (?)}");
Call.setstring (1, parameter);
rs = Call.executequery ();
Java code
1.SQLQuery query = Session.createsqlquery ("{Call proc (?)}");
2.query.setstring (0, parameter);
3.List List =query.list ();

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/nengyu/archive/2009/11/20/4840204.aspx