用java写一个学生管理系统

学习java一学期了,写了一个简单的学生管理系统。用swing写界面,连接后台mysql数据库。
导入导出使用的是poi的jar包。
在这里插入图片描述
在这里插入图片描述
在学习过程中有很多没有教过的知识需要自己去网络上或书上查找资料。对一些类的操作有了练习了解。比如List类用来存放数据、swing的使用、一对一关系、正则表达式、事件监听、数据库相关操作、还有输入流、输出流等知识。有了一定的模块化思想,比如写了一个connection类封装数据库连接的代码,其他类再进行调用。但还是很多地方没有写的很完善,代码的冗余性还比较高,可以进一步优化。还学习了怎么使用WindowBuilder插件绘制swing界面。通过项目实战学习到很多知识,显著的对本学期知识进行了巩固加强。

XsglMain.java

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JRootPane;
import javax.swing.border.EmptyBorder;
import javax.swing.event.TableModelEvent;
import javax.swing.table.DefaultTableModel;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JTable;
import javax.swing.JScrollPane;
import java.awt.event.ActionListener;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.regex.Pattern;
import java.awt.event.ActionEvent;
import javax.swing.JLabel;
import javax.swing.JMenuBar;
import javax.swing.JMenu;
import javax.swing.JMenuItem;
import java.awt.Color;
/*
 * 主界面
 */
public class XsglMain extends JFrame implements ActionListener {
	private ImageIcon background;
	private JRootPane imagePanel;
	private JPanel contentPane;
	private JTextField text1;
	JTable table;
	private DefaultTableModel tableModel;
	XsglInformation stuInfo ;
	boolean flag = false;
	private JLabel mainInfo = new JLabel();
	XsglLogin login = null;
	public void setXsglLogin(XsglLogin login){
		this.login = login;
	}
	public static void main(String[] args) {		
		XsglMain frame = new XsglMain();
					
	}

	/**
	 * Create the frame.
	 */
	public XsglMain() {
		setTitle("\u5B66\u751F\u7BA1\u7406\u7CFB\u7EDF");
		setVisible(true);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 722, 514);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		JPanel panel = new JPanel();
		panel.setBounds(5, 5, 697, 20);
		contentPane.add(panel);
		panel.setLayout(null);
		
		JMenuBar menuBar = new JMenuBar();
		menuBar.setBounds(0, 0, 697, 20);
		panel.add(menuBar);
		
		JMenu menu = new JMenu("\u529F\u80FD");
		menu.setForeground(Color.DARK_GRAY);
		menuBar.add(menu);
		
		JMenuItem mntmexcel = new JMenuItem("\u5BFC\u51FA\u81F3Excel");
		mntmexcel.setHorizontalAlignment(SwingConstants.LEFT);
		mntmexcel.addActionListener(this);
		mntmexcel.setActionCommand("exportExcelFile");
		menu.add(mntmexcel);
		
		JMenuItem mntmexcel_1 = new JMenuItem("\u4ECEExcel\u5BFC\u5165");
		mntmexcel_1.setHorizontalAlignment(SwingConstants.LEFT);
		mntmexcel_1.addActionListener(this);
		mntmexcel_1.setActionCommand("fromExcel");
		menu.add(mntmexcel_1);
		
		JMenu menu_1 = new JMenu("\u7528\u6237");
		menu_1.setForeground(Color.DARK_GRAY);
		menuBar.add(menu_1);
		
		JMenuItem logout = new JMenuItem("\u5207\u6362\u8D26\u6237");
		logout.setHorizontalAlignment(SwingConstants.LEFT);
		logout.addActionListener(this);
		logout.setActionCommand("logout");
		menu_1.add(logout);
		
		JMenuItem thisInfo = new JMenuItem("\u5F53\u524D\u7528\u6237\u4FE1\u606F");
		thisInfo.addActionListener(this);
		thisInfo.setActionCommand("getInfo");
		menu_1.add(thisInfo);
		
		JPanel panel_1 = new JPanel();
		panel_1.setBounds(5, 429, 697, 33);
		contentPane.add(panel_1);
		panel_1.setLayout(null);
		
		JButton add = new JButton("\u6DFB\u52A0");		
		add.addActionListener(this);
		add.setActionCommand("add");
		add.setBounds(399, 0, 93, 33);
		panel_1.add(add);
		
