欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【Springboot】Springboot JPA操作Greenplum

程序员文章站 2022-05-02 09:37:25
...

遇到的坑

上班第二天差点删库跑路

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

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

spring.jpa.hibernate.ddl-auto=none

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

【Springboot】Springboot JPA操作Greenplum
数据库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