学生管理系统

学生信息管理系统（学生表、班级表）

1.学生信息入库

2.查询学生信息

   ①编号查询学生；②名字查询学生；③查询所有学生

3.查看班级

    ①查看所有班级信息（班级编号 班级名称 班级人数）；②查看单个班级下的所有学生信息

4.修改学生信息（编号不允许修改）

5.删除学生信息

6.退出系统

BaseDao类

public class BaseDao{
	static String driver="com.mysql.jdbc.Driver.class";
	static String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
	static String username="root";
	static String password="";
	Connection conn=null;
	//建立连接
	public PreparedStatement getConnect(String sql){
		PreparedStatement ps=null;
		try {
			//1.加载驱动（driver）
			Class.forName(driver);
			//2.建立数据库连接
			conn=DriverManager.getConnection(url,username,password);
			//3.得到发送sql语句的对象并且发送sql语句至数据库
			ps=conn.prepareStatement(sql);//一次一条
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ps;
	}
	//关闭连接
	public void closed(ResultSet rs,PreparedStatement ps){
		try{
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
		}
			if(conn!=null){
				conn.close();
			}
	}catch(SQLException e){
		// TODO Auto-generated catch block
					e.printStackTrace();
		}
	}
}

Student类：

public class Student {
	private int sno;
	private String sname;
	private String gender;
	private String address;
	private int age;
	private int cno;
	
	
	
	public int getSno() {
		return sno;
	}

	public void setSno(int sno) {
		this.sno = sno;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		if(age>0&&age<=30){
			this.age = age;
		}else{
			System.out.println("年龄不符，暂时设置为18");
			this.age=18;
		}
		
	}

	public int getCno() {
		return cno;
	}

	public void setCno(int cno) {
		this.cno = cno;
	}

	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}

	public Student(int sno, String sname, String gender, String address,
			int age, int cno) {
		super();
		this.sno = sno;
		this.sname = sname;
		this.gender = gender;
		this.address = address;
		this.age = age;
		if(age>0&&age<=30){
			this.age = age;
		}else{
			System.out.println("年龄不符，暂时设置为18");
			this.age=18;
		}
		this.cno = cno;
	}
	
	
}

Work类：

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;



