0

I am trying to query my database by using a Java function with another attribute defined in the database. The statement generates no error. However, the output is wrong. The result of the output is null but from my checking it is not null. Please can anyone tell me what I need to do? How can I use JAVA functions in SQl statements?

expired_rows = dbMngr.runQuery(String.format("SELECT ID FROM Student WHERE 'System.currentTimeMillis()' - ArrivalTime > (%s) ", 5000));}

if (expired_rows == null) { 
    System.out.println("The number of expired row is " + expired_rows);
}
if (expired_rows != null) { 
    System.out.println("The number of expired row is " + expired_rows.length );
}
takendarkk
  • 3,347
  • 8
  • 25
  • 37
user3048253
  • 1
  • 1
  • 4
  • `long l = System.currentTimeMillis();` Then use the value of `l` in your SQL statement. – takendarkk Mar 17 '14 at 21:11
  • Use [prepared statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) – GriffeyDog Mar 17 '14 at 21:11
  • Got some error using of prepd stmt.long l = System.currentTimeMillis(); String query = "select id from Student where ? - ArrivalTime > 5000"; try { PreparedStatement expired_row = con.prepareStatement(query); expired_row.setLong(1, l); expired_row.execute(); ResultSet res = expired_row.getResultSet(); if (res == null) { System.out.println("The number of expired row is " + res); expired_rows.length ); } if (res != null) { System.out.println("The number of expired row is " + res); System.out.println("The number of expired row is " + res.length ); } – user3048253 Mar 17 '14 at 22:41
  • ResultSet res =expired_row.executeQuery(); Then count the number of rows via a `while (rs.next){i++)` sort of loop. I think the null checks are redundant - executeQuery will either throw a SQLException or return a ResultSet - I don't think it will ever return a null, and if it did it would generally mean that the error is completely unrecoverable – DaveH Mar 17 '14 at 22:52
  • Thanks Dave, i am still getting an error: Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException. Do you feel this could be the cause: String query = "select id from Student where ? - ArrivalTime > 5000"? – user3048253 Mar 17 '14 at 23:15
  • Which line does the stacktrace indicate has the problem? – DaveH Mar 18 '14 at 07:16
  • PreparedStatement expired_row = con.prepareStatement(query); – user3048253 Mar 18 '14 at 08:23
  • I'd say that you haven't successfully got a connection to the database in that case. Are you sure that `con` is not null? – DaveH Mar 18 '14 at 09:53

1 Answers1

0

You can't use the java function in the way you are trying to. You are just passing the String "'System.currentTimeMillis()'" to the dbms - you want to pass in the result of evaluating that function

Would 

"SELECT ID FROM Student WHERE  %l - ArrivalTime > (%s) ",System.currentTimeMillis(), 5000));}"

work? ( I'm unsure of the syntax for String.format and don;t have acces to the docs, but hopefully you get the picture ... )

A better solution, but a little further away from the work you have already done, would be to use a PreparedStatement as suggested by GriffeyDog.

Also, if no error is being generated, then it seems a quite likely that your dbMngr class is swallowing the exceptions without reporting them.

DaveH
  • 7,187
  • 5
  • 32
  • 53