Thread: Mapping SQL UDT to java class

Mapping SQL UDT to java class

From
"Shen, Ning (NSHEN)"
Date:

Hi

 

We have some user defined data types in our database. For instance, ICAO type is defined as follow:

 

CREATE TYPE "ICAO" AS

   ("Type" smallint,

"Addr" bytea);

 

In my java client side, I have:

 

public class ICAO implements SQLData {

 

            @Override

            public void readSQL(SQLInput aStream, String aTypeName)

            {

                            typeName = aTypeName;

                           

                            try {

                                            icaoType = aStream.readShort();

                                            icaoAddr = aStream.readBytes();

                            }

                            catch (SQLException e) {

                                            e.printStackTrace();

                            }

            }

           

            @Override

            public void writeSQL(SQLOutput aStream)

            {

                            try {

                                            aStream.writeShort(icaoType);

                                            aStream.writeBytes(icaoAddr);

                            }

                            catch (SQLException e) {

                                            e.printStackTrace();

                            }

            }

           

            @Override

            public String getSQLTypeName()

            {

                            return typeName;

            }

 

            public void setType(short aType)

            {

                            icaoType = aType;

            }

           

            public void setAddr(String aHexAddr)

            {

                            icaoAddr = numToBytes(hexToInt(aHexAddr));

            }

 

private short icaoType;  

private byte[] icaoAddr;

private String typeName = "ICAO";

 

 

            // ******* unit test driver

            public static void main(String[] args)

            {

                            final String AVLC_SELECT = "SELECT \"RSSI\", \"SymbolCount\", \"ReedSolErr\", \"Quality\", \"FlagCount\", \"LowConfidence\", \"BrokenMsg\", \"BadCRCCount\", \"AVLC\".\"DateTime\", \"TimeStamp\", \"LineNum\", \"SourceFiles\".\"Station\", \"AVLC\".\"Src\", \"Dest\", \"AVLC\".\"Msg\" AS \"AVLCMsg\", \"SQP\".\"Msg\" AS \"SQPMsg\", \"AVLC\".\"RadioAddr\", \"FileName\", \"LogType\", \"SentRecv\", \"AG\", \"CR\", \"P\", \"F\", \"Type\", \"NS\", \"NR\", \"Score\", \"Delay\", \"SREJPairs\", \"XIDType\", \"Information\", \"XIDInfo\", \"BlockID\", \"Label\", \"Address\", \"MSN\", \"Text\", \"Agency\", \"Flight\", Stations.\"RadioNum\", Stations.\"Freq\", COALESCE(Stations.\"Char\", '?') \"Char\", \"UpDownOther\"(\"AVLC\".\"RadioAddr\", \"AVLC\".\"Src\", \"Dest\"), \"RFLength\"(\"AVLC\".\"Msg\"), \"ULReportInd\".\"DateTime\" \"ULReportIndDateTime\", \"CSMADecisionTime\", \"TM1\", \"TM2\", \"TM3\", \"p\""

                                                            + "FROM \"AVLC\""

                                                            + "NATURAL JOIN \"SourceFiles\""

                                                            + "LEFT OUTER JOIN \"SQP\" USING(\"RadioAddr\", \"SQPFileNum\", \"SQPLineNum\")"

                                                            + "LEFT OUTER JOIN \"ULReportInd\" USING(\"RadioAddr\", \"ULReportIndFileNum\", \"ULReportIndLineNum\")"

                                                            + "LEFT OUTER JOIN \"ARINC618\" USING(\"FileNum\", \"LineNum\")"

                                                            + "LEFT OUTER JOIN Stations USING(\"Station\", \"RadioAddr\")";

                            final String AVLC_AC_WHERE = "WHERE (\"Dest\" = ? OR \"Src\" = ?) AND \"AVLC\".\"DateTime\" BETWEEN ? AND ?";

                            final String STATIONS = "WITH Stations AS"

                                                            + "("

                                                            + "SELECT DISTINCT \"Station\", \"RadioAddr\", \"RadioNum\", \"Freq\", \"Char\""

                                                            + "FROM \"Radios\""

                                                            + "NATURAL JOIN \"SourceFiles\""

                                                            + "LEFT OUTER JOIN \"InstChar\" USING(\"Freq\")"

                                                            + ")";

                           

                            String query = STATIONS + AVLC_SELECT + AVLC_AC_WHERE;

                           

                            ICAO clnpIcao = new ICAO();

                            clnpIcao.setType((short)1);

                            clnpIcao.setAddr("400AE7");

                           

                            Connection pgConn = (new VdlPgDBConnection("Field20151015")).getConnection();

                           

                            try {

                                            PreparedStatement stm = pgConn.prepareStatement(query);

 

Map<String, Class<?>> newMap = pgConn.getTypeMap();

                                            if (newMap == null)

                                            {

                                                            newMap = new java.util.HashMap<String, Class<?>>();

                                            }

                                            newMap.put("public.ICAO", ICAO.class);

                                            pgConn.setTypeMap(newMap);

 

                                            Timestamp st = Timestamp.valueOf("2015-10-15 10:00:00.0");

                                            Timestamp et = Timestamp.valueOf("2015-10-15 12:00:00.0");

                                           

                                            stm.setObject(1, clnpIcao);

                                            stm.setObject(2, clnpIcao);

                                            stm.setTimestamp(3,st);

                                            stm.setTimestamp(4,st);

                                           

                                            ResultSet result = stm.executeQuery();

                                           

                                            List<List<Object>> table = new ArrayList<List<Object>>();

                                            List<Object> row = new ArrayList<Object>();

                                            Object colData;

                                           

                                            while (result.next())

                                            {

                                                            for (int i = 1; i <= result.getMetaData().getColumnCount(); i++)

                                                            {

                                                                            colData = result.getObject(i);

                                                                            if (colData != null)

                                                                                            row.add(colData);

                                                                            else

                                                                                            row.add("<null>");

                                                            }

                                                           

                                                            System.out.print("\n row = " + row);

                                                           

                                                            table.add(row);

                                            }

                                           

                            }

                            catch (SQLException e)

                            {

                                            e.printStackTrace();

                            }

            }   // ******* end of main()

}   // end of ICAO class

 

 

