利用Mybatis向PostgreSQL中插入並查詢JSON欄位

2022-07-22 18:00:25

應用場景介紹

將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欄位的文章就介紹到這了,更多相關Mybatis查詢JSON欄位內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

利用Mybatis向PostgreSQL中插入並查詢JSON欄位

目錄

應用場景介紹

資料insert

資料select

BATCH 批次插入

熱門文章