How can I create a new schema in mysql from my spring boot app

Question

I want to create new database schemas in mysql from spring boot as it is done with command line -> create database [schema-name]

How can I accomplish that?

I am using hibernate,jpa

Answer 1

I suppose you want to create DB programmatically.

You can use the following code to accomplish that:

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

@Component
public class ApplicationStartup implements ApplicationListener<ApplicationReadyEvent> {

    @Value("${database:DEMODB}")
    private String database;

    /**
     * This event is executed as late as conceivably possible to indicate that
     * the application is ready to service requests.
     */
    @Override
    public void onApplicationEvent(final ApplicationReadyEvent event) {

        // Defines the JDBC URL. As you can see, we are not specifying
        // the database name in the URL.
        String url = "jdbc:mysql://localhost";

        // Defines username and password to connect to database server.
        String username = "root";
        String password = "master";

        // SQL command to create a database in MySQL.
        String sql = "CREATE DATABASE IF NOT EXISTS " + database;

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Make sure this component will be discovered by component scan at runtime.

You can pass database name with command line as follows:

java -jar spring-boot-app.jar --database=test_db

If no database specified - this code will create DB with name DEMODB. See @Value annotation over the 'database' field.