-1

I have a problem, I don't know how to SELECT columns FROM table in database WHERE datetime is some particular date.

I do like this:

 public List<Dezurstvo> vratiPredmeteISatnicuPocetkaIspita(Dezurstvo dezurstvo) throws SQLException, ParseException{
        SimpleDateFormat ulazniformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String ulaz = ulazniformat.format(dezurstvo.getDatum());

        Date izlaz = new Date(ulazniformat.parse(ulaz).getTime());

        String sqlVratiPredmeteISatnicuPocetkaIspita = "SELECT PredmetID, Datum FROM dezurstvo WHERE (NastavnikID = " + dezurstvo.getNastavnik().getNastavnikId() +") AND (Datum = " + izlaz + ")";
        Statement stat = konekcija.createStatement();
        ResultSet rs = stat.executeQuery(sqlVratiPredmeteISatnicuPocetkaIspita);

        List<Dezurstvo> listaDezurstava = new ArrayList();
        while(rs.next()){
        .............etc.

There is some code after that, but the problem is, there isn't error, but this rs.next() is empty, this ResultSet rs is actually empty, it doesn't find rows that matches whit this date, even thaw I insert date that exist in database in form from which I get date. It just doesn't do this while loop, it jumps over it, because I suppose rs is empty.

Probably the problem is because I don't know how to compare this two date. In my mySql database datetime format is this yyyy-MM-dd HH:mm:ss, so I tried to change format of date red from form to this database format because I thought this is the problem. But still doesn't work...

Now I think the problem is maybe this Date izlaz = new Date... is sql.Date and maybe it only contains yyyy-MM-dd but not HH:mm:ss...And it can`t compair...

Please help, I`m struggling with this for few days...

And this is different question from "Date Java to MySql DateTime" which somebody told it is duplicate, because I am not sure what is the reason why my code doesn`t work...

Svetlana
  • 61
  • 1
  • 2
  • 10
  • It is not the same question as Date Java to MySql DateTime , please read my question and you will see... – Svetlana Oct 10 '16 at 12:45
  • 2
    It actually *is* the same. If you use the answer to that question (use a PreparedStatement), you'll see that you can compare the date because it *automatically* gives you the correct format. – RealSkeptic Oct 10 '16 at 12:48

2 Answers2

1

IMHO the safest approach is using PreparedStatements, e.g.

PreparedStement ps=konekcija.prepareStatement("select .... where datum =?");
ps.setDate(1,  izlaz);
ResultSet rs=ps.executeQuery();

BTW it also helps protect you from sql injections.

Pelit Mamani
  • 2,321
  • 2
  • 13
  • 11
  • This also doesn`t work...It still sees rs as empty...I think the problem is my sql query actually looks like this when some values are inputed: SELECT PredmetID, Datum FROM dezurstvo WHERE (NastavnikID = 2) AND (Datum = '2016-06-15') and the problem maybe is that in database I have datetime format and no date looks like this...my date looks like:2016-06-15 18:18:29 and I think he can`t compair...maybe... – Svetlana Oct 10 '16 at 15:24
  • Sorry for misunderstanding: indeed java.sql.Date is only for days granularity, not for hours/minutes/etc. If you need them, use ps.setTimestamp(new Timestamp(yourTimeInMillis)) – Pelit Mamani Oct 11 '16 at 06:11
0

you may try this one! there are two returntypes for getDate() method of ResultSet object.

 java.util.Date getDate(int/String)
 java.sql.Date getDate(int/String)

please use the java.sql.Date one and then simple SimpleDateFormat for translation into String type. then this string type be compared if you want for a particular matching type! hope it works!

alkaramansari1@gmail.com
Alkaram Ansari
  • 79
  • 6
  • Hi, that is actually the solution. Yes, PreparedStatement should be used as Pelit said, but instead of ps.setDate(1, izlaz) it should be: SimpleDateFormat ulazniformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String ulaz = ulazniformat.format(dezurstvo.getDatum()); ps.setString(2, ulaz); – Svetlana Oct 10 '16 at 16:19
  • I wanted to put this as an answer but my question is closed but I hope this will help too. – Svetlana Oct 10 '16 at 16:20