JDBC returns nothing

Question

I am building an API with Spring and JDBC. I now ran into a problem where the JDBC query would not return anything, even though the same query in SQL Developer returns data.

This is my method in java:

public Standardtexte getStandardtexte(String kurztext)
{
    Standardtexte standardtexte = new Standardtexte();
    standardtexte.kurztext = kurztext;
    standardtexte.schluessel = jdbc.query("select schluessel, langtext from hb_standardtexte where kurztext = ?", (rs, rowNum) -> {
        Schluessel s = new Schluessel();
        s.schluessel = StringUtils.trimToEmpty(rs.getString(1));
        s.langtext = StringUtils.trimToEmpty(rs.getString(2));
        return s;
    }, kurztext);
    return standardtexte;
}

I am trying this request in Postman: localhost:8080/standardtexte?kurztext=KOELUNG but getting no useful result:

{
    "kurztext": "KOELUNG",
    "schluessel": []
}

When I try the same query in SQL Developer:

select schluessel, langtext from hb_standardtexte where kurztext = 'KOELUNG';

I get this result: Result table image

Can someone help me / explain why this is happening?

I also have tried it with other parameters for kurztext: The weird thing: other parameters work.

Answer 1

Summary: Don't use the CHAR data-type for variable-length strings. Use VARCHAR2.

---

If the table is:

CREATE TABLE hb_standardtexte (
  kurztext CHAR(10)
);

With the data:

INSERT INTO hb_standardtexte (kurztext) VALUES ('KOELUNG')

Then when you use:

select kurztext
from   hb_standardtexte
where  kurztext = 'KOELUNG';

The output is:

KURZTEXT
KOELUNG

The Oracle Literal documentation states:

Text literals have properties of both the CHAR and VARCHAR2 data types:

• Within expressions and conditions, Oracle treats text literals as though they have the data type CHAR by comparing them using blank-padded comparison semantics.

The Data Type Comparison Rules state:

Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

If you are passing the value such that it has a VARCHAR2 data-type. For example, this can be forced in SQL by using CAST:

select kurztext
from   hb_standardtexte
where  kurztext = CAST('KOELUNG' AS VARCHAR2(10));

Then blank-padded comparison semantics are not used and the query outputs zero rows.

You are likely to find that Java is passing the value as a VARCHAR2 and not as a CHAR.

---

The normal solution is:

1. Fix your table and not to use CHAR data-types and always use VARCHAR2 for the column and remove any padding.

   ALTER TABLE hb_standardtexte MODIFY kurztext VARCHAR2(10);
   
   UPDATE hb_standardtexte
   SET   kurztext = RTRIM(kurztext)
   WHERE kurztext LIKE '% ';
   

   Then your query should work.

2. If you must use CHAR then pad the string with whitespace to the correct length (i.e. pass the string "KOELUNG ", assuming the column is 10 characters long, and not "KOELUNG").

fiddle