技术小白的博客------IDEA+Mysql下数据的增删改查

1. 新建java项目
   
   
2. 建立bean、util包
   
   3、在bean新建User类
   
   4、在User类中添加相应属性，和数据库表项一致
   
   5、右键单击选择generate,在分别自动生成构造函数、getter、setter、toString.
   

代码片

User.java

package bean;

public class User {
    private int userid;
    private  String username;
    private String userpwd;

    public User(int userid, String username, String userpwd) {
        this.userid = userid;
        this.username = username;
        this.userpwd = userpwd;
    }

    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

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

    public String getUserpwd() {
        return userpwd;
    }

    public void setUserpwd(String userpwd) {
        this.userpwd = userpwd;
    }

    public String toString() {
        return "User{" +
                "userid=" + userid +
                ", username='" + username + '\'' +
                ", userpwd='" + userpwd + '\'' +
                '}';
    }
}

6、在util包下新建DBUtil类，用于封装数据库的连接和关闭操作。

代码片

DBUtil.java。

package util;

import java.sql.*;

public class DBUtil {
    public static Connection  DBConnection()
    {
        Connection conn=null;
        String driver="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/db_test";
        String user="root";
        String password="123456";
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            if (!conn.isClosed()) {
                System.out.println("数据库连接成功！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return conn;

    }
    public static void CloseDB(ResultSet resultSet,Statement stmt,Connection conn)
    {
        if (resultSet != null) {
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

7、在util类中新建UserDao类，用于保存User的增删改查。

代码片

UserDao.java

package util;

import bean.User;

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

public class UserDao {
    public static void insertUser(int userid, String username, String userpwd) {
        Connection conn = null;
        PreparedStatement presta = null;
        conn = DBUtil.DBConnection();
        boolean insertflag = true;
        String sql = "insert into user values(?,?,?)";
        try {
            presta = conn.prepareStatement(sql);
            presta.setInt(1, userid);
            presta.setString(2, username);
            presta.setString(3, userpwd);
            presta.execute();

        } catch (SQLException e) {
            insertflag = false;
            e.printStackTrace();
        } finally {
            if (insertflag) {
                System.out.println("添加纪录成功");
            } else {
                System.out.println("添加记录失败");
            }
            DBUtil.CloseDB(null, presta, conn);
        }
    }

    public static List<User> findAllUser() {
        List<User> list = new ArrayList<>();

        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            conn = DBUtil.DBConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM user";
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                int userid = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String userpwd = resultSet.getString(3);
                User user = new User(userid, username, userpwd);
                list.add(user);
            }
            System.out.println(list.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
        } finally {
            DBUtil.CloseDB(resultSet, stmt, conn);
        }
        return list;
    }

    public static void deleteUser(int userid) {
        Connection conn = null;
        PreparedStatement presta = null;
        conn = DBUtil.DBConnection();
        boolean deleteflag = true;
        String sql = "delete from user where userid=?";
        try {
            presta = conn.prepareStatement(sql);
            presta.setInt(1, userid);
            presta.execute();

        } catch (SQLException e) {
            deleteflag = false;
            e.printStackTrace();
        } finally {
            if (deleteflag) {
                System.out.println("删除纪录成功");
            } else {
                System.out.println("删除记录失败");
            }
            DBUtil.CloseDB(null, presta, conn);
        }
    }

    public static void updateUser(int userid, String username, String userpwd) {
        Connection conn = null;
        PreparedStatement prest = null;
        String sql=null;
        conn = DBUtil.DBConnection();
        boolean updateflag = true;

        try {

            if(username==null)
            {
                sql = "update user set userpwd=?where userid=?";
                prest = conn.prepareStatement(sql);
                prest.setString(1, userpwd);
                prest.setInt(2, userid);
            }
            else if(userpwd==null)
            {
                sql = "update user set username=? where userid=?";
                prest = conn.prepareStatement(sql);
                prest.setString(1, username);
                prest.setInt(2, userid);
            }
            else
            {
                sql = "update user set username=?,userpwd=? where userid=?";
                prest = conn.prepareStatement(sql);
                prest.setString(1, username);
                prest.setString(2, userpwd);
                prest.setInt(3, userid);
            }

            prest.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (updateflag) {
                System.out.println("修改纪录成功");
            } else {
                System.out.println("修改记录失败");
            }
            DBUtil.CloseDB(null, prest, conn);
        }
    }
}

8、在main函数中调用增删改查操作。

代码片

Main.java

package com.company;

import util.UserDao;

public class Main {

    public static void main(String[] args) {

       /*查询全部记录
        UserDao.findAllUser();
       */

        /*添加记录
        int userid=003;
        String username="xiaoyingying";
        String userpwd="12345678";
        UserDao.insertUser(userid,username,userpwd);
        */

        /*删除记录
        int userid=003;
        UserDao.deleteUser(userid);
        */


        /*更新记录
        int userid=002;
        String username="coder123";
        UserDao.updateUser(userid,username,null);
        */


    }
}

9、运行结果
（1）查询所有user:

（2）添加user

（3）删除user

（4）更新user

至此数据库基本的增删改查工作已经完成！