[Practice! ] Java database linkage (Connector / J 8.0.20)

1. Prior knowledge

-[Latest] How to build Java environment on Ubuntu

-[Even beginners can do it! ] How to create a Java environment on Windows 10 (JDK14.0.1)

-[Easy-to-understand explanation! ] How to use Java instance

-[Even beginners can do it! ] How to install Eclipse on Windows 10 (Java environment construction)

-[Even beginners can do it! ] How to install MySQL on Windows 10 (MySQL Server 8.0.20)

As prior knowledge, the contents of the above link are required.

2. Preparation

1. Type cmd in the search box to launch Command Prompt.
2. Login with mysql -u username -p.

test.sql

create database test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use test;

CREATE TABLE test1( 
    id TINYINT ZEROFILL NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY(id));

INSERT INTO `test1`(`name`) VALUES ("test1");
INSERT INTO `test1`(`name`) VALUES ("test2");
INSERT INTO `test1`(`name`) VALUES ("test3");
INSERT INTO `test1`(`name`) VALUES ("test4");

2. Copy the above SQL statement and execute it in the command prompt.
3. Success if Query OK appears as shown in the image.

3. Install Connector

1. Go to MySQL :: Download Connector / J.
2. Select Platform Independent and click the Downloads button.
3. Click No thanks, just start my download..
4. Extract the ZIP file.
5. Make sure the ZIP file is extracted and mysql-connector-java-8.0.20 exists.
6. Move mysql-connector-java-8.0.20 to C: \ Program Files \ MySQL.

4. Java database linkage

1. After restarting Eclipse, select [New] → [Java Project].
2. Enter TestDB for the project name, select JavaSE-1.8 to use the execution environment JRE, and click the Finish button.
3. Select [New (N)] → [Class].
4. Enter db in the package and Test1 in the name, check public static void main (String [] args), and click the Finish button.
5. Right-click on the project and select [Build Path (B)] → [Add External Archive (V)].
6. Select mysql-connector-java-8.0.20.jar in C: \ Program Files \ MySQL \ mysql-connector-java-8.0.20.

Test1.java

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test1 {
	public static void main(String[] args) {
		try {
		       Class.forName("com.mysql.cj.jdbc.Driver");
		       Connection conn = DriverManager.getConnection(
		               "jdbc:mysql://localhost/test?characterEncoding=UTF-8&serverTimezone=JST",
		               "test",
		               "test"
		               );
		       Statement st = conn.createStatement();
		       ResultSet rs = st.executeQuery("select * from test1");

		       while(rs.next()) {
		           int id = rs.getInt("id");
		           String name = rs.getString("name");
		           System.out.println("ID："+id+"name:"+name);
		       }
		       rs.close();
		       st.close();
		       conn.close();
		       System.exit(0);
		   } catch (ClassNotFoundException e) {
		       System.out.println("The driver could not be loaded"+ e);
		   } catch (SQLException e) {
		       System.out.println("Database connection error"+ e);
		   }
	}
}

7. Copy the above sentence, specify S-JIS as the character code, and save the file name as Test1.java.
8. Select [Run]-> [Run]-> [Java Application].
9. Success if it looks like the image.

5. Basic way of writing Java database linkage

Java database linkage

public class class name{
    public static void main(String[] args) {
        try {
               //JDBC driver class name
               Class.forName("com.mysql.cj.jdbc.Driver");
               //Get a connection
Connection Connection type variable name= DriverManager.getConnection(
                       "jdbc:mysql:URL",
                       "DB user name",
                       "password"
                       );
               //Get an object of Statement class
Statement Statement type variable name=Connection type variable name.createStatement();
               //Execute SQL
ResultSet ResultSet type variable name=Statement type variable name.executeQuery("SQL statement");

               while(rs.next()) {
                   //SQL display
               }
ResultSet type variable name.close();
Statement type variable name.close();
Connection type variable name.close();
               System.exit(0);
           } catch (ClassNotFoundException e) {
               //Driver loading failure
           } catch (SQLException e) {
               //Database connection failed
           }
    }
}

--Basic Java database linkage is described as above.

6. Related

-[Useful to remember !!!] Easy creation of constructor and getter / setter in Eclipse -[Useful to remember !!!] Easy creation of inherited class in Eclipse -[Useful to remember !!!] Change MySQL character code -[Even beginners can do it! ] How to write Javadoc -[Easy-to-understand explanation! ] How to use Java overload -[Easy-to-understand explanation! ] How to use Java encapsulation -[Easy-to-understand explanation! ] How to use Java inheritance [Override explanation] -[Easy-to-understand explanation! ] Type conversion of reference type in Java -[Easy-to-understand explanation! ] How to use Java polymorphism -[Easy-to-understand explanation! ] How to use ArrayList [Java] -[Practice! ] Introduction of JFrame (explanation up to screen creation)