Solved

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

Posted on 2008-10-20
4
559 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:glynco
  • 3
4 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22764067
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 +" ' " );
       
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22764085
instead of statement use PreparedStatement;

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

Author Comment

by:glynco
ID: 22764104
rs = stmt.executeQuery("SELECT itemid, itemname FROM inventorytable WHERE itemname = ' "+itemnamestr +" ' " );

The records do not show-up.
0
 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 500 total points
ID: 22764115
may be space was there within the single quote in the string

the string should be 'itemvarable';

 itemname = '"+itemnamestr +"'" ;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question