Categories: java, sql, json, spring, hibernate

Hibernate: Criteria & createSQLQuery: how to get proper JSON?

1 answer

When I try this I get the proper JSON as a result, but it takes a lot of time:

        Criteria c = sessionFactory.getCurrentSession().createCriteria(User.class);         List<User> users = c.list();         List<User> specialUsers = new ArrayList<>();          for (User user : users) {             List<Perm> userPerms = user.getProfile().getPerms();              for (Perm perm : userPerms) {                 if (perm.getId().equals(SPECIAL_ID)) {                     specialUsers.add(user);                 }             }         }          return specialUsers;

and the JSON is like:

[{"id":111,"name":"Name111"},{"id":222,"name":"Name222"}]

In attempt to improve performance I tried code below. In SQL app the results are OK, a few records of users:

        String sql = "SELECT u.id, u.name FROM app.user u inner join app.perms p where u.profile = p.profile AND p.right= :rightId";         List<User> specialUsers= (List<User>)sessionFactory.getCurrentSession()                 .createSQLQuery(sql)                 .setParameter("rightId", SPECIAL_ID)                 .list();          return specialUsers;

Now the 'JSON' however looks like this:

[[111,"Name111"],[222,"Name222"]]

I tried several things, like select *, criteria.add(Restrictions...) but to no effect. What I noticed is that in the first case specialUsers.toString returns proper data, in the second case it returns meaningless Strings like Ljava.lang.Object;@23e1469f.

Any hints how to solve this?

Received answer to this question:
The best answer according to the author of the question:

I managed to solve this in this way, may not be perfect:

        // get ids of all special users         String sql = "SELECT u.id FROM app.user u inner join app.perms p where u.profile = p.profile AND p.right= :rightId";         List<Integer> intIds = sessionFactory.getCurrentSession()                 .createSQLQuery(sql)                 .setParameter("rightId", SPECIAL_ID)                 .list();          // convert to long values         List<Long> longIds = intIds.stream()                 .mapToLong(Integer::longValue)                 .boxed().collect(Collectors.toList());          // get all special users         Criteria c = sessionFactory                 .getCurrentSession()                 .createCriteria(User.class)                 .add(Restrictions.in("id", longIds));          List<User> specialUsers = c.list();         return specialUsers;     }