目录
一、学生管理系统
1.数据库辅助类
DBHelper.java
package com.liaoxin.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.jdbc.MysqlSavepoint;
/**
* 数据库辅助类
* @author Administrator
*
*/
public class DBHelper {
private static final String URL="jdbc:mysql://localhost:3306/db_0618?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
private static final String CNAME="com.mysql.jdbc.MysqlSavepoint";
// MysqlSavepoint
/**
* 加载驱动
*/
static {
try {
Class.forName(CNAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获得连接
* @return 连接
*/
public static Connection getCon() {
Connection con=null;
try {
con=DriverManager.getConnection(URL, "root", "123456");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
/**
* 关闭资源
* @param con 连接
* @param st 执行对象
* @param rs 结果集
*/
public static void myClose(Connection con,Statement st,ResultSet rs) {
try {
if(rs!=null) {
rs.close();
}
if(st!=null) {
st.close();
}
if(con!=null&&!con.isClosed()) {
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
2.实体类
学生实体类:
package com.liaoxin.entity;
import java.io.Serializable;
import java.util.List;
/**
* 实体类:学生类
* @author Administrator
*
*/
public class Student implements Serializable{
private static final long serialVersionUID = 1L;
private int sid;
private String sname;
private Class c;
private Teacher t;
private List<Hobby> h;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Class getC() {
return c;
}
public void setC(Class c) {
this.c = c;
}
public Teacher getT() {
return t;
}
public void setT(Teacher t) {
this.t = t;
}
public List<Hobby> getH() {
return h;
}
public void setH(List<Hobby> h) {
this.h = h;
}
public Student() {
// TODO Auto-generated constructor stub
}
public Student(int sid, String sname, Class c, Teacher t, List<Hobby> h) {
this.sid = sid;
this.sname = sname;
this.c = c;
this.t = t;
this.h = h;
}
public Student(String sname, Class c, Teacher t, List<Hobby> h) {
this.sname = sname;
this.c = c;
this.t = t;
this.h = h;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", cl=" + c + ", th=" + t + ", hy=" + h + "]";
}
}
教员实体类:
package com.liaoxin.entity;
import java.io.Serializable;
/**
* 实体类:教员类
* @author Administrator
*
*/
public class Teacher implements Serializable{
private static final long serialVersionUID = 1L;
private int tid;
private String tname;
private Class cid;
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public Class getCid() {
return cid;
}
public void setCid(Class cid) {
this.cid = cid;
}
public Teacher() {
// TODO Auto-generated constructor stub
}
public Teacher(int tid, String tname, Class cid) {
this.tid = tid;
this.tname = tname;
this.cid = cid;
}
public Teacher( String tname, Class cid) {
this.tname = tname;
this.cid = cid;
}
@Override
public String toString() {
return "Theaher [tid=" + tid + ", tname=" + tname + ", cid=" + cid + "]";
}
}
班级实体类:
package com.liaoxin.entity;
import java.io.Serializable;
/**
* 实体类:班级类
* @author Administrator
*
*/
public class Class implements Serializable{
private static final long serialVersionUID = 1L;
private int cid;
private String cname;
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Class() {
// TODO Auto-generated constructor stub
}
public Class(int cid, String cname) {
this.cid = cid;
this.cname = cname;
}
public Class(String cname) {
this.cname = cname;
}
@Override
public String toString() {
return "Class [cid=" + cid + ", cname=" + cname + "]";
}
}
爱好实体类:
package com.liaoxin.entity;
import java.io.Serializable;
/**
* 实体类:爱好类
* @author Administrator
*
*/
public class Hobby implements Serializable{
private static final long serialVersionUID = 1L;
private int hid;
private String hname;
public int getHid() {
return hid;
}
public void setHid(int hid) {
this.hid = hid;
}
public String getHname() {
return hname;
}
public void setHname(String hname) {
this.hname = hname;
}
public Hobby() {
// TODO Auto-generated constructor stub
}
public Hobby(int hid, String hname) {
this.hid = hid;
this.hname = hname;
}
public Hobby(String hname) {
this.hname = hname;
}
@Override
public String toString() {
return "Hobby [hid=" + hid + ", hname=" + hname + "]";
}
}
3.数据库访问层
Student
数据库访问层接口:
package com.liaoxin.dao;
import java.util.List;
import com.liaoxin.entity.Class;
import com.liaoxin.entity.Hobby;
import com.liaoxin.entity.Student;
import com.liaoxin.entity.Teacher;
public interface IStudentDao {
/**
* 学生集合
* @return
*/
public List<Student> getAll(String cid,String tid,String hid,int pageIndex,int pageSize);
/**
* 查询单个学生
* @param sid 学生编号
* @return 返回单个学生
*/
public Student getdg(int sid);
/**
* 增加
* @param stu
* @return
*/
public int addStu(String sname, Class cl, Teacher th, List<Hobby> hy);
/**
* 删除
* @param sid 删除id
* @return 返回行数
*/
public int delStu(int sid);
/**
* 修改
* @param sid 学生编号
* @param sname 学生姓名
* @param cl 班级
* @param th 教员
* @param hy 爱好
* @return 返回执行行数
*/
public int updStu(String sname, Class cl, Teacher th, List<Hobby> hy);
/**
* 总数
* @return
*/
public int count(String cid,String tid,String hid);
}
数据库访问层:
package com.liaoxin.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;
import com.liaoxin.biz.HobbyBiz;
import com.liaoxin.biz.IHobbyBiz;
import com.liaoxin.entity.Class;
import com.liaoxin.entity.Hobby;
import com.liaoxin.entity.Student;
import com.liaoxin.entity.Teacher;
import com.liaoxin.util.DBHelper;
public class StudentDao implements IStudentDao{
//扩大作用域
Connection con = null;
Statement st = null;
ResultSet rs = null;
//爱好
IHobbyBiz ihb = new HobbyBiz();
@Override
public List<Student> getAll(String cid,String tid,String hid,int pageIndex,int pageSize) {
int a = (pageIndex-1)*pageSize;
List<Student> ls = new ArrayList<Student>();
try {
//连接数据库
con = DBHelper.getCon();
//定义sql语句
String sql = "select * from (\r\n" +
"select * from (\r\n" +
"select * from (\r\n" +
"select * from (\r\n" +
"select a.*,b.hname,c.cname,d.tname from tb_studentdent a,tb_hobby b,tb_class c,tb_teacher d where a.cid=c.cid and a.tid=d.tid and a.hid = b.hid\r\n" +
")q "+cid+"\r\n" +
")w "+tid+"\r\n" +
")e "+hid+"\r\n" +
")r limit "+a+","+pageSize+"";
// System.out.println(sql);
//执行sql语句
st = con.prepareStatement(sql);
//获得结果集
rs = st.executeQuery(sql);
//遍历结果及
while(rs.next()) {
List<Hobby> hy = new ArrayList<>();
String str = rs.getString("hid");
String[] split = str.split(" ");
for (String hids : split) {
//查询单个
Hobby hby = ihb.getdg(Integer.parseInt(hids));
hy.add(hby);
}
//班级
Class cl = new Class(rs.getInt("cid"),rs.getString("cname"));
//教员
Teacher th = new Teacher(rs.getInt("tid"),rs.getString("tname"),cl);
//学生
Student stu = new Student(rs.getInt("sid"),rs.getString("sname"),cl,th,hy);
//加到集合中
ls.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, st, rs);
}
return ls;
}
@Override
public Student getdg(int sid) {
Student stu = new Student();
try {
//连接数据库
con = DBHelper.getCon();
//定义sql语句
String sql = "select * from (\r\n" +
"select a.*,b.hname,c.cname,d.tname from tb_student a,tb_hobby b,tb_class c,tb_teacher d where a.cid=c.cname and a.tid=d.tid and a.hid = b.hid ) e where e.sid="&