【Springboot】Springboot JPA操作Greenplum

遇到的坑

上班第二天差点删库跑路

第一次使用这个，配置拷贝别人的，配置的是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