Jan 02

Introduction to PostgreSQL PL/Java, part 4: User Defined Types

By Bear GilesNo Comments

User-defined types in the database are controversial. They’re not standard – at some point the DBA has to create them – and this introduces portability issues. Standard tools won’t know about them. You must access them via the ‘struct’ methods in ResultSets and PreparedStatements.

On the other hand there are a LOT of things that are otherwise only supported as byte[]. This prevents database functions and stored procedures from easily manipulating them.

What would be a good user-defined type? It must be atomic and it must be possible to do meaningful work via stored procedures. N.B., a database user-defined type is not the same thing as a java class. Nearly all java classes should be stored as standard tuples and you should only use database UDTs if there’s a compelling reason.

A touchstone I like is asking whether you’re ever tempted to cache immutable information about the type, vs. about the tuple, in addition to the object itself. E.g., a X.509 digital certificate has a number of immutable fields that would be valid search terms but it’s expensive to extract that information for every row. (Sidenote: you can use triggers to extract the information when the record is inserted and updated. This ensures the cached values are always accurate.)

Examples:

  • complex numbers (stored procedures: arithmetic)
  • rational numbers (stored procedures: arithmetic)
  • galois field numbers (stored procedures: arithmetic modulo a fixed value)
  • images (stored procedures: get dimensions)
  • PDF documents (stored procedures: extract elements)
  • digital certificates and private keys (stored procedures: crypto)

Something that should also be addressed is the proper language for implementation. It’s easy to prototype in PL/Java but you can make a strong argument that types should be ultimately implemented as a standard PostgreSQL extensions since they’re more likely to be available in the future when you’re looking at a 20-year-old dump. In some important ways this is just a small part of the problem – the issue isn’t whether the actual storage and function implementation is written in C or java, it’s how it’s tied into the rest of the system.

PL/Java Implementation

A PL/Java user defined type must implement the java.sql.SQLData interface, a static method that creates the object from a String, and an instance method that creates a String from the object. These methods must complementary – it must be possible to run a value through a full cycle in either direction and get the original value back.

N.B., this is often impossible with doubles – this is why you get numbers like 4.000000001 or 2.999999999. In these cases you have do to the best you can and warn the user.

In many cases an object can be stored more efficiently in a binary format. In PostgreSQL terms these are TOAST types. This is handled by implementing two new methods that work with SQLInput and SQLOutput streams.

A simple implementation of a rational type follows.

  1. public class Rational implements SQLData {
  2.     private long numerator;
  3.     private long denominator;
  4.     private String typeName;
  5.  
  6.     public static Rational parse(String input, String typeName)
  7.             throws SQLException {
  8.         Pattern pattern = Pattern.compile("(-?[0-9]+)( */ *(-?[0-9]+))?");
  9.         Matcher matcher = pattern.matcher(input);
  10.         if (!matcher.matches()) {
  11.             throw new SQLException("Unable to parse rational from string \"" + input
  12.                     + '"');
  13.         }
  14.         if (matcher.groupCount() == 3) {
  15.             if (matcher.group(3) == null) {
  16.                 return new Rational(Long.parseLong(matcher.group(1)));
  17.             }
  18.             return new Rational(Long.parseLong(matcher.group(1)),
  19.                     Long.parseLong(matcher.group(3)));
  20.         }
  21.         throw new SQLException("invalid format: \"" + input
  22.                 + '"');
  23.     }
  24.  
  25.     public Rational(long numerator) throws SQLException {
  26.         this(numerator, 1);
  27.     }
  28.  
  29.     public Rational(long numerator, long denominator) throws SQLException {
  30.         if (denominator == 0) {
  31.             throw new SQLException("demominator must be non-zero");
  32.         }
  33.  
  34.         // do a little bit of normalization
  35.         if (denominator < 0) {
  36.             numerator = -numerator;
  37.             denominator = -denominator;
  38.         }
  39.  
  40.         this.numerator = numerator;
  41.         this.denominator = denominator;
  42.     }
  43.  
  44.     public Rational(int numerator, int denominator, String typeName)
  45.             throws SQLException {
  46.         this(numerator, denominator);
  47.         this.typeName = typeName;
  48.     }
  49.  
  50.     public String getSQLTypeName() {
  51.         return typeName;
  52.     }
  53.  
  54.     public void readSQL(SQLInput stream, String typeName) throws SQLException {
  55.         this.numerator = stream.readLong();
  56.         this.denominator = stream.readLong();
  57.         this.typeName = typeName;
  58.     }
  59.  
  60.     public void writeSQL(SQLOutput stream) throws SQLException {
  61.         stream.writeLong(numerator);
  62.         stream.writeLong(denominator);
  63.     }
  64.  
  65.     public String toString() {
  66.         String value = null;
  67.         if (denominator == 1) {
  68.             value = String.valueOf(numerator);
  69.         } else {
  70.             value = String.format("%d/%d", numerator, denominator);
  71.         }
  72.         return value;
  73.     }
  74.  
  75.     /*
  76.      * Meaningful code that actually does something with this type was
  77.      * intentionally left out.
  78.      */
  79. }
