JDBC基础知识(5)——部门管理系统的实现

部门管理系统

功能:

1. 部门注册
2. 部门查询
3. 部门删除
4. 部门更新

package com.bjpowernode.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class 部门管理系统 {
    public static void main(String[] args) throws Exception{
        Scanner scanner = new Scanner(System.in);
        int flag = 0;
        String deptNo,dname,loc;
        String insert_sql = "insert into dept values(?,?,?)";
        String select_sql = "select * from dept";
        String delete_sql = "delete from dept where deptNo = ?";
        String update_sql = "update dept set dname = ?,loc = ? where deptNo = ?";
        //操作界面
        System.out.println("*****1.部门注册*****");
        System.out.println("*****2.部门查询*****");
        System.out.println("*****3.部门删除*****");
        System.out.println("*****4.部门更新*****");
        System.out.println("请选择");
        flag = scanner.nextInt();
        if(flag == 1){
            System.out.println("请输入新部门编号");
            deptNo = scanner.next();
            System.out.println("请输入新部门名称");
            dname = scanner.next();
            System.out.println("请输入新部门位置");
            loc = scanner.next();
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123456");
            PreparedStatement car = con.prepareStatement(insert_sql);
            car.setString(1,deptNo);
            car.setString(2,dname);
            car.setString(3,loc);
            flag = car.executeUpdate();
            if(car != null) {
                car.close();
            }
            if(con != null) {
                con.close();
            }
            if(flag == 1){
                System.out.println("新部门注册成功");
            }else {
                System.out.println("新部门注册失败");
            }
        }else if(flag == 2){
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123456");
            PreparedStatement car = con.prepareStatement(select_sql);
            ResultSet table = car.executeQuery();
            while(table.next()){
                deptNo = table.getString("deptNo");
                dname = table.getString("dname");
                loc = table.getString("loc");
                System.out.println("部门编号 "+deptNo+" 部门名称 "+dname+" 部门位置 "+loc);
            }
            if(table != null) {
                table.close();
            }
            if(car != null) {
                car.close();
            }
            if(con != null) {
                con.close();
            }
        }else if(flag == 3){
            System.out.println("请输入需要删除的部门编号");
            deptNo = scanner.next();
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123456");
            PreparedStatement car = con.prepareStatement(delete_sql);
            car.setString(1,deptNo);
            flag = car.executeUpdate();
            if(car != null) {
                car.close();
            }
            if(con != null) {
                con.close();
            }
            if(flag == 1){
                System.out.println("部门"+deptNo+"被成功删除");
            }else {
                System.out.println("部门"+deptNo+"无法删除");
            }
        }else if(flag == 4){
            System.out.println("请输入部门编号");
            deptNo = scanner.next();
            System.out.println("请输入部门新名称");
            dname = scanner.next();
            System.out.println("请输入部门新位置");
            loc = scanner.next();
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123456");
            PreparedStatement car = con.prepareStatement(update_sql);
            car.setString(1,dname);
            car.setString(2,loc);
            car.setString(3,deptNo);
            flag = car.executeUpdate();
            if(car != null) {
                car.close();
            }
            if(con != null) {
                con.close();
            }
            if(flag == 1){
                System.out.println("部门"+deptNo+"被成功更新");
            }else {
                System.out.println("部门"+deptNo+"无法更新");
            }
        }
    }
}