Spring 保存帶Array欄位的記錄到PostgreSQL

来源:https://www.cnblogs.com/dk168/archive/2023/02/24/17152761.html
-Advertisement-
Play Games

字元串 字元集用來做什麼 字元集是為每個字元分配一個唯一的ID 在同一個字元集內，字元的ID是唯一的，不同字元集ID可能是不同的 UTF-8是編碼規則或者說是Unicode的一種實現 UTF-8將Unicode中的字元ID以某種方式進行編碼 變長的編碼規則： 1-4位元組，具體規則： 0xxxx表示0 ...


前言

本文繼續學習PostgreSQL, 看到PostgreSQL有個Array欄位，感覺可以用來存儲某種表，比如股票每天的價格， 我們稱為market_price表，先來看下最開始market_price 表的定義

create table market_price(
id char(10),
trade_date  date, 
open float , 
high float, 
low float,
close float,
primary key (id,trade_date)
);

表說明

id 每支股票有個對應的編號，
trade_date是交易日期，
open high low close分別代表開盤價，最高價，最低價，收盤價。

這樣定義表結構，我們要查詢某天的價格非常方便，給定id和日期就能查出來，但是有個問題就是存到postgreSQL後， 記錄會非常多，假設全球有10萬隻股票，我們存儲從1990到今天的數據，那麼中間的日期數量就是每支股票有大概12000條記錄。總記錄數就是有12億條記錄，對於關係型資料庫數據上億後，查詢性能會下降比較明顯， 有什麼辦法可以把記錄數減少一些呢？ 我們可以嘗試一下Array來存儲下， 看這樣的表結構

create table market_price_month_array(
id char(10),
year smallint, 
month smallint,
open float array[31], 
high float array[31], 
low float array[31],
close float array[31]
primary key (id,year,month)
);

我們這裡使用了Array，把每個月的數據存成1行，每個月都按31天算，open[1]就表示第一天, open[2] 就表示第2天， 這樣數據行數能減少30倍，12億行變成4千萬行，查詢性能會好很多。
下麵是存入和更新的例子

postgres=# insert into market_price_month_array values('0P00000001',2023,2,'{2.11,2.12,2.13,2.14,2.15,2.16,2.17,2.18,2.19}','{4.11,4.12,4.13,4.14,4.15,4.16,4.17,4.18,4.19}','{1.11,1.12,1.13,1.14,1.15,1.16,1.17,1.18,1.19}','{3.11,3.12,3.13,3.14,3.15,3.16,3.17,3.18,3.19}');
INSERT 0 1
postgres=# select * from market_price_month_array;
 0P00000001 | 2023 |     2 | {2.11,2.12,2.13,2.14,2.15,2.16,2.17,2.18,2.19} | {4.11,4.12,4.13,4.14,4.15,4.16,4.17,4.18,4.19} | {1.11,1.12,1.13,1.14,1.15,1.16,1.17,1.18,1.19} | {3.11,3.12,3.
13,3.14,3.15,3.16,3.17,3.18,3.19}
(1 row)


postgres=# update market_price_month_array set open[19] = 2.19, high[19] = 4.19, low[19]= 1.19, close[19]=3.19 where id = '0P00000001' and year = 2023 and month = 2;
UPDATE 1
postgres=# select * from market_price_month_array;
 0P00000001 | 2023 |     2 | {2.11,2.12,2.13,2.14,2.15,2.16,2.17,NULL,2.19,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2.19} | {4.11,4.12,4.13,4.14,4.15,4.16,4.17,NULL,4.19,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,4.19} | {1.11,1.12,1.13,1.14,1.15,1.16,1.17,NULL,1.19,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1.19} | {3.11,3.12,3.13,3.14,3.15,3.16,3.17,NULL,3.19,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3.19}

插入的時候，值用“'{2.11,2.12,2.13,2.14,2.15,2.16,2.17,2.18,2.19}'”, 沒有的日期就自動設置為NULL了。
想更新哪一天的，就直接用close[19]=3.19， 使用非常方便。

那麼我們想要用Java來進行插入數據應該怎麼做呢？ 是不是和其他非數組的類型一樣的用法吶？當然是有些不一樣的，下麵部分就是如何使用Spring來保存Array類型。

JPA 方式保存

JPA方式是我們存入資料庫的時候最方便的方式，定義個entity, 然後定義個介面就能幹活了。
但是這裡直接在Entity裡面這樣定義Double[] open是不行的，需要加一個類型轉化，我參考了這篇文章https://www.baeldung.com/java-hibernate-map-postgresql-array
這裡直接給代碼

