Spring Boot JPA操作PostgreSQL的jsonb类型

Tips:
springboot版本：2.0.8.RELEASE
jdk：1.8

在设计数据库表的时候，不确定后期升级是否需要扩展字段，大多数人会添加扩展字段，PostgreSQL的jsonb类型为我们提供了极大的便利。

为了让JPA支持jsonb，需要自定义PostgreSQL9Dialect

import org.hibernate.dialect.PostgreSQL9Dialect;

import java.sql.Types;

public class CustomPostgreSqlDialect extends PostgreSQL9Dialect {

    public CustomPostgreSqlDialect() {
        super();
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }
}

指定方言spring.jpa.database-platform: com.xxx.PostgreSqlDialect,如下

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/postgres
    username: postgres
    password: 123456
    hikari:
      connection-timeout: 20000
      maximum-pool-size: 5
  jpa:
    hibernate:
      ddl-auto: create
    show-sql: true
    database-platform: com.example.jsonb.config.CustomPostgreSqlDialect
    properties:
      hibernate.temp.use_jdbc_metadata_defaults: false
logging:
  level:
    org.hibernate.type.descriptor.sql.BasicBinder: trace

自定义jsonb数据类型

import com.fasterxml.jackson.databind.ObjectMapper;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.type.SerializationException;
import org.hibernate.usertype.UserType;
import org.postgresql.util.PGobject;
import org.springframework.util.ObjectUtils;

import java.io.IOException;
import java.io.Serializable;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class JsonbType implements UserType {

    private final ObjectMapper mapper = new ObjectMapper();

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }

    @Override
    public Class<?> returnedClass() {
        return Map.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return ObjectUtils.nullSafeEquals(x, y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x == null ? 0 : x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor sharedSessionContractImplementor, Object owner) throws HibernateException, SQLException {
        Object obj = rs.getObject(names[0]);
        if (obj == null) {
            return null;
        }
        PGobject o = (PGobject) obj;
        if (o.getValue() != null) {
            try {
                // return JSON.parse(o.getValue(), Map.class);
                return mapper.readValue(o.getValue(), Map.class);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return new HashMap<String, Object>();
    }

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

    @Override
    public Object deepCopy(Object originalValue) throws HibernateException {
        if (originalValue != null) {
            try {
                // 替换为其他JSON解析器
                // return JSON.parse(JSON.toJSON(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 boolean isMutable() {
        return true;
    }

    @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 o) throws HibernateException {
        return deepCopy(cached);
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original);
    }
}

在Entity上使用

import com.example.jsonb.config.JsonbListType;
import com.example.jsonb.config.JsonbType;
import lombok.Data;
import lombok.experimental.Accessors;
import org.hibernate.annotations.*;

import javax.persistence.*;
import javax.persistence.Entity;
import java.util.List;
import java.util.Map;

@Entity
@Data
@Accessors(chain = true)
@TypeDef(name = "JsonbType", typeClass = JsonbType.class)
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType")
    private Map<String, Object> info;
}

5.定义repository

import com.example.jsonb.po.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface AccountRepository extends JpaRepository<Account, Integer> {
    @Query(value = "select * from account where info ->> 'name' = :name", nativeQuery = true)
    List<Account> findByName(@Param("name") String name);
}

测试

import com.example.jsonb.po.Account;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.*;

@RunWith(SpringRunner.class)
@SpringBootTest
public class AccountRepositoryTest {

    @Autowired
    AccountRepository accountRepository;

    @Before
    public void setUp() throws Exception {
        HashMap<String, Object> info = new HashMap<>();
        info.put("name", "光头强");
        info.put("age", 3);
        info.put("tel", "110");
        info.put("Hobby", Arrays.asList("吃饭", "睡觉", "砍树", "打豆豆"));
        Account account = new Account().setInfo(info);
        accountRepository.save(account);
    }

    @Test
    public void findByName() {
        List<Account> accounts = accountRepository.findByName("光头强");
        System.out.println(accounts);
    }
}

测试结果