JDBC Returns empty result set,but records are present in database

Question

public class alldata {

public static void main(String arg[])
{

    String str;
    try
    {
       Class.forName("oracle.jdbc.driver.OracleDriver");

         Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","root");
           PreparedStatement stmt2;

         String sql="select * from RECORDDATA where ID= ? and COMPANY_NM= ?";
        stmt2=con.prepareStatement(sql);
         int i=5;
        str="Audi";
         try
         {

             stmt2.setInt(1, i);
             System.out.println("after setting id");
             stmt2.setString(2,str);
             System.out.println("after setting string");
         }
         catch(Exception iner)
         {
             System.out.println("int coulnm"+iner);
         }
        try
        { 

         ResultSet showdata=stmt2.executeQuery();
         System.out.println("after rs");


            while(showdata.next())
         {

             System.out.println("in next");
             System.out.println("\n"+showdata.getInt(1)+showdata.getString(2)+showdata.getString(3)+showdata.getString(4)+showdata.getString(5)+showdata.getString(6)+showdata.getString(7));
             System.out.println("after next");
         }
    }catch(Exception e)
    {
        e.printStackTrace();
    }
}catch(Exception e2)
{
    e2.printStackTrace();
}

}
}

if i uses hardcoded values ID=5 and COMPANY_NM=Audi it retrives 3 records from database... but my program accepts these value dynamically from user...so that is not working.... please help...i m stuck at this stage... showdata.next() method does not executing.. although there are 3 records are present in database.

Answer 1

If you expect more than one record to be returned, why can I not find a loop in your code to iterate those records?

Also, next() will retrieve the next record. Calling it twice means you're looking at the second retrieved record.

Now for other stuff:

• Java naming convention is a class name must start with uppercase letter.

• You don't need Class.forName("oracle.jdbc.driver.OracleDriver"). That isn't needed for JDBC drivers compatible with Java 6 or later.

• Remove all try-catch statements. You're catching errors, then letting code continue running as if nothing was wrong. Don't do that!

• You're not release the resources. Use try-with-resources for better resource management. It's also simpler to use.

class Alldata {
    public static void main(String arg[]) throws SQLException {
        int id = 5;
        String name = "Audi";

        try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "root")) {
            String sql = "select * from RECORDDATA where ID= ? and COMPANY_NM= ?";
            try (PreparedStatement stmt = con.prepareStatement(sql)) {
                stmt.setInt(1, id);
                stmt.setString(2, name);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        System.out.printf("\n%d, %s, %s, %s, %s, %s, %s%n", rs.getInt(1),
                                          rs.getString(2), rs.getString(3), rs.getString(4),
                                          rs.getString(5), rs.getString(6), rs.getString(7));
                    }
                }
            }
        }
    }
}

Note that you're using the system schema. You should not create user tables in the system schema.

User system should only be used for database maintenance, e.g. to create new users. It should never be used by an application.

---

UPDATE

From comments, it would appear that statement works when hardcoded, but not when using ? markers. Just to be sure I got that right, please try this code:

class Alldata {
    public static void main(String arg[]) throws SQLException {
        try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "root")) {
            String sql1 = "select count(*) from RECORDDATA where ID= ? and COMPANY_NM= ?";
            try (PreparedStatement stmt = con.prepareStatement(sql1)) {
                stmt.setInt(1, 5);
                stmt.setString(2, "Audi");
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next())
                        System.out.println("Count 1: " + rs.getInt(1));
                }
            }
            String sql2 = "select count(*) from RECORDDATA where ID= 5 and COMPANY_NM= 'Audi'";
            try (PreparedStatement stmt = con.prepareStatement(sql2)) {
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next())
                        System.out.println("Count 2: " + rs.getInt(1));
                }
            }
        }
    }
}

It should print the same number for both "Count 1" and "Count 2".