ColumnTransformer in hibernate
hibernate, java, jpa, jpql, orm
Solution
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.
Problem
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?