Ibatis.net call Oracle stored over return cursor sys_refcursor result set

Recently developed a set of programs using the Ibatis.net framework and Oracle 11g. One of the requirements is to query the dataset of the specified condition through a stored procedure.

But in the process of development encountered a problem, the problem is as follows:

1. How do I execute a stored procedure through ibatis.net?

2. How do I pass parameters when I execute a stored procedure through ibatis.net?

3. How do the DbType and type correspond when passing parameters during a stored procedure through ibatis.net?

What is the corresponding dbtype for the cursor collection returned in the 4.oracle stored procedure sys_refcursor?

5. How can the Sys_refcursor cursor data collection be serialized and stored in the Ilist<t> collection after the stored procedure is executed through ibatis.net?

The following is an overview of the code structure of my program, mainly divided into the following layers:

1.ENT solid layer, database tables, views, datasets and entity classes

2.PRL persistence layer, data persistence, warehousing, library, etc.

3.SER service layer, package persistence layer

4.WEB front end, map files, DAO files, and other config files as well as front end.

Here are answers to the above questions:

The 1.SER layer encapsulates the call method to the calling stored procedure and executes to the end, calling the Queryforlist<t> (Statementname, Parameterobject) method in Ibatis.net. (e.g. method)

(Figure I)

2.ibatis.net in the framework of the transfer of parameters to be divided into two blocks, 1 is the XML file configuration to pass parameters, and the other is constructed in figure one of the parameterobjec.

First Block: Storedprocedure.xml configuration file, construct configuration parameters.

(Figure II)

Second BLOCK: Constructs the hashtable of the PARAMETEROBJEC parameter.

(Figure III)

3. With respect to dbtype and type relationships, the DBTYPE is only the data type in the Oracle data, type only the data type that the data in the database is converted to. Net. (as can be seen from figure II)

4. In Ibatis.net, the default is to use Odp.net to call the Oracle database to do the encapsulation, the corresponding dbtype for the refcursor, rather than directly fill out the Sys_refcursor this type, to remove the previous sys_.

5. Now that the parameters have been processed, the data set can also be obtained, how to serialize the resulting SYS_REFCURSOR cursor data collection to the corresponding entity set? This time is also achieved through the configuration in the Storedprocedure.xml configuration file.

(Figure IV)

Finally, how to configure the call stored procedure in the Storedprocedure.xml file. as follows:

(Figure V)

Several parameters to be aware of:

1.id= "PROEVENTTYPE2TJ", which is a parameter that constructs a unique SQL statement and cannot be duplicated.

2.parametermap= "Proeventtypepara", which refers to the parameters of the configuration file configuration in issue 2.

3.resultmap= "Proveventtype2tjresult", the data set will be obtained from the Oracle cursor Sys_refcursor, compared to the class specified in the Resultmap. Assigns each of the cursors in the cursor to the attributes of the mapped entity class, completes the serialization, and populates the data into the Ilist<> collection.

Ibatis.net call Oracle stored over return cursor sys_refcursor result set