Spring Data Jpa整合PostgreSQL（一）

蔚落 2022-05-29 02:53 344阅读 0赞

直接看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了,这都什么玩意啊,浪费了我两天时间.