public class Work {
	static boolean flag=true;
	static Scanner sc=new Scanner(System.in);
	static BaseDao bd=new BaseDao();
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		System.out.println("----------------学生信息管理系统---------------------");
		do{
			init();
		}while(flag);
	
	}
	//系统界面初始化方法
		public static void init(){
			System.out.println("以下为功能板块");
			System.out.println("1.学生信息入库");
			System.out.println("2.查询学生信息");
			System.out.println("3.查看班级信息");
			System.out.println("4.修改学生信息");
			System.out.println("5.删除学生信息");
			System.out.println("6.退出系统");
			System.out.print("请输入您要使用的功能编号:");
			int id=sc.nextInt();
			if(id==1){
				Student s=new Student();
				System.out.print("请输入新增学生的姓名");
				s.setSname(sc.next());
				System.out.print("请输入新增学生的性别");
				s.setGender(sc.next());
				System.out.print("请输入新增学生的年龄");
				s.setAge(sc.nextInt());
				System.out.print("请输入新增学生的地址");
				s.setAddress(sc.next());
				//查询所有班级信息
				getAllClass();
				System.out.print("请选择班级并输入班级编号");
				s.setCno(sc.nextInt());
				//添加到数据库中
				addStudent(s);
			}else if(id==2){
				selectStuMap();
			}else if(id==3){
				selectClassMap();
			}else if(id==4){
				System.out.println("请输入学生编号：");
				Student s=getStuById(sc.nextInt());
				System.out.print("请输入新姓名");
				s.setSname(sc.next());
				System.out.print("请输入新性别");
				s.setGender(sc.next());
				System.out.print("请输入新年龄");
				s.setAge(sc.nextInt());
				System.out.print("请输入新地址");
				s.setAddress(sc.next());
				getAllClass();
				System.out.print("请选择班级并输入班级编号");
				s.setCno(sc.nextInt());
				updateStu(s);
				
			}else if(id==5){
				System.out.println("请输入学生编号：");
				Student s=getStuById(sc.nextInt());
				dropStu(s.getSno());
			}else if(id==6){
				flag=false;
				System.out.println("系统已退出！！！");
			}
		}
		
		//查询初始化
		public static void selectStuMap(){
			System.out.println("查询功能如下：");
			System.out.println("1.编号查询学生");
			System.out.println("2.名称查询学生");
			System.out.println("3.查询所有学生");
			System.out.println("4.返回上级菜单");
			System.out.println("请输入功能编号");
			int no=sc.nextInt();
			if(no==1){
				System.out.println("请输入学生编号:");
				int sno=sc.nextInt();
				getStuById(sno);
			}else if(no==2){
				System.out.println("请输入学生姓名:");
				getStuByName(sc.next());
			}else if(no==3){
				getAllStu();
			}else if(no==4){
				init();
			}
		}
		
		public static void selectClassMap(){
			System.out.println("查询班级功能如下：");
			System.out.println("1.查询所有班级信息");
			System.out.println("2.查询单个班级下的所有学生");
			System.out.println("3.返回上级菜单");
			int no=sc.nextInt();
			if(no==1){
				getAllClass();
			}else if(no==2){
				System.out.println("请输入班级名称");
				getStuByClass(sc.next());
			}else if(no==3){
				init();
			}
		}
		//查询所有班级信息
		public static void getAllClass(){
			PreparedStatement ps=bd.getConnect("select c.cno,c.class_name,count(*) 班级人数 from class c,student s where c.cno=s.cno GROUP BY cno,c.class_name");
			ResultSet rs=null;
			try {
				rs=ps.executeQuery();
				System.out.println("班级编号\t班级名称\t班级人数");
				while(rs.next()){
					System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getInt(3));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				bd.closed(rs, ps);
			}
		}
		//增加学生信息
		public static void addStudent(Student s){
			PreparedStatement ps=bd.getConnect("insert into student(sname,gender,address,cno) values(?,?,?,?,?)");
			try {
				ps.setString(1, s.getSname());
				ps.setString(2, s.getGender());
				ps.setString(3, s.getAddress());
				ps.setInt(4, s.getAge());
				ps.setInt(5, s.getCno());
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				bd.closed(null, ps);
			}
		}
		//通过编号查询学生
		public static Student getStuById(int sno){
			PreparedStatement ps=bd.getConnect("select s.sno,s.name,s.gender,s.address,s.age,c.class_name,c.cno from student s,class c where s.cno=c.cno and s.sno=?");
			ResultSet rs=null;
			Student s=null;
			try {
				ps.setInt(1, sno);
				rs=ps.executeQuery();
				System.out.println("编号\t姓名\t性别\t住址\t年龄\t班级");
				if(rs.next()){
					System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getInt(5)+"\t"+rs.getInt(6));
					s=new Student(sno,rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getInt(7));
				}else{
					System.out.println("此编号对应的学生不存在！！！");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				bd.closed(rs, ps);
			}
			return s;
		}	
			//通过姓名查询学生
			public static void getStuByName(String sname){
				PreparedStatement ps=bd.getConnect("select s.sno,s.name,s.gender,s.address,s.age,c.class_name from student s,class c where s.cno=c.cno and s.sno=?");
				ResultSet rs=null;
				try {
					ps.setString(1, sname);
					rs=ps.executeQuery();
					System.out.println("编号\t姓名\t性别\t住址\t年龄\t班级");
					boolean f=false;
					while(rs.next()){
						f=true;
						System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getInt(5)+"\t"+rs.getInt(6));
					}
					if(!f){
						System.out.println("此名称对应的学生不存在！！！");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					bd.closed(rs, ps);
				}
		}
			
			//查询所有学生
			public static void getAllStu(){
				PreparedStatement ps=bd.getConnect("select s.sno,s.name,s.gender,s.address,s.age,c.class_name from student s,class c where s.cno=c.cno ");
				ResultSet rs=null;
				try {
					rs=ps.executeQuery();
					System.out.println("编号\t姓名\t性别\t住址\t年龄\t班级");
					while(rs.next()){
						System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getInt(5)+"\t"+rs.getInt(6));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					bd.closed(rs, ps);
				}
			}
			
			//查询单个班级
			public static void getStuByClass(String className){
				PreparedStatement ps=bd.getConnect("select s.sno,s.sname,s.gender,s.address,s.age from class c,student s where s.cno=c.cno and c.class_name=?");
				ResultSet rs=null;
				try {
					ps.setString(1, className);
					rs=ps.executeQuery();
					while(rs.next()){
						System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getInt(5));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					bd.closed(rs, ps);
				}
			}
			public static void updateStu(Student s){
				PreparedStatement ps=bd.getConnect("update student set sname=?,gender=?,address=?,age=?,cno=?,sno=?");
				try {
					ps.setString(1, s.getSname());
					ps.setString(2, s.getGender());
					ps.setString(3, s.getAddress());
					ps.setInt(4, s.getAge());
					ps.setInt(5, s.getCno());
					ps.setInt(6, s.getSno());
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					bd.closed(null, ps);
				}
				
			}

			public static void dropStu(int sno){
				PreparedStatement ps=bd.getConnect("delete from Student where sno=?");
				try {
					ps.setInt(1, sno);
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					bd.closed(null, ps);
				}
			}
}