Java操作mysql数据库

Java操作mysql数据库

打开IDEA创建新的项目

选择Intellij
创建后的默认程序

寻找JDBC的JAR包

打开Maven网站
https://mvnrepository.com/
搜索

mysql-connector-j

点击后进入下面界面

选择8.3.0版本

选择下载JAR包

创建lib文件夹
New>Directory>lib

粘贴jar包到lib
右键lib>Open in >Explorer

添加后右键mysql-connector-j-8.3.0.jar>add as libraries

创建example数据库

CREATE DATABASE IF NOT EXISTS example;
USE example;
CREATE TABLE IF NOT EXISTS user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);
INSERT INTO user (name, password) VALUES ("张三", "123456");

连接mysql

创建MysqlConnection类

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

public class MysqlConnection {
    public static final String URL = "jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";
	//jdbc:mysql是固定写法
	//localhost:3306是数据库地址和端口号
    //example是数据库名称
    //useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC是连接参数
    //useUnicode=true表示使用Unicode字符集
    //characterEncoding=utf-8表示使用UTF-8字符集
    //useSSL=false表示不使用SSL连接
    //serverTimezone=UTC表示使用UTC时区
    public static final String USER = "root";

    public static final String PASSWORD = "123456";

   	public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static String test
    .Connection() {
        try{
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            return "Connection failed:"+e;
        }
        return "Connection success";
    }


}

修改Main类

public class Main {
    public static void main(String[] args){
        System.out.println(MysqlConnection.testConnection());
    }
}

结果

查询数据库

修改Main

import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args){
//        System.out.println(MysqlConnection.testConnection());
        List<Map<String, String>> dataList = MysqlConnection.selectAll("user", List.of("id", "name", "password"));

        for (Map<String, String> dataMap : dataList) {
            System.out.println("查询结果:");
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                System.out.println(entry.getKey() + ": " + entry.getValue());
            }
            System.out.println();

        }
    }
}

修改MysqlConnection

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MysqlConnection {
    public static final String URL = "jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";

    public static final String USER = "root";

    public static final String PASSWORD = "123456";
    public static String selectAll = "SELECT * FROM ?";
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static String testConnection() {
        try{
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            return "Connection failed:"+e;
        }
        return "Connection success";
    }
    public static List<Map<String, String>> selectAll(String tableName, List<String> fieldName) {
        List<Map<String, String>> selectList = new ArrayList<>();
        String selectAll = "SELECT " + String.join(", ", fieldName) + " FROM " + tableName;

        try {
            Connection conn = getConnection();
            PreparedStatement statement = conn.prepareStatement(selectAll);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                Map<String, String> row = new HashMap<>();
                for (String field : fieldName) {
                    row.put(field, rs.getString(field));
                }
                selectList.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return selectList;
    }
}

插入

修改Main

import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args){
//        System.out.println(MysqlConnection.testConnection());

        List<Map<String, String>> dataList = List.of(
                Map.of("name", "test", "password", "123456"),
                Map.of("name", "test2", "password", "123456")
        );
        String result = MysqlConnection.insert("user", dataList);
        System.out.println(result);

        List<Map<String, String>> select_dataList = MysqlConnection.selectAll("user", List.of("id", "name", "password"));

        for (Map<String, String> dataMap : select_dataList) {
            System.out.println("查询结果:");
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                System.out.println(entry.getKey() + ": " + entry.getValue());
            }
            System.out.println();

        }
    }
}

修改MysqlConnection

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MysqlConnection {
    public static final String URL = "jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";

    public static final String USER = "root";

    public static final String PASSWORD = "123456";
    public static String selectAll = "SELECT * FROM ?";
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static String testConnection() {
        try{
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            return "Connection failed:"+e;
        }
        return "Connection success";
    }
    public static List<Map<String, String>> selectAll(String tableName, List<String> fieldName) {
        List<Map<String, String>> selectList = new ArrayList<>();
        String selectAll = "SELECT " + String.join(", ", fieldName) + " FROM " + tableName;

        try {
            Connection conn = getConnection();
            PreparedStatement statement = conn.prepareStatement(selectAll);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                Map<String, String> row = new HashMap<>();
                for (String field : fieldName) {
                    row.put(field, rs.getString(field));
                }
                selectList.add(row);
            }
            conn.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return selectList;
    }
    public static String insert(String tableName, List<Map<String, String>> dataList) {
        if (dataList.isEmpty()) {
            return "没有数据需要插入";
        }

        Map<String, String> firstRow = dataList.getFirst();
        StringBuilder columns = new StringBuilder();
        for (String column : firstRow.keySet()) {
            if (!columns.isEmpty()) {
                columns.append(", ");
            }
            columns.append(column);
        }
        StringBuilder values = new StringBuilder();
        for (Map<String, String> data : dataList) {
            if (!values.isEmpty()) {
                values.append(", ");
            }
            values.append("(");
            for (String value : data.values()) {
                values.append("'").append(value).append("', ");
            }
            values.setLength(values.length() - 2); // 移除最后一个逗号和空格
            values.append(")");
        }

        String sql = "INSERT INTO " + tableName + " (" + columns.toString() + ") VALUES " + values.toString();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                return "成功插入 " + affectedRows + " 行";
            } else {
                return "插入失败，没有行受影响";
            }
        } catch (SQLException e) {
            return "插入失败: " + e.getMessage();
        }
    }
}

结果：

更新

修改Main

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args) throws SQLException {

        List<Map<String, String>> dataList = List.of(
                Map.of("name", "aaa", "password", "12345678")
        );
        System.out.println(MysqlConnection.update("user", dataList.getFirst(), "id = 1"));
        List<Map<String, String>> select_dataList = MysqlConnection.selectAll("user", List.of("id", "name", "password"));

        for (Map<String, String> dataMap : select_dataList) {
            System.out.println("查询结果:");
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                System.out.println(entry.getKey() + ": " + entry.getValue());
            }
            System.out.println();

        }
    }
}

