The solution of using mybatis and PostgreSQL Json fields as query criteria

Keywords: Java JSON PostgreSQL Database Mybatis

Date:2019-11-15

Think before reading:

You didn't think of a solution?

Is PostgreSQL database supported by itself or is there another solution?

Note: first of all, the reason why the database uses the Json data type is that when we do an app push business, the backend pushes several fields to the app We use jsonb data type directly here.

Using push technology: Aurora push

Now it's business,

As follows:

"rule":{
    "tags": {
        "target": "logon"
    },
    "time": "2019-11-15 10:00:00",
    "method": "scheduled",
    "source": "backend",
    "aliases": [],
    "sendType": "tag",
    "registrationIds": []
}

Now I want to get the sendType="tag" in the rule field. What's the matter now?

The code is as follows:

  #####Mybatis#####
  <select id="selectPushDataList" resultMap="BaseResultMap" parameterType="com.jpc.JpushData" >
    SELECT
      <include refid="Base_Column_List" />
    FROM
        jp_push jpt
    <where>
      delete_flag=1
      and (rule::json->>'sendType')::text = 'tag'
      <if test="name != null and name !=''" >
        and name =#{name,jdbcType=VARCHAR}
      </if>
      ORDER BY
        create_time ASC
         LIMIT ${pageSize} OFFSET ${(currentPage - 1) * pageSize}
    </where>
  </select>
  
  
  ####PostgreSQL  SQL####
      SELECT
        uuid,
        create_user,
        create_time,
        update_user,
        update_time,
        delete_flag,
        NAME,
        type,
        push,
        rule,
    STATUS 
    FROM
        jp_push jpt 
    WHERE
        jpt.delete_flag = 1 
        AND ( jpct.rule :: json ->> 'sendType' ) :: text = 'tag' 
        AND jpt.NAME = 'testname' 
    ORDER BY
        jpt.create_time ASC 
    LIMIT 20 OFFSET 0

The results are as follows:

Programmer Group

The solution of using mybatis and PostgreSQL Json fields as query criteria

Hot Keywords