Oracle sql query works in sql developer but not in java

Question

I have filters in a datatable and when user enters some value it should return a list of results matching that filter. I want it to be case-insensitive. I create the query string for a prepared statement via Java string concatenation, as in the below:

public static List<Logger> getAll(int from, int to, Map<String, Object> filters, String sortField,
        SortOrder sortOrder) {
    Connection con = null;
    PreparedStatement ps = null;
    List<Logger> lista = new ArrayList<>();
    String upit = "Select * from (select m.*,rownum r from (";
    String upitZaFilterISort = "select m.* from eps_stage.MDM_OSB_LOG m";

    try {
        con = DataConnect.getConnection();
        int upper = from + to;

        if (filters.size() > 0) {
            upitZaFilterISort = upitZaFilterISort.concat(" where 1=1");
            Set<String> keys = filters.keySet();

            // To get all key: value
            for (String key : keys) {
                if (key.equalsIgnoreCase("status") || key.equalsIgnoreCase("mbr")
                        || key.equalsIgnoreCase("pib") || key.equalsIgnoreCase("jmbg")
                        || key.equalsIgnoreCase("poruka_tip") || key.equalsIgnoreCase("aplikacija")
                        || key.equalsIgnoreCase("operacija")) {
                    upitZaFilterISort = upitZaFilterISort.concat(
                            " AND UPPER(" + key.toString() + ") LIKE '" + filters.get(key).toString().toUpperCase() + "%'");
                } 
            }
            }
        }

        String sort = "";

        ps = con.prepareStatement(upit + upitZaFilterISort + ") m ) where r>=? and r<=?");

        ps.setInt(1, from);
        ps.setInt(2, upper);

        System.out.println(upit+ upitZaFilterISort + sort+") m " + ") where r>=? and r<=?");

        ResultSet resultSet = ps.executeQuery();

In this line is a problem:

upitZaFilterISort = upitZaFilterISort.concat(
        " AND UPPER(" + key.toString() + ") LIKE '" + filters.get(key).toString().toUpperCase() + "%'");

When I use case-sensitive comparison it works:

upitZaFilterISort = upitZaFilterISort.concat(
        " AND " + key.toString() + " LIKE '" + filters.get(key).toString() + "%'");

After concatenation query:

Select * from (select m.*,rownum r from (select m.* from eps_stage.MDM_OSB_LOG m where 1=1 AND UPPER(poruka_tip) LIKE 'V%') m ) where r>=1 and r<=20

It returns the expected result when I run it in Oracle SQL Developer, but in my app it returns an empty result set.

Does Java put quotes somewhere I don't expect? I will provide more info if needed.

Please show the code you have tried and any error messages you get. — mwarren
– mwarren, Commented Jul 29, 2019 at 14:08
Is the argument to UPPER() supposed to be a column name or a literal? If the latter then it needs to be quoted. Also, if the latter then I'm confused by your claim that you intend to use this for a prepared statement, because in that case you are bypassing the natural, best-practice approach of setting the value via a parameter to the statement instead of via string concatenation. Among many other things, that would bypass the need to worry about quoting. — John Bollinger
– John Bollinger, Commented Jul 29, 2019 at 14:13
In any case, when it does not work in your app, what actually happens? Incorrect result? (Then what did you expect, and why?) Exception thrown? (Then what was the exception message?) Overall, please provide a minimal reproducible example that models the issue. — John Bollinger
– John Bollinger, Commented Jul 29, 2019 at 14:19
@JohnBollinger it's a column name. Case sensitive comparison works perfectly and return desired result, case insensitive returns nothing .. Post edited and more code and explanation added — Rattus
– Rattus, Commented Jul 29, 2019 at 14:36
I wouldn't know why it works in Sql Developer so on this I would also ask that you add a reproducible example. In the meantime, I do see a number of practices I'd rather avoid if possible: 1. concatenation in SQL queries (search for "Sql Injection") 2. the UPPER appears on the left-hand side of the condition but not the right one: I'd put it on both sides, for uniformity 3. building queries in a Java class is something I'd rather avoid as you are mixing two very different languages, reducing overall readability. Externalize your queries if possible. — Filippo Possenti
– Filippo Possenti, Commented Jul 29, 2019 at 15:11

Soumen Mukherjee · Accepted Answer · 2019-09-26 07:32:45Z

1

Try this:

Check if the user has all the required privileges to make the statements
It may happen that the port has only one open connection. Therefore you can only use java or oracle sql developer. Try disconnecting from sql developer and running your java program. If it doesn't work tell me.

I hope it has been helpful

edited Sep 26, 2019 at 7:32

Soumen Mukherjee

3,3023 gold badges26 silver badges37 bronze badges

answered Sep 20, 2019 at 10:39

J Murga

53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff Stewart · Accepted Answer · 2019-07-31 17:43:19Z

0

Re: https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_globalization.htm#CCHIJBCG, section "Changing NLS Parameter Values for All Sessions"

Please check your session settings for NLC_COMP, which may be set to LINGUISTIC. The link below can get you there in SQL Developer. If set to LINGUISTIC then your SQL Developer sessions are performing case insensitive searches, possibly explaining differences between the sessions.

Also, concur with Filippo's recomended practices.

answered Jul 31, 2019 at 17:43

Jeff Stewart

1255 bronze badges

Collectives™ on Stack Overflow

Oracle sql query works in sql developer but not in java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related