How do I create MySQL database programmatically in Java?

By Wayan in JDBC 4 Comments

There are times that you might need to create a 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 a new database if there is no database with demodb name 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.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 = "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.

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I register out parameter in CallableStatement?

By Wayan in JDBC 0 Comment

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

import java.sql.*;

public class RegisterOutParameter {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

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

            // Sets the input parameter
            cb.setString(1, "Data Structures, Algorithms");

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

            // Executes the query
            cb.executeQuery();

            // Gets the query result output
            System.out.println("Name  : " + cb.getString(1));
            System.out.println("Code  : " + cb.getString(2));
            System.out.println("Price : " + cb.getBigDecimal(3));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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

DELIMITER ;;
DROP PROCEDURE IF EXISTS `Get_Product_Detail_By_Name`;;
CREATE PROCEDURE `Get_Product_Detail_By_Name`(
    INOUT product_name VARCHAR(100),
    OUT product_code VARCHAR(20),
    OUT product_price DECIMAL(10, 2))
BEGIN
    SELECT code INTO product_code FROM product WHERE name = product_name;

    SELECT price INTO product_price FROM product WHERE name = product_name;
END;;
DELIMITER ;

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I limit MySQL query result?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class SqlLimitExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create PreparedStatement to get all data from a database.
            String query = "select count(*) from product";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet result = ps.executeQuery();

            int total = 0;
            while (result.next()) {
                total = result.getInt(1);
            }

            System.out.println("Total number of data in database: " +
                               total + "\n");

            // Create PreparedStatement to the first 5 records only.
            query = "select * from product limit 5";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("Result fetched with specified limit 5");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                                   ", code:" + result.getString("code") +
                                   ", name:" + result.getString("name") +
                                   ", price:" + result.getString("price"));
            }

            // Create PreparedStatement to get data from the 4th
            // record (remember the first record is 0) and limited
            // to 3 records only.
            query = "select * from product limit 3, 3";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("\nResult fetched with specified limit 3, 3");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                                   ", code:" + result.getString("code") +
                                   ", name:" + result.getString("name") +
                                   ", price:" + result.getString("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

An example result of our program is:

Total number of data in database: 9

Result fetched with specified limit 5
====================================
id:1, code:P0000001, name:UML Distilled 3rd Edition, price:25.00
id:3, code:P0000003, name:PHP Programming, price:20.00
id:4, code:P0000004, name:Longman Active Study Dictionary, price:40.00
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00

Result fetched with specified limit 3, 3
====================================
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I get system functions supported by database?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class SystemFunction {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            DatabaseMetaData meta = connection.getMetaData();

            // Get system functions supported by database
            String[] functions = meta.getSystemFunctions().split(",\\s*");

            for (String function : functions) {
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here are MySQL database supported system functions.

Function = DATABASE
Function = USER
Function = SYSTEM_USER
Function = SESSION_USER
Function = PASSWORD
Function = ENCRYPT
Function = LAST_INSERT_ID
Function = VERSION

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I get date time functions supported by database?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class DateTimeFunction {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            DatabaseMetaData meta = connection.getMetaData();

            // Get date and time functions supported by database
            String[] functions = meta.getTimeDateFunctions().split(",\\s*");

            for (String function : functions) {
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Date and time functions supported by MySQL database.

Function = DAYOFWEEK
Function = WEEKDAY
Function = DAYOFMONTH
Function = DAYOFYEAR
Function = MONTH
Function = DAYNAME
Function = MONTHNAME
Function = QUARTER
Function = WEEK
Function = YEAR
Function = HOUR
Function = MINUTE
Function = SECOND
Function = PERIOD_ADD
Function = PERIOD_DIFF
Function = TO_DAYS
Function = FROM_DAYS
Function = DATE_FORMAT
Function = TIME_FORMAT
Function = CURDATE
Function = CURRENT_DATE
Function = CURTIME
Function = CURRENT_TIME
Function = NOW
Function = SYSDATE
Function = CURRENT_TIMESTAMP
Function = UNIX_TIMESTAMP
Function = FROM_UNIXTIME
Function = SEC_TO_TIME
Function = TIME_TO_SEC

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central