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://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</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.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 read CLOBs data from database?

package org.kodejava.example.sql;

import java.io.File;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.*;

public class ClobReadDemo {
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String username = "kodejava";
    private static String password = "welcome";

    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, username, password);

            String sql = "SELECT name, description, data FROM documents ";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString(1);
                System.out.println("Name        = " + name);
                String description = resultSet.getString(2);
                System.out.println("Description = " + description);

                File data = new File("C:\\The Appfuse Primer.txt");

                // Get the character stream of our CLOB data
                Reader reader = resultSet.getCharacterStream(3);
                FileWriter writer = new FileWriter(data);
                char[] buffer = new char[1];
                while (reader.read(buffer) > 0) {
                    writer.write(buffer);
                }
                writer.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}

How do I retrieve auto-generated keys?

package org.kodejava.example.sql;

import java.sql.*;

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

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

            //
            // The orders table have an ID column which value will be auto generated
            // by database. When inserting a new record a new id for the primary key
            // will be generated and we will get the generated key so we can used it
            // in another process. For instance if we have a master detail tables
            // where the details table required an id from the master table.
            //
            String insert = "INSERT INTO orders (username, order_date) VALUES ('foobar', '2007-12-13')";
            Statement stmt = connection.createStatement();

            //
            // When executing the statement we can pass the Statement.RETURN_GENERATED_KEYS
            // so that we can later extract the generated key from the result set object
            // returned by this method.
            //
            stmt.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);

            ResultSet keys = stmt.getGeneratedKeys();
            int lastKey = 1;
            while (keys.next()) {
                lastKey = keys.getInt(1);
            }

            System.out.println("Last Key: " + lastKey);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        }
    }
}

How do I commit or rollback transaction in JDBC?

Executing a database manipulation command such as insert, update or delete can sometime throws exception due to invalid data. To protect the integrity of our application data we must make sure when we a transaction was failed we must rollback all the executed command so that it affect the state of our data.

In this example we are using MySQL database. To enable transaction capability in MySQL make sure that you are using InnoDB storage engine to create the your tables.

package org.kodejava.example.sql;

import java.sql.*;

public class TransactionRollbackExample {
    private static final String url = "jdbc:mysql://localhost/sampledb";
    private static final String username = "root";
    private static final String password = "";

    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);

            String query = "INSERT INTO orders (username, order_date) " +
                           "VALUES (?, ?)";
            PreparedStatement stmt = conn.prepareStatement(query,
                    PreparedStatement.RETURN_GENERATED_KEYS);
            stmt.setString(1, "javaduke");
            stmt.setDate(2, new Date(System.currentTimeMillis()));
            stmt.execute();

            ResultSet keys = stmt.getGeneratedKeys();
            int id = 1;
            if (keys.next()) {
                id = keys.getInt(1);
            }

            // This is an invalid statement that will cause exception to 
            // demonstrate a rollback.
            query = "INSERT INTO order_details (order_id, product_id, " +
                    "quantity, price) VALUES (?, ?, ?, ?, ?)";
            PreparedStatement detailStmt = conn.prepareStatement(query);
            detailStmt.setInt(1, id);
            detailStmt.setString(2, "P0000001");
            detailStmt.setInt(3, 10);
            detailStmt.setDouble(4, 100);
            detailStmt.execute();

            // Commit transaction to mark it as a success database operation
            conn.commit();
            System.out.println("Transaction commit...");
        } catch (SQLException e) {
            // Rollback any database transaction due to exception occurred
            if (conn != null) {
                conn.rollback();
                System.out.println("Transaction rollback...");
            }
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}

How do I create a connection to MS Access database?

Here is an example about how to create a database connection to MS Access database. To allow the database access to be authenticated the security user account can be add from Tools->Security->User and Group Accounts menu in the Microsoft Access 2003.

On the example below we can either connect through the DSN created previously on the Windows system or we can create it in our program as the long URL below.

package org.kodejava.example.sql;

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

public class MSAccessConnect {
    // If you want to use you ODBC DSN
    //private static final String URL = "jdbc:odbc:TestDB";

    private static final String USERNAME = "admin";
    private static final String PASSWORD = "welcome";
    private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";

    private static final String URL =
        "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\Database\testdb.mdb;}";

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

            // Do something with the connection here!
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }
}

How do I disable auto commit mode in JDBC?

The code fragment below show you how we disable auto commit operation when executing a JDBC command.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.SQLException;

public class CommitSettingExample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            // DO: Get a connection to database, we need to obtain the 
            // database connection prior to executing any JDBC commands/

            // Disable the auto-commit operation. By default every statement
            // executed against database in JDBC is in auto-commit mode. To 
            // disable auto-commit set it to false
            connection.setAutoCommit(false);

            // DO: Execute some other database operation here

            // Finally we must call the commit method explicitly to finish
            // all database manipulation operation
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}