demo代码说明
springboot使用mybatis-plus整合pgsql
在springboot项目中使用pgsql,使用了mybatis-plus、druid
代码见 spring-demo: springboot 结合各种插件 demo
注意事项:
版本兼容
正常springboot使用,但是注意需要添加pgsql正确版本[maven pgsql高版本向下兼容],
比如pgsql时14,那么需要对应maven中42.5版本,可以查看pgsql对jdbc的官网Home | pgJDBC
pgsql | maven-pgsql |
14.x | 42.5 |
13.x | 42.4 |
12.x | 42.2.24 |
11.x | 42.2.20 |
类型转换
pgsql特殊类型,比如location与java数据类型的相互转换问题
在java项目中进行转换,一般按照三步:
1.定义转换规则,及pgsql中的类型如何转换到java中
public class LocationTypeHandler extends BaseTypeHandler<double[]> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, double[] parameter, JdbcType jdbcType) throws SQLException {
if (parameter != null && parameter.length == 2) {
ps.setObject(i, "(" + parameter[0] + "," + parameter[1] + ")", Types.OTHER);
} else {
ps.setNull(i, Types.OTHER);
}
}
@Override
public double[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
String point = rs.getString(columnName);
if (point == null) {
return null;
}
point = point.replace("(", "").replace(")", "");
String[] split = point.split(",");
return new double[]{Double.parseDouble(split[0]), Double.parseDouble(split[1])};
}
@Override
public double[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String point = rs.getString(columnIndex);
if (point == null) {
return null;
}
point = point.replace("(", "").replace(")", "");
String[] split = point.split(",");
return new double[]{Double.parseDouble(split[0]), Double.parseDouble(split[1])};
}
@Override
public double[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String point = cs.getString(columnIndex);
if (point == null) {
return null;
}
point = point.replace("(", "").replace(")", "");
String[] split = point.split(",");
return new double[]{Double.parseDouble(split[0]), Double.parseDouble(split[1])};
}
}
2.注册自定义转换规则
@Configuration
public class MyBatisConfig {
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
// 获取类型处理器注册器
TypeHandlerRegistry typeHandlerRegistry = sessionFactory.getObject().getConfiguration().getTypeHandlerRegistry();
// 注册自定义类型处理器
typeHandlerRegistry.register(double[].class, JdbcType.OTHER, LocationTypeHandler.class);
return sessionFactory.getObject();
}
}
3.使用
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("city")
public class City {
private Integer id;
private String name;
@TableField(typeHandler = LocationTypeHandler.class)
private double[] location;
}
这样可以正常相互转换
double[] location = new double[] {1,4};
testService.addCity(new City(4,"nj",location));