CODEWITHSUNDEEP
javamysqljsonhibernaterest
PeakGen
PeakGen

Reputation: 23035

Hibernate: No results when ran a query to get results between a date range

I have used Hibernate in my REST API to do the database work. I have basically 3 layers;

  1. JSON Service Layer - This will handle the JSON Requests
  2. Service Layer - This will handle any operation to the data sent or taken from database layer
  3. Database Layer - Works with database. Functions for save, update etc.

Please check the below code

JSON Service Layer

@GET
    @Path("/getByTimeRange/{fromDate}/{toDate}")
    @Produces(MediaType.APPLICATION_JSON)
    public List<Measurements> getByTimeRange(@PathParam("fromDate") String fromDate, @PathParam("toDate") String toDate)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        MeasurementsService measurementsService = new MeasurementsService();
        List<Measurements> byTimeRange = null;
        try {
            byTimeRange = measurementsService.getByTimeRange(sdf.parse(fromDate), sdf.parse(toDate));
        } catch (ParseException ex) {
            Logger.getLogger(MeasurementsJSONService.class.getName()).log(Level.SEVERE, null, ex);
        }
        return byTimeRange;
    }

Service Layer

public List<Measurements> getByTimeRange(Date fromDate, Date toDate)
    {
        Session session = measurementsDAOInterface.openCurrentSession();
        Transaction transaction = null;
        List<Measurements> byTimeRange = null;

        try
        {
            transaction = measurementsDAOInterface.openTransaction(session);
            byTimeRange = measurementsDAOInterface.getByTimeRange(fromDate, toDate, session);
            transaction.commit();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            session.close();
        }

        return byTimeRange;
    }

Database Layer

public List<Measurements> getByTimeRange(Date fromDate, Date toDate, Session session) 
    {
        Query query = session.createQuery("FROM Units where last_updated BETWEEN :fromDate and :toDate");
        query.setParameter("fromDate", fromDate);
        query.setParameter("toDate", toDate);
        List<Measurements> list = query.list();

        System.out.println("Size "+list.size() );
        System.out.println("Dates "+toDate +" : "+ fromDate);

        for(int i=0;i<list.size();i++)
        {
            System.out.println("Item: "+list.get(i).getLastUpdated());
        }

        return list;
    }

So basically what I am trying to do is retrieving the data sets belong to a specific date range - Let's say between 14-02-2016 to 14-09-2016. In Hibernate, I have used the below code

FROM Units where last_updated BETWEEN :fromDate and :toDate

Units is the table and last_updated is the name of the column.

Anyway, I always get no results, just blank. Even the code System.out.println("Size "+list.size() ); inside database layer prints 0.

What is wrong here?

Upvotes: 0

Views: 539

Answers (2)

PeakGen
PeakGen

Reputation: 23035

The issue was I have used an incorrect table name in my query. It should be FROM Measurements where last_updated BETWEEN :fromDate and :toDate

Upvotes: 0

Mariano Cali
Mariano Cali

Reputation: 116

Can you put the Units class here?
Have you a member in Units with last_updated name?
Have you a data in the dataBase?
If you run the normal SQL query in the database it has any results?

Here I give you an example of a Between with dates.

@Override
public List<Auto> getAutosFechaVenta(Calendar fechaDesde, Calendar fechaHasta) {

    EntityManager em = null;
    List<Auto> listaAutos = null;

    try {
        em = emf.createEntityManager();
        em.getTransaction().begin();
        String autosFechabetween = "from Auto a where fechaVenta between :desde and :hasta ";
        Query query = em.createQuery(autosFechabetween);
        query.setParameter("desde", fechaDesde);
        query.setParameter("hasta", fechaHasta);

        listaAutos = query.getResultList();

    } catch (Exception e) {
    } finally {
        try {
            if (em != null) {
                em.close();
            }
        } catch (Exception e) {
        }

    }
    return listaAutos;
}

When you start the transaction? See my code: em.getTransaction().begin();

Maybe you can try with query.getResultList();

I hope this example can hel you. Let my know your results.

Upvotes: 1

Related Questions