javapostgresqlhibernatehibernate-mappingusertype

HibernateException: Could not determine a type for class exception with spring-data-jpa native query for postgresql


I have a spring boot application with a bean and a repository. I use postgresql as a database. The database has a table called testjob, with two column: id (UUID type) and parameters (jsonb type). I followed this article to support jsonb: https://thoughts-on-java.org/persist-postgresqls-jsonb-data-type-hibernate/ I created every class mentioned in the article, but when I run my test which simply calls the upsert method on the repository, it throws an exception with this cause:

Caused by: org.hibernate.HibernateException: Could not determine a type for class: com.test.Parameters

This exception is only thrown when I run the native query, but with JPQL everything work fine. Sadly I need to run it in native because I will have to use postgresql ON_CONFLICT statement in the future. I would appriciate any advices, thanks.

My classes:

TestJobBean.java

@Entity
@Table(name = "testjob")
public class TestJobBean {

    @Id
    private UUID id;

    @NotNull @Column(name = "parameters") @Type(type = "ParametersType")
    private Parameters parameters;
    ...
}
TestJobRepository.java

@Repository
@Transactional
public interface TestJobRepository extends JpaRepository<TestJobBean, UUID>, TestJobRepositoryCustom {
}
TestJobRepositoryCustom.java

public interface TestJobRepositoryCustom {
    int upsert(UUID id, Parameters parameters);
}
TestJobRepositoryCustomImpl.java

@Transactional
public class TestJobRepositoryCustomImpl implements TestJobRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public int upsert(UUID id, Parameters parameters) {
        final Query query = entityManager.createNativeQuery("INSERT INTO testjob (id, parameters) VALUES(:id, :parameters)");
        query.setParameter("id", id);
        query.setParameter("parameters", parameters);
        return query.executeUpdate();
    }

}
package-info.java

@org.hibernate.annotations.TypeDef(name = "ParametersType", typeClass = ParametersType.class)
package com.test.job;
Parameters.java

@Immutable
public class Parameters {

    private final long from;
    private final long to;
    private final @Nonnull String name;

    public Parameters(@JsonProperty("from") long from, @JsonProperty("to") long to,
            @JsonProperty("name") String name) {
        this.from = from;
        this.to = to;
        this.name = name;
    }

    @JsonProperty("from")
    public long getFrom() {
        return from;
    }

    @JsonProperty("to")
    public long getTo() {
        return to;
    }

    @JsonProperty("name")
    public long getName() {
        return name;
    }
}
TestPostgreSQL95Dialect.java

public class TestPostgreSQL95Dialect extends PostgreSQL95Dialect {

    public TestPostgreSQL95Dialect() {
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }
}
ParametersType.java

public class ParametersType implements UserType {

    private final @Nonnull ObjectMapper mapper = new ObjectMapper();

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

    @Override
    public Class<ParametersType> returnedClass() {
        return ParametersType.class;
    }

    @Override
    public Object nullSafeGet(final ResultSet rs, final String[] names, final SharedSessionContractImplementor session,
            final Object owner) throws HibernateException, SQLException {
        final String cellContent = rs.getString(names[0]);
        if (cellContent == null) {
            return null;
        }
        try {
            return mapper.readValue(cellContent, returnedClass());
        } catch (final IOException ex) {
            throw new RuntimeException("Failed to convert string to an instance!", ex);
        }
    }

    @Override
    public void nullSafeSet(final PreparedStatement ps, final Object value, final int idx,
            final SharedSessionContractImplementor session) throws HibernateException, SQLException {
        if (value == null) {
            ps.setNull(idx, Types.OTHER);
            return;
        }
        try {
            final String valueAsJson = mapper.writeValueAsString(value);
            ps.setObject(idx, valueAsJson, Types.OTHER);
        } catch (final Exception ex) {
            throw new RuntimeException("Failed to convert the instance to string!", ex);
        }
    }

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

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

    @Override
    public Serializable disassemble(final Object value) throws HibernateException {
        try {
            final String valueAsJson = mapper.writeValueAsString(value);
            return valueAsJson;
        } catch (final Exception ex) {
            throw new RuntimeException("Failed to convert the instance to string!", ex);
        }
    }

    @Override
    public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
        try {
            return mapper.readValue((String) cached, returnedClass());
        } catch (final IOException ex) {
            throw new RuntimeException("Failed to convert string to an instance!", ex);
        }
    }

    @Override
    public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
        return original;
    }

    @Override
    public boolean equals(final Object obj1, final Object obj2) throws HibernateException {
        return Objects.equals(obj1, obj2);
    }

    @Override
    public int hashCode(final Object obj) throws HibernateException {
        return obj.hashCode();
    }
}

Solution

  • From the comment of coladict: "UserType implementations are not added to the recognized types you can use in native queries" So the solution was to convert the object to string and cast the string as jsonb.

    TestJobRepositoryCustomImpl.java
    
    @Transactional
    public class TestJobRepositoryCustomImpl implements TestJobRepositoryCustom {
    
        @PersistenceContext
        private EntityManager entityManager;
    
        @Autowired
        private ObjectMapper objectMapper;
    
        @Override
        public int upsert(UUID id, Parameters parameters) {
            final String parametersAsString = objectMapper.writer().writeValueAsString(parameters);
            final Query query = entityManager.createNativeQuery("INSERT INTO testjob (id, parameters) VALUES(:id, cast(:parameters as jsonb))");
            query.setParameter("id", id);
            query.setParameter("parameters", parametersAsString);
            return query.executeUpdate();
        }
    
    }