管理学生数据和MySql数据库相结合实现根据学号进行增删改查

功能：

（1）向表中增加记录并显示增加后的所有记录（新增记录的具体数据自定）；

（2）从表中删除id=1的记录，并显示删除后的所有记录；

（3）修改表中记录：查询条件id=2，将name修改为：王杰，修改完毕显示所有记录；

（4）查询表中id=3的记录并显示。

结果图：

 为了防止sql注入可使用prepareStatement()和适当修改

源码：

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class StudentManager extends JFrame implements ActionListener {
    JLabel lbId, lbName, lbSex, lbAge, lbInfo;
    JTextField txtId, txtName, txtSex, txtAge;
    JButton btnAdd, btnDel, btnSearch, btnUpdate;

    Connection conn;
    Statement stmt;

    public StudentManager() {
        super("学生信息管理系统");

        lbId = new JLabel("学号");
        lbName = new JLabel("姓名");
        lbSex = new JLabel("性别");
        lbAge = new JLabel("年龄");

        txtId = new JTextField(10);
        txtName = new JTextField(20);
        txtSex = new JTextField(2);
        txtAge = new JTextField(3);

        btnAdd = new JButton("添加");
        btnDel = new JButton("删除");
        btnSearch = new JButton("查询");
        btnUpdate = new JButton("修改");

        lbInfo = new JLabel("欢迎使用学生信息管理系统");

        JPanel pnlInput = new JPanel(new GridLayout(4, 2));
        pnlInput.add(lbId);
        pnlInput.add(txtId);
        pnlInput.add(lbName);
        pnlInput.add(txtName);
        pnlInput.add(lbSex);
        pnlInput.add(txtSex);
        pnlInput.add(lbAge);
        pnlInput.add(txtAge);

        JPanel pnlButton = new JPanel();
        pnlButton.add(btnAdd);
        pnlButton.add(btnDel);
        pnlButton.add(btnSearch);
        pnlButton.add(btnUpdate);

        Container contentPane = getContentPane();
        contentPane.setLayout(new BorderLayout());
        contentPane.add(pnlInput, BorderLayout.CENTER);
        contentPane.add(pnlButton, BorderLayout.SOUTH);
        contentPane.add(lbInfo, BorderLayout.NORTH);

        btnAdd.addActionListener(this);
        btnDel.addActionListener(this);
        btnSearch.addActionListener(this);
        btnUpdate.addActionListener(this);

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/stu";
            conn = DriverManager.getConnection(url, "root", "密码");
            stmt = conn.createStatement();
        } catch (Exception e) {
            lbInfo.setText("数据库连接失败");
            e.printStackTrace();
        }

        setSize(300, 200);
        setVisible(true);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }

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

    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == btnAdd) {
            addStudent();
        } else if (e.getSource() == btnDel) {
            delStudent();
        } else if (e.getSource() == btnSearch) {
            searchStudent();
        } else if (e.getSource() == btnUpdate) {
           updateStudent();
        }
    }

    public void addStudent() {
        int id = Integer.parseInt(txtId.getText().trim());
        String name = txtName.getText().trim();
        String sex = txtSex.getText().trim();
        int age = Integer.parseInt(txtAge.getText().trim());

        try {
            String sql = "INSERT INTO student (id, Name, Sex, Age) VALUES (" + id + ", '" + name + "', '" + sex + "', " + age + ")";
            stmt.executeUpdate(sql);
            lbInfo.setText("添加成功");
        } catch (Exception e) {
            lbInfo.setText("添加失败");
            e.printStackTrace();
        }
    }
    public void  delStudent() {
        int id1 = Integer.parseInt(txtId.getText().trim());
//        String name = txtName.getText().trim();
//        String sex = txtSex.getText().trim();
//        int age = Integer.parseInt(txtAge.getText().trim());

        try {
            String sql = "delete  from student where id = "+id1;
                    stmt.executeUpdate(sql);
            lbInfo.setText("删除成功");
        } catch (Exception e) {
            lbInfo.setText("删除失败");
            e.printStackTrace();
        }
    }
    public void  searchStudent() {
        int id1 = Integer.parseInt(txtId.getText().trim());
        String name ="";
        String sex = "";
        int age = 0;

        try {
            String sql = "select * from student where id = "+id1;
            ResultSet rs = stmt.executeQuery(sql);
          while(rs.next()){
              int ID = rs.getInt(1);
              String NAME = rs.getString(2);
              String SEX = rs.getString(3);
              String AGE = rs.getString(4);
              System.out.println(ID+NAME+SEX+AGE);
          }

            lbInfo.setText("查询成功");
        } catch (Exception e) {
            lbInfo.setText("查询失败");
            e.printStackTrace();
        }
    }
    public void  updateStudent(){
        int id1 = Integer.parseInt(txtId.getText().trim());
        String name = txtName.getText().trim();
        String sex = txtSex.getText().trim();
        int age = Integer.parseInt(txtAge.getText().trim());

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu", "root", "密码");
            String sql = "UPDATE student SET Name=?, Sex=?, Age=? WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, sex);
            pstmt.setInt(3, age);
            pstmt.setInt(4, id1);
            int result = pstmt.executeUpdate();
            if (result > 0) {
                System.out.println("Update success");
            } else {
                System.out.println("Update failed");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}