Result Set empty with Prepared Statement in java against Oracle



I have the following code that is not returning any data to the ResultSet defined:

Connection _con = null;
PreparedStatement _ps = null;
ResultSet _rs = null;
UsuarioBean _Ret = null;

_con = (new ConEvaluacionProveedores()).getConexion();
_ps = _con.prepareStatement("SELECT * from EPUsuario WHERE UsuCod = ? AND UsuPsw = ?");
_ps.setInt(1, pUsuario.getCedula());
_ps.setString(2, pUsuario.getContrasenia());

_rs = _ps.executeQuery();
while ({
    _Ret = BuscarUsuario(pUsuario);

The DB against which I run it is Oracle , and if I execute exactly the same against Postgres it returns the data correctly.

If I make the query using java.sql.Statement , it returns data correctly.

Is there a restriction on the use of java.sql.PreparedStatement against Oracle or something that you may not be taking into account?

Thank you very much already.

asked by Guillermo 05.06.2017 в 16:31

2 answers


It is known that in Oracle, queries with WHERE that include fields of type CHAR usually give problems in the sense that, if you have for example the column UsuPsw of type CHAR (9) and in it you have this: 123456 .

                        habrá espacios en blanco 
                        hasta llegar a un total de nueve caracteres.

when wanting to compare it with the string 123456 will not match, because having a field% co_of% of fixed size, Oracle will fill it to the right with blank characters.

Several solutions exist:

  • Use CHAR to avoid empty blanks.
  • Define the field of type TRIM instead of VARCHAR
  • Tell the query to add the possible blank fill characters with CHAR

There are other solutions and considerations to take into account, depending on the context of your application, the data you handle and how you handle them, apply the most effective one.

Here you can find other details that might help.

answered by 05.06.2017 / 20:37

There is no difference in Oracle that causes an error such as the one you mention, however there are some differences between the default configuration of one engine and another , and maybe that's where your problem comes from.

For example, Oracle is usually configured by default to distinguish uppercase from lowercase, unlike other engines, so your query could work simply by changing it to this one:

SELECT * from EPUsuario WHERE UPPER(UsuCod) = ? AND UPPER(UsuPsw) = ?

And of course doing .toUpperCase() of input strings.

Another problem I see in your code is that you do not close any resources . I imagine that you have not controlled the exceptions because this code is an example for this web, but you should always close the resources in a block finally or, if you have Java7 or higher, using a try-with-resources.

answered by 05.06.2017 в 17:49