1

I'm new to Java and still learning, please keep that in mind when answering.

So I want to get a list of strings based on data from a MySQL database using JPQL. More specifically I'm trying to get all unique years from a certain column in a certain table. The data type is a MySQL date (yyyy-mm-dd). The SQL is working perfectly fine! But trying to put it in a List does not do what I expected.

What I have so far:

Servlet:

// em is of the type EntityManager, other queries retrieving objects working fine
List<String> years = em.createQuery("select distinct (date_format(p.birthday, '%Y')) as year from Player p order by year").getResultList();
request.setAttribute("years", years);

View using JSTL:

<c:forEach var="year" items="${years}">
      <p>${year}</p>
</c:forEach>

This gives something like:

[Ljava.lang.Object;@b3d38cd
Wouter
  • 430
  • 2
  • 5
  • 13
  • 1
    That means that the returned list is not a List, but a List. I'm surprised that such a query would return a List. Are you sure this is indeed the executed query? What's the point of `group by`? If you want distinct results, use `select distinct`. – JB Nizet May 10 '15 at 16:03

1 Answers1

0

Use JPA built-in support for dates. For Hibernate:

Query query = em.createQuery("select distinct YEAR(p.birthday) as year from Player p order by year");
List<String> years = query.getResultLits();

For other implementations (JPA 2.1):

Query query = em.createQuery("select distinct FUNCTION('date_format', p.birthday, '%Y') as year from Player p order by year");
List<String> years = query.getResultLits();

I do not think servlet is the best place to have EntityManager inside. I would use DAO pattern Data Access Object Pattern

kor88
  • 11
  • 3