JDBC(四):简易部门管理系统的实现

做一个简单的部门管理系统
基本功能就是对数据库里的部门表进行增删查改

package Dept_Management;

import java.sql.*;
import java.util.Scanner;

public class TestMain {
    public static void main(String[] args) {
        Scanner s = new Scanner(System.in);
        int flag = 0;
        int deptno;
        String dname;
        String loc;
        String sql = "select count(*) from emp where ename=? and empno=?";
        String sql1 = "insert into dept(deptno,dname,loc)values(?,?,?)";
        String sql2 = "select * from dept";
        String sql3 = "delete from dept where deptno=?";
        String sql4 = "update dept set dname=?,loc=? where deptno=?";

        PreparedStatement preparedstatement = null;
        ResultSet rs = null;
        Connection connection = null;
        Driver driver = null;

        try {
            driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);
            //2.获取连接
            String url = "jdbc:mysql://localhost:3306/mysql";
            String user = "root";
            String password = "333";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("********请输入用户名**********");
            String Username = s.next();
            System.out.println("********请输入密码************");
            String pwd = s.next();
            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setString(1, Username);
            preparedstatement.setInt(2, Integer.valueOf(pwd));
            rs = preparedstatement.executeQuery();
            while (rs.next()) {
                flag = rs.getInt("count(*)");
            }
            if (rs != null) {
                rs.close();
            }
            if (preparedstatement != null) {
                preparedstatement.close();
            }
            if (flag != 1) {
                System.out.println("登录信息不存在，请重新登录");
                return;
            }

            while (true) {
                System.out.println("欢迎使用部门管理系统");
                System.out.println("****1.添加部门****");
                System.out.println("****2.查询部门****");
                System.out.println("****3.删除部门****");
                System.out.println("****4.更新部门****");
                System.out.println("****请输入操作****");
                flag = s.nextInt();
                if (flag == 1) {
                    System.out.println("请输入添加新部门的编号");
                    deptno = s.nextInt();
                    System.out.println("请输入添加新部门的名称");
                    dname = s.next();
                    System.out.println("请输入添加新部门的位置");
                    loc = s.next();
                    preparedstatement = connection.prepareStatement(sql1);
                    preparedstatement.setInt(1, deptno);
                    preparedstatement.setString(2, dname);
                    preparedstatement.setString(3, loc);
                    flag = preparedstatement.executeUpdate();
                    //connection的销毁放在最后
                    if (preparedstatement != null) {
                        preparedstatement.close();
                    }
                    if (flag == 1) {
                        System.out.println("部门添加成功");
                    } else {
                        System.out.println("部门添加失败");
                    }
                } else if (flag == 2) {
                    preparedstatement = connection.prepareStatement(sql2);
                    rs = preparedstatement.executeQuery();
                    //处理查询的结果集
                    //rs一次取一行，判断下一行是否有数据
                    System.out.println("deptno,dname,loc");
                    while (rs.next()) {
                        deptno = rs.getInt(1);
                        dname = rs.getString(2);
                        loc = rs.getString(3);
                        //或者直接写成字段名，查询结果后的字段名，如果as重命名后，用重命名的
                        //除了String类型，还有其他类型
                        System.out.println(deptno + "," + dname + "," + loc);
                    }
                    if (rs != null) {
                        rs.close();
                    }
                    if (preparedstatement != null) {
                        preparedstatement.close();
                    }
                } else if (flag == 3) {

                    preparedstatement = connection.prepareStatement(sql3);
                    System.out.println("请输入要删除的部门编号:");
                    int x = s.nextInt();
                    preparedstatement.setInt(1, x);
                    int count = preparedstatement.executeUpdate();
                    if (count == 1) {
                        System.out.println("删除成功");
                    } else {
                        System.out.println("删除失败");
                    }
                    if (preparedstatement != null) {
                        preparedstatement.close();
                    }
                } else if (flag == 4) {

                    System.out.println("请输入更新的部门编号");
                    deptno = s.nextInt();
                    System.out.println("请输入更新的部门名称");
                    dname = s.next();
                    System.out.println("请输入更新的部门位置");
                    loc = s.next();
                    preparedstatement = connection.prepareStatement(sql4);
                    preparedstatement.setString(1, dname);
                    preparedstatement.setString(2, loc);
                    preparedstatement.setInt(3, deptno);
                    flag = preparedstatement.executeUpdate();
                    if (flag == 1) {
                        System.out.println("部门信息更新成功");
                    } else {
                        System.out.println("部门信息更新失败");
                    }
                    if (preparedstatement != null) {
                        preparedstatement.close();
                    }
                }
                System.out.println("输入任意数字继续使用系统，输入0退出系统");
                flag = s.nextInt();
                if (flag == 0) {
                    try {
                        if (connection != null) {
                            connection.close();
                        }
                        System.out.println("系统正在退出，谢谢使用！");
                        break;
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (preparedstatement != null) {
                    preparedstatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

观察代码可知，很多地方可以复用，有必要单独写成一个子方法，需要的时候再调用

首先从简单的包装起来，初始化界面
直接写个初始化方法即可

package Dept_Management;

public class UI_Start {
    public void Initiate_Menu() {
        System.out.println("欢迎使用部门管理系统");
        System.out.println("****1.添加部门****");
        System.out.println("****2.查询部门****");
        System.out.println("****3.删除部门****");
        System.out.println("****4.更新部门****");
        System.out.println("****请输入操作****");
    }
}

还需要写一个JDBC的工具类
将能够封装起来的都封装了，主程序中就可以省些代码了

package Dept_Management;

import java.sql.*;

public class JDBCUtil {
    private Connection con = null;
    private PreparedStatement ps = null;

    //注册Driver，在第一次调用工具类时就加载
    static {
        Driver driver = null;
        try {
            driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //创建connection
    public Connection createCon() {
        String url = "jdbc:mysql://localhost:3306/mysql";
        String user = "root";
        String password = "333";
        try {
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return con;
    }


    //封装PreparedStatement对象
    public PreparedStatement createPs(String sql) {
        try {
            Connection con = createCon();
            ps = con.prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ps;
    }

//写一个关闭方法
    public void close() {

        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
//重载关闭方法
    public void close(Connection con) {

        close();

        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
//重载关闭方法
    public void close(ResultSet rs) {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        close();
    }
}

但主程序还是要写很多，主要用来定义各种变量

package Dept_Management;

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

public class MainTest {
    public static void main(String[] args) throws SQLException {
    //声明一个工具对象
        JDBCUtil jdbcUtil = new JDBCUtil();
        //初始化界面
        UI_Start ui_start = new UI_Start();
        //声明一个DAO对象
        DeptDAO deptDAO = new DeptDAO();
        ResultSet rs = null;
        Connection con = null;
        PreparedStatement ps = null;
        //定义初试变量
        String deptno;
        String dname;
        String loc;
        int flag = 0;
        String sql = "select count(*) from emp where ename=? and empno=?";
        try {
            Scanner s = new Scanner(System.in);
            System.out.println("********请输入用户名**********");
            String Username = s.next();
            System.out.println("********请输入密码************");
            String pwd = s.next();
			//利用工具类来建立ps对象
            ps = jdbcUtil.createPs(sql);
            con = jdbcUtil.createCon();
            ps.setString(1, Username);
            ps.setInt(2, Integer.valueOf(pwd));
            rs = ps.executeQuery();
            while (rs.next()) {
            //判断是否存在该用户
                flag = rs.getInt("count(*)");
            }
            jdbcUtil.close(rs);

            if (flag != 1) {
                System.out.println("登录信息不存在，请重新登录");
                return;
            }

            while (true) {
                ui_start.Initiate_Menu();

                flag = s.nextInt();
                if (flag == 1) {
                    System.out.println("请输入添加新部门的编号");
                    deptno = s.next();
                    System.out.println("请输入添加新部门的名称");
                    dname = s.next();
                    System.out.println("请输入添加新部门的位置");
                    loc = s.next();
				//调用add方法
                    flag = deptDAO.add(deptno, dname, loc);

                    if (flag == 1) {
                        System.out.println("部门添加成功");
                    } else {
                        System.out.println("部门添加失败");
                    }
                    
                    //执行查询语句
                } else if (flag == 2) {
                //这里使用一个Dept类的泛型，用来接受查询结果，因为查询结果是一个集合
                    List<Dept> deptList = deptDAO.query();
                    System.out.println("deptno,dname,loc");
                    for (Dept dept : deptList
                    ) {
                        System.out.println("deptno:" + dept.getDeptno() + "   dname:" + dept.getDname() + "  loc:" + dept.getLoc());
                    }
                    jdbcUtil.close(rs);
                    //执行删除操作
                } else if (flag == 3) {

                    System.out.println("请输入要删除的部门编号:");
                    String deptNo = s.next();
                    //调用删除方法
                    int count = deptDAO.delete(deptNo);
                    if (count == 1) {
                        System.out.println("删除成功");
                    } else {
                        System.out.println("删除失败");
                    }
                    jdbcUtil.close();
                } else if (flag == 4) {

                    System.out.println("请输入更新的部门编号");
                    deptno = s.next();
                    System.out.println("请输入更新的部门名称");
                    dname = s.next();
                    System.out.println("请输入更新的部门位置");
                    loc = s.next();
					//调用更新方法
                    flag = deptDAO.update(deptno, dname, loc);
                    if (flag == 1) {
                        System.out.println("部门信息更新成功");
                    } else {
                        System.out.println("部门信息更新失败");
                    }
                    jdbcUtil.close();
                } else {
                    System.out.println("输入的数字不合法");
                }
                System.out.println("输入任意数字继续使用系统，输入0退出系统");
                flag = s.nextInt();
                if (flag == 0) {
                    try {
                        if (con != null) {
                            con.close();
                        }
                        System.out.println("系统正在退出，谢谢使用！");
                        break;
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (
                SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close(con);
        }
    }
}

进一步看这些具体的方法

package Dept_Management;

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

public class DeptDAO {
    private JDBCUtil jdbcUtil = new JDBCUtil();

//定义add方法
    public int add(String deptNo, String dname, String loc) {
        String sql1 = "insert into dept(deptno,dname,loc)values(?,?,?)";

        PreparedStatement ps = jdbcUtil.createPs(sql1);
        int result = 0;
        try {
            ps.setInt(1, Integer.valueOf(deptNo));
            ps.setString(2, dname);
            ps.setString(3, loc);
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close();
        }
        return result;
    }

//定义一个查询方法
    public List query() {
        String sql2 = "select * from dept";
        PreparedStatement ps = jdbcUtil.createPs(sql2);
        ResultSet rs = null;
        //返回的结果集是一个集合
        List list = new ArrayList();
        try {
            rs = ps.executeQuery();
            while (rs.next()) {
                int deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                //定义了一个dept类用来装返回结果
                Dept dept = new Dept(deptno, dname, loc);
                //每得到一条查询结果就添加进list里面
                list.add(dept);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close();
        }
        return list;
    }

//删除操作
    public int delete(String deptno) {
        String sql3 = "delete from dept where deptno=?";

        PreparedStatement ps = jdbcUtil.createPs(sql3);
        int result = 0;
        try {
            ps.setInt(1, Integer.valueOf(deptno));
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close();
        }
        return result;
    }
//更新操作
    public int update(String deptno, String dname, String loc) {
        String sql4 = "update dept set dname=?,loc=? where deptno=?";

        PreparedStatement ps = jdbcUtil.createPs(sql4);
        int result = 0;
        try {
            ps.setString(1, dname);
            ps.setString(2, loc);
            ps.setInt(3, Integer.valueOf(deptno));
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close();
        }
        return result;
    }
}

声明dept类

package Dept_Management;

public class Dept {
    Integer deptno;
    String dname;
    String loc;

    public Dept(Integer deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    public Dept() {
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }
}

运行效果还是正常的