CODEWITHSUNDEEP
javaoracle-databasejdbc
Akshaya
Akshaya

Reputation: 11

jdbc oracle 11g PreparedStatement not producing results

I am trying fetch data based on a condition on committee column using jdbc.Using Statement it produces the desired result but using PreparedStatement it does not.I cannot figure out what has gone wrong.Kindly help.Here is both the programs one using Statement and the other one using PreparedStatement and my table structure as well

import java.sql.*;

class SelectPrepared {
    public static void main(String args[]) {

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

            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "abcd","abcd");
            String sql = "select * from tatuserinfo where committee = 'GENERAL'";
            Statement stmt = con.createStatement();
            // stmt.setString(1,"GENERAL");//1 specifies the first parameter in the query

            ResultSet myRs = stmt.executeQuery(sql);
            while (myRs.next()) {
                System.out.println(myRs.getString(1) + myRs.getString(2) + myRs.getString(3) + myRs.getString(4)
                        + myRs.getString(5) + myRs.getString(6) + myRs.getString(7) + myRs.getString(8));
            }
            con.close();

        } catch (Exception e) {
            System.out.println(e);
        }

    }
}

import java.sql.*;

class SelectPreparedOne {
  public static void main(String args[]) {

    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      String s = "GENERAL";
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "abcd","abcd");
      String sql = "select * from tatuserinfo where committee = ?";
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setString(1, s);// 1 specifies the first parameter in the query

      ResultSet myRs = stmt.executeQuery();
      while (myRs.next()) {
        System.out.println(myRs.getString(1) + myRs.getString(2) + myRs.getString(3) + myRs.getString(4)
            + myRs.getString(5) + myRs.getString(6) + myRs.getString(7) + myRs.getString(8));
      }
      con.close();

    } catch (Exception e) {
      System.out.println(e);
    }

  }
}

Table structure

USERNAME          VARCHAR2(40) 
PASSWORD          VARCHAR2(40) 
ROLE              VARCHAR2(40) 
NAME              VARCHAR2(40) 
DESIGNATION       VARCHAR2(40) 
DEPARTMENT        VARCHAR2(40) 
EMAILID           VARCHAR2(40) 
COMMITTEE         CHAR(15)

Upvotes: 1

Views: 71

Answers (1)

user330315
user330315

Reputation:

TL/DR: don't use char use varchar2

CHAR(15) gets blank padded to 15 characters, so the column contains the value 'GENERAL ' and that's not equal to the supplied value of 'GENERAL'

The correct fix is to change the column to VARCHAR2(15)

An intermediate ugly workaround (until you fix the column definition) is to use trim:

where trim(committee) = ?;

Upvotes: 1

Related Questions