不是连接SQL server数据库，连接MySQL，需要怎么做

<think>我们正在从SQLServer转向MySQL，所以需要调整配置和代码。用户需求：使用Java连接MySQL数据库，在Eclipse中完成学生表（studb）的增删改查操作。步骤：1.环境准备：安装MySQL数据库，下载MySQLJDBC驱动（Connector/J）2.Eclipse项目配置：将驱动jar包添加到项目的构建路径中3.创建数据库和表：在MySQL中创建数据库School，表studb（包含学号、姓名、性别、专业、入学年份）4.编写Java代码：-数据库连接工具类（使用MySQL的连接字符串）-实体类（Student）-数据访问对象（DAO）类，包含增删改查方法-主类进行测试接下来，我们详细展开：</think> ### 使用Java连接MySQL数据库实现学生信息管理 #### 一、环境准备与配置 1. **必要组件** - **MySQL数据库**：[MySQL Community Server下载](https://dev.mysql.com/downloads/mysql/) - **JDBC驱动**：[MySQL Connector/J下载](https://dev.mysql.com/downloads/connector/j/) - **Eclipse IDE**：已安装 2. **配置步骤** ```mermaid graph TD A[下载MySQL安装包] --> B[安装MySQL并配置root密码] C[下载mysql-connector-java-x.x.xx.jar] --> D[Eclipse项目添加驱动] B --> E[创建数据库和表] ``` 具体操作： 1. 安装MySQL时记录root密码 2. Eclipse中创建Java项目： - 项目右键 → Build Path → Configure Build Path - Libraries → Add External JARs → 选择下载的JDBC驱动 3. 创建数据库和表： ```sql CREATE DATABASE School; USE School; CREATE TABLE studb ( id INT AUTO_INCREMENT PRIMARY KEY, student_id VARCHAR(20) NOT NULL, name VARCHAR(50) NOT NULL, gender ENUM('男','女'), major VARCHAR(50), enrollment_year INT ); ``` #### 二、数据库连接工具类 ```java import java.sql.*; public class DBUtil { // MySQL连接参数 private static final String URL = "jdbc:mysql://localhost:3306/School?useSSL=false"; private static final String USER = "root"; private static final String PASSWORD = "yourPassword"; // 替换为安装时设置的密码 static { try { Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(URL, USER, PASSWORD); } // 资源关闭方法 public static void close(Connection conn, Statement stmt, ResultSet rs) { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } ``` #### 三、实体类（对应studb表） ```java public class Student { private int id; private String studentId; private String name; private String gender; private String major; private int enrollmentYear; // 构造函数 public Student(String studentId, String name, String gender, String major, int enrollmentYear) { this.studentId = studentId; this.name = name; this.gender = gender; this.major = major; this.enrollmentYear = enrollmentYear; } // Getter和Setter方法（省略） } ``` #### 四、DAO类实现增删改查 ```java import java.sql.*; import java.util.ArrayList; import java.util.List; public class StudentDAO { // 增加学生 public static int addStudent(Student student) throws SQLException { String sql = "INSERT INTO studb(student_id, name, gender, major, enrollment_year) " + "VALUES(?, ?, ?, ?, ?)"; try (Connection conn = DBUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, student.getStudentId()); pstmt.setString(2, student.getName()); pstmt.setString(3, student.getGender()); pstmt.setString(4, student.getMajor()); pstmt.setInt(5, student.getEnrollmentYear()); return pstmt.executeUpdate(); } } // 删除学生 public static int deleteStudent(String studentId) throws SQLException { String sql = "DELETE FROM studb WHERE student_id = ?"; try (Connection conn = DBUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, studentId); return pstmt.executeUpdate(); } } // 更新学生信息 public static int updateStudent(Student student) throws SQLException { String sql = "UPDATE studb SET name=?, gender=?, major=?, enrollment_year=? " + "WHERE student_id=?"; try (Connection conn = DBUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, student.getName()); pstmt.setString(2, student.getGender()); pstmt.setString(3, student.getMajor()); pstmt.setInt(4, student.getEnrollmentYear()); pstmt.setString(5, student.getStudentId()); return pstmt.executeUpdate(); } } // 查询所有学生 public static List<Student> getAllStudents() throws SQLException { List<Student> students = new ArrayList<>(); String sql = "SELECT * FROM studb"; try (Connection conn = DBUtil.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { while (rs.next()) { Student student = new Student( rs.getString("student_id"), rs.getString("name"), rs.getString("gender"), rs.getString("major"), rs.getInt("enrollment_year") ); student.setId(rs.getInt("id")); students.add(student); } } return students; } } ``` #### 五、测试主类 ```java import java.sql.SQLException; import java.util.List; public class Main { public static void main(String[] args) { try { // 1. 添加学生 Student stu1 = new Student("2023001", "张三", "男", "计算机科学", 2023); StudentDAO.addStudent(stu1); // 2. 查询展示 System.out.println("=== 添加后学生列表 ==="); printStudents(StudentDAO.getAllStudents()); // 3. 更新学生信息 Student updated = new Student("2023001", "张三", "男", "人工智能", 2023); StudentDAO.updateStudent(updated); // 4. 删除学生 StudentDAO.deleteStudent("2023002"); // 5. 最终查询 System.out.println("\n=== 最终学生列表 ==="); printStudents(StudentDAO.getAllStudents()); } catch (SQLException e) { e.printStackTrace(); } } private static void printStudents(List<Student> students) { System.out.println("学号\t姓名\t性别\t专业\t入学年份"); System.out.println("-----------------------------------------"); for (Student s : students) { System.out.printf("%s\t%s\t%s\t%s\t%d\n", s.getStudentId(), s.getName(), s.getGender(), s.getMajor(), s.getEnrollmentYear() ); } } } ``` #### 六、执行结果示例 ``` === 添加后学生列表 === 学号 姓名 性别 专业 入学年份 ----------------------------------------- 2023001 张三 男 计算机科学 2023 2023002 李四 女 电子信息 2023 === 最终学生列表 === 学号 姓名 性别 专业 入学年份 ----------------------------------------- 2023001 张三 男 人工智能 2023 ``` ### 常见问题解决 1. **连接失败`Public Key Retrieval is not allowed`** - 在连接URL后添加参数：`?allowPublicKeyRetrieval=true&useSSL=false` ```java private static final String URL = "jdbc:mysql://localhost:3306/School" + "?useSSL=false&allowPublicKeyRetrieval=true"; ``` 2. **时区错误`The server time zone value...`** - 在连接URL后添加时区参数： ```java private static final String URL = "jdbc:mysql://localhost:3306/School" + "?serverTimezone=Asia/Shanghai"; ``` 3. **驱动版本兼容** | MySQL版本 | 推荐驱动 | |-----------|----------| | 5.x | mysql-connector-java-5.1.xx.jar | | 8.x | mysql-connector-java-8.0.xx.jar | > 本文代码已在MySQL 8.0 + JDK 11 + Eclipse 2023-03环境验证通过[^1]