Daizc
Daizc
count.articles52
count.tags25
count.categories3
首页
追番
归档
关于

categories

recent_posts

© 2024 Daizc Powered by Hexo & Nexmoe



使用JDBC操作Postgre时jsonb操作符被识别为参数的问题

使用JDBC操作Postgre时jsonb操作符被识别为参数的问题

2022年08月26日 我永远爱学习

使用JDBC操作Postgre时jsonb操作符被识别为参数的问题

一句话解决方式

把sql中的?替换为??，就能解决

原SQL

1
2
-- 可以在pgClient中正常执行
SELECT * FROM project WHERE (tags ?& array['Sonar'])

现SQL

1
2
-- 可以通过org.postgresql.Driver正常执行
SELECT * FROM project WHERE (tags ??& array['Sonar'])

相关环境版本

  • org.postgresql.postgresql:42.3.4

报错的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Override
public List<Project> listByQuery(ProjectQuery query) {
        // json查询
    if (CollectionUtils.isNotEmpty(tagContains)) {
        var tagContainsArray = tagContains.toArray();
        var placeHolder = IntStream.range(0, tagContains.size()).boxed().map(s -> "{" + s + "}")
                .collect(Collectors.joining(",", "tags ?& array[", "]"));
        if (queryWrapper.nonEmptyOfWhere()) {
            queryWrapper.apply("AND " + placeHolder, tagContainsArray);
        } else {
            queryWrapper.apply(placeHolder, tagContainsArray);
        }
    }
    return baseMapper.selectList(queryWrapper); // <= 此行报错
}

特征日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2022-08-26 14:04:17.666 [TID:SLT-1563036635401490432] [ERROR] ller.GlobalExceptionController[70]- 出现未知异常:
### Error querying database.  Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
### The error may exist in com/kailinjt/middleware/beam/mapper/ProjectMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,key,display_name,description,product_id,technical_manager_id,gitlab_project_id,development_language,tags,create_time,update_time,create_user_id,update_user_id  FROM project     WHERE (tags ?& array[?])
### Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
; 未设定参数值 2 的内容。; nested exception is org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
### The error may exist in com/kailinjt/middleware/beam/mapper/ProjectMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,key,display_name,description,product_id,technical_manager_id,gitlab_project_id,development_language,tags,create_time,update_time,create_user_id,update_user_id  FROM project     WHERE (tags ?& array[?])
### Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
; 未设定参数值 2 的内容。; nested exception is org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
  at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104) ~[spring-jdbc-5.3.14.jar:5.3.14]
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.14.jar:5.3.14]
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.14.jar:5.3.14]
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.14.jar:5.3.14]
  at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) ~[mybatis-spring-2.0.6.jar:2.0.6]
    <!-- 下面这行堆栈是框架代码的错误源，上面的堆栈是在抛出和翻译这个错误 -->
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.6.jar:2.0.6]  
  at jdk.proxy2.$Proxy139.selectList(Unknown Source) ~[?:?]
  at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.6.jar:2.0.6]
  at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
  at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
  at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
  at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.4.3.4.jar:3.4.3.4]
  at jdk.proxy2.$Proxy165.selectList(Unknown Source) ~[?:?]
    <!-- 下面这行堆栈是业务代码错误源，上面的堆栈是在框架中处理的 -->
  at com.kailinjt.middleware.beam.service.impl.ProjectServiceImpl.listByQuery(ProjectServiceImpl.java:103) ~[classes/:?]
    <!-- 以下堆栈业务无关，因此略掉... -->