public class Rational implements SQLData {
    private long numerator;
    private long denominator;
    private String typeName;

    public static Rational parse(String input, String typeName)
            throws SQLException {
        Pattern pattern = Pattern.compile("(-?[0-9]+)( */ *(-?[0-9]+))?");
        Matcher matcher = pattern.matcher(input);
        if (!matcher.matches()) {
            throw new SQLException("Unable to parse rational from string \"" + input
                    + '"');
        }
        if (matcher.groupCount() == 3) {
            if (matcher.group(3) == null) {
                return new Rational(Long.parseLong(matcher.group(1)));
            }
            return new Rational(Long.parseLong(matcher.group(1)),
                    Long.parseLong(matcher.group(3)));
        }
        throw new SQLException("invalid format: \"" + input
                + '"');
    }

    public Rational(long numerator) throws SQLException {
        this(numerator, 1);
    }

    public Rational(long numerator, long denominator) throws SQLException {
        if (denominator == 0) {
            throw new SQLException("demominator must be non-zero");
        }

        // do a little bit of normalization
        if (denominator < 0) {
            numerator = -numerator;
            denominator = -denominator;
        }

        this.numerator = numerator;
        this.denominator = denominator;
    }

    public Rational(int numerator, int denominator, String typeName)
            throws SQLException {
        this(numerator, denominator);
        this.typeName = typeName;
    }

    public String getSQLTypeName() {
        return typeName;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        this.numerator = stream.readLong();
        this.denominator = stream.readLong();
        this.typeName = typeName;
    }

    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeLong(numerator);
        stream.writeLong(denominator);
    }

    public String toString() {
        String value = null;
        if (denominator == 1) {
            value = String.valueOf(numerator);
        } else {
            value = String.format("%d/%d", numerator, denominator);
        }
        return value;
    }

    /*
     * Meaningful code that actually does something with this type was
     * intentionally left out.
     */
}

and

  1.     /* The shell type */
  2.     CREATE TYPE javatest.rational;
  3.  
  4.     /* The scalar input function */
  5.     CREATE FUNCTION javatest.rational_in(cstring)
  6.       RETURNS javatest.rational
  7.       AS 'UDT[sandbox.Rational] input'
  8.       LANGUAGE java IMMUTABLE STRICT;
  9.  
  10.     /* The scalar output function */
  11.     CREATE FUNCTION javatest.rational_out(javatest.rational)
  12.       RETURNS cstring
  13.       AS 'UDT[sandbox.Rational] output'
  14.       LANGUAGE java IMMUTABLE STRICT;
  15.  
  16.     /* The scalar receive function */
  17.     CREATE FUNCTION javatest.rational_recv(internal)
  18.       RETURNS javatest.rational
  19.       AS 'UDT[sandbox.Rational] receive'
  20.       LANGUAGE java IMMUTABLE STRICT;
  21.  
  22.     /* The scalar send function */
  23.     CREATE FUNCTION javatest.rational_send(javatest.rational)
  24.       RETURNS bytea
  25.       AS 'UDT[sandbox.Rational] send'
  26.       LANGUAGE java IMMUTABLE STRICT;
  27.  
  28.     CREATE TYPE javatest.rational (
  29.       internallength = 16,
  30.       input = javatest.rational_in,
  31.       output = javatest.rational_out,
  32.       receive = javatest.rational_recv,
  33.       send = javatest.rational_send,
  34.       alignment = int);
    /* The shell type */
    CREATE TYPE javatest.rational;

    /* The scalar input function */
    CREATE FUNCTION javatest.rational_in(cstring)
      RETURNS javatest.rational
      AS 'UDT[sandbox.Rational] input'
      LANGUAGE java IMMUTABLE STRICT;

    /* The scalar output function */
    CREATE FUNCTION javatest.rational_out(javatest.rational)
      RETURNS cstring
      AS 'UDT[sandbox.Rational] output'
      LANGUAGE java IMMUTABLE STRICT;

    /* The scalar receive function */
    CREATE FUNCTION javatest.rational_recv(internal)
      RETURNS javatest.rational
      AS 'UDT[sandbox.Rational] receive'
      LANGUAGE java IMMUTABLE STRICT;

    /* The scalar send function */
    CREATE FUNCTION javatest.rational_send(javatest.rational)
      RETURNS bytea
      AS 'UDT[sandbox.Rational] send'
      LANGUAGE java IMMUTABLE STRICT;

    CREATE TYPE javatest.rational (
      internallength = 16,
      input = javatest.rational_in,
      output = javatest.rational_out,
      receive = javatest.rational_recv,
      send = javatest.rational_send,
      alignment = int);

