springboot mybatis-plus连接 postgreSQL

首先引入依赖包

 org.springframework.boot spring-boot-starter-test    com.baomidou mybatis-plus-boot-starter 3.4.3    org.postgresql postgresql 42.2.14 runtime    org.projectlombok lombok 1.18.20 provided    com.alibaba druid-spring-boot-starter 1.2.1    mysql mysql-connector-java 8.0.27 runtime    com.baomidou dynamic-datasource-spring-boot-starter 3.5.1   com.baomidou mybatis-plus-generator 3.5.2 

然后开始建表

CREATE TABLE "public"."post_user" (
  "id" int8 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "code" varchar(255) COLLATE "pg_catalog"."default",
  "edit_time" date,
  CONSTRAINT "user_pkey" PRIMARY KEY ("id")
)
;
ALTER TABLE "public"."post_user" 
  OWNER TO "postgres";

application.yml 配置文件 这里可以加入动态数据源切换mysql数据库对比一下mysql性能

spring:
  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 50MB
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss   #涉及接收日期参数格式
  main:
    allow-bean-definition-overriding: true  #重写jacksonObjectMapper
#  datasource:
#    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
#    url: jdbc:sqlserver://10.10.1.65:1433;DatabaseName=EVODetection_ASSY_BZH
#    username: sa
#    password: 123456
  datasource:
    dynamic:
      druid:
        #        filter: stat,wall,log4j  #druid监控
        initial-size: 1 #连接池初始化大小
        min-idle: 3 #最小空闲连接数
        max-active: 3 #最大连接数
        #监控页面的配置放在了DruidConfig中，否则不起作用
      #        stat-view-servlet:
      #          enabled: true
      #          login-username: admin
      #          login-password: admin
      primary: dataSourceDefault
      datasource:
        dataSourceDefault:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://10.10.1.82:3308/EVODetection_ASSY_BZH_JM?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
          username: root
          password: Fitow@2022xxx
        dataSourcePostgreSql:
          driver-class-name: org.postgresql.Driver
          url: jdbc:postgresql://192.168.206.128:5432/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowMultiQueries=true
          username: postgres
          password: 123456xxx
        dataSourceM:
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.206.128:3306/test?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
            username: root
            password: 123456

创建实体类

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
import java.util.Date;
@Getter
@Setter
@TableName("post_user")
public class PostUser implements Serializable {
    @TableId("id")
    @ApiModelProperty(value = "主键ID")
    private Long id;
    @TableField("name")
    private String name;
    @TableField("code")
    private String code;
    @TableField(value = "edit_time",fill = FieldFill.INSERT_UPDATE)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date editTime;
}

创建mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fitow.ois.entity.PostUser;
import com.fitow.ois.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper {
}

service

import com.baomidou.mybatisplus.extension.service.IService;
import com.fitow.ois.entity.PostUser;
import com.fitow.ois.entity.User;
public interface UserService extends IService {
}

serviceImpl

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fitow.ois.entity.PostUser;
import com.fitow.ois.entity.User;
import com.fitow.ois.mapper.UserMapper;
import com.fitow.ois.service.UserService;
import org.springframework.stereotype.Service;
@Service
@DS("dataSourcePostgreSql") //数据源切换
public class UserServiceImpl extends ServiceImpl implements UserService {
}

编写测试类测试

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.fitow.ois.service.*;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
i
/**
 * @desc
 * @Author zhangchuang
 * @date 2021/10/18 10:41
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class CommonTest {
    @Autowired
    UserService userService;
    @Test
    public  void test() throws Exception {
//        //todo 插入性能
//        long l = System.currentTimeMillis();
//        for(int i =0;i<100000;i++){
//            ArrayList postUsers = new ArrayList<>(1000);
//            for(int m = 0; m<1000; m++){
//                PostUser user = new PostUser();
//                user.setName("xxxx");
//                user.setCode("xxsdf");
//                postUsers.add(user);
//            }
//            userService.saveBatch(postUsers);
//        }
//        System.out.println(System.currentTimeMillis()-l);
        //查询性能
//        long l = System.currentTimeMillis();
//        List list = userService.list();
//        System.out.println(System.currentTimeMillis()-l);
    }

然后就可以测试了，

写了一个批量插入10万数据 4秒插入完成 同样的代码 mysql8.0 65秒插入完成，吞吐量postgreSQL性能确实占优。