Data source management | PostgreSQL environment integration, JSON type application

Source code: GitHub point here || GitEE point here

1, Introduction to PostgreSQL

1. Comparison with MySQL

PostgreSQL is a powerful and open-source relational database system. There are a lot of comparative analysis between PostgreSQL and MySQL on the Internet. Mostly from the performance, open source protocol, SQL standards, development difficulties to compare, as long as there will be differences and differences, just have a look.

Let's go on: there is always a contrast in the programming world, but no matter what the result is, it should be used or used when the business needs it. Compared with PostgreSQL, MySQL rarely prevails, but it is still widely used in China.

2. PostgreSQL features

  • Synchronous replication of multiple copies to meet the financial level reliability requirements;
  • Support rich data types, including text, image, sound, video, JSON, etc. in addition to common basic ones;
  • With full-text search function, it can simplify the implementation process of search function;
  • Efficiently handle the graph structure, and easily realize the relationship type of "friend's friend's friend's friend";
  • Geographic information processing extension, support map search related business;

2, Development environment integration

1. Basic dependence

Import dependent package, version will be loaded automatically. This case is loaded with version 42.2.6.


2. Core profile

Druid connection pool management is used here.

      driverClassName: org.postgresql.Driver
      url: jdbc:postgresql://
      username: root01
      password: 123456

3. Connection pool configuration

public class DruidConfig {

    private String dbUrl;
    private String username;
    private String password;
    private String driverClassName;

    public DruidDataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        return datasource;

4. Persistence layer configuration

Based on related components of mybatis, it is basically consistent with MySQL environment integration in usage.

  mapper-locations: classpath*:/mapper/**/*.xml
      id-type: AUTO
      field-strategy: NOT_NULL
      logic-delete-value: -1
      logic-not-delete-value: 0
    banner: false
    map-underscore-to-camel-case: true
    cache-enabled: false
    call-setters-on-nulls: true
    jdbc-type-for-null: 'null'

5. Basic test cases

Provide a basic use case of data query, write and page query.

@Api(value = "UserController")
public class UserController {

    private UserService userService ;

    public UserEntity selectById (Integer id){
        return userService.selectById(id) ;

    public Integer insert (UserEntity userEntity){
        return userService.insert(userEntity) ;

    public PageInfo<UserEntity> pageQuery (@RequestParam("page") int page){
        int pageSize = 3 ;
        return userService.pageQuery(page,pageSize) ;

3, JSON type usage

PostgreSQL supports JSON data type format, but it differs from general data types in usage.

1. Json table field creation

Here, the user list field is of JSON type, which are the first batch of users in the storage scenario, which are the second batch of users, and so on.

CREATE TABLE pq_user_json (
    title VARCHAR (32) NOT NULL,
    user_list json NOT NULL,
    CONSTRAINT "user_json_pkey" PRIMARY KEY ("id")

2. Type converter

Define a converter between database and entity object, mainly the conversion between JSON data and Java object.

public class JsonTypeHandler extends BaseTypeHandler<Object> {

    private static final PGobject jsonObject = new PGobject();

    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setObject(i, jsonObject);

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

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

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

3. Call method

Specify the mapping type typeHandler of the field.

<mapper namespace="" >

    <insert id="addUserJson" parameterType="">
        INSERT INTO pq_user_json (id,title,user_list,create_time)
        VALUES (#{id}, #{title}, #{userList,}, #{createTime})


4. JSON format test

JSON format data warehousing, outbound query.

public class UserJsonController {

    private UserJsonService userJsonService ;

    public boolean addUserJson (){
        List<UserEntity> userEntities = new ArrayList<>() ;
        UserEntity userEntity1 = new UserEntity(1,"LiSi",22,new Date());
        UserEntity userEntity2 = new UserEntity(2,"WangWu",23,new Date());
        UserJsonEntity userJsonEntity = new UserJsonEntity();
        userJsonEntity.setTitle("List of the first batch");
        userJsonEntity.setCreateTime(new Date());
        return userJsonService.addUserJson(userJsonEntity) ;

    public List<UserEntity> findUserJson (@RequestParam("id") Integer id){
        UserJsonEntity userJsonEntity = userJsonService.findUserJson(id) ;
        return JSON.parseArray(userJsonEntity.getUserList(),UserEntity.class) ;

4, Source code address


Tags: Programming JSON PostgreSQL Druid Spring

Posted on Sat, 25 Apr 2020 22:03:05 -0400 by SurgeProto