Spring Data Jpa整合PostgreSQL（一）

直接看2吧。。

==================

自己写的时候遇到很多问题,总结一下,分享出来,留作记录

只是简单的实现,以后会尝试PostgreSQL的更多特性

安装PostgreSQL : 可选方案是在你的系统环境下安装官方提供的安装包,或是运行Docker镜像.我使用的版本为10.3

Spring Boot版本暂不可以为2.0,原因是不兼容当前驱动版本.我使用的是1.5.9.RELEASE. Postgre驱动版本为42.2.1

首先要导入依赖包..

    compile('org.springframework.boot:spring-boot-starter-data-jpa')
    compile('org.springframework.boot:spring-boot-starter-web')
    compile group: 'org.postgresql', name: 'postgresql', version: '42.2.1'
    compile group: 'org.projectlombok',name: 'lombok'
    testCompile('org.springframework.boot:spring-boot-starter-test')

ps:你需要一个lombok插件,如果不会安装就不要导入这个依赖

配置yml

spring:
  datasource:
    url: jdbc:postgresql://19:5432/postgres
    username: postgres
    password: 123456
    driverClassName: org.postgresql.Driver
  jpa:
    show-sql: true
    database-platform: cn.zhenyang.cap.galaxy.dialect.JsonbPostgresDialect
    hibernate:
      ddl-auto: update

 

ps:请注意,方言是必须要配的,不然就会很难受.下面会提到

 

public class JsonbPostgresDialect extends PostgreSQL94Dialect {


	public JsonbPostgresDialect() {
		super();
        registerColumnType(Types.JAVA_OBJECT, "jsonb");
        registerHibernateType( Types.ARRAY, StringType.class.getName());
	}
}

另外还要自定义jsonb类型

public class JsonbType implements UserType, ParameterizedType {

	private final ObjectMapper mapper = new ObjectMapper();;

	private static final ClassLoaderService classLoaderService = new ClassLoaderServiceImpl();

	public static final String CLASS = "CLASS";

	private Class<?> jsonClassType;


	@Override
	public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
			throws HibernateException, SQLException {
		if (value == null) {
			st.setNull(index, Types.OTHER);
		} else {
			try {
				st.setObject(index, mapper.writeValueAsString(value), Types.OTHER);
			} catch (IOException e) {
				e.printStackTrace();
			}

		}
	}

	@Override
	public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
			throws HibernateException, SQLException {
		PGobject o = (PGobject) rs.getObject(names[0]);
		if (o.getValue() != null) {
			// 这里请自行判断~~~
//			return new UserInfo();
			//			return null;
		}

//		return new HashMap<String, Object>();
		return null;
	}
	@Override
	public Object deepCopy(Object originalValue) throws HibernateException {
		if (originalValue != null) {
			try {
				System.out.println(mapper.readValue(mapper.writeValueAsString(originalValue), returnedClass()));
				return mapper.readValue(mapper.writeValueAsString(originalValue), returnedClass());
			} catch (IOException e) {
				throw new HibernateException("Failed to deep copy object", e);
			}
		}
		return null;
	}


	@Override
	public Serializable disassemble(Object value) throws HibernateException {
		Object copy = deepCopy(value);

		if (copy instanceof Serializable) {
			return (Serializable) copy;
		}

		throw new SerializationException(
				String.format("Cannot serialize '%s', %s is not Serializable.", value, value.getClass()), null);
	}

	@Override
	public Object assemble(Serializable cached, Object owner) throws HibernateException {
		return deepCopy(cached);
	}

	@Override
	public Object replace(Object original, Object target, Object owner) throws HibernateException {
		return deepCopy(original);
	}
	@Override
	public boolean isMutable() {
		return true;
	}
	@Override
	public int hashCode(Object x) throws HibernateException {
		if (x == null) {
			return 0;
		}
		return x.hashCode();
	}
	@Override
	public boolean equals(Object x, Object y) throws HibernateException {
		return ObjectUtils.nullSafeEquals(x, y);
	}
	@Override
	public Class<?> returnedClass() {
		return Map.class;
	}
	@Override
	public int[] sqlTypes() {
		return new int[] { Types.JAVA_OBJECT };
	}
	@Override
	public void setParameterValues(Properties parameters) {
		final String clazz = (String) parameters.get(CLASS);
		jsonClassType = classLoaderService.classForName(clazz);
	}

 

实体类:

 

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@TypeDefs({
        @TypeDef(name = "JsonbType", typeClass = JsonbType.class, parameters = {
                @Parameter(name = JsonbType.CLASS, value = "cn.zhenyang.pgsql.entity.UserInfo")}),
//        @TypeDef(name = "JsonbType", typeClass = UserType.class, parameters = {
//                @Parameter(name = JsonbType.CLASS, value = "cn.zhenyang.pgsql.entity.UserInfo) })
})
@Table(name = "tb_user", schema = "cap")
public class User {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType")
    private UserInfo userInfo;

    private UserSecret userSecret;

    @Embedded
    @AttributeOverrides({@AttributeOverride(name = "country", column = @Column(name = "nationality"))})
    private UserAddress userAddress;

    private UserContactWay userContactWay;

}

使用原生sql

    @Query(value = "select * from cap.tb_user where (user_info ->> 'nation') = ?1",nativeQuery = true)
    List<User> findByNation(String nation);

经测试,可以完成数据的增删查.

总结一下吧:

1.普通数据的修改是可以的,但是,在修改jsonb数据时,会出现实体与HashMap类型转换的错误,暂不知咋修改.

2.hibernate本身不支持双冒号的语法,需要用形如:

select enum_range(null\\:\\:week);

这样的转义字符才可以生效.

3.虽然指定的方言,但还是无法识别如enum_range等的方法,需要注册方言类型.

registerHibernateType( Types.ARRAY, StringType.class.getName());

registerColumnType(Types.JAVA_OBJECT, "jsonb");

好歹算是写出来了,但我觉得吧,既然都这样了,我一个JPA的优势没用到,净填坑了,还不如用Mybatis了,这都什么玩意啊,浪费了我两天时间.

附上git地址,想受苦的可以参考一下,如果研究明白了拜托写到博客分享一下....

https://gitee.com/zhenyang_294/postgres-demo.git