postgresql-jpa常见异常汇总

问题1:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
  建议：No operator matches the given name and argument types. You might need to add explicit type casts.
  位置：155
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

问题原因：是因为postgres支持uuid类型的数据(使用uuid需要安装uuid-ossp插件，查看已有插件SELECT * FROM pg_extension)，对于uuid的类型的数据，代码里面是通过String类型接受的，这种情况下ResultSet是无法自动转化的。

解决方法：在连接串后面加上stringtype=unspecified

jdbc:postgresql://127.0.0.1:59948/postgres?stringtype=unspecified

问题2: postgres、mysql联合主键，使用jpa查询

Caused by: java.lang.IllegalArgumentException: This class [class cn.test.internal.beans.KvLatest] does not define an IdClass
    at org.hibernate.metamodel.model.domain.internal.AbstractIdentifiableType.getIdClassAttributes(AbstractIdentifiableType.java:194)
    at org.springframework.data.jpa.repository.support.JpaMetamodelEntityInformation$IdMetadata.<init>(JpaMetamodelEntityInformation.java:272)
    at org.springframework.data.jpa.repository.support.JpaMetamodelEntityInformation.<init>(JpaMetamodelEntityInformation.java:89)
    at org.springframework.data.jpa.repository.support.JpaEntityInformationSupport.getEntityInformation(JpaEntityInformationSupport.java:66)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getEntityInformation(JpaRepositoryFactory.java:229)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getTargetRepository(JpaRepositoryFactory.java:179)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getTargetRepository(JpaRepositoryFactory.java:162)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getTargetRepository(JpaRepositoryFactory.java:72)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:310)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$4(RepositoryFactoryBeanSupport.java:294)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:211)
    at org.springframework.data.util.Lazy.get(Lazy.java:95)

create table ts_test_latest
(
    entity_id uuid    not null,
    key       integer not null,
    ts        bigint  not null,
    bool_v    boolean,
    str_v     varchar(10000000),
    long_v    bigint,
    dbl_v     double precision,
    json_v    json,
    constraint ts_test_latest_pkey
        primary key (entity_id, key)
);

需要创建单独的联合主键Class

public class KvIdClass implements Serializable {
    private static final long serialVersionUID = -1L;

    private String entityId;

    private Integer key;

    public KvIdClass(){

    }

    public KvIdClass(String entityId, Integer key) {
        this.entityId = entityId;
        this.key = key;
    }

    public String getEntityId() {
        return entityId;
    }

    public void setEntityId(String entityId) {
        this.entityId = entityId;
    }

    public Integer getKey() {
        return key;
    }

    public void setKey(Integer key) {
        this.key = key;
    }
}

然后在表对应的实体类上指定主键

@Entity
@IdClass(KvIdClass.class)//指定主键
@Table(name = KV_LATEST_TABLE_NAME)//指定表名
public class KvLatest implements Serializable {
    private static final long serialVersionUID = 7897471972976228950L;

    @Id
    private String entityId;

    @Id
    private Integer key;

    @Column(name = "long_v")
    private Long longV;

    private Long ts;

    public KvLatest(){

    }

    public KvLatest(String entityId, Integer key, Long longV) {
        this.entityId = entityId;
        this.key = key;
        this.longV = longV;
    }

    public String getEntityId() {
        return entityId;
    }

    public void setEntityId(String entityId) {
        this.entityId = entityId;
    }

    public Integer getKey() {
        return key;
    }

    public void setKey(Integer key) {
        this.key = key;
    }

    public long getLongV() {
        return longV;
    }

    public void setLongV(Long longV) {
        this.longV = longV;
    }

    public long getTs() {
        return ts;
    }

    public void setTs(Long ts) {
        this.ts = ts;
    }
}

集成org.springframework.data.jpa.repository.JpaRepository时，指定对应的实体类，

例如：public interface JpaRepository<表对应的实体类, 联合主键实体类> 

public interface KvLatestRepository extends JpaRepository<KvLatest, KvIdClass> {
    @Query("SELECT new KvLatest(d.entityId,d.key,d.longV) " +
            " FROM KvLatest d " +
            " where d.entityId = :deviceId and d.key=:key and d.ts > :time")
    KvLatest findKvLatest(@Param("deviceId") String deviceId,
                          @Param("key") Integer key,
                          @Param("time") Long time);
}

问题3: postgres修改时区，系统默认是UTC

set time zone 'PRC';
show timezone;

持续更新....