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;}@Overridepublic 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 + "]"; }}dao层：
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 Login() {// 每一条记录封装到JavaBean中 ， 再将JavaBean放入List集合 try {String sql = "select * from Login";List list = qr.query(sql, new BeanListHandler(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 selectAllStudent() {// 每一条记录封装到JavaBean中 ， 再将JavaBean放入List集合 try {String sql = "select * from Student";List list = qr.query(sql, new BeanListHandler(Student.class));return list; } catch (SQLException ex) {System.out.println(ex);throw new RuntimeException("学生信息查询失败"); } } // 查询学分在2到5之间的课程号、课程名、课程类型 ， 并将查询结果按课程类型升序排列 ， 同一类型的课程按学分降序排列 public List 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 list = qr.query(sql, new BeanListHandler(Course.class));return list; } catch (SQLException ex) {System.out.println(ex);throw new RuntimeException("课程信息查询失败"); } } // 查询计算机学院各个班级的编号、名称 ， 如果平均值低于700则不显示,多表连接 public List 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 list = qr.query(sql, new BeanListHandler(Score.class));return list; } catch (SQLException ex) {System.out.println(ex);throw new RuntimeException("信息查询失败"); } }}