java JDBC与数据库实验

使用MySQL创建一个数据库company，建立一个员工表emp，表结构如下：

职工编号（number，文本），职工姓名（name，文本），性别（sex，文本），出生年月（birthday，日期），部门号（department，文本），工资（salary，数字，双精度），其中职工编号为主键。然后在表中随机添加一些数据

编写一个GUI程序，当运行该程序时，执行以下操作：

(1) 通过文本框输入一个部门号，点击按钮，程序显示该部门的职工信息列表，输出职工编号、姓名、性别、出生年月及工资。

(2) 通过文本框输入一个职工编号，点击删除按钮，程序从emp表中删除该职工记录，并显示删除后的职工信息。如果输入的职工号不存在要给出相应的提示，当表中没有记录时，程序结束。

本人不太懂这个 求如何实现

改成你自己的mysql配置，和表就能使用了

package com.szzq.dao;


import com.szzq.model.User;

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    static String name;
    static String psw;
    static boolean flag = false;

    public void inter(User user) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/qingshukeji?serverTimezone=GMT%2B8", name, "123456");
        String sql = "insert into t_user(username,address,phone,id)values(?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(4, user.getId());
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getAddress());
        preparedStatement.setString(3, user.getPhone());
        preparedStatement.execute();
        preparedStatement.close();
        connection.close();
    }

    static {
        JFrame frame = new JFrame("数据库连接");
        JPanel panel = new JPanel();
        JPanel panel1 = new JPanel();
        JButton button = new JButton("连接");
        JButton button1 = new JButton("添加");
        JButton button2 = new JButton("修改");
        JButton button3 = new JButton("查询");
        JButton button4 = new JButton("删除");
        JButton button5 = new JButton("断开");
        JButton button6 = new JButton("发送");
        button.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JOptionPane.showMessageDialog(frame,"数据库连接成功!");
            }
        });
        button5.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JOptionPane.showMessageDialog(frame,"连接以断开!");
            }
        });


        JTextField textField = new JTextField(20);
        JTextArea textArea = new JTextArea(20, 50);
        textArea.setFont(new Font("楷体", Font.BOLD, 20));
        textArea.setBackground(Color.cyan);
        Container container = frame.getContentPane();
        container.add(textArea);
        JScrollPane jScrollPane = new JScrollPane(textArea);
        container.add(jScrollPane);

        button6.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                textArea.append(textField.getText() + "\n");
            }
        });
        button4.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String dialog = JOptionPane.showInputDialog(frame, "请输入需要删除数据的序号！");
                int da = Integer.parseInt(dialog);
                int i = JOptionPane.showConfirmDialog(frame, "确定删除数据吗?");
                if(i==0){
                    try {
                        deleteUserById(da);
                    } catch (ClassNotFoundException | SQLException classNotFoundException) {
                        classNotFoundException.printStackTrace();
                    }
                    JOptionPane.showMessageDialog(frame,"数据删除成功!");
                }
            }
        });

        button2.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                W2();
            }
        });

        button3.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                List<dan.model.User> list = null;
                try {
                    list = new UserDao().selectUserAll();
                } catch (SQLException | ClassNotFoundException throwables) {
                    throwables.printStackTrace();
                }
                int 数据 = JOptionPane.showConfirmDialog(frame, "是否显示转换后的数据");
                if (数据 == 0) {
                    for (dan.model.User user : list) {
                        textArea.append(user.toString().replace("User{", "").replace("id", "序号")
                                .replace("Username", "姓名").replace("address", "地址").replace("phone", "电话").replace("'", "")
                                .replace("'", "").replace("}", "").replace("=", ":  ") + "\n");
                    }
                }
                if (数据 == 1) {
                    for (dan.model.User user : list) {
                        textArea.append((user) + "\n");
                    }
                }
            }
        });

        button1.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                W1();
            }
        });

        textField.addKeyListener(new KeyAdapter() {
            @Override
            public void keyPressed(KeyEvent e) {
                if (e.getKeyCode() == KeyEvent.VK_ENTER) {
                    textArea.append(textField.getText() + "\n");
                    textField.setText("");
                }
            }
        });

        panel.add(button);
        panel.add(button1);
        panel.add(button2);
        panel.add(button3);
        panel.add(button4);
        panel.add(button5);
        panel1.add(textField);
        panel1.add(button6);
        frame.add(panel1, BorderLayout.NORTH);
        frame.add(panel, BorderLayout.SOUTH);

        //添加弹窗事件
        name = JOptionPane.showInputDialog(frame, "请输入数据库用户名:");
        psw = JOptionPane.showInputDialog(frame, "请输入数据库连接密码:");
        if (name.equals("root") && psw.equals("123456")) {
            JOptionPane.showMessageDialog(frame, "登录成功!");
        } else {
            JOptionPane.showMessageDialog(frame, "用户名或者密码错误");
            System.exit(0);
        }

        frame.setVisible(true);
        frame.setResizable(false);
        frame.setBounds(200, 200, 800, 500);
        frame.setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);


    }

    public static void W1() {

        JFrame jFrame = new JFrame("数据添加");
        jFrame.setBounds(400, 400, 300, 400);
        JTextField textField1 = new JTextField(20);
        textField1.setText("请输入序号");
        JTextField textField2 = new JTextField(20);
        textField2.setText("请输入姓名");
        JTextField textField3 = new JTextField(20);
        textField3.setText("请输入电话");
        JTextField textField4 = new JTextField(20);
        textField4.setText("请输入住址");
        JPanel panel1 = new JPanel(new GridLayout(8, 1));
        panel1.add(textField1);
        panel1.add(textField2);
        panel1.add(textField3);
        panel1.add(textField4);
        JButton button = new JButton("添加到数据库");
        JPanel panel = new JPanel();
        panel.add(button);
        jFrame.add(panel1);


        jFrame.add(panel, BorderLayout.SOUTH);
        button.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                User user = new User();
                int i = Integer.parseInt(textField1.getText());
                user.setId(i);
                user.setUsername(textField2.getText());
                user.setPhone(textField3.getText());
                user.setAddress(textField4.getText());
                try {
                    new UserDao().inter(user);
                } catch (ClassNotFoundException | SQLException classNotFoundException) {
                    classNotFoundException.printStackTrace();
                }
                if (flag == true) {
                    JOptionPane.showMessageDialog(jFrame, "数据添加成功!");
                } else if (!flag) {
                    JOptionPane.showMessageDialog(jFrame, "数据添加失败!");
                }
                {

                }
            }
        });
        jFrame.setResizable(false);
        jFrame.setVisible(true);

    }

    public static void W2() {

        JFrame jFrame = new JFrame("数据修改");
        jFrame.setBounds(600, 200, 300, 400);
        JTextField textField1 = new JTextField(20);
        textField1.setText("请输入要修改的序号");
        JTextField textField2 = new JTextField(20);
        textField2.setText("请输入修改后的姓名");
        JTextField textField3 = new JTextField(20);
        textField3.setText("请输入修改后的电话");
        JTextField textField4 = new JTextField(20);
        textField4.setText("请输入修改后的住址");
        JPanel panel1 = new JPanel(new GridLayout(8, 1));
        panel1.add(textField1);
        panel1.add(textField2);
        panel1.add(textField3);
        panel1.add(textField4);
        JButton button = new JButton("修改并提交数据库");
        JPanel panel = new JPanel();
        panel.add(button);
        jFrame.add(panel1);


        jFrame.add(panel, BorderLayout.SOUTH);
        button.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                User user = new User();
                int i = Integer.parseInt(textField1.getText());
                user.setId(i);
                user.setUsername(textField2.getText());
                user.setPhone(textField3.getText());
                user.setAddress(textField4.getText());
                try {
                    new UserDao().updateUser(user);
                } catch (ClassNotFoundException | SQLException classNotFoundException) {
                    classNotFoundException.printStackTrace();
                }
                if (flag) {
                    JOptionPane.showMessageDialog(jFrame, "数据修改成功!");
                } else {
                    JOptionPane.showMessageDialog(jFrame, "数据修改失败!");
                }
            }
        });
        jFrame.setResizable(false);
        jFrame.setVisible(true);

    }


    //数据查询
    public List<dan.model.User> selectUserAll() throws SQLException, ClassNotFoundException {
        List<dan.model.User> list = new ArrayList<dan.model.User>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;//结果集合对象，用于封装数据库的查询结果
        String sql = "";

        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/qingshukeji?serverTimezone=GMT%2B8", name, psw);
        sql = "select * from t_user";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();//执行数据库的查询，并返回查询结果
        while (rs.next()) {//rs.next,让光标向下移动一次并判断下一个元素是否有值，如果有值则返回真进入循环
            dan.model.User user = new dan.model.User();
            user.setId(rs.getInt("id"));//将结果集中当前元素的显示列名为id的数据获取出来并设置到user的id属性上
            user.setUsername(rs.getString("username"));
            user.setAddress(rs.getString("address"));
            user.setPhone(rs.getString("phone"));
            list.add(user);
            flag = true;
        }
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    //数据删除
    public static void deleteUserById(int id) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection coon = null;
        PreparedStatement ps = null;
        coon = DriverManager.getConnection("jdbc:mysql://localhost:3306/qingshukeji?serverTimezone=GMT%2B8", "root", "123456");
        String sql = "delete  from t_user where id=?";//定义SQL语句，？是占位符需要后期动态为？赋值
        ps = coon.prepareStatement(sql);
        ps.setInt(1, id);
        System.out.println("正在删除数据···");
        ps.execute();
        ps.close();
    }

    //数据修改
    public void updateUser(User user) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection coon = null;
        PreparedStatement ps = null;
        coon = DriverManager.getConnection("jdbc:mysql://localhost:3306/qingshukeji?serverTimezone=GMT%2B8", "root", "123456");
        String sql = "update t_user set username=?,address = ?,phone = ? where id = ?";//定义SQL语句，？是占位符需要后期动态为？赋值
        ps = coon.prepareStatement(sql);
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getAddress());
        ps.setString(3, user.getPhone());
        ps.setLong(4, user.getId());
        ps.execute();
        ps.close();
        coon.close();
    }

    public static void main(String[] args){
    new UserDao();
    }
}