java returns empty String value for oracle VARCHAR

问题:

I have the following code which appears to work correctly but it does not display any values for the personCode string. PERSON_CODE is a VARCHAR2 in an Oracle 9i database.

I am using Java SE 1.7 and ojdbc7.jar for my project. I am new to Java can anybody give me some help with this?

private static void GetEmployee(String input) {
String output = "";
Connection con=null;
PreparedStatement stmt = null;
String sql ="SELECT ALL BADGE_NUMBER, PERSON_CODE FROM BADGETABLE WHERE BADGE_NUMBER = ?";

try {
    //load driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con=DriverManager.getConnection("jdbc:oracle:thin:username/password@host:1521:database");

    //declaring statement
    stmt = con.prepareStatement(sql);
    stmt.setString(1, input);

    // execute query
    ResultSet rows = stmt.executeQuery();

    int i = 0;
    while(rows.next()) {
        i++;
        String badgeCode = rows.getString(1);
        String personCode = rows.getString(2);
        String personType = rows.getString(3);
        System.out.println("Badge number: " + badgeCode);
        System.out.println("Employee ID: " + personCode);
    }
    System.out.println("Number of results: " + i);


    rows.close();    // All done with that resultset
    stmt.close();  // All done with that statement
    con.close();  // All done with that DB connection


}
catch (SQLException e) {
    System.err.println(e);
} 
catch (ClassNotFoundException e) {
    System.err.println(e);
}

return;
}

回答1:

I ran into this same problem using:

1. Oracle 9i Enterprise Edition 64bit (JServer Rlease 9.2.0.1.0 - Production)
2. JDBC 12.1.0.1.0 - ojdbc7.jar
3. Java OpenJDK 64bit, 1.7.0_09-icedtea

with a table like this: create table person ( first_name varchar2(60) );

And query like this using sqlline: select first_name, cast(substr(first_name,0,1) as char) from person;

Would have a result set of ["","S"].

I did not have any other Oracle jars on my class path as was found to be problem for others, but when I switched from ojdbc7.jar to ojdbc6_g.jar this problem resolved. This is driver version 11.2.0.3.0 which is under the 12c download section.

回答2:

Look at your query :

String sql = "SELECT ALL BADGE_NUMBER, PERSON_CODE FROM BADGETABLE WHERE BADGE_NUMBER = ?";

The below code will throw Exception :

String badgeCode = rows.getString(1);
String personCode = rows.getString(2);
// there is no third column in your resultset
String personType = rows.getString(3); 

You can change your query to (if you don't want to use the column names):

String sql = "SELECT * FROM BADGETABLE WHERE BADGE_NUMBER = ?";

Or specify the third column :

String sql = "SELECT ALL BADGE_NUMBER, PERSON_CODE ,PERSON_TYPE FROM BADGETABLE WHERE BADGE_NUMBER = ?";

And retrieve data using the column name :

String badgeCode = rows.getString("BADGE_NUMBER");
String personCode = rows.getString("PERSON_CODE");
String personType = rows.getString("PERSON_TYPE");

Also , move the close() statements under the finally block :

} finally {
 try { rows.close(); } catch (Exception e) {  }
 try { stmt.close(); } catch (Exception e) {  }
 try { con.close(); } catch (Exception e) {  }
}

回答3:

Same problem when using:

 Oracle 9i Enterprise Edition 64bit 9.2.0.8.0 - on Sun Server Sun OS 10.
    JDBC 12.1.0.2.0 - ojdbc7.jar (thin driver)
    manifest info: Created-By: 20.75-b01 (Sun Microsystems Inc.)  
Implementation Vendor: Oracle Corporation Implementation-Version: 12.1.0.2.0

For me helped this solution, it is not the best one but it worked:

select to_clob(field_name) as field_name from table_name

Also if you are sure about the length of the field this solution might be helpful:

  select cast(field_name as char(10)) as field_name from table_name

回答4:

I am happy to resport that I solved this problem, but, it was an extremely long and painfull search in my code, when in fact, the problem was actually in my Glassfish server.

I read somewhere on this site that someone had a similar problem and they had REMOVED some jar's with the following name: ojdbc18???.jar . When I searched for this, I didn't find any, so I moved on.

After pulling out most of my hair, I decided to start browsing around on the webserver, and sure enough, there were other jars in the lib dir of the WEB-SERVER, which would be visible to my app.

Those jar's were ojdbc14???.jar. Wholly freak. As soon as I DELETED those ojdbc14's, everythig worked fine.

To that end, search your class path and make sure jar's like that can not be found.

Good luck, Nick

回答5:

Ignoring the all in your SQL, your basic problem is that you try to get 3 fields, when your sql query only selects 2 :

SELECT ALL BADGE_NUMBER, PERSON_CODE FROM BADGETABLE ...