PostgreSQL 保存json,jsonb类型

PostgresQL 字符串隐式转换JSON脚本：

-- 隐式将varchar转换为json
CREATE OR REPLACE FUNCTION json_in_varchar(varchar) RETURNS json AS $$
SELECT json_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;

DROP CAST IF EXISTS (varchar as json);
CREATE CAST (varchar AS json) WITH FUNCTION json_in_varchar(varchar) AS IMPLICIT;

-- 隐式将varchar转换为jsonb
CREATE OR REPLACE FUNCTION jsonb_in_varchar(varchar) RETURNS jsonb AS $$
SELECT jsonb_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;

DROP CAST IF EXISTS (varchar as jsonb);
CREATE CAST (varchar AS jsonb) WITH FUNCTION jsonb_in_varchar(varchar) AS IMPLICIT;

 

package com.apress.spring.domain;

import java.io.IOException;
import java.util.List;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;;

/*
 * */
@Converter
public class ListString2JsonConverter implements AttributeConverter<List<String>, String> {

	@Override
	public String convertToDatabaseColumn(List<String> attribute) {
		if(attribute == null || attribute.size() == 0) return "[]";
		ObjectMapper mapper = new ObjectMapper();
		String json = null;
		try {
			json = mapper.writeValueAsString(attribute);
		} catch (JsonProcessingException e) {
			e.printStackTrace();
		}
		return json;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> convertToEntityAttribute(String dbData) {
		ObjectMapper mapper = new ObjectMapper();
		List<String> list = null;
		try {
			list = (List<String>) mapper.readValue(dbData, List.class);
		} catch (JsonParseException e) {
			e.printStackTrace();
		} catch (JsonMappingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

}


/*
 * 使用hibernate类型转换
 * SQL script:
 * testdb=# create table testjson2(id serial, data json, answers jsonb, remain_balance decimal(19,6));
 * */
@Entity
@Table(name = "testjson2")
public class Testjson2 {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

// List转换为String,数据库中再转换为JSON.
	@Convert(converter = ListString2JsonConverter.class)
	private List<String> answers;