[大数据]JDBC连接数据库Mysql（附增删改查）

package com.example.java_algorithms;

import com.mysql.cj.jdbc.Driver;
import io.swagger.models.auth.In;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Logger;
/*
SQL脚本：
CREATE DATABASE `monitor` DEFAULT CHARACTER SET utf8 ;
        USE `monitor`;
        DROP TABLE IF EXISTS `t_user`;
        CREATE TABLE `t_user` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `email` varchar(200) DEFAULT NULL COMMENT 'QQ邮箱',
        `password` varchar(50) DEFAULT NULL COMMENT '密码',
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
        insert  into `t_user`(`id`,`email`,`password`) values (1,'123456','1234564'),(2,'10000','141414'),(3,'小环','123456'),(4,'小环','123456');
 */
/**
 * 连接数据库 导包：mysql-connector-java
 */
public class JDBC {
    /**
     * 连接数据库
     * @return
     */
    public static Connection connectMysql() {
        /*
        定义连接数据库需要的东西
         */
        //定义url
        String url="jdbc:mysql://localhost:3306/monitor?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        //定义驱动
        String driverName="com.mysql.cj.jdbc.Driver";
        //用户名
        String username="root";
        //密码
        String password="123456";
        /*
        1. 加载驱动
         */
        try {
            Class.forName(driverName);
            //成功后，则连接数据库.输入url,用户名、密码
            Connection connection=DriverManager.getConnection(url,username,password);
            System.out.println("数据库连接成功！");
            return connection;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();//在命令行打印异常信息在程序中出错的位置及原因
            System.out.println("数据库连接失败！");
        }catch (SQLException e){
            e.printStackTrace();
            System.out.println("数据库连接失败！");
        }
        return null;
    }
    /**
     * 数据库的增删改查语句
     */
    /**
     *查询所有
     */
    static List<HashMap<String,Object>> selectAllUser(Connection connection){
        String sql="SELECT id,email,password FROM t_user";

        try {
            //执行
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            System.out.println("执行成功：");
            //执行SQL语句
            //定义接收的对象
            ResultSet resultSet=preparedStatement.executeQuery(sql);
            //定义数组接收遍历的对象,每一个元素类型--HashMap对象
            List<HashMap<String,Object>> hashMapList=new ArrayList<>();
            while (resultSet.next()){
                HashMap<String,Object> hashMap=new HashMap<>();
                hashMap.put("id",resultSet.getInt("id"));
                hashMap.put("email",resultSet.getString("email"));
                hashMap.put("password",resultSet.getString("password"));
                hashMapList.add(hashMap);
            }
            return hashMapList;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 增
     */
    public static void addUser(Connection connection,HashMap<String,Object>hashMap){
        String sql = "INSERT INTO t_user(email,password) VALUES(?,?)";
        try {
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,hashMap.get("email").toString());
            preparedStatement.setString(2,hashMap.get("password").toString());
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除
     * @param
     *
     */

    public static void delete(Connection connection,String email){
        String sql ="DELETE FROM t_user WHERE email=?";
        /*
        DELETE FROM t_user;删除所有
        TRUNCATE t_user;(id重新自增)
         */
        try {
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,email);
            int i=preparedStatement.executeUpdate();
            if(i>0) {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 改
     */
    public static void updateUser(Connection connection,String email,String password){
        String sql="UPDATE t_user SET password=? WHERE email =?";
        try {
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,password);
            preparedStatement.setString(2,email);
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        //连接数据库
        Connection connection=connectMysql();
        //查询t_user表中的所有信息
        List<HashMap<String,Object>> hashMapList=selectAllUser(connection);
        System.out.println("查询所有："+hashMapList);
        //增加数据
        HashMap<String,Object> addHashMap=new HashMap<>();
        addHashMap.put("email","小红");
        addHashMap.put("password","123456");
        addUser(connection,addHashMap);
        //删除
        delete(connection,"小环");
        //修改
        updateUser(connection,"小红","123456789000");
    }
}