修改MysqlConnection

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class MysqlConnection {
    public static final String URL = "jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";

    public static final String USER = "root";

    public static final String PASSWORD = "123456";
    public static String selectAll = "SELECT * FROM ?";
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static String testConnection() {
        try{
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            return "Connection failed:"+e;
        }
        return "Connection success";
    }
    public static List<Map<String, String>> selectAll(String tableName, List<String> fieldName) {
        List<Map<String, String>> selectList = new ArrayList<>();
        String selectAll = "SELECT " + String.join(", ", fieldName) + " FROM " + tableName;

        try {
            Connection conn = getConnection();
            PreparedStatement statement = conn.prepareStatement(selectAll);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                Map<String, String> row = new HashMap<>();
                for (String field : fieldName) {
                    row.put(field, rs.getString(field));
                }
                selectList.add(row);
            }
            conn.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return selectList;
    }
    public static String insert(String tableName, List<Map<String, String>> dataList) {
        if (dataList.isEmpty()) {
            return "没有数据需要插入";
        }

        Map<String, String> firstRow = dataList.getFirst();
        StringBuilder columns = new StringBuilder();
        for (String column : firstRow.keySet()) {
            if (!columns.isEmpty()) {
                columns.append(", ");
            }
            columns.append(column);
        }
        StringBuilder values = new StringBuilder();
        for (Map<String, String> data : dataList) {
            if (!values.isEmpty()) {
                values.append(", ");
            }
            values.append("(");
            for (String value : data.values()) {
                values.append("'").append(value).append("', ");
            }
            values.setLength(values.length() - 2); // 移除最后一个逗号和空格
            values.append(")");
        }

        String sql = "INSERT INTO " + tableName + " (" + columns.toString() + ") VALUES " + values.toString();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                return "成功插入 " + affectedRows + " 行";
            } else {
                return "插入失败，没有行受影响";
            }
        } catch (SQLException e) {
            return "插入失败: " + e.getMessage();
        }
    }

    public static int update(String tableName, Map<String, String> updateData, String condition) throws SQLException {

        String setClause = updateData.keySet().stream()
                .map(key -> key + " = ?")
                .collect(Collectors.joining(", "));

        String sql = "UPDATE " + tableName + " SET " + setClause + " WHERE " + condition;

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            int index = 1;
            for (String value : updateData.values()) {
                pstmt.setString(index++, value);
            }
            return pstmt.executeUpdate();
        }
    }
}

结果:

删除

修改Main

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args) throws SQLException {

        MysqlConnection.delete("user", "1");
        List<Map<String, String>> select_dataList = MysqlConnection.selectAll("user", List.of("id", "name", "password"));

        for (Map<String, String> dataMap : select_dataList) {
            System.out.println("查询结果:");
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                System.out.println(entry.getKey() + ": " + entry.getValue());
            }
            System.out.println();

        }
    }
}

修改MysqlConnection

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class MysqlConnection {
    public static final String URL = "jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";

    public static final String USER = "root";

    public static final String PASSWORD = "123456";
    public static String selectAll = "SELECT * FROM ?";
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    public static String testConnection() {
        try{
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            return "Connection failed:"+e;
        }
        return "Connection success";
    }
    public static List<Map<String, String>> selectAll(String tableName, List<String> fieldName) {
        List<Map<String, String>> selectList = new ArrayList<>();
        String selectAll = "SELECT " + String.join(", ", fieldName) + " FROM " + tableName;

        try {
            Connection conn = getConnection();
            PreparedStatement statement = conn.prepareStatement(selectAll);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                Map<String, String> row = new HashMap<>();
                for (String field : fieldName) {
                    row.put(field, rs.getString(field));
                }
                selectList.add(row);
            }
            conn.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return selectList;
    }
    public static String insert(String tableName, List<Map<String, String>> dataList) {
        if (dataList.isEmpty()) {
            return "没有数据需要插入";
        }

        Map<String, String> firstRow = dataList.getFirst();
        StringBuilder columns = new StringBuilder();
        for (String column : firstRow.keySet()) {
            if (!columns.isEmpty()) {
                columns.append(", ");
            }
            columns.append(column);
        }
        StringBuilder values = new StringBuilder();
        for (Map<String, String> data : dataList) {
            if (!values.isEmpty()) {
                values.append(", ");
            }
            values.append("(");
            for (String value : data.values()) {
                values.append("'").append(value).append("', ");
            }
            values.setLength(values.length() - 2); // 移除最后一个逗号和空格
            values.append(")");
        }

        String sql = "INSERT INTO " + tableName + " (" + columns.toString() + ") VALUES " + values.toString();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                return "成功插入 " + affectedRows + " 行";
            } else {
                return "插入失败，没有行受影响";
            }
        } catch (SQLException e) {
            return "插入失败: " + e.getMessage();
        }
    }

    public static int update(String tableName, Map<String, String> updateData, String condition) throws SQLException {

        String setClause = updateData.keySet().stream()
                .map(key -> key + " = ?")
                .collect(Collectors.joining(", "));

        String sql = "UPDATE " + tableName + " SET " + setClause + " WHERE " + condition;

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            int index = 1;
            for (String value : updateData.values()) {
                pstmt.setString(index++, value);
            }
            return pstmt.executeUpdate();
        }
    }


    public static void delete(String tableName, String condition) throws SQLException {

        String sql = "DELETE FROM " + tableName + " WHERE id=" + condition;

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.executeUpdate();
        }
    }
}