		JButton delete = new JButton("\u5220\u9664");
		delete.addActionListener(this);
		delete.setActionCommand("delete");
		delete.setBounds(502, 0, 93, 33);
		panel_1.add(delete);
		
		JButton all = new JButton("\u5237\u65B0");
		all.addActionListener(this);
		all.setActionCommand("all");
		all.setBounds(604, 0, 93, 33);
		panel_1.add(all);
		
		
		mainInfo.setBounds(0, 10, 175, 15);
		panel_1.add(mainInfo);
		mainInfo.setText("");
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(5, 65, 697, 354);
		contentPane.add(scrollPane);
		table = new JTable();	
		tableModel =(DefaultTableModel) table.getModel() ;
		stuInfo = new XsglInformation("select * from student");		
		
		tableModel.setDataVector(this.stuInfo.data, this.stuInfo.columns);
		tableModel.fireTableDataChanged();
		
		scrollPane.setViewportView(table);
		
		JButton search = new JButton("");
		search.setForeground(Color.WHITE);
		search.setIcon(new ImageIcon("F:\\JavaTest\\\u5B66\u751F\u7BA1\u7406\u7CFB\u7EDF 2.0\\2.jpg"));
		search.setBounds(672, 35, 20, 20);
		contentPane.add(search);
		search.setActionCommand("search");
		search.addActionListener(this);
		search.setActionCommand("search");		
		
		this.tableModel.addTableModelListener(e -> tableChanged(e));//表格事件监听
		
//
//		//背景部分
//		background = new ImageIcon("F:/JavaTest/学生管理系统 2.0/bin/1.jpg");// 背景图片
//		 ImageIcon img = new ImageIcon("F:/JavaTest/学生管理系统 2.0/bin/1.jpg"); 
//		  JLabel imgLabel = new JLabel(img); //JLabel imgLabel = new JLabel(new ImageIcon("back.jpg"));
//		  getLayeredPane().add(imgLabel, new Integer(Integer.MIN_VALUE)); 
//		  imgLabel.setBounds(0,0,img.getIconWidth(), img.getIconHeight()); //背景图片的位置
//		  //将contentPane设置成透明的 
//		  ((JPanel)getContentPane()).setOpaque(false);
		  
