java连接mysql增删改查四层结构

Java连接MySQL数据库

1、下载MySQL Connector/J驱动：访问MySQL官网（https://dev.mysql.com/downloads/connector/j/）下载对应版本的MySQL Connector/J驱动。

2、将下载的jar包添加到Java项目的类路径中。

创建数据库连接

1、导入相关包：

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

2、编写方法创建数据库连接：

public static Connection getConnection() {
    Connection connection = null;
    try {
        // 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取数据库连接
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名？useSSL=false&serverTimezone=UTC", "用户名", "密码");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

执行增删改查操作

1、插入数据：

public static void insertData(String name, int age) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = getConnection();
        String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setInt(2, age);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeResource(connection, preparedStatement);
    }
}

2、查询数据：

public static List<Map<String, Object>> queryData() {
    List<Map<String, Object>> resultList = new ArrayList<>();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = getConnection();
        String sql = "SELECT * FROM users";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> rowData = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(metaData.getColumnLabel(i), resultSet.getObject(i));
            }
            resultList.add(rowData);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeResource(connection, preparedStatement, resultSet);
    }
    return resultList;
}

3、更新数据：

public static void updateData(int id, String newName, int newAge) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = getConnection();
        String sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, newName);
        preparedStatement.setInt(2, newAge);
        preparedStatement.setInt(3, id);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeResource(connection, preparedStatement);
    }
}

4、删除数据：

public static void deleteData(int id) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = getConnection();
        String sql = "DELETE FROM users WHERE id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeResource(connection, preparedStatement);
    }
}