java - 使用 JDBC 进行日期过滤

Question

日期过滤的正确格式是什么 - JDBC 到 SQL

我一直在尝试将以下内容与 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 采用字符串格式，sD 和 eD 采用日期格式。

该查询在 MS-Access 中运行良好，但出现以下异常:

net.ucanaccess.jdbc.UcanaccessSQLException: unknown token: 

更新

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();
    }

}

这是我正在使用的代码

Answer 1

对于 JDBC，更好的方法是使用 PreparedStatement 的 setDate/Time/Timestamp 方法。而且您不应该关心具体数据库的日期格式。

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

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

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