Spring Boot Tk Mybatis PostgreSQL 整合实现 json 增删改查-昊天SEO

package com.mpper.configer;

import com.alibaba.druid.support.json.JSONUtils;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



@MappedTypes(JSONObject.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JsonTypeHandler extends BaseTypeHandler<JSONObject>{
    private static final PGobject pgObject = new PGobject();

    //设置非空参数
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {

        pgObject.setType("json");
        pgObject.setValue(JSONUtils.toJSONString(parameter));
        ps.setObject(i, pgObject);
    }

    //根据列名，获取可以为空的结果
    @Override
    public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String sqlJson = rs.getString(columnName);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }
    //根据列索引，获取可以为空的结果
    @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;
    }

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

package com.mpper.configer;

import com.alibaba.druid.support.json.JSONUtils;

import com.alibaba.fastjson.JSONObject;

import org.apache.ibatis.type.BaseTypeHandler;

import org.apache.ibatis.type.JdbcType;

import org.apache.ibatis.type.MappedJdbcTypes;

import org.apache.ibatis.type.MappedTypes;

import org.postgresql.util.PGobject;

import java.sql.CallableStatement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

@MappedTypes(JSONObject.class)

@MappedJdbcTypes(JdbcType.VARCHAR)

public class JsonTypeHandler extends BaseTypeHandler<JSONObject>{

private static final PGobject pgObject = new PGobject();

//设置非空参数

@Override

public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {

pgObject.setType("json");

pgObject.setValue(JSONUtils.toJSONString(parameter));

ps.setObject(i, pgObject);

}

//根据列名，获取可以为空的结果

@Override

public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {

String sqlJson = rs.getString(columnName);

if (null != sqlJson){

return JSONObject.parseObject(sqlJson);

}

return null;

}

//根据列索引，获取可以为空的结果

@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;

}

package com.mpper.model;

import com.alibaba.fastjson.JSONObject;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;


import javax.persistence.Id;
import javax.persistence.Table;


