My application has two data sources. First datasource access Postgre using JdbcTemplate. Second datasource access CockroachDb using Spring Data JPA.
So i created two configurations for this datasources.
Persisting to both datasources works fine, except objects with spatial data. When i trying to save spatial data to CockroachDb, i get an error:
Hibernate: select geoobject0_.id as id1_2_0_, geoobject0_.address as address2_2_0_, geoobject0_.address_id as address_3_2_0_, geoobject0_.created as created4_2_0_, geoobject0_.flat_count as flat_cou5_2_0_, geoobject0_.geo_provider_id as geo_prov6_2_0_, geoobject0_.geometry as geometry7_2_0_, geoobject0_.hq_id as hq_id8_2_0_, geoobject0_.is_generated as is_gener9_2_0_, geoobject0_.is_living as is_livi10_2_0_, geoobject0_.payload as payload11_2_0_, geoobject0_.source_id as source_12_2_0_, geoobject0_.type as type13_2_0_, geoobject0_.uic_id as uic_id14_2_0_, geoobject0_.version_id as version15_2_0_, geoobject0_.voter_count as voter_c16_2_0_ from geo_service.geo_object geoobject0_ where geoobject0_.id=? Hibernate: insert into geo_service.geo_object (address, address_id, created, flat_count, geo_provider_id, geometry, hq_id, is_generated, is_living, payload, source_id, type, uic_id, version_id, voter_count, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 2022-01-18 17:01:39.583 [ForkJoinPool.commonPool-worker-3] WARN o.h.e.jdbc.spi.SqlExceptionHelper logExceptions() - SQL Error: 0, SQLState: XXUUU 2022-01-18 17:01:39.584 [ForkJoinPool.commonPool-worker-3] ERROR o.h.e.jdbc.spi.SqlExceptionHelper logExceptions() - ERROR: wkb: unknown byte order: 10101100
Java configuration:
For Postgre:
@Configuration
public class UicDbConfig {
@Bean(name = "uicDataSource")
@Qualifier("uicDataSource")
@Primary
@ConfigurationProperties(prefix="spring.uic.datasource")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
}
For CockroachDb:
@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;
}
}
application.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
Domain object:
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;
}
Repository:
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> {
}
Service:
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);
}
pom.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>
