Connect from Java to MySQL using Eclipse

I will explain how to operate MySQL in Java. This time I will install XAMPP in a Windows environment and use MySQL.

Advance preparation

-Installation of JDK ・ Installation of Eclipse ・ Installation of XAMPP -Preparation of JDBC driver

Eclipse preparation

1. Create a Java project

Create a new project to be used this time with ** "File (F)"> "New (N)"> "Java Project" ** on the upper left. Make the project name easy to understand ** "MysqlTest" **. ** Click "Finish" ** to create.

2. Incorporate the JDBC driver into your project

Once the project is created, right-click on the project and select ** "Build Path (B)"> "Add External Archive (V) ..." ** to select the JDBC driver (mysql-connector-java-8.0). Open .19.jar). It is okay if ** mysql-connector-java-8.0.19.jar ** is added to the "reference library" directly under the project.

MySQL preparation

Start XAMPP and let MySQL ** "Start" **. When you can start, click ** "Shell" ** on the right to start it. After starting, enter the following command to connect to MySQL as the root user. Please refer to this article for password settings.

python

mysql -u root -p

After connecting, create a database with the following command. This time we will create a database named ** "test_db" **.

python

CREATE DATABASE test_db;

If you can create it successfully, connect to the database you just created with the following command.

python

USE test_db;

The flow up to this point is as shown in the image below.

Next, create a table and store the data. This time we will create a table called ** "test" **. Enter the following command. * Don't forget the comma! If you make a typo, interrupt it with ** "\ c" ** and re-enter it.

python

CREATE TABLE test(
    id VARCHAR(3),
    name VARCHAR(10)
);

Once the table is created, we will store the data. Let's store 3 lines of data this time.

python

INSERT INTO test VALUES('001', 'Mandarin orange');
INSERT INTO test VALUES('002', 'Apple');
INSERT INTO test VALUES('003', 'Grape');

The flow up to this point is as shown in the image below.

Let's check if the data is stored properly with the following command.

python

SELECT * FROM test;

MySQL is now ready.

Connect to MySQL from Java

Now you're ready to go. From here, let's create a Java program, access the database created earlier, and display the data in the table.

First, right-click ** "src" ** in the ** "MysqlTest" ** project that you created first, and select "New (W)"> "Package" to select the package "java_mysql" **. Create a.

Next, right-click the package you just created and select ** "New (W)"> "File" ** to create a file called ** "Test.java" **.

If you can do so far, I will write the contents of ** "Test.java" **. I wrote the meaning of the code in the comments.

Test.java

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

public class Test {

	public static void main(String[] args) {
    
    //Variable preparation
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

    //SQL statement creation
		String sql = "SELECT * FROM test";

		try {
			//JDBC driver loading
			Class.forName("com.mysql.cj.jdbc.Driver");
			//Database connection
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db?serverTimezone=JST", "root", "root");
			//SQL execution preparation
			stmt = con.prepareStatement(sql);
			//Get execution result
			rs = stmt.executeQuery();

      //Until there is no data(rs.next()Until becomes false)repeat
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");

				System.out.println(id + ":" + name);
			}
		} catch (ClassNotFoundException e) {
			System.out.println("An error occurred while loading the JDBC driver");
		} catch (SQLException e) {
			System.out.println("There was an error accessing the database.");
		} finally {
			try {
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("There was an error accessing the database.");
			}
		}
	}

}

After writing the code, save it, right-click ** "Test.java" ** and execute it from ** "Run"> "Java Application" **.

If the following is displayed on the console, it is successful.