Search code examples
javasqlmysqlswingjdbc

In MySQL, how can I create a stored procedure to create the database itself and then call this procedure from Java using JDBC?

I'm developing a Java application and need to write a separate stored procedure in MySQL to create a database itself by query:

CREATE DATABASE IF NOT EXISTS db_name;

I tried this one:

DELIMITER //

CREATE PROCEDURE create_database_and_dish_table()
BEGIN
    CREATE DATABASE IF NOT EXISTS restaurant_db;
    USE restaurant_db;
    CREATE TABLE dish (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description VARCHAR(255),
        price DECIMAL(10, 2),
        category VARCHAR(50)
    );
END //

In MySQL Workbench, however, I receive an error because I did not specify the required database name. So how can I do that? And after that, how can I connect to the MySQL server from my Java JDBC application without mentioning the database name in the connection string and then call the desired stored creation procedure?

Solution

  • This is a "Chicken and Egg" problem.

    You can't create the procedure in the restaurant_db database before you create that database.

    By analogy: in any operating system, you can't create a file within a folder before you create that folder. You must first create the folder.

    Likewise, there's no way you can make a stored procedure that creates a database, such that the stored procedure resides within that database.

    The stored procedure can reside in another database, and it can create a new database and tables within that new database.

    You could also create database first, before you create the procedure, then you can create a procedure in that database to create tables or other objects.

    I would just not bother with a procedure at all. There's nothing in the procedure you show that needs to be in a procedure. You can just run DDL statements directly from Java.