CODEWITHSUNDEEP
javaoracle-databasejdbctemplate
Black Swan
Black Swan

Reputation: 851

Bad SQL grammar, Oracle : while the query is working fine in DB editor in Spring Boot Application

I am using Oracle DB with Spring Boot Application. The query is working fine in DBeaver but not in actual application. I have copied the query from the console error message.

Query calling function:

  public List<UserFullNameDesignationDto> getUserFullNameDesignation(String[] userNames) {
    String queryParam = "";
    for (String uName : userNames) {
      queryParam += "'" + uName + "',";
    }
    queryParam = queryParam.substring(0, queryParam.length() - 1);
    String sql =
        "SELECT\n"
            + "\tu.USERNAME,\n"
            + "\tu.FULL_NAME,\n"
            + "\tcd.NAME \n"
            + "FROM\n"
            + "\tUSER_ENTITY u\n"
            + "LEFT JOIN CORE_DESIGNATIONS cd ON u.DESIGNATION_ID = cd.ID \n"
            + "WHERE\n"
            + "\tu.USERNAME IN ("
            + queryParam
            + ");\n";

    var rowMapper = BeanPropertyRowMapper.newInstance(UserFullNameDesignationDto.class);
     List<UserFullNameDesignationDto> list = jdbcTemplate.query(sql, rowMapper);
    System.out.println(list);
    return jdbcTemplate.query(sql, rowMapper);
  }

Stack trace:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT
    u.USERNAME,
    u.FULL_NAME,
    cd.NAME 
FROM
    USER_ENTITY u
LEFT JOIN CORE_DESIGNATIONS cd ON u.DESIGNATION_ID = cd.ID 
WHERE
    u.USERNAME IN ('aro_user','afo_user1','afo_user1','afo_user1','afo_user1','afo_user1');
]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
............
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

This line will be

'aro_user','afo_user1','afo_user1','afo_user1','afo_user1','afo_user1'

replaced by variable.

Upvotes: 0

Views: 2115

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21075

The root cause of the error is that you copied the semicolon in the JDBCTemplate statement (which works in SQL IDE but not in JDBC)

+ ");\n";

remove it

+ ")\n";

and it will work (or you get an other error;)

Anyway you should re-think your way of concatenation input in the SQL statement towards the usage of bind variables. There are lot of examples for binding IN list on this site.

Upvotes: 2

jcompetence
jcompetence

Reputation: 8383

List<String> userIds = Arrays.asList("User1", "User2");

String inSql = String.join(",", Collections.nCopies(userIds.size(), "?"));

 
List<User> employees = jdbcTemplate.query(String.format("SELECT * FROM Users WHERE id IN (%s)", inSql), userIds.toArray());

Please see, https://www.baeldung.com/spring-jdbctemplate-in-list for

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142788

As far as I can tell, you'll have to split values stored in that variable into rows. How? Like this:

SQL> with test(queryParam) as
  2  (select q'['aro_user','afo_user1','afo_user1','afo_user1','afo_user1','afo_user1']' from dual)
  3  select regexp_substr(queryParam, '[^,]+', 1, level) val
  4  from test
  5  connect by level <= regexp_count(queryParam, ',') + 1
  6  /

VAL
----------------------------------------------------------------------
'aro_user'
'afo_user1'
'afo_user1'
'afo_user1'
'afo_user1'
'afo_user1'

6 rows selected.

SQL>

It means that this:

        + "\tu.USERNAME IN ("
        + queryParam
        + ");\n";

should be modified so that queryParam is replaced by

(    SELECT REGEXP_SUBSTR (queryParam,
                                 '[^,]+',
                                 1,
                                 LEVEL) val
             FROM test
       CONNECT BY LEVEL <= REGEXP_COUNT (queryParam, ',') + 1)

Upvotes: 0

Related Questions