Fetching value from the database

Question

I have a mysql table named 'customer'. serialnumber (int), name(varchar), gender(varchar) are the table fields.

I have a int variable a = 2.

I want to fetch the value from the table when serialnumber = a. That is when the serialnumber = 2 it should display the particular customer's name.

String url="jdbc:mysql://localhost/shopping";
String usr ="root";
String pass = "rootpassword";

session.setAttribute( "URL", url );
session.setAttribute( "user", usr );
session.setAttribute( "passwd", pass );

String connectionURL = (String)session.getAttribute( "URL" );
String user = (String)session.getAttribute("user");
String passwd = (String)session.getAttribute("passwd");
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      connection = DriverManager.getConnection(connectionURL, user, passwd);
      statement = connection.createStatement();

rs=statement.executeQuery("select * from customer where serialnumber like '%a%' ");

      while (rs.next()) {
      out.println("Customer Name: ");
      out.println(rs.getString("name")); 
     }rs.close();
    }catch(Exception ce){out.println(ce);}

This throws me nothing. But when i give the number instead a in rs.execute Query it displays the customer name. How to solve this and what is my mistake??

Thanks

And why are you using LIKE when you want to test equality? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 1, 2011 at 22:17

Adam Wenger · Accepted Answer · 2011-12-01 22:24:25Z

1

You are treating a like a literal, instead of a variable. You will need to update the query like this:

executeQuery("SELECT * FROM customer WHERE serialnumber LIKE '%" + a + "%' ");

I did not include rs = statement. for brevity, and to remove the scrolling. You will still need it for your solution.

@ypercube makes a good point: If you are looking to see when the serialnumber is exactly 2:

rs = statement.executeQuery("SELECT * FROM customer WHERE serialnumber = " + a);

Also, if you're only using the name field, you shouldn't SELECT * but instead SELECT name

edited Dec 1, 2011 at 22:24

answered Dec 1, 2011 at 22:11

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blessed one Over a year ago

Yeah, it was my bad not declare a as how it should be. I updated my query as you said and it worked just fine. Regarding the value of 'a' it is not fixed. it changes. Thanks.

Ivan Buttinoni · Accepted Answer · 2011-12-01 22:48:48Z

0

I don't think that LIKE is appropriate to an integer column, even if it work. Here a more formal/efficent/secure version:

String selectString = "SELECT * FROM customer WHERE serialnumber = ?";
selectStm = connection.prepareStatement(selectString);
selectStm.setInt(1, a.intValue());
rs = selectStm.executeQuery();

edited Dec 1, 2011 at 22:48

answered Dec 1, 2011 at 22:36

Ivan Buttinoni

4,1451 gold badge27 silver badges46 bronze badges

Collectives™ on Stack Overflow

Fetching value from the database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related