学生信息管理系统(学生表、班级表)
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);
}
}
}