Link to home
Start Free TrialLog in
Avatar of glynco
glynco

asked on

How can I enter a variable on a ResultSet Execute Query Where clause?

This is the Query I need to put a variable on the Where clause. Variable is itemnamestr that will be entered by the user.
rs = stmt.executeQuery("SELECT itemid, itemname FROM inventorytable WHERE itemname = itemnamestr ");
I got this error when putting itemnamestr as variable on SQL Where clause

Got an exception!
Unknown column 'itemnamestr' in 'where clause'

How can I correctly do it?
import java.sql.*; 
import javax.swing.*;
public class NewClass {
    
 
  
    public static void main (String[] args) { 
        String itemnamestr;
        
        itemnamestr = JOptionPane.showInputDialog(null, "Enter item name.");
 
        try { 
            String url = "jdbc:mysql://localhost:3306/test1"; 
            Connection conn = DriverManager.getConnection(url,"root","mypassword"); 
            Statement stmt = conn.createStatement(); 
            ResultSet rs; 
  
            rs = stmt.executeQuery("SELECT itemid, itemname FROM inventorytable WHERE itemname = itemnamestr "); 
            while ( rs.next() ) { 
                String itemidvar = rs.getString("itemid"); 
                System.out.println(itemidvar); 
                String itemnamevar = rs.getString("itemname"); 
                System.out.println(itemnamevar); 
            } 
            conn.close(); 
        } catch (Exception e) { 
            System.err.println("Got an exception! "); 
            System.err.println(e.getMessage()); 
        } 
    } 
}

Open in new window

Avatar of chaitu chaitu
chaitu chaitu
Flag of India image

you have to pass variable like this.the string to be in single quote.

   rs = stmt.executeQuery("SELECT itemid, itemname FROM inventorytable WHERE itemname = ' "+itemnamestr +" ' " );
       
instead of statement use PreparedStatement;

 PreparedStatement stmt = conn.prepareStatement(("SELECT itemid, itemname FROM inventorytable WHERE itemname = ?");
   
          stsmt.setString(1,itemnamestr);
          rs=stsmt.executeQuery();
   
Avatar of glynco
glynco

ASKER

rs = stmt.executeQuery("SELECT itemid, itemname FROM inventorytable WHERE itemname = ' "+itemnamestr +" ' " );

The records do not show-up.
ASKER CERTIFIED SOLUTION
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial