迷你学生数据库管理系统二

Table查询功能

stuModel:

package com.test;

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

import javax.swing.table.*;

public class stuModel extends AbstractTableModel {
    Vector rowData, columnNames;
    Connection ct = null;
    ResultSet rs = null;
    PreparedStatement ps = null;

    public stuModel(String sql) {
        columnNames = new Vector();
        columnNames.add("学号");
        columnNames.add("名字");
        columnNames.add("性别");
        columnNames.add("年龄");
        columnNames.add("籍贯");
        columnNames.add("系别");
        //
        rowData = new Vector();// 可以存放多行
        try {// 1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.、
            ct = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");

            ps = ct.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vector hang = new Vector();
                hang.add(rs.getString(1));
                hang.add(rs.getString(2));
                hang.add(rs.getString(3));
                hang.add(rs.getInt(4));
                hang.add(rs.getString(5));
                hang.add(rs.getString(6));
                // 加入到rowData
                rowData.add(hang);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                if (ct != null)
                    ct.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }

    @Override
    public int getRowCount() {// 多少行
        return this.rowData.size();
    }

    @Override
    public int getColumnCount() {// 多少列
        // TODO Auto-generated method stub
        return this.columnNames.size();
    }

    @Override
    public Object getValueAt(int row, int column) {
        return ((Vector) this.rowData.get(row)).get(column);
    }
}

test；

package com.test;

import javax.swing.*;
import java.util.*;
import java.sql.*;
import java.awt.*;
import java.awt.Event;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class Test1 extends JFrame implements ActionListener {
    // rowData用来存放行数据
    // columnNames用来存放列名

    JTable jt = null;
    JScrollPane jsp = null;

    JPanel jp1, jp2;
    JLabel jl1;
    JButton jb1, jb2, jb3, jb4;
    JTextField jtf;
    stuModel sm;

    public static void main(String[] args) {
        Test1 test = new Test1();
    }

    public Test1() {

        // jt=new JTable(rowData,columnNames);
        // 初始化
        jp1 = new JPanel();
        jtf = new JTextField(10);
        jb1 = new JButton("查询");
        jb1.addActionListener(this);
        jl1 = new JLabel("请输入名字");
        jp1.add(jl1);
        jp1.add(jtf);
        jp1.add(jb1);
        // 初始化下面的
        jp2 = new JPanel();
        jb2 = new JButton("添加");
        jb3 = new JButton("修改");
        jb4 = new JButton("删除");
        jp2.add(jb2);
        jp2.add(jb3);
        jp2.add(jb4);
        // 创建一个数据模型对象
        // 初始化
        jt = new JTable(sm);
        jsp = new JScrollPane(jt);
        // 把jsp放入到JFrame
        this.add(jsp);
        this.add(jp1, "North");
        this.add(jp2, "South");

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

    @Override
    public void actionPerformed(ActionEvent e) {
        // TODO Auto-generated method stub
        if (e.getSource() == jb1) {
            System.out.println("用户希望被查询");
        }
        String name = this.jtf.getText().trim();
        String sql = "select* from stu where stuName='" + name + "'";
        // String sql="select* from stu";
        sm = new stuModel(sql);
        //
        jt.setModel(sm);

    }
}

基本实现了增删改查：

test；

package com.test;

import javax.swing.*;
import java.util.*;
import java.sql.*;
import java.awt.*;
import java.awt.Event;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class Test1 extends JFrame implements ActionListener {
    // rowData用来存放行数据
    // columnNames用来存放列名

    JTable jt = null;
    JScrollPane jsp = null;

    JPanel jp1, jp2;
    JLabel jl1;
    JButton jb1, jb2, jb3, jb4;
    JTextField jtf;
    stuModel sm;// 防止内存泄漏

    public static void main(String[] args) {
        Test1 test = new Test1();
    }

    public Test1() {

        // jt=new JTable(rowData,columnNames);
        // 初始化
        jp1 = new JPanel();
        jtf = new JTextField(10);
        jb1 = new JButton("查询");
        jb1.addActionListener(this);
        jl1 = new JLabel("请输入名字");
        jp1.add(jl1);
        jp1.add(jtf);
        jp1.add(jb1);
        // 初始化下面的
        jp2 = new JPanel();
        jb2 = new JButton("添加");
        jb2.addActionListener(this);
        jb3 = new JButton("修改");
        jb3.addActionListener(this);
        jb4 = new JButton("删除");
        jb4.addActionListener(this);
        jp2.add(jb2);
        jp2.add(jb3);
        jp2.add(jb4);
        // 创建一个数据模型对象
        // 初始化
        jt = new JTable(sm);
        jsp = new JScrollPane(jt);
        // 把jsp放入到JFrame
        this.add(jsp);
        this.add(jp1, "North");
        this.add(jp2, "South");

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

    @Override
    public void actionPerformed(ActionEvent e) {
        // TODO Auto-generated method stub
        if (e.getSource() == jb1) {
            System.out.println("用户希望被查询");
            String name = this.jtf.getText().trim();
            String sql = "select* from stu where stuName='" + name + "'";
            if (name.equals(""))
                sql = "";
            sm = new stuModel(sql);
            jt.setModel(sm);
        }
        // 当用户点击廷加
        else if (e.getSource() == jb2) {
            stuAdd sa = new stuAdd(this, "添加学生", false);// 父窗口 注意true与false区别
            System.out.println("meixiugaia");
            sm = new stuModel();
            jt.setModel(sm);
        } else if (e.getSource() == jb4) {
            // 返回用户点上的行，要是没选择返回-1
            int rowNum = this.jt.getSelectedRow();
            if (rowNum == -1) {
                // 提示
                JOptionPane.showMessageDialog(this, "请选择一行");
                return;
            }
            // 得到学生编号
            String stuId = (String) sm.getValueAt(rowNum, 0);
            System.out.println("id=" + stuId);
            // 连接数据库完成删除
            Connection conn = null;
            Statement stml = null;
            ResultSet rs = null;
            PreparedStatement ps = null;
            // 连接数据库
            try {
                // 1.加载驱动
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");
                String sql = "delete from stu where stuId=?";
                ps = conn.prepareStatement(sql);
                ps.setString(1, stuId);

                // 4.执行操作
                ps.executeUpdate();
            } catch (Exception e2) {
                e2.printStackTrace();
            } finally {
                try {
                    if (rs != null)
                        rs.close();
                    if (ps != null)
                        ps.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                sm = new stuModel();
                jt.setModel(sm);
            }
        } else if (e.getSource() == jb3) {
            // 用户希望修改
            int rowNum = this.jt.getSelectedRow();
            if (rowNum == -1) {
                // 提示
                JOptionPane.showMessageDialog(this, "请选择一行");
                return;
            }
            // 显示修改对话框
            new stuUp(this, "修改", true, sm, rowNum);
            sm = new stuModel();
            jt.setModel(sm);
        }

    }

}

stuModel

//**

package com.test;

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

import javax.swing.table.*;

public class stuModel extends AbstractTableModel {
    Vector rowData, columnNames;
    Connection ct = null;
    ResultSet rs = null;
    PreparedStatement ps = null;

    public void addstu() {
        // 根据用户输入的sql语句完成添加任务
    }

    public void init(String sql) {
        if (sql.equals("")) {
            sql = "select* from stu";
        }

        System.out.println(sql);
        columnNames = new Vector();
        columnNames.add("学号");
        columnNames.add("名字");
        columnNames.add("性别");
        columnNames.add("年龄");
        columnNames.add("籍贯");
        columnNames.add("系别");
        //
        rowData = new Vector();// 可以存放多行
        try {// 1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.、
            ct = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");

            ps = ct.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vector hang = new Vector();
                hang.add(rs.getString(1));
                hang.add(rs.getString(2));
                hang.add(rs.getString(3));
                hang.add(rs.getInt(4));
                hang.add(rs.getString(5));
                hang.add(rs.getString(6));
                // 加入到rowData
                rowData.add(hang);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                if (ct != null)
                    ct.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }

    public stuModel() {
        this.init("");
    }

    public stuModel(String sql) {
        this.init(sql);
    }

    @Override
    public int getRowCount() {// 多少行
        return this.rowData.size();
    }

    @Override
    public int getColumnCount() {// 多少列
        // TODO Auto-generated method stub
        return this.columnNames.size();
    }

    @Override
    public Object getValueAt(int row, int column) {
        return ((Vector) this.rowData.get(row)).get(column);
    }
}

stuAdd

package com.test;

import javax.swing.*;
import javax.swing.border.Border;

import org.apache.commons.dbcp.DriverManagerConnectionFactory;

import java.sql.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class stuAdd extends JDialog implements ActionListener {
    // 定义需要的swing组件
    JLabel jl1, jl2, jl3, jl4, jl5, jl6;
    JButton jb1, jb2, jb3;
    JTextField jtf1, jtf2, jtf3, jtf4, jtf5, jtf6;
    JPanel jp1, jp2, jp3;

    // 他的父窗口owner 窗口名字 模式窗口or非模式（）
    public stuAdd(Frame owner, String title, boolean model) {
        super(owner, title, model);
        jl1 = new JLabel("stuId");
        jl2 = new JLabel("stuName");
        jl3 = new JLabel("stuSex");
        jl4 = new JLabel("stuAge");
        jl5 = new JLabel("stuJg");
        jl6 = new JLabel("stuDept");

        jtf1 = new JTextField();
        jtf2 = new JTextField();
        jtf3 = new JTextField();
        jtf4 = new JTextField();
        jtf5 = new JTextField();
        jtf6 = new JTextField();

        jb1 = new JButton("添加");
        jb1.addActionListener(this);
        jb2 = new JButton("取消");

        jp1 = new JPanel();
        jp2 = new JPanel();
        jp3 = new JPanel();
        // 设置布局
        jp1.setLayout(new GridLayout(6, 1));
        jp2.setLayout(new GridLayout(6, 1));
        // 添加组件
        jp1.add(jl1);
        jp1.add(jl2);
        jp1.add(jl3);
        jp1.add(jl4);
        jp1.add(jl5);
        jp1.add(jl6);

        jp2.add(jtf1);
        jp2.add(jtf2);
        jp2.add(jtf3);
        jp2.add(jtf4);
        jp2.add(jtf5);
        jp2.add(jtf6);

        jp3.add(jb1);
        jp3.add(jb2);
        this.add(jp1, BorderLayout.WEST);
        this.add(jp2, BorderLayout.CENTER);
        this.add(jp3, BorderLayout.SOUTH);
        // 展现
        this.setSize(350, 300);
        this.setVisible(true);
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        System.out.println("你好啊");
        if (e.getSource() == jb1) {
            // 连接数据库
            Connection conn = null;
            Statement stml = null;
            ResultSet rs = null;
            PreparedStatement ps = null;
            // 连接数据库
            try {
                // 1.加载驱动
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");
                String sql = "insert into stu values(?,?,?,?,?,?)";
                ps = conn.prepareStatement(sql);
                // 给参数赋值
                ps.setString(1, jtf1.getText());
                ps.setString(2, jtf2.getText());
                ps.setString(3, jtf3.getText());
                ps.setString(4, jtf4.getText());
                ps.setString(5, jtf5.getText());
                ps.setString(6, jtf6.getText());
                // 4.执行操作
                ps.executeUpdate();
                this.dispose();// 关闭对话框
            } catch (Exception e2) {
                e2.printStackTrace();
            } finally {
                try {
                    if (rs != null)
                        rs.close();
                    if (ps != null)
                        ps.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }

            }
        }
    }
}

stuUp

package com.test;

import javax.swing.*;
import javax.swing.border.Border;

import org.apache.commons.dbcp.DriverManagerConnectionFactory;

import java.sql.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class stuUp extends JDialog implements ActionListener {
    // 定义需要的swing组件
    JLabel jl1, jl2, jl3, jl4, jl5, jl6;
    JButton jb1, jb2, jb3;
    JTextField jtf1, jtf2, jtf3, jtf4, jtf5, jtf6;
    JPanel jp1, jp2, jp3;

    // 他的父窗口owner 窗口名字 模式窗口or非模式（）
    public stuUp(Frame owner, String title, boolean model, stuModel sm, int rowNum) {
        super(owner, title, model);
        jl1 = new JLabel("stuId");
        jl2 = new JLabel("stuName");
        jl3 = new JLabel("stuSex");
        jl4 = new JLabel("stuAge");
        jl5 = new JLabel("stuJg");
        jl6 = new JLabel("stuDept");

        jtf1 = new JTextField();
        // 初始化数据
        jtf1.setText((String) sm.getValueAt(rowNum, 0));
        jtf2 = new JTextField();
        // 让jtf1不能被修改
        jtf1.setEnabled(false);
        jtf2.setText((String) sm.getValueAt(rowNum, 1));
        jtf3 = new JTextField();
        jtf3.setText((String) sm.getValueAt(rowNum, 2));
        jtf4 = new JTextField();
        jtf4.setText((String) sm.getValueAt(rowNum, 3).toString());
        jtf5 = new JTextField();
        jtf5.setText((String) sm.getValueAt(rowNum, 4));
        jtf6 = new JTextField();
        jtf6.setText((String) sm.getValueAt(rowNum, 5));

        jb1 = new JButton("修改");
        jb1.addActionListener(this);
        jb2 = new JButton("取消");

        jp1 = new JPanel();
        jp2 = new JPanel();
        jp3 = new JPanel();
        // 设置布局
        jp1.setLayout(new GridLayout(6, 1));
        jp2.setLayout(new GridLayout(6, 1));
        // 添加组件
        jp1.add(jl1);
        jp1.add(jl2);
        jp1.add(jl3);
        jp1.add(jl4);
        jp1.add(jl5);
        jp1.add(jl6);

        jp2.add(jtf1);
        jp2.add(jtf2);
        jp2.add(jtf3);
        jp2.add(jtf4);
        jp2.add(jtf5);
        jp2.add(jtf6);

        jp3.add(jb1);
        jp3.add(jb2);
        this.add(jp1, BorderLayout.WEST);
        this.add(jp2, BorderLayout.CENTER);
        this.add(jp3, BorderLayout.SOUTH);
        // 展现
        this.setSize(350, 300);
        this.setVisible(true);
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        System.out.println("你好啊");
        if (e.getSource() == jb1) {
            // 连接数据库
            Connection conn = null;
            Statement stml = null;
            ResultSet rs = null;
            PreparedStatement ps = null;
            // 连接数据库
            try {
                // 1.加载驱动
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");
                String sql = "update stu set stuName=?,stuSex=?,stuAge=?,stuJg=?,stuDept=? where stuId=?";
                ps = conn.prepareStatement(sql);
                // 给参数赋值
                ps.setString(1, jtf2.getText());
                ps.setString(2, jtf3.getText());
                ps.setString(3, jtf4.getText());
                ps.setString(4, jtf5.getText());
                ps.setString(5, jtf6.getText());
                ps.setString(6, jtf1.getText());
                // 4.执行操作
                System.out.println(sql);
                ps.executeUpdate();
                this.dispose();// 关闭对话框
            } catch (Exception e2) {
                e2.printStackTrace();
            } finally {
                try {
                    if (rs != null)
                        rs.close();
                    if (ps != null)
                        ps.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }

            }
        }
    }
}