ColumnTransformer in hibernate

Question

I have an Entity which I use the ColumnTransformer for bind and extract values:

@Entity
class BPoint {
    @Id
    private Integer id;

    @ColumnTransformer(read = "astext(shape)", write = "toshape(?)")
    private Shape shape;

}

And the dao:

class BPointDao {
    @Autowired
    private EntityManager em;

    @Override
    public Page<BPoint> findAll(Pageable pageable) {
        Query q = em.createQuery("from BPoint");
        List<BPoint> r = q.getResultList();
        int total = em.createQuery("select count(*)  from BPoint").getFirstResult();
        return new PageImpl(r, pageable, total);
    }
    @Override
    public Integer save(BPoint hbds) {
        em.persist(hbds);
        return hbds.getId();
    }
}

It works, however once I have to do some query which need the use the sql functions, I meet some problems, take this valid native sql for example:

select * from BPoint h where inside(h.shape, 100) = 1;

First I tried to use the hql like this:

Query q = em.createNativeQuery("select astext(shape) from BPoint h where inside(h.shape, ?) = 1");

However I found that the generated sql contains things like

...... where inside(astext(h.shape),100).....

It seems that the ColumnTransformer read value is used in the sql function inside which is not expected.

So I tried to use the native sql query like this:

Query q = em.createNativeQuery("select * from BPoint h where inside(h.shape, ? = 1");

Not the sql can be executed, but the result cannot be mapped correctly.

Then I have to add the select fileds manualy like this:

Query q = em.createNativeQuery("select id,astext(shape) from BPoint h where inside(h.shape, ? = 1");

But how about if my Entity have a lot of fileds say it is more than 20? And how about if some columns name changed?

Is there an alternative method to meet my requirement?

Answer 1

First try supplying the return type to your native query:

Query q = em.createNativeQuery("select * from BPoint h where inside(h.shape, ? = 1", BPoint.clss);
List<BPoint> result = q.getResultList();

Or you could try to do it like this:

Session session = em.unwrap(Session.class);
List<BPoint> points = (List<BPoint>) session.createSQLQuery("SELECT {h.*} FROM BPoint {h} WHERE inside({h}.shape, ? = 1")
    .addEntity("h", BPoint.class)
.list();

The Hibernate specific API allows you to map native query results to Entities.