Member Menu
 
 Monthly JBoss newsletter:
 
Hibernate Books
CaveatEmptor

Oracle table types (varray) in where clause

This is extension of Use Oracle Object Types with Hibernate (http://www.hibernate.org/261.html).

As per the article I created a table type in oracle as follows

CREATE TYPE ILIST AS TABLE OF VARCHAR2(100);

and created a class IList.java to map it with hibernate as follows

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.sql.ARRAY;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class IList implements UserType{

    private static final int[] SQL_TYPES = { Types.ARRAY };

    public int[] sqlTypes() {
        return SQL_TYPES;
    }

    public Class returnedClass() {
        return ARRAY.class;
    }

    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y)
            return true;
        else if (x == null || y == null)
            return false;
        else
            return x.equals(y);
    }

    public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)
            throws HibernateException, SQLException {
        ARRAY result = null;
        ARRAY array = (ARRAY) resultSet.getArray(names[0]);
        if (!resultSet.wasNull())
            result = array;
        return result;
    }

    public void nullSafeSet(PreparedStatement statement, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null)
            statement.setNull(index, SQL_TYPES[0]);
        else {
            statement.setArray(index, (ARRAY) value);
        }
    }

    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    public boolean isMutable() {
        return false;
    }

}

But for me the main issue was how I can use a column of type ILIST in a table in where clause of a select query. For that I wrote following custom oracle funstion (its just a prototype)

CREATE OR REPLACE FUNCTION doesContain (collection ILIST, val varchar2) RETURN NUMBER
IS
i INTEGER := 1;
flag NUMBER := 0;
BEGIN
    if collection.count()> 0 then
        Loop
        if(collection(i) = val) then 
           flag := 1;
        end if;
        Exit when (flag = 1 or i = collection.count());
        i := i+1;              
        END LOOP;   
    end if;
    RETURN flag; 
END doesContain;

and then used this function in HQL like any other oracle function and it worked. For example

select bu.buName, bu.buSicCodes from BusinessUnit bu where doescontain(bu.buSicCodes, 1000)=1

Here buSicCodes is of type IList.

© Copyright 2006, Red Hat Middleware, LLC. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc. [Privacy Policy]