DynamicReports not producing report with where clause on date

Question

I'm facing a very strange issue. I'm trying to generate report in DynamicReports API. The report is being generated without WHERE clause but is not being generated with WHERE clause. The database is MySQL. Any help is much appreciated. Here is the code:

Service class:

public class ReportSevice {
    public void createReport (String reportDate) {
        System.out.println(reportDate); 
        String sql_statement = null;
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/world","root", "password");
            sql_statement = "SELECT id, fName, lName, pickUpDate FROM reservations_db WHERE pickUpDate = " + reportDate;
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        } catch (Exception e) {
            e.printStackTrace();
        }

        JasperReportBuilder report = DynamicReports.report();
        report
        .columns(
                Columns.column("Customer Id", "id", DataTypes.integerType()),
                Columns.column("First Name", "fName", DataTypes.stringType()),
                Columns.column("Last Name", "lName", DataTypes.stringType()),
                Columns.column("Date", "pickUpDate", DataTypes.stringType()))
        .title(Components.text("********Report By Date********")
                .setHorizontalTextAlignment(HorizontalTextAlignment.CENTER))
        .pageFooter(Components.pageXofY())
        .setDataSource(sql_statement, connection);

        try {
            report.show();
        } catch (DRException e) {
            e.printStackTrace();
        }
    }
}

Answer 1

At last I got it working. The solution is very simple. I'm sharing the code here for future users. Instead of

sql_statement = "SELECT id, fName, lName, pickUpDate FROM reservations_db WHERE pickUpDate = " + reportDate;

write this:

sql_statement = "SELECT id, fName, lName, pickUpDate FROM reservations_db WHERE pickUpDate=$P{reportDate}"

Answer 2

I can't promise that this will solve your particular problem, but it's worht giving a shot: Instead of writing an entire query in your source, complete with "where" clauses and what not, make a function in SQL, which takes pickUpDate as a parameter, runs the query and returns table. And calling it will look something like this: sql_statement = "select * from function_name(" + reportDate + ")"

I hope that helps, I work with jasperReports and my life's been a lot easier since I addopted this method. Cheers.