学生管理系统——数据库、java基础

package pers.zhb.student.domain;public class Course {
private String courseno;
private String cname;
private String type;
private float credit; public Course() { } @Override
public String toString() {
return "Course [courseno=" + courseno + ", cname=" + cname + ", type="
+ type + ", credit=" + credit + "]";
} public String getCourseno() {
return courseno;
} public void setCourseno(String courseno) {
this.courseno = courseno;
} public String getCname() {
return cname;
} public void setCname(String cname) {
this.cname = cname;
} public String getType() {
return type;
} public void setType(String type) {
this.type = type;
} public float getCredit() {
return credit;
} public void setCredit(float credit) {
this.credit = credit;
}}

package pers.zhb.student.domain;public class Log {
private String account;
private String password;
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Log [account=" + account + ", password=" + password + "]";
}}

package pers.zhb.student.domain;public class Score {
private String classno;
private String classname; public String getClassno() {
return classno;
} public void setClassno(String classno) {
this.classno = classno;
} public String getClassname() {
return classname;
} public void setClassname(String classname) {
this.classname = classname;
} @Override
public String toString() {
return "Score [classno=" + classno + ", classname=" + classname + "]";
}}

package pers.zhb.student.domain;public class Student {
private String studentno;
private String sname;
private String sex;
private String birthday;
private String classno;
private float point; public String getStudentno() {
return studentno;
} public void setStudentno(String studentno) {
this.studentno = studentno;
} public String getSname() {
return sname;
} public void setSname(String sname) {
this.sname = sname;
} public String getSex() {
return sex;
} public void setSex(String sex) {
this.sex = sex;
} public String getBirthday() {
return birthday;
} public void setBirthday(String birthday) {
this.birthday = birthday;
} public String getClassno() {
return classno;
} public void setClassno(String classno) {
this.classno = classno;
} public float getPoint() {
return point;
} public void setPoint(float point) {
this.point = point;
} public String getPhone() {
return phone;
} public void setPhone(String phone) {
this.phone = phone;
} public String getEmail() {
return email;
} public void setEmail(String email) {
this.email = email;
} private String phone;
private String email; @Override
public String toString() {
return "Student [studentno=" + studentno + ", sname=" + sname
+ ", sex=" + sex + ", birthday=" + birthday + ", classno="
+ classno + ", point=" + point + ", phone=" + phone
+ ", email=" + email + "]";
}
}

