springboot mybatis-plus连接 postgreSQL_mybatisplus pgsql

首先引入依赖包

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
 <!-- mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3</version>
        </dependency>
         <!--l数据库驱动-->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.14</version>
            <scope>runtime</scope>
        </dependency>

 <!--lombok自动注解-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
 <!--阿里数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.1</version>
        </dependency>
        <!-- 切换mysql库-->
          <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
            <scope>runtime</scope>
        </dependency>
        <!-- 动态数据源 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

然后开始建表

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";
1
2
3
4
5
6
7
8
9
10
11

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

创建实体类

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;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

创建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<PostUser> {
}
1
2
3
4
5
6
7
8

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<PostUser> {
}
1
2
3
4
5
6

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<UserMapper, PostUser> implements UserService {
}
1
2
3
4
5
6
7
8
9
10
11
12

编写测试类测试

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<PostUser> 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<PostUser> list = userService.list();
//        System.out.println(System.currentTimeMillis()-l);

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

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