Calling Stored Procedure with list of values

[Chinababu Illa]

Hi All,

I have a requirement like this. I have set of applications (application is a java object). Every application has a set of fields (Field is a java object). Every field has set of attributes. I need to store multiple fields for a specified application at one go.
I have a stored procedure with following signature.

PROCEDURE FIELDS_UPDATE
(formatupd             IN      FIELDS_TYP,
 O_msg                   OUT     VARCHAR2)

IS
---
---

END

Where FIELDS_TYP is an OBJECT defined as follows. [I removed and renamed some of the fields to make it simple]

CREATE OR REPLACE TYPE abc.FIELDS_OBJECT AS OBJECT
(
N_id                     NUMBER,
N_app_id              NUMBER,
N_field_nm           VARCHAR2(32),
N_field_data_typ   NUMBER,
N_field_label         VARCHAR2(80),
)
/

CREATE type FIELDS_TYP AS TABLE OF FIELDS_OBJECT
/

This stored procedure can take list of field objects in single invocation.

How should invoke this stored procedure with a list of field objects from java code in single invocation using Spring 3 JDBC module. I am not using any of the ORM tools.
Note: Since I want to write a generic code, I am not supposed to use any of the database specific APIs like ARRAY from Oracle.

Please suggest me some solution.

Thanks,
Chinna

[Bill Gorder]

Have you read the documentation?

http://static.springsource.org/spring/docs/3.1.x/s...c.html#jdbc-simple-jdbc-call-1
http://static.springsource.org/spring/docs/3.1.x/s...c.html#jdbc-simple-jdbc-call-3
http://static.springsource.org/spring/docs/3.1.x/s...jdbc.html#jdbc-StoredProcedure
http://static.springsource.org/spring/docs/3.1.x/s...l/jdbc.html#jdbc-complex-types

You might also want to take a peek at
http://www.springsource.org/spring-data/jdbc-extensions

And another thread that seems to be doing the same thing here:
http://forum.springsource.org/showthread.php?33053-How-can-i-pass-arraylist-to-stored-procedure

[Chinababu Illa]

Hi Bill,

I have read the documentation, but I didn't completely understand. There is very little information in the documentation about calling stored procedure with list of objects.
But, now my problem is resolved after going though one of your links and the spring documentation together. I could not do it anyway without Oracle's ARRAY and STRUCT types.

What I have actually done is, I created STRUCT object for each set of input values and kept them in ARRAY object and passed this ARRAY object to stored procedure. Now it worked.

For anyone's reference, I am posting complete working code below.

package com.test;

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

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor;

public class ContactTest {
	private void execute() throws Exception {
		// some DI will be welcome here
		BasicDataSource ds = new BasicDataSource();
		ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		ds.setUrl("jdbc:oracle:thin:@localhost:1521:testdb");
		ds.setUsername("user1");
		ds.setPassword("password");
		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(
						"WRIMPFORMATFIELDS_OBJECT", con);
				STRUCT[] structList = new STRUCT[2];

				ArrayList<FormatField> fieldObjlist = new ArrayList<FormatField>();
				/** ***first OBj** */
				FormatField f1 = new FormatField();
				f1.setId(123);
				f1.setAppId(22);
				f1.setDefaultValue("abc");
				f1.setEndCol(10);
				f1.setStartCol(1);
				f1.setFormatId(1);
				f1.setFieldOrder(1);
				f1.setSelected(true);
				
				fieldObjlist.add(f1);
				/** **2nd obj** */
				FormatField f2 = new FormatField();
				f2.setId(234);
				f2.setAppId(22);
				f2.setDefaultValue("abc");
				f2.setEndCol(10);
				f2.setStartCol(1);
				f2.setFormatId(1);
				f2.setSelected(true);
				f2.setFieldOrder(2);
				fieldObjlist.add(f2);
				ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, con);
				int i=0;
				for (FormatField field : fieldObjlist) {
					Object[] attributes = { new Long(field.getId()),
							new Long(field.getAppId()), new Long(field.getFormatId()), new Long(field.getFieldOrder()), new Long(field.getStartCol()), new Long(field.getEndCol()), field.getDefaultValue(), new Long(1) };
					STRUCT p1 = new STRUCT(sdesc, con, attributes);
					structList[i++] = p1;
				}
				
				ARRAY inArray = new ARRAY(arrayDescriptor, con, structList);
				return inArray;
			}
		};
		
		Map params = new HashMap();
		params.put("pDemo", param);
		SumStoredProcedure sproc = new SumStoredProcedure(template,
				"WRIMPORTS.WRIMPFORMATFIELDS_INSERT");
		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", OracleTypes.ARRAY,
					"WRIMPFORMATFIELDS_TYP"));
			declareParameter(new SqlOutParameter("O_msg", Types.VARCHAR));
			compile();
		}
	}
}

Thanks Bill,


-Chinna

[Bill Gorder]

Chinna,

Your welcome and thanks for posting back with your solution.