Announcement Announcement Module
Collapse
No announcement yet.
How can i pass arraylist to stored procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can i pass arraylist to stored procedure

    can anybody help me in giving sample code & guidance as how to pass a arraylist of java objects to a stored procedure to oracle database using spring


    Thanks in advance

  • #2
    Haven't worked much with stored procedure but since you passing parameters as a map, you can simply get the values from the array and place them inside the map used by the stored procedure.

    Comment


    • #3
      Take a look at the following post. It gives you a couple of options of accomplishing this. The sample code is based on call to the following procedure:

      Code:
      CREATE OR REPLACE TYPE numbers AS TABLE OF NUMBER;
      /
      
      CREATE OR REPLACE PROCEDURE reserve_seats(in_performance_id in number,
                  in_seats in numbers,
                  in_price in number,
                  in_reserved_until in date,
                  out_new_booking_id out number)
      is
      begin
        -- Get a new pk for the booking table
        select booking_seq.nextval into out_new_booking_id from dual;
      
        -- Create a new booking
        insert into booking(id, date_made, price, reserved_until)
           values (out_new_booking_id, sysdate, in_price, in_reserved_until);
      
        for i in 1..in_seats.count loop
          insert into seat_status (Seat_id, Performance_id)
             values(in_seats(i), in_performance_id);
          update seat_status set Booking_id = out_new_booking_id
                      where Seat_id = in_seats(i)
                      and Performance_id = in_performance_id;
        end loop;
      end;
      /

      Comment


      • #4
        Thanks for ur reply.
        But its throwing a "Invalid Pattern" exception.

        i am creating a TABLE of Oracle object type Like this:

        create or replace type Contact_OBJ_ARRAY as table
        of Contact_OBJ;

        where the Contact_OBJ is defined as a oracle type like:

        CREATE OR REPLACE TYPE Contact_OBJ AS OBJECT (
        id number ,
        firstname varchar2(30),
        lastName varchar2(30),
        email varchar2(30)
        )


        I have a bean called Contact in java like:

        public class Contact {

        private long id;
        private String firstName;
        private String lastName;
        private String email;

        /**setter & getter for the fields*/
        }

        Then my oracle stored procedure looks like following with one in parameter :

        CREATE OR REPLACE PROCEDURE
        Contact_OBJ_ARRAY_PROC ( p_obj_array in Contact_OBJ_ARRAY ) AS
        begin
        //do some work
        end;

        And i have extended the AbstractsqlType class also:

        public class MyObjectArray extends AbstractSqlTypeValue {

        private ArrayList values;



        public MyObjectArray(ArrayList values) {
        this.values = values;

        }

        public Object createTypeValue(Connection con, int sqlType, String typeName)
        throws SQLException {
        OracleConnection oracle = (OracleConnection) ((org.apache.commons.dbcp.PoolableConnection) con)
        .getDelegate();
        HashMap map=new HashMap();
        try{
        map.put("CONTACT_OBJ",Class.forName("Contact"));
        }
        catch(Exception e){
        e.printStackTrace();
        }
        oracle.setTypeMap(map);
        oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor(typeName, oracle);

        return new oracle.sql.ARRAY(desc, oracle, (Contact[]) values
        .toArray(new Contact[values.size()]));
        }
        }

        I am calling the porocedure as:

        //iNSTANTIATE THE STORED PROC CLASS

        final StoredProcedure proc=new StoredProcedure(getJdbcTemplate(),"Contact_OBJ_ARR AY_PROC"){};

        HashMap param=new HashMap();

        //create a new arraylist

        ArrayList list=new ArrayList();

        //create Contact objects & populate them

        Contact c=new Contact();
        c.setName("xyz");

        //finally add the contact object to the list

        list.add(c);

        //declare the sotred proc parameters

        proc.declareParameter(new SqlParameter("param1",TYPES.ARRAY,"Contact_OBJ_ARR AY") );
        param.put("param1",list);

        proc.compile();

        //finally execute the proc by passing the hashmap to the execute method

        proc.execute(param);

        //IN FACT I HAVE A OracleSpringStoredProcedureDAO.executeProcedure() METHOD WHICH DOES ALL THIS.

        ****************************************
        EXCEPTION TRACE
        ***************************************
        Exception in thread "main"
        org.springframework.jdbc.UncategorizedSQLException :
        CallableStatementCallback; uncategorized SQLException for SQL [{call CONTACT_OBJ_ARRAY_PROC(?)}]; SQL state [null];
        error code [17074]; invalid name pattern: ATS.CONTACT_OBJ_ARRAY;
        nested exception is java.sql.SQLException: invalid name pattern: ATS.CONTACT_OBJ_ARRAY


        Regards
        bob





        Originally posted by trisberg View Post
        Take a look at the following post. It gives you a couple of options of accomplishing this. The sample code is based on call to the following procedure:

        Code:
        CREATE OR REPLACE TYPE numbers AS TABLE OF NUMBER;
        /
        
        CREATE OR REPLACE PROCEDURE reserve_seats(in_performance_id in number,
                    in_seats in numbers,
                    in_price in number,
                    in_reserved_until in date,
                    out_new_booking_id out number)
        is
        begin
          -- Get a new pk for the booking table
          select booking_seq.nextval into out_new_booking_id from dual;
        
          -- Create a new booking
          insert into booking(id, date_made, price, reserved_until)
             values (out_new_booking_id, sysdate, in_price, in_reserved_until);
        
          for i in 1..in_seats.count loop
            insert into seat_status (Seat_id, Performance_id)
               values(in_seats(i), in_performance_id);
            update seat_status set Booking_id = out_new_booking_id
                        where Seat_id = in_seats(i)
                        and Performance_id = in_performance_id;
          end loop;
        end;
        /

        Comment


        • #5
          I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):
          Code:
          StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
          List structList = new ArrayList();
          for (Contact c : list) {
              Object[] attributes = {c.id;
                  c.firstName
                  c.lastName
                  c.email};
              STRUCT s1 = new STRUCT(sdesc, con, attributes);
              structList.add(s1);
          }
          Then pass in the structList as your parameter value:

          Code:
          proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
          param.put("param1", structList);
          I think this might work

          Comment


          • #6
            Thanks a lot.
            I will try that.

            Originally posted by trisberg View Post
            I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):
            Code:
            StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
            List structList = new ArrayList();
            for (Contact c : list) {
                Object[] attributes = {c.id;
                    c.firstName
                    c.lastName
                    c.email};
                STRUCT s1 = new STRUCT(sdesc, con, attributes);
                structList.add(s1);
            }
            Then pass in the structList as your parameter value:

            Code:
            proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
            param.put("param1", structList);
            I think this might work

            Comment


            • #7
              still not working

              Hi
              I have created a struct as cited by u. but still its giving the same error.Here is my final class which does all the stuff:

              import java.sql.Connection;
              import java.sql.SQLException;
              import java.sql.Types;
              import java.util.ArrayList;
              import java.util.List;
              import java.util.Map;
              import java.util.HashMap;

              import org.apache.commons.dbcp.BasicDataSource;

              import oracle.sql.StructDescriptor;
              import oracle.sql.STRUCT;

              import org.springframework.jdbc.core.JdbcTemplate;
              import org.springframework.jdbc.core.SqlOutParameter;
              import org.springframework.jdbc.core.SqlTypeValue;
              import org.springframework.jdbc.core.support.AbstractSqlT ypeValue;
              import org.springframework.jdbc.object.StoredProcedure;
              import org.springframework.jdbc.support.nativejdbc.Common sDbcpNativeJdbcExtractor;

              public class ContactTest {
              private void execute() throws Exception {
              // some DI will be welcome here
              BasicDataSource ds = new BasicDataSource();
              ds.setDriverClassName("oracle.jdbc.driver.OracleDr iver");
              ds.setUrl("jdbc:oracle:thin:@c5899:1521B");
              ds.setUsername("uas");
              ds.setPassword("pas");
              JdbcTemplate template = new JdbcTemplate(ds);
              CommonsDbcpNativeJdbcExtractor nje = new CommonsDbcpNativeJdbcExtractor();
              template.setNativeJdbcExtractor(nje);

              SqlTypeValue param = new AbstractSqlTypeValue() {
              protected Object createTypeValue(Connection con, int sqlType,
              String typeName) throws SQLException {

              StructDescriptor sdesc = StructDescriptor.createDescriptor(
              "Contact_OBJ", con);
              List structList = new ArrayList();

              ArrayList conactObjlist = new ArrayList();
              /** ***first OBj** */
              Contact c = new Contact();
              c.setId(6666);
              c.setFirstName("hdx");
              c.setLastName("k");
              c.setEmail("[email protected]");
              conactObjlist.add(c);
              /** **2nd obj** */
              Contact c1 = new Contact();
              c1.setId(1111);
              c1.setFirstName("hari");
              c1.setLastName("h");
              c1.setEmail("[email protected]");
              conactObjlist.add(c1);

              for (int i = 0; i < conactObjlist.size(); i++) {

              Contact c2 = (Contact) conactObjlist.get(i);
              Object[] attributes = { new Long(c2.getId()),
              c2.getFirstName(), c2.getLastName(), c2.getEmail() };
              STRUCT p1 = new STRUCT(sdesc, con, attributes);
              structList.add(p1);
              }
              return structList;
              }
              };
              Map params = new HashMap();
              params.put("pDemo", param);
              SumStoredProcedure sproc = new SumStoredProcedure(template,
              "Contact_OBJ_ARRAY_PROC");
              sproc.execute(params);
              ds.close();
              }

              public static void main(String[] args) throws Exception {
              new ContactTest().execute();
              }

              private class SumStoredProcedure extends StoredProcedure {

              public SumStoredProcedure(JdbcTemplate jdbcTemplate, String name) {

              setJdbcTemplate(jdbcTemplate);
              setSql(name);
              declareParameter(new SqlParameter("pDemo", Types.ARRAY,
              "Contact_OBJ_ARRAY"));
              compile();
              }
              }
              }



              Originally posted by trisberg View Post
              I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):
              Code:
              StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
              List structList = new ArrayList();
              for (Contact c : list) {
                  Object[] attributes = {c.id;
                      c.firstName
                      c.lastName
                      c.email};
                  STRUCT s1 = new STRUCT(sdesc, con, attributes);
                  structList.add(s1);
              }
              Then pass in the structList as your parameter value:

              Code:
              proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
              param.put("param1", structList);
              I think this might work
              Last edited by bob; Jan 11th, 2007, 02:24 AM.

              Comment


              • #8
                Pls help....

                Originally posted by trisberg View Post
                I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):
                Code:
                StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
                List structList = new ArrayList();
                for (Contact c : list) {
                    Object[] attributes = {c.id;
                        c.firstName
                        c.lastName
                        c.email};
                    STRUCT s1 = new STRUCT(sdesc, con, attributes);
                    structList.add(s1);
                }
                Then pass in the structList as your parameter value:

                Code:
                proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
                param.put("param1", structList);
                I think this might work

                Comment


                • #9
                  If you can post a working example of how you would solve this using plain JDBC code, then I can help you translate this into Spring.

                  Also, if you use CODE tags around your code, then formatting is preserved

                  Comment


                  • #10
                    Originally posted by trisberg View Post
                    If you can post a working example of how you would solve this using plain JDBC code, then I can help you translate this into Spring.

                    Also, if you use CODE tags around your code, then formatting is preserved
                    Hi
                    As per our discussions regarding passing arraylist of oblects to stored procedure ,i am posting a example of jdbc code which is passing arraylist
                    to stored procs .Please help me in converting the code in spring.I am getting the same exception(the one we discussed earlier) while i am trying with spring.
                    Here is my bean class that implements java.sql.SQLData interface .
                    Code:
                    import java.io.Serializable;
                    import java.sql.SQLData;
                    import java.sql.SQLException;
                    import java.sql.SQLInput;
                    import java.sql.SQLOutput;
                    
                    public class EmployeeObj implements SQLData
                     {
                      private String sql_type="TS.TEST_EMP_OBJ";
                      public int empNo;
                      public String empName;
                      
                    
                      public EmployeeObj() 
                      { 
                      }
                                                                                // line 14
                    public EmployeeObj (String sql_type, int empNo, String empName)
                      {
                        this.sql_type = sql_type;
                        this.empNo = empNo;
                        this.empName = empName;
                        
                       }                                                        // line 20
                    
                      ////// implements SQLData //////
                     
                      // define a get method to return the SQL type of the object  line 24
                      public String getSQLTypeName() throws SQLException
                      { 
                        return sql_type; 
                      }                                                         // line 28
                     
                      // define the required readSQL() method                      line 30
                      public void readSQL(SQLInput stream, String typeName)
                        throws SQLException
                      {
                        sql_type = typeName;
                        empName = stream.readString();
                        empNo = stream.readInt();
                      }  
                      // define the required writeSQL() method                     line 39
                      public void writeSQL(SQLOutput stream)
                        throws SQLException
                      { 
                    	  stream.writeInt(empNo);
                        stream.writeString(empName);
                       
                      }
                    }
                    Here is the class that calls the stored proc:
                    Code:
                    import java.sql.CallableStatement;
                    import java.sql.Connection;
                    import java.sql.DriverManager;
                    import java.sql.SQLException;
                    import java.util.Dictionary;
                    
                    import oracle.jdbc.driver.OracleTypes;
                    import oracle.sql.ARRAY;
                    import oracle.sql.ArrayDescriptor;
                    
                    public class TestProc {
                    public static void main(String args[]){
                    	Connection con=null;
                    	try{
                    		 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
                    	con = DriverManager.getConnection("jdbc:oracle:thin:@c5899:1521:DB", "username", "password");
                    	String query = "{call TEST_EMP_OBJ_ARRAY_PROC (?)}";
                    	
                    	
                    	CallableStatement cs = con.prepareCall(query);
                    	ArrayDescriptor des = ArrayDescriptor.createDescriptor("TEST_EMP_OBJ_ARRAY", con);
                    	EmployeeObj emp=new EmployeeObj("TS.TEST_EMP_OBJ",234,"hello");
                    	EmployeeObj emp1=new EmployeeObj("TS.TEST_EMP_OBJ",235,"bye");
                    	Object[] employees= new Object[]{emp,emp1};
                    	ARRAY a = new ARRAY(des, con, employees);
                    	cs.setObject(1, (Object)a);
                    	cs.execute();
                    	if (cs != null) {
                    		cs.close();
                         }
                    	}
                    	catch(SQLException e){
                    		e.printStackTrace();
                    	}
                    	
                    	
                    }}
                    where TS.TEST_EMP_OBJ is the oracle object type for java object employeeobj.
                    TEST_EMP_OBJ_ARRAY is the array of TEST_EMP_OBJ  in oracle
                    Last edited by bob; Feb 22nd, 2007, 10:09 PM.

                    Comment


                    • #11
                      Originally posted by trisberg View Post
                      If you can post a working example of how you would solve this using plain JDBC code, then I can help you translate this into Spring.

                      Also, if you use CODE tags around your code, then formatting is preserved
                      Hi
                      Please help me to convert the jdbc stored proc to spring.


                      Regards
                      Bob

                      Comment


                      • #12
                        Originally posted by trisberg View Post
                        If you can post a working example of how you would solve this using plain JDBC code, then I can help you translate this into Spring.

                        Also, if you use CODE tags around your code, then formatting is preserved
                        Hi
                        I had already prepared a jdbc code to call stored procedure but while converting the jdbc code into spring its throwing exception.As we have discussed earlier,could you kindly help me to convert the jdbc code into spring.


                        Thanks in advance.

                        Regards
                        bob

                        Comment


                        • #13
                          Bob,

                          I'll take a crack at it the next couple of days.

                          Comment


                          • #14
                            Originally posted by trisberg View Post
                            Bob,

                            I'll take a crack at it the next couple of days.
                            Thanks a lot.I am eagerly waitng for the post.

                            Regards
                            bob

                            Comment


                            • #15
                              I have just came across the same issue. If I get resolved I'll post here. Otherwise it'd be great if the original OP solved the problem to update the posting.

                              Comment

                              Working...
                              X