Java with MySQL: Database Connectivity and Examples

Database Connectivity and Examples

In this tutorial, you will learn how to connect and control the MySQL database using java. We use MySQL Connector/J driver for this tutorial. It’s an official JDBC driver for MySQL.

Tutorial 1: Java Tutorial: How to connect a MySQL database and Java

What is Java?

Java is a general-purpose, open-source and free programming language. It originally developed by Sun Microsystems in 1995 and more than 3 billion devices run Java. It is used to creating Desktop applications, Mobile applications, Web applications, Web servers, Games, and more. Java works on a variety of platforms. such as Windows, Mac, Linux and more. Java is easy to learn and simple to use.

Java Tutorial: Introduction for beginners (Basic)
Java Tutorial: Introduction for beginners – Part 2 (Basic)
Java Tutorial: Most Popular Java IDE in 2019
SQL Tutorial: Introduction for beginners (Basic)

How to install IntelliJ IDEA on Windows (2019)

Open a Current Project (tutorial_db)

java open current project - IntelliJ IDEA
java open current project - IntelliJ IDEA

How to connect a MySQL database and Java

Create Connection (Tutorial 1)

Connect.java

package com.ctechf;

//import class
import java.sql.*;
public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306";
        String user = "root";
        String pass = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);

            System.out.println("Connection Successful");
      
            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Main.java

public class Main {
    public static void main(String[] args) {
        DBConnect connect = new DBConnect();
    }
}

Java/MySQL – Create Database

...
//Create Database
String sql = "CREATE DATABASE db_java";
stmt.executeUpdate(sql);
...

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306";
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Create Database
            String sql = "CREATE DATABASE db_java";
            stmt.executeUpdate(sql);

            System.out.println("Connection Successful");

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

connect a MySQL database and java

Java/MySQL – Create Table

...
//Create Table "Users"
stmt = conn.createStatement();
String sql = "CREATE TABLE USERS " +
             "(id INTEGER not NULL, " +
             " first VARCHAR(50), " +
             " last VARCHAR(50), " +
             " contact VARCHAR(20), " +
                " PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
...
java with mysql - create table

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);

            //Create Table "Users"
            stmt = conn.createStatement();
            String sql = "CREATE TABLE USERS " +
                    "(id INTEGER not NULL, " +
                    " first VARCHAR(50), " +
                    " last VARCHAR(50), " +
                    " contact VARCHAR(20), " +
                    " PRIMARY KEY ( id ))";
            stmt.executeUpdate(sql);
            System.out.println("Table Created Successful...");

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

create table

Java/MySQL – Insert Records

...
//Inserting records.
stmt = conn.createStatement();
String sql = "INSERT INTO Users VALUES (1, 'Dean', 'Winchester', '123456789')";
stmt.executeUpdate(sql);
            
sql = "INSERT INTO Users VALUES (2, 'Sam', '', '457-457-8741')";
stmt.executeUpdate(sql);

sql = "INSERT INTO Users VALUES (3, 'Cas', 'Ang', '')";
stmt.executeUpdate(sql);
            
sql = "INSERT INTO Users VALUES(4, 'Kol', 'el', '0')";
stmt.executeUpdate(sql);
...
java with mysql - insert data

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);

            //Inserting records.
            stmt = conn.createStatement();
            String sql = "INSERT INTO Users " +
                    "VALUES (1, 'Dean', 'Winchester', '123456789')";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO Users " +
                    "VALUES (2, 'Sam', '', '457-457-8741')";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO Users " +
                    "VALUES (3, 'Cas', 'Ang', '')";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO Users " +
                    "VALUES(4, 'Kol', 'el', '0')";
            stmt.executeUpdate(sql);
            System.out.println("Data Inserted Successful...");

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

insert data

Java/MySQL – Show Table Data

...
//Show records.
String sql = "SELECT * FROM Users";
rs = stmt.executeQuery(sql);
while(rs.next()){
    //Retrieve by column name
    int id  = rs.getInt("id");
    String firstName = rs.getString("first");
    String lastName = rs.getString("last");
    String contactNo = rs.getString("contact");

    //Display values
    System.out.print("ID: " + id);
    System.out.print(", Name: " + firstName + " " + lastName);
    System.out.println(", Contact No: " + contactNo);
}
...
java with mysql - show table data

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Show records.
            String sql = "SELECT * FROM Users";
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                String firstName = rs.getString("first");
                String lastName = rs.getString("last");
                String contactNo = rs.getString("contact");

                //Display values
                System.out.print("ID: " + id);
                System.out.print(", Name: " + firstName + " " + lastName);
                System.out.println(", Contact No: " + contactNo);
            }
            rs.close();

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

output - show table data

WHERE Clause

...
//Show records using where.
String sql = "SELECT * FROM Users WHERE id > 2 ";
rs = stmt.executeQuery(sql);

while(rs.next()){
     //Retrieve by column name
     int id  = rs.getInt("id");
     String firstName = rs.getString("first");
     String lastName = rs.getString("last");
     String contactNo = rs.getString("contact");

     //Display values
     System.out.print("ID: " + id);
     System.out.print(", Name: " + firstName + " " + lastName);
     System.out.println(", Contact No: " + contactNo);
}
...
java with mysql - show data

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Show records using where.
            String sql = "SELECT * FROM Users WHERE id > 2 ";
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                String firstName = rs.getString("first");
                String lastName = rs.getString("last");
                String contactNo = rs.getString("contact");

                //Display values
                System.out.print("ID: " + id);
                System.out.print(", Name: " + firstName + " " + lastName);
                System.out.println(", Contact No: " + contactNo);
            }
            rs.close();

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

