Java+MySQL实现学生信息管理系统源码

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; 

/**  * 项目名：student  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-上午9:42:48  */ 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  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-上午9:42:36  */ 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;  }  } 

/**  * 项目名：student  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-上午9:43:21  */ 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();   }  } } 

/**  * 项目名：student  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-上午10:04:37  */ 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();   }  } } 

/**  * 项目名：student  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-上午9:59:58  */ 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;  }  } 

/**  * 项目名：student  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-上午10:00:07  */ 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;  }  } 

/**  * 项目名：student  * 修改历史：  * 作者： MZ  * 创建时间： 2016年1月6日-下午1:37:39  */ 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);  }  }