ORA-00936 error when running through java class

Question

I am required to use the OracleDataSource for a school project. I have created and tested my query on Oracle Developer and I get the proper output. When I try executing the query through my java code I get the following error: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression. I did some digging online and people say that it might be due to the WHERE clause. So I print out the query before executing it. The query is as such: SELECT b.ISBN, b.TITLE, COUNT(*) FROM BOOKS b JOIN BOOK_SUBJECT bs ON bs.ISBN = b.ISBN WHERE bs.SUBJECT_ID IN (47,46,43) GROUP BY b.ISBN, b.TITLE ORDER BY COUNT(*) DESC If I type this same query into Developer it works, but when I run my java class I get the ORA error. I am forming my query this way:

  Statement stmt = conn.createStatement();
  String query = "SELECT b.ISBN, b.TITLE, COUNT(*) FROM BOOKS b JOIN BOOK_SUBJECT bs ON bs.ISBN = b.ISBN WHERE bs.SUBJECT_ID IN (";
  PreparedStatement ps = conn.prepareStatement(query);

  for(int i = 0; i < args.length; i++) {
    //int psVar = i + 1; 
    //ps.setInt(psVar, Integer.parseInt(args[i]));
    if(i == args.length - 1) {
        query += args[i] + ")";
    } else {
        query += args[i] + ",";
    }
  }

  query += " GROUP BY b.ISBN, b.TITLE ORDER BY COUNT(*) DESC";

  //Execute the query
  System.out.println(query);
  ResultSet rset = ps.executeQuery();

I am lost, Thank you for your help

Answer 1

Your problem is that you are preparing the query before you have finished constructing it. Move the PreparedStatement ps = conn.prepareStatement(query); after the last assignment to query, and use placeholders for each of the arguments:

Statement stmt = conn.createStatement();
String query = "SELECT b.ISBN, b.TITLE, COUNT(*) FROM BOOKS b JOIN BOOK_SUBJECT bs ON bs.ISBN = b.ISBN WHERE bs.SUBJECT_ID IN (";

for(int i = 0; i < args.length; i++) {
  if(i == args.length - 1) {
    query += "?)";
  } else {
    query += "?, ";
  }
}

query += " GROUP BY b.ISBN, b.TITLE ORDER BY COUNT(*) DESC";
PreparedStatement ps = conn.prepareStatement(query);

for(int i = 0; i < args.length; i++) {
  int psVar = i + 1; 
  ps.setInt(psVar, Integer.parseInt(args[i]));
}

ResultSet rset = ps.executeQuery();