源码跟踪

  1. 由日志可知，这段SQL中有两个参数需要填充，但我们只传入了一个参数导致报错 
    1
    Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
  2. 打开问题SQL发现这个SQL中的确只有一个参数，猜测是PG的jsonb操作符?&中的?被错误的识别为了参数，基于此，反向查找参数来源。 
    1
    SELECT  id,key,display_name,description,product_id,technical_manager_id,gitlab_project_id,development_language,tags,create_time,update_time,create_user_id,update_user_id  FROM project WHERE (tags ?& array[?])
  3. 根据堆栈反向查找错误源org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.6.jar:2.0.6] 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    private class SqlSessionInterceptor implements InvocationHandler {
        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        SqlSession sqlSession = getSqlSession(SqlSessionTemplate.this.sqlSessionFactory,
            SqlSessionTemplate.this.executorType, SqlSessionTemplate.this.exceptionTranslator);
        try {
            Object result = method.invoke(sqlSession, args);                      // <= 实际报错的是这行
            if (!isSqlSessionTransactional(sqlSession, SqlSessionTemplate.this.sqlSessionFactory)) {
            // force commit even on non-dirty sessions because some databases require
            // a commit/rollback before calling close()
            sqlSession.commit(true);
            }
            return result;
        } catch (Throwable t) {
            Throwable unwrapped = unwrapThrowable(t);
            if (SqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
            // release the connection to avoid a deadlock if the translator is no loaded. See issue #22
            closeSqlSession(sqlSession, SqlSessionTemplate.this.sqlSessionFactory);
            sqlSession = null;
            Throwable translated = SqlSessionTemplate.this.exceptionTranslator
                .translateExceptionIfPossible((PersistenceException) unwrapped);    // <= 报错的是这行
            if (translated != null) {
                unwrapped = translated;
            }
            }
            throw unwrapped;
        } finally {
            if (sqlSession != null) {
            closeSqlSession(sqlSession, SqlSessionTemplate.this.sqlSessionFactory);
            }
        }
        }
    }
  4. 到第三步这里发现已经在mybatis和jdbcDriver里了，但是还看不出问题，因为看漏了一个Caused by
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
      Caused by: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:284) ~[postgresql-42.3.4.jar:42.3.4]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:339) ~[postgresql-42.3.4.jar:42.3.4]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.3.4.jar:42.3.4]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.3.4.jar:42.3.4]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167) ~[postgresql-42.3.4.jar:42.3.4]
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156) ~[postgresql-42.3.4.jar:42.3.4]
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) ~[druid-1.2.8.jar:1.2.8]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.7.jar:3.5.7]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64) ~[mybatis-3.5.7.jar:3.5.7]
    at jdk.proxy2.$Proxy222.query(Unknown Source) ~[?:?]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.7.jar:3.5.7]
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81) ~[mybatis-plus-extension-3.4.3.4.jar:3.4.3.4]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) ~[mybatis-3.5.7.jar:3.5.7]
    at jdk.proxy2.$Proxy221.query(Unknown Source) ~[?:?]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.7.jar:3.5.7]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.7.jar:3.5.7]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
    ... 128 more
  5. 导航到驱动中的异常点org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:284) ~[postgresql-42.3.4.jar:42.3.4]，发现根本原因果然是参数列表paramValues预期两个值，实际只有一个值导致的
    1
    2
    3
    4
    5
    6
    7
    8
    9
    @Override
    public void checkAllParametersSet() throws SQLException {
      for (int i = 0; i < paramTypes.length; ++i) {
        if (direction(i) != OUT && paramValues[i] == null) {
          throw new PSQLException(GT.tr("No value specified for parameter {0}.", i + 1),
              PSQLState.INVALID_PARAMETER_VALUE);
        }
      }
    }
  6. 反向查找类变量paramValues是何时被赋值的:
    1. org.postgresql.core.v3.SimpleParameterList#SimpleParameterList[48]构造方法传入
    2. org.postgresql.core.v3.SimpleQuery#createParameterList[52] 由getBindCount()计算得到
    3. 由类变量nativeQuery.bindPositions的长度乘以批处理数getBatchSize()得到 
      1
      2
      3
      4
      public final int getBindCount() {

          return nativeQuery.bindPositions.length * getBatchSize();
      }
  7. 反向查找类变量nativeQuery.bindPositions是何时被赋值的:
    1. org.postgresql.core.NativeQuery#NativeQuery(java.lang.String, int[], boolean, org.postgresql.core.SqlCommand)[36-37]构造方法传入
    2. 这个构造使用的方式太多了，在这行打个断点，启动应用后执行SQL触发断点，动态抛出一个异常获取堆栈: 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      java.lang.Exception
          at org.postgresql.core.NativeQuery.<init>(NativeQuery.java:36)
          <!-- 得到真实的使用位置 -->
          at org.postgresql.core.Parser.parseJdbcSql(Parser.java:295)      
          at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:65)
          at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19)
          at org.postgresql.util.LruCache.borrow(LruCache.java:123)
          at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:296)
          at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:172)
          at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:88)
          <!-- 以下堆栈略 -->
    3. 发现原参数是bindPositions的包装，转而追踪参数bindPositions: 
      1
      2
      3
      4
      5
      NativeQuery lastQuery = new NativeQuery(nativeSql.toString(),
          toIntArray(bindPositions), !splitStatements,       // <= 就是这个toInArray包装
          SqlCommand.createStatementTypeInfo(currentCommandType,
              isBatchedReWriteConfigured, valuesBraceOpenPosition, valuesBraceClosePosition,
              isReturningPresent, (nativeQueries == null ? 0 : nativeQueries.size())));
    4. 追踪到org.postgresql.core.Parser#parseJdbcSqlSQL解析器中,读取到SQL中的?时的处理方式: 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      case '?':
        nativeSql.append(aChars, fragmentStart, i - fragmentStart);
        // 有连续两个?的时候，只向sql中拼接一个?，不计入bindPositions中
        if (i + 1 < aChars.length && aChars[i + 1] == '?') /* replace ?? with ? */ {
          nativeSql.append('?');
          i++; // make sure the coming ? is not treated as a bind
        } else {
          // 只有一个?的时候，检查是否解析参数
          if (!withParameters) {
            // 不解析参数
            nativeSql.append('?');
          } else {
            // 解析参数
            if (bindPositions == null) {
              bindPositions = new ArrayList<Integer>();
            }
            bindPositions.add(nativeSql.length());
            int bindIndex = bindPositions.size();
            nativeSql.append(NativeQuery.bindName(bindIndex));
          }
        }
        fragmentStart = i + 1;
        break;
    5. 把sql中的?替换为??，问题成功解决。

copyright.author：Daizc
copyright.permalink：https://note.bequick.run/%E4%BD%BF%E7%94%A8JDBC%E6%93%8D%E4%BD%9CPostgre%E6%97%B6jsonb%E6%93%8D%E4%BD%9C%E7%AC%A6%E8%A2%AB%E8%AF%86%E5%88%AB%E4%B8%BA%E5%8F%82%E6%95%B0%E7%9A%84%E9%97%AE%E9%A2%98/
版权声明：本文采用 CC BY-NC-SA 3.0 CN 协议进行许可
请在你的配置文件中设置评论框插槽
×