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();
        }
    }
}