首页 > 代码库 > JDBC实验
JDBC实验
实验要求
创建一个学生数据库，平台是MySql，图形界面用Swing编写，实现学生的增删改查功能。最终结果如下图所示
这次实验本来是学习JDBC使用的，但难点不在数据库，在图形界面上~~~
整体思路：
1.创建一个student数据库，写一个Student类。
2.先用java实现数据库的增删改查操作，因此创建一个DBControl类与数据库进行交互。
3.画出整个图形界面，因为表格是放在ScrollPane中的，所以在表格处画出一个ScrollPane即可，表格由代码另外创建。整个界面就是一个MyWindow类。
4.由于表格的数据是放在tableModel中的，所以创建一个MyTabelModel类，这个类通过调用DBcontrol类的方法来实现数据的存取。
难点的解决
每次对学生进行增删改查后，表要实时变化。
我在界面的代码中写了一个refresh函数，每当有有数据变化，它都会将当前表所在的JScrollPane移除，
之后TableModel再从数据库中读取数据，重新建立一个表格，再将其添加到新建的JScrollPane中，放到界面中去。
这是所有的代码
student类
package model;public class Student { private String stu_id; private String stu_name; private String birthday; private String sex; private String province; private String city; public Student() {} public Student(String stu_id, String stu_name, String birthday, String sex, String province, String city) { super(); this.stu_id = stu_id; this.stu_name = stu_name; this.birthday = birthday; this.sex = sex; this.province = province; this.city = city; } /** * @return the stu_id */ public String getStu_id() { return stu_id; } /** * @param stu_id the stu_id to set */ public void setStu_id(String stu_id) { this.stu_id = stu_id; } /** * @return the stu_name */ public String getStu_name() { return stu_name; } /** * @param stu_name the stu_name to set */ public void setStu_name(String stu_name) { this.stu_name = stu_name; } /** * @return the birthday */ public String getBirthday() { return birthday; } /** * @param birthday the birthday to set */ public void setBirthday(String birthday) { this.birthday = birthday; } /** * @return the sex */ public String getSex() { return sex; } /** * @param sex the sex to set */ public void setSex(String sex) { this.sex = sex; } /** * @return the province */ public String getProvince() { return province; } /** * @param province the province to set */ public void setProvince(String province) { this.province = province; } /** * @return the city */ public String getCity() { return city; } /** * @param city the city to set */ public void setCity(String city) { this.city = city; }}
DBControl类
package dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import model.Student;public class DBControl { public static final String URL = "jdbc:mysql://localhost:3306/student"; public static final String USER = "root"; public static final String PASSWORD = "123456"; private static Connection conn = null; static { try { // 1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); // 2. 获得数据库连接 conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public static Connection getConnection() { return conn; } // 获得一个日期的毫秒数 public static long getMs(String str) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 此处会抛异常 try { Date date = sdf.parse(str); return date.getTime(); } catch (Exception e) { e.printStackTrace(); } return 0; } // 返回学生人数 public int getSize() throws SQLException { Connection conn = getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT stu_id FROM stu_info where del = 0"); int size = 0; while(rs.next()) { size++; } rs.close(); stmt.close(); return size; } // 返回全部学生的数据 public List<Student> getAll() throws SQLException { Connection conn = getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt .executeQuery("SELECT stu_id, stu_name, birthday, sex, province, city FROM stu_info where del = 0"); List<Student> sl = new ArrayList<Student>(); Student s = null; while (rs.next()) { s = new Student(); s.setStu_id(rs.getString("stu_id")); s.setStu_name(rs.getString("stu_name")); s.setBirthday(rs.getDate("birthday").toString()); s.setSex(rs.getString("sex")); s.setProvince(rs.getString("province")); s.setCity(rs.getString("city")); sl.add(s); } rs.close(); stmt.close(); return sl; } // 增加学生记录 public boolean add(Student s) throws SQLException { // 获取连接 Connection conn = getConnection(); // sql String sql = "INSERT INTO stu_info(stu_id, stu_name, birthday, sex, province, city, del)" + "VALUES(?,?,?,?,?,?,0);"; // 预编译 PreparedStatement ptmt = conn.prepareStatement(sql); // 预编译SQL，减少sql执行 // 传参 ptmt.setString(1, s.getStu_id()); ptmt.setString(2, s.getStu_name()); long date = getMs(s.getBirthday()); ptmt.setDate(3, new java.sql.Date(date)); ptmt.setString(4, s.getSex()); ptmt.setString(5, s.getProvince()); ptmt.setString(6, s.getCity()); // 执行 try { ptmt.execute(); } catch (Exception e) { e.printStackTrace(); ptmt.close(); return false; } ptmt.close(); return true; } // 根据学号查询学生 public Student get(String stu_id) throws SQLException { // 获取连接 Connection conn = getConnection(); // sql, 每行加空格 String sql = "select * from stu_info where stu_id=? and del = 0"; // 预编译SQL，减少sql执行 PreparedStatement ptmt = conn.prepareStatement(sql); // 传参 ptmt.setString(1, stu_id); // 执行 ResultSet rs = ptmt.executeQuery(); Student s = null; while (rs.next()) { s = new Student(); s.setStu_id(rs.getString("stu_id")); s.setStu_name(rs.getString("stu_name")); s.setBirthday(rs.getDate("birthday").toString()); s.setSex(rs.getString("sex")); s.setProvince(rs.getString("province")); s.setCity(rs.getString("city")); } rs.close(); ptmt.close(); return s; } // 根据学号删除学生 public boolean del(String stu_id) throws SQLException { // 获取连接 Connection conn = getConnection(); // sql, 每行加空格 String sql = "UPDATE stu_info SET del=1, stu_id = \‘"+stu_id+" \‘ WHERE stu_id = ?"; // 预编译SQL，减少sql执行 PreparedStatement ptmt = conn.prepareStatement(sql); // 传参 ptmt.setString(1, stu_id); // 执行 try { ptmt.execute(); ptmt.close(); return true; } catch (Exception e) { ptmt.close(); e.printStackTrace(); return false; } } // 根据学号改变学生的信息 public boolean change(String stu_id, Student s) throws SQLException { // 获取连接 Connection conn = getConnection(); // sql, 每行加空格 String sql = "UPDATE stu_info SET stu_name=?, birthday=?, sex=?, province=?, city = ?" + "WHERE stu_id = \‘" + stu_id + "\‘ and del = 0"; // 预编译 PreparedStatement ptmt = conn.prepareStatement(sql); // 传参 ptmt.setString(1, s.getStu_name()); long date = getMs(s.getBirthday()); ptmt.setDate(2, new java.sql.Date(date)); ptmt.setString(3, s.getSex()); ptmt.setString(4, s.getProvince()); ptmt.setString(5, s.getCity()); //执行 try { ptmt.execute(); ptmt.close(); return true; } catch (Exception e) { ptmt.close(); e.printStackTrace(); return false; } }}
MyTableModel类
package ui;import java.util.List;import javax.swing.table.AbstractTableModel;import dao.DBControl;import model.Student;public class MyTableModel extends AbstractTableModel { private static final long serialVersionUID = 1L; DBControl dbc = new DBControl(); private String[] columnNames = {"stu_id","stu_name","birthday","sex","province","city"}; private List<Student> studentList; private String[][] studentInfo; //列数 private int column; //行数 private int row; //一个row行column列的表格模型 public MyTableModel() throws Exception { this.row = dbc.getSize(); this.column = 6; this.studentList = dbc.getAll(); studentInfo = new String[row][6]; int i = 0; for (Student st : studentList) { studentInfo[i][0] = st.getStu_id(); studentInfo[i][1] = st.getStu_name(); studentInfo[i][2] = st.getBirthday(); studentInfo[i][3] = st.getSex(); studentInfo[i][4] = st.getProvince(); studentInfo[i][5] = st.getCity(); i++; } } //获得行数，即显示的表格数据有多少行 public int getRowCount() { return row; } //获得列数，即显示的表格数据有多少列 public int getColumnCount() { return column; } //获得某一行某一列的值，即为所有格子添加数据 public Object getValueAt(int r, int c) { return studentInfo[r][c]; } //获得某一列的列名，即显示所有列名 public String getColumnName(int c) { return columnNames[c]; } //通过学号获得某个学生信息所在的行数 public int getRow(String stu_id) { int result = -1; int i = studentInfo.length; for (int j = 0; j < i; j++) { if (studentInfo[j][0].equals(stu_id)) { result = j; break; } } return result; }}
MyWindow类
package ui;import java.awt.EventQueue;public class MyWindow { DBControl dbc = new DBControl(); MyTableModel mtm; private JFrame frame; private JTextField numberText; private JTextField nameText; private JTextField birthdayText; private JTextField provinceText; private JTextField cityText; private JTextField queryNumberText; private JRadioButton maleButton; private JRadioButton femaleButton; private JButton addButton; private JButton deleteButton; private JButton refreshButton; private JScrollPane scrollPane; private JButton queryButton; private JTable table; private JLabel label_1; private JLabel label_2; private JLabel label_3; private JLabel label_4; private JLabel label_5; public static void main(String[] args) { EventQueue.invokeLater(new Runnable() { public void run() { try { MyWindow window = new MyWindow(); window.frame.setVisible(true); } catch (Exception e) { e.printStackTrace(); } } }); } /** * Create the application. */ public MyWindow() throws Exception { initialize(); } // 创建一个表格 public void refreshTable() throws Exception { if (scrollPane != null) { frame.remove(scrollPane); } // 创建一个model MyTableModel model = new MyTableModel(); // 通过model建立一个表 table = new JTable(model); // 将表放到滚动面板里 scrollPane = new JScrollPane(table); scrollPane.setBounds(224, 34, 465, 426); frame.getContentPane().add(scrollPane); table.addMouseListener(new tableListener()); } // 为左侧表格赋值 private void setLeftPanel(String stu_id, String name, String birthday, String sex, String province, String city) { numberText.setText(stu_id); nameText.setText(name); birthdayText.setText(birthday); maleButton.setSelected(false); femaleButton.setSelected(false); if (sex.equals("男")) maleButton.setSelected(true); else femaleButton.setSelected(true); provinceText.setText(province); cityText.setText(city); } // 取得左侧表格值 private Student getLeftPanel() { Student st = new Student(); st.setStu_id(numberText.getText()); st.setStu_name(nameText.getText()); st.setBirthday(birthdayText.getText()); st.setSex(maleButton.isSelected() ? "男" : "女"); st.setProvince(provinceText.getText()); st.setCity(cityText.getText()); return st; } private void initialize() throws Exception { frame = new JFrame(); frame.setBounds(100, 100, 756, 559); frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); frame.getContentPane().setLayout(null); JLabel lblNewLabel = new JLabel("学生"); lblNewLabel.setBounds(59, 10, 54, 27); frame.getContentPane().add(lblNewLabel); numberText = new JTextField(); numberText.setBounds(59, 47, 102, 40); frame.getContentPane().add(numberText); numberText.setColumns(10); nameText = new JTextField(); nameText.setColumns(10); nameText.setBounds(59, 97, 102, 40); frame.getContentPane().add(nameText); birthdayText = new JTextField(); birthdayText.setColumns(10); birthdayText.setBounds(59, 147, 102, 40); frame.getContentPane().add(birthdayText); maleButton = new JRadioButton("男"); maleButton.setBounds(55, 219, 53, 23); frame.getContentPane().add(maleButton); femaleButton = new JRadioButton("女"); femaleButton.setBounds(110, 219, 51, 23); frame.getContentPane().add(femaleButton); provinceText = new JTextField(); provinceText.setColumns(10); provinceText.setBounds(59, 266, 102, 40); frame.getContentPane().add(provinceText); cityText = new JTextField(); cityText.setColumns(10); cityText.setBounds(59, 316, 102, 40); frame.getContentPane().add(cityText); addButton = new JButton("添加"); addButton.setBounds(69, 366, 93, 27); frame.getContentPane().add(addButton); deleteButton = new JButton("删除"); deleteButton.setBounds(68, 403, 93, 27); frame.getContentPane().add(deleteButton); refreshButton = new JButton("更新"); refreshButton.setBounds(68, 440, 93, 27); frame.getContentPane().add(refreshButton); refreshTable(); queryButton = new JButton("查询"); queryButton.setBounds(456, 470, 93, 27); frame.getContentPane().add(queryButton); queryNumberText = new JTextField(); queryNumberText.setColumns(10); queryNumberText.setBounds(344, 470, 102, 27); frame.getContentPane().add(queryNumberText); JLabel label = new JLabel("学号"); label.setBounds(280, 470, 54, 27); frame.getContentPane().add(label); label_1 = new JLabel("学号"); label_1.setBounds(14, 53, 35, 27); frame.getContentPane().add(label_1); label_2 = new JLabel("名字"); label_2.setBounds(14, 97, 35, 27); frame.getContentPane().add(label_2); label_3 = new JLabel("生日"); label_3.setBounds(14, 147, 35, 27); frame.getContentPane().add(label_3); label_4 = new JLabel("省份"); label_4.setBounds(10, 271, 39, 28); frame.getContentPane().add(label_4); label_5 = new JLabel("城市"); label_5.setBounds(10, 328, 35, 23); frame.getContentPane().add(label_5); queryButton.addActionListener(new queryButtonListener()); addButton.addActionListener(new addButtonListener()); deleteButton.addActionListener(new deleteButtonListener()); refreshButton.addActionListener(new refreshButtonListener()); maleButton.addActionListener(new maleButtonListener()); femaleButton.addActionListener(new femaleButtonListener()); } // 查询一个学生 private class queryButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { if (!queryNumberText.getText().equals("")) { try { Student st = dbc.get(queryNumberText.getText()); if (st != null) { setLeftPanel(st.getStu_id(), st.getStu_name(), st.getBirthday(), st.getSex(), st.getProvince(), st.getCity()); int row = new MyTableModel().getRow(st.getStu_id()); if (row != -1) { table.changeSelection(row, row, false, false); } } else { JOptionPane.showMessageDialog(null, "查无此人"); } } catch (Exception ex) { ex.printStackTrace(); } } else { JOptionPane.showMessageDialog(null, "请输入学号"); } } } // 添加一个学生 private class addButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { Student st = getLeftPanel(); //如果有空项 if (st.getStu_id().equals("") || st.getStu_name().equals("") || st.getProvince().equals("") || st.getCity().equals("")) { JOptionPane.showMessageDialog(null, "有些信息没有填"); return; } //如果日期格式不对 if (!dateFormatIsCorrect(st.getBirthday())) { JOptionPane.showMessageDialog(null, "生日格式错误，应为yyyy-mm-dd"); return; } //如果学号和别人重复 try { if (new MyTableModel().getRow(st.getStu_id())!=-1) { JOptionPane.showMessageDialog(null, "学号重复了"); return; } } catch (Exception ex) { ex.printStackTrace(); } //如果没有选择性别 if (!maleButton.isSelected()&&!femaleButton.isSelected()) { JOptionPane.showMessageDialog(null, "请选择你的性别"); return; } if (!st.getStu_id().equals("")) { try { dbc.add(st); //更新列表 refreshTable(); //选中当前这个新添加的人 int row = new MyTableModel().getRow(st.getStu_id()); if (row != -1) { table.changeSelection(row, row, false, false); } JOptionPane.showMessageDialog(null, "添加成功"); } catch (Exception ex) { ex.printStackTrace(); } } else { JOptionPane.showMessageDialog(null, "请输入学号！"); } } } // 删除一个学生 private class deleteButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { String stu_id = numberText.getText(); //System.out.println(stu_id); //如果没有这个学号 try { int row = new MyTableModel().getRow(stu_id); if (row == -1) { JOptionPane.showMessageDialog(null, "没有这个学号"); return; } } catch (Exception ex) { //ex.printStackTrace(); return; } if (!stu_id.equals("")) { try { if (dbc.del(stu_id)) { JOptionPane.showMessageDialog(null, "删除成功"); refreshTable(); } else { JOptionPane.showMessageDialog(null, "删除失败"); } } catch (Exception ex) { ex.printStackTrace(); } } else { JOptionPane.showMessageDialog(null, "请输入学号！"); } } } //更新一个学生的信息 private class refreshButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { Student st = getLeftPanel(); if (!st.getStu_id().equals("")) { try { if (dbc.change(st.getStu_id(), st)) { JOptionPane.showMessageDialog(null, "更新成功"); refreshTable(); //这个学生变成选中状态 int row = new MyTableModel().getRow(st.getStu_id()); if (row != -1) { table.changeSelection(row, row, false, false); } } else { JOptionPane.showMessageDialog(null, "更新失败"); } } catch (Exception ex) { ex.printStackTrace(); } } else { JOptionPane.showMessageDialog(null, "请输入学号！"); } } } //选中男性按钮 private class maleButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { femaleButton.setSelected(false); } } //选中女性按钮 private class femaleButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { maleButton.setSelected(false); } } //表格事件 private class tableListener implements MouseListener { @Override public void mouseClicked(MouseEvent e) { try { mtm = new MyTableModel(); } catch (Exception ex) { ex.printStackTrace(); } int row = table.getSelectedRow(); setLeftPanel((String)mtm.getValueAt(row, 0), (String)mtm.getValueAt(row, 1), (String)mtm.getValueAt(row, 2), (String)mtm.getValueAt(row, 3), (String)mtm.getValueAt(row, 4), (String)mtm.getValueAt(row, 5)); } public void mousePressed(MouseEvent e) {} public void mouseReleased(MouseEvent e) {} public void mouseEntered(MouseEvent e) {} public void mouseExited(MouseEvent e) {} } //确保输入日期格式正确 private boolean dateFormatIsCorrect(String birthday) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 此处会抛异常 try { Date date = sdf.parse(birthday); return true; } catch (Exception e) { return false; } }}
最后的效果（由于每次操作表格都会直接更新，所以全部检索按钮就不需要了）
JDBC实验
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。