SQL select column with conditions JAVA

user_16136595 0
Viewed 361

I tried to get specific value where the column of names in my database contains "jansen",but when i run my code it shows error like this

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'jansen' in 'where clause'

here is my code

try {
     Statement stmt=MyConnection.getConnection().createStatement();
     ResultSet rs=stmt.executeQuery("select * from emp where name = jansen");
     while(rs.next())
         System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
     }catch (Exception e){
          System.out.println(e);
    }

[and here is my database in Sqlyog]

1

select sql java
2 Answers

You should put quotes around string values in your SQL statement.

"select * from emp where name = 'jansen'"

Another issue you may run into later, is that your resources (e.g. connection, statement, ...) aren't cleaned up after you've used them. You should actually call the close() function after using it. You would then have to put that code inside a finally block, and again catch it to prevent having the close function itself throwing exceptions. Because it would be messy and bloated, there's a better solution to clean things up:

Create your statement between the braces of your try, and it will close it cleanly for you. (It's called a try-with-resources statement)

try (Statement stmt = MyConnection.getConnection().createStatement()) {
  ResultSet rs = stmt.executeQuery(query);
  while (rs.next()) {
    ...
  }
} catch (SQLException e) {
  System.out.println(e);
}

You can read more about executing SQL statements in java, in the official tutorial.

user_18339610

You should use a PreparedStatement instead of Statement in this way:


PreparedStatement statement = MyConnection.getConnection().prepareStatement("select * from emp where name = :empId");
statement.setString(empId, "jansen");
ResultSet rs = statement.executeQuery();

Then "jansen" would be replaced by String variable with dynamic name.

user_119715030
Related