package db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import system.model.SMS;
import model.Course;
import model.Exam;
import model.Grade;
import model.IUser;
import model.SchoolClass;
import model.Student;
import model.StudentScore;
import model.Teacher;
public class DbOperate {
/**
* 根据用户名得到用户对象，如返回null则表示此用户不存在
*/
public IUser getUser(String userid){
Connection con=null;
Statement sm=null;
ResultSet rs=null;
try{
con=ConnectionManager.getConnection();
sm=con.createStatement();
rs=sm.executeQuery("select * from iuser where userId='"+userid+"'");
if(rs.next()){
IUser user=createUserFromRs(rs);
//将数据设置到实体类中
user.setId(new Long(rs.getLong("id")));
user.setUserId(userid);
user.setPassword(rs.getString("password"));
user.setName(rs.getString("name"));
user.setLatestOnline(rs.getDate("latestOnline"));
return user;
}
}catch(SQLException e){
e.printStackTrace();
}finally{
close(rs);
close(sm);
close(con);
}
return null;
}
/**
* 根据数据库记录rs中的Type字段值来创建实际的用户类型,并赋给其特有的值
*/
private IUser createUserFromRs(ResultSet rs) throws SQLException{
String type=rs.getString("type");//根据type值判断此记录属于什么类
if(type.equalsIgnoreCase(SMS.IUSER_TEACHER)){
Teacher o=new Teacher();
Long iuser_id=new Long(rs.getLong("id"));
Set set=getCourse(iuser_id);
o.setCourses(set);
return o;
}
if(type.equalsIgnoreCase(SMS.IUSER_STUDENT)){
Student o=new Student();
Long schoolclass_id=new Long(rs.getLong("schoolclass_id"));
o.setSchoolclass(getSchoolClass(schoolclass_id));
return o;
}
return null;
}
/**
* 根据用户的id字段，得到其相应的课程记录，不会返回null值
*/
public Set getCourse(Long iuser_id){
Connection con=null;
Statement sm=null;
ResultSet rs=null;
try{
con=ConnectionManager.getConnection();
sm=con.createStatement();
//由用户课程连接表得到此用户对应的所有课程ID值
String subSql="select course_id from iuser_course WHERE iuser_id="+iuser_id;
//得到这些ID值对应的课程记录
String sql="Select * from course where id in("+subSql+")";
rs=sm.executeQuery(sql);
Set set=new HashSet();
while(rs.next()){
Course course=new Course();
course.setId(new Long(rs.getInt("id")));
course.setName(rs.getString("name"));
set.add(course);
}
return set;
}catch(SQLException e){
e.printStackTrace();
}finally{
close(rs);
close(sm);
close(con);
}return Collections.EMPTY_SET;
}
/**
* 根据班级的id得到班级对象，返回null表示不存在对应的班级
*/
public SchoolClass getSchoolClass(Long id){
Connection con=null;
java.sql.PreparedStatement sm=null;
ResultSet rs=null;
try{
con=ConnectionManager.getConnection();
sm=con.prepareStatement("SELECT * from schoolclass where id="+id);
rs=sm.executeQuery();
if(rs.next()){
SchoolClass schoolClass=new SchoolClass();
schoolClass.setId(new Long(rs.getInt("id")));
schoolClass.setName(rs.getString("name"));
{//设置年级属性
Grade grade=new Grade();
grade.setId(new Long(rs.getInt("grade_id")));
schoolClass.setGrade(grade);
}
return schoolClass;
}
}catch(SQLException e){
e.printStackTrace();
}finally{
close(rs);
close(sm);
close(con);
}
return null;
}
/**
* 关闭ResultSet对象
*/
void close(ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch(SQLException e){}
rs=null;
}
}
/**
* 关闭Statement对象
*/
void close(Statement sm){
if(sm!=null){
try{
sm.close();
}catch(SQLException e){}
sm=null;
}
}
/**
* 关闭Connection对象
*/
void close(Connection con){}
/**
* 删除用户
*/
public boolean removeUser(IUser user){
Connection con=null;
Statement sm=null;
ResultSet rs=null;
boolean result=true;
try{
/**
* 控制事务的方法：
* 1：setAutoCommit(Boolean autoCommit)设置是否自动提交事务
* 2:commit()提交事务
* 3：rollback()撤销事务
*/
con=ConnectionManager.getConnection();
con.setAutoCommit(false);//禁止自动提效事务
sm=con.createStatement();
sm.addBatch("delete from iuser where id="+user.getId());
if(user instanceof Teacher)
sm.addBatch("delete from iuser_course where iuser_id="+user.getUserId());
sm.executeBatch();
con.commit();
}catch(SQLException e){
result=false;
e.printStackTrace();
try{
con.rollback();//如出现异常则同意
}catch(Exception e2){
e2.printStackTrace();
}
}finally{
close(rs);
close(sm);
close(con);
}
return result;
}
/**
* 查找方式
*/
public List getUsers(QueryInfo qi){
Connection con=null;
Statement sm=null;
ResultSet rs=null;
try{
con=ConnectionManager.getConnection();
sm=con.createStatement();
//得到总记录数
rs=sm.executeQuery("select count(id) from iuser");
rs.next();
qi.rsCount=rs.getInt(1);
if(qi.rsCount==0)//等于0表示没有记录
return Collections.EMPTY_LIST;
//算出总页数
if(qi.rsCount%qi.pageSize==0)
qi.pageCount=qi.rsCount/qi.pageSize+1;
//算出起始位置=（当前页号-1）*每页记录数
int start=(qi.currentPage-1)*qi.pageSize;
rs=sm.executeQuery("select * from iuser limit "+start+"," +qi.pageSize);
List list=new ArrayList(qi.pageSize);
while(rs.next()){
IUser user=createUserFromRs(rs);
user.setId(new Long(rs.getLong("id")));
user.setUserId(rs.getString("userid"));
user.setPassword(rs.getString("password"));
user.setName(rs.getString("name"));
user.setLatestOnline(rs.getDate("latestOnline"));
list.add(user);
}
return list;
}catch(SQLException e){
e.printStackTrace();
}finally{
close(rs);
close(sm);
close(con);
}
return Collections.EMPTY_LIST;
}
/**
* 插入用户方法
*/
public boolean insertUser(IUser user){
Connection con=null;
PreparedStatement sm=null;
ResultSet rs=null;
try{
con=ConnectionManager.getConnection();
if(getUser(user.getUserId())!=null)//检查数库的userid是否重名
return false;
String sql="insert into iuser (type,userid,password,name,schoolclass_id) values (?,?,?,?,?);";
sm=con.prepareStatement(sql);
sm.setString(2, user.getUserId());
sm.setString(3, user.getPassword());
sm.setString(4, user.getName());
if(user instanceof Student){
sm.setString(1, SMS.IUSER_STUDENT);
SchoolClass schoolClass=((Student)user).getSchoolclass();
if(schoolClass==null)
sm.setNull(5,java.sql.Types.BIGINT);
else
sm.setInt(5, schoolClass.getid().intValue());
sm.execute();
}else if(user instanceof Teacher){
sm.setString(1, SMS.IUSER_TEACHER);
sm.setNull(5, java.sql.Types.BIGINT);
sm.execute();//将用户表的记录插入
rs=sm.executeQuery("select id from iuser where userid='"+user.getUserId()+"'");
rs.next();
int iuser_id=rs.getInt(1);
//如果是老师还要处理他的课程情况
Set set=((Teacher)user).getCourses();
if(set!=null){
for(Iterator it=set.iterator();it.hasNext();){
Course element=(Course)it.next();
String course_id=element.getId()+"";
sql="insert into iuser_course values ("+iuser_id+","+course_id+")";
sm.addBatch(sql);
}
}
sm.executeBatch();
}
return true;
}catch(SQLException e){
e.printStackTrace();
try{
con.rollback();//如出现异常则回滚
}catch(Exception e2){
e2.printStackTrace();
}
}fina
