CODEWITHSUNDEEP
javahibernatehql
splatter_fadli
splatter_fadli

Reputation: 781

Could not locate named parameter

I have a hql to show my data based on between(to_date), but i got Could not locate named parameter error

this is my code

    SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
    Date date1 = Calendar.getInstance().getTime();
    String tgl1 = sdf1.format(date1);
    String Tanggal_awal = tgl1+" 00:00:00";
    System.out.println(Tanggal_awal);

    SimpleDateFormat sdf2 = new SimpleDateFormat("dd-MM-yyyy");
    Date date2 = Calendar.getInstance().getTime();
    String tgl2 = sdf2.format(date2);
    String Tanggal_akhir = tgl2+" 23:59:59";
    System.out.println(Tanggal_akhir);

    Query LoadSource = session_source.createQuery("select CLIENT,SERVICE,SERVICE_TYPE,PROVIDER_CODE,COUNT(*) FROM SwitcherServiceSource" +
            " where TIMESTAMP between to_date(':awal','dd-MM-yyyy HH24:MI:SS') and to_date(':akhir','dd-MM-yyyy HH24:MI:SS')" +
            " and PROVIDER_CODE is not null group by CLIENT,SERVICE,SERVICE_TYPE,PROVIDER_CODE order by CLIENT,SERVICE,SERVICE_TYPE,PROVIDER_CODE");
    LoadSource.setParameter("awal", Tanggal_awal);
    LoadSource.setParameter("akhir", Tanggal_akhir);

any help will be pleasure :)

Upvotes: 1

Views: 2636

Answers (1)

Adrian Shum
Adrian Shum

Reputation: 40036

You do not need to quote your parameters. i.e.:

Instead of having where TIMESTAMP between to_date(':awal','dd-MM-yyyy HH24:MI:SS') and to_date(':akhir','dd-MM-yyyy HH24:MI:SS'), you should write where TIMESTAMP between to_date(:awal,'dd-MM-yyyy HH24:MI:SS') and to_date(:akhir,'dd-MM-yyyy HH24:MI:SS')

And even better, you can (and you should) even avoid binding a string to that parameter. You can bind a Date/Timestamp, so that the query is even more effective and readable: where TIMESTAMP between :awal and :akhir, and do something similar to this in your code:

Calendar cal = Calendar.getInstance();
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);

Date fromDate = cal.getTime();

cal.set(Calendar.HOUR_OF_DAY, 23);
cal.set(Calendar.MINUTE, 59);
cal.set(Calendar.SECOND, 59);

Date toDate = cal.getTime();

Query loadSource = session_source.createQuery("select blablabla FROM SwitcherServiceSource" +
        " where TIMESTAMP between :awal and :akhir " +
        " and blablabla group by blabla order by blablabla");
loadSource.setParameter("awal", fromDate );
loadSource.setParameter("akhir", toDate);

Upvotes: 1

Related Questions