The error message I got is:

 

Can't infer the SQL type to use for an instance of vdlmenu.ICAO. Use setObject() with an explicit Types value to specify the type to use.

 

However, for the overloaded setObject((int parameterIndex, Object x, SQLType targetSqlType))  and setObject(int parameterIndex, Object x, int targetSqlType),

What is the value for the 3rd parameter?

 

Thanks

 

Ning

Attachment

Re: Mapping SQL UDT to java class

From
Dave Cramer
Date:
I don't have time at the moment to test this, but I'd try Types.OTHER.

Candidly I don't expect this to work as we don't implement SQLData 

I'd love to see this implemented in the driver though.

If you are really stuck you can try https://github.com/impossibl/pgjdbc-ng


On 12 November 2015 at 12:10, Shen, Ning (NSHEN) <NSHEN@arinc.com> wrote:

Hi

 

We have some user defined data types in our database. For instance, ICAO type is defined as follow:

 

CREATE TYPE "ICAO" AS

   ("Type" smallint,

"Addr" bytea);

 

In my java client side, I have:

 

public class ICAO implements SQLData {

 

            @Override

            public void readSQL(SQLInput aStream, String aTypeName)

            {

                            typeName = aTypeName;

                           

                            try {

                                            icaoType = aStream.readShort();

                                            icaoAddr = aStream.readBytes();

                            }

                            catch (SQLException e) {

                                            e.printStackTrace();

                            }

            }

           

            @Override

            public void writeSQL(SQLOutput aStream)

            {

                            try {

                                            aStream.writeShort(icaoType);

                                            aStream.writeBytes(icaoAddr);

                            }

                            catch (SQLException e) {

                                            e.printStackTrace();

                            }

            }

           

            @Override

            public String getSQLTypeName()

            {

                            return typeName;

            }

 

            public void setType(short aType)

            {

                            icaoType = aType;

            }

           

            public void setAddr(String aHexAddr)

            {

                            icaoAddr = numToBytes(hexToInt(aHexAddr));

            }

 

private short icaoType;  

private byte[] icaoAddr;

private String typeName = "ICAO";

 

 

            // ******* unit test driver

            public static void main(String[] args)

