Using variables in where clause of SQL statement in JAVA

Question

I want to select some rows from my database based on the values stored in my variables (supp_rec array). I have used tips on similar previous post/question. However, I am still having some error messages. The code snippet is below:

waiting_time = dbMngr.runQuery( "SELECT " + "ExpectedWaitingTime" + " FROM Student" +     "where deptID = '" + supp_rec[1] + "' and weight = '" + supp_rec[2] + "'");
prob = PoisonModel(Phase3GUI.existence_time -     Long.parseLong(waiting_time[0]),2,Phase3GUI.existence_time);
if (prob > 0.5)
 {
    dbMngr.execUpdate( "DELETE " + " FROM Student" + "where deptID NOT IN" + supp_rec[1] + " and weight NOT IN" + supp_rec[2]);
 }+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The error message is below:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'deptID NOT IN1 and weight NOT IN8' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '1' and weight = '8'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Please I will appreciate any help on how to solve these errors. Thank you.

Use placeholders. Update the code to that first and then ask again. In any case, if you look at the actual SQL text you'll immediately see several syntax problems. — user2864740
– user2864740, Commented Jan 25, 2014 at 19:32
This is a very messy way of doing things. Instead of "SELECT "+ variable " rest of statement;" try String.format("SELECT %s rest of statement;", variable); — 735Tesla
– 735Tesla, Commented Jan 25, 2014 at 19:33
@735Tesla But using String.format for arbitrary values is a poor way of handling the overall task here. — user2864740
– user2864740, Commented Jan 25, 2014 at 19:34
Also I don't know if you wanted it this way but enclosing ExpectedWaitingTime in quotes makes it interpret the string letter ally instead of looking at the variable's value. If it isn't a variable, why not just put it all in one string? — 735Tesla
– 735Tesla, Commented Jan 25, 2014 at 19:34
@735Tesla It honestly doesn't matter. Teach good techniques that can be consistently applied. — user2864740
– user2864740, Commented Jan 25, 2014 at 19:38

Orel Eraki · Accepted Answer · 2014-01-25 19:36:33Z

1

Added () between the variables content, spaces between them and formatted a little bit

dbMngr.execUpdate(String.format("DELETE FROM Student WHERE deptID NOT IN (%s) and weight NOT IN (%s)", supp_rec[1], supp_rec[2]));

Additionally, It's highly risky for you, not be using protections against SQL Injections

answered Jan 25, 2014 at 19:36

Orel Eraki

12.2k4 gold badges31 silver badges38 bronze badges

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

Comments

zodvik · Accepted Answer · 2014-01-25 20:21:06Z

1

The mysql error message is

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'deptID NOT IN1 and weight NOT IN8' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

So, without the space, mysql tries to parse IN1 as a part of it's syntax & hence the query fails. It should be IN 1 where IN is valid SQL token.

To correct this, add a space after IN in your statement -

dbMngr.execUpdate( "DELETE " + " FROM Student" + "where deptID NOT IN " + supp_rec[1] + " and weight NOT IN " + supp_rec[2]);

EDIT : Noticed that you're passing a single value for NOT IN. In that case, it should be just != operator.

dbMngr.execUpdate( "DELETE " + " FROM Student" + "where deptID != " + supp_rec[1] + " and weight != " + supp_rec[2]);

edited Jan 25, 2014 at 20:21

answered Jan 25, 2014 at 19:35

zodvik

9212 gold badges9 silver badges22 bronze badges

1 Comment

user2864740 Over a year ago

There are still errors. (At least one missing space, and I suspect that supp_rec[x] aren't valid as IN operators - in particular because I suspect they are missing parenthesis, but I'm not entirely sure what they are supposed to contain.)

Samuel Edwin Ward · Accepted Answer · 2014-01-25 19:50:26Z

0

You should really use a PreparedStatement. The SQL will be much easier to read and you'll avoid most of these problems.

answered Jan 25, 2014 at 19:50

Samuel Edwin Ward

6,6953 gold badges38 silver badges63 bronze badges

1 Comment

user3048253 Over a year ago

Thank you all for your comments. Error now resolved.

Collectives™ on Stack Overflow

Using variables in where clause of SQL statement in JAVA

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related