@Data
@Slf4j
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "usertable")
public class User {
    @Id
    private Long id;
    private JSONObject info;
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

package com.mpper.model;

import com.alibaba.fastjson.JSONObject;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import lombok.extern.slf4j.Slf4j;

import javax.persistence.Id;

import javax.persistence.Table;

@Data

@Slf4j

@AllArgsConstructor

@NoArgsConstructor

@Table(name = "usertable")

public class User {

@Id

private Long id;

private JSONObject info;

}

package com.mpper.dao;

import com.alibaba.fastjson.JSONObject;
import com.mpper.configer.MyMapper;
import com.mpper.model.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper extends MyMapper<User> {

    List<User> selectAllUser();
    void insertJsonB(User user);

    Object selectDataById(Integer id);

    List<User> selectUserByAgeRange(@Param("ageStart") Integer ageStart, @Param("ageEnd") Integer ageEnd);

    // Param 绑定数据
    void updateById(@Param("id") Integer id,@Param("info") JSONObject info);
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

package com.mpper.dao;

import com.alibaba.fastjson.JSONObject;

import com.mpper.configer.MyMapper;

import com.mpper.model.User;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper extends MyMapper<User> {

List<User> selectAllUser();

void insertJsonB(User user);

Object selectDataById(Integer id);

List<User> selectUserByAgeRange(@Param("ageStart") Integer ageStart, @Param("ageEnd") Integer ageEnd);

// Param 绑定数据

void updateById(@Param("id") Integer id,@Param("info") JSONObject info);

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mpper.dao.UserMapper">
    <!-- 插入 记得在 json 字段加入typeHandler=com.mpper.configer.JsonTypeHandler
     JsonTypeHandler 是咱们刚刚定义的 类
    -->
    <insert id="insertJsonB" parameterType="com.mpper.model.User">
        insert into usertable(id,info)
        values (${id},#{info,typeHandler=com.mpper.configer.JsonTypeHandler})
    </insert>



   <!-- 查询 一定要写个resultMap 不然获取的 json的数据为空 
    -->
   <resultMap id="userResultMap" type="com.mpper.model.User">
        <id property="id" column="id"/>
        <result property="info" column="info" typeHandler="com.mpper.configer.JsonTypeHandler"/>
    </resultMap>

    <select id="selectAllUser" resultMap="userResultMap">
        select * from usertable where  (info <![CDATA[ #>>]]> '{age}')::int between 0 and 1000;
    </select>

    <resultMap id="selectDataByIdResultMap" type="com.mpper.model.User">
        <id property="id" column="id"/>
        <result property="info" column="info" typeHandler="com.mpper.configer.JsonTypeHandler"/>
    </resultMap>

    <select id="selectDataById" parameterType="integer" resultMap="selectDataByIdResultMap">
        select id,info from usertable where id=#{id};
    </select>
    <resultMap id="selectUserByAgeRangeResultMap" type="com.mpper.model.User">
        <id property="id" column="id"/>
        <result property="info" column="info" typeHandler="com.mpper.configer.JsonTypeHandler"/>
    </resultMap>


    <select id="selectUserByAgeRange" resultMap="selectUserByAgeRangeResultMap">

        select * from usertable where  (info <![CDATA[ #>>]]> '{age}')::int between #{ageStart} and #{ageEnd};

    </select>
    <!-- 插入 记得在 json 字段加入typeHandler=com.mpper.configer.JsonTypeHandler
     JsonTypeHandler 是咱们刚刚定义的 类
    -->
    <update id="updateById">
        update usertable set info = #{info,typeHandler=com.mpper.configer.JsonTypeHandler} where id = #{id};
    </update>

</mapper>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- 插入记得在 json 字段加入typeHandler=com.mpper.configer.JsonTypeHandler

JsonTypeHandler 是咱们刚刚定义的类

-->

insert into usertable(id,info)

values (${id},#{info,typeHandler=com.mpper.configer.JsonTypeHandler})

</insert>

<!-- 查询一定要写个resultMap 不然获取的 json的数据为空

-->

</resultMap>

select * from usertable where (info <![CDATA[ #>>]]> '{age}')::int between 0 and 1000;

</select>

</resultMap>

select id,info from usertable where id=#{id};

</select>

</resultMap>

select * from usertable where (info <![CDATA[ #>>]]> '{age}')::int between #{ageStart} and #{ageEnd};

</select>

<!-- 插入记得在 json 字段加入typeHandler=com.mpper.configer.JsonTypeHandler

JsonTypeHandler 是咱们刚刚定义的类

-->

update usertable set info = #{info,typeHandler=com.mpper.configer.JsonTypeHandler} where id = #{id};

</update>

</mapper>

spring:
  application:
    name: mmper
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://127.0.0.1:5432/xxx
    username: postgres
    password: postgres

1

2

3

4

5

6

7

8

9

10

spring:

application:

name: mmper

datasource:

type: com.alibaba.druid.pool.DruidDataSource

driver-class-name: org.postgresql.Driver

url: jdbc:postgresql://127.0.0.1:5432/xxx

username: postgres

password: postgres

Spring Boot Tk Mybatis PostgreSQL 整合实现 json 增删改查

项目需求

项目目录

自定义JsonTypeHandler

定义实体

数据库结构如下：

定义 DAO 层

定义 mapper

数据库相关信息

查询结果如下：

PostgreSQL　Jsonb　between　查询

Windows 10 启动 PostgreSQL 服务

发表评论点击这里取消回复。

Spring Boot Tk Mybatis PostgreSQL 整合 实现 json 增删改查

项目需求

项目目录

自定义JsonTypeHandler

定义实体

数据库结构如下：

定义 DAO 层

定义 mapper

数据库相关信息

查询结果如下：

PostgreSQL Jsonb between 查询

Windows 10 启动 PostgreSQL 服务

猜你喜欢

Spring定义bean的三种方式和五种自动装配

PostgreSQL 基础教程

给 Spring Boot 增加一个 拦截器

Spring boot 增加 Redis 缓存

centos 安装 PostgreSQL 详细教程

Java 中 MongoDB 分页查询的总结_[转]12498158的技术博客_51CTO博客

发表评论 点击这里取消回复。

关注我们的公众号

Spring Boot Tk Mybatis PostgreSQL 整合实现 json 增删改查

PostgreSQL　Jsonb　between　查询

给 Spring Boot 增加一个拦截器

发表评论点击这里取消回复。