mysql - 如何将 "between yyyy-mm-dd and yyyy-mm-dd"插入准备好的语句中？

Question

我试图在某一年内返回一个元组，但我无法让准备好的语句返回除空集以外的任何内容。这是我调用它的参数:year.valueOf("2014-01-01");不确定这是否已经是问题，这只是为了测试它通常是来自文本字段的值。

public List<Sales> findYear(Date date) {
    try {
        List<Sales> listSales = new ArrayList<>();

我尝试了两个参数，所以这只是测试版本。当我将两个日期都硬编码到准备好的语句中时，它就起作用了，所以方法本身看起来很好。在 ? 周围添加 ' '没有区别。

        PreparedStatement ps = ConnectDB
                .getConnection()
                .prepareStatement(
                        "select * from sales where sale_date between ? and '2014-12-30'");
        ps.setDate(1, date);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Sales s = new Sales();
            s.setEmpno(rs.getInt("empno"));
            s.setOutno(rs.getInt("outno"));
            s.setPno(rs.getInt("pno"));
            s.setCno(rs.getInt("cno"));
            s.setOno(rs.getInt("ono"));
            s.setQty(rs.getInt("qty"));
            s.setSale_date(rs.getDate("sale_date"));
            s.setSale_time(rs.getTime("sale_time"));
            listSales.add(s);
        }
        return listSales;

    } catch (Exception e) {
        System.out.println(e.getMessage());
        return null;
    }
}

Answer 1

"select * from sales where sale_date between ?-?-? and ?-?-?"

然后设置日期:

prepare.setInt(1, day1);
prepare.setInt(2, month1);
prepare.setInt(3, year1);
prepare.setInt(4, day2);
prepare.setInt(5, month2);
prepare.setInt(6, year3);

您还可以将日期转换为字符串:

"select * from sales where sale_date between ? and ?"
...

String date1Str = SimpleDateFormat("dd-MM-yyyy").format(date1);
String date2Str = SimpleDateFormat("dd-MM-yyyy").format(date2);

prepare.setString(1, date1Str);
prepare.setString(2, date2Str);

---

扩展示例:

try {    
        PreparedStatement ps = ConnectDB.getConnection()
                        .prepareStatement(
                        "select * from sales where sale_date between ? and ?");
        ps.setDate(1, date);
        // whatever date2 is
        ps.setDate(2, date2);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
                Sales s = new Sales();
                s.setEmpno(rs.getInt("empno"));
                s.setOutno(rs.getInt("outno"));
                s.setPno(rs.getInt("pno"));
                s.setCno(rs.getInt("cno"));
                s.setOno(rs.getInt("ono"));
                s.setQty(rs.getInt("qty"));
                s.setSale_date(rs.getDate("sale_date"));
                s.setSale_time(rs.getTime("sale_time"));
                listSales.add(s);
        }
        return listSales;

} catch (Exception e) {
        System.out.println(e.getMessage());
        return null;
}