怎么利用Mybatis向PostgreSQL中插入并查詢JSON字段

這篇文章主要介紹“怎么利用Mybatis向PostgreSQL中插入并查詢JSON字段”，在日常操作中，相信很多人在怎么利用Mybatis向PostgreSQL中插入并查詢JSON字段問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么利用Mybatis向PostgreSQL中插入并查詢JSON字段”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

應用場景介紹

將TCP發過來的數據包（通過消息隊列發過來）解析出數據（一個數據包含有多幀，一幀中含有多條信息），并和本地規則表的格式對應起來。以JsonLineMsg實體類代表對應的一幀數據：

package tsdb.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.sql.Timestamp;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class JsonLineMsg {
    private Timestamp timeStamp; // 時間戳

    private String keyAndRuleData; // key value,key為根據規則表生成的唯一標識，value為TCP解析出的對應的數據。這個字段對應數據庫中的Json類型字段，String類型進入數據庫還需轉換為Json格式。
}

對應psql的表結構為：

上面JsonLineMsg實體類的一個對象就代表的一幀中的所有數據項many（key:value），keyAndRuleData字段用來存儲所有數據項，在psql中對應一個類型為json（或jsonb）的字段。

數據insert

為了查詢JSON中的字段，在insert的過程中有些注意事項，如果插入時JSON格式不正確，查詢JSON字段是總返回null。

記錄一下：為了降低數據庫打開關閉的耗時，每積累20幀持久化一次。

note:

• foreach批量插入 、 mybatis ExecutorType.BATCH模式插入 、 for循環insert

• 其實實際意義上來說，包括在程序里面for循環還是在sql里面for循環都不算是批量操作。只有將ExecutorType設置為BATCH模式才是真正意義上的批量操作。 并且事實證明在sql循環時設置batch與否其實執行時間差別不是很大，幾乎可以忽略不計。所以其實如果不是特別要求性能。可以直接在sql中使用for循環即可 。謹慎使用batch，如果需要使用batch，請在需要的函數上面設置batch，不要全局使用。因為batch也是有副作用的。 比如在Insert操作時，在事務沒有提交之前，是沒有辦法獲取到自增的id，；此外，對于update、delete無法返回更新、插入條數。這在某型情形下是不符合業務要求的。上面的是搬運的，不過后來有看了看，還是應該用BATCH的Executor來批量導入，實際項目中foreach不可控，指不定啥時候就報錯了，文章最后記錄了ExecutorType為BATCH寫法的關鍵部分） foreach的xml拼接sql是最不推薦的方式，使用時有大段的xml和sql語句要寫，很容易出錯，工作效率很低。更關鍵點是，雖然效率尚可，但是真正需要效率的時候你掛了，要你何用？ 批處理執行是有大數據量插入時推薦的做法，使用起來也比較方便。

• 關于批處理的方式的具體說明，可以參考推文強烈推薦MyBatis 三種批量插入方式的比較或者去StackOverFlow查一下，講解的比較全面，總之，還是用ExecutorType為BATCH寫法比較靠譜。

一幀中包含多條信息，一條信息對應一個key:value，所以每次從規則表生成的key和TCP解析出的value都要加到一個代表一幀所有數據的JSON串中。

要注意的代碼如下：

                 // 存儲一幀的所有key:value
                StringBuilder json = new StringBuilder();
                json.append("{");
                // frmLen 幀中信息個數
                for (int j = 0; j < frmLen; j++) {
                    StatRule stat = frm.getStat(j);
                    assert stat != null;
                    // 一條stat的key和value
                    int key = stat.getKey();
                    long value = System.nanoTime();
//                    String value = ParseStat.Parse(datas, stat);
                    json.append("\"");  // key左右必須加引號，key必為String類型
                    json.append(key);
                    json.append("\"");
                    json.append(":");
//                    json.append("\"");
                    json.append(value); // value左右不是必須加引號，若是String則加
//                    json.append("\"");
                    if ((j != statLen - 1)) {
                        json.append(",");
                    }
                }
                json.append("}");
                JsonLineMsg jsonLineMsg = new JsonLineMsg(new Timestamp(System.currentTimeMillis()), json.toString());

要注意的就是這個key和value加入數據庫的類型如果為text(即java字符串)就要加引號，所以key兩頭必須加，value看情況。

對應的XML中的語句：

    <insert id="batchInsertJsonLineMsg"
            useGeneratedKeys="true" >
        insert into jsonlinemsg (timestamp ,keyandruledata ) values
        <foreach item="item" collection="list" separator="," close=";">
            (#{item.timeStamp},(#{item.keyAndRuleData})::json)
        </foreach>
    </insert>

這個::json就是將非json類型轉為json類型，否則JAVA中String類型會對應其他的數據庫字段類型，插入會報錯。

note: psql 4種類型轉換 https://www.postgresql.org/docs/14/sql-syntax-lexical.html

• type 'string' 只能用于字面常量轉換、且不能用于數組中

• typename ( 'string' ) 可用于運行時類型轉換

• 'string'::type 可用于數組，可用于運行時類型轉換

• CAST ( 'string' AS type ) 可用于數組，可用于運行時類型轉換

插入后用Navicat查看：

如果查看到類似于 "{"1":"1_234"}"、{\"1\":\"1_123\"}這樣，格式就是不正確的，查詢JSON中字段會返回null。

數據select

  <select id="selectValueData" resultType="String">
        select keyandruledata::json ->>#{key}  from jsonlinemsg where timestamp = (#{time}::timestamp)
    </select>

要注意的就是這個::json，至于 -> 還是 ->>可以參考開頭的官網鏈接。

ps: timescaledb官網推薦用jsonb，但是我測試發現jsonb查詢插入都比不上json，不知道為啥
ps: 發現了，原來是轉換為tsdb時，索引沒建立起來，重新建表又測試了一遍，確實jsonb讀取快。

BATCH 批量插入

// 獲取連接的方法，設置ExecutorType.BATCH以及關閉自動提交
    public static SqlSession getSessionForBatch(String xmlPath, Properties properties) throws IOException {
        return MybatisUtil.getSqlSessionFactory(xmlPath, properties).openSession(ExecutorType.BATCH,false);
    }

    public void update(List<PropUrl> propUrlLst) throws IOException {
        // ExecutorType.BATCH
        try (SqlSession session = MybatisUtil.getSessionForBatch(myBatisConfigXmlPath)) {
            InitTsdbUrlTableMapper mapper = (InitTsdbUrlTableMapper) session.getMapper(mapperClazz);

            for (int i = 0; i < propUrlLst.size(); i++) {
                mapper.updatePropMatchRule(propUrlLst.get(i));
                // 每50次提交一次防止內存溢出
                if ((i+1) % 50 == 0) {
                    session.commit();
                    session.clearCache();
                }
            }
            session.commit();
            session.clearCache();

            log.info("update successfully ->{}", propUrlLst);
        }
    }

到此，關于“怎么利用Mybatis向PostgreSQL中插入并查詢JSON字段”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！