PG-SQL 关于int4range ,jsonb ,array等的特殊数据类型，用java实现的新增

PG-SQL 关于int4range ,jsonb,array 等的特殊数据类型，用java实现的新增

1、创建一张表

CREATE TABLE public.test_type (
  id serial,
  factory_no varchar(32),
  material_name varchar(32),
  apply_date timestamp,
  code_no_range int4range,
  inner_code  varchar(1000)[],
  extend_entity jsonb
)

2、实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CodeApplyEntity implements Serializable {

    private String id;
    private String factoryNo;
    private String materialName;
    private String applyDate;
    private String codeNoRange;
    private List<String> innerCode;
    private String extendEntity;
}

3、新增一条数据

3、1 Dao层

int insertCodeApplyData(
@Param("vos") List<CodeApplyEntity> vos, 
@Param("tableName") String tableName);

3、2 mapper文件

<insert id="insertCodeApplyData">
   insert into ${tableName}
      (factory_no, material_name, apply_date, 
      code_no_range, inner_code, extend_entity )
   values
    <foreach collection="vos" item="vo" separator=",">
	   (#{vo.factoryNo}, #{vo.materialName}, #{vo.applyDate},
	    #{vo.codeNoRange}::int4range, 
	    #{vo.innerCode,typeHandler = com.it.springcloud.management.dao.ListTypeHandler},
	  	#{vo.extendEntity}::jsonb )
	</foreach>
</insert>

4、也可以写sql直接新增

INSERT INTO test_type
(factory_no, material_name, apply_date,code_no_range, inner_code, extend_entity) 
VALUES 
( 'MOT2', 'M023', sysdate, '[1,11)',  '{a,b,c}', null ),
( 'MOT2', 'M023', sysdate, '[11,21)', '{c,d,e}', null  ),
( 'MOT2', 'M023', sysdate, '[21,31)', '{a,d,f}', null  ),
( 'MOT2', 'M023', sysdate, '[31,41)', '{b,g,j}', '[{"key": "height", "name": "高度", "value": "170"}, {"key": "weight", "name": "重量", "value": "50kg"}]' ),
( 'MOT2', 'M023', sysdate, '[41,51)', '{h,i,k}', '[{"key": "batch", "name": "批次号", "value": "ABCDEF"}]'  );

5、数据库目前的数据

6、对Array 数据进行查询

6. 1 查询即包含a,又包含b的数据

select id, factory_no as factoryNo, material_name as materialName, 	
		code_no_range as codeNoRange, inner_code as innerCode
 from public.test_typea
 where a.inner_code  @>
  #{list,typeHandler = com.it.springcloud.management.dao.ListTypeHandler}

6. 2 查询包含a,或者包含b的数据

select id, factory_no as factoryNo, material_name as materialName, 	
		code_no_range as codeNoRange, inner_code as innerCode
 from public.test_typea
 where a.inner_code  && 
  #{list,typeHandler = com.it.springcloud.management.dao.ListTypeHandler}

7、数组操作符

操作符	描述	示例	结果ID
=	相等	a.inner_code = ‘{a,b,c}’	1
<>	不等于	a.inner_code <> ‘{a,b,c}’	2,3,4,5
<	小于
>	大于
<=	小于或等于
>=	大于或等于
@>	包含	a.inner_code @> ‘{a,b}’	1
<@	包含于	a.inner_code <@ ‘{a,b,c,d,e}’	1,2
&&	是否包含任意一个	a.inner_code && ‘{a,b}’	1,3,4
|	数组与元素连接	select a.inner_code || ‘{x,y}’ as innerCode from test_type a	{a,b,c,x,y}

8、ListTypeHandler 文件

@MappedJdbcTypes(JdbcType.ARRAY)
@MappedTypes(List.class)
public class ListTypeHandler extends BaseTypeHandler<List<String>> {

    private static final String TYPE_NAME_VARCHAR = "varchar";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType) throws SQLException {
        String typeName = TYPE_NAME_VARCHAR;
        //这3行是关键的代码，创建Array，然后ps.setArray(i, array)就可以了
        Connection conn = ps.getConnection();
        Array array = conn.createArrayOf(typeName, parameter.toArray());
        ps.setArray(i, array);
    }

    @Override
    public List<String> getNullableResult(ResultSet resultSet, String s) throws SQLException {
        Array array = resultSet.getArray(s);
        return castPgArray2List( array);
    }

    @Override
    public List<String> getNullableResult(ResultSet resultSet, int i) throws SQLException {
        Array array = resultSet.getArray(i);
        return castPgArray2List( array);
    }

    @Override
    public List<String> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        Array array = callableStatement.getArray(i);
        return castPgArray2List( array);
    }

    private List<String> castPgArray2List(Array array){
        if (array == null)
            return null;
        try {
            return Arrays.asList( (String[]) array.getArray());
        } catch (SQLException throwables) {
            return null;
        }
    }

}

9、JsonTypeHandler文件

@MappedJdbcTypes(JdbcType.OTHER)
@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("jsonb");
        if (parameter instanceof String) {
            jsonObject.setValue((String) parameter);
        } else {
            jsonObject.setValue(JSON.toJSONString(parameter));
        }
        ps.setObject(i, jsonObject);
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return JSON.parseObject(rs.getString(columnName), Object.class);
    }

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

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

10、RangeTypeHandler文件

@MappedJdbcTypes(JdbcType.CHAR)
@MappedTypes(RangeCode.class)
public class RangeTypeHandler extends BaseTypeHandler<RangeCode> {

    private static final PGobject pGobject = new PGobject();

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, RangeCode rangeCode, JdbcType jdbcType) throws SQLException {
        pGobject.setType("int4range");
        pGobject.setValue(rangeCode.toString());
        preparedStatement.setObject(i, pGobject);
    }

    @Override
    public RangeCode getNullableResult(ResultSet resultSet, String s) throws SQLException {
        String temp = resultSet.getString(s);
        return castPgRange2RangeCode(temp);
    }

    @Override
    public RangeCode getNullableResult(ResultSet resultSet, int i) throws SQLException {
        String temp = resultSet.getString(i);
        return castPgRange2RangeCode(temp);
    }

    @Override
    public RangeCode getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        String temp = callableStatement.getString(i);
        return castPgRange2RangeCode(temp);
    }

    private RangeCode castPgRange2RangeCode(String s){
        if (s == null)
            return null;
        if (!s.contains("[") || !s.contains(",") || !s.contains(")"))
            throw new IllegalArgumentException();
        String[] sArr = s.split(",");
        String a = sArr[0].substring(1);
        String b = sArr[1].substring(0, sArr[1].length()-1);
        RangeCode rangeCode = new RangeCode(Integer.valueOf(a), Integer.valueOf(b) - 1);
        return rangeCode;
    }
}