Creating MySQL database programatically 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.sql;

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 = "root";

        // 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.

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.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class GetConnectionWithProperties {
    private String url = "jdbc:mysql://localhost/testdb";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try {
            Connection connection = demo.getConnection();

            // do something with the connection.
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

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

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.sql;

import java.sql.*;

public class TryWithResourceJdbc {
    public static final String URL = "jdbc:mysql://localhost/sampledb";

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");

            TryWithResourceJdbc demo = new TryWithResourceJdbc();
            demo.selectData();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Select data from m_users table.
     * @throws SQLException when an exception happens.
     */
    private void selectData() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "root", "");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM m_users")) {

            while (rs.next()) {
                String username = rs.getString("username");
                String password = rs.getString("password");

                System.out.println("username: " + username +
                        "; password: " + password);
            }
        }
    }
}

How do I retrieve available schemas in database?

The examples below show you how to get the available schema names in Oracle database. The available schema names can be obtained from the DatabaseMetaData object by calling the getSchemas() method.

This method returns a ResultSet object. After obtaining the ResultSet object we need to iterate it and get the schema name by calling the result set getString() method and pass TABLE_SCHEM as the column name.

package org.kodejava.example.sql;

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

public class MetadataGetSchema {

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            //
            // Gets DatabaseMetaData
            //
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Retrieves the schema names available in this database
            //
            ResultSet rs = metadata.getSchemas();

            while (rs.next()) {
                String schema = rs.getString("TABLE_SCHEM");
                System.out.println("Schema: " + schema);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";        
        return DriverManager.getConnection(url, "kodejava", "welcome");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

Here an a list of schema names available in the Oracle XE database:

Schema: ANONYMOUS
Schema: CTXSYS
Schema: DBSNMP
Schema: DIP
Schema: FLOWS_020100
Schema: FLOWS_FILES
Schema: HR
Schema: KODEJAVA
Schema: MDSYS
Schema: OUTLN
Schema: SYS
Schema: SYSTEM
Schema: TSMSYS
Schema: XDB

How do I get the maximum number of concurrent connections?

This example show you how to get the maximum number of concurrent connections to a database that are possible. To get this information we use the DatabaseMetaData.getMaxConnections() method call. If return value is zero it means that there is no limit or the limit is unknown.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.DriverManager;

public class MaxConnections {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            //
            // Get database meta data.
            //
            DatabaseMetaData metaData = connection.getMetaData();

            //
            // Retrieves the maximum number of concurrent
            // connections to this database that are possible.
            // A result of zero means that there is no limit or
            // the limit is not known.
            //
            int max = metaData.getMaxConnections();
            System.out.println("Max concurrent connections allowed: " + max);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

How do I check whether a driver is JDBC compliant?

package org.kodejava.example.sql;

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

public class JdbcCompliant {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/kodejavadb";
            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 (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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.sql;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;

public class DriverPropertyInfoDemo {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost/kodejavadb";

        try {
            //
            // Gets information about the possible properties for this
            // driver.
            //
            Driver driver = getDriver(url);
            DriverPropertyInfo[] properties = driver.getPropertyInfo(url, null);

            for (DriverPropertyInfo info : properties) {
                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(",");
                    }

                    //
                    // Deletes the last ","
                    //
                    sb = sb.deleteCharAt(sb.length() - 1);
                    System.out.println(sb.toString());
                }
            }

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

    // 
    // Get the Driver used to connect to database
    //
    private static Driver getDriver(String url) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getDriver(url);
    }
}

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.sql;

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

public class WasNullExample {
    public static void main(String[] args) {
        Connection connection = null;

        try {
            connection = getConnection();

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

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

            //
            // 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 (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     *
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     *
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

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

CREATE PROCEDURE GET_PRODUCT_BY_PRICE2 (IN vprice decimal, OUT vproduct varchar(30))
BEGIN
  SELECT name INTO vproduct
  FROM products
  WHERE price = vprice;
END;

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.sql;

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

public class CallableStatementExample {
    public static void main(String[] args) {
        Connection connection = null;

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

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

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

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

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

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

CREATE PROCEDURE GET_PRODUCT_BY_PRICE (IN vprice decimal)
BEGIN
 SELECT name
 FROM product
 WHERE price = vprice;
END;

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.sql;

import java.sql.*;

public class RegisterOutParameter {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

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

            // 
            // Sets the input parameter
            //
            cb.setString(1, "bag");

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

            //
            // Executes the query
            //
            cb.executeQuery();

            //
            // Gets the query result output
            //
            System.out.println("Code    : " + cb.getString(2));
            System.out.println("Price   : " + cb.getBigDecimal(3));
            System.out.println("Quantity: " + cb.getInt(4));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

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

DROP PROCEDURE IF EXISTS kodejavadb.GET_DETAIL_BY_PRODUCT;
CREATE PROCEDURE kodejavadb.`GET_DETAIL_BY_PRODUCT`(IN vproduct varchar(30),
OUT vcode varchar(5),
OUT vprice decimal,
OUT vqty int)
BEGIN
  SELECT
  code INTO vcode
  FROM kodejavadb.products
  WHERE name = vproduct;

  SELECT
  price INTO vprice
  FROM kodejavadb.products
  WHERE name = vproduct;

  SELECT
  qty INTO vqty
  FROM kodejavadb.products
  WHERE name = vproduct;
END;