The database have an attribute with type BIT(1) where the value can be '0'/'1', I have an hibernate entity that have an attribute:

  private Boolean attribute;

What i tried:

• Use @Type
• use Boolean/boolean
• use String
• Use a Converter
• Use a custom type(https://www.baeldung.com/hibernate-custom-types)

But I only receive errors like

ERROR: column  is of type bit but expression is of type boolean
ERROR: column  is of type bit but expression is of type bytea
ERROR: column is of type bit but expression is of type various
ERROR: column is of type bit but expression is of type string

CodePudding user response：

At first glance, I would try using the following @Column definition for your field:

@Column(columnDefinition="bit")
private Boolean attribute;

If it doesn't work, although I am aware that you mentioned you tested it in your question, try defining a custom @Type. The idea is based on this other SO question, the following test case, and this article from Vlad Mihalcea.

Your custom type definition could be as follows:

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

public class PostgresBit1Type
    extends ImmutableType<Boolean> {

  public PostgresBit1Type() {
    super(Boolean.class);
  }

  @Override
  public int[] sqlTypes() {
    return new int[]{Types.BIT};
  }

  @Override
  public Boolean get(
      ResultSet rs,
      String[] names,
      SharedSessionContractImplementor session,
      Object owner)
      throws SQLException {
    // The paradox is that the driver itself treats BIT(1) as boolean
    Boolean result = rs.getBoolean(names[0]);
    return result;
  }

  @Override
  public void set(
      PreparedStatement st,
      Point value,
      int index,
      SharedSessionContractImplementor session)
      throws SQLException {
    if (value == null) {
      st.setNull(index, Types.BIT);
    } else {
      Object holder = ReflectionUtils.newInstance(
        "org.postgresql.util.PGobject"
      );
      ReflectionUtils.invokeSetter(
        holder,
        "type",
        "bit"
      );
      ReflectionUtils.invokeSetter(
        holder,
        "value",
        value ? "1" : "0"
      );
      st.setObject(index, holder);
    }
  }
}

As mentioned in the companion SO answer, the Inmutable class is taken from this excellent article from Vlad Mihalcea:

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;

import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

public abstract class ImmutableType<T> implements UserType {

  private final Class<T> clazz;

  protected ImmutableType(Class<T> clazz) {
    this.clazz = clazz;
  }

  @Override
  public Object nullSafeGet(
      ResultSet rs,
      String[] names,
      SharedSessionContractImplementor session,
      Object owner)
      throws SQLException {
    return get(rs, names, session, owner);
  }

  @Override
  public void nullSafeSet(
      PreparedStatement st,
      Object value,
      int index,
      SharedSessionContractImplementor session)
      throws SQLException {
    set(st, clazz.cast(value), index, session);
  }

  protected abstract T get(
      ResultSet rs,
      String[] names,
      SharedSessionContractImplementor session,
      Object owner) throws SQLException;

  protected abstract void set(
      PreparedStatement st,
      T value,
      int index,
      SharedSessionContractImplementor session)
      throws SQLException;


  @Override
  public Class<T> returnedClass() {
    return clazz;
  }

  @Override
  public boolean equals(Object x, Object y) {
    return Objects.equals(x, y);
  }

  @Override
  public int hashCode(Object x) {
    return x.hashCode();
  }

  @Override
  public Object deepCopy(Object value) {
    return value;
  }

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

  @Override
  public Serializable disassemble(Object o) {
    return (Serializable) o;
  }

  @Override
  public Object assemble(
      Serializable cached,
      Object owner) {
    return cached;
  }

  @Override
  public Object replace(
      Object o,
      Object target,
      Object owner) {
    return o;
  }
}

Finally, define your Boolean field similar to:

@Type(type = "your.packg.PostgresBit1Type")
@Column(columnDefinition = "bit")
public Boolean attribute;

I recommend to use Boolean and not boolean to appropriately handle null. values.

Although I think it should work I haven't tested the solution myself. I hope at least it gives you some clues to advance in the problem resolution.

CodePudding user response：

**

First to clarify things:

**

The Java Boolean represents a single bit value that can be 0 or 1 (False or True).

Whereas SQL defines an SQL BIT type. However, unlike what you expect, BIT in SQL can be used as a parameterized type to define a fixed-length binary string (not just 0 or 1 it can be 00000, 00001, 01010... etc.).

so that's why it might bring you problems while you map them.

However, try this:

@Column(name = "columnName", columnDefinition="BIT")
private Boolean columnVariable;

or this:

@Type(type = "numeric_boolean")
private Boolean columnVariable;