Criteria ORDER BY date not working?

Question

Can you please help me in solving this problem. I am trying to order the results of an criteria query by date, but I'm not getting the results I need.I saved date in String format,how can i order by date using criteria

The code I'm using is:

@Override
public List<Program> getListProgram() {
    Session session=sessionFactory.openSession();
    Criteria criteria=session.createCriteria(Program.class);
    criteria.addOrder(Order.asc("createdDate"));
    List<Program> programs=(List<Program>)criteria.list();
    return programs;
}

Results are:

Results should be:

I need to select the date in the format above.

Your help is much appreciated.

Why do you use Dates in a String and not a proper Date type column? — Tom, Nov 14 '16 at 13:04
@Tom its happen accidently how can i sort.Pleas help me Tom? — Symon Kt, Nov 14 '16 at 13:17

Incepter · Accepted Answer · 2016-11-14T13:36:15.333

6

You have to call criteria.addOrder(Order.asc("createdDate")); before executing the list method on criteria.

@Override
public List<Program> getListProgram() {
    Session session=sessionFactory.openSession();
    Criteria criteria=session.createCriteria(Program.class);
    criteria.addOrder(Order.asc("createdDate"));
    List<Program> programs=(List<Program>)criteria.list();
    return programs;
}

EDIT

In your case, if you want to order by String dates, as i mentionned in the comments, this answer is not the proper you can get ( may be turning creationDate into a Date type is the best! for sure).

You can try some code like :

static final String DF = "DD/MM/YYYY";
static final SimpleDateFormat SDF = new SimpleDateFormat(DF);

@Override
public List<Program> getListProgram() {
    Session session=sessionFactory.openSession();
    Criteria criteria=session.createCriteria(Program.class);
    List<Program> =(List<Program>)criteria.list();
    boolean asc = true;
    programs.sort((a, b) -> {
        int comparison = 0;
        try {
            comparison = SDF.parse(a.getCreatedDate()).compareTo(SDF.parse(b.getCreatedDate()));
        } catch (ParseException e) {
            // handle it!!
        }
        return asc ? comparison : (0-comparison);
    });
    return programs;
}

EDIT 2

If you want to avoid using lambdas, try using this instead :

Collections.sort(programs, new Comparator<Main>() {
        @Override
        public int compare(Program a, Program b) {
            int comparison = 0;
        try {
            comparison = SDF.parse(a.getCreatedDate()).compareTo(SDF.parse(b.getCreatedDate()));
        } catch (ParseException e) {
            // handle it!!
        }
        return asc ? comparison : (0-comparison);
        }
    });

edited Nov 14 '16 at 13:36

answered Nov 14 '16 at 13:01

Incepter

2,711
15
33

I updated my question actually i am saving created date in String format how can i order by date .Can you undersand y question..... – Symon Kt Nov 14 '16 at 13:04
what sgbd are you using ? – Incepter Nov 14 '16 at 13:07
Actually i have a solution in my mind but it s not proper – Incepter Nov 14 '16 at 13:10
Can you help me I already entered in that column how to process sorting.really i am struggling – Symon Kt Nov 14 '16 at 13:15
you can check it, I used java 8 to write the little brick of code, if you don't use java 8 let us know. – Incepter Nov 14 '16 at 13:28
I understood you are using lambda please edit your code Collection .sort thats rit? i will give you 100% Ok... – Symon Kt Nov 14 '16 at 13:33
I want Lambda but check programs.sort((a, b) is correct that one is Collections.sort(programs, (p1, p2) -> -----); rit – Symon Kt Nov 14 '16 at 13:42
for descending? – Symon Kt Nov 14 '16 at 13:44
i used a variable to simulate ascending, boolean asc = true; you can turn it to false to obtain descending sorting – Incepter Nov 14 '16 at 13:46

score 1 · Answer 2 · answered Apr 23 '18 at 19:07

This is how Implemented it

 private List<Users> findUsers(boolean all, int maxResults, int firstResult, SchoolData data) {
    EntityManager em = getEntityManager(data.getExternalId());
    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery();
        Root<Users> from = cq.from(Users.class);
        cq.orderBy(cb.desc(from.get("dateCreated")));
        cq.select(cq.from(Users.class));
        Query q = em.createQuery(cq);
        if (!all) {
            q.setMaxResults(firstResult);
            q.setFirstResult(maxResults);
        }
        return q.getResultList();
    } finally {
        em.close();
    }
}

score 0 · Answer 3 · answered Nov 14 '16 at 13:03

0

Criteria are passed to Hibernate and are translated to the certain language. They appear after where clause in the select so they all the limitations must be set before executing query. Although you don't see it, there is a query generated and executed when calling criteria.list().

Try the following:

@Override
public List<Program> getListProgram() {
    Session session=sessionFactory.openSession();
    Criteria criteria=session.createCriteria(Program.class);
    criteria.addOrder(Order.asc("createdDate"));
    List<Program> programs=(List<Program>)criteria.list();
    return programs;
}

answered Nov 14 '16 at 13:03

xenteros

15,586
12
56
91

I updated my question i am saving created date in String format how can i order by date – Symon Kt Nov 14 '16 at 13:06
@SymonKt Dates/values are correctly sorted for a String. – Tom Nov 14 '16 at 13:13

score 0 · Answer 4 · answered Oct 27 '17 at 23:12

Firstly, dates should be stored as dates, not strings. If you are working with a legacy system, it may not be possible to change this, but I'd argue that it probably would make your life easier to bite the bullet and refactor.

As a string, the sort function is working because 05 comes before 06.

I'd suggest using annotation or mapping, as outlined in this question, to convert your string to a date in the pojo so that it is the correct type at least somewhere in your application.

public class StringToDateConverter implements AttributeConverter<Date, String> {
  String convertToDatabaseColumn(Date attribute) { /* Conversion code */ }

  Date convertToEntityAttribute(String dbData) { /* Conversion code */ }
}

and

public class MyPojo {
  @javax.persistence.Convert(converter = StringToDateConverter.class)
  public Date getCreateDate() {
  }
}

Criteria ORDER BY date not working?

4 Answers4