My problem: UserType for PostgreSQL BIT(n) works only with hibernate native connection, but fails with poolable connection (defined in Tomcat).
I worked out how to map PostgreSQL BIT(n) type (
http://www.postgresql.org/docs/8.1/inte ... e-bit.html) with java.util.BitSet (using UserType interface of course). It works fine for hibernate native connection, but not for poolable connection (defined in Tomcat). More specifically I have problem with setting informations with PreparedStatement.setObject(.., .., java.sql.Types.OTHER). I must use setObject() because SQL query should look like "... B'0101010101010010101010000' ...". That's why setString() isn't good enough (among others it puts additional quotes on both sides of string).
Please! Help! I was working on this for several days but I have stuck. I have read a lot of documentation, articles, blogs etc. I think I know PreparedStatement and Connection API by heart already ;) I have no more ideas. I'm desperated and frustrated. Appreciate any help...
If you think I should not use BIT(n) type, please, read notes at the end of my message.
OK, here are results of my work for hibernate native connection:
hibernate.cfg.xml :
Code:
<property name="connection.url">jdbc:postgresql://localhost/my_db_name</property>
<property name="connection.username">user</property>
<property name="connection.password">pass</property>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
annotation in my VO (value object) class:
Code:
@org.hibernate.annotations.Type(
type = "myProject.dao.userType.PGBitStringUserType"
)
@Column(unique=true)
private BitSet code;
My DAO class is classic DAO using GenericDAO pattern. Nothing unusual. I has one additional method:
Code:
public FooVO findByCode(BitSet bs) {
Query q;
if (bs==null) {
q = getSession().createQuery("from FooVO where code = null");
} else {
q = getSession().createQuery("from FooVO where code = :code");
q.setParameter("code", bs, Hibernate.custom(PGBitStringUserType.class));
}
return (IngredientVO)q.uniqueResult();
}
and my UserType class:
Code:
public class PGBitStringUserType implements UserType {
public PGBitStringUserType() {
super();
}
public int[] sqlTypes() {
return new int[]{java.sql.Types.OTHER};
}
public Class returnedClass() {
return BitSet.class;
}
public Object assemble(Serializable cached, Object owner) throws HibernateException {
return cached;
}
public Serializable disassemble(Object o) throws HibernateException {
return (Serializable) o;
}
public Object deepCopy(Object o) throws HibernateException {
return o == null ? null : ((BitSet)o).clone();
}
public boolean equals(Object o, Object o1) throws HibernateException {
return (o == null) ? (o1 == null) : o.equals(o1);
}
public int hashCode(Object o) throws HibernateException {
return o==null ? 0 : o.hashCode();
}
public boolean isMutable() {
return false;
}
public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
String value = (String)Hibernate.STRING.nullSafeGet(rs, names[0]);
if (value == null)
return null;
BitSet bs = new BitSet( 800 );
int pos = value.indexOf("1", 0);
while (pos != -1) {
bs.set(pos);
pos = value.indexOf("1", pos+1);
}
return bs;
}
public void nullSafeSet(PreparedStatement statement, Object o, int i) throws HibernateException, SQLException {
if (o == null) {
statement.setNull(i, java.sql.Types.OTHER);
} else {
BitSet bs = (BitSet) o;
StringBuffer value = new StringBuffer();
for (int k=0; k<bs.length(); k++)
value.append(bs.get(k)==true ? '1' : '0');
while (value.length()<800)
value.append('0');
statement.setObject(i, value.toString(), java.sql.Types.OTHER);
}
}
public Object replace(Object original, Object target, Object owner) throws HibernateException {
return original;
}
}
PS: I have also prepared additional class:
"public class PGBitString extends PGobject implements Serializable, Cloneable"
but have doscovered that it's not used at all (probably because I do not use ResultSet.getObject()) and everything works without it as well...
Then I wanted to move on and to change hibernate.cfg.xml settings to use poolable connection (defined in Tomcat):Code:
<property name="hibernate.connection.datasource">java:comp/env/jdbc/myProject</property>
Of course it caused Exceptions in nullSafeSet() method caused by statement.setObject().
I tried to fix it so I changed nullSafeSet() method and added some debug code as well:
Code:
public class PGBitStringUserType implements UserType {
[...]
public void nullSafeSet(PreparedStatement statement, Object o, int i) throws HibernateException, SQLException {
if (o == null) {
statement.setNull(i, java.sql.Types.OTHER);
} else {
// some informations from debug process:
// statement is of type Jdbc3PreparedStatement for hibernate connection
// and org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement type for poolable connection
// statement.getConnection() is of type org.postgresql.jdbc3.Jdbc3Connection for hibernate connection
// and org.apache.tomcat.dbcp.dbcp.PoolableConnection type for poolable connection
BitSet bs = (BitSet) o;
PGBitSet PGbs = new PGBitSet(bs); // PGBitSet definition is beneath
statement.setObject(i, PGbs, java.sql.Types.OTHER);
}
}
}
Code:
public class PGBitSet implements SQLData {
public BitSet bs;
private String sql_type;
public PGBitSet(BitSet bs) {
this.bs = bs;
}
public String getSQLTypeName() throws SQLException {
return sql_type;
}
public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
String value = stream.readString();
if (value == null) {
this.bs = null;
} else {
BitSet bs = new BitSet( 800 );
int pos = value.indexOf("1", 0);
while (pos != -1) {
bs.set(pos);
pos = value.indexOf("1", pos+1);
}
this.bs = bs;
}
}
public void writeSQL(SQLOutput stream) throws SQLException {
StringBuffer value = new StringBuffer();
for (int k=0; k<bs.length(); k++)
value.append(bs.get(k)==true ? '1' : '0');
while (value.length()<800)
value.append('0');
stream.writeString("B'" + value.toString() + "'");
}
}
First thing: PGBitSet seems to be not used... but why??? Documentation for PreparedStatement.setObject() says: "The given Java object will be converted to the given targetSqlType before being sent to the database. If the object has a custom mapping (is of a class implementing the interface SQLData), the JDBC driver should call the method SQLData.writeSQL to write it to the SQL data stream.".
Second thing: I got Exceptions when I try to add or find something by the code field (every operation for which SQL query contains condition for code field).
Code:
ERROR puszka.web.action.ExceptionHandler- ERROR: could not execute query
[...]
Caused by: org.postgresql.util.PSQLException: Cannot cast an instance of puszka.dao.userType.PGBitSet to type Types.OTHER
at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1677)
at org.postgresql.jdbc3.AbstractJdbc3Statement.setObject(AbstractJdbc3Statement.java:1435)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1686)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:162)
at puszka.dao.userType.PGBitStringUserType.nullSafeSet(PGBitStringUserType.java:111)
at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:156)
at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:38)
at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:491)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1563)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
... 35 more
PGBitStringUserType.java:111 is:
statement.setObject(i, PGbs, java.sql.Types.OTHER);
By the way: searching by id works fine - it's because query doesn't contain code field (and nullSafeSet() method isn't called).
I can ommit problems for find method - changing findByCode() method in my DAO class is good enough:
Code:
public FooVO findByCode(BitSet bs) {
Query q;
if (bs==null) {
q = getSession().createQuery("from FooVO where code = null");
} else {
StringBuffer value = new StringBuffer();
for (int k=0; k<bs.length(); k++)
value.append(bs.get(k)==true ? '1' : '0');
while (value.length()<800)
value.append('0');
return findSingleResultByCriteria(Expression.sql("code=B'" + value.toString() + "'"));
}
return (IngredientVO)q.uniqueResult();
}
And as far as code field may be generated on database side it's not even necessary to map it in VO class at all. I don't like this solutions but it suits my needs. Nevertheless I would prefer to do it right. Can you help me?
And now one more explanation: why Bit(n)?
Well, my goal is to use PostgreSQL BIT(n) to represent some object which is composed of elements. I have already about 500 elements and I estimate that max.number of elements will not be greater than 800. I also need bit operations to compare my objects, to find objects with specified elements etc. That's why Bit(n) seems to fulfill my needs.
Each object is composed of 2 to 30 elements, so I also have considered to use arrays. But I have found that mapping arrays isn't easy as well :-/
Any other suggestions? Or maybe someone have managed to map arrays?
I would appreciate ANY HELP. Thanks in advance.
Login
FAQ
Search