Notes on Java backend development of PostgreSQL (I)


Notes on Java backend development of PostgreSQL (I)

1. Requirement 1: it is necessary to recursively query all child records under the parent record

Realization thinking ①:
(1) List the limited children, and then query all child records level by level
(2) You need to query the corresponding child elements in batches

Shortcomings: dynamic expansion cannot be realized, new children are added later, code needs to be reconstructed, and new requirements cannot be applied. This method is not used

Realization thinking ②:
(1) Use SQL to recursively query all child elements of the parent element
(2) All child element records can be obtained in one query

Advantages: it supports dynamic expansion and adding new children. SQL also supports querying all child elements


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

2. Requirement 2: query the latest record under the specified group

(1) Fuzzy query to obtain all data under the specified group
(2) According to the reverse order of update time, obtain the first record and use the limit statement


select * from tb_ Worker where group = 'group 9' order by update_ time desc limit 1

3. Requirement 3: query the bigint timestamp in the data table through SQL and return the date string in the format of ‘yyyy MM DD hh24: Mi: Ss’

(1) The timestamp of the specified field needs to be converted to a string in the format of ‘yyyy MM DD hh24: Mi: Ss’
(2) Specifies the column name to return the corresponding data


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

4. Requirement 4: when using mybatis to query fields in jsonb format, specific objects can be used to update and query

(1) Because DB uses PostgreSQL, the typehandler provided by the mybatis framework does not support the entity object operation of jsonb fields. You need to customize a typehandler that supports jsonb object operation
(2) You need to register the custom typehandler with the default management factory of 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.sisensing.cgm.common.core.utils.JsonUtils;

 * @author qz.wu
 * @date 2021/5/17 16:37
 *The handler processor of the @ descriptions general jsonb attribute. Note: object.class cannot be placed in the mappedtypes annotation, which will cause an error when the type parser cannot be obtained
@MappedJdbcTypes(value = JdbcType.VARCHAR, includeNullJdbcType = true)
public class JsonbTypeHandler<T> extends BaseTypeHandler<T> {

    private Class<T> clazz;

    public JsonbTypeHandler(Class<T> clazz) {
        if (clazz == null) {
            throw new IllegalArgumentException("Type argument cannot be null");
        this.clazz = clazz;

    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)
        throws SQLException {
        if (ps != null) {
            PGobject jsonObject = new PGobject();
            ps.setObject(i, jsonObject);

    public T getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        return parse(resultSet.getString(columnName));

    public T getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return (T)parse(resultSet.getString(i));

    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;


(1) You need to put the customized jsonb object into the @ mappedtypes annotation and give it to the factory registration class management of mybatis's typehandler

(2) The specified scanning typehandler path of mybatis plus needs to be specified:

            (that is, register the customized typehandler in the manager of sqlsessionfactory)

Recommended Today

OC basis

IOS development interview essential skills chart.png What are objects and what are the objects in OC? An object is an instance of a class; Is an instance created through a class, which is generally called an instance object; Common objects in OC include instance objects, class objects, and metaclass objects; What is a class? What […]