		  text1 = new JTextField();
		  text1.setBounds(5, 27, 697, 34);
		  contentPane.add(text1);
		  text1.setText("\u8BF7\u8F93\u5165\u5173\u952E\u5B57\u8FDB\u884C\u67E5\u627E\uFF08\u53EF\u4EE5\u4E3A\u5B66\u53F7\u3001\u59D3\u540D\u3001\u5E74\u9F84\u3001\u6027\u522B\uFF09");
		  text1.setHorizontalAlignment(SwingConstants.LEFT);
		  text1.setColumns(40);
		 
	}

	@Override
	public void actionPerformed(ActionEvent e) {
		// TODO 自动生成的方法存根		
		if (e.getActionCommand().equals("search")){
			//调用查询模块
			flag = true;
			String name = this.text1.getText().trim();
			String i=this.keyWord();
			String sql = "select * from student where "+i+" = '"+name+"'";
			stuInfo = new XsglInformation(sql);				
			tableModel.setDataVector(this.stuInfo.data, this.stuInfo.columns);
			tableModel.fireTableDataChanged();
			this.mainInfo.setText("搜索'"+this.text1.getText()+"'完成");
			flag = false;
		}else if (e.getActionCommand().equals("all")){	
			//刷新模块
			flag = true;
			stuInfo = new XsglInformation("select * from student");
			tableModel.setDataVector(stuInfo.data,stuInfo.columns);
			tableModel.fireTableDataChanged();
			this.mainInfo.setText("刷新成功");
			flag = false;
		}else if (e.getActionCommand().equals("add")){	
			//增加模块
			if(this.login.identity.equals("管理员")){
			flag = true;
			String sql = "select * from student where Sno = '20001000'";
			try {
			PreparedStatement pstmt = stuInfo.conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();						
								
			if(rs.next()){
				this.mainInfo.setText("请先进行修改再继续添加下一行");
			}else{
			try {
				stuInfo.add("99999999","姓名", "18" ,"男女");
			} catch (Exception e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
				}		
			Vector<Object> date = new Vector<Object>();
			date.add("99999999");
			date.add("姓名");
			date.add("18");
			date.add("男女");
			this.tableModel.addRow(date);
			this.mainInfo.setText("添加一行成功");			
			flag = false;
				}
			} catch (SQLException e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
			}	
			flag = false;
			}else{
				this.mainInfo.setText("您是学生,没有添加权限");
			}
	    }else if (e.getActionCommand().equals("delete")){
			//调用删除模块 
	    	if(this.login.identity.equals("管理员")){
	    	flag = true;
			int row = this.table.getSelectedRow() ;	
		
			if(row == -1){
				this.mainInfo.setText("请先选择一行");
			}else{
			Object value = this.table.getValueAt(row,0);
			try {
				this.stuInfo.delete(value);
			} catch (SQLException e2) {
				// TODO 自动生成的 catch 块
				e2.printStackTrace();
			}
			try {
				stuInfo.searchAll();
			} catch (SQLException e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
			}
			tableModel.setDataVector(stuInfo.data, stuInfo.columns);
			tableModel.fireTableDataChanged();
			this.mainInfo.setText("删除成功");
			}
			flag = false;
	    	}else{
				this.mainInfo.setText("您是学生,没有删除权限");
				}	    	
		}else if (e.getActionCommand().equals("exportExcelFile")){
			//导出模块			
			try {
				new Xsgl_Excel().exportTable(table);
			} catch (Exception e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
			}		
		}else if (e.getActionCommand().equals("fromExcel")){
			//导入模块			
			if(this.login.identity.equals("管理员")){
				flag = true;
				new Xsgl_Excel().fromExcel();	
				this.mainInfo.setText("导入成功");	
				//刷新表格信息
				stuInfo = new XsglInformation("select * from student");
				tableModel.setDataVector(stuInfo.data,stuInfo.columns);
				tableModel.fireTableDataChanged();
				flag = false;
			}else{
				this.mainInfo.setText("您是学生,没有导入权限");				
			}
		}else if (e.getActionCommand().equals("logout")){
			//注销模块	
			new XsglLogin().setVisible(true);
			setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
			this.dispose();
			
		}else if (e.getActionCommand().equals("getInfo")){
			//获得当前账户模块				
			Info in = new Info();
			in.id.setText(this.login.id);			
			try {
				in.setInfo(this.login.id);
			} catch (SQLException e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
			}			
		}		  
	}
	
	public void tableChanged(TableModelEvent e){
		if(this.login.identity.equals("管理员")){
			if(e.getType() == TableModelEvent.UPDATE && flag == false){					
				int row = table.getSelectedRow();	
		    	int column = table.getSelectedColumn();
		    	String Sno = table.getValueAt(row,0).toString();    	
		    	String Sname = table.getValueAt(row,1).toString();	  	
		    	Object Sage = table.getValueAt(row,2);	
		    	String Sgender = table.getValueAt(row,3).toString();		
		    	if(column == 0){ 
		    		if(stuInfo.matchesSno(Sno)){
		    		String sql = "UPDATE student SET Sno='"+Sno+"' WHERE Sname ='"+Sname+"'";
					stuInfo.updata(sql);
					this.mainInfo.setText("’"+Sno+"‘修改成功");
		    		}else{
		    			this.mainInfo.setText("’"+Sno+"‘输入格式不正确");
		    		}
				}else if(column == 1){
					if(stuInfo.matchesSname(Sname)){
					String sql = "UPDATE student SET Sname='"+Sname+"' WHERE Sno ='"+Sno+"'";
					new XsglInformation().updata(sql);	
					this.mainInfo.setText("’"+Sname+"‘修改成功");
					}else{
		    			this.mainInfo.setText("’"+Sname+"‘输入格式不正确");
		    		}
				}else if(column == 2){
					if(stuInfo.matchesSage(Sage.toString())){
					String sql = "UPDATE student SET Sage='"+Sage+"' WHERE Sno ='"+Sno+"'";
					new XsglInformation().updata(sql);	
					this.mainInfo.setText("’"+Sage+"‘修改成功");
					}else{
		    			this.mainInfo.setText("’"+Sage+"‘输入格式不正确");
		    		}
				}else if(column == 3){
					if(stuInfo.matchesSgender(Sgender)){
					String sql = "UPDATE student SET Sgender='"+Sgender+"' WHERE Sno ='"+Sno+"'";
					new XsglInformation().updata(sql);		
					this.mainInfo.setText("’"+Sgender+"‘修改成功");
					}else{
		    			this.mainInfo.setText("’"+Sgender+"‘输入格式不正确");
		    		}
				}else{
					System.out.println("更新失败");
				}
			}			
		}
		else{
			this.mainInfo.setText("您是学生,没有修改权限");
		}
	}
	
	public String keyWord(){
		String key = this.text1.getText().trim();
		String type = null;
		if(Pattern.matches("\\d{8}",key)){
			type = "Sno";			
		}else if(Pattern.matches("([男]|[女])+$",key)){
			type = "Sgender";			
		}else if(Pattern.matches("^[0-9]*$",key)){
			type = "Sage";			
		}else if(Pattern.matches("^[\u4E00-\u9FA5]+$",key)){
			type = "Sname";			 
		}else{
			type = null;
		}
		return type;		
	} 			
}

