how to print an alias attribute in JSP file using @Query annotation

Question

This is rows in database.

group userid
1    a
2    b
1    c
3    d

I want to query using @query annotation about

@Query(value ="SELECT *, count(*) as cnt FROM user group by body order by cnt DESC", nativeQuery = true)
List<User> getGroupRanking();

and, i expect to result about

group cnt
1     2
2     1
3     d

but List<User> collection is not accept to 'cnt' attribute which was alias. another reason not to accept, User defined class was not containing 'cnt' attribute. i was defined 'cnt' attribute in User Class like following code.

public class User{
 @Column(name ="group")
 private String group;
 ..

 private Integer cnt;
 public Integer getCnt(){ return cnt; }
 public void setCnt(Integer cnt){ this.cnt = cnt; }

}

Although I will prevent to update the schema, The above code update the schema. because

appConfig.xml configuration was set like this

<prop key="hibernate.hbm2ddl.auto">update</prop>    

i don't want to update schema, and i want to display cnt attribute in JSP file through User Class(bean) when i using @Query annotation. how to accept a 'cnt' attribute using the User class without update schema? 'as' keyword usage not containing in detail(link)

Answer 1

It's difficult to understand what you need, but I'll try... ))

First create a projection:

public interface UserCount {
     User getUser();
     long getUserCount();
}

Then create a repository query method which return this projection:

public interface UserRepo extends JpaRepository<User, Long> {

    @Query("select u as user, count(u) as userCount from User u group by u order by userCount desc")
    List<UserCount> getUserCounts();
}

Then you can get list of UserCount projections and send it to your JSP view.

Additional reading:

• Projections on repository query methods
• Data transfer object

UPDATE

Controller:

@Controller
public class UserController {

    @Autoware
    private UserRepo repo;

    @GetMapping("/")
    public String withCount(Model model) {

        List<UserCount> userCounts = getUserCounts();
        model.addAttribute("userCounts", userCounts);

        return "userCounts";
    }
}

JSP

<table>
  ...
  <c:forEach items="${userCounts}" var="item">
    <tr>
      <td>${item.user.group}</td>
      ...
      <td>${item.userCount}</td>
    </tr>
  </c:forEach>
</table>

Some info: 1, 2

I recommend to switch from jsp to thymeleaf.