Type modifiers

PostgreSQL allows types to have modifiers. Examples are in ‘varchar(200)’ or ‘numeric(8,2)’.

PL/Java does not currently support this functionality (via the ‘typmod_in’ and ‘typmod_out’ methods) but I have submitted a request for it.

Casts

Custom types aren’t particularly useful if all you can do is store and retrieve the values as opaque objects. Why not use bytea and be done with it?

In fact there are many UDTs where it makes sense to be able to cast a UDT to a different type. Numeric types, like complex or rational numbers, should be able to be converted to and from the standard integer and floating number types (albeit with limitations).

This should be done with restraint.

Casts are implemented as single argument static methods. In the java world these methods are often named newInstance so I’m doing the same here.

  1.     public static Rational newInstance(String input) throws SQLException {
  2.         if (input == null) {
  3.             return null;
  4.         }
  5.         return parse(input, "javatest.rational");
  6.     }
  7.  
  8.     public static Rational newInstance(int value) throws SQLException {
  9.         return new Rational(value);
  10.     }
  11.  
  12.     public static Rational newInstance(Integer value) throws SQLException {
  13.         if (value == null) {
  14.             return null;
  15.         }
  16.         return new Rational(value.intValue());
  17.     }
  18.  
  19.     public static Rational newInstance(long value) throws SQLException {
  20.         return new Rational(value);
  21.     }
  22.  
  23.     public static Rational newInstance(Long value) throws SQLException {
  24.         if (value == null) {
  25.             return null;
  26.         }
  27.         return new Rational(value.longValue());
  28.     }
  29.  
  30.     public static Double value(Rational value) throws SQLException {
  31.         if (value == null) {
  32.             return null;
  33.         }
  34.         return value.doubleValue();
  35.     }
    public static Rational newInstance(String input) throws SQLException {
        if (input == null) {
            return null;
        }
        return parse(input, "javatest.rational");
    }

    public static Rational newInstance(int value) throws SQLException {
        return new Rational(value);
    }

    public static Rational newInstance(Integer value) throws SQLException {
        if (value == null) {
            return null;
        }
        return new Rational(value.intValue());
    }

    public static Rational newInstance(long value) throws SQLException {
        return new Rational(value);
    }

    public static Rational newInstance(Long value) throws SQLException {
        if (value == null) {
            return null;
        }
        return new Rational(value.longValue());
    }

    public static Double value(Rational value) throws SQLException {
        if (value == null) {
            return null;
        }
        return value.doubleValue();
    }

