Java JDBC – How to Resolve No Results Returned from JDBC in Spring with Oracle

javajdbcoraclespring

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.

Best Answer

Make sure you COMMIT your data.

In Oracle, if you do not COMMIT then only the session where the the data was created can see the uncommitted data and other sessions, even those belonging to the same user, will never see that uncommitted data.

For example, the behaviour can be explained:

  1. If you create the data in SQL Developer but do not COMMIT the data or close the session.
  2. Then if you query the data from Java then it will connect to the database and create a new session (even if you connect as the same user) and then you will never see the uncommitted data.
  3. But if you go back to the open session in SQL Developer then you will see the data exists in its uncommitted form in that session.
  4. To see it in other sessions, COMMIT the data in SQL Developer.
Related Question