How to create a schema and initialize database in a Spring Boot application

Sitewide-10usd300x250 A common way to auto-create a database schema in a Spring Boot JPA application is just to set a spring.jpa.hibernate.ddl-auto property to create, create-drop or update. This would create a schema matching the entity classes in your application, when your application starts.

But, sometimes you may like to create a schema for which you have not defined entity classes. For example, if you are coding an authorization server by using spring-security-oauth2, you may like to create a schema like this.

Spring Boot provides an easy way to do so! If you place files named schema.sql and data.sql in classpath root (i.e. src/main/resurces), they’ll be run when your application starts.

It’s a powerful feature – for example, you can have different initialization scripts for different database providers. Visit Spring Boot reference for more details.

Happy coding!

3 thoughts on “How to create a schema and initialize database in a Spring Boot application

  1. Developer Reply

    Hello! In eclipse and spring boot application I trying to create database and tables via spring-boot-starter-jdbc. schema.sql file in /src/main/resources/ folder. This folder also included in buid path. But i have an error:

    2017-09-27 14:16:55.748 WARN 7288 — [ restartedMain] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSource’ defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Tomcat.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSourceInitializer’: Invocation of init method failed; nested exception is org.springframework.boot.context.config.ResourceNotFoundException: ServletContext resource [/schema.sql] defined by ‘spring.datasource.schema’ does not exist

    config:
    #После первого запуска (создание бд) установить в false
    spring.datasource.initialize=true
    #драйвер myssql
    spring.datasource.driverClassName = com.mysql.jdbc.Driver
    #for first connection to create database
    spring.datasource.url=jdbc:mysql://localhost:3306/mysql
    #fyi, будет создана бд mobile
    mysql.url=jdbc:mysql://localhost:3306/mobile?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT
    #логин суперпользователя или бд менеджера
    spring.datasource.username=root
    #пароль
    spring.datasource.password=12345678
    #скрипт создания бд и таблиц
    spring.datasource.schema=schema.sql
    spring.datasource.continue-on-error=true
    #другие вынесенные константы
    #длина номера телефон
    mobile_length=10
    #минимальная длина пароля
    password_length=8

    Would you like to help me please? Tx

  2. Sanjay Post authorReply

    I think by multiple schema you mean a kind of multiple databases? And, you want to map an entity class to a particular database, let’s say? Interesting, but I haven’t tried or thought about this. Once you find a way, please share!

  3. Babafemi Reply

    Dear Sir,
    Thank you for the nice write up. However, the issue is I want to use multiple schemas in one application and I can get the application to route the entity classes/definitions to desired or selected schema.

    Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *