ToB企服应用市场:ToB评测及商务社交产业平台

标题: Spring 保存带Array字段的记录到PostgreSQL [打印本页]
作者: 铁佛    时间: 2023-2-24 19:17
标题: Spring 保存带Array字段的记录到PostgreSQL
前言

本文继续学习PostgreSQL,  看到PostgreSQL有个Array字段，感觉可以用来存储某种表，比如股票每天的价格， 我们称为market_price表，先来看下最开始market_price 表的定义
  1. create table market_price(
  2. id char(10),
  3. trade_date  date,
  4. open float ,
  5. high float,
  6. low float,
  7. close float,
  8. primary key (id,trade_date)
  9. );
复制代码
表说明

id 每支股票有个对应的编号，
trade_date是交易日期，
open high low close分别代表开盘价，最高价，最低价，收盘价。
这样定义表结构，我们要查询某天的价格非常方便，给定id和日期就能查出来，但是有个问题就是存到postgreSQL后， 记录会非常多，假设全球有10万只股票，我们存储从1990到今天的数据，那么中间的日期数量就是每支股票有大概12000条记录。总记录数就是有12亿条记录，对于关系型数据库数据上亿后，查询性能会下降比较明显， 有什么办法可以把记录数减少一些呢？ 我们可以尝试一下Array来存储下， 看这样的表结构
  1. create table market_price_month_array(
  2. id char(10),
  3. year smallint,
  4. month smallint,
  5. open float array[31],
  6. high float array[31],
  7. low float array[31],
  8. close float array[31]
  9. primary key (id,year,month)
  10. );
复制代码
我们这里使用了Array，把每个月的数据存成1行，每个月都按31天算，open[1]就表示第一天, open[2] 就表示第2天， 这样数据行数能减少30倍，12亿行变成4千万行，查询性能会好很多。
下面是存入和更新的例子
  1. 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}');
  2. INSERT 0 1
  3. postgres=# select * from market_price_month_array;
  4. 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.
  5. 13,3.14,3.15,3.16,3.17,3.18,3.19}
  6. (1 row)
  9. 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;
  10. UPDATE 1
  11. postgres=# select * from market_price_month_array;
  12. 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,
  13. 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
  14. 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
这里直接给代码
  1. package ken.postgresql.poc;
  3. import lombok.AllArgsConstructor;
  4. import lombok.Builder;
  5. import lombok.Data;
  6. import lombok.NoArgsConstructor;
  7. import org.hibernate.annotations.Type;
  9. import javax.persistence.Column;
  10. import javax.persistence.EmbeddedId;
  11. import javax.persistence.Entity;
  12. import javax.persistence.Table;
  14. @Entity
  15. @Table(name = "market_price_month_array")
  16. @Data
  17. @Builder
  18. @NoArgsConstructor
  19. @AllArgsConstructor
  20. public class MarketPriceMonth {
  21.     @EmbeddedId
  22.     private MarketPriceMonthKey id;
  24.     @Column(columnDefinition = "float[]")
  25.     @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
  26.     private Double[] open;
  28.     @Column(columnDefinition = "float[]")
  29.     @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
  30.     private Double[] high;
  32.     @Column(columnDefinition = "float[]")
  33.     @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
  34.     private Double[] low;
  36.     @Column(columnDefinition = "float[]")
  37.     @Type(type = "ken.postgresql.poc.arraymapping.CustomDoubleArrayType")
  38.     private Double[] close;
  40. }
