编程知识 cdmana.com

Notes on Java backend development of PostgreSQL (I)

postgreSQL Of JAVA The backend development SQL Notes ( One )

1、 Need one : You need to recursively query all child records under the parent record

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

The inadequacies of being : Unable to achieve dynamic expansion , Add new children later , Need to refactor code , Unable to adapt to new requirements , Do not use this method to implement

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

advantage : Support dynamic expansion , Add a new child ,sql It also supports querying all child elements

Case study :

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、 Demand two : Query the specified group , The latest record

reflection :
(1) Fuzzy query , Get all data under the specified group
(2) Arrange in reverse order according to the update time , Get the first record , Use limit sentence

Case study :

select * from tb_worker where group = ' The first 9 Group ' order by update_time desc limit 1




3、 Demand 3 : Put... In the data table bigint The timestamp of the type , adopt sql Inquire about , The return format is ‘yyyy-MM-dd HH24:MI:SS’ Format date string

reflection :
(1) You need to convert the timestamp of the specified field to ‘yyyy-MM-dd HH24:MI:SS’ Format string
(2) Specify the column name to return the corresponding data

Case study :

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




4、 Demand 4 : The implementation will jsonb Format fields , Use mybatis When inquiring , Support updating with specific objects 、 Query operation

reflection
(1) because db It uses postgreSQL,mybatis Framework provided typeHandler I won't support it jsonb Field entity object operation , Need to customize a support jsonb Object operation typeHandler
(2) You need to customize typeHandler Sign up to mybatis Default management factory

Case study :

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  General purpose jsonb Attribute handler processor   Be careful : Out of commission Object.class Put it in MappedTypes In the annotations , This will result in an error when the type parser cannot be obtained 
 */
@MappedTypes({JSONObject.class})
@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;
    }

    @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)  You need to set your own jsonb Objects in the @MappedTypes Annotations , hand mybatis Of typeHandler Factory registration management 

(2) You need to specify the mybatis-plus Specified scan for typeHandler route :

            ( About to customize typeHandler Sign up to sqlSessionFactory In the manager of )

    mybatis-plus.typeHandlersPackage=com.xxx.xxx.xx.handler

版权声明
本文为[JAVA_ IN_ LIFE]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/09/20210909134311743z.html

Scroll to Top