            {

                            final String AVLC_SELECT = "SELECT \"RSSI\", \"SymbolCount\", \"ReedSolErr\", \"Quality\", \"FlagCount\", \"LowConfidence\", \"BrokenMsg\", \"BadCRCCount\", \"AVLC\".\"DateTime\", \"TimeStamp\", \"LineNum\", \"SourceFiles\".\"Station\", \"AVLC\".\"Src\", \"Dest\", \"AVLC\".\"Msg\" AS \"AVLCMsg\", \"SQP\".\"Msg\" AS \"SQPMsg\", \"AVLC\".\"RadioAddr\", \"FileName\", \"LogType\", \"SentRecv\", \"AG\", \"CR\", \"P\", \"F\", \"Type\", \"NS\", \"NR\", \"Score\", \"Delay\", \"SREJPairs\", \"XIDType\", \"Information\", \"XIDInfo\", \"BlockID\", \"Label\", \"Address\", \"MSN\", \"Text\", \"Agency\", \"Flight\", Stations.\"RadioNum\", Stations.\"Freq\", COALESCE(Stations.\"Char\", '?') \"Char\", \"UpDownOther\"(\"AVLC\".\"RadioAddr\", \"AVLC\".\"Src\", \"Dest\"), \"RFLength\"(\"AVLC\".\"Msg\"), \"ULReportInd\".\"DateTime\" \"ULReportIndDateTime\", \"CSMADecisionTime\", \"TM1\", \"TM2\", \"TM3\", \"p\""

                                                            + "FROM \"AVLC\""

                                                            + "NATURAL JOIN \"SourceFiles\""

                                                            + "LEFT OUTER JOIN \"SQP\" USING(\"RadioAddr\", \"SQPFileNum\", \"SQPLineNum\")"

                                                            + "LEFT OUTER JOIN \"ULReportInd\" USING(\"RadioAddr\", \"ULReportIndFileNum\", \"ULReportIndLineNum\")"

                                                            + "LEFT OUTER JOIN \"ARINC618\" USING(\"FileNum\", \"LineNum\")"

                                                            + "LEFT OUTER JOIN Stations USING(\"Station\", \"RadioAddr\")";

                            final String AVLC_AC_WHERE = "WHERE (\"Dest\" = ? OR \"Src\" = ?) AND \"AVLC\".\"DateTime\" BETWEEN ? AND ?";

                            final String STATIONS = "WITH Stations AS"

                                                            + "("

                                                            + "SELECT DISTINCT \"Station\", \"RadioAddr\", \"RadioNum\", \"Freq\", \"Char\""

                                                            + "FROM \"Radios\""

                                                            + "NATURAL JOIN \"SourceFiles\""

                                                            + "LEFT OUTER JOIN \"InstChar\" USING(\"Freq\")"

                                                            + ")";

                           

                            String query = STATIONS + AVLC_SELECT + AVLC_AC_WHERE;

                           

                            ICAO clnpIcao = new ICAO();

                            clnpIcao.setType((short)1);

                            clnpIcao.setAddr("400AE7");

                           

                            Connection pgConn = (new VdlPgDBConnection("Field20151015")).getConnection();

                           

                            try {

                                            PreparedStatement stm = pgConn.prepareStatement(query);

 

Map<String, Class<?>> newMap = pgConn.getTypeMap();

                                            if (newMap == null)

                                            {

                                                            newMap = new java.util.HashMap<String, Class<?>>();

                                            }

                                            newMap.put("public.ICAO", ICAO.class);

                                            pgConn.setTypeMap(newMap);

 

                                            Timestamp st = Timestamp.valueOf("2015-10-15 10:00:00.0");

                                            Timestamp et = Timestamp.valueOf("2015-10-15 12:00:00.0");

                                           

                                            stm.setObject(1, clnpIcao);

                                            stm.setObject(2, clnpIcao);

                                            stm.setTimestamp(3,st);

                                            stm.setTimestamp(4,st);

                                           

                                            ResultSet result = stm.executeQuery();

                                           

                                            List<List<Object>> table = new ArrayList<List<Object>>();

                                            List<Object> row = new ArrayList<Object>();

                                            Object colData;

                                           

                                            while (result.next())

                                            {

                                                            for (int i = 1; i <= result.getMetaData().getColumnCount(); i++)

                                                            {

                                                                            colData = result.getObject(i);

                                                                            if (colData != null)

                                                                                            row.add(colData);

                                                                            else

                                                                                            row.add("<null>");

                                                            }

                                                           

                                                            System.out.print("\n row = " + row);

                                                           

                                                            table.add(row);

                                            }

                                           

                            }

                            catch (SQLException e)

                            {

                                            e.printStackTrace();

                            }

            }   // ******* end of main()

}   // end of ICAO class

 

 

