新增:
INSERT INTO test_json (
json_value,
create_time
)
VALUES
(
'{"name":"limingming","age":15}',
now()
);
查询:
//查询全部json
SELECT
json_value ->> '$' AS jsonValue
FROM
test_json;
//查询json指定字段
SELECT
json_value ->> '$.name' AS name
FROM
test_json
更新:
//全部json
UPDATE test_json
SET json_value = json_set (json_value, '{"name":"libai","age":15}')
WHERE
age = 15;
//json指定字段
UPDATE test_json
SET json_value = json_set (json_value, '$.name', 'libai')
WHERE
age = 15;
mybatis整合json ,需要定义sql处理器
@MappedTypes(JSONObject.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class MybatisJsonHandler extends BaseTypeHandler<JSONObject> {
/**
* 设置非空参数
* @param ps PreparedStatement
* @param i int
* @param parameter JSONObject
* @param jdbcType JdbcType
* @throws SQLException
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, String.valueOf(parameter.toJSONString()));
}
/**
* 根据列名,获取可以为空的结果
* @param rs
* @param columnName
* @return
* @throws SQLException
*/
@Override
public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
String sqlJson = rs.getString(columnName);
if (null != sqlJson){
try {
sqlJson = new String(sqlJson.getBytes("iso-8859-1"),"utf-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return JSONObject.parseObject(sqlJson);
}
return null;
}
/**
* 根据列索引,获取可以为空的结果
* @param rs
* @param columnIndex
* @return
* @throws SQLException
*/
@Override
public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String sqlJson = rs.getString(columnIndex);
if (null != sqlJson){
return JSONObject.parseObject(sqlJson);
}
return null;
}
@Override
public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String sqlJson = cs.getString(columnIndex);
if (null != sqlJson){
return JSONObject.parseObject(sqlJson);
}
return null;
}
}