Reading data from mysql database using java

Question

Firstly, I'm reading the product name and number of products from user using jTextFields. For that product I read the product id and price from database using sql query. But in the below code I display the product price in a jtextField but while running tha file I get query executed successfully but I'm not getting anything in the jtextField.

And please check the sql query and resultset use, table name is "item" and database name is "myshop", I declared variables globelly and this code is in a jButton's 'ActionPeformed" part.

String item_name=name.getText();
int item_no=Integer.parseInt(no.getText());
String sql="SELECT id,price FROM item WHERE item.name='item_name'";
try{       
Class.forName("com.mysql.jdbc.Driver");
Connection con(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/myshop","root","mysql");
java.sql.Statement stmt=con.createStatement();
if (stmt.execute(sql)) {
rs = stmt.getResultSet();
JOptionPane.showMessageDialog(this, "succes","executed query",JOptionPane.PLAIN_MESSAGE);
} else {
System.err.println("select failed");}
int idIndex   = rs.findColumn("id");
int priceIndex    = rs.findColumn("price");

while(rs.next()){
item_id=rs.getInt(idIndex);
item_price=rs.getInt(priceIndex);
jTextField1.setText(""+item_price);//displaying product price in a jTextField1
jTextField2.setText(""+item_id);//displaying product id in a jTextField2  
  }
}
catch(Exception e){
JOptionPane.showMessageDialog(this, e.getMessage());
}

String sql = "SELECT id,price FROM item WHERE id=" + item_no; — Elliott Frisch
– Elliott Frisch, Commented Dec 17, 2013 at 5:51

Prabhakaran Ramaswamy · Accepted Answer · 2013-12-17 05:53:51Z

3

This line should be

String sql="SELECT id,price FROM item WHERE item.name='item_name'";

like this

String sql="SELECT id,price FROM item WHERE item.name='"+item_name+"'";

answered Dec 17, 2013 at 5:53

Prabhakaran Ramaswamy

26.1k10 gold badges61 silver badges65 bronze badges

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

4 Comments

Sachin Over a year ago

Please try to provide answer without SQL injections

Sachin Over a year ago

item.name='"+item_name+"'" this will lead to SQL Injection en.wikipedia.org/wiki/SQL_injection

Sachin Over a year ago

Also see Aniket's answer about SQL injection.

Sachin Over a year ago

Though he was asking what I am doing wrong. But still you have to provide much better code so that OP will also understand the good practice of coding

camickr · Accepted Answer · 2013-12-17 06:05:34Z

3

Use a PreparedStatement so you don't have to worry about delimiting all the variables:

String sql="SELECT id, price FROM item WHERE item.name = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString( 1, item_name);
ResultSet rs = stmt.executeQuery();

Then the prepared statement will replace the variable for you with the proper quotes.

edited Dec 17, 2013 at 6:05

answered Dec 17, 2013 at 5:59

camickr

325k21 gold badges174 silver badges293 bronze badges

Comments

Satheesh Cheveri · Accepted Answer · 2013-12-17 05:56:16Z

1

you would need to take item_name as param and put in quotes,

String sql="SELECT id,price FROM item WHERE item.name='"+ item_name+"'";

answered Dec 17, 2013 at 5:56

Satheesh Cheveri

3,7093 gold badges29 silver badges47 bronze badges

1 Comment

camickr Over a year ago

-1, this answer was given 3 minutes earlier. No need to clutter the forum with duplicate answers.

Aniket Kulkarni · Accepted Answer · 2013-12-17 06:01:10Z

0

Try to avoid this type of mistake by using PreparedStatement

String sql="SELECT id,price FROM item WHERE item.name=?";  
PreapredStatement ps = con.prepareStatement(sql);
ps.setString(1,item_name); 
ResultSet rs = ps.executeQuery();

Use of PreparedStatement also prevent SQL injection attack.

answered Dec 17, 2013 at 6:01

Aniket Kulkarni

13k10 gold badges70 silver badges94 bronze badges

Comments

Lijo · Accepted Answer · 2013-12-17 06:12:15Z

try this code .

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myshop","root","mysql");
            PreparedStatement pt=con.prepareStatement("SELECT id,price FROM item WHERE item.name=?");
            pt.setString(1,"item_name");
          ResultSet rs;
           if(pt.execute())
           {
                rs=pt.getResultSet();
               JOptionPane.showMessageDialog(this, "succes","executed query",JOptionPane.PLAIN_MESSAGE);
          }
           else {
    System.err.println("select failed");
           }

        while(rs.next()){
    item_id=rs.getInt(1);
    item_price=rs.getInt(2);
    jTextField1.setText(""+item_price);//displaying product price in a jTextField1
    jTextField2.setText(""+item_id);//displaying product id in a jTextField2    

        }

Mark Rotteveel · Accepted Answer · 2022-05-21 08:35:21Z

0

First, you need an reader, like this

private static void reader() throws SQLException {
    DataBaseName db = new DataBaseName ();
    names = db.getNames();
}

edited May 21, 2022 at 8:35

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

answered May 20, 2022 at 4:50

lordhunter

11 bronze badge

1 Comment

Mark Rotteveel Over a year ago

This seems entirely unrelated to the question asked.

Collectives™ on Stack Overflow

Reading data from mysql database using java

6 Answers 6

4 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related