Tutorial on how to connect and use MySQL 8.0 in IDEA's Maven project

First, let's take a look at my basic development environment:

Operating system: MacOS 10.13.5 Editor: IDEA 2018.3 Others: MySQL8.0.15, Maven 3.3.9, JDK 1.8

OK, let’s start:

Step 1: Create a new Maven project in IDEA

1. Use the skeleton to create a Maven project. Select: maven-archetype-quickstart

2. Fill in GroupId and ArtifactId

3. The first one selects the folder where Maven is installed, the second one selects conf/settings.xml in the folder where Maven is installed, and the third one will be automatically filled in if localRepository is configured in settings.xml. If not, the default local repository will be displayed.

4. Click Finish to successfully create the Maven project

Step 2: Configure pom.xml

Add the coordinates of the jar package to be used in the warehouse to the tag in pom.xml

1. dom4j jar package coordinates

<dependency>
 <groupId>org.dom4j</groupId>
 <artifactId>dom4j</artifactId>
 <version>2.1.1</version>
</dependency>

2.mysql jar package coordinates

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.13</version>
  <scope>runtime</scope>
</dependency>

Step 3: Create the JDBC.xml configuration file and set

<?xml version='1.0' encoding='UTF-8'?>
<accounts>
  <account>
    <url>jdbc:mysql://localhost:3306/mybase?useSSL=false&amp;serverTimezone=CTT</url>
    <user>root</user>
    <password>123456</password>
  </account>
</accounts>

Create JDBC.xml under src. This xml file contains the information to be used when connecting to the database, including url, root, and password. Because I am using MySQL 8.0, the URL is different from previous versions. Mybase is the name of the database to connect to, and & is the escape character for &.

Step 4: Create JDBCUtils and TestJDBCUtils

Create two files, JDBCUtils.java and TestJDBCUtils.java, in the com.langsin.jdbcutil package

Step 5: Write JDBCUtils and TestJDBCUtils

package com.langsin.jdbcutil;

import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import java.sql.*;

public class JDBCUtils {
 private JDBCUtils {}
 private static Connection con;

 static {
  try {
   //Initialize MySQL Driver class Class.forName("com.mysql.cj.jdbc.Driver");
   //Get the database connection information in the XML file through dom4j SAXReader reader = new SAXReader();
   Document doc = reader.read("src/JDBC.xml");
   Element root = doc.getRootElement();
   Element ele = root.element("account");
   String url = ele.element("url");
   String user = ele.element("user");
   String password = ele.element("password");
   //Connect to database con = DriverManager.getConnection(url, user, password);
  } catch(Exception e) {
   throw new RuntimeException(e + ", database connection failed!");
  }
 }

 public static Connection getConnection() {
  return con;
 }

 public static void close(Connection con, Statement state) {
  if(con != null) {
   try {
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if(state != null) {
   try {
    state.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public static void close(Connection con, Statement state, ResultSet rs) {
  if(con != null) {
   try {
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if(state != null) {
   try {
    state.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }  
  if(rs != null) {
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}
package com.langsin.jdbcutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestJDBCUtils {
 public static void main(String[] args) {
  Connection con = JDBCUtils.getConnection();
  String sql = "SELECT * FROM sort";
  //Create a PreparedStatement object and send the sql statement to the database PreparedStatement pst = con.prepareStatement(sql);
  //Get the result set after execution ResultSet rs = pst.executeQuery();
  // Output all data in the second column of the sort table while(rs.next()) {
   System.out.println(rs.getString(2));
  }
  JDBCUtils.close(con, pst, rs);
 }
}

Well, now as long as we execute the program, the console will output the results we want.

Summarize

The above is a tutorial on how to connect and use MySQL 8.0 in the IDEA Maven project. I hope it will be helpful to you!

You may also be interested in:

IDEA uses properties configuration file to connect to MySQL database
Detailed diagram of IntelliJ IDEA connecting to MySQL database
Detailed explanation of how to connect to MySQL database using Java in IntelliJ IDEA
IDEA complete code to connect to MySQL database and perform query operations

<<: Detailed tutorial on installing qt5.12.8 and environment configuration on ubuntu18.04

>>: Detailed explanation of custom swiper component in JavaScript