postgreSQL的JAVA后端开发SQL小记（一） postgreSQL的JAVA后端开发SQL小记（

postgreSQL的JAVA后端开发SQL小记（一）
1、需求一：需要实现递归查询父级记录下的所有子级记录
实现思考①：
（1）将有限的子级列出来，然后逐级去查询所有的子级记录
（2）需要分批去查询对应的子级元素
存在的不足：无法实现动态扩展，后续增加新的子级，需要重构代码，无法适用新的需求，不使用该方式实现
实现思考②：
（1）使用sql递归查询父级元素的所有子级元素
（2）一次查询即可获取所有子级元素记录
优点：支持动态扩展，添加新的子级，sql同样支持查询所有的子级元素
案例：

WITH RECURSIVE sub_element ( id, name, code, LEVEL, superior_code ) AS( SELECT base.id, base.name, base.code, base.LEVEL, base.superior_code, base.update_time FROM tb_ele_info base WHERE base.superior_code = 'jx-0043'UNION ALL SELECT e2.id, e2.name, e2.code, e2.LEVEL, e2.superior_code, e2.update_time FROM tb_ele_info e2, tb_ele_info e3 where e2.superior_code = e3.code )SELECT * FROM sub_element ORDER BY update_time DESC, ID ASC;

2、需求二：查询指定分组下，最新的一条记录
思考：
（1）模糊查询，获取指定分组下的所有数据
（2）根据更新时间倒序排列，获取第一条记录，使用limit语句
案例：

select * from tb_worker where group = '第9组' order by update_time desc limit 1

3、需求三：将数据表中的bigint类型的时间戳，通过sql查询，返回格式为 ‘yyyy-MM-dd HH24:MI:SS’ 格式的日期字符串
思考：
（1）需要将指定字段的时间戳转为‘yyyy-MM-dd HH24:MI:SS’ 格式的字符串
（2）指定列名返回对应的数据
案例：

select name, t, v, to_char(to_timestamp(create_time/1000),'yyyy-MM-dd HH24:MI:SS') as format_date from tb_data

【postgreSQL的JAVA后端开发SQL小记（一）】4、需求四：实现将jsonb格式的字段，使用mybatis查询时，支持使用具体对象进行更新、查询操作
思考
（1）因为db使用的是postgreSQL，mybatis框架提供的typeHandler不支持jsonb字段的实体对象操作，需要自定义一个支持jsonb对象操作的typeHandler
（2）需要将自定义的typeHandler注册到mybatis的默认管理工厂
案例：

import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedJdbcTypes; import org.apache.ibatis.type.MappedTypes; import org.postgresql.util.PGobject; import com.alibaba.fastjson.JSONObject; import com.sisensing.cgm.common.core.utils.JsonUtils; /** * @author qz.wu * @date 2021/5/17 16:37 * @descripitions 通用的jsonb属性的handler处理器注意：不能使用Object.class放在MappedTypes注解中，会导致获取不到类型解析器报错 */ @MappedTypes({JSONObject.class}) @MappedJdbcTypes(value = https://www.it610.com/article/JdbcType.VARCHAR, includeNullJdbcType = true) public class JsonbTypeHandler extends BaseTypeHandler {private Class clazz; public JsonbTypeHandler(Class clazz) { if (clazz == null) { throw new IllegalArgumentException("Type argument cannot be null"); } this.clazz = clazz; }@Override public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException { if (ps != null) { PGobject jsonObject = new PGobject(); jsonObject.setType("json"); jsonObject.setValue(JsonUtils.beanToJsonStr(parameter)); ps.setObject(i, jsonObject); } }@Override public T getNullableResult(ResultSet resultSet, String columnName) throws SQLException { return parse(resultSet.getString(columnName)); }@Override public T getNullableResult(ResultSet resultSet, int i) throws SQLException { return (T)parse(resultSet.getString(i)); }@Override public T getNullableResult(CallableStatement callableStatement, int i) throws SQLException { return (T)parse(callableStatement.getString(i)); }private T parse(String json) { if (null != json) { return JSONObject.parseObject(json, clazz); } return null; } }

tips:

(1) 需要将自定的jsonb对象放到@MappedTypes注解里面，交给mybatis的typeHandler的工厂注册类管理（2）需要指定mybatis-plus的指定扫描typeHandler路径：(即将自定义的typeHandler注册到sqlSessionFactory的管理器中)mybatis-plus.typeHandlersPackage=com.xxx.xxx.xx.handler