Creating MySQL database programmatically in Java

There are times that you might need to create database or tables right after you run your program instead of manually creating it. In this example I will show you how you can do this using JDBC and MySQL database. The first thing we need to do as usual when creating a JDBC program is to define a JDBC URL. One thing that you’ll notice here is that we don’t define the database name in the URL. So the URL will be like jdbc:mysql://localhost.

After defining the URL we need to create a connection to the database. We issued the DriverManager.getConnection() method and pass the URL, username and password as the arguments. The next step is to create a PreparedStatement. When we call the preparedStatement() method we pass an SQL command to create the database, which is CREATE DATABASE IF NOT EXISTS DEMODB. This will create the database when there is database with DEMODB exists in the database. Finally call the PreparedStatement‘s execute() method.

Now you can try for your self, start typing the following code snippet in your text editor or IDE and execute it to create the database.

package org.kodejava.example.jdbc;

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

public class CreateMySQLDatabaseExample {
    public static void main(String[] args) {
        // 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 = "";

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

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

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

After you are executing the code snippet above you will find a new database named DEMODB created in your MySQL database server.

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How to establish connection to database with Properties?

In the following code snippet you’ll see how to pass some connection arguments when connecting to a database. To do this we can use the java.util.Properties class. We can put some key value pairs as a connection arguments to the Properties object before we pass this information into the DriverManager class.

Let’s see the example below:

package org.kodejava.example.jdbc;

import java.sql.*;
import java.util.Properties;

public class GetConnectionWithProperties {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try (Connection connection = demo.getConnection()) {
            // do something with the connection.
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM products");
            while (rs.next()) {
                System.out.println("Code = " + rs.getString("code"));
                System.out.println("Name = " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", USERNAME);
        connectionProps.put("password", PASSWORD);

        Connection connection = DriverManager.getConnection(URL, connectionProps);
        System.out.println("Connected to database.");
        return connection;
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How to automatically close resources in JDBC?

One thing that we need to do manually when programming using JDBC is to make sure to close all the resources that we use. All resources including the ResultSet, Statement and Connection must be closed. This usually will produce a lot of boiler plate code in our program.

Starting from JDBC 4.1 which is a part of Java 7 we can use the try-with-resources statement to automatically manage the resources that we use. This try statement closes the resources used when the block finishes its execution either normally or abruptly.

Here is an example that show us how to use the try-with-resources statement.

package org.kodejava.example.jdbc;

import java.sql.*;

public class TryWithResourceJdbc {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection conn =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM products")) {

            while (rs.next()) {
                String code = rs.getString("code");
                String name = rs.getString("name");

                System.out.println("Code: " + code + "; Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How do I check whether a driver is JDBC compliant?

package org.kodejava.example.jdbc;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCCompliant {
    private static final String URL = "jdbc:mysql://localhost/kodejava";

    public static void main(String[] args) {
        try {
            Driver driver = DriverManager.getDriver(URL);

            // Check if the driver is a genuine JDBC compliant driver.
            if (driver.jdbcCompliant()) {
                System.out.println("A genuine JDBC compliant driver");
            } else {
                System.out.println("Not a genuine JDBC compliant driver");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>


How do I get JDBC driver property information?

The example below show you how to get the information about the possible properties for the corresponding JDBC driver.

package org.kodejava.example.jdbc;

import java.sql.*;

public class DriverPropertyInfoDemo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";

    public static void main(String[] args) {
        try {
            // Gets information about the possible properties for this
            // driver.
            Driver driver = DriverManager.getDriver(URL);
            DriverPropertyInfo[] props = driver.getPropertyInfo(URL, null);

            for (DriverPropertyInfo info : props) {
                System.out.println("-----------------------------------");
                System.out.println("Name       : " + info.name);
                System.out.println("Description: " + info.description);
                System.out.println("Value      : " + info.value);

                String[] choices = info.choices;
                if (choices != null) {
                    StringBuilder sb = new StringBuilder("Choices    : ");
                    for (String choice : choices) {
                        sb.append(choice).append(",");
                    }

                    System.out.println(sb.toString());
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The example result of the code snippet above:

-----------------------------------
Name       : HOST
Description: Hostname of MySQL Server
Value      : localhost
-----------------------------------
Name       : PORT
Description: Port number of MySQL Server
Value      : 3306
-----------------------------------
Name       : DBNAME
Description: Database name
Value      : kodejava
-----------------------------------
Name       : allowLoadLocalInfile
Description: Should the driver allow use of 'LOAD DATA LOCAL INFILE...' (defaults to 'true').
Value      : true
Choices    : true,false,yes,no,

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How do I check if the OUT parameter value is null?

This example show you how to use the CallableStatement.wasNull() method call to see if the last OUT parameter has a value of SQL NULL.

package org.kodejava.example.jdbc;

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Types;
import java.sql.SQLException;

public class WasNullExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Prepares a call to the sored procedure
            String query = "call GetProductByPrice2(?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setDouble(1, 40d);

            // Registers the OUT parameter
            cb.registerOutParameter(2, Types.VARCHAR);

            // Executes the query
            cb.executeQuery();

            // Gets the OUT parameter value
            cb.getString(2);

            // Checks if the last OUT parameter has value of SQL NULL.
            // This method should be called only after calling a
            // getter method; otherwise, there is no value to use in
            // determining whether it is null or not.
            if (cb.wasNull()) {
                System.out.println("Product has an SQL NULL value");
            } else {
                System.out.println("Product: " + cb.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we called in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS GetProductByPrice2;;
CREATE PROCEDURE GetProductByPrice2(IN product_price DECIMAL, OUT product_name VARCHAR(50))
  BEGIN
    SELECT NAME INTO product_name FROM products WHERE price = product_price;
  END;;
DELIMITER ;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How do I call a stored procedure that return a result set?

This example show you how to call a stored procedure that return a result set of the query execution result.

package org.kodejava.example.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallableStatementExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Prepares a call to the sored procedure. This SP takes
            // one IN parameter
            String query = "call GetProductByPrice(?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setDouble(1, 40d);

            // Execute the query
            ResultSet rs = cb.executeQuery();

            while (rs.next()) {
                System.out.println("Product: " + rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we use in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS GetProductByPrice;;
CREATE PROCEDURE GetProductByPrice(IN product_price DECIMAL)
  BEGIN
    SELECT name FROM products WHERE price = product_price;
  END;;
DELIMITER ;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How do I register out parameter in CallableStatement?

This example show you how to register out parameter for executing a stored procedure using the CallableStatement.registerOutParameter() method call. We must register the out parameters before the query execution. The registerOutParameter() method takes two parameters, the index of the parameter and the sql data type of the out parameter.

package org.kodejava.example.jdbc;

import java.sql.*;

public class RegisterOutParameter {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Creates a CallableStatement for executing the stored
            // procedure
            String query = "call GetDetailByName(?, ?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setString(1, "Ruby on Rails");

            // Registers the out parameters
            cb.registerOutParameter(2, Types.VARCHAR);
            cb.registerOutParameter(3, Types.DECIMAL);

            // Executes the query
            cb.executeQuery();

            // Gets the query result output
            System.out.println("Name  : " + cb.getString(1));
            System.out.println("Code  : " + cb.getString(2));
            System.out.println("Price : " + cb.getBigDecimal(3));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the MySQL stored procedure that we call in the code above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS `GetDetailByName`;;
CREATE PROCEDURE `GetDetailByName`(INOUT  product_name  VARCHAR(50),
                                   OUT product_code  VARCHAR(10),
                                   OUT product_price DECIMAL(10, 2))
  BEGIN
    SELECT code INTO product_code FROM products WHERE name = product_name;

    SELECT price INTO product_price FROM products WHERE name = product_name;
  END;;
DELIMITER ;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>