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, String> {

@Override

public String convertToDatabaseColumn(List 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 convertToEntityAttribute(String dbData) {

ObjectMapper mapper = new ObjectMapper();

List list = null;

try {

list = (List) mapper.readValue(dbData, List.class);

} catch (JsonParseException e) {

e.printStackTrace();

} catch (JsonMappingException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return list;

}

}

@Entity

@Table(name = "testjson2")

public class Testjson2 {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

private Integer id;

// List转换为String,数据库中再转换为JSON.

@Convert(converter = ListString2JsonConverter.class)

private List answers;