Implementing Java Connection to MySQL Database and JDBC Interaction Example Based on IDEA from Scratch

Objective#

Implement Java and database interaction, including configuring SQL environment, Java libraries, and implementing a simple example.

Environment Configuration#

Host Configuration: IntelliJ IDEA, SDK18, Windows11

Download:
Connector/J
System Selection: Platform Independent
connectorJ

MySQL Community 8.0
Note: Choose mysql-installer-community, not mysql-installer-web-community
MySQLCommunity

After downloading MySQLCommunity, install only the server. Remember the information and set up the database account (in this example, <username>, <password>). The image below shows a successful installation, which may differ from the installation process.

mysqlInstaller

IDEA Configuration#

MySQL - Create New Data Source for Connection#

After creating a new project, go to the menu bar, select View -> Tool Windows -> Database. Create a new data source and download the MySQL driver. Take note of the class name (in the image, it is com.mysql.cj.jdbc.Driver).
mysqldriver

For the data source, you can use the database account used during MySQL installation. Take note of the URL address.
localhost
Test the connection at the bottom to ensure it is successful before proceeding to the next step.

Import Connector/J into Project Library#

Go to File -> Project Structure -> Libraries. Add the downloaded Connector/J jar file (after extraction) and click OK.
projectStructure

Implementation of Interaction#

Database#

Go to the console and enter MySQL commands. You can refer to the MySQL Tutorial: MySQL Database Learning Compendium (From Beginner to Master).

sqlkongzhitai

Enter the following code:

CREATE DATABASE test; #Create a database
USE test; #Select the database for operation
CREATE TABLE IF NOT EXISTS students (   #Create the students table
    id INT NOT NULL PRIMARY KEY ,
    name TEXT NOT NULL ,
    age INT NOT NULL );
INSERT INTO students VALUES (1,'name1',18); #Insert data
INSERT INTO students VALUES (2,'name2',12);
INSERT INTO students VALUES (3,'name3',22);

After running the code, you should see the creation completed.

Java Class#

Create a new Java class. The code is as follows:

import java.sql.*;

public class Test {

    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";//Driver class name
    static final String DB_URL = "jdbc:mysql://localhost:3306";//Database URL address

    static final String USERNAME = "username";//Database username
    static final String PASSWORD = "password";//Database password

    public static void main(String[] args){

        Connection conn = null;
        Statement stat = null;

        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("Connecting...");
            conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD);
            stat = conn.createStatement();

            String sql = "SELECT * FROM test.students";//MySQL statement here
            ResultSet rs = stat.executeQuery(sql);

            while(rs.next()){//Loop through the result set
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("id = " + id);
                System.out.println("name = " + name);
                System.out.println("age = " + age);
            }

            rs.close();//Release resources
            stat.close();
            conn.close();

        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                if (stat!=null) stat.close();
            } catch (SQLException ignored) {
            } try {
                if (conn!=null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
            System.out.println("Success!");
        }
    }
}

Output:

Connecting...
id = 1
name = name1
age = 18
id = 2
name = name2
age = 12
id = 3
name = name3
age = 22
Success!