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