The error message I got is:

 

Can't infer the SQL type to use for an instance of vdlmenu.ICAO. Use setObject() with an explicit Types value to specify the type to use.

 

However, for the overloaded setObject((int parameterIndex, Object x, SQLType targetSqlType))  and setObject(int parameterIndex, Object x, int targetSqlType),

What is the value for the 3rd parameter?

 

Thanks

 

Ning


Re: Mapping SQL UDT to java class

From
Vladimir Sitnikov
Date:
Dave is right, SQLData is not yet supported by pgjdbc.

Shen, Ning> it seems impossible to query these UDTs and use the
functions defined in the server without JDBC’s support for the UDT.

That is not quite true.
You can always send/receive arbitrary structures via setString/getString.

Our company uses that a lot with great success (modulo manual escaping
of arrays and structs).
For trivial types like "smallint + bytea" it should be rather simple.

Vladimir


Re: Mapping SQL UDT to java class

From
Kevin Wooten
Date:

I'd love to see this implemented in the driver though.

If you are really stuck you can try https://github.com/impossibl/pgjdbc-ng


This feature (SQLData and complex types) is what started the journey that has become… pgjdbc-ng. 

Shen,  looking at the code below it should work fine with the pgjdbc-ng driver.



On 12 November 2015 at 12:10, Shen, Ning (NSHEN) <NSHEN@arinc.com> wrote:

Hi

 

We have some user defined data types in our database. For instance, ICAO type is defined as follow:

 

CREATE TYPE "ICAO" AS

   ("Type" smallint,

"Addr" bytea);

 

In my java client side, I have:

 

public class ICAO implements SQLData {

 

            @Override

            public void readSQL(SQLInput aStream, String aTypeName)

            {

                            typeName = aTypeName;

                           

                            try {

                                            icaoType = aStream.readShort();

                                            icaoAddr = aStream.readBytes();

                            }

                            catch (SQLException e) {

                                            e.printStackTrace();

                            }

            }

           

            @Override

            public void writeSQL(SQLOutput aStream)

            {

                            try {

                                            aStream.writeShort(icaoType);

                                            aStream.writeBytes(icaoAddr);

                            }

                            catch (SQLException e) {

                                            e.printStackTrace();

                            }

            }

           

            @Override

            public String getSQLTypeName()

            {

                            return typeName;

            }

 

            public void setType(short aType)

            {

                            icaoType = aType;

            }

           

            public void setAddr(String aHexAddr)

            {

                            icaoAddr = numToBytes(hexToInt(aHexAddr));

            }

 

private short icaoType;  

private byte[] icaoAddr;

private String typeName = "ICAO";

 

 

            // ******* unit test driver

            public static void main(String[] args)

