Use a CHAR field in the WHERE clause in a PreparedStatement – Real’s Java How-to

Posted on by mtoplistChuyên mục:Top List

Use a CHAR field in the WHERE clause in a PreparedStatement

Tag(s): JDBC

About cookies on this site

We use cookies to collect and analyze information on site performance and usage,
to provide social media features and to enhance and customize content and advertisements.

With Oracle, if a CHAR field used in a WHERE clause contains trailing spaces then the trailing
spaces must be there, there is no automatic trimming.

With Oracle, if a CHAR field used in a WHERE clause contains trailing spaces then the trailing spaces must be there, there is no automatic trimming.

For example, if the “code_value” field is defined as CHAR(10) and the content is
“A10” then the real value is “A10       ” and the trailing
spaces must be present in the comparaison to have a match.

The following PreparedStatement will fail to retrieve the value : 

 PreparedStatement ps;
 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE code_value= ?") ;
 ps.setString(1,"A10");
 rs = ps.executeQuery();
 while (rs.next())
    System.out.println("results: " + rs.getString(1));

PreparedStatement ps;
 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE code_value= ?") ;
 ps.setString(1,"A10          ");
 rs = ps.executeQuery();
 while (rs.next())
    System.out.println("results: " + rs.getString(1));

 Statement s;
 s= conn.createStatement();
 String val = "A10";
 String sql ="SELECT desc_value from prod.DICT_VALUES WHERE code_value='" + val + "'" ;
 ResultSet rs = s.executeQuery(sql);
 while (rs.next())
   System.out.println("results: " + rs.getString(1));

But this one is okThe easy fix is to define the field as a VARCHAR not a CHAR or replace the PreparedStatement with a Statement.To keep a PreparedStatement, you need to add explicitly the trailing spaces or trim the value.

The first try is to trim the value with the rtrim() function. 

 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE rtrim(code_value)= ?") ;
 ps.setString(1,"A10");
 rs = ps.executeQuery();
 while (rs.next())
   System.out.println("results: " + rs.getString(1));

This is working fine but Oracle will not use the index and probably perform a table scan which is not a good thing!

Oracle provides an API to make sure that the parameter received is handled as a CHAR value by the database. 

import oracle.jdbc.OraclePreparedStatement;
...
PreparedStatement ps;

ps = conn.prepareStatement("SELECT dewsc_value from prod.DICT_VALUES WHERE code_value= ?") ;
((OraclePreparedStatement)ps).setFixedCHAR(1, "A10");
rs = ps.executeQuery();
while (rs.next())
   System.out.println("results: " + rs.getString(1));

The good thing is that we don’t need to know the CHAR width of the field, the driver will figure it out for us but using the special Oracle class can be problem.

A better solution is to instruct the database to add the missing trailing spaces with the rpad() function. 

 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE code_value= rpad(?, 32, ' ') ") ;
 ps.setString(1,"A10");
 rs = ps.executeQuery();
 while (rs.next())
   System.out.println("results: " + rs.getString(1));

This a better solution because we are not using a special Oracle class and the database will use the index.

mtoplist