错误的SQL语法，Oracle:当查询在Spring Boot应用程序的DB编辑器中运行良好时

Question

  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);
  }

堆栈跟踪：

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

这一行将'aro_user'、'afo_user1'、'afo_user1'、'afo_user1'、'afo_user1'、'afo_user1'、'afo_user1'替换为varriable。

Answer 1

据我所知，您必须将存储在该变量中的值拆分为行。怎么做？像这样：

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>

这意味着：

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

应修改，以便queryparam替换为

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