DB select with java util date

Question

The DB persist java.util.date and i need a query to get all the data between 2 Dates with only using java.util.date. I use the JPA 2.1

String sql = "SELECT * FROM table WHERE created > '"+date1+"' AND created < '"+date2+"'";

This query provides always a List with 0 elements

Can you try: Query q = em.createQuery("Select m from table m " + "where m.created > :date1 and m.created < :date2"); q.setParameter("date1", date1); q.setParameter("date2", date2); List<Message> results = (List<Message>) q.getResultList(); — Sunil Dabburi
– Sunil Dabburi, Commented May 25, 2016 at 12:44

Basil Bourque · Accepted Answer · 2016-05-25 13:14:19Z

3

The problem is that your dates are being added to the SQL query as string values. The database is getting something like:

String sql = "SELECT * FROM table WHERE created > 'Wed May 25 14:33:18 SAST 2016' and created < 'Wed May 25 14:33:18 SAST 2016'";

The database is seeing it as a string and that explains why it isn't working.

The single best way to resolve it is use bind variables::

String sql = ""SELECT * FROM table WHERE created > ? AND created < ?"
//Create the query object using that SQL string, then set values
sqlQuery.setParameter(1, date1);
sqlQuery.setParameter(2, date2);

Then execute the prepared statement.

edited May 25, 2016 at 13:14

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

answered May 25, 2016 at 12:43

ernest_k

45.5k5 gold badges58 silver badges107 bronze badges

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

Collectives™ on Stack Overflow

DB select with java util date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related