PostgreSQL + Hibernate + Spring auto create database

Question

I'm working with PostgreSQL and Spring 4 and want my app auto create database when it running.

My Entity Class is:

@Entity
@Table(name = "user", schema = "public")
public class User extends BaseEntity {

    private Integer id;
    private String name;
    private Integer contractId;

    public User() {
    }

    public User(Integer id) {
        super(id);
    }

    @Id
    @Column(name = "usr_id", nullable = false)
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Basic
    @Column(name = "usr_name", nullable = true, length = -1)
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Basic
    @Column(name = "usr_contract_id", nullable = true)
    public Integer getContractId() {
        return contractId;
    }

    public void setContractId(Integer contractId) {
        this.contractId = contractId;
    }

}

HibernateConfig.java

@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@PropertySources({
    @PropertySource(value = "classpath:application.properties")})
@ConfigurationProperties(prefix = "spring.datasource")
public class HibernateConfig {

    @Autowired
    private Environment environment;

    @Autowired
    private DataSource dataSource;

    @Autowired
    private MultiTenantConnectionProvider multiTenantConnectionProvider;

    @Autowired
    private CurrentTenantIdentifierResolver currentTenantIdentifierResolver;

    public HibernateConfig() {}

    @Bean
    public LocalSessionFactoryBean sessionFactory() throws Exception {

        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setHibernateProperties(hibernateProperties());

        sessionFactory.setPackagesToScan(new String[] {
            "com.xxx.xxx.model",
        });

        return sessionFactory;
    }

    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put(DIALECT, environment.getRequiredProperty(DIALECT));
        properties.put(SHOW_SQL, environment.getRequiredProperty(SHOW_SQL));
        properties.put(FORMAT_SQL, environment.getRequiredProperty(FORMAT_SQL));
        properties.put(HBM2DDL_AUTO, environment.getRequiredProperty(HBM2DDL_AUTO));

        return properties;
    }

    @Bean
    @Primary
    @Autowired
    public HibernateTransactionManager transactionManager(SessionFactory s) {
        HibernateTransactionManager txManager = new HibernateTransactionManager();
        txManager.setSessionFactory(s);
        return txManager;
    }

    @Bean
    @Autowired
    public HibernateTemplate hibernateTemplate(SessionFactory s) {
        HibernateTemplate hibernateTemplate = new HibernateTemplate(s);
        return hibernateTemplate;
    }
}

application.properties

# Database connection settings:
jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/database
jdbc.username=postgres
jdbc.password=111111

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql=false
hibernate.format_sql=false
hibernate.hbm2ddl.auto=update

spring.datasource.initialSize=50
spring.datasource.maxActive=200
spring.datasource.maxIdle=200
spring.datasource.minIdle=50

But when I running SQL to access table User, this will appear error: Table 'User' does not exist.

How can I make Hibernate to auto create database?

Have you tired hibernate.hbm2ddl.auto=create? – sAm Jul 11 '16 at 06:02 — sAm, Jul 11 '16 at 06:02
hey did you solve this? and how? – Ryuzaki L Jan 23 '19 at 21:54 — Ryuzaki L, Jan 23 '19 at 21:54

score 28 · Answer 1 · edited May 23 '17 at 12:32

Postgres unlike mysql does not support Create Database If not exist.

Thus changing hibernate.hbm2ddl.auto=create and changing URL jdbc.url=jdbc:postgresql://localhost/database?createDatabaseIfNotExist=true won't work for you.

However you can try simulating the behavior as in below questions:

Create Postgres database on the fly, if it doesn't exists using Hibernate

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

score 9 · Answer 2 · edited Nov 16 '18 at 13:55

Try this way

spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL94Dialect

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url= jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=123

spring.jpa.show-sql=true
spring.session.store-type=none

This is work for me.

From the Automatic schema generation section of the Hibernate User Guide:

javax.persistence.schema-generation.database.action

Setting to perform SchemaManagementTool actions automatically as part of the SessionFactory lifecycle. Valid options are defined by the externalJpaName value of the Action enum:

none - No action will be performed.

create - Database creation will be generated.

drop - Database dropping will be generated.

drop-and-create - Database dropping will be generated followed by database creation.

there spring.jpa.hibernate.ddl-auto=update ==> update, you can change according to your scenario.

aksappy · Accepted Answer · 2016-07-18T09:37:23.837

The property hibernate.hbm2ddl.auto will do the trick for you. It automatically validates or exports schema DDL to the database when the SessionFactory is created. With create-drop, the database schema will be dropped when the SessionFactory is closed explicitly.

Hibernate can accept these options for the above property.

validate: validate the schema, makes no changes to the database.

update: update the schema.

create: creates the schema, destroying previous data.

create-drop: drop the schema at the end of the session.

Jayesh Solanki · Answer 4 · 2022-09-10T04:06:39.047

Spring Boot

Postgres does not support createDatabaseIfNotExist=true So you can try similar way and it worked for me see Screenshot

@SpringBootApplication
public class SpringSecurityJwtApplication{

    public static void main(String[] args) {
        Logger logger = LoggerFactory.getLogger(SpringSecurityJwtApplication.class);
        Connection connection = null;
        Statement statement = null;
        try {
            logger.debug("Creating database if not exist...");
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "postgres", "postgres");
            statement = connection.createStatement();
            statement.executeQuery("SELECT count(*) FROM pg_database WHERE datname = 'database_name'");
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            int count = resultSet.getInt(1);

            if (count <= 0) {
                statement.executeUpdate("CREATE DATABASE database_name");
                logger.debug("Database created.");
            } else {
                logger.debug("Database already exist.");
            }
        } catch (SQLException e) {
            logger.error(e.toString());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                    logger.debug("Closed Statement.");
                }
                if (connection != null) {
                    logger.debug("Closed Connection.");
                    connection.close();
                }
            } catch (SQLException e) {
                logger.error(e.toString());
            }
        }
        SpringApplication.run(SpringSecurityJwtApplication.class, args);
    }
}

score 1 · Answer 5 · answered Nov 20 '19 at 10:10

1

The problem is about hibernate dialect. You are using old one. You should use newer one like this.

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL95Dialect

answered Nov 20 '19 at 10:10

Ali Katkar

517
4
7

score 0 · Answer 6 · answered Jan 09 '20 at 11:11

0

Just change

from:

@Table(name = "user") || @Entity(name="user")

to:

@Table(name = "users") || @Entity(name="users")

Because PostgreSQL has default "user"

answered Jan 09 '20 at 11:11

MrOlloyor

1

score -3 · Answer 7 · answered Oct 17 '18 at 09:27

-3

you can have a schema.sql script with "CREATE SCHEMA IF NOT EXISTS x;" with

spring.jpa.properties.hibernate.hbm2ddl.auto=update

It should work

answered Oct 17 '18 at 09:27

Meriam

181
2
3

PostgreSQL + Hibernate + Spring auto create database

7 Answers7

Linked