创建数据库student（mysql建student表）

基于Java swing Mysql实现学生信息管理系统：主要实现JDBC对学生信息进行增删改查，应付一般课设足矣，分享给大家。

源码：

https://github.com/ZhuangM/student.git

创建数据库student,mysql建student表(1)

1、开发环境：jdk7 MySQL5 win7

代码结构：model-DAO-view

2、数据库设计——建库建表语句

CREATE DATABASE student;

DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `username` varchar(20) NOT NULL, `password` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; LOCK TABLES `admin` WRITE; INSERT INTO `admin` VALUES (1,'admin','admin','admin'); UNLOCK TABLES; DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `sno` varchar(20) NOT NULL, `department` varchar(20) NOT NULL, `hometown` varchar(20) NOT NULL, `mark` varchar(20) NOT NULL, `email` varchar(20) NOT NULL, `tel` varchar(20) NOT NULL, `sex` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; INSERT INTO `student` VALUES (18,'张三','001','信息科学技术学院','辽宁','80','zhangsan@163.com','13888888888','男'),(19,'李四','002','理学院','上海','70','lisi@sina.com','13812341234','男'),(20,'王五','003','外国语学院','北京','88','wangwu@126.com','13698765432','女'); UNLOCK TABLES;

3、model——管理员、学生

/** * 项目名：student * 修改历史： */ package com.student.model; /** * 模块说明：admin * */ public class Admin { private int id; private String name; private String username; private String password; public String getName() { return name; } public void setName(String name) { this.name = name; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } }

/** * 项目名：student * 修改历史： */ package com.student.model; /** * 模块说明：学生 * */ public class Student { private int id; private String sno;// 学号 private String name; private String sex; private String department;// 院系 private String homeTown;// 籍贯 private String mark;// 学分 private String email; private String tel;// 联系方式 public int getId() { return id; } public void setId(int id) { this.id = id; } public String getSno() { return sno; } public void setSno(String sno) { this.sno = sno; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getDepartment() { return department; } public void setDepartment(String department) { this.department = department; } public String getHomeTown() { return homeTown; } public void setHomeTown(String homeTown) { this.homeTown = homeTown; } public String getMark() { return mark; } public void setMark(String mark) { this.mark = mark; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel; } }

4、工具类DBUtil（对jdbc进行封装）

/** * 项目名：student * 修改历史： */ package com.student.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.student.AppConstants; /** * 模块说明：数据库工具类 * */ public class DBUtil { private static DBUtil db; private Connection conn; private PreparedStatement ps; private ResultSet rs; private DBUtil() { } public static DBUtil getDBUtil() { if (db == null) { db = new DBUtil(); } return db; } public int executeUpdate(String sql) { int result = -1; if (getConn() == null) { return result; } try { ps = conn.prepareStatement(sql); result = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return result; } public int executeUpdate(String sql, Object[] obj) { int result = -1; if (getConn() == null) { return result; } try { ps = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i ) { ps.setObject(i 1, obj[i]); } result = ps.executeUpdate(); close(); } catch (SQLException e) { e.printStackTrace(); } return result; } public ResultSet executeQuery(String sql) { if (getConn() == null) { return null; } try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return rs; } public ResultSet executeQuery(String sql, Object[] obj) { if (getConn() == null) { return null; } try { ps = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i ) { ps.setObject(i 1, obj[i]); } rs = ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return rs; } private Connection getConn() { try { if (conn == null || conn.isClosed()) { Class.forName(AppConstants.JDBC_DRIVER); conn = DriverManager.getConnection(AppConstants.JDBC_URL, AppConstants.JDBC_USERNAME, AppConstants.JDBC_PASSWORD); } } catch (ClassNotFoundException e) { System.out.println("jdbc driver is not found."); e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } public void close() { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }

5、dao：主要调用DBUtil操作相应的model——增删改查

BaseDAO.java

/** * 项目名：student * 修改历史： */ package com.student.base; import java.sql.ResultSet; import java.sql.SQLException; import com.student.DAO; import com.student.dao.AdminDAO; import com.student.dao.StudentDAO; import com.student.util.DBUtil; /** * 模块说明： DAO基类 * */ public abstract class BaseDAO { protected final DBUtil db = DBUtil.getDBUtil(); protected ResultSet rs; private static BaseDAO baseDAO; public BaseDAO() { init(); } private void init() { // buildAbilityDAO(); } // protected abstract void buildAbilityDAO(); public static synchronized BaseDAO getAbilityDAO(DAO dao) { switch (dao) { case AdminDAO: if (baseDAO == null || baseDAO.getClass() != AdminDAO.class) { baseDAO = AdminDAO.getInstance(); } break; case StudentDAO: if (baseDAO == null || baseDAO.getClass() != StudentDAO.class) { baseDAO = StudentDAO.getInstance(); } break; default: break; } return baseDAO; } protected void destroy() { try { if (rs != null) { rs.close(); } } catch (SQLException se) { se.printStackTrace(); } finally { db.close(); } } }

AdminDAO.java

/** * 项目名：student * 修改历史： */ package com.student.dao; import java.sql.SQLException; import com.student.base.BaseDAO; /** * 模块说明：管理员增删改查 * */ public class AdminDAO extends BaseDAO { private static AdminDAO ad = null; public static synchronized AdminDAO getInstance() { if (ad == null) { ad = new AdminDAO(); } return ad; } public boolean queryForLogin(String username, String password) { boolean result = false; if (username.length() == 0 || password.length() == 0) { return result; } String sql = "select * from admin where username=? and password=?"; String[] param = { username, password }; rs = db.executeQuery(sql, param); try { if (rs.next()) { result = true; } } catch (SQLException e) { e.printStackTrace(); } finally { destroy(); } return result; } }

StudentDAO.java

/** * 项目名：student * 修改历史： */ package com.student.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.student.base.BaseDAO; import com.student.model.Student; /** * 模块说明：学生增删改查 * */ public class StudentDAO extends BaseDAO { private final int fieldNum = 9; private final int showNum = 15; private static StudentDAO sd = null; public static synchronized StudentDAO getInstance() { if (sd == null) { sd = new StudentDAO(); } return sd; } // update public boolean update(Student stu) { boolean result = false; if (stu == null) { return result; } try { // check if (queryBySno(stu.getSno()) == 0) { return result; } // update String sql = "update student set sex=?,department=?,email=?,tel=?,hometown=?,mark=? where name=? and sno=?"; String[] param = { stu.getSex(), stu.getDepartment(), stu.getEmail(), stu.getTel(), stu.getHomeTown(), stu.getMark(), stu.getName(), stu.getSno() }; int rowCount = db.executeUpdate(sql, param); if (rowCount == 1) { result = true; } } catch (SQLException se) { se.printStackTrace(); } finally { destroy(); } return result; } // delete public boolean delete(Student stu) { boolean result = false; if (stu == null) { return result; } String sql = "delete from student where name=? and sno=?"; String[] param = { stu.getName(), stu.getSno() }; int rowCount = db.executeUpdate(sql, param); if (rowCount == 1) { result = true; } destroy(); return result; } // add public boolean add(Student stu) { boolean result = false; if (stu == null) { return result; } try { // check if (queryBySno(stu.getSno()) == 1) { return result; } // insert String sql = "insert into student(name,sno,sex,department,hometown,mark,email,tel) values(?,?,?,?,?,?,?,?)"; String[] param = { stu.getName(), stu.getSno(), stu.getSex(), stu.getDepartment(), stu.getHomeTown(), stu.getMark(), stu.getEmail(), stu.getTel() }; if (db.executeUpdate(sql, param) == 1) { result = true; } } catch (SQLException se) { se.printStackTrace(); } finally { destroy(); } return result; } // query by name public String[][] queryByName(String name) { String[][] result = null; if (name.length() < 0) { return result; } List<Student> stus = new ArrayList<Student>(); int i = 0; String sql = "select * from student where name like ?"; String[] param = { "%" name "%" }; rs = db.executeQuery(sql, param); try { while (rs.next()) { buildList(rs, stus, i); i ; } if (stus.size() > 0) { result = new String[stus.size()][fieldNum]; for (int j = 0; j < stus.size(); j ) { buildResult(result, stus, j); } } } catch (SQLException se) { se.printStackTrace(); } finally { destroy(); } return result; } // query public String[][] list(int pageNum) { String[][] result = null; if (pageNum < 1) { return result; } List<Student> stus = new ArrayList<Student>(); int i = 0; int beginNum = (pageNum - 1) * showNum; String sql = "select * from student limit ?,?"; Integer[] param = { beginNum, showNum }; rs = db.executeQuery(sql, param); try { while (rs.next()) { buildList(rs, stus, i); i ; } if (stus.size() > 0) { result = new String[stus.size()][fieldNum]; for (int j = 0; j < stus.size(); j ) { buildResult(result, stus, j); } } } catch (SQLException se) { se.printStackTrace(); } finally { destroy(); } return result; } // 将rs记录添加到list中 private void buildList(ResultSet rs, List<Student> list, int i) throws SQLException { Student stu = new Student(); stu.setId(i 1); stu.setName(rs.getString("name")); stu.setDepartment(rs.getString("department")); stu.setEmail(rs.getString("email")); stu.setHomeTown(rs.getString("hometown")); stu.setMark(rs.getString("mark")); stu.setSex(rs.getString("sex")); stu.setSno(rs.getString("sno")); stu.setTel(rs.getString("tel")); list.add(stu); } // 将list中记录添加到二维数组中 private void buildResult(String[][] result, List<Student> stus, int j) { Student stu = stus.get(j); result[j][0] = String.valueOf(stu.getId()); result[j][1] = stu.getName(); result[j][2] = stu.getSno(); result[j][3] = stu.getSex(); result[j][4] = stu.getDepartment(); result[j][5] = stu.getHomeTown(); result[j][6] = stu.getMark(); result[j][7] = stu.getEmail(); result[j][8] = stu.getTel(); } // query by sno private int queryBySno(String sno) throws SQLException { int result = 0; if ("".equals(sno) || sno == null) { return result; } String checkSql = "select * from student where sno=?"; String[] checkParam = { sno }; rs = db.executeQuery(checkSql, checkParam); if (rs.next()) { result = 1; } return result; } }

6、 view：与用户交互的界面（包括LoginView.java、MainView.java、AddView.java、DeleteView.java、UpdateView.java），主要使用DAO提供的接口，由于篇幅原因，仅列出MainView即首页。

/** * 项目名：student * 修改历史： */ package com.student.view; import java.awt.BorderLayout; import java.awt.GridLayout; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.awt.event.KeyAdapter; import java.awt.event.KeyEvent; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTable; import javax.swing.JTextField; import javax.swing.table.DefaultTableCellRenderer; import javax.swing.table.DefaultTableModel; import javax.swing.table.TableColumn; import com.student.AppConstants; import com.student.DAO; import com.student.base.BaseDAO; import com.student.dao.StudentDAO; /** * 模块说明：首页 * */ public class MainView extends JFrame { private static final long serialVersionUID = 5870864087464173884L; private final int maxPageNum = 99; private JPanel jPanelNorth, jPanelSouth, jPanelCenter; private JButton jButtonFirst, jButtonLast, jButtonNext, jButtonPre, jButtonAdd, jButtonDelete, jButtonUpdate, jButtonFind; private JLabel currPageNumJLabel; private JTextField condition; public static JTable jTable; private JScrollPane jScrollPane; private DefaultTableModel myTableModel; public static String[] column = { "id", AppConstants.STUDENT_NAME, AppConstants.STUDENT_SNO, AppConstants.STUDENT_SEX, AppConstants.STUDENT_DEPARTMETN, AppConstants.STUDENT_HOMETOWN, AppConstants.STUDENT_MARK, AppConstants.STUDENT_EMAIL, AppConstants.STUDENT_TEL }; public static int currPageNum = 1; public MainView() { init(); } private void init() { setTitle(AppConstants.MAINVIEW_TITLE); // north panel jPanelNorth = new JPanel(); jPanelNorth.setLayout(new GridLayout(1, 5)); condition = new JTextField(AppConstants.PARAM_FIND_CONDITION); condition.addKeyListener(new FindListener()); jPanelNorth.add(condition); // query by name jButtonFind = new JButton(AppConstants.PARAM_FIND); jButtonFind.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { find(); } }); jButtonFind.addKeyListener(new FindListener()); // add jPanelNorth.add(jButtonFind); jButtonAdd = new JButton(AppConstants.PARAM_ADD); jButtonAdd.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { new AddView(); } }); jPanelNorth.add(jButtonAdd); // delete jButtonDelete = new JButton(AppConstants.PARAM_DELETE); jButtonDelete.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { new DeleteView(); } }); jPanelNorth.add(jButtonDelete); // update jButtonUpdate = new JButton(AppConstants.PARAM_UPDATE); jButtonUpdate.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { new UpdateView(); } }); jPanelNorth.add(jButtonUpdate); // center panel jPanelCenter = new JPanel(); jPanelCenter.setLayout(new GridLayout(1, 1)); // init jTable String[][] result = ((StudentDAO) BaseDAO.getAbilityDAO(DAO.StudentDAO)).list(currPageNum); myTableModel = new DefaultTableModel(result, column); jTable = new JTable(myTableModel); DefaultTableCellRenderer cr = new DefaultTableCellRenderer(); cr.setHorizontalAlignment(JLabel.CENTER); jTable.setDefaultRenderer(Object.class, cr); initJTable(jTable, result); jScrollPane = new JScrollPane(jTable); jPanelCenter.add(jScrollPane); // south panel jPanelSouth = new JPanel(); jPanelSouth.setLayout(new GridLayout(1, 5)); jButtonFirst = new JButton(AppConstants.MAINVIEW_FIRST); jButtonFirst.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { currPageNum = 1; String[][] result = ((StudentDAO) BaseDAO.getAbilityDAO(DAO.StudentDAO)).list(currPageNum); initJTable(jTable, result); currPageNumJLabel.setText(AppConstants.MAINVIEW_PAGENUM_JLABEL_DI currPageNum AppConstants.MAINVIEW_PAGENUM_JLABEL_YE); } }); jButtonPre = new JButton(AppConstants.MAINVIEW_PRE); jButtonPre.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { currPageNum--; if (currPageNum <= 0) { currPageNum = 1; } String[][] result = ((StudentDAO) BaseDAO.getAbilityDAO(DAO.StudentDAO)).list(currPageNum); initJTable(jTable, result); currPageNumJLabel.setText(AppConstants.MAINVIEW_PAGENUM_JLABEL_DI currPageNum AppConstants.MAINVIEW_PAGENUM_JLABEL_YE); } }); jButtonNext = new JButton(AppConstants.MAINVIEW_NEXT); jButtonNext.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { currPageNum ; if (currPageNum > maxPageNum) { currPageNum = maxPageNum; } String[][] result = ((StudentDAO) BaseDAO.getAbilityDAO(DAO.StudentDAO)).list(currPageNum); initJTable(jTable, result); currPageNumJLabel.setText(AppConstants.MAINVIEW_PAGENUM_JLABEL_DI currPageNum AppConstants.MAINVIEW_PAGENUM_JLABEL_YE); } }); jButtonLast = new JButton(AppConstants.MAINVIEW_LAST); jButtonLast.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { currPageNum = maxPageNum; String[][] result = ((StudentDAO) BaseDAO.getAbilityDAO(DAO.StudentDAO)).list(currPageNum); initJTable(jTable, result); currPageNumJLabel.setText(AppConstants.MAINVIEW_PAGENUM_JLABEL_DI currPageNum AppConstants.MAINVIEW_PAGENUM_JLABEL_YE); } }); currPageNumJLabel = new JLabel( AppConstants.MAINVIEW_PAGENUM_JLABEL_DI currPageNum AppConstants.MAINVIEW_PAGENUM_JLABEL_YE); currPageNumJLabel.setHorizontalAlignment(JLabel.CENTER); jPanelSouth.add(jButtonFirst); jPanelSouth.add(jButtonPre); jPanelSouth.add(currPageNumJLabel); jPanelSouth.add(jButtonNext); jPanelSouth.add(jButtonLast); this.add(jPanelNorth, BorderLayout.NORTH); this.add(jPanelCenter, BorderLayout.CENTER); this.add(jPanelSouth, BorderLayout.SOUTH); setBounds(400, 200, 750, 340); setResizable(false); setDefaultCloseOperation(DISPOSE_ON_CLOSE); setVisible(true); } public static void initJTable(JTable jTable, String[][] result) { ((DefaultTableModel) jTable.getModel()).setDataVector(result, column); jTable.setRowHeight(20); TableColumn firsetColumn = jTable.getColumnModel().getColumn(0); firsetColumn.setPreferredWidth(30); firsetColumn.setMaxWidth(30); firsetColumn.setMinWidth(30); TableColumn secondColumn = jTable.getColumnModel().getColumn(1); secondColumn.setPreferredWidth(60); secondColumn.setMaxWidth(60); secondColumn.setMinWidth(60); TableColumn thirdColumn = jTable.getColumnModel().getColumn(2); thirdColumn.setPreferredWidth(90); thirdColumn.setMaxWidth(90); thirdColumn.setMinWidth(90); TableColumn fourthColumn = jTable.getColumnModel().getColumn(3); fourthColumn.setPreferredWidth(30); fourthColumn.setMaxWidth(30); fourthColumn.setMinWidth(30); TableColumn seventhColumn = jTable.getColumnModel().getColumn(6); seventhColumn.setPreferredWidth(30); seventhColumn.setMaxWidth(30); seventhColumn.setMinWidth(30); TableColumn ninthColumn = jTable.getColumnModel().getColumn(8); ninthColumn.setPreferredWidth(90); ninthColumn.setMaxWidth(90); ninthColumn.setMinWidth(90); } private class FindListener extends KeyAdapter { @Override public void keyPressed(KeyEvent e) { if (e.getKeyCode() == KeyEvent.VK_ENTER) { find(); } } } private void find() { currPageNum = 0; String param = condition.getText(); if ("".equals(param) || param == null) { initJTable(MainView.jTable, null); currPageNumJLabel.setText(AppConstants.MAINVIEW_FIND_JLABEL); return; } String[][] result = ((StudentDAO) BaseDAO.getAbilityDAO(DAO.StudentDAO)).queryByName(param); condition.setText(""); initJTable(MainView.jTable, result); currPageNumJLabel.setText(AppConstants.MAINVIEW_FIND_JLABEL); } }

PS：如果觉得我的分享不错，欢迎大家随手点赞、转发。