CODEWITHSUNDEEP
javamysqlsql
BigBug
BigBug

Reputation: 6290

SQL select statement with where clause

how would i write this sql statement without a hard coded value?

resultSet = statement
    .executeQuery("select * from myDatabase.myTable where name = 'john'");
// this works

rather have something like:

String name = "john"; 
resultSet = statement
    .executeQuery("select * from myDatabase.myTable where name =" + name);
// Unknown column 'john' in 'where clause' at
// sun.reflect.NativeConstructorAccessorImpl.newInstance0...etc...

thanks in advance..

Upvotes: 5

Views: 87239

Answers (6)

Isaac
Isaac

Reputation: 16736

It is a terrible idea to construct SQL queries the way you currently do, as it opens the door to all sorts of SQL injection attacks. To do this properly, you'll have to use Prepared Statements instead. This will also resolve all sorts of escaping issues that you're evidently having at the moment.

PreparedStatement statement = connection.prepareStatement("select * from myDatabase.myTable where name = ?");    
statement.setString(1, name);    
ResultSet resultSet = statement.executeQuery();

Note that prepareStatement() is an expensive call (unless your application server uses statement caching and other similar facilities). Theoretically, it'd be best if you prepare the statement once, and then reuse it multiple times (though not concurrently):

String[] names = new String[] {"Isaac", "Hello"};
PreparedStatement statement = connection.prepareStatement("select * from myDatabase.myTable where name = ?");

for (String name: names) {
    statement.setString(1, name);    
    ResultSet resultSet = statement.executeQuery();
    ...
    ...
    statement.clearParameters();
}

Upvotes: 26

user2001714
user2001714

Reputation:

this should work:

String name = "john"; 
resultSet = statement
    .executeQuery("select * from myDatabase.myTable where name =" + "'" + name + "'");

Upvotes: 1

c.pramod
c.pramod

Reputation: 606

Try the following :

String name = "john"; 

resultSet = statement
      .executeQuery("select * from myDatabase.myTable where myTable.name = '" + name + "'");

Upvotes: 0

piokuc
piokuc

Reputation: 26204

You are missing the single quotes around your string, your code corrected:

String name = "john";
String sql = "select * from myDatabase.myTable where name = '" + name + "'";
// Examine the text of the query in the debugger, log it or print it out using System.out.println
resultSet = statement.executeQuery(sql);

Print out / log text of the query before executing the query to see if it looks OK.

If you are going to do a lot of similar queries where only the constant changes, consider using prepared statements

Upvotes: 4

Matt K
Matt K

Reputation: 6708

Put quotes around your name value since it's a string.

"select * from myDatabase.myTable where name ='" + name + "'"

Upvotes: -1

Z .
Z .

Reputation: 12837

you need to put quotes around the value ('john' instead of john)...

Upvotes: 0

Related Questions