前言
本文继续学习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的方法,遇到问题解决问题,然后解决了是非常好的一种提升技能的方式。 这里不知道有没有更简单的方法,要是有就好了,省得写这么多自定义的类型。
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |