oracle stored procedure notes, and use stored procedures with Hibernate

2011-07-24  来源:本站原创  分类:Database  人气:178 

Oracle stored procedure is mainly used to deal with complex business logic, the application of the logic (code, such as java form) into the database (pl / sql form) to perform. Through stored procedures to perform batch updates. Stored procedures run directly in the database, perform more efficiently.
Can be defined in an Oracle database called batchUpdateStudent () stored procedure code is as follows:

create or replace procedure batchUpdateStudent(p_age in number) as
begin
update STUDENT set AGE=AGE+1 where AGE>p_age;
end;

Above stored procedure has a parameter p_age, on behalf of the student's age, the application can call a stored procedure as follows:

//hibernater  Update or delete stored procedure does not support directly, you can bypass the Hibernate,
// Direct use in Hibernate JDBC

tx = session.beginTransaction();
Connection con=session.connection();

String procedure = "{call batchUpdateStudent(?) }";
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1,0); // The age parameter is set to 0
cstmt.executeUpdate();
tx.commit();

Use stored procedures for querying
Hibernate3 introduces support for queries on the stored procedure. The procedure must return a result set, Hibernate can be used as the first external parameters. Here is a Oracle9i and later stored procedure example.

Create or REPLACE FUNCTION selectAllEmployments
RETURN    SYS_REFCURSOR
   AS
      st_cursor    SYS_REFCURSOR;
   BEGIN
     OPEN st_cursor FOR
     Select EMPLOYEE,EMPLOYER,
     STARTDATE, ENDDATE,
     REGIONCODE, EID, VALUE, CURRENCY
     FROM EMPLOYMENT;
     RETURN st_cursor;
END;

In Hibernate to use this query, you need to map it via a named query.

<sql-query    name="selectAllEmployees_SP"    callable="true">
           <return    alias="emp"   >
                   <return-property    name="employee"    column="EMPLOYEE"/>
                   <return-property    name="employer"    column="EMPLOYER"/>
                   <return-property    name="startDate"    column="STARTDATE"/>
                   <return-property    name="endDate"    column="ENDDATE"/>
                   <return-property    name="regionCode"    column="REGIONCODE"/>
                   <return-property    name="id"    column="EID"/>
                   <return-property    name="salary">
                           <return-column    name="VALUE"/>
                           <return-column    name="CURRENCY"/>
                   </return-property>
           </return>
           {    ?    =    call    selectAllEmployments()    }
   </sql-query>   

Notice stored procedures currently only return scalars and entities. <return-join> And <load-collection> is not supported

<hibernate-mapping package="com.unmi.vo"> <class name="Test" table="TEST"/>       

<sql-query callable="true" > 

<return alias="aa" > <return-property name="oborqt" column="OBORQT"/>

<return-property column="MOORQT"/> <return-property name="roschn" column="ROSCHN"/>

<return-property column="PLANDATE"/> 

<return> { ? = call selectAllUsers() } </sql-query> 

</hibernate-mapping>

{? = Call selectAllUsers ()} can be written as {call selectAllUsers ()}, if there are parameters to be written {? = Call selectAllUsers (?,?,?)} code in the appropriate position for the query set value OK Java code to call the following key

Session session = HibernateUtil.currentSession();

Query query = session.getNamedQuery("selectAllUsers"); 

List list = query.list();

System.out.println(list);

Ask your stored procedure must return a recordset, otherwise they will be wrong if your stored procedure is complete the task should not check the configuration file with the following three labels

<sql-insert callable="true">{call createPerson (?, ?)}</sql-insert>

<sql-delete callable="true">{? = call deletePerson (?)}</sql-delete> 

<sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update>

Simple oracle stored procedure call Hibernate mode:

this.pnumberManager.getHibernateTemplate().execute(
                new HibernateCallback() ...{
                    public Object doInHibernate(Session session)
                            throws HibernateException, SQLException ...{
                        CallableStatement cs = session
                                .connection()
                                .prepareCall("{call modifyapppnumber_remain(?)}");
                        cs.setString(1, foundationid);
                        cs.execute();
                        return null;
                    }
                });
相关文章