and

  1.   CREATE FUNCTION javatest.rational_string_as_rational(varchar) RETURNS javatest.rational
  2.         AS 'sandbox.Rational.newInstance'
  3.         LANGUAGE JAVA IMMUTABLE STRICT;
  4.  
  5.   CREATE FUNCTION javatest.rational_int_as_rational(int4) RETURNS javatest.rational
  6.         AS 'sandbox.Rational.newInstance'
  7.         LANGUAGE JAVA IMMUTABLE STRICT;
  8.  
  9.   CREATE FUNCTION javatest.rational_long_as_rational(int8) RETURNS javatest.rational
  10.         AS 'sandbox.Rational.newInstance'
  11.         LANGUAGE JAVA IMMUTABLE STRICT;
  12.  
  13.   CREATE FUNCTION javatest.rational_as_double(javatest.rational) RETURNS float8
  14.         AS 'sandbox.Rational.value'
  15.         LANGUAGE JAVA IMMUTABLE STRICT;
  16.  
  17.   CREATE CAST (varchar AS javatest.rational)
  18.       WITH FUNCTION javatest.rational_string_as_rational(varchar)
  19.       AS ASSIGNMENT;
  20.  
  21.   CREATE CAST (int4 AS javatest.rational)
  22.       WITH FUNCTION javatest.rational_int_as_rational(int4)
  23.       AS ASSIGNMENT;
  24.  
  25.   CREATE CAST (int8 AS javatest.rational)
  26.       WITH FUNCTION javatest.rational_long_as_rational(int8)
  27.       AS ASSIGNMENT;
  28.  
  29.   CREATE CAST (javatest.rational AS float8)
  30.       WITH FUNCTION javatest.rational_as_double(javatest.rational)
  31.       AS ASSIGNMENT;
  CREATE FUNCTION javatest.rational_string_as_rational(varchar) RETURNS javatest.rational
        AS 'sandbox.Rational.newInstance'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE FUNCTION javatest.rational_int_as_rational(int4) RETURNS javatest.rational
        AS 'sandbox.Rational.newInstance'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE FUNCTION javatest.rational_long_as_rational(int8) RETURNS javatest.rational
        AS 'sandbox.Rational.newInstance'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE FUNCTION javatest.rational_as_double(javatest.rational) RETURNS float8
        AS 'sandbox.Rational.value'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE CAST (varchar AS javatest.rational)
      WITH FUNCTION javatest.rational_string_as_rational(varchar)
      AS ASSIGNMENT;

  CREATE CAST (int4 AS javatest.rational)
      WITH FUNCTION javatest.rational_int_as_rational(int4)
      AS ASSIGNMENT;

  CREATE CAST (int8 AS javatest.rational)
      WITH FUNCTION javatest.rational_long_as_rational(int8)
      AS ASSIGNMENT;

  CREATE CAST (javatest.rational AS float8)
      WITH FUNCTION javatest.rational_as_double(javatest.rational)
      AS ASSIGNMENT;

(Sidenote: STRICT means that the function will return NULL if any argument is NULL. This allows the database to make some optimizations.)

(Sidenote: we may only be able to use the IMMUTABLE flag if the java objects are also immutable. We should probably make our Rational objects immutable since the other numeric types are immutable.)

Aggregate Functions

What about min()? Rational numbers are a numeric type so shouldn’t they support all of the standard aggregate functions?

Defining new aggregate functions is straightforward. Simple aggregate functions only need a static member function that take two UDT values and return one. This is easy to see with maximums, minimums, sums, products, etc. More complex aggregates require an ancillary UDT that contains state information, a static method that takes one state UDT and one UDT and returns a state UDT, and a finalization method that takes the final state UDT and produces the results. This is easy to see with averages – you need a state type that contains a counter and a running sum.

Several examples of the former type of aggregate function follow.

  1.     // compare two Rational objects. We use BigInteger to avoid overflow.
  2.     public static int compare(Rational p, Rational q) {
  3.         if (p == null) {
  4.             return 1;
  5.         } else if (q == null) {
  6.             return -1;
  7.         }
  8.         BigInteger l = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator()));
  9.         BigInteger r = BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator()));
  10.         return l.compareTo(r);
  11.     }
  12.  
  13.     public static Rational min(Rational p, Rational q) {
  14.         if ((p == null) || (q == null)) {
  15.             return null;
  16.         }
  17.         return (p.compareTo(q) <= 0) ? p : q;
  18.     }
  19.  
  20.     public static Rational max(Rational p, Rational q) {
  21.         if ((p == null) || (q == null)) {
  22.             return null;
  23.         }
  24.         return (q.compareTo(p) < 0) ? p : q;
  25.     }
  26.  
  27.     public static Rational add(Rational p, Rational q) throws SQLException {
  28.         if ((p == null) || (q == null)) {
  29.             return null;
  30.         }
  31.         BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).add(
  32.                 BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
  33.         BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
  34.         BigInteger gcd = n.gcd(d);
  35.         n = n.divide(gcd);
  36.         d = d.divide(gcd);
  37.         return new Rational(n.longValue(), d.longValue());
  38.     }
    // compare two Rational objects. We use BigInteger to avoid overflow.
    public static int compare(Rational p, Rational q) {
        if (p == null) {
            return 1;
        } else if (q == null) {
            return -1;
        }
        BigInteger l = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger r = BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator()));
        return l.compareTo(r);
    }

    public static Rational min(Rational p, Rational q) {
        if ((p == null) || (q == null)) {
            return null;
        }
        return (p.compareTo(q) <= 0) ? p : q;
    }

    public static Rational max(Rational p, Rational q) {
        if ((p == null) || (q == null)) {
            return null;
        }
        return (q.compareTo(p) < 0) ? p : q;
    }

    public static Rational add(Rational p, Rational q) throws SQLException {
        if ((p == null) || (q == null)) {
            return null;
        }
        BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).add(
                BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
        BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger gcd = n.gcd(d);
        n = n.divide(gcd);
        d = d.divide(gcd);
        return new Rational(n.longValue(), d.longValue());
    }

