(How to) Make composite PGObject with Text? (Was: (How to) Make SQLData of UUID?)

*TLDR, primary question:* when I create a custom subclass of PGObject to
match a composite type containing the Text/varchar data; how do I properly
deserialize these text fields in void PGObject::setValue(String s) and how
do I properly serialize them in String PGObject::getValue()?

*More explanation (for the readers and even for myself):*

Well, seems like was my primary mistake in original email was trying to use
the SQLData/typemap approach; seems it is still not supported by
org.postgresql driver. And the primary way of using the PostgreSQL's
composite types in Java is to make a matching PGObject subclass, and bind
it through PGConnection::addDataType.

Alas, there is rather little documentation on actually doing that; and the
existing github code gives some hints, but insufficiently. I actually
managed to map the type created like CREATE TYPE MYFUNC_RETURN_TYPE AS (key
UUID) to a Java class. But then faced even a more basic issue: how to bind
a type containing the text strings (like Text or varchar)?

Imagine I have a different type CREATE TYPE MYTYPE2 AS (a TEXT, b TEXT).
To use it in Java, both ways (receiving in the ResultSets, and setting in
the prepared statements), I’ll need to write the matching Java class,
something like this:

public abstract class MyType2 extends PGobject implements Serializable,
Cloneable {
public String a;
public String b;

// Not all the necessary methods are shown

public MyType2() { setType(""); }

public MyType2(String a, String b) {
this();
this.a = a;
this.b = b;
}

@Override
public void setValue(String s) throws SQLException {
// This method will parse
// the record literal from PostgreSQL
// and set the data
}

@Override public String getValue() {
// This method will convert
// the data into the PostgreSQL-compatible
// record literal
}
}

The problems are: 1. how to parse (setValue) the serialized literal with
this type properly? 2. And more importantly, how to serialize it (getValue)
properly for PostgreSQL?
Both questions assume that the string may be as wild as it happens - any
Unicode, any single or double quotes, any combinations of backslash
characters.

*1.* Actually, I seem to have enough luck parsing the literal inside
the void setValue(String
s).

The incoming string will be something like ("ABC","DEF"); with some weird
handling of NULLs.
Create a new PGTokenizer that cuts the leading and the trailing brackets,
and that assumes the items are split with comma:
PGtokenizer t = new PGtokenizer(PGtokenizer.removePara(s), ',');
Then get both tokens:
String aRaw = t.getToken(0), bRaw = t.getToken(1);
Interestingly, there may be no getToken(1), if b is NULL. Therefore, if
t.getSize() == 1 (rather than 2), I should consider b = null, but that’s a
different story.
Then I’ll check both aRaw and bRaw. If they are empty strings, the
according field will be null, too.
And finally, I should remove the leading and trailing double quotes if they
exist; and then,.. I manually deal with the backslashes and double-quotes
as they income doubled!?
a = PGtokenizer.remove(aRaw, "\"", "\"")
// replace <""> to <">
.replaceAll("\"\"", "\"")
// replace <\\> to <\>
.replaceAll("\\\\\\\\", "\\\\");

Is that right way to deal with it, so low-level? But there seems no helper
de-escaping functions that I could use and be safe. But don’t I miss
something else to be de-escaped?..

*2.* More problem is with the serializing the data in the String getValue().
I’ll have to create the string like ("Value of a","Value of b"); if some of
them e.g. a is NULL, the string will look something like (,"Value of b").
Well, okay; I should add leading/trailing brackets and split them with
comma; and if a or b is null, put an empty string instead of it.
But how to properly escape the string itself?
The best I found is String.format("\"%s\"", Utils.escapeLiteral(null, str,
true)) – but it doesn’t seem to handle various combinations of single or
double quotes.

Any idea how to escape the strings properly then?

пн, 22 июл. 2019 г. в 00:58, Alexander Myodov <amyodov(at)gmail(dot)com>:

> Hello!
>
> I seem to miss something obvious maybe, but I cannot find a way to use
> UUID as SQLData; for example, to make a proper JDBC handling of custom
> PostgreSQL type containing an UUID.
>
> Imagine I made a custom type returned from some PL/PgSQL function:
>
> CREATE TYPE MYFUNC_RETURN_TYPE AS
>
> (
>
> key UUID
>
> );
>
>
> Now, to handle it in JDBC, I need to make a custom type like MyfuncReturnType implements
> SQLData, and then add it to the type map of the connection. In
> MyfuncReturnType, I’ll need to implement void writeSQL(SQLOutput stream)
> method, and at some point I’ll need to do something like this: to call
> either
>
> SQLOutput::writeObject(SQLData x)
>
> or
>
> SQLOutput::writeObject(Object x, SQLType targetSqlType),
>
> passing my UUID in somehow.
>
>
> But UUID doesn’t satisfy SQLData interface in any way; and if I use the
> second method, I do not have a proper SQLType for UUID anywhere.
>
>
> I previously used the pgjdbc-ng driver, and they had a collection of
> postgresql-specific SQLType's; but that driver had other problems, so I
> decided to switch to the mainstream JDBC driver, and faced this lack.
>
>
> Any hints please?
>
> --
> Alex Myodov
>

--
Alex Myodov