登录界面:
XsglLogin.java

import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import javax.swing.WindowConstants;

class LoginCheck{
	private String name ;
	private String password ;
	public LoginCheck(String name,String password) {
		this.name = name ;
		this.password = password ;

	}

	public boolean check(String ID,String pass) {
		boolean i = false;
		try {
			i = new XsglInformation().checkID(ID, pass);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return i;
	}
}
public class XsglLogin extends JFrame implements ActionListener{
	private JFrame frame = new JFrame("学生管理系统--登录") ;
	private JButton submit = new JButton("\u767B\u5F55");
	private JButton reset = new JButton("\u91CD\u7F6E");
	private JLabel nameLab = new JLabel("NAME\uFF1A") ;
	private JLabel passLab = new JLabel("PASS\uFF1A") ;
	private JLabel infoLab = new JLabel("\u8BF7\u8F93\u5165\u8D26\u53F7\u5BC6\u7801") ;
	private JTextField nameText = new JTextField(10) ;
	private JPasswordField passText = new JPasswordField() ;
	private JPanel pan = new JPanel() ;
	private JPanel imagePanel;
	private ImageIcon background;	
	private XsglMain main = null;
	String id = null;
	String identity = null;
	public XsglLogin(){
		submit.setBounds(208, 303, 126, 23);
		submit.addActionListener(this);
		submit.setActionCommand("login");
		reset.setFont(new Font("宋体", Font.PLAIN, 13));
		reset.setBounds(448, 336, 67, 19);
		reset.addActionListener(new ActionListener(){
			@Override
			public void actionPerformed(ActionEvent e) {
				if (e.getSource()==reset) {
					nameText.setText("");
					passText.setText("");
					infoLab.setText("请输入账号密码");
				}
			}
		});
		frame.getContentPane().setLayout(null);
		frame.getContentPane().setLayout(null);
		nameLab.setBounds(136, 196, 57, 15);
		frame.getContentPane().add(nameLab) ;
		passLab.setBounds(136, 242, 57, 15);
		frame.getContentPane().add(passLab) ;
		infoLab.setBounds(10, 336, 162, 15);
		frame.getContentPane().add(infoLab) ;
		nameText.setForeground(Color.BLACK);
		nameText.setBackground(Color.WHITE);
		nameText.setBounds(184, 193, 184, 21);
		frame.getContentPane().add(nameText) ;
		passText.setBounds(184, 239, 184, 21);
		frame.getContentPane().add(passText) ;
		frame.getContentPane().add(submit) ;
		frame.getContentPane().add(reset) ;
		frame.setTitle("\u5B66\u751F\u7BA1\u7406\u7CFB\u7EDF");
		
		frame.setBackground(Color.DARK_GRAY) ;
		frame.setLocation(300,200) ;
		frame.setVisible(true) ;
		
		//背景部分
		background = new ImageIcon("F:/JavaTest/学生管理系统 2.0/bin/bgbg.jpg");// 背景图片
		  JLabel label = new JLabel(background);// 把背景图片显示在一个标签里面
		  // 把标签的大小位置设置为图片刚好填充整个面板
		  label.setBounds(0, 0, background.getIconWidth(),
		    background.getIconHeight());
		  // 把内容窗格转化为JPanel,否则不能用方法setOpaque()来使内容窗格透明
		  imagePanel = (JPanel) frame.getContentPane();
		  
		  JButton btnNewButton = new JButton("\u6CE8\u518C");
		  btnNewButton.setBounds(381, 336, 67, 19);
		  frame.getContentPane().add(btnNewButton);
		  imagePanel.setOpaque(false);
		  frame.getLayeredPane().setLayout(null);
		  // 把背景图片添加到分层窗格的最底层作为背景
		  frame.getLayeredPane().add(label, new Integer(Integer.MIN_VALUE));
		  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		  frame.setSize(541, 404);
		
		setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
	}
	public static void main(String[] args) {
		new XsglLogin() ;
	}
	@Override
	public void actionPerformed(ActionEvent e) {
		if(e.getActionCommand().equals("login")){
			String ID = nameText.getText() ;
			String pass = new String(passText.getPassword()) ;
			LoginCheck login = new LoginCheck(ID,pass) ;
			if(login.check(ID,pass)){
				infoLab.setText("登陆成功") ;
				frame.setVisible(false);
				this.main = new XsglMain();
				this.id = ID;
				try {
					this.identity = new XsglInformation().setIdentity(ID);
				} catch (SQLException e1) {
					// TODO 自动生成的 catch 块
					e1.printStackTrace();
				}
				main.setXsglLogin(this);
				
				}else{
					infoLab.setText("账号或密码错误") ;			
			}
		}
	}
}

学生信息类:
存放一些方法

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.regex.Pattern;

import javax.swing.table.DefaultTableModel;

public class XsglInformation  {	
	Connection conn = new Xsgl_connection().getConn();
	DefaultTableModel model = null;
	Vector columns,data = null;
	public XsglInformation(String sql){
		try {
			this.excuteSql(sql);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		
	}
	public XsglInformation() {
		// TODO 自动生成的构造函数存根
	}
	public void delete(Object value) throws SQLException{		
		
		String sql="delete from student where Sno=?";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setObject(1, value);
		ps.executeUpdate();
		
	}
	public DefaultTableModel searchAll() throws SQLException{	
		this.excuteSql( "select * from student");				
		return model;		
	}
	
	public void add(String Sno,String Sname,String sage,String Sgender)throws Exception{
		//添加模块	
		boolean i = false;		
		if(this.matchesSno(Sno)&&this.matchesSname(Sname)&&this.matchesSage(sage)&&this.matchesSgender(Sgender)){			
			try {				
				int Sage = Integer.parseInt(sage);
				String sql="insert into student values('"+Sno+"','"+Sname+"',"+sage+",'"+Sgender+"')";
				PreparedStatement ps=conn.prepareStatement(sql);
				int result=ps.executeUpdate();
				System.out.println("成功添加一行 ( "+Sno+" "+Sname+" "+sage+" "+Sgender+" )");
			} catch (Exception e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
				}						
			}else{
				System.out.println("添加失败,信息格式输入有错");
			}
		}
	
	public boolean checkID(String ID,String pass) throws SQLException{
		String sql = "SELECT * FROM admin WHERE adminID ='"+ID+"' AND adminPassword='"+pass+"'";
		PreparedStatement ps=conn.prepareStatement(sql);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			String adminID = rs.getString(1);
			String adminPass = rs.getString(2);
			if(adminID.equals(ID)&&adminPass.equals(pass)){				
				return true;
			}
		}
		return false;
	}
	DefaultTableModel excuteSql(String sql) throws SQLException {
		// TODO 自动生成的方法存根
		this.data = new Vector(); 
		this.columns = new Vector<String>();
    	columns.add("学号");
    	columns.add("姓名");
    	columns.add("年龄 ");
    	columns.add("性别");    	
    	Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);     
       