package ken.postgresql.poc;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table(name = "market_price_month_array")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class MarketPriceMonth {
    @EmbeddedId
    private MarketPriceMonthKey id;

    @Column(columnDefinition = "float[]")
    @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
    private Double[] open;

    @Column(columnDefinition = "float[]")
    @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
    private Double[] high;

    @Column(columnDefinition = "float[]")
    @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
    private Double[] low;

    @Column(columnDefinition = "float[]")
    @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
    private Double[] close;

}

自定義CustomDoubleArrayType代碼

package ken.postgresql.poc.arraymapping;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.Array;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;

public class CustomDoubleArrayType implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.ARRAY};
    }

    @Override
    public Class returnedClass() {
        return Double[].class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x instanceof Double[] && y instanceof Double[]) {
            return Arrays.deepEquals((Double[])x, (Double[])y);
        } else {
            return false;
        }
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return Arrays.hashCode((Double[])x);
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner)
            throws HibernateException, SQLException {
        Array array = rs.getArray(names[0]);
        return array != null ? array.getArray() : null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        if (value != null && st != null) {
            Array array = session.connection().createArrayOf("float", (Double[])value);
            st.setArray(index, array);
        } else {
            st.setNull(index, sqlTypes()[0]);
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        Double[] a = (Double[]) value;
        return Arrays.copyOf(a, a.length);
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return original;
    }

}

定義介面後就可以直接使用了

public interface MarketPriceMonthRepository  extends JpaRepository<MarketPriceMonth, MarketPriceMonthKey> {
}

jdbcTemplate Batch保存

上面的方法一條條保存沒有問題，但是當數據量大的時候，比如我們批量把數據導入的時候，一條條保存就很不給力了，我們需要用batch方法， 這裡有一篇batch和不用batch對比性能的文章https://www.baeldung.com/spring-jdbc-batch-inserts
使用jdbcTemplate.batchUpdate 方法來批量保存
batchUpdate 有四個參數
batchUpdate(
String sql,
Collection batchArgs,
int batchSize,
ParameterizedPreparedStatementSetter pss)

batchArgs 是我們需要保存的數據
batchSize 是我們一次保存多少條，可以自動幫我們把batchArgs裡面的數據分次保存
pss 是一個FunctionalInterface，可以接受Lambda表達式,

(PreparedStatement ps, MarketPriceMonth marketPriceMonth) -> {
#這裡給ps設置值
};

PreparedStatement 有個ps.setArray(int parameterIndex, Array x)方法，
我們需要做得就是創建一個Array。
有一個方法創建方法是這樣的, 調用connection的方法來create array

    private java.sql.Array createSqlArray(Double[] list){
       java.sql.Array intArray = null;
       try {
           intArray = jdbcTemplate.getDataSource().getConnection().createArrayOf("float", list);
       } catch (SQLException ignore) {
           log.error("meet error",ignore);
       }
       return intArray;
   }

但是我使用的時候，這個方法很慢，沒有成功，感覺不行。
後來換成了自定義一個繼承java.sql.Array的類來轉換數組。

package ken.postgresql.poc.repostory;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.Map;

public class PostgreSQLDoubleArray implements java.sql.Array {

   private final Double[] doubleArray;
   private final String stringValue;

   public PostgreSQLDoubleArray(Double[] intArray) {
       this.doubleArray = intArray;
       this.stringValue = intArrayToPostgreSQLInt4ArrayString(intArray);
   }

   public String toString() {
       return stringValue;
   }

   /**
    * This static method can be used to convert an integer array to string representation of PostgreSQL integer array.
    *
    * @param a source integer array
    * @return string representation of a given integer array
    */
   public static String intArrayToPostgreSQLInt4ArrayString(Double[] a) {
       if (a == null) {
           return "NULL";
       }
       final int al = a.length;
       if (al == 0) {
           return "{}";
       }
       StringBuilder sb = new StringBuilder(); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
       sb.append('{');
       for (int i = 0; i < al; i++) {
           if (i > 0) sb.append(',');
           sb.append(a[i]);
       }
       sb.append('}');
       return sb.toString();
   }

   @Override
   public Object getArray() throws SQLException {
       return doubleArray == null ? null : Arrays.copyOf(doubleArray, doubleArray.length);
   }

   @Override
   public Object getArray(Map<String, Class<?>> map) throws SQLException {
       return getArray();
   }

   public Object getArray(long index, int count) throws SQLException {
       return doubleArray == null ? null : Arrays.copyOfRange(doubleArray, (int) index, (int) index + count);
   }

   public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
       return getArray(index, count);
   }

   public int getBaseType() throws SQLException {
       return Types.DOUBLE;
   }

   public String getBaseTypeName() throws SQLException {
       return "float";
   }

   public ResultSet getResultSet() throws SQLException {
       throw new UnsupportedOperationException();
   }

   public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
       throw new UnsupportedOperationException();
   }

   public ResultSet getResultSet(long index, int count) throws SQLException {
       throw new UnsupportedOperationException();
   }

   public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
       throw new UnsupportedOperationException();
   }

   public void free() throws SQLException {
   }
}