and

  1.     CREATE FUNCTION javatest.min(javatest.rational, javatest.rational) RETURNS javatest.rational
  2.         AS 'sandbox.Rational.min'
  3.         LANGUAGE JAVA IMMUTABLE STRICT;
  4.  
  5.     CREATE FUNCTION javatest.max(javatest.rational, javatest.rational) RETURNS javatest.rational
  6.         AS 'sandbox.Rational.max'
  7.         LANGUAGE JAVA IMMUTABLE STRICT;
  8.  
  9.     CREATE AGGREGATE min(javatest.rational) (
  10.       sfunc = javatest.min,
  11.       stype = javatest.rational
  12.     );
  13.  
  14.     CREATE AGGREGATE max(javatest.rational) (
  15.       sfunc = javatest.max,
  16.       stype = javatest.rational
  17.     );
  18.  
  19.     CREATE AGGREGATE sum(javatest.rational) (
  20.       sfunc = javatest.add,
  21.       stype = javatest.rational
  22.     );
    CREATE FUNCTION javatest.min(javatest.rational, javatest.rational) RETURNS javatest.rational
        AS 'sandbox.Rational.min'
        LANGUAGE JAVA IMMUTABLE STRICT;

    CREATE FUNCTION javatest.max(javatest.rational, javatest.rational) RETURNS javatest.rational
        AS 'sandbox.Rational.max'
        LANGUAGE JAVA IMMUTABLE STRICT;

    CREATE AGGREGATE min(javatest.rational) (
      sfunc = javatest.min,
      stype = javatest.rational
    );

    CREATE AGGREGATE max(javatest.rational) (
      sfunc = javatest.max,
      stype = javatest.rational
    );

    CREATE AGGREGATE sum(javatest.rational) (
      sfunc = javatest.add,
      stype = javatest.rational
    );

Integration with Hibernate

It is possible to link PL/Java user-defined types and Hibernate user-defined types. Warning: the hibernate code is highly database-specific.