        while (rs.next()) {        	
        	Vector row = new Vector();        
        	row.add(rs.getInt(1));
        	row.add(rs.getString(2));
        	row.add(rs.getInt(3));
        	row.add(rs.getString(4));
        	data.add(row);
        	}
        model = new DefaultTableModel(data, columns);
        return model;
        
        }
	public void updata(String sql)  {				
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			int result=ps.executeUpdate();			
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}	
									
	}	
	public boolean matchesSno(String Sno){
		String regex = "\\d{8}";
		if(Pattern.matches(regex, Sno)){
			return true;
		}else{
			System.out.println(Sno+"数据有误");
			return false;
		}
	}
	public boolean matchesSgender(String sgender) {
		// TODO 自动生成的方法存根
		String regex = "([男]|[女])+$";
		if(Pattern.matches(regex, sgender)){
			return true;
		}else{
			System.out.println(sgender+"数据有误");
			return false;
		}
	}
	public boolean matchesSage(String sage) {
		// TODO 自动生成的方法存根
		String regex = "^[0-9]*$";
		if(Pattern.matches(regex, sage)){
			return true;
		}else{
			System.out.println(sage+"数据有误");
			return false;
		}
	}
	public boolean matchesSname(String sname) {
		
		String regex = "^[\u4E00-\u9FA5]+$";
		if(Pattern.matches(regex, sname)){
			return true;
		}else{
			System.out.println(sname+"数据有误");
			return false;
		}
	}	
	public Boolean checkPrimaryKey(String primaryKey){
		String sql = "select * from student where Sno = '"+primaryKey+"'";
		Boolean flag = false;
		PreparedStatement pst;
		try {
			pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				flag = true;
			}else{
				flag = false;
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return flag;			
	}
	public String setIdentity(String ID) throws SQLException{
		String sql = "SELECT Identity FROM admin WHERE adminID ='"+ID+"'";
		String identity = null;
		PreparedStatement ps=conn.prepareStatement(sql);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			identity = rs.getString(1);					
		}
		return identity;				
	}
}

