How to connect to MySQL database in Java

First of all, since it is a version 8 database, the writing method of the configuration class is different from that of version 5. Please note that when using idea or eclipse at the same time, you need to import the jar package

If you want to download For jar packages with 8 different versions, you only need to modify 8.0.28 to the specified version.

The method of idea to import jar package is as follows:

##Then comes the code part. First create the table:

CREATE TABLE `train_message` (
                                 `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                 `train_name` varchar(20) NOT NULL COMMENT '列车名',
                                 `origin` varchar(30) NOT NULL COMMENT '始发地',
                                 `terminal` varchar(30) NOT NULL COMMENT '终到地',
                                 `departure_time` timestamp NOT NULL COMMENT '出站时间',
                                 `state` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '正常' COMMENT '列车状态',
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3

Then create the connected configuration class DbConfig.java, localhost is the ip address of the machine, If there is a server, fill in the IP address of the server. message is the name of the database. Here is a picture showing the name that many novices misunderstand

##

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *  数据库配置类
 *  @author 景苒
 */
public class DbConfig {
    public Connection dbConfig() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e) {
            System.out.print("加载驱动失败!");
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/message?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
        String user = "root";
        String password = "123456";
        return DriverManager.getConnection(url, user, password);
    }
}

Then write the main function Main.java. The function body of the main function here can be written at the end. Just open the comment if you need any function. For a quick comment method, select this sentence and press ctrl plus /. All annotated.

import java.sql.SQLException;

/**
 * 主函数，调用功能
 * @author 景苒
 */
public class Main {
    public static void main(String[] args) throws SQLException {
//        new GetMessage().getMessage();
//        new UpdateTrainState().updateTrainState();
//        new InsertTrain().insertTrain();
//        new GetNumber().getNumber();
    }
}

Then there are the functions of each:

1. Query all train information from Shenyang to Wuhan, sorted by departure time

Build the GetMessage.java class

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

/**
 * 查询沈阳到武汉的所有列车信息，按出发时间先后排序
 * @author 景苒
 */
public class GetMessage {
    public void getMessage() throws SQLException {
        Connection con = new DbConfig().dbConfig();
        String sql = "select * from `train_message` where origin = ? and terminal = ? ORDER BY departure_time ASC";
        String origin = "沈阳";
        String terminal = "武汉";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, origin);
        ps.setString(2, terminal);
        ResultSet rs = ps.executeQuery();
        try {
            while (rs.next()) {
                System.out.println("列车名：" + rs.getString("train_name")
                        + " 始发站：" + rs.getString("origin")
                        + " 终到站：" + rs.getString("terminal")
                        + " 出发时间：" + rs.getString("departure_time")
                        + " 列车状态：" + rs.getString("state"));
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ps.close();
            con.close();
        }
    }
}

2. Modify the status of T2255 train to out of service

Create the UpdateTrainState.java class

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 修改T2255列车的状态为停运
 * @author 景苒
 */
public class UpdateTrainState {
    public void updateTrainState() throws SQLException {
        Connection con = new DbConfig().dbConfig();
        String sql = "UPDATE `train_message` SET state = '停运' WHERE train_name = 'T2255'";
        Statement statement = con.createStatement();
        try {
            int i = statement.executeUpdate(sql);
            if (i > 0) {
                System.out.println("更新成功");
            }else {
                System.out.println("更新失败");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            statement.close();
            con.close();
        }
    }
}

3. Add a new train information (enter it yourself)

Build the InsertTrain.java class

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 新增一辆列车信息（自己输入）
 * 始发时间为timestamp类型，输入时需要确保格式正确，如：2019-01-01 00:00:00
 * @author 景苒
 */
public class InsertTrain {
    public void insertTrain() throws SQLException {
        Connection con = new DbConfig().dbConfig();
        Scanner scanner = new Scanner(System.in);
        String sql = "insert into `train_message` values(null, ?, ?, ?, ?, default)";
        System.out.print("请输入列车名：");
        String trainName = scanner.nextLine();
        System.out.print("请输入始发站：");
        String origin = scanner.nextLine();
        System.out.print("请输入终到站：");
        String terminal = scanner.nextLine();
        System.out.print("请输入始发时间：");
        String departureTime = scanner.nextLine();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, trainName);
        ps.setString(2, origin);
        ps.setString(3, terminal);
        ps.setString(4, departureTime);
        try {
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ps.close();
            con.close();
        }
    }
}

4. Query the number of trains with normal status

Build the GetNumber.java class

import java.sql.Statement;

/**
 * 查询状态为正常的列车数量
 * @author 景苒
 */
public class GetNumber {
    public void getNumber() throws SQLException {
        Connection con = new DbConfig().dbConfig();
        String sql = "select count(state) from `train_message` where state = '正常'";
        Statement statement = con.createStatement();
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println("状态为正常的列车数量为：" + resultSet.getInt(1));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            statement.close();
            con.close();
        }
    }
}

Finally, attach the attribute structure diagram and sample of navicat Inserted statements

Just write a few data according to your own needs. The above is the example code for java to connect to mysql database, and eclipse also Much the same, except for the way of importing the jar package.

The above is the detailed content of How to connect to MySQL database in Java. For more information, please follow other related articles on the PHP Chinese website!