Spring数据JPA多数据源-蟑螂数据库

Question

我的应用程序有两个数据源。第一个数据源使用JdbcTemplate访问Postgre。第二个数据源使用Spring数据JPA访问CockroachDb。

所以我为这个数据源创建了两种配置。

除了具有空间数据的对象之外，坚持使用这两个数据源工作正常。当我试图保存空间数据到CockroachDb，我得到一个错误：

Hibernate：选择geoobject0_. id为id1_2_0_，geoobject0_.地址为address2_2_0_，geoobject0_.address_id为address_3_2_0_，geoobject0_.创建为created4_2_0_，geoobject0_.flat_count为flat_cou5_2_0_，geoobject0_.geo_provider_id为geo_prov6_2_0_，geoobject0_.几何为geometry7_2_0_，geoobject0_.hq_id为hq_id8_2_0_，geoobject0_is_generated为is_gener9_2_0_，geoobject0_.is_living为is_livi10_2_0_，geoobject0_payload11_2_0_，geoobject0_。source_idsource_12_2_0_，geoobject0_。键入type13_2_0_，geoobject0_。uic_iduic_id14_2_0_，geoobject0_。version_idversion15_2_0_，geoobject0_。voter_countvoter_c16_2_0_geo_service.geo_objectgeoobject0_geoobject0_。Hibernate：插入到geo_service.geo_object（地址，address_id，创建，flat_count，geo_provider_id，几何，hq_id，is_generated，is_living，有效载荷，source_id，类型，uic_id，version_id，voter_count，id）值 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?)2022-01-18 17:01:39.583[ForkJoinPool.common池工人-3]WARNo.h.e.jdbc.spi.SqlExceptionHelper logExceptions（）-SQL错误： 0， SQLState： XXUUU2022-01-18 17:01:39.584[ForkJoinPool.common池工人-3]ERRORo.h.e.jdbc.spi.SqlExceptionHelper logExceptions（）-错误： wkb：未知字节顺序： 10101100

Java配置：

博士后：

@Configuration
public class UicDbConfig {

    @Bean(name = "uicDataSource")
    @Qualifier("uicDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.uic.datasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

对于蟑螂B：

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "ru.stimul.geoservice.repo",
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager")
public class CockroachDbConfig {

    @Autowired
    private Environment env;

    @Bean("primaryDataSource")
    @ConfigurationProperties(prefix="spring.primary.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder
            ,@Qualifier("primaryDataSource") DataSource primaryDataSource){

        LocalContainerEntityManagerFactoryBean em = builder
                .dataSource(primaryDataSource)
                .packages("ru.stimul.geoservice.domain.primary")
                .persistenceUnit("domains")
                .build();

        final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(additionalProperties());

        return em;

    }

    @Bean(name = "primaryTransactionManager")
    public JpaTransactionManager transactionManager(@Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(primaryEntityManagerFactory);

        return transactionManager;
    }


    private Properties additionalProperties() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", env.getProperty("spring.primary.jpa.properties.hibernate.dialect"));
        properties.setProperty("hibernate.show_sql", env.getProperty("spring.primary.jpa.show-sql"));
        return properties;
    }
}

应用yml：

spring:
  application.name: geo-service
  http:
    encoding:
      charset: UTF-8
      enabled: true
  main:
    allow-bean-definition-overriding: true

  primary:
    datasource:
      jdbcUrl: jdbc:postgresql://localhost:26257/geo_service
      driverClassName: org.postgresql.Driver
      username: admin
      password: 
    jpa:
      show-sql: true
      hibernate:
        ddl-auto: none
      properties:
        hibernate:
          dialect: org.hibernate.spatial.dialect.cockroachdb.CockroachDB202SpatialDialect
      generate-ddl: false

  uic:
    datasource:
      jdbc-url: "jdbc:postgresql://localhost:2222/uic"
      driverClassName: org.postgresql.Driver
      username: postgres
      password: 
      schema: 
      hikari:
        connectionTimeout: 20000
        maximumPoolSize: 10
      max-active: 3
      initial-size: 1
      max-idle: 2
      min-idle: 1
      test-while-idle: true
      test-on-borrow: true
      max-idle-time: 160000
      max-lifetime: 0

域对象：

import com.vividsolutions.jts.geom.Point;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import ru.stimul.geoservice.Geo;

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

@Data
@Builder(toBuilder = true)
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "geo_object", schema = "geo_service")
@Slf4j
public class GeoObject {

    @Id
    @Column
    String id;

    @Column
    Long created;

    @Column(name = "version_id")
    String versionId;

    @Column(name = "hq_id")
    String hqId;

    @Column(name = "source_id")
    String sourceId;

    @Column(name = "geo_provider_id")
    String geoProviderId;

    @Column(name = "uic_id")
    String uicId;

    @Column(name = "address_id")
    String addressId;

    Point geometry;

    @Column
    String address;

    @Column
    String type;

    @Column(name = "flat_count")
    Long flatCount;

    @Column(name = "voter_count")
    Long voterCount;

    @Column(name = "is_living")
    boolean isLiving;

    @Column(name = "is_generated")
    boolean isGenerated;

    @Column(length = 2000)
    String payload;
}

存储库：

package ru.stimul.geoservice.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import ru.stimul.geoservice.domain.primary.GeoObject;

@Repository
public interface CockroachDbRepository extends JpaRepository<GeoObject, String> {
}

 

服务：

public GeoObject upsertWithoutAccessCheck(String requestId, GeoObject geoObject) {
        if (Objects.isNull(geoObject.getId()) || StringUtils.isEmpty(geoObject.getId())) {
            geoObject = geoObject.toBuilder()
                    .id(UUID.randomUUID().toString())
                    .build();
        }
        return cockroachDbRepository.saveAndFlush(geoObject);
}

波姆。xml:

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.4.33.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            <version>5.4.33.Final</version>
        </dependency>

Answer 1

我建议您使用Hikari来处理多个数据源。

    @Configuration
    @ConfigurationProperties(prefix = "first.datasource")
    @EnableTransactionManagement
    public class FirstDBConfig extends HikariConfig {
    
        @Bean
        public DataSource firstDataSource() {
            return new HikariDataSource(this);
        }
    ...........................................
    
    @Configuration
    @ConfigurationProperties(prefix = "second.datasource")
    @EnableTransactionManagement
    @EnableJpaRepositories( basePackages = {
                "com.myproyect.repos"
        },
        entityManagerFactoryRef = "myEntityManager",
        transactionManagerRef = "myTransactionManager")
    public class SecondDBConfig extends HikariConfig {
    
        @Bean
        public DataSource secondDataSource() {
            return new HikariDataSource(this);
        }

有关更多详细信息，请阅读hikari

https://github.com/brettwooldridge/HikariCP#readme