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.

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

2. Core profile

Druid connection pool management is used here.

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driverClassName: org.postgresql.Driver
      url: jdbc:postgresql://127.0.0.1:5432/db_01
      username: root01
      password: 123456

3. Connection pool configuration

@Configuration
public class DruidConfig {

    @Value("${spring.datasource.druid.url}")
    private String dbUrl;
    @Value("${spring.datasource.druid.username}")
    private String username;
    @Value("${spring.datasource.druid.password}")
    private String password;
    @Value("${spring.datasource.druid.driverClassName}")
    private String driverClassName;

    @Bean
    public DruidDataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        return datasource;
    }
}

4. Persistence layer configuration

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

mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  typeAliasesPackage: com.post.gresql.*.entity
  global-config:
    db-config:
      id-type: AUTO
      field-strategy: NOT_NULL
      logic-delete-value: -1
      logic-not-delete-value: 0
    banner: false
  configuration:
    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")
@RestController
public class UserController {

    @Resource
    private UserService userService ;

    @GetMapping("/selectById")
    public UserEntity selectById (Integer id){
        return userService.selectById(id) ;
    }

    @PostMapping("/insert")
    public Integer insert (UserEntity userEntity){
        return userService.insert(userEntity) ;
    }

    @GetMapping("/pageQuery")
    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 (
    ID INT NOT NULL,
    title VARCHAR (32) NOT NULL,
    user_list json NOT NULL,
    create_time TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP,
    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.

@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("json");
        jsonObject.setValue(parameter.toString());
        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);
    }
}

3. Call method

Specify the mapping type typeHandler of the field.

<mapper namespace="com.post.gresql.mapper.UserJsonMapper" >

    <insert id="addUserJson" parameterType="com.post.gresql.entity.UserJsonEntity">
        INSERT INTO pq_user_json (id,title,user_list,create_time)
        VALUES (#{id}, #{title}, #{userList, typeHandler=com.post.gresql.config.JsonTypeHandler}, #{createTime})
    </insert>

</mapper>

4. JSON format test

JSON format data warehousing, outbound query.

@RestController
public class UserJsonController {

    @Resource
    private UserJsonService userJsonService ;

    @GetMapping("/addUserJson")
    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());
        userEntities.add(userEntity1);
        userEntities.add(userEntity2);
        UserJsonEntity userJsonEntity = new UserJsonEntity();
        userJsonEntity.setId(1);
        userJsonEntity.setTitle("List of the first batch");
        userJsonEntity.setUserList(JSON.toJSONString(userEntities));
        userJsonEntity.setCreateTime(new Date());
        return userJsonService.addUserJson(userJsonEntity) ;
    }

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

4, Source code address

GitHub·address
https://github.com/cicadasmile/data-manage-parent
GitEE·address
https://gitee.com/cicadasmile/data-manage-parent