遇到的坑

上班第二天差点删库跑路

第一次使用这个，配置拷贝别人的，配置的是spring.jpa.hibernate.ddl-auto=create，把公司的表删了！幸好那个表是postgis生成的，重新运行一下命令就又生成了。

#none：默认值，什么都不做，每次启动项目，不会对数据库进行任何验证和操作 #create：每次运行项目，没有表会新建表，如果表内有数据会被清空 #create-drop：每次程序结束的时候会清空表 #update：每次运行程序，没有表会新建表，但是表内有数据不会被清空，只会更新表结构。 #validate：运行程序会校验数据与数据库的字段类型是否相同，不同会报错

spring.jpa.hibernate.ddl-auto=none

JPA将表名转换了，导致报错

数据库postgresql，模式shp，表名test_zc，正常语句select * from shp.test_zc，可是JPA将表名shp.test_zc转换成了shp_test_zc，报错了

正文：Springboot JPA 操作 Greenpum

1.导包

springboot项目，pom.xml加入postgresql依赖，greenplum-jdbc驱动

<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <!-- https://mvnrepository.com/artifact/com.pivotal/greenplum-jdbc --> <dependency> <groupId>com.pivotal</groupId> <artifactId>greenplum-jdbc</artifactId> <version>5.1.4</version> </dependency>

2.配置

配置application.properties

#通用数据源配置 spring.datasource.driver-class-name=com.pivotal.jdbc.GreenplumDriver spring.datasource.url=jdbc:pivotal:greenplum://ip:port;DatabaseName=bigdata spring.datasource.username=gpadmin spring.datasource.password=gpadmin # Hikari 数据源专用配置 spring.datasource.hikari.maximum-pool-size=20 spring.datasource.hikari.minimum-idle=5 # JPA 相关配置 spring.jpa.database=postgresql #spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect spring.jpa.show-sql=true #none：默认值，什么都不做，每次启动项目，不会对数据库进行任何验证和操作 #create：每次运行项目，没有表会新建表，如果表内有数据会被清空 #create-drop：每次程序结束的时候会清空表 #update：每次运行程序，没有表会新建表，但是表内有数据不会被清空，只会更新表结构。 #validate：运行程序会校验数据与数据库的字段类型是否相同，不同会报错 spring.jpa.hibernate.ddl-auto=none

解决表名转换application.yml

spring: jpa: hibernate: naming: physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3.编写与表对应的实体类

@Data @AllArgsConstructor @NoArgsConstructor @Entity @Table(name = "shp.test_zc") public class TestZc { //分布键 @Id String id; //日期 @Column Date date; //价格 @Column(length = 255) String amt; }

4.编写Dao接口

@Repository public interface TestZcDao extends JpaRepository<TestZc,String> { }

5.测试

需要测试的包

<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-test</artifactId> </dependency> @RunWith(SpringRunner.class) @SpringBootTest public class TestZcDaoTest { @Autowired private TestZcDao testZcDao; @Before public void before() { } @Test public void testFind() { Optional<TestZc> byId = testZcDao.findById("1"); //select * from shp.test_zc where id=1; TestZc testZc = byId.get(); TestZc t2=new TestZc(); t2.setAmt("天空飘来五个字"); t2.setDate(new Date()); t2.setId("2"); testZcDao.save(t2); //如果表中没有就是insert into，如果有，就是update System.out.println(testZc.getAmt()); testZc.setAmt("我更改了amt"); testZcDao.save(testZc);//update testZcDao.deleteById("3");//delete } @After public void after() { } }

运行正常的结果

2020-07-03 14:38:57.296 INFO 19048 --- [ main] com.kqgeo.greenplum.test.TestZcDaoTest : Started TestZcDaoTest in 12.978 seconds (JVM running for 15.899) Hibernate: select testzc0_.id as id1_0_0_, testzc0_.amt as amt2_0_0_, testzc0_.date as date3_0_0_ from shp.test_zc testzc0_ where testzc0_.id=? Hibernate: select testzc0_.id as id1_0_0_, testzc0_.amt as amt2_0_0_, testzc0_.date as date3_0_0_ from shp.test_zc testzc0_ where testzc0_.id=? Hibernate: update shp.test_zc set amt=?, date=? where id=? 999.98 Hibernate: select testzc0_.id as id1_0_0_, testzc0_.amt as amt2_0_0_, testzc0_.date as date3_0_0_ from shp.test_zc testzc0_ where testzc0_.id=? Hibernate: update shp.test_zc set amt=?, date=? where id=? Hibernate: select testzc0_.id as id1_0_0_, testzc0_.amt as amt2_0_0_, testzc0_.date as date3_0_0_ from shp.test_zc testzc0_ where testzc0_.id=? Hibernate: delete from shp.test_zc where id=? 2020-07-03 14:38:58.209 INFO 19048 --- [extShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default' 2020-07-03 14:38:58.213 INFO 19048 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor' 2020-07-03 14:38:58.214 INFO 19048 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated... 2020-07-03 14:38:58.219 INFO 19048 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed. Process finished with exit code 0