output - show data

Like Clause

...
//Show records using where and like.
String sql = "SELECT * FROM Users WHERE first LIKE 'D%' ";
rs = stmt.executeQuery(sql);

while(rs.next()){
    //Retrieve by column name
    int id  = rs.getInt("id");
    String firstName = rs.getString("first");
    String lastName = rs.getString("last");
    String contactNo = rs.getString("contact");

    //Display values
    System.out.print("ID: " + id);
    System.out.print(", Name: " + firstName + " " + lastName);
    System.out.println(", Contact No: " + contactNo);
}
...
java with mysql - show data

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Show records using where and like.
            String sql = "SELECT * FROM Users WHERE first LIKE 'D%' ";
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                String firstName = rs.getString("first");
                String lastName = rs.getString("last");
                String contactNo = rs.getString("contact");

                //Display values
                System.out.print("ID: " + id);
                System.out.print(", Name: " + firstName + " " + lastName);
                System.out.println(", Contact No: " + contactNo);
            }
            rs.close();

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

output - show data

Sorting Data – ASC

...
//Sort Records.
String sql = "SELECT id, first, last FROM Users ORDER BY first ASC";
rs = stmt.executeQuery(sql);

while(rs.next()){
   //Retrieve by column name
   int id  = rs.getInt("id");
   String firstName = rs.getString("first");
   String lastName = rs.getString("last");

   //Display values
   System.out.print("ID: " + id);
   System.out.println(", Name: " + firstName + " " + lastName);
}
...
java with mysql - sorting data

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Sort Records.
            String sql = "SELECT id, first, last FROM Users ORDER BY first ASC";
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                String firstName = rs.getString("first");
                String lastName = rs.getString("last");

                //Display values
                System.out.print("ID: " + id);
                System.out.println(", Name: " + firstName + " " + lastName);
            }
            rs.close();

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

output - sorting data

Sorting Data – DESC

...
//Sort Records.
String sql = "SELECT id, first, last FROM Users ORDER BY first DESC";
rs = stmt.executeQuery(sql);

while(rs.next()){
   //Retrieve by column name
   int id  = rs.getInt("id");
   String firstName = rs.getString("first");
   String lastName = rs.getString("last");

   //Display values
   System.out.print("ID: " + id);
   System.out.println(", Name: " + firstName + " " + lastName);
}
...
java with mysql - sorting data

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Sort Records.
            String sql = "SELECT id, first, last FROM Users ORDER BY first DESC";
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                String firstName = rs.getString("first");
                String lastName = rs.getString("last");

                //Display values
                System.out.print("ID: " + id);
                System.out.println(", Name: " + firstName + " " + lastName);
            }
            rs.close();

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

output - sorting data

Java/MySQL – Update Table Data

...
//Update table data
String sql = "UPDATE Users SET first = 'ellen' WHERE id in (2, 4)";
stmt.executeUpdate(sql);
...

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Update table data
            String sql = "UPDATE Users SET first = 'ellen' WHERE id in (2, 4)";
            stmt.executeUpdate(sql);

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

update table data

Java/MySQL – Delete Table Data

...
//Delete table data
String sql = "DELETE FROM Users WHERE id = 4";
stmt.executeUpdate(sql);
...

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Delete table data
            String sql = "DELETE FROM Users WHERE id = 4";
            stmt.executeUpdate(sql);

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Output:

delete table data

Java/MySQL – Drop

Drop-Table

...
//Drop table
String sql = "DROP TABLE Users";
stmt.executeUpdate(sql);
...

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Drop table
            String sql = "DROP TABLE Users";
            stmt.executeUpdate(sql);

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Drop Database

...
//Drop database
String sql = "DROP DATABASE db_java";
stmt.executeUpdate(sql);
...

Connect.java

package com.ctechf;

//import class
import java.sql.*;

public class DBConnect {
    private Connection conn;
    private Statement stmt;

    public DBConnect() {
        String url = "jdbc:mysql://localhost:3306/db_java"; //Database -> db_java
        String user = "root";
        String pass = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            stmt = conn.createStatement();

            //Drop database
            String sql = "DROP DATABASE db_java";
            stmt.executeUpdate(sql);

            stmt.close();
            conn.close();
        }catch (Exception ex){
            System.out.println("Error" + ex);
        }
    }
}

Hello Guys,

Hope this post will help you to learn something, If you enjoy my work then please share my posts with your friends and anyone who might be interested in programming and don’t forget to subscribe my mailing list.

If you had any problem with this post, please do mention it in the comment section.

Like ctechf Facebook fan page: https://www.facebook.com/ctechf

Best Free eBooks to Learn Python Programming

Free eBooks to Learn Python

Free eBooks to Learn Python, Here are 13 of the best Python eBooks for python lovers.
Download Now

What is Python?

Python is a scripting language and that is used for creating engineering-analysis tools, web development (server-side), software development, system scripting, and animation software. It designed by Guido van Rossum and first released in 1991. It’s an interpreted, High-level. General-purpose language. Python runs on Windows, Mac OS, and Linux.

Python Tutorial: Introduction for Beginners (Part 1)
Python Tutorial: Introduction for Beginners (Part 2)
Python Tutorial: How to Connect MySQL Database in Python
Python Tutorial: Python GUI Programming (Tkinter)
Python Tutorial: Send Emails Using Python
Python Tutorial: Django Tutorial for Beginners
Python Tutorial: Parsing command-line arguments

How to be a Programmer

Liked it? Take a second to support CtechF on Patreon!

Leave a Reply

2 Shares
Share
Tweet
Share
Pin
Share