Date filtering using JDBC

Question

What is the correct format for date filtering - JDBC to SQL

I have been trying to use the following with an MS-Access DB

SELECT doctorbusiness.dateofreport, 
       doctorbusiness.patientname, 
       doctorbusiness.labcomm, 
       doctorbusiness.xcomm, 
       doctorbusiness.spccomm, 
       doctorbusiness.ecgcomm 
FROM   doctorbusiness 
WHERE  doctorbusiness.doctorname = '"+selectedDoc+"' 
       AND (( doctorbusiness.dateofreport >= # "+sd+" # ) 
             AND ( doctorbusiness.dateofreport <= # "+ed+" # ))

selectedDoc is in String and sD and eD in date format.

The query runs fine in MS-Access but gives the following exception :

net.ucanaccess.jdbc.UcanaccessSQLException: unknown token:

UPDATE

public void printDoctorIncome() {

    Date startDate = easypath.docB_startDate_jxdp.getDate();
    Calendar calSD = Calendar.getInstance();
    calSD.setTime(startDate); // convert your date to Calendar object
    int daysToDecrement = -1;
    calSD.add(Calendar.DATE, daysToDecrement);
    Date real_StartDate = calSD.getTime();
    SimpleDateFormat sdF1 = new SimpleDateFormat("dd-MM-yyyy");
    String sD = sdF1.format(real_StartDate);
    JOptionPane.showMessageDialog(null, sD);

    Date endDate = easypath.docB_endDate_jxdp.getDate();
    Calendar calED = Calendar.getInstance();
    calED.setTime(endDate); // convert your date to Calendar object
    int daysToIncrement = +1;
    calED.add(Calendar.DATE, daysToIncrement);
    Date real_endDate = calED.getTime();
    SimpleDateFormat sdF2 = new SimpleDateFormat("dd-MM-yyyy");
    String eD = sdF2.format(real_endDate);
    JOptionPane.showMessageDialog(null, eD);

    String selectedDoc = easypath.drname_jlist.getSelectedValue().toString();
    String sql = "SELECT doctorBusiness.dateofreport, doctorBusiness.patientName, doctorBusiness.labComm, doctorBusiness.xComm, doctorBusiness.spcComm, doctorBusiness.ecgComm FROM doctorBusiness WHERE doctorBusiness.doctorname ='"+selectedDoc+"' AND (doctorBusiness.dateofreport >= ?"+sD+"? AND doctorBusiness.dateofreport <= ?"+eD+"?)";
    try {
        conn = connectDB.getConnection();
        psmt = conn.prepareStatement(sql);
        rs = psmt.executeQuery();
        doctorIncome.docIncomePrint_table.setModel(DbUtils.resultSetToTableModel(rs));
        doctorIncome dI = new doctorIncome();
        dI.setVisible(true);

    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "Error");
        e.printStackTrace();
    }

}

This is the code I am using

Ya I guess it is a syntax error but the same thing works in MS-Access directly — mustangDC, Aug 08 '15 at 09:45
@Jens : could you please suggest what might be the problem here? — mustangDC, Aug 08 '15 at 09:51
Your code is not using prepared statements the way you should use them. Please follow a [basic JDBC tutorial](http://docs.oracle.com/javase/tutorial/jdbc/basics/), and specifically look at the [use of prepared statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). The main problem is that you are concatenating strings into your query instead of using parameter placeholders and **setting** the parameter value with one of the `setXXX` ,methods. — Mark Rotteveel, Aug 08 '15 at 10:32

Sergii Lagutin · Accepted Answer · 2015-08-08T10:21:02.517

3

With JDBC better way to do it is use setDate/Time/Timestamp methods of PreparedStatement. And you shouldn't care about concrete DB's date format.

Date dateFrom = ...
Date dateTo = ...

String sql = "... where myDate >= ? and myDate <= ? "

preparedStatement.setDate(1, dateFrom);
preparedStatement.setDate(2, dateTo);

edited Aug 08 '15 at 10:21

answered Aug 08 '15 at 09:43

Sergii Lagutin

10,561
1
34
43

`sD` and `eD` are start dates and end dates in formated dates respectively. Do you mean that I should remove `#`s from the query? – mustangDC Aug 08 '15 at 09:46
@mustangDC No. Use `PreparedStatement` instance to set parameters to your query. Replace `#sD#` and `#eD#` with `?` and allow `JDBC` classes do their job for you. – Sergii Lagutin Aug 08 '15 at 09:52
tried . now it throws an exception which is `net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: 14 required: )` – mustangDC Aug 08 '15 at 09:54
@mustangDC check out if your sql is correct grammatically – Sergii Lagutin Aug 08 '15 at 09:57
I have checked a number of times there are no bracket mismatching, have been struck here for number of days, could not find any sql grammar error – mustangDC Aug 08 '15 at 10:01
@mustangDC could you update your post please and show us current code (java and sql)? – Sergii Lagutin Aug 08 '15 at 10:03
1

@mustangDC, you need to change the `WHERE` clause in the query into `WHERE doctorBusiness.doctorname = ? AND doctorBusiness.dateofreport >= ? AND doctorBusiness.dateofreport <= ?` and additionally set the bind variables to `psmt` before running `executeQuery`: `psmt.setString(1, selectedDoc); psmt.setDate(2, sD); psmt.setDate(3, eD);` Also, use the [try-with-resources statement](http://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc) so that resources get closed properly even if an exception is thrown. – Mick Mnemonic Aug 09 '15 at 01:22
@Mick and Sergey , u both gave me the correct guidence. Thanks for that. But additionally I need to add and convert a java.sql.Date – mustangDC Aug 09 '15 at 06:38
@mustanDC, sorry, there was a mistake in my previous comment. No need for the date format; just use `psmt.setDate(2, new java.sql.Date(real_StartDate.getTime())); psmt.setDate(3, new java.sql.Date(real_endDate.getTime()));` – Mick Mnemonic Aug 09 '15 at 08:31

score 1 · Answer 2 · answered Aug 09 '15 at 14:57

1

Using a PreparedStatement is a good idea. But you can also use either #MM/dd/yyyy# or #yyyy-MM-dd# (with or without hours:minutes:seconds).

answered Aug 09 '15 at 14:57

jamadei

1,700
9
8

Date filtering using JDBC

UPDATE

2 Answers2