Type mismatch error on querying

Hibernate Search
1

Hi everyone!
I am using Hibernate Search 6.1 with an Apache Lucene backend. I have a Person entity which I am trying to index, and it looks like this:

@Entity
@Table(name = "PERSON")
@Indexed
public class Person {

    //Validators and custom generators for id
    @Id
    @Type(type = <my-type>)
    //my-type class provides custom logic for how the id field should be converted to and from the numeric type in the database
    @Column(name = "ID", precision = 10, scale = 0)
    private String id;

    @Column(name = "LAST_NAME")
    @FullTextField
    private String lastName;

    @Column(name = "FIRST_NAME")
    @FullTextField
    private String firstName;

    //Getters and Setters
    //...

}

I am attempting to execute the following query through indexes:

List<Person> queryResults = searchSession.search(Person.class)
    .where(f -> f.match().field("firstName").matching("rahul"))
    .fetchAllHits();

However, I encounter the following error:

ERROR: operator does not exist: numeric = character varying at character 658
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
STATEMENT: select person0_.ID as id1_99_, person0_.LAST_NAME as last_nam3_99_, person0_.FIRST_NAME as first_na4_99_, [other-fields] from rahul.PERSON person0_ where (person0_.ID in($1))

I assume this error is because in my database schema id is defined to be of type numeric(10,0) but is a String in Person class, causing a type mismatch when Hibernate Search tries to load entities from the database.

Could someone please advise how I can resolve this issue without changing my database schema or the Person class? Any suggestions or insights would be greatly appreciated.

Thank you.

2

Hello.

As far as I can see, this is unrelated to Hibernate Search; you would get the same error with a simple session.createQuery("select p from Person p where p.id in (:ids)", Person.class).setParameter("ids", List.of("foo")).list(), wouldn’t you?

If so, I’d suggest asking in the Hibernate ORM category.

In any case, the problem possibly lies in your custom type passed @Type(...); I’d suggest you show us how that is implemented.

3

I am trying to integrate Hibernate Search in an existing codebase where direct queries to the database are used but without involving the id field. Even when querying through indexes, my query only involves firstName, lastName etc.
My custom type implements UserType and has logic for mapping a String to a Numeric SQL type. I don’t think the problem lies there as saving and retrieving Person entities by direct queries from the database works as intended.

4

Your Hibernate Search queries are retrieving entities, so they do involve the identifier. That’s the only way (currently) to link index results back to Hibernate ORM entities.

If you really don’t want to involve the database ID at any point, you’ll have to use projections.

Does a query like the one I showed above work, or not?

5

Is there a way to cast id into numeric before the entities are retrieved?

I tried to implement a similar query and it doesn’t work. It gives the same error I mentioned before.

6

There is not. Hibernate Search expects Hibernate ORM queries to work correctly.

Then there is a problem with your type definition, be it in your implementation or in Hibernate ORM itself (bugs happen).

You need to provide a reproducer including your type definition, otherwise nobody can help you.

7

Here is the type definition:

public class MyType implements UserType {

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

    public MyType() {
        super();
    }

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

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

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return (x == y) || (x != null && y != null && (x.equals(y)));
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) {
            return null;
        }
        return new String((String) value);
    }

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

    @Override
    public Object assemble(Serializable arg0, Object arg1) throws HibernateException {
        return deepCopy(arg0);
    }

    @Override
    public Serializable disassemble(Object value) {
        return (Serializable) deepCopy(value);
    }
    
    @Override
    public int hashCode(Object arg0) throws HibernateException {
        return arg0.hashCode();
    }
    
    @Override
    public Object replace(Object arg0, Object arg1, Object arg2) throws HibernateException {
        return deepCopy(arg0);
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner)
            throws HibernateException, SQLException {
        int value = rs.getInt(names[0]);
        return rs.wasNull() ? null : String.valueOf(value);
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.NUMERIC);
        } else {
            if (value instanceof String) {
                if (value.equals("")) {
                    st.setNull(index, Types.NUMERIC);
                } else {
                    st.setInt(index, Integer.parseInt((String) value));
                }
            } else {
                throw new RuntimeException();
            }

        }

    }

}