Select value from database with date condition

Question

I have a JComboBox that contains months ( september , july ... ) And one jTable And a table (Bills) in database that contains ( Id bill , date , products ..)

I want that when a choose a value from jComboBox like september it give me all products in september .

And the date format is like 2014/May/27 14:31:04

and I tried this code but it didnt work because in java i cant use :

String sql1 = "select Products from Bills where Date LIKE "?????+jComboBox1.getSelectedItem()+*" ";

(it give an error and I can't compile)

The code :

try

{

    Class.forName(driver).newInstance();

    Connection con = (Connection)DriverManager.getConnection(url,user,pass);

    String sql1 = "select Products from Bills where Date LIKE "?????+jComboBox1.getSelectedItem()+* " ";

    PreparedStatement pst = con.prepareStatement(sql1);

    ResultSet rs = (ResultSet) pst.executeQuery(sql1);

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

}
catch( Exception e){

    JOptionPane.showMessageDialog(null, e);

}

best practise , sout the query, and check in the Database, and continue further — LMK, May 27 '14 at 12:50

score 0 · Answer 1 · answered May 27 '14 at 12:50

0

String sql1 = "select Products from Bills where Date LIKE "?????+jComboBox1.getSelectedItem()+* " ";

Why is like that ?? What are the questions marks?

Should be like that:

String sql1 = "select Products from Bills where Date LIKE '"+jComboBox1.getSelectedItem()+ "'";

answered May 27 '14 at 12:50

Aviad

1,539
1
9
24

yes I know but the date format is 2014/May/27 14:31:04 , and date type is string and the problem is that the conditon is : String sql1 = "select Products from Bills where Date LIKE ????'"+jComboBox1.getSelectedItem()+ "'* "; but didnt work – user3673594 May 27 '14 at 13:04
Well.. You should format your date with SimpleDateFormat Here is a nice tutorial: http://www.mkyong.com/java/java-date-and-calendar-examples/ Something like that: SimpleDateFormat("mm"); In addition after you do that your string will be: "select Products from Bills where Date LIKE '????/"+jComboBox1.getSelectedItem()+ "*'"; Something like that.. But i would suggest to use preparedStatment Here is a nice tutorial http://www.mkyong.com/jdbc/jdbc-preparestatement-example-select-list-of-the-records/ – Aviad May 27 '14 at 13:12
Well it cannot work, because the SQL wildcard is a '%' not a '*', so Your query should be "select Products from Bills where Date LIKE '%"+jComboBox1.getSelectedItem()+ "%' "; ... but again, not fuddle sql together, use parameters. – gorefest May 27 '14 at 13:14

score 0 · Answer 2 · edited May 23 '17 at 12:17

You are comparing apples and pears. Dates only can be compared to matching types. So if the column is of type date, you cannot use a String comparison on it (which LIKE actually is).

Your prepared statement should look sorta

...
Date d = new SimpleDateFormat("yyyy/mm/dd hh:mm:ss").parse(jComboBox1.getSelectedItem());
PreparedStatement pst = con.prepareStatement("SELECT PRODUCTS FROM BILLS WHERE DATE = :dv");
pst.setDate("dv", d);
...

Otherwise, You will run into trouble creating invalid SQL or - even worse - make Your software beeing endangered by SQL injection (security issue).

EDIT : Date algorithms always come up with the special flavour of the database vendor. You should check the DB manual for proper operands. For example, DB2 allows inline casting a date without any operand. See SQL query to select dates between two dates for some example.

date column is of type string , and date fromat : 2014/May/27 14:31:04 , for this reason i used LIKE " ?????+jComboBox1.getSelectedItem()+ * " — user3673594, May 27 '14 at 12:56
ok, then forget about the date cast - although it is no good design to use the wrong type ;-) A "DATE LIKE :dv" works if you simply make the value '%Sep%' — gorefest, May 27 '14 at 12:58

Select value from database with date condition

2 Answers2