Oracle.sql.STRUCT

[bluedolphincp]

I ran into a problem when learning Spring. Can somebody please help me?
I defined a Type in Oracle:

create type Address_type as object
    (street varchar2(50),
    city varchar2(25),
    state char(2),
    zip number);

and used this Type in a Table:

SQL> describe service_type;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(18)

 NAME                                               VARCHAR2(200)

 COMMENTS                                           VARCHAR2(500)

 ADDRESS                                            ADDRESS_TYPE

Then in my application code, I tried to get Address using JdbcTemplate:

public class OracleObjectTest extends TestCase
{
	private JdbcTemplate jt;

	protected void setUp() throws Exception
	{
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
		dataSource.setUrl("jdbc:oracle:thin:@172.16.9.158:1521:sdh");
		dataSource.setUsername("R4");
		dataSource.setPassword("R4");

		jt = new JdbcTemplate(dataSource);
	}

	class Address
	{
		String street, city, state;

		long zipcode;
		
		public void setCity(String city)
		{
			this.city = city;
		}
		
		public void setState(String state)
		{
			this.state = state;
		}
		
		public void setStreet(String street)
		{
			this.street = street;
		}
		
		public void setZipcode(long zipcode)
		{
			this.zipcode = zipcode;
		}
		public String toString()
		{
			return "street:" + street + " city:" + city + " state:" + state
					+ " zipcode:" + zipcode;
		}

	}

	public void testQuery()
	{
		String queryStr = "select address from service_type where id= ? ";
		final List addresses = new ArrayList();
		jt.query(queryStr, new Object[] { new Long(1) },
				new RowCallbackHandler()
				{
					public void processRow(ResultSet rs) throws SQLException
					{					
						STRUCT addressStruct = (STRUCT) rs.getObject(1);
						if (addressStruct == null)
							return;

						String street = (String) (addressStruct.getAttributes()[0]);
						String city = (String) addressStruct.getAttributes()[1]
								.toString();
						String state = (String) addressStruct.getAttributes()[2]
								.toString();
						long zipCode = Long.parseLong(addressStruct
								.getAttributes()[3].toString());
						String addressStr = street + ", " + city + ", " + state
								+ " " + zipCode;

						Address address = new Address();
						address.setStreet(street);
						address.setCity(city);
						address.setState(state);
						address.setZipcode(zipCode);

						addresses.add(address);
					}
				});

		for (Iterator it = addresses.iterator(); it.hasNext();)
		{
			Address address = (Address) it.next();
			System.out.println(address);
		}
	}
}

The message printed on the console is:

street:0x6131 city:0x6231 state:0x6331 zipcode:1111

How can i get a readable String instead of a hexadecimal?

Also, I tried to update the table with following:

public void testUpdate()
	{
		String updateStr = "UPDATE service_type SET address = ? "
				+ "WHERE id = ?";
		jt.update(updateStr, new PreparedStatementSetter()
		{
			public void setValues(PreparedStatement ps) throws SQLException
			{
				Address address = new Address();
				address.setStreet("t1");
				address.setCity("t2");
				address.setState("t3");
				address.setZipcode(12222);
				ps.setObject(1, address);
				ps.setLong(2, 1);
			}
		});
	}

This time an exception was thrown out:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE service_type SET address = ? WHERE id = ?]; SQL state [null]; error code [17004]; 无效的列类型; nested exception is java.sql.SQLException: 无效的列类型
java.sql.SQLException: 无效的列类型
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2886)
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3015)
	at OracleObjectTest$2.setValues(OracleObjectTest.java:120)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:695)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:476)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:691)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:753)

Thanks very much.

[mreuvers]

Hi,

Your fetching of the object type seems to be correct, though you don't need to do an extra toString when retrieving an attribute, at least not for the strings.

String street = (String)struct.getAttributes()[0];

I use some types too and they work fine (no hexadecimal output). It might be your nls settings in the database, the version, or the version of drivers you use, the jdk etc. This is not a spring problem, I suggest you visit the otn.oracle.com forums for this question.

As for the update, you need to use a STRUCT. The way you use it now, it is only valid for REF type of objects (you need to implement a required interface then to your business object).

Regards,
Martijn

[infomercialscams]

There is a good knowledge for the software. Oracle, sql, struct is a very hard to use because that software is basically used by net developers only..