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)

Cepr0 · Accepted Answer · 2017-07-31 18:33:38Z

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:

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.

edited Jul 31, 2017 at 18:33

answered Jul 31, 2017 at 14:22

Cepr0

31.2k9 gold badges82 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3736174 Over a year ago

You gave the proper answer to the intent of the question. I have a question, how do I print out a jsp file using getUser() and getUserCount() in the interface?

Cepr0 Over a year ago

Don't understand you. How do you imagine printing a jsp file using a repository method that works with the database only? Or you want to ask how to use repo method in mvc controller to display its value in jsp page?..

user3736174 Over a year ago

thank you for your answer. I want to using methodgetUser() and getUserCount() in jsp file. but I can't handle it. I can't understand projection. and why call alias( ${item.userCount} ) instead of method name( ${item.getUserCount()} ).

user3736174 Over a year ago

more than anything, my jsp file error has occured. like this.

Servlet.service() for servlet [appServlet] in context with path [/app] threw exception [An exception occurred processing JSP page /WEB-INF/views/group.jsp at line 35  33: 			<c:forEach items="${list}" var="item"> 34: 				<div class="b-field-tit"> 35: 				${item.count} 36: 				</div> 37: 			</c:forEach> 38:

my database ;is mariadb, this syntax is not correct @Query("select u as user, count(u) as userCount from User u group by u alias 'u' of the table name not accept to query syntax in mariadb

user3736174 Over a year ago

my query of the annotation like this,

@Query(value = "select name, count(*) as userCount from User group by body order by userCount desc", nativeQuery = false)

abobe the query is working in jsp file. like this. ${item} but, result log is useless. [Ljava.lang.Object;@466a8d2a [Ljava.lang.Object;@720087c ... this expression in jsp file is not working ${item.userCount}

|

Collectives™ on Stack Overflow

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

1 Answer 1

UPDATE

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related