This is the hibernate user-defined type. PostgreSQL 9.1 does not support the STRUCT type and uses strings instead. We don’t have to use the PL/Java user-defined data type to perform the marshaling but it ensures consistency. TheDbRationalType is the Rational class above. The same class could be used in both places but would introduce dependency on a Hibernate interface into the PL/Java class. This may be acceptable if you extract that single interface from the Hibernate source code.

  1. public class Rational implements UserType, Serializable {
  2.     private final int[] sqlTypesSupported = new int[] { Types.OTHER };
  3.     private long numerator;
  4.     private long denominator;
  5.  
  6.     public Rational() {
  7.         numerator = 0;
  8.         denominator = 1;
  9.     }
  10.  
  11.     public Rational(long numerator, long denominator) {
  12.         this.numerator = numerator;
  13.         this.denominator = denominator;
  14.     }
  15.  
  16.     public long getNumerator() {
  17.         return numerator;
  18.     }
  19.  
  20.     public long getDenominator() {
  21.         return denominator;
  22.     }
  23.  
  24.     @Override
  25.     public Object assemble(Serializable cached, Object owner)
  26.             throws HibernateException {
  27.         if (!(cached instanceof Rational)) {
  28.             throw new HibernateException("invalid argument");
  29.         }
  30.         Rational r = (Rational) cached;
  31.         return new Rational(r.getNumerator(), r.getDenominator());
  32.     }
  33.  
  34.     @Override
  35.     public Serializable disassemble(Object value) throws HibernateException {
  36.         if (!(value instanceof Rational)) {
  37.             throw new HibernateException("invalid argument");
  38.         }
  39.         return (Rational) value;
  40.     }
  41.  
  42.     @Override
  43.     public Object deepCopy(Object value) throws HibernateException {
  44.         if (value == null) {
  45.             return null
  46.         }
  47.         if (!(value instanceof Rational)) {
  48.             throw new HibernateException("invalid argument");
  49.         }
  50.         Rational v = (Rational) value;
  51.         return new Rational(v.getNumerator(), v.getDenominator());
  52.     }
  53.  
  54.     @Override
  55.     public boolean isMutable() {
  56.         return true;
  57.     }
  58.  
  59.     //
  60.     // important: PGobject is postgresql-specific
  61.     //
  62.     @Override
  63.     public Object nullSafeGet(ResultSet rs, String[] names, Object owners)
  64.             throws HibernateException, SQLException {
  65.         PGobject pgo = (PGobject) rs.getObject(names[0]);
  66.         if (rs.wasNull()) {
  67.             return null;
  68.         }
  69.         TheDbRationalType r = TheDbRationalType.parse(pgo.getValue(), "rational");
  70.         return new Rational(r.getNumerator(), r.getDenominator());
  71.     }
  72.  
  73.     //
  74.     // important: using Types.OTHER may be postgresql-specific
  75.     //
  76.     @Override
  77.     public void nullSafeSet(PreparedStatement ps, Object value, int index)
  78.             throws HibernateException, SQLException {
  79.         if (value == null) {
  80.             ps.setNull(index, Types.OTHER);
  81.         } else if (!(value instanceof Rational)) {
  82.             throw new HibernateException("invalid argument");
  83.         } else {
  84.             Rational t = (Rational) value;
  85.             ps.setObject(index,
  86.                     new TheDbRationalType(t.getNumerator(), t.getDenominator()), Types.OTHER);
  87.         }
  88.     }
  89.  
  90.     @Override
  91.     public Object replace(Object original, Object target, Object owner)
  92.             throws HibernateException {
  93.         if (!(original instanceof Rational)
  94.                 || !(target instanceof Rational)) {
  95.             throw new HibernateException("invalid argument");
  96.         }
  97.         Rational r = (Rational) original;
  98.         return new Rational(r.getNumerator(), r.getDenominator());
  99.     }
  100.  
  101.     @Override
  102.     public Class returnedClass() {
  103.         return Rational.class;
  104.     }
  105.  
  106.     @Override
  107.     public int[] sqlTypes() {
  108.         return sqlTypesSupported;
  109.     }
  110.  
  111.     @Override
  112.     public String toString() {
  113.         String value = "";
  114.         if (denominator == 1) {
  115.             value = String.valueOf(numerator);
  116.         } else {
  117.             value = String.format("%d/%d", numerator, denominator);
  118.         }
  119.         return value;
  120.     }
  121.  
  122.     // for UserType
  123.     @Override
  124.     public int hashCode(Object value) {
  125.         Rational r = (Rational) value;
  126.         return (int) (31 * r.getNumerator() + r.getDenominator());
  127.     }
  128.  
  129.     @Override
  130.     public int hashCode() {
  131.         return hashCode(this);
  132.     }
  133.  
  134.     // for UserType
  135.     @Override
  136.     public boolean equals(Object left, Object right) {
  137.         if (left == right) {
  138.             return true;
  139.         }
  140.         if ((left == null) || (right == null)) {
  141.             return false;
  142.         }
  143.         if (!(left instanceof Rational) || !(right instanceof Rational)) {
  144.             return false;
  145.         }
  146.  
  147.         Rational l = (Rational) left;
  148.         Rational r = (Rational) right;
  149.         return (l.getNumerator() == r.getNumerator())
  150.                 && (l.getDenominator() == r.getDenominator());
  151.     }
  152.  
  153.     @Override
  154.     public boolean equals(Object value) {
  155.         return equals(this, value);
  156.     }
  157. }
public class Rational implements UserType, Serializable {
    private final int[] sqlTypesSupported = new int[] { Types.OTHER };
    private long numerator;
    private long denominator;

    public Rational() {
        numerator = 0;
        denominator = 1;
    }

    public Rational(long numerator, long denominator) {
        this.numerator = numerator;
        this.denominator = denominator;
    }