            {

                            final String AVLC_SELECT = "SELECT \"RSSI\", \"SymbolCount\", \"ReedSolErr\", \"Quality\", \"FlagCount\", \"LowConfidence\", \"BrokenMsg\", \"BadCRCCount\", \"AVLC\".\"DateTime\", \"TimeStamp\", \"LineNum\", \"SourceFiles\".\"Station\", \"AVLC\".\"Src\", \"Dest\", \"AVLC\".\"Msg\" AS \"AVLCMsg\", \"SQP\".\"Msg\" AS \"SQPMsg\", \"AVLC\".\"RadioAddr\", \"FileName\", \"LogType\", \"SentRecv\", \"AG\", \"CR\", \"P\", \"F\", \"Type\", \"NS\", \"NR\", \"Score\", \"Delay\", \"SREJPairs\", \"XIDType\", \"Information\", \"XIDInfo\", \"BlockID\", \"Label\", \"Address\", \"MSN\", \"Text\", \"Agency\", \"Flight\", Stations.\"RadioNum\", Stations.\"Freq\", COALESCE(Stations.\"Char\", '?') \"Char\", \"UpDownOther\"(\"AVLC\".\"RadioAddr\", \"AVLC\".\"Src\", \"Dest\"), \"RFLength\"(\"AVLC\".\"Msg\"), \"ULReportInd\".\"DateTime\" \"ULReportIndDateTime\", \"CSMADecisionTime\", \"TM1\", \"TM2\", \"TM3\", \"p\""

                                                            + "FROM \"AVLC\""

                                                            + "NATURAL JOIN \"SourceFiles\""

                                                            + "LEFT OUTER JOIN \"SQP\" USING(\"RadioAddr\", \"SQPFileNum\", \"SQPLineNum\")"

                                                            + "LEFT OUTER JOIN \"ULReportInd\" USING(\"RadioAddr\", \"ULReportIndFileNum\", \"ULReportIndLineNum\")"

                                                            + "LEFT OUTER JOIN \"ARINC618\" USING(\"FileNum\", \"LineNum\")"

                                                            + "LEFT OUTER JOIN Stations USING(\"Station\", \"RadioAddr\")";

                            final String AVLC_AC_WHERE = "WHERE (\"Dest\" = ? OR \"Src\" = ?) AND \"AVLC\".\"DateTime\" BETWEEN ? AND ?";

                            final String STATIONS = "WITH Stations AS"

                                                            + "("

                                                            + "SELECT DISTINCT \"Station\", \"RadioAddr\", \"RadioNum\", \"Freq\", \"Char\""

                                                            + "FROM \"Radios\""

                                                            + "NATURAL JOIN \"SourceFiles\""

                                                            + "LEFT OUTER JOIN \"InstChar\" USING(\"Freq\")"

                                                            + ")";

                           

                            String query = STATIONS + AVLC_SELECT + AVLC_AC_WHERE;

                           

                            ICAO clnpIcao = new ICAO();

                            clnpIcao.setType((short)1);

                            clnpIcao.setAddr("400AE7");

                           

                            Connection pgConn = (new VdlPgDBConnection("Field20151015")).getConnection();

                           

                            try {

                                            PreparedStatement stm = pgConn.prepareStatement(query);

 

Map<String, Class<?>> newMap = pgConn.getTypeMap();

                                            if (newMap == null)

                                            {

                                                            newMap = new java.util.HashMap<String, Class<?>>();

                                            }

                                            newMap.put("public.ICAO", ICAO.class);

                                            pgConn.setTypeMap(newMap);

 

                                            Timestamp st = Timestamp.valueOf("2015-10-15 10:00:00.0");

                                            Timestamp et = Timestamp.valueOf("2015-10-15 12:00:00.0");

                                           

                                            stm.setObject(1, clnpIcao);

                                            stm.setObject(2, clnpIcao);

                                            stm.setTimestamp(3,st);

                                            stm.setTimestamp(4,st);

                                           

                                            ResultSet result = stm.executeQuery();

                                           

                                            List<List<Object>> table = new ArrayList<List<Object>>();

                                            List<Object> row = new ArrayList<Object>();

                                            Object colData;

                                           

                                            while (result.next())

                                            {

                                                            for (int i = 1; i <= result.getMetaData().getColumnCount(); i++)

                                                            {

                                                                            colData = result.getObject(i);

                                                                            if (colData != null)

                                                                                            row.add(colData);

                                                                            else

                                                                                            row.add("<null>");

                                                            }

                                                           

                                                            System.out.print("\n row = " + row);

                                                           

                                                            table.add(row);

                                            }

                                           

                            }

                            catch (SQLException e)

                            {

                                            e.printStackTrace();

                            }

            }   // ******* end of main()

}   // end of ICAO class

 

 

The error message I got is:

 

Can't infer the SQL type to use for an instance of vdlmenu.ICAO. Use setObject() with an explicit Types value to specify the type to use.

 

However, for the overloaded setObject((int parameterIndex, Object x, SQLType targetSqlType))  and setObject(int parameterIndex, Object x, int targetSqlType),

What is the value for the 3rd parameter?

 

Thanks

 

Ning