Java Oracle JDBC SELECT statement

Question

I am practising Oracle JDBC using Java in Eclipse. I understood how to output SELECT * from product by iterating each line of the table using .next(). I am stuggling to output this statement:

SELECT pid, pname 
FROM product 
WHERE price>20

Here is my code:

import java.sql.*;

public class intro {

    /**
     * @param args
     */
    public static void main(String[] args)
    {
    //  throws SQLException
        
        //initiazlie the connection
        
        Connection con=null;
        
        try //try connection to database
        {
            //load driver
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("Oracle JDBC driver loaded ok.");
            con=DriverManager.getConnection("jdbc:oracle:thin:test/123321@localhost:1521:orcl");
            System.out.println("Connect with @oracle:1521:orcl");
            
            //declaring statement
            Statement stmt = con.createStatement();
            
            String dropProductTable="drop table product cascade constraints";
            
            //create string
            String createProductTable="CREATE TABLE product(" +
             "pid number," +
             "pname CHAR(20)," +
             "price number," +
             "PRIMARY KEY (pid)" +
             ")"; //do not add the semicolon(;) after closing the parenthesis.
            
            
            /*drop table */
            stmt.executeUpdate(dropProductTable);
            
            
            //execute the create statement
            stmt.executeUpdate(createProductTable);//execure the create statement
            
            //create string that holds the insert statement
            String insertIntoProduct="INSERT INTO product VALUES (1,'Pepsi',10)";
            String insertIntoProduct1="INSERT INTO product VALUES (2,'Fanta',20)";
            String insertIntoProduct2="INSERT INTO product VALUES (3,'Mirinda',30)";
            String insertIntoProduct3="INSERT INTO product VALUES (4,'Gum',5)";
            String updatePrice="UPDATE product set price=55 where price=20";


            
            //stmt.executeUpdate(insertIntoProduct);
            stmt.executeUpdate(insertIntoProduct);
            stmt.executeUpdate(insertIntoProduct1);
            stmt.executeUpdate(insertIntoProduct2);
            stmt.executeUpdate(insertIntoProduct3);

           //update statement
            stmt.executeUpdate(updatePrice);
            

            
            //save the select statement in a string
            String selectStat="SELECT * FROM product";
            String selectProduct="SELECT pid, pname from product where price>20";
            //stmt.executeUpdate(selectStat);
            
            //create a result set
            ResultSet rows = stmt.executeQuery(selectStat);
            ResultSet rows1= stmt.executeQuery(selectProduct);
            
            //stmt.executeQuery(selectStat);
            
            
            int count=0;
            while (rows.next()) {
                count+=1;
                String productNumber = rows.getString("pid");
                String productName = rows.getString("pname");
                String productPrice = rows.getString("price");
                System.out.println("Row #:"+count);
                System.out.println("Product#: "+productNumber);
                System.out.println("Product Name: "+productName);
                System.out.println("Price: "+productPrice);
            
                }
            
            int count1=0;
            while (rows1.next()) {
                count1+=1;
                String productNumber = rows1.getString("pid");
                String productName = rows1.getString("pname");
                String productPrice = rows1.getString("price");
                System.out.println("Row #:"+count);
                System.out.println("Product#: "+productNumber);
                System.out.println("Product Name: "+productName);
                System.out.println("Price: "+productPrice);
            
                }
            
            con.close();
            
        }
                catch (Exception e)
                {
                    System.err.println("Exception:"+e.getMessage());
                }
                
        
        }
        
    }

When I am trying to ouput the selectProduct variable I get this error:

Exception:Invalid column name

Here is the output I am getting:

Oracle JDBC driver loaded ok.
Connect with @oracle:1521:orcl
Row #:0
Product#: 2
Product Name: Fanta               
Price: 55
Row #:0
Product#: 3
Product Name: Mirinda             
Price: 30

When you do the selectStat query, what are the columns returned? — Adel Boutros
– Adel Boutros, Commented Jan 8, 2012 at 14:49
The problem will be in one of your SQL calls. I strongly recommend splitting your logic into discrete methods - each should only do one thing. Also output the stack trace, as it would tell you where in your program you're geting the problem, making errors quicker to diagnose. — Dan Hardiker
– Dan Hardiker, Commented Jan 8, 2012 at 14:51

Aaron · Accepted Answer · 2012-01-08 14:48:52Z

10

In your SELECT you are only getting "pid" and "pname":

String selectProduct="SELECT pid, pname from product...

But then you're trying to use a field that isn't in your SELECT:

String productPrice = rows1.getString("price");

Try putting "price" in your SELECT clause.

answered Jan 8, 2012 at 14:48

Aaron

58k11 gold badges123 silver badges142 bronze badges

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

1 Comment

Aaron Over a year ago

Yes, you are correct that each result set should be processed in separate loops.

Sathyajith Bhat · Accepted Answer · 2012-01-08 14:51:51Z

7

You have to replace

SELECT pid, pname from product where price>20;

with

SELECT pid, pname, price from product where price>20;

edited Jan 8, 2012 at 14:51

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

answered Jan 8, 2012 at 14:50

uahakan

5961 gold badge8 silver badges23 bronze badges

Collectives™ on Stack Overflow

Java Oracle JDBC SELECT statement

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related