    public long getNumerator() {
        return numerator;
    }

    public long getDenominator() {
        return denominator;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        if (!(cached instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        Rational r = (Rational) cached;
        return new Rational(r.getNumerator(), r.getDenominator());
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        if (!(value instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        return (Rational) value;
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) {
            return null
        }
        if (!(value instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        Rational v = (Rational) value;
        return new Rational(v.getNumerator(), v.getDenominator());
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    //
    // important: PGobject is postgresql-specific
    //
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owners)
            throws HibernateException, SQLException {
        PGobject pgo = (PGobject) rs.getObject(names[0]);
        if (rs.wasNull()) {
            return null;
        }
        TheDbRationalType r = TheDbRationalType.parse(pgo.getValue(), "rational");
        return new Rational(r.getNumerator(), r.getDenominator());
    }

    //
    // important: using Types.OTHER may be postgresql-specific
    //
    @Override
    public void nullSafeSet(PreparedStatement ps, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            ps.setNull(index, Types.OTHER);
        } else if (!(value instanceof Rational)) {
            throw new HibernateException("invalid argument");
        } else {
            Rational t = (Rational) value;
            ps.setObject(index,
                    new TheDbRationalType(t.getNumerator(), t.getDenominator()), Types.OTHER);
        }
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        if (!(original instanceof Rational)
                || !(target instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        Rational r = (Rational) original;
        return new Rational(r.getNumerator(), r.getDenominator());
    }

    @Override
    public Class returnedClass() {
        return Rational.class;
    }

    @Override
    public int[] sqlTypes() {
        return sqlTypesSupported;
    }

    @Override
    public String toString() {
        String value = "";
        if (denominator == 1) {
            value = String.valueOf(numerator);
        } else {
            value = String.format("%d/%d", numerator, denominator);
        }
        return value;
    }

    // for UserType
    @Override
    public int hashCode(Object value) {
        Rational r = (Rational) value;
        return (int) (31 * r.getNumerator() + r.getDenominator());
    }

    @Override
    public int hashCode() {
        return hashCode(this);
    }

    // for UserType
    @Override
    public boolean equals(Object left, Object right) {
        if (left == right) {
            return true;
        }
        if ((left == null) || (right == null)) {
            return false;
        }
        if (!(left instanceof Rational) || !(right instanceof Rational)) {
            return false;
        }

        Rational l = (Rational) left;
        Rational r = (Rational) right;
        return (l.getNumerator() == r.getNumerator())
                && (l.getDenominator() == r.getDenominator());
    }

    @Override
    public boolean equals(Object value) {
        return equals(this, value);
    }
}

CustomTypes.hbm.xml

  1. <?xml version='1.0' encoding='utf-8'?>
  2. <!DOCTYPE hibernate-mapping PUBLIC
  3.        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  4.        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
  5.  
  6. <hibernate-mapping>
  7.  
  8.     <typedef name="javatest.rational" class="sandbox.RationalType"/>
  9.  
  10. </hibernate-mapping>
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

    <typedef name="javatest.rational" class="sandbox.RationalType"/>

</hibernate-mapping>

TestTable.hbm.xml

  1. <?xml version='1.0' encoding='utf-8'?>
  2. <!DOCTYPE hibernate-mapping PUBLIC
  3.        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  4.        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
  5.  
  6. <hibernate-mapping>
  7.  
  8.     <class name="sandbox.TestTable" table="test_table">
  9.         <id name="id"/>
  10.         <property name="value" type="javatest.rational" />
  11.     </class>
  12.  
  13. </hibernate-mapping>
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

    <class name="sandbox.TestTable" table="test_table">
        <id name="id"/>
        <property name="value" type="javatest.rational" />
    </class>

</hibernate-mapping>

More Information

Creating a Scalar UDT in Java (user guide)

CREATE AGGREGATE documentation (PostgreSQL)

CREATE CAST documentation (PostgreSQL)

CREATE TYPE documentation (PostgreSQL)

CREATE OPERATOR documentation (PostgreSQL) (We’ll discuss this next time – it’s how you define tests for equality, etc.)

CREATE OPERATOR CLASS documentation (PostgreSQL) (We’ll discuss this next time – it’s how you define indexes.)

Interfacing user-defined types to indexes (PostgreSQL) (We’ll discuss this next time.)

java

Leave your Comment

Blue Taste Theme created by Jabox