PostgreSql 项目实战 3 - springBoot3 + mybatis 迁移pg库修改配置

1 配置pox

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: org.postgresql.Driver  # 修改驱动类
    druid:
      # 主库数据源
      master:
        url: jdbc:postgresql://192.168.1.xx:5432/库?currentSchema=public&stringtype=unspecified&TimeZone=Europe/Madrid 
        username: username
        password: password

    # 配置检测连接是否有效 - PostgreSQL 验证SQL
    validationQuery: SELECT 1

    # 添加JPA配置用于方言识别 - postgresql
    jpa:
      properties:
        hibernate:
          jdbc:
            time_zone: Europe/Madrid
      database-platform: org.hibernate.dialect.PostgreSQLDialect
      show-sql: false
      hibernate:
        ddl-auto: none

# PageHelper分页插件
pagehelper:
  helperDialect: postgresql # 切换数据源 支持pg库 LIMIT ? OFFSET ? 语法
  supportMethodsArguments: true
  params: count=countSql
  reasonable: true

2 pg连接驱动

<postgresql.version>42.7.8</postgresql.version>

<!-- postgresql 驱动-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

3 业务代码改造

3.1 sysdate() 不兼容

使用 `now()` 会返回带时区的时间戳，自动适应 Europe/Madrid 时区

3.2 字符串拼接不兼容

mysql:模糊匹配

AND menu_name like concat('%', #{menuName}, '%')

pg: 模糊匹配

-- 不区分大小写
AND menu_name ILIKE '%'|| #{menuName} || '%'
-- 区分大小写
AND menu_name LIKE '%'|| #{menuName} || '%'

3.2 关键字

MySQL：

`query`

pg:

"query"   在pg中query不在是关键字，但是允许""

3.3 空判断

MySQL：

ifnull(perms,'') as perms

pg：

coalesce(perms,'') as perms

3.4 时间判断

mysql: date_format（）pg库不支持

<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
    and date_format(create_time,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
    and date_format(create_time,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
</if>

pg:

<if test="params.beginTime != null and params.beginTime != ''">
    and create_time &gt;= to_date(#{params.beginTime}, 'YYYY-MM-DD')
</if>
<if test="params.endTime != null and params.endTime != ''">
    and create_time &lt; to_date(#{params.endTime}, 'YYYY-MM-DD') + interval '1 day'
</if>

3.5 find_in_set 不支持

mysql:

<update id="updateDeptChildrenStatus">
    update sys_dept set status = #{status} where find_in_set(#{deptId}, ancestors) and  tenant_id = #{tenantId}
</update>

pg库：

<!-- 1 字符串包含判断 -->
<update id="updateDeptChildrenStatus">
    UPDATE sys_dept 
    SET status = #{status} 
    WHERE ancestors LIKE '%' || #{deptId} || '%' 
      AND tenant_id = #{tenantId}
</update>

<!-- 2 正则包含判断 -->
<update id="updateDeptChildrenStatus">
    UPDATE sys_dept 
    SET status = #{status} 
    WHERE ancestors ~ ('(^|,)' || #{deptId} || '(,|$)')
      AND tenant_id = #{tenantId}
</update>

<!-- 3 最精确，需要标准逗号分隔 -->
<update id="updateDeptChildrenStatus">
    UPDATE sys_dept 
    SET status = #{status} 
    WHERE #{deptId} = ANY(string_to_array(ancestors, ','))
      AND tenant_id = #{tenantId}
</update>

4 主键需要单独创序列

-- 创建序列
CREATE SEQUENCE erp_mesa_mesa_id_seq;

-- 修改表结构，设置默认值为序列
ALTER TABLE erp_mesa 
ALTER COLUMN mesa_id SET DEFAULT nextval('erp_mesa_mesa_id_seq');

-- 或者重建表时指定
CREATE TABLE erp_mesa (
    mesa_id BIGINT PRIMARY KEY DEFAULT nextval('erp_mesa_mesa_id_seq'),
    mesa_name VARCHAR(255) NOT NULL,
    mesa_no VARCHAR(50),
    max_capacity INTEGER,
    min_capacity INTEGER,
    create_time TIMESTAMP
);