SQL Statement - Java

Question

I've been trying to come up with a couple of SQL statements on how to get this right but no luck. I've tried between, >= and =<. Basically, the SQL statement that I've used is working but to an extent only.

My code works like this: the user will choose a date range (from date and to date) and the program will retrieve and show the data it has within those range. Like I said, it works but it also shows the days from the other months when what I want to show is just those particular days that the user picked. eg. from July 1, 2016 to July 5, 2016. What's happening is any month of the year that has those dates will show as well which makes that particular method a bit useless.

Any help or any explanation why is this so would be appreciated.

Below is my code:

stringFromDate = sdf.format(fromDate.getDate());

stringToDate = sdf.format(toDate.getDate());

String query = "Select * from tblSavings where date between '" + stringFromDate+ "' and '" + stringToDate+"'";

try{ 

    pstmt = conn.prepareStatement(query);

    rs = pstmt.executeQuery();

    tblList.setModel(DbUtils.resultSetToTableModel(rs));

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Jul 7, 2016 at 7:04
Most likely the format of stringFromDate and stringToDate are incorrect, and this is why the query is not working as you expect. Update and show us the actual raw query being executed. Also, what version of SQL is being used? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 7, 2016 at 7:05
@marc_s I'm looking at the link you gave me right now. Thanks. — joppiealiw
– joppiealiw, Commented Jul 7, 2016 at 7:16
@TimBiegeleisen I'm using SQLite. Thanks! What I don't get is why is it getting the days from the other months. I really thought it was working but then this. I'm a bit moronic when it comes to terminologies and would like to give you what you asked but I'm afraid you jargoned the hell out of me. Thanks still. :) — joppiealiw
– joppiealiw, Commented Jul 7, 2016 at 7:16
Show us the literal values of stringFromDate and stringToDate. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 7, 2016 at 7:22

Tim Biegeleisen · Accepted Answer · 2016-07-07 07:48:29Z

1

You can use SimpleDateFormat to get a string which is in proper format to be used as a date in SQLite, the proper format being yyyy-MM-dd:

String pattern = "yyyy-MM-dd";
SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
String stringFromDate = dateFormat.format(fromDate.getDate());

answered Jul 7, 2016 at 7:48

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joppiealiw Over a year ago

This fixed it! Thanks a lot!

Mark Rotteveel · Accepted Answer · 2016-07-07 08:44:41Z

1

Seems like you're trying to re-invent the wheel here. PreparedStatements were created exactly for this usecase - setting variable values in a predefined structure. In your case, with the setDate method. From the context I'm guessing your fromDate and toDate variables are java.util.Date instances, so you'll have to convert them to java.sql.Dates

java.sql.Date fromDateToBind = new java.sql.Date(fromDate);
java.sql.Date toDateToBind = new java.sql.Date(toDate);

String query = "Select * from tblSavings where date between ? and ?";

try{ 
    pstmt = conn.prepareStatement(query);
    pstmt.setDate(1, fromDateToBind);
    pstmt.setDate(2, toDateToBind);
    rs = pstmt.executeQuery();

    // use the results...
} // etc...

edited Jul 7, 2016 at 8:44

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jul 7, 2016 at 7:47

Mureinik

316k54 gold badges400 silver badges405 bronze badges

1 Comment

joppiealiw Over a year ago

I've used what Tim suggested and it worked. I'll keep this in mind as well and will try on the next project I'm gonna make. Thanks!

Collectives™ on Stack Overflow

SQL Statement - Java

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related