基于Nacos-1.4.0

1. 修改配置文件

esourcesapplication.properties

#*************** Config Module Related Configurations ***************#
### If use MySQL as datasource:
spring.datasource.platform=postgresql
### Count of DB:
db.num=1
### Connect URL of DB:
db.url.0=jdbc:postgresql://192.168.71.100:5432/appform?characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=UTC
db.user=appform
db.password=appform

2. 修改配置相关Java

PropertyUtil.java

// External data sources are used by default in cluster mode
setUseExternalDB("postgresql".equalsIgnoreCase(getString("spring.datasource.platform", "")));

StartingSpringApplicationRunListener.java

// External data sources are used by default in cluster mode
boolean useExternalStorage = ("postgresql".equalsIgnoreCase(env.getProperty("spring.datasource.platform", "")));

3. 修改SQL

ExternalStoragePaginationHelperImpl.java

String selectSql = "";
if (isDerby()) {
          
   
    selectSql = sqlFetchRows + " OFFSET " + startRow + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
} else if (lastMaxId != null) {
          
   
    selectSql = sqlFetchRows + " and id > " + lastMaxId + " order by id asc" + " limit " + 0 + "," + pageSize;
} else {
          
    // 修改部分, mysql 改 postgre 查询
    selectSql = sqlFetchRows + " limit " + pageSize + " offset " + startRow;
}
List<E> result = jdbcTemplate.query(selectSql, args, rowMapper);

修改角色模糊查询ExternalRolePersistServiceImpl.java

@Override
public List<String> findRolesLikeRoleName(String role) {
          
   
    String sql = "SELECT role FROM roles WHERE role like %" + role + "%";
    List<String> users = this.jt.queryForList(sql, null, String.class);
    return users;
}

修改用户模糊查询ExternalUserPersistServiceImpl.java

@Override
public List<String> findUserLikeUsername(String username) {
          
   
    String sql = "SELECT username FROM users WHERE username like %" + username + "%";
    List<String> users = this.jt.queryForList(sql, null, String.class);
    return users;
}

修改获取返回主键IDExternalStoragePersistServiceImpl.java

PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
改为
PreparedStatement ps = connection.prepareStatement(sql, new String[]{
          
   "id"});

否则会报：The getKey method should only be used when a single key is returned

4. 将ExternalStoragePersistServiceImpl.java中的limit ?,? 改为 offset ？ limit ？

5. PostgreSQL schema