package pers.zhb.student.dao;import java.sql.SQLException;
import java.util.List;import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;import pers.zhb.student.domain.Log;
import pers.zhb.student.domain.Score;
import pers.zhb.student.domain.Student;
import pers.zhb.student.domain.Course;
import pers.zhb.student.tools.JDBCUtils;public class StudentDao {
BasicDataSource datasource = JDBCUtils.getDataSource();
private QueryRunner qr = new QueryRunner(datasource); // 注册
public void register(Log reg) {
try {
String sql = "INSERT INTO Login(account,password) VALUES(?,?)";
Object[] params = { reg.getAccount(), reg.getPassword() };
qr.update(sql, params); } catch (SQLException e) {
throw new RuntimeException(e);
}
} // 登录
public List<Log> Login() {// 每一条记录封装到JavaBean中，再将JavaBean放入List集合
try {
String sql = "select * from Login";
List<Log> list = qr.query(sql, new BeanListHandler<Log>(Log.class)); return list;
} catch (SQLException ex) {
System.out.println(ex);
throw new RuntimeException("学生信息查询失败");
} } // 添加学生信息
public void addStudent(Student stu) {
try {
String sql = "INSERT INTO student(studentno,sname,sex,birthday,classno,point,phone,email) VALUES(?,?,?,?,?,?,?,?)";
Object[] params = { stu.getStudentno(), stu.getSname(),
stu.getSex(), stu.getBirthday(), stu.getClassno(),
stu.getPoint(), stu.getPhone(), stu.getEmail() };
qr.update(sql, params); } catch (SQLException e) {
throw new RuntimeException(e);
} } // 删除学生信息
public void deleteStudent(String snum) {
try { String sql = "DELETE from Student where studentno =?";
Object[] delete = { snum };
qr.update(sql, delete); } catch (SQLException e) {
throw new RuntimeException(e);
} } // 修改学生信息
public void updataStudent(Student stu) {
try {
String sql = "Update Student set sname=?,sex=?,birthday=?,classno=?,point=?,phone=?,email=? Where studentno=?";
Object[] update = { stu.getSname(), stu.getSex(),
stu.getBirthday(), stu.getClassno(), stu.getPoint(),
stu.getPhone(), stu.getEmail(), stu.getStudentno(), };
qr.update(sql, update);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} // 查询全部学生信息,单表查询
public List<Student> selectAllStudent() {// 每一条记录封装到JavaBean中，再将JavaBean放入List集合
try {
String sql = "select * from Student";
List<Student> list = qr.query(sql, new BeanListHandler<Student>(
Student.class)); return list;
} catch (SQLException ex) {
System.out.println(ex);
throw new RuntimeException("学生信息查询失败");
} } // 查询学分在2到5之间的课程号、课程名、课程类型，并将查询结果按课程类型升序排列，同一类型的课程按学分降序排列
public List<Course> selectCourse() {// 每一条记录封装到JavaBean中，再将JavaBean放入List集合
try {
String sql = "select courseno,cname,type,credit from course where credit between 2 and 5 order by type asc ,credit desc";
List<Course> list = qr.query(sql, new BeanListHandler<Course>(
Course.class));
return list;
} catch (SQLException ex) {
System.out.println(ex);
throw new RuntimeException("课程信息查询失败");
} } // 查询计算机学院各个班级的编号、名称，如果平均值低于700则不显示,多表连接
public List<Score> selectScore() {// 每一条记录封装到JavaBean中，再将JavaBean放入List集合
try {
String sql = "select class.classno,class.classname from class,student where student.classno=class.classno and class.department='计算机学院' group by student.classno,class.classname,class.classno having AVG(point)>=700";
List<Score> list = qr.query(sql, new BeanListHandler<Score>(
Score.class));
return list;
} catch (SQLException ex) {
System.out.println(ex);
throw new RuntimeException("信息查询失败");
} }}

package pers.zhb.student.services;import java.util.List;import pers.zhb.student.dao.StudentDao;
import pers.zhb.student.domain.Course;
import pers.zhb.student.domain.Log;
import pers.zhb.student.domain.Student;
import pers.zhb.student.domain.Score;public class StudentService {
private StudentDao dao = new StudentDao(); public List<Log> Login() {
return dao.Login();
} public void register(Log reg) {
dao.register(reg);
} public void addStudent(Student stu) {
dao.addStudent(stu);
} public void deleteStudent(String snum) {
dao.deleteStudent(snum);
} public void updataStudent(Student stu) {
dao.updataStudent(stu);
} public List<Student> selectAllStudent() {
return dao.selectAllStudent();
} public List<Course> selectCourse() {
return dao.selectCourse();
} public List<Score> selectScore() {
return dao.selectScore();
} public boolean checkPassword(String str) {
boolean b = str.matches("[a-zA-Z_0-9]{6}");
return b;
}}

package pers.zhb.student.controller;import java.util.List;import pers.zhb.student.domain.Course;
import pers.zhb.student.domain.Log;
import pers.zhb.student.domain.Student;
import pers.zhb.student.domain.Score;
import pers.zhb.student.services.StudentService;public class StudentController {
private StudentService service = new StudentService();
public void register(Log reg){
service.register(reg);
}
public List<Log> Login() {
return service.Login();
}
public void addStudent(Student stu) {
service.addStudent(stu);
} public void deleteStudent(String snum) {
service.deleteStudent(snum);
} public void updataStudent(Student stu, String studentno) {
service.updataStudent(stu);
} public List<Student> selectAllStudent() {
return service.selectAllStudent();
}
public List<Course> selectCourse() {
return service.selectCourse();
}
public List<Score> selectScore() {
return service.selectScore();
}}

package pers.zhb.student.view;import java.util.Iterator;
import java.util.List;
import java.util.Scanner;import pers.zhb.student.controller.StudentController;
import pers.zhb.student.domain.Course;
import pers.zhb.student.domain.Log;
import pers.zhb.student.domain.Score;
import pers.zhb.student.domain.Student;
import pers.zhb.student.services.StudentService;public class MainView {
private StudentController controller = new StudentController();
private StudentService service = new StudentService(); public void run() {
Scanner sc = new Scanner(System.in); while (true) {
System.out.println();
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("请您输入要操作的功能序号:"); int choose = sc.nextInt(); switch (choose) {
case :
addStudent();
break;
case :
deleteStudent();
break;
case :
updataStudent();
break;
case :
select();
break;
case :
System.out.println("您已退出学生信息管理系统。");
System.exit();
break;
default:
System.out.println("输入有误");
break;
}
}
} public void select() {// 学生查询、课程查询、
Scanner sc = new Scanner(System.in);
while (true) {
System.out.println();
System.out.println("============欢迎进入学生信息查询页面============");
System.out.println("1:查询所有学生信息");
System.out.println("2:查询学分在2到5之间的课程号、课程名、课程类型");
System.out.println("3:查询计算机学院各个班级的编号、名称，如果平均值低于700则不显示");
System.out.println("4:进入主页面");
System.out.println("请您输入要操作的功能序号:");
int choose = sc.nextInt();
switch (choose) {
case :
selectAll();
break;
case :
selectCourse();
break;
case :
selectScore();
break;
case :
run();
}
}
} public void register() {// 注册
System.out.println("a:新用户");
System.out.println("b:老用户");
Scanner sc = new Scanner(System.in);
String registerChoose = sc.next();
if (registerChoose.equals("a")) {
Log l = new Log();
System.out.println("请输入账号：");
l.setAccount(sc.next());
System.out.println("请输入六位密码(字母、数字、下划线)：");
String password=sc.next(); if(service.checkPassword(password)){
l.setPassword(password);
}
else{
System.out.println("密码的格式不正确");
System.exit();
}
service.register(l);
System.out.println("您已注册成功");
}
if (registerChoose.equals("b")) {
Login();
}
} public void Login() {
Scanner sc = new Scanner(System.in);
System.out.println("请输入账号");
String account = sc.next();
System.out.println("请输入密码");
String password = sc.next();
Iterator<Log> listIt = service.Login().iterator();// 获取集合的实现类对象，并调用集合的iterator()
while (listIt.hasNext()) {
Log l = listIt.next();
if (account.equals(l.getAccount())
&& password.equals(l.getPassword())) {
System.out.println(l.getAccount() + "，" + "您已登录成功");
run();
}
}
Iterator<Log> listIt1 = service.Login().iterator();// 获取集合的实现类对象，并调用集合的iterator()
while (listIt1.hasNext()) {
Log l = listIt1.next();
if (account.equals(l.getAccount()) == true
|| password.equals(l.getPassword()) == false) {
System.out.println("登录失败");
System.exit();
}
} } public void addStudent() {// 添加学生信息
Scanner sc = new Scanner(System.in);
Student stu = new Student();
System.out.println("请输入学号：");
stu.setStudentno(sc.next());
System.out.println("请输入姓名：");
stu.setSname(sc.next());
System.out.println("请输入性别");
stu.setSex(sc.next());
System.out.println("请输入出生日期");
stu.setBirthday(sc.next());
System.out.println("请输入班级编号");
stu.setClassno(sc.next());
System.out.println("请输入入学成绩");
stu.setPoint(sc.nextFloat());
System.out.println("请输入联系电话");
stu.setPhone(sc.next());
System.out.println("请输入电子邮箱");
stu.setEmail(sc.next());
service.addStudent(stu);
System.out.println(stu.getSname() + "的信息添加成功"); } public void deleteStudent() {// 删除某一个学生的信息
System.out.println("请输入要删除的学生的学号");
Scanner sc = new Scanner(System.in);
String snum = sc.next();
service.deleteStudent(snum);
System.out.println("学号为" + snum + "的学生信息删除成功");
} public void updataStudent() {// 修改学生信息
Scanner sc = new Scanner(System.in); Student stu = new Student();
System.out.println("请输入学号：");
stu.setStudentno(sc.next());
System.out.println("请输入新的姓名：");
stu.setSname(sc.next());
System.out.println("请输入新的性别");
stu.setSex(sc.next());
System.out.println("请输入新的出生日期");
stu.setBirthday(sc.next());
System.out.println("请输入新的班级编号");
stu.setClassno(sc.next());
System.out.println("请输入新的入学成绩");
stu.setPoint(sc.nextFloat());
System.out.println("请输入新的联系电话");
stu.setPhone(sc.next());
System.out.println("请输入新的电子邮箱");
stu.setEmail(sc.next());
service.updataStudent(stu); } public void selectAll() {// 查询所有
List<Student> list = controller.selectAllStudent();
if (list.size()!= )
print(list);
else
System.out.println("没有查询到数据");
} private void print(List<Student> list) {// 查询所有
System.out.println("全部学生信息：");
for (Student students : list) {
System.out.println(students);
}
} public void selectCourse() {// 查询课程
List<Course> list = controller.selectCourse();
if (list.size() != )
printCourse(list);
else
System.out.println("没有查询到数据");
} private void printCourse(List<Course> list) {// 查询课程
System.out
.println("学分在2到5之间的课程号、课程名、课程类型，并将查询结果按课程类型升序排列，同一类型的课程按学分降序排列：");
for (Course courses : list) {
System.out.println(courses);
}
} public void selectScore() {// 查询分数
List<Score> list = controller.selectScore();
if (list.size() != )
printScore(list);
else
System.out.println("没有查询到数据");
} private void printScore(List<Score> list) {// 查询分数
System.out.println("查询计算机学院各个班级的编号、名称，如果平均值低于700则不显示:");
for (Score scores : list) {
System.out.println(scores);
}
}}

package pers.zhb.student.tools;import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;import org.apache.commons.dbcp.BasicDataSource;public class JDBCUtils {
private static BasicDataSource datasource = new BasicDataSource();
/*
* BasicDataSource类，实现了datasource接口
*/ static {// 静态代码块,对象BasicDataSource对象中的配置,自定义 InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("DB.properties");
Properties pro = new Properties();
try {
pro.load(in);
} catch (IOException e) {
e.printStackTrace();
}
// 数据库基本的连接信息，必须要设置（必须项）
datasource.setDriverClassName(pro.getProperty("driver"));
datasource.setUrl(pro.getProperty("url"));
datasource.setUsername(pro.getProperty("username"));
datasource.setPassword(pro.getProperty("password"));
// 对象连接池中的连接数量配置,可以不设置（基本项）
datasource.setInitialSize();// 初始化的连接数
datasource.setMaxActive();// 最大连接数量
datasource.setMaxIdle();// 最大空闲数
datasource.setMinIdle();// 最小空闲
} // 返回BasicDataSource类的对象
public static BasicDataSource getDataSource() {
return datasource;
}
}

package pers.zhb.StudentMangemrent;import pers.zhb.student.view.MainView;public class MainApp {
public static void main(String[] args) {
MainView mainWay=new MainView();
mainWay.register();
mainWay.run();// MainView类的匿名对象调用方法
}
}