Objective and Prerequisites

Objective: This segment introduces implementation of how to create a database and table in MySQL using Java.

Prerequisites: MySQL setup is installed and running in the background. Also, the project has been developed and executed in Eclipse IDE (Integrated Development Environment).

To run the following program, you need to download the JDBC driver jar file Download JDBC driver. Once downloaded, you can import the library by right-clicking on your project in Eclipse -> Properties -> Java Build Path -> Libraries -> Add External JARs...

Start MySQL
kodingwindow@kw:~$ sudo mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'kodingwindow';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)
Java MySQL to connect, create database, and table
KW.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

class KW
{
    public static void main(String[] args) 
    {
        String dbname="DB";
        try
        {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306","root", "kodingwindow");
            System.out.println("Connection Established Successfully");
            Statement st = con.createStatement();
            st.executeUpdate("DROP DATABASE IF EXISTS "+ dbname + "");
            st.executeUpdate("CREATE DATABASE "+ dbname + "");
            System.out.println("Database Created");
            
            con.close();
            System.out.println("Connection Released Successfully");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
}
Output
kodingwindow@kw:~$ javac KW.java
kodingwindow@kw:~$ java -cp mysql-connector-java-8.0.30.jar: KW
Connection Established Successfully
Database Created
Connection Released Successfully
kodingwindow@kw:~$
Java MySQL to connect, create database, and table
KW.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

class KW
{
    public static void main(String[] args) 
    {
        try
        {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/DB","root", "kodingwindow");
            Statement st = con.createStatement();
            st.executeUpdate("DROP TABLE IF EXISTS holders");
            st.executeUpdate("CREATE TABLE holders(account_no BIGINT PRIMARY KEY, name VARCHAR(30) NOT NULL, bank VARCHAR(10), amount BIGINT NOT NULL)");
            System.out.println("Table Created");
            
            ResultSet rs=st.executeQuery("SHOW TABLES");  
            System.out.println("List of Tables");
            while(rs.next())
            {
                System.out.println(rs.getString("tables_in_db"));
            }
            con.close();
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
}
Output
kodingwindow@kw:~$ javac KW.java
kodingwindow@kw:~$ java -cp mysql-connector-java-8.0.30.jar: KW
Table Created
List of Tables
holders
kodingwindow@kw:~$
MySQL Instance
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE DB;
Database changed

mysql> SHOW TABLES;
+--------------+
| Tables_in_db |
+--------------+
| holders      |
+--------------+
1 row in set (0.00 sec)

mysql> DESC holders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| account_no | bigint      | NO   | PRI | NULL    |       |
| name       | varchar(30) | NO   |     | NULL    |       |
| bank       | varchar(10) | YES  |     | NULL    |       |
| amount     | bigint      | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.33 sec)
What Next?
Java MySQL to insert and retrieve the records from a table Java MySQL to update the table records Java MySQL to perform JDBC CRUD operations
Advertisement