JdbcTemplate calls the stored procedure. Enter the parameter array to return to the cursor

Note: The source of This article is the jdbctemplate call stored procedure. Enter the parameter array to return to Cursor "

Demand:

Java passes in a list object. The relevant data is found and returned from the database.

If you loop through the data, you produce n SQL. (n is the size of list)

Start planning to use temporary tables, first insert the list data into the database. You can use BULK INSERT, although you also need to produce n SQL, but performance should be much faster than n queries. (but no practice).

Colleagues remind that you can use stored procedures, bulk query. It hasn't been used before. Start thinking to use in (ID1, ID2) so query. Previous stored procedures have not been used. Most of them are directly operating SQL.

Implementation procedure 1) Create type

  1 CREATE OR REPLACE TYPE test_object as OBJECT  2 (  3    COLUMN1 VARCHAR2 (50), c8>  4    COLUMN2 number (10,2)  5 )

2) Create type of TABLE

  1 CREATE OR REPLACE TYPE "test_object_array" as TABLE of Test_object

3) Create a stored procedure

  1 CREATE OR REPLACE PROCEDURE proc_dd_getpersoninfo (v_test_list in Test_object,  2 p_cur out S Ys_refcursor  3 )  4  5 The entry parameter is one that can become an array, used in the same time as a temporary table.   6  7 SELECT * from TABLE (v_test_list)  8

4) Writing Java code

1@Repository2   Public classDatacachedaoimplImplementsDatacachedao {3 4@Autowired5JdbcTemplate JdbcTemplate;6 7     PrivateARRAY GetArray (list<testobject> List, Connection con)throwsSQLException {8struct[] Struts =NewStruct[list.size ()];9          inti = 0;Ten           for(Testobject cr:list) { One  AObject[] Obs = {Cr.getcontractnumber (), Cr.getreceiveamount ()}; -              //mapping to Object -Structdescriptor st =NewStructdescriptor ("Test_object", con); theStruts[i] =NewSTRUCT (St, con, OBS); -} -  -         //mapping to Array +Arraydescriptor arraydept = Arraydescriptor.createdescriptor ("Test_object_array", con); -ARRAY Deptarrayobject =NewARRAY (arraydept, con, struts); +          returnDeptarrayobject; A} at  -@Override -       PublicList<testobjectresponse> getcontractrepaymentlist (list<testobject> List) { -  -         returnJdbctemplate.execute (NewCallableStatementCreator () { -  in@Override -               PublicCallableStatement createcallablestatement (Connection con)throwsSQLException { to  +                 if(Con.iswrapperfor (OracleConnection.class)) { -con = Con.unwrap (oracleconnection.class); the} *  $String callprocedure = "{call Proc_dd_getpersoninfo (?,?)}";Panax NotoginsengCallableStatement cs = Con.preparecall (callprocedure); -Array array = GetArray (list, con); the  +Cs.setarray (1, array); ACs.registeroutparameter (2, oracletypes.cursor); the  +                 returnCs -} $  $},NewCallablestatementcallback<list<testobjectresponse>> () { -  -@Override the               PublicList<testobjectresponse> doincallablestatement (CallableStatement cs) -                      throwsSQLException, DataAccessException {Wuyi  thelist<testobjectresponse> list =NewArraylist<> (); -  WuCs.execute (); -  AboutResultSet rs = (ResultSet) cs.getobject (2); $                   while(Rs.next ()) { -Testobjectresponse response =NewTestobjectresponse (); -                      //Assemble your bean -List.add (response); A} +                  returnList the} -}); $} the} the 

The

JdbcTemplate calls the stored procedure. Enter the parameter array to return to the cursor