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

How do I delete record from table?

In this example we are showing you how to delete a record from table in the database. We use a standard JDBC library for this purpose. For the database we use MySQL, you can use any type of database you want. All you need to do is to find the JDBC driver for the database and configure it accordingly.

So here is the code example for deleting records from a table in database.

package org.kodejava.example.sql;

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

public class DeleteRecordExample {
    public static void main(String[] args) throws Exception {
        // This is our connection url to MySQL database, where jdbc is the
        // prefix for all jdbc connection. The mysql sub telling that we
        // are using MySQL database. Localhost is where our database is
        // reside and kodejava is our database name.
        String url = "jdbc:mysql://localhost/kodejava";

        // To connect to a database we will need a user and password for
        // the database server to allow us to manipulate its database.
        String username = "kodejava";
        String password = "kodejava123";

        Connection connection = null;
        try {
            // Load the jdbc driver class
            Class.forName("com.mysql.jdbc.Driver");
            // Then we ask a connection from the DriverManager by passing
            // the connection URL and the password.
            connection = DriverManager.getConnection(url, username, password);

            // To delete records from tables we create an SQL delete command.
            // The question mark that we used in the where clause will be the
            // holder of value that will be assigned by PreparedStatement
            // class.
            String sql = "DELETE FROM m_users WHERE id = ?";
            int id = 2;

            // Create a statement object. We use PreparedStatement here.
            PreparedStatement statement = connection.prepareStatement(sql);

            // Pass a value of a userId that will tell the database which
            // records in the database to be deleted. Remember that when
            // using a statement object the index parameter is start from
            // 1 not 0 as in the Java array data type index.
            statement.setInt(1, id);

            // Tell the statement to execute the command. The executeUpdate()
            // method for a delete command returns number of records deleted
            // as the command executed in the database. If no records was
            // deleted it will simply return 0
            int rows = statement.executeUpdate();

            System.out.println(rows + " record(s) deleted.");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        }
    }
}

How do I create a connection to database?

This post is about an example for obtaining a connection to MySQL database. For connecting to other database all you have to do is change the url to match to url format for a particular database and of course you have to register a correct JDBC driver of the database you are using.

Here are the steps:

  • Define the JDBC url of your database. Below is the format of JDBC url for MySQL database.
public static final String URL = "jdbc:mysql://localhost/kodejava";
  • Define the username dan password for the connection.
public static final String USERNAME = "kodejava";
public static final String PASSWORD = "kodejava123";
  • Register the database JDBC driver to be used by our program. Below is the driver for MySQL database.
Class.forName("com.mysql.jdbc.Driver");
  • We can open a connection to the database.
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
  • Do any database operation such as select, insert, update and delete.
  • Finally don’t forget to close the Connection object. We usually do this in the finally block of the try-catch block.
if (connection != null) {
    connection.close();
}

Here is the complete code snippet.

package org.kodejava.example.sql;

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

public class ConnectionSample {
    public static final String URL = "jdbc:mysql://localhost/kodejava";
    public static final String USERNAME = "kodejava";
    public static final String PASSWORD = "kodejava123";

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