复制代码
自定义CustomDoubleArrayType代码
  1. package ken.postgresql.poc.arraymapping;
  3. import org.hibernate.HibernateException;
  4. import org.hibernate.engine.spi.SharedSessionContractImplementor;
  5. import org.hibernate.usertype.UserType;
  7. import java.io.Serializable;
  8. import java.sql.Array;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.sql.Types;
  13. import java.util.Arrays;
  15. public class CustomDoubleArrayType implements UserType {
  17.     @Override
  18.     public int[] sqlTypes() {
  19.         return new int[]{Types.ARRAY};
  20.     }
  22.     @Override
  23.     public Class returnedClass() {
  24.         return Double[].class;
  25.     }
  27.     @Override
  28.     public boolean equals(Object x, Object y) throws HibernateException {
  29.         if (x instanceof Double[] && y instanceof Double[]) {
  30.             return Arrays.deepEquals((Double[])x, (Double[])y);
  31.         } else {
  32.             return false;
  33.         }
  34.     }
  36.     @Override
  37.     public int hashCode(Object x) throws HibernateException {
  38.         return Arrays.hashCode((Double[])x);
  39.     }
  41.     @Override
  42.     public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner)
  43.             throws HibernateException, SQLException {
  44.         Array array = rs.getArray(names[0]);
  45.         return array != null ? array.getArray() : null;
  46.     }
  48.     @Override
  49.     public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
  50.             throws HibernateException, SQLException {
  51.         if (value != null && st != null) {
  52.             Array array = session.connection().createArrayOf("float", (Double[])value);
  53.             st.setArray(index, array);
  54.         } else {
  55.             st.setNull(index, sqlTypes()[0]);
  56.         }
  57.     }
  59.     @Override
  60.     public Object deepCopy(Object value) throws HibernateException {
  61.         Double[] a = (Double[]) value;
  62.         return Arrays.copyOf(a, a.length);
  63.     }
  65.     @Override
  66.     public boolean isMutable() {
  67.         return false;
  68.     }
  70.     @Override
  71.     public Serializable disassemble(Object value) throws HibernateException {
  72.         return (Serializable) value;
  73.     }
  75.     @Override
  76.     public Object assemble(Serializable cached, Object owner) throws HibernateException {
  77.         return cached;
  78.     }
  80.     @Override
  81.     public Object replace(Object original, Object target, Object owner) throws HibernateException {
  82.         return original;
  83.     }
  85. }
复制代码
定义接口后就可以直接使用了
  1. public interface MarketPriceMonthRepository  extends JpaRepository<MarketPriceMonth, MarketPriceMonthKey> {
  2. }
复制代码
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表达式,
  1. (PreparedStatement ps, MarketPriceMonth marketPriceMonth) -> {
  2. #这里给ps设置值
  3. };
复制代码
PreparedStatement 有个ps.setArray(int parameterIndex, Array x)方法，
我们需要做得就是创建一个Array。
有一个方法创建方法是这样的, 调用connection的方法来create array
  1.     private java.sql.Array createSqlArray(Double[] list){
  2.        java.sql.Array intArray = null;
  3.        try {
  4.            intArray = jdbcTemplate.getDataSource().getConnection().createArrayOf("float", list);
  5.        } catch (SQLException ignore) {
  6.            log.error("meet error",ignore);
  7.        }
  8.        return intArray;
  9.    }