导入导出实现类:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import javax.swing.JFileChooser;
import javax.swing.JLabel;
import javax.swing.JTable;
import javax.swing.table.TableModel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Xsgl_Excel {
	Workbook wb = null;	
	String Sno ;
	String Sname;
	String Sage;
	String Sgender;
	String filePath ;
	XsglInformation info = null;
	public void exportTable(JTable table) throws Exception {
	//用POI包导出
		wb = new XSSFWorkbook();
		Sheet hs = wb.createSheet();
		TableModel tm = table.getModel();
		int column = tm.getColumnCount();
		int row = tm.getRowCount();
		for (int i = 0; i < row + 1; i++) {
			Row hr = hs.createRow(i);
			for (int j = 0; j < column; j++) {
				if (i == 0) {
					String value = tm.getColumnName(j); 					
					Cell hc = hr.createCell(j);
					hc.setCellValue(value);
					}else if (tm.getValueAt(i - 1, j) != null) {
						String value = tm.getValueAt(i - 1, j).toString();							
						Cell hc = hr.createCell(j);
						hc.setCellValue(value);	
					}
				}
			}		
	    //导出文件路径
	    String basePath = this.selectPath();
	    //文件名
	    
	    String exportFileName = ".xlsx";
	    File  file = new File(basePath+exportFileName);
	    //文件输出流
	    FileOutputStream outStream = new FileOutputStream(file);
	    wb.write(outStream);
	    outStream.flush();
	    outStream.close();
	    System.out.println("导出文件成功!文件导出路径: "+basePath+exportFileName);
	    
	}
	public String selectPath() throws Exception {
		
		JFileChooser jfc=new JFileChooser("F:\\JavaTest\\学生管理系统 2.0\\excel");  
		jfc.showDialog(new JLabel(), "选择");  
		jfc.setMultiSelectionEnabled(false);//不能多选			
		File file=jfc.getSelectedFile();
		return filePath = file.getAbsolutePath();
	}
	public void getDateFromExcel(){
		
		if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
        {
            System.out.println("文件不是excel类型");
        }else{
        	System.out.println("文件是excel类型");
        }
	}
	public Xsgl_Excel(){}
	
	public void fromExcel() {
		try {
			this.selectPath();
		} catch (Exception e1) {
			// TODO 自动生成的 catch 块
			e1.printStackTrace();
		}
		this.getDateFromExcel();		
		try {
			FileInputStream fileIn = new FileInputStream(filePath);
			wb = WorkbookFactory.create(fileIn);
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		Sheet sh = wb.getSheetAt((short)0);
		for(int i = 1;i<=sh.getLastRowNum();i++){
			Row row = sh.getRow(i);
			for(int j = 0;j<=3;j++){
				Cell cell = row.getCell((short)j);
				cell.setCellType(CellType.STRING);
				if(j==0){
					Sno = cell.getStringCellValue().toString();					
				}else if(j==1){
					Sname = cell.getStringCellValue().toString();
				}else if(j==2){					
					Sage = cell.getStringCellValue().toString();					
				}else if(j==3){
					Sgender = cell.getStringCellValue().toString();					 
				}	            
			}			
			info = new XsglInformation();
			if(info.checkPrimaryKey(Sno)){
				System.out.println("已存在"+Sno+"不能重复增加");
			}else{
				try {
					info.add(Sno, Sname, Sage, Sgender);
				} catch (Exception e) {
					// TODO 自动生成的 catch 块
					e.printStackTrace();
				}
				
			}
						
		}			
	}
}

连接数据库类

import java.sql.*;
 
public class Xsgl_connection {
	//private static String DBDRIVER = "com.mysql.cj.jdbc.Driver";
	private static String DBURL = "jdbc:mysql://localhost:3306/xsgl?serverTimezone=UTC";
	private static String DBUSERNAME = "root";
	private static String DBPASSWORD = "mysqladmin";
	Connection conn = null;	//数据库连接
	
	public Connection getConn(){
		
		try {
          //Class.forName(DBDRIVER);
            conn = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
          //  stmt= conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
		return conn;
	}
	
	public static void close(Connection conn){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
		}
	}
    public static void main(String[] args) throws SQLException {
    	//测试代码
    	String sql = "SELECT Sno,Sname,Sage,Sgender FROM student";
        Connection conn = new Xsgl_connection().getConn(); 
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);        
        rs = stmt.executeQuery(sql);
        while(rs.next()){
        	int sno = rs.getInt(1);
        	String sname = rs.getString(2);
        	int sage = rs.getInt(3);
        	String sgander = rs.getString(4);
        	System.out.println("学号:"+sno);
        	System.out.println("姓名:"+sname);
        	System.out.println("年龄:"+sage);
        	System.out.println("性别:"+sgander);
        	System.out.println("--------------");
        }
        try {
			stmt = conn.createStatement();
			 rs = stmt.executeQuery(sql);
		     close(conn);
		     System.out.println("关闭");
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
    }
}

弹出框查看当前登录信息

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.swing.JDialog;
import javax.swing.JPanel;
import javax.swing.WindowConstants;
import javax.swing.border.EmptyBorder;
import javax.swing.JLabel;

public class Info extends JDialog {
	private final JPanel contentPanel = new JPanel();
	JLabel id = new JLabel("id");
	JLabel Name = new JLabel("name");
	JLabel identity = new JLabel("identity");
	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		try {
			Info dialog = new Info();
			dialog.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Create the dialog.
	 */
	public void setInfo(String ID) throws SQLException{
		String sql = "SELECT Name,Identity FROM admin WHERE adminID ='"+ID+"'";
		PreparedStatement ps=new Xsgl_connection().getConn().prepareStatement(sql);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			String Name = rs.getString(1);
			String identity = rs.getString(2);
			this.Name.setText(Name);
			this.identity.setText(identity);
		}
				
	}
	
	public Info() {
		this.setVisible(true);
		setBounds(100, 100, 272, 231);
		getContentPane().setLayout(new BorderLayout());
		contentPanel.setBorder(new EmptyBorder(5, 5, 5, 5));
		getContentPane().add(contentPanel, BorderLayout.CENTER);
		contentPanel.setLayout(null);
		{
			JLabel label = new JLabel("\u8D26  \u540D\uFF1A");
			label.setBounds(74, 61, 50, 15);
			contentPanel.add(label);
		}
		
		
		id.setBounds(134, 61, 54, 15);
		contentPanel.add(id);
		
		JLabel label = new JLabel("\u6635  \u79F0\uFF1A");
		label.setBounds(74, 86, 54, 15);
		contentPanel.add(label);
		
		Name = new JLabel("name");
		Name.setBounds(134, 86, 54, 15);
		contentPanel.add(Name);
		
		JLabel llll = new JLabel("\u8EAB  \u4EFD\uFF1A");
		llll.setBounds(74, 111, 54, 15);
		contentPanel.add(llll);
		
		
		identity.setBounds(134, 111, 54, 15);
		contentPanel.add(identity);
		{
			JPanel buttonPane = new JPanel();
			buttonPane.setLayout(new FlowLayout(FlowLayout.RIGHT));
			getContentPane().add(buttonPane, BorderLayout.SOUTH);
		}
	}	
}

发布了14 篇原创文章 · 获赞 6 · 访问量 5189
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览