CODEWITHSUNDEEP
javapostgresqlhibernatespring-bootspring-data-jpa
Ryuzaki L
Ryuzaki L

Reputation: 40078

Create schema if does not exist by using spring Jpa with hibernate

I'm working on spring boot 2 application and trying to establish connection with postgresql database with configuring hikari datasource and spring Jpa.

I'm succeed in that and i'm using hibernate.hbm2ddl.auto as update so it is creating table if not exists, but only the problem is it is throwing an exception when schema does not exist

Error

GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement
Caused by: org.postgresql.util.PSQLException: ERROR: schema "test_schema" does not exist

I configured everything manually through config class

Config Class

@Bean
@Primary
public HikariDataSource dataSource() {

    HikariConfig config = new HikariConfig();

    config.setJdbcUrl(databaseUrl);
    config.setUsername(username);
    config.setPassword(password);
    config.setDriverClassName(driverClassName);
    config.setConnectionTimeout(connectionTimeout);
    config.setIdleTimeout(idleTimeout);
    config.setMaximumPoolSize(maxpoolSize);
    config.setMaxLifetime(maxLifeTime);
    config.setMinimumIdle(minIdleConnections);
    //config.setPoolName(poolName);

    return new HikariDataSource(config);
}

@Bean
public Properties additionalProps() {
    Properties jpaProps = new Properties();

    jpaProps.put(hbDialect, "org.hibernate.dialect.PostgreSQLDialect");
    jpaProps.put(autoDDL, "update");
    jpaProps.put(showSql, true);
    jpaProps.put(formatSql, true);

    return jpaProps;
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setDataSource(dataSource());
    factory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    factory.setPackagesToScan("com.target.storetaskcount.entity");
    factory.setJpaProperties(additionalProps());
    return factory;
}

@Bean
public JpaTransactionManager transactionManager(EntityManagerFactory factory) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
    return transactionManager;
}

How would i make this to create schema if not exist?

I saw a similar question here and tried everything no luck so far similar-question (i don't wanna use flyway db)

Here is the documentation hibernate-doc, but it's not clear how add this property to create schema javax.persistence.schema-generation.database.action

Upvotes: 5

Views: 21407

Answers (4)

foshoshin
foshoshin

Reputation: 176

Updated answer for December 2023 Running Spring boot 3.2 with Hibernate 6.3.1: we now have jakarta.peristence.create-database-schemas: true

Example:

jpa:
  hibernate:      
    ddl-auto: create
  properties:
    jakarta:
      persistence:
        create-database-schemas: true
    hibernate:
      default_schema: ${DB_SCHEMA:}

Upvotes: 1

Enix
Enix

Reputation: 146

While the other answer is totally correct, I'm sharing what worked for me. Adding this to application.properties file will create schemas if absent:

spring.jpa.properties.hibernate.hbm2ddl.create_namespaces=true

Here we use the Hibernates native property prefixed (spring.jpa.properties.*) with Spring JPA.

Similarly, you can use many other Hibernates native properties this way.

Spring Docs

You can set it, along with other Hibernate native properties, by using spring.jpa.properties.* (the prefix is stripped before adding them to the entity manager).

In my case I was using MSSQL Server

Upvotes: 5

Ryuzaki L
Ryuzaki L

Reputation: 40078

Adding this property worked and created schema if not exist here

jpaProps.put("javax.persistence.create-database-schemas", true);

The JPA variant of hibernate.hbm2ddl.create_namespaces. Specifies whether the persistence provider is to create the database schema(s) in addition to creating database objects (tables, sequences, constraints, etc). The value of this boolean property should be set to true if the persistence provider is to create schemas in the database or to generate DDL that contains "CREATE SCHEMA" commands. If this property is not supplied (or is explicitly false), the provider should not attempt to create database schemas.

Upvotes: 5

jcompetence
jcompetence

Reputation: 8393

This is what worked for me for Postgres on Springboot 2.6.2.

It will create the schema (adapterborder) if it does not exist and use it as the default_schema when creating all Entity Tables.

Enabled TRACE for logging to see how it works as well.

spring:
  jpa:
    open-in-view: false
    generate-ddl: true
    properties:
      javax:
        persistence:
          schema-generation:
            database:
              action: create
      hibernate:
        show_sql: true
        use_sql_comments: true
        format_sql: true
        generate_statistics: false
        jdbc:
          fetch_size: 2000
          lob.non_contextual_creation: true
        dialect: org.hibernate.dialect.PostgreSQLDialect
        ddl-auto: create
        hbm2dll:
          create_namespaces: true
        default_schema: adapterborder

  datasource:
    url: jdbc:postgresql://localhost:5432/postgres?currentSchema=adapterborder
    username: postgres
    password: postgres
    driver-class-name: org.postgresql.Driver
    testWhileIdle: true
    hikari:
      minimumIdle: 5
      maximumPoolSize: 20
      idleTimeout: 30000
      poolName: SpringBootJPAHikariCP
      maxLifetime: 200000
      connectionTimeout: 30000
      connection-test-query: SELECT 1

logging:
  level:
    '[org.springframework.data]': TRACE
    '[org.hibernate]': TRACE
    '[javax.persistence]': TRACE

Upvotes: 3

Related Questions