就是把數組拼成string後傳進去。
這樣調用

    public void saveAll(List<MarketPriceMonth> marketPriceMonthList)
    {
        this.jdbcTemplate.batchUpdate("INSERT INTO market_price_month_array (id, year, month, open, high, low, close) VALUES (?,?,?,?,?,?,?)",
                marketPriceMonthList,
                100,
                (PreparedStatement ps, MarketPriceMonth marketPriceMonth) -> {
                    MarketPriceMonthKey key = marketPriceMonth.getId();
                    ps.setString(1, key.getId());
                    ps.setInt(2,  key.getYear());
                    ps.setInt(3,  key.getMonth());
                    ps.setArray(4, new PostgreSQLDoubleArray(marketPriceMonth.getOpen()));
                    ps.setArray(5, new PostgreSQLDoubleArray(marketPriceMonth.getHigh()));
                    ps.setArray(6, new PostgreSQLDoubleArray(marketPriceMonth.getLow()));
                    ps.setArray(7, new PostgreSQLDoubleArray(marketPriceMonth.getClose()));
                });
    }

我也嘗試過直接用String, 然後自己拼接這個string，沒有成功，報類型轉換錯誤！

ps.setString(1, createArrayString(marketPriceMonth.getOpen()));

    private String createArrayString(Double[] list)
    {
        StringBuilder stringBuilder = new StringBuilder();
        for (Double d:list
             ) {
            if (stringBuilder.length() != 0)
            {
                stringBuilder.append(",");
            }
            stringBuilder.append(d!=null?d.toString():"null");
        }

        return stringBuilder.toString();
    }

使用batch後，本機測試，性能提升非常明顯。

總結

這篇文章是我這周解決問題的一個記錄，看起來非常簡單，但是也花了我一些時間，找到了一個可以快速保存Array類型數據到postgresql的方法，遇到問題解決問題，然後解決了是非常好的一種提升技能的方式。 這裡不知道有沒有更簡單的方法，要是有就好了，省得寫這麼多自定義的類型。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • c語言以及高級語言中的float到底是什麼以及IEEE754
    對記憶體里float4位元組的好奇 初學電腦都要學那個什麼二進位十進位什麼補碼 反碼那些玩意兒哈，由於最近要做一個單片機往另外一個單片機發數據的需求，直接c語言指針 然後float4位元組傳過去不就得了嗎，麻煩就麻煩在這裡 另一端編程機是個啥鳥lua 麻煩的一逼，integer這種我們就不說了哈因為實在 ...
  • Openfoam UPstream類探索
    前言 上篇文章我們大體捋順了Pstream類，但上篇沒有講到的是Pstream類很多東西是從UPstream類繼承來的 這次我們大體看一下UPstream類，以避免Pstream類內很多繼承來的東西不會用 簡述幾個常用的函數如下： Pstream::myProcNo() //- Number of ...
  • 【多線程與高併發】- 淺談volatile
    volatile是Java語言中的一種輕量級的同步機制，它可以確保共用變數的記憶體可見性，也就是當一個線程修改了共用變數的值時，其他線程能夠立即知道這個修改。 ...
  • day03-自己實現Mybatis底層機制-02
    自己實現Mybatis底層機制-02 7.任務階段4&5 階段4任務：開發Mapper介面和Mapper.xml 階段5任務：開發和Mapper介面相映射的MapperBean （1）Mapper介面 package com.li.mapper; import com.li.entity.Monst ...
  • XXL-JOB適配金倉kingbaseV8R3版本
    1、背景 最近工作中有需要管理定時任務的需求，項目使用了kingbase作為資料庫，所以研究了xxl-job適配kingbase，根據xxl-job作者的版本更新日誌描述，v2.4.0之後官方將支持多資料庫，敬請期待 xxl-job：2.3.1 金倉kingbase：V8R3 已上傳修改之後的版本到 ...
  • 面向對象進階第二天
    面向對象進階第二天 許可權修飾符 作用：約束成員變數，構造器，方法等的訪問範圍 自己定義成員（方法，成員變數，構造器等）一般需要滿足如下要求： 成員變數一般私有。 方法一般公開。 如果該成員只希望本類訪問，使用private修飾。 如果該成員只希望本類，同一個包下的其他類和不同包下的子類訪問，使用pr ...
  • Python爬蟲基礎丨採集知乎問答區內容
    不知道現在還有多少人玩知某乎，我們那時候沒事就刷刷知某乎，貼某吧，不知留下了多少足跡。 知乎上問答的質量挺高，今天咱們就對其問答內容進行爬蟲實驗。 效果展示 可以看到，數據咱們就獲取下來了，保存就可以了。 代碼展示 import requests import re import json impo ...
  • python正則表達式替換所有內容並同時保留找到的內容
    Ansible-playbook 快速入門到放棄 隔岸紅塵忙似火，當軒青嶂冷如冰。 1-簡介 playbook 相當於可以把模塊命令都寫入到配置文件裡面，這樣就可以直接執行配置文件了，類似腳本。 2-playbook 初體驗 編寫test.yml 文件，在serviceA 主機機器上的/opt/tj ...
一周排行
    -Advertisement-
    Play Games
  • GoF之工廠模式
    GoF之工廠模式 @目錄GoF之工廠模式每博一文案1. 簡單說明“23種設計模式”1.2 介紹工廠模式的三種形態1.3 簡單工廠模式（靜態工廠模式）1.3.1 簡單工廠模式的優缺點:1.4 工廠方法模式1.4.1 工廠方法模式的優缺點:1.5 抽象工廠模式1.6 抽象工廠模式的優缺點：2. 總結:3 ...
  • dotnet C# 簡單的追加文件夾到 ZipArchive 壓縮文件的方法
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • Elasticsearch 系列（六）- ES數據同步和ES集群
    本章將和大家分享ES的數據同步方案和ES集群相關知識。廢話不多說，下麵我們直接進入主題。 一、ES數據同步 1、數據同步問題 Elasticsearch中的酒店數據來自於mysql資料庫，因此mysql數據發生改變時，Elasticsearch也必須跟著改變，這個就是Elasticsearch與my ...
  • 自動化測試數據生成：Asp.Net Core單元測試利器AutoFixture詳解
    引言 在我們之前的文章中介紹過使用Bogus生成模擬測試數據，今天來講解一下功能更加強大自動生成測試數據的工具的庫"AutoFixture"。 什麼是AutoFixture? AutoFixture 是一個針對 .NET 的開源庫，旨在最大程度地減少單元測試中的“安排（Arrange）”階段，以提高 ...
  • .NET Emit 入門教程：第七部分：實戰項目1：將 DbDataReader 轉實體
    經過前面幾個部分學習，相信學過的同學已經能夠掌握 .NET Emit 這種中間語言，並能使得它來編寫一些應用，以提高程式的性能。隨著 IL 指令篇的結束，本系列也已經接近尾聲，在這接近結束的最後，會提供幾個可供直接使用的示例，以供大伙分析或使用在項目中。 ...
  • Windows下綁定線程到指定的CPU核心
    當從不同來源導入Excel數據時，可能存在重覆的記錄。為了確保數據的準確性，通常需要刪除這些重覆的行。手動查找並刪除可能會非常耗費時間，而通過編程腳本則可以實現在短時間內處理大量數據。本文將提供一個使用C# 快速查找並刪除Excel重覆項的免費解決方案。 以下是實現步驟： 1. 首先安裝免費.NET ...
  • mORMot 1.18 第07章 簡單的讀寫操作
    C++ 異常處理 C++ 異常處理機制允許程式在運行時處理錯誤或意外情況。它提供了捕獲和處理錯誤的一種結構化方式，使程式更加健壯和可靠。 異常處理的基本概念： 異常: 程式在運行時發生的錯誤或意外情況。 拋出異常: 使用 throw 關鍵字將異常傳遞給調用堆棧。 捕獲異常: 使用 try-catch ...
  • mORMot 1.18 第06章 概念
    優秀且經驗豐富的Java開發人員的特征之一是對API的廣泛瞭解，包括JDK和第三方庫。 我花了很多時間來學習API，尤其是在閱讀了Effective Java 3rd Edition之後 ，Joshua Bloch建議在Java 3rd Edition中使用現有的API進行開發，而不是為常見的東西編 ...
  • php 開發規範
    框架 · 使用laravel框架，原因：tp的框架路由和orm沒有laravel好用 · 使用強制路由，方便介面多時，分多版本，分文件夾等操作 介面 · 介面開發註意欄位類型，欄位是int，查詢成功失敗都要返回int（對接java等強類型語言方便） · 查詢介面用GET、其他用POST 代碼 · 所 ...
  • 2024 年 4 月 28 日 周日 晴 熱（1253 字）
    正文 下午找企業的人去鎮上做貸後。 車上聽同事跟那個司機對罵，火星子都快出來了。司機跟那同事更熟一些，連我在內一共就三個人，同事那一手指桑罵槐給我都聽愣了。司機也是老社會人了，馬上聽出來了，為那個無辜的企業經辦人辯護，實際上是為自己辯護。 “這個事情你不能怪企業。”“但他們總不能讓銀行的人全權負責， ...
所有分類