intellij idea连接MySQL并进行增删改查操作

项目的创建

打开intellij idea创建java工程，在该工程中新建一个文件夹lib，然后复制mysql-connector-java-5.1.36.jar粘贴到lib文件中


鼠标右键选择 Add as Library,把jar包导入到工程中

增删改查操作

数据库已经导入的数据

1.查询

package com.zrgj.util;

import java.sql.*;

public class Test {
    public static void main(String[] args) {

        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&" +
                            "characterEncoding=utf-8&user=" +
                            "root&password=1234");//user为root，密码为1234
            System.out.println("创建连接成功");
            //3.写sql
            String sql = "select * from userinfo";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql得到结果集
            rs = statement.executeQuery();
            //6.处理结果集
            while (rs.next()) {
                System.out.print(rs.getInt(1));
                System.out.print(rs.getString(2));
                System.out.print(rs.getString(3));
            }
            //7.关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }
}

运行结果

2.插入数据

package com.zrgj.util;

import java.sql.*;

public class insert {
    public static void main(String[] args) {
        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&" +
                            "characterEncoding=utf-8&user=" +
                            "root&password=1234");
            System.out.println("创建连接成功");
            //3.写sql
            String sql = "insert into userinfo(username,password) values(?,?)";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql得到结果集
            statement =  connection.prepareStatement(sql);
            //6.处理结果集,插入数据
            statement.setString(1, "Rose");
            statement.setString(2, "123");
            statement.executeUpdate();
            System.out.println("插入成功！");
            //7.关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }
}

3.修改数据

package com.zrgj.util;

import java.sql.*;

public class update {
    public static void main(String[] args) {
        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&" +
                            "characterEncoding=utf-8&user=" +
                            "root&password=1234");
            System.out.println("创建连接成功");
            //3.写sql
            String sql = "update userinfo set username=?,password=? where id=?";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql得到结果集
            statement =  connection.prepareStatement(sql);
            //6.处理结果集,插入数据
            statement.setString(1,"abc");
            statement.setString(2,"789");
            statement.setInt(3,1);
            statement.executeUpdate();
            System.out.println("修改成功！");
            //7.关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }
}

4.删除数据

package com.zrgj.util;

import java.sql.*;

public class delete {
    public static void main(String[] args) {
        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&" +
                            "characterEncoding=utf-8&user=" +
                            "root&password=1234");
            System.out.println("创建连接成功");
            //3.写sql
            String sql = "delete from userinfo where id=?";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql得到结果集
            statement.setInt(1,2);
            statement.executeUpdate();
            System.out.println("删除成功！");
            //7.关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }
}

对增删改查进行优化
1.创建工具类

package com.zrgj.util;

import java.sql.*;

public class JDBCUtil {
     private static ResultSet rs = null;
     private static PreparedStatement statement = null;
    private static Connection connection = null;
     private JDBCUtil (){}
     static {
         try {
             Class.forName("com.mysql.jdbc.Driver");
         } catch (Exception e) {
             e.printStackTrace();
         }
     }

    public static Connection getConnection(){
         try{
             Class.forName("com.mysql.jdbc.Driver");
             connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&characterEncoding=utf-8&user=root&password=1234");
         }catch (Exception e) {
             e.printStackTrace();
         }
         return connection;
    }

    public static void close(ResultSet rs,Statement stat,Connection conn){
         try {
             if(rs != null) rs.close();
             if(stat != null) stat.close();
             if(conn != null) conn.close();
         }catch (Exception e) {
             e.printStackTrace();
         }finally {
             if (rs != null) {
                 try {
                     rs.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
             if (statement != null) {
                 try {
                     statement.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
             if (connection != null) {
                 try {
                     connection.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
         }
    }
}

2.构造实体类，根据我们数据库表中的属性创建User类

package com.zrgj.bean;

public class userInfo {
    private  int id;
    private  String username;
    private  String password;

    public void setId(int id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

3.创建持久层，对数据库进行增删改查

package com.zrgj.dao;

import com.whut.bean.userInfo;
import com.whut.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserInfoDao {
    public static void main(String[] args){
        UserInfoDao userdao = new UserInfoDao();
        List<userInfo> list = userdao.findALL();

    }

    public List<userInfo> findALL(){
        PreparedStatement statement = null;
        ResultSet rs = null;
        Connection connection = JDBCUtil.getConnection();
        List<userInfo> list = new ArrayList<>();
        try {
            String sql = "select * from userinfo";
            //4.得到statement对象执行sql
            statement = connection.prepareStatement(sql);
            //5.得到结果集
            rs = statement.executeQuery();

            //6.处理结果集
            while (rs.next()) {
                userInfo uinfo = new userInfo();
                uinfo.setId(rs.getInt(1));
                uinfo.setUsername(rs.getString(2));
                uinfo.setPassword(rs.getString(3));
               // System.out.println(uinfo);
                list.add(uinfo);
            }
            //7.关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(null,statement,connection);
        }
       return list;
    }

    public void add(){
        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();

            String sql = "insert into userinfo(username,password) values(?,?)";

            statement =  connection.prepareStatement(sql);

            statement.setString(1, "ccz");
            statement.setString(2, "1234");
            statement.executeUpdate();
            System.out.println("插入成功！");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(null,statement,connection);
        }
    }

    public void update(){
        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = JDBCUtil.getConnection();
            //3.写SQL
            String sql = "update userinfo set username=?,password=? where id=?";
            //4.得到statement对象执行sql
            statement = connection.prepareStatement(sql);
            //5.得到结果集
            statement.setString(1,"abc");
            statement.setString(2,"789");
            statement.setInt(3,1);
            statement.executeUpdate();
            System.out.println("修改成功！");
            //6.处理结果集
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,statement,connection);
        }
    }

    public void delete(){
        ResultSet rs = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            //3.写SQL
            String sql = "delete from userinfo where id=?";
            //4.得到statement对象执行sql
            statement = connection.prepareStatement(sql);
            //5.得到结果集
            statement.setInt(1,2);
            statement.executeUpdate();
            System.out.println("删除成功！");
            //6.处理结果集
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,statement,connection);
        }
    }

}

4.对Test进行修改

package com.zrgj;

import com.whut.bean.userInfo;
import com.whut.dao.UserInfoDao;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        UserInfoDao  uinfo = new UserInfoDao();
        List<userInfo> list = uinfo.findALL();
        System.out.println(list);
        uinfo.add();
        List<userInfo> list1 = uinfo.findALL();
        System.out.println(list1);
        uinfo.update();
        List<userInfo> list2 = uinfo.findALL();
        System.out.println(list2);
        uinfo.delete();
        List<userInfo> list3 = uinfo.findALL();
        System.out.println(list3);
    }
}

导出完成了代码优化