Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jul 2008
    Location
    Falls Chuch, VA
    Posts
    30
    Thanks
    0
    Thanked 1 Time in 1 Post

    writing a SQL statement in Java

    Hi.

    I have written the following Sql statement in my java code.

    Code:
    String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '+city+' AND State = '+state+'";
                rsZipCodes = oStatement.executeQuery(str);
    Now when i am running the above statement in my query browser i am getting the following sql statement.
    Code:
    SELECT ZipCode,State FROM zipcodes WHERE City ='+city+' AND State = '+state+';
    In the above statement the city and state value is passed from the user.

    But i need the below Sql statement to get the correct output.
    Code:
    SELECT ZipCode,State FROM zipcodes WHERE City = 'TAMPA' AND State ='FL';
    Finally my problem is with dealing with the double codes and single codes in the WHERE clause.

    Anybody know how to deal with this.
    Thanks in Advance.

  • #2
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    Quote Originally Posted by adi501 View Post
    Code:
    String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '+city+' AND State = '+state+'";
                rsZipCodes = oStatement.executeQuery(str);
    Code:
    String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '"+city+"' AND State = '"+state+"'";
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • #3
    Regular Coder brad211987's Avatar
    Join Date
    Sep 2005
    Location
    Ohio
    Posts
    631
    Thanks
    10
    Thanked 50 Times in 50 Posts
    Also look into using prepared statements, they are considered more secure and I find them easier to use.

  • #4
    Senior Coder jerry62704's Avatar
    Join Date
    Oct 2007
    Location
    Springfield, IL
    Posts
    1,100
    Thanks
    13
    Thanked 87 Times in 87 Posts
    You have to put strings in quotes. Where you have ('+city+') to concatenate the variable "city" to the string you are building, you need to tell the system that the value of "city" is a literal. Just add a quote before and after it inside of the rest of the string parts.

    String str = "SELECT ZipCode,State FROM zipcodes WHERE City = '" + city + "' AND State = '" + state + "'";

    making the first part:

    "SELECT ZipCode, State FROM zipcodes WHERE City = '"
    and the next part:
    + city + "' ...
    ending the quotes around the variable. Same thing with the last variable:
    "' AND State = '" + state + "'";

    BTW, you might use prepared statements which eliminates the confusion of quotes on strings and dates, but if you understand them you don't need it. If you do use it, it would look like this:

    Code:
    PreparedStatement pstmt = this.conn.prepareStatement(SQLStrings.getInsertUserQuery());
    
    pstmt.setString(1, this.loginForm.getJdbcUserName());
    pstmt.setString(2, this.loginForm.getUserPassword());
    pstmt.setString(3, this.loginForm.getUserID());
    pstmt.executeUpdate();
    Where the sql statement would look like this:
    Code:
    String insertUserQuery = 
    	"insert into \"DHSDB2\".\"JV_PERSON\" " +
    		"(\"USERID\", \"PASSWORD\", \"NAME\") " + 
    	"values(?, ?, ?)";
    The "?" in the query are substituted to the numbered parameters in the prepared statement.
    .
    .
    ...and gladly would he learn and gladly teach

    Visit www.LiberalsWin.com for humor and the unique Bush/Obama Approval Polls


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •