pgsql中json字段中的内容字段当成查询条件

1. 参考文章地址及作用

https://www.cnblogs.com/cndarren/p/13218290.html – 处理了mybatis-plus解析json处理符号-> ->>出现异常的问题

https://www.postgresql.org/docs/12/functions-json.html – 官方文档，函数操作符等

2. 处理问题

• 项目中，表中字段info_json是json类型字段，存的内容大概如下（json数组）：

[{"infoJson":{"fiberCores":"12","fiberLength":"50","fiberTubeColor":"rgb(255, 0, 0)","flberEndAddr":"","fiberType":"单模"},"tempId":"821004413999120384"}]

• 目标使用其中的字段，fiberType，进行页面查询/搜索，实体类中属性如下：

@TableField(value = "info_json",typeHandler = JSONTypeHandler.class)
@ApiModelProperty(value = "扩展属性")
private Object infoJson;

• xxxmapper.xml中resultmap中内容如下：

<result column="info_json" jdbcType="OTHER" property="infoJson" typeHandler="cn.etcom.web.dao.resource.JSONTypeHandler" />

• sql中的条件如下：

<if test="rsDevInfo.fiberType != null and rsDevInfo.fiberType != ''">
  <bind name="fiberType" value="rsDevInfo.fiberType"/>
  and info.info_json::json->0->'infoJson'->>'fiberType'=#{fiberType}
</if>

• 这个时候出现异常，mybatis解析->和->>的时候解析不了，>符号做转移& g t; 也不行。最后使用如下处理：

@SqlParser(filter=true)
IPage<RsDevInfo> selectPage2(Page<RsDevInfo> page, @Param(value = "rsDevInfo") RsDevInfo rsDevInfo);

• 其中需要yml配置文件中开启配置才能生效 sql-parser-cache: true：

mybatis-plus:
  # xml扫描，多个目录用逗号或者分号分隔（告诉 Mapper 所对应的 XML 文件位置）
  mapper-locations: classpath*:mapper/**/*.xml,mapper/*.xml
  type-aliases-package: cn.xxx.entity,cn.xxx.web.entity
  #typeAliasesPackage: cn.xxx.entity,cn.xxx.web.entity
  # 以下配置均有默认值,可以不设置
  global-config:
    sql-parser-cache: true
    db-config:
      #主键类型 AUTO:"数据库ID自增" INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
      id-type: ASSIGN_ID

补充：上边的处理应该会导致一个问题的出现，就是多租户的（saas）租户id无法通过配置文件自动拼装成条件。配置文件如下：

package cn.etcom.web.config;//


import cn.etcom.properties.MpTableExProperties;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

//                            _ooOoo_
//                           o8888888o
//                           88" . "88
//                           (| -_- |)
//                           O\  =  /O
//                        ____/`---'\____
//                      .'  \\|     |//  `.
//                     /  \\|||  :  |||//  \
//                    /  _||||| -:- |||||-  \
//                    |   | \\\  -  /// |   |
//                    | \_|  ''\---/''  |   |
//                    \  .-\__  `-`  ___/-. /
//                  ___`. .'  /--.--\  `. . __
//               ."" '<  `.___\_<|>_/___.'  >'"".
//              | | :  `- \`.;`\ _ /`;.`/ - ` : | |
//              \  \ `-.   \_ __\ /__ _/   .-` /  /
//         ======`-.____`-.___\_____/___.-`____.-'======
//                            `=---='
//        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//                      佛祖保佑       永无BUG
@Configuration
public class MybatisPlusConfig {

    @Autowired
    private MpTableExProperties mpTableExProperties;
    //@Autowired
    //private RedisUtil redisUtil;

    /**
     * 分页组件 +  租户ID动态注入。
     *
     * @return PaginationInterceptor
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        List<ISqlParser> sqlParserList = new ArrayList<>();
        TenantSqlParser tenantSqlParser = new TenantSqlParser();
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId(boolean where) {
                String tenantId;
                //获取租户id
                try {
//                    tenantId = redisUtil.getActiveUser().getTenantId();
                    tenantId = "111111111";
                } catch (Exception e) {
                    e.printStackTrace();
                    tenantId = "0";
                }
                return new StringValue(tenantId);
            }

            @Override
            public String getTenantIdColumn() {
                return "tenant_id";
            }

            @Override
            public boolean doTableFilter(String tableName) {
                //是否进行过滤表, true:表示忽略，false:需要解析多租户字段
                if (mpTableExProperties.getName() == null) {
                    return false;
                }
                return mpTableExProperties
                        .getName()
                        .parallelStream()
                        .anyMatch(
                                x -> x.toLowerCase().equals(tableName.toLowerCase())
                        );
            }
        });
        sqlParserList.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParserList);
        return paginationInterceptor;
    }
}

补充：自己使用另一种效率慢的方式写的仅供参考：

select
    info.id, info.tenant_id,info.host_id,info.qrcode, info.name,info.code 
    from rs_dev_info info
    where 1=1
and  info.id in (select a.id from (select id,json_extract_path_text(json_array_elements(info_json),'infoJson','fiberType') as fiberType from rs_dev_info ) a where a.fiberType='单模')

此方法通过先筛选符合条件的id做子查询，效率低下。但不会出现上述问题，无需在mapper.java的方法上增加注解，数据量小的可酌情使用。

参考官网，针对使用的方法补充解释：
json_array_elements(json) 拿到json数组的第一个对象。
json_extract_path_text(json,keys) 可以逐层的获取json对象中的内容，根据key。

补充：自定义的 JSONTypeHandler:

package cn.etcom.web.dao.resource;

import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@MappedTypes(Object.class)
public class JSONTypeHandler extends BaseTypeHandler<Object> {

    private static final PGobject jsonObject = new PGobject();
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        jsonObject.setType("json");
        try {
            jsonObject.setValue(new ObjectMapper().writeValueAsString(parameter));  //java对象转化成json字符串
        } catch (IOException e) {
            e.printStackTrace();
        }
        ps.setObject(i, jsonObject);
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return  JSONObject.parse(rs.getString(columnName)); // rs.getString(columnName);                                 // 返回String
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return JSONObject.parse(rs.getString(columnIndex));
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return JSONObject.parse(cs.getString(columnIndex));
    }

}