Re: JDBC and composite types

[Kris Jurka <books(at)ejurka(dot)com>]

On Sat, 19 May 2007, Marcin Gala wrote:

> Could anyone tell me, how to read table's columns that are of composite type? 
> I was trying to use getObject with different types of casting, however 
> nothing worked

Technically getObject on a complex type should return an instance of 
java.sql.Struct by default or an instance of java.sql.SQLData if the type 
has been mapped to a specific java class.  This has not been implemented 
in the pg driver yet though.

> :( Also getString doesn't give me a string representation of type...

It does for me.  See the attached example.

Kris Jurka

import java.sql.*;

public class CompositeType {

	public static void main(String args[]) throws Exception {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5830/jurka","jurka","");
		Statement stmt = conn.createStatement();

		try {
			stmt.execute("DROP TABLE comptable");
			stmt.execute("DROP TYPE mycomplex");
		} catch (SQLException sqle) {}

		stmt.execute("CREATE TYPE mycomplex AS (a int, b int) ");
		stmt.execute("CREATE TABLE comptable (id int, val mycomplex)");
		stmt.execute("INSERT INTO comptable VALUES (1, ROW(2,3))");

		ResultSet rs = stmt.executeQuery("SELECT id, val FROM comptable");
		rs.next();
		System.out.println(rs.getString(2));

	}

}