Pass value and use it in Liferay custom query

Question

I have to pick up date (liferay date picker) entered by user and display the contents based on that date on jsp. By default when the page loads the date will be the current date and hence data of the current date should be displayed.

I am using custom query and making a call in the jsp as follows:

 List<Object[]> ObjdisplayAttListName = AttendanceLocalServiceUtil.findAttendance(currdate);

The currdate is the date whose data should be displayed

In the custom query I have:

 select stud.studName , stud.empTitle, attendance.attRemarks, attendance.attStatus , stud.fileEntryI from student stud left outer join attendance attendance on stud.studId = attendance.studId and attendance.attDate LIKE ? order by studName asc;

I always get a null pointer exception. How am I getting NPE? and is my query correct when I write attendance.attDate LIKE ? In my jsp I have the datepicker which is inside the form. this date is also used to process data by passing it in java class. How should I extract this date and send it to custom query?

Here is my FinderImpl:

 public class AttendanceFinderImpl extends BasePersistenceImpl  implements AttendanceFinder {

  public static String FIND_Attendance = "findAttendance";

@SuppressWarnings("rawtypes")
public List findAttendance(Date attendanceDate) throws SystemException {

    List attSearchList = null;
    Session session = null;
    try {
    session = openSession();
    String sql = CustomSQLUtil.get(FIND_Attendance);
    System.out.println("session : " + session);
    System.out.println("SQL QUERY CustomSQLUtil.get(findAttendance): " + CustomSQLUtil.get("findAttendance"));
    SQLQuery query = session.createSQLQuery(sql);
    System.out.println("SQLQuery : " + query);
    //query.addEntity("Attendance", AttendanceImpl.class);
    //query.addEntity("Employee", EmployeeImpl.class);
    QueryPos qPos = QueryPos.getInstance(query);
    System.out.println("I am returning the query : " + query);
    System.out.println("List size.list: "+ query.list());
    qPos.add(attendanceDate);
    System.out.println("List size: "+ query.list().size());
    return (List)query.list();
    }catch (Exception e) {
    }
    return null;

    }

}

Is `attendance.attDate` column a date-type or a String? Also I think since you are not catching any exception it is giving you `Null-pointer` instead as you are doing `return null`. There is some other exception that is thrown in your finder method, have `e.printStackTrace()` to find out. — Prakash K, May 27 '13 at 13:00
I think the error may be an `SQLGrammerException` since you have not replaced the `LIKE ?` question-mark. Oh I see that in [this comment](http://stackoverflow.com/questions/16748361/pass-value-and-use-it-in-liferay-custom-query#comment24158065_16763545) you have added `qPos` but not updated your question. — Rasabihari Kumar, May 27 '13 at 13:46
@ Rasabihari Kumar: Do i need to replace the LIKE ? -the question mark with some variable? Right now it is dispplaying the results but it is not displaying the corrcect results of that date.. When I printd the date in finderImpl it printed the correct date — Seeya K, Jun 01 '13 at 06:27

score 0 · Answer 1 · answered May 26 '13 at 20:45

0

I will assume that both 'currdate', and attendance.attDate, are java.util.Date types. If this is the case, then I think that your problem starts with comparing dates with sql 'LIKE' statement, that's actually meant to be used with String compare.

If working with dates, you should work with '>' and '<', as it's practically impossible to exact match 2 Date symbols, especially if one of them is created by real time submits (a new instance is created with new Date() ) and will have millisecond level precision, and the Dates that you get from a date picker will look like 02-12-2013 00:00:00

answered May 26 '13 at 20:45

yannicuLar

3,083
3
32
50

1

I removed "LIKE" ans in FinderImpl class I modified: qPos.add(attendanceDate); as qPos.add("%" + attendanceDate + "%"); So now it is not showing NPE – Seeya K May 27 '13 at 06:54
can you debug and give me the value of var 'currdate' in runtime ? Just print it before calling 'AttendanceLocalServiceUtil.findAttendance(currdate);' – yannicuLar May 27 '13 at 07:46
I printed the date in finderImpl it prints correct date.. but yes like you said it prints 02-12-2013 00:00:00. I dont have two dates to compare.. i only need to fetch data if it matches with the date.. like the query i have shown in my ques. do i need to replace ?-ques mark in the query? it is fetching the results from query but not the exact results of that date. how shoud i structure query so it fetched data of given date. – Seeya K Jun 01 '13 at 06:26
is this date you getting from database or date picker selected date? – Laxman Rana Jun 01 '13 at 07:14
I think you're still comparing Dates with the 'Like' operand and that's wrong. Even if the function won't crash, it's more likely going to return no results. Even if the Date->toString matching did work, A date like '02-12-2013 12:05:26' would not return true when compared with '02-12-2013 00:00:00'. It would if it was compared with '%02-12-2013%' I strongly suggest you test your SQL queries with an SQL browsing tool (like phpMyAdmin) before implementing the CustomQuery function. This will save you a great amount of time and debugging – yannicuLar Jun 02 '13 at 17:23

Pass value and use it in Liferay custom query

1 Answers1