java连接mysql8.0.28数据库实例

CREATETABLE `train_message` (
                                 `id`intNOTNULL AUTO_INCREMENT COMMENT'主键id',
                                 `train_name`varchar(20)NOTNULL COMMENT'列车名',
                                 `origin`varchar(30)NOTNULL COMMENT'始发地',
                                 `terminal`varchar(30)NOTNULL COMMENT'终到地',
                                 `departure_time`timestampNOTNULL COMMENT'出站时间',
                                 `state`varchar(10)CHARACTERSET utf8 COLLATE utf8_general_ciNOTNULLDEFAULT'正常' COMMENT'列车状态',PRIMARYKEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9DEFAULT CHARSET=utf8mb3

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/**
 *  数据库配置类
 *@author 景苒*/publicclass 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);
    }
}

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

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/**
 * 查询沈阳到武汉的所有列车信息，按出发时间先后排序
 *@author 景苒*/publicclass GetMessage {publicvoid 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();
        }
    }
}

import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;/**
 * 修改T2255列车的状态为停运
 *@author 景苒*/publicclass UpdateTrainState {publicvoid 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();
        }
    }
}

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Scanner;/**
 * 新增一辆列车信息（自己输入）
 * 始发时间为timestamp类型，输入时需要确保格式正确，如：2019-01-01 00:00:00
 *@author 景苒*/publicclass InsertTrain {publicvoid 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();
        }
    }
}

import java.sql.Statement;/**
 * 查询状态为正常的列车数量
 *@author 景苒*/publicclass GetNumber {publicvoid 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();
        }
    }
}