复制代码
但是我使用的时候，这个方法很慢，没有成功，感觉不行。
后来换成了自定义一个继承java.sql.Array的类来转换数组。
  1. package ken.postgresql.poc.repostory;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.sql.Types;
  6. import java.util.Arrays;
  7. import java.util.Map;
  9. public class PostgreSQLDoubleArray implements java.sql.Array {
  11.    private final Double[] doubleArray;
  12.    private final String stringValue;
  14.    public PostgreSQLDoubleArray(Double[] intArray) {
  15.        this.doubleArray = intArray;
  16.        this.stringValue = intArrayToPostgreSQLInt4ArrayString(intArray);
  17.    }
  19.    public String toString() {
  20.        return stringValue;
  21.    }
  23.    /**
  24.     * This static method can be used to convert an integer array to string representation of PostgreSQL integer array.
  25.     *
  26.     * @param a source integer array
  27.     * @return string representation of a given integer array
  28.     */
  29.    public static String intArrayToPostgreSQLInt4ArrayString(Double[] a) {
  30.        if (a == null) {
  31.            return "NULL";
  32.        }
  33.        final int al = a.length;
  34.        if (al == 0) {
  35.            return "{}";
  36.        }
  37.        StringBuilder sb = new StringBuilder(); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
  38.        sb.append('{');
  39.        for (int i = 0; i < al; i++) {
  40.            if (i > 0) sb.append(',');
  41.            sb.append(a[i]);
  42.        }
  43.        sb.append('}');
  44.        return sb.toString();
  45.    }
  47.    @Override
  48.    public Object getArray() throws SQLException {
  49.        return doubleArray == null ? null : Arrays.copyOf(doubleArray, doubleArray.length);
  50.    }
  52.    @Override
  53.    public Object getArray(Map<String, Class<?>> map) throws SQLException {
  54.        return getArray();
  55.    }
  57.    public Object getArray(long index, int count) throws SQLException {
  58.        return doubleArray == null ? null : Arrays.copyOfRange(doubleArray, (int) index, (int) index + count);
  59.    }
  61.    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
  62.        return getArray(index, count);
  63.    }
  65.    public int getBaseType() throws SQLException {
  66.        return Types.DOUBLE;
  67.    }
  69.    public String getBaseTypeName() throws SQLException {
  70.        return "float";
  71.    }
  73.    public ResultSet getResultSet() throws SQLException {
  74.        throw new UnsupportedOperationException();
  75.    }
  77.    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
  78.        throw new UnsupportedOperationException();
  79.    }
  81.    public ResultSet getResultSet(long index, int count) throws SQLException {
  82.        throw new UnsupportedOperationException();
  83.    }
  85.    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
  86.        throw new UnsupportedOperationException();
  87.    }
  89.    public void free() throws SQLException {
  90.    }
  91. }
复制代码
就是把数组拼成string后传进去。
这样调用
  1.     public void saveAll(List<MarketPriceMonth> marketPriceMonthList)
  2.     {
  3.         this.jdbcTemplate.batchUpdate("INSERT INTO market_price_month_array (id, year, month, open, high, low, close) VALUES (?,?,?,?,?,?,?)",
  4.                 marketPriceMonthList,
  5.                 100,
  6.                 (PreparedStatement ps, MarketPriceMonth marketPriceMonth) -> {
  7.                     MarketPriceMonthKey key = marketPriceMonth.getId();
  8.                     ps.setString(1, key.getId());
  9.                     ps.setInt(2,  key.getYear());
  10.                     ps.setInt(3,  key.getMonth());
  11.                     ps.setArray(4, new PostgreSQLDoubleArray(marketPriceMonth.getOpen()));
  12.                     ps.setArray(5, new PostgreSQLDoubleArray(marketPriceMonth.getHigh()));
  13.                     ps.setArray(6, new PostgreSQLDoubleArray(marketPriceMonth.getLow()));
  14.                     ps.setArray(7, new PostgreSQLDoubleArray(marketPriceMonth.getClose()));
  15.                 });
  16.     }
复制代码
我也尝试过直接用String, 然后自己拼接这个string，没有成功，报类型转换错误！
  1. ps.setString(1, createArrayString(marketPriceMonth.getOpen()));
  3.     private String createArrayString(Double[] list)
  4.     {
  5.         StringBuilder stringBuilder = new StringBuilder();
  6.         for (Double d:list
  7.              ) {
  8.             if (stringBuilder.length() != 0)
  9.             {
  10.                 stringBuilder.append(",");
  11.             }
  12.             stringBuilder.append(d!=null?d.toString():"null");
  13.         }
  15.         return stringBuilder.toString();
  16.     }
复制代码
使用batch后，本机测试，性能提升非常明显。
总结

这篇文章是我这周解决问题的一个记录，看起来非常简单，但是也花了我一些时间，找到了一个可以快速保存Array类型数据到postgresql的方法，遇到问题解决问题，然后解决了是非常好的一种提升技能的方式。 这里不知道有没有更简单的方法，要是有就好了，省得写这么多自定义的类型。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4