querydao.java

中应用程序的访问权限对Java Web Console 中应用程序的访问权限 成功登录 Web 控制台后,可能无法自动访问在该控制台中注册的所有应用程序。通常,必须安装应用程序,才能让所有的用户在控制

/**
 * 
 */
package edu.yinhe.mis.model;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.yinhe.mis.dto.QueryDTO;
import edu.yinhe.mis.vo.QueryVO;
import edu.yinhe.system.model.BaseDAO;
/**
 * @author 王熙亮
 *
 */
public class QueryDAO extends BaseDAO {

	public Object Permissionlist(Object arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public Object delete(Object obj) throws SQLException {
		boolean flag = false;
		PreparedStatement pstat = null;
		ResultSet rst = null;
		String SQL = null;
		QueryDTO dto = null;
		String StrNo = "";
		String newpwd = "";
		try {
			dto = (QueryDTO) obj;
			if(dto.getStuNo()==null){
				StrNo = "";
			}else{
				StrNo = dto.getStuNo();
			}
			if(dto.getNewpwd()==null){
				newpwd = "";
			}else
			{
				newpwd = dto.getNewpwd();
			}
			if(!StrNo.trim().equals("")&&!newpwd.trim().equals("")){
			SQL = "UPDATE STUDENT SET REMARK2='"+dto.getNewpwd()+"'"+" WHERE STU_NO='"+dto.getStuNo()+"'";
			}
			if(SQL!=null){
			pstat = conn.prepareStatement(SQL);
			flag = pstat.execute();
			}
			
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public Object find() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public Object findAll() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public Object find(Object obj) throws SQLException {
		QueryDTO dto = null;
		QueryVO vo = null;
		List list =new ArrayList();
		Object[] objs = new Object[2];
		PreparedStatement pstat = null;
		ResultSet rst = null;
		String SQL = null;
		String classno = "";
		String password = "";
		try {
			dto = (QueryDTO) obj;
			objs[0] = getCount(dto);
			if(dto.getClass_no()==null){
				classno = "";
			}else
			{
				classno = dto.getClass_no();
			}
			if(!classno.trim().equals("")){
			     SQL = "SELECT * FROM PKTABLE WHERE CLASS_NO = '"+dto.getClass_no()+"'";
			}
			int i = (Integer.parseInt(dto.getCurrentPage())-1)*Integer.parseInt(dto.getRowperPage());
			SQL += " LIMIT "+i+","+dto.getRowperPage();
			pstat = conn.prepareStatement(SQL);
			rst = pstat.executeQuery();
			while(rst.next()){
			vo = new QueryVO();
			vo.setHour_date(rst.getString("HOUR_DATE")==null?"待定":rst.getString("HOUR_DATE"));
			vo.setPkweek(rst.getString("PKWEEK")==null?"待定":rst.getString("PKWEEK"));
			vo.setTeacher_name(rst.getString("TEACHER_NAME")==null?"待定":rst.getString("TEACHER_NAME"));
			vo.setClassname(rst.getString("CLASS_NAME")==null?"待定":rst.getString("CLASS_NAME"));
			vo.setClassroom_no(rst.getString("CLASSROOM")==null?"待定":rst.getString("CLASSROOM"));
			vo.setObject_name(rst.getString("OBJECT_NAME")==null?"待定":rst.getString("OBJECT_NAME"));
			vo.setClasstimes(rst.getString("CLASSTIMES")==null?"待定":rst.getString("CLASSTIMES"));
			vo.setTeachtime(rst.getString("TEACHTIME")==null?"待定":rst.getString("TEACHTIME"));
			vo.setSchool_no(rst.getString("SCHOOL_NO")==null?"待定":rst.getString("SCHOOL_NO"));
			list.add(vo);
			}
			objs[1] = list; 
		} finally{
			dto = null;
			if(pstat != null)pstat.close();
			if(rst != null)rst.close();
			SQL = null;
		}
		return objs;
	}
	public Object findAll(Object obj) throws SQLException {
		QueryDTO queryDTO = null;
		
		QueryVO vo = null;
		List list = new ArrayList();
		PreparedStatement pstat = null;
		ResultSet rst = null;
		String SQL = null;
		Object[] objs =new Object[2];
		String classno = "";
		String classroom = "";
		String schoolno = "";
		try {
			queryDTO = (QueryDTO) obj;
			objs[0] = getCount(queryDTO);
			if(queryDTO.getClass_no()==null){
				classno = "";
			}else{
				classno = queryDTO.getClass_no();
			}
			if(queryDTO.getClassroom_no()==null){
				classroom = "";
			}else{
				classroom = queryDTO.getClassroom_no();
			}
			if(queryDTO.getSchool_no()==null){
				schoolno = "";
			}else{
				schoolno = queryDTO.getSchool_no();
			}
			SQL = "SELECT * FROM PKTABLE WHERE 1=1";
			if(!classno.trim().equals("")){
				SQL+=" AND CLASS_NO ='"+queryDTO.getClass_no()+"'";
			}else if(!classroom.trim().equals("")){  
			SQL+=" AND CLASSROOM ='"+queryDTO.getClassroom_no()+"'";
			}else if(!schoolno.trim().equals("")){
			SQL+=" AND SCHOOL_NO ='"+queryDTO.getSchool_no()+"'";
			}	
			int i = (Integer.parseInt(queryDTO.getCurrentPage())-1)*Integer.parseInt(queryDTO.getRowperPage());
			SQL+=" ORDER BY CLASS_NAME LIMIT "+i+","+queryDTO.getRowperPage();
			pstat = conn.prepareStatement(SQL);
			rst = pstat.executeQuery();
			while(rst.next()){
			vo = new QueryVO();
		   vo.setHour_date(rst.getString("HOUR_DATE")==null?"待定":rst.getString("HOUR_DATE"));
		   vo.setPkweek(rst.getString("PKWEEK")==null?"待定":rst.getString("PKWEEK"));	
		   vo.setTeacher_name(rst.getString("TEACHER_NAME")==null?"待定":rst.getString("TEACHER_NAME"));
		   vo.setClassname(rst.getString("CLASS_NAME")==null?"待定":rst.getString("CLASS_NAME"));
		   vo.setClassroom_no(rst.getString("CLASSROOM")==null?"待定":rst.getString("CLASSROOM"));
		   vo.setObject_name(rst.getString("OBJECT_NAME")==null?"待定":rst.getString("OBJECT_NAME"));
		   vo.setClasstimes(rst.getString("CLASSTIMES")==null?"待定":rst.getString("CLASSTIMES"));
		   vo.setTeachtime(rst.getString("TEACHTIME")==null?"待定":rst.getString("TEACHTIME"));
		   vo.setSchool_no(rst.getString("SCHOOL_NO")==null?"待定":rst.getString("SCHOOL_NO"));
		   list.add(vo);
			}
			objs[1] = list;
		}finally{
			queryDTO = null;
			if(pstat != null)pstat.close();
			if(rst != null)rst.close();
			SQL = null;
		}
		return objs;
		
	}

	public Object findById(Object obj) throws SQLException {
		  QueryDTO dto = new QueryDTO(); 
		  List list = new ArrayList();
		  QueryVO vo = null;
		  PreparedStatement pstat = null;
		  ResultSet rst = null;
		  String SQL = null;
		  Object[] objs =new Object[2];
		  String teacherNum = "";
		  String hdate = "";
		  try {
			  dto = (QueryDTO) obj;
			  objs[0] = getCount(dto);
			  if(dto.getTeacher_no()==null){
				  teacherNum = "";
			  }else{
				  teacherNum=dto.getTeacher_no();
			  }
			  if(dto.getHour_date()==null)
			  {
				  hdate = "";
			  }else
			  {
				  hdate = dto.getHour_date();
			  }
			  SQL = "SELECT * FROM PKTABLE WHERE 1=1";
			  if(!teacherNum.trim().equals("")){
				  SQL +=" AND TEACHER_NO='"+dto.getTeacher_no()+"'";
			  }else if(!hdate.trim().equals("")){
				  SQL +=" AND HOUR_DATE='"+dto.getHour_date()+"'";
			  }
			  int i = (Integer.parseInt(dto.getCurrentPage())-1)*Integer.parseInt(dto.getRowperPage());
			  SQL+=" ORDER BY TEACHER_NO LIMIT "+i+","+dto.getRowperPage(); 
			  pstat = conn.prepareStatement(SQL);
			  rst = pstat.executeQuery();
			  while(rst.next()){
				  vo = new QueryVO();
				 vo.setHour_date(rst.getString("HOUR_DATE")==null?"待定":rst.getString("HOUR_DATE")); 
				 vo.setPkweek(rst.getString("PKWEEK")==null?"待定":rst.getString("PKWEEK"));
				 vo.setPkweeks(rst.getString("PKWEEKS")==null?"待定":rst.getString("PKWEEKS"));
				 vo.setObject_no(rst.getString("OBJECT_NO")==null?"待定":rst.getString("OBJECT_NO"));
				 vo.setTeachtime(rst.getString("TEACHTIME")==null?"待定":rst.getString("TEACHTIME"));
				 vo.setSchool_no(rst.getString("SCHOOL_NO")==null?"待定":rst.getString("SCHOOL_NO"));
				 vo.setClass_no(rst.getString("CLASS_NO")==null?"待定":rst.getString("CLASS_NO"));
				 vo.setTeacher_name(rst.getString("TEACHER_NAME")==null?"待定":rst.getString("TEACHER_NAME"));
				 vo.setTeacher_no(rst.getString("TEACHER_NO")==null?"待定":rst.getString("TEACHER_NO"));			  
				 vo.setDepartment_no(rst.getString("DEPARTMENT_NO")==null?"待定":rst.getString("DEPARTMENT_NO"));
				 vo.setClassroom_no(rst.getString("CLASSROOM")==null?"待定":rst.getString("CLASSROOM"));
				 vo.setClasstimes(rst.getString("CLASSTIMES")==null?"待定":rst.getString("CLASSTIMES"));
				 list.add(vo);
			  }
			  objs[1]=list;
		} finally{
			if(rst!=null)rst.close();
			if(pstat!=null)pstat.close();
			SQL=null;
			dto=null;
		}
		return objs;
	}


	public Object findByObject(Object obj) throws SQLException {
		String SQL=null;
		QueryDTO dto=null;
		QueryVO vo = null;
		PreparedStatement pstat=null;
		ResultSet rst=null;
		List list = new ArrayList();
		String strNo = "";
		String classNo = "";
		String password = "";
		try {
			dto = (QueryDTO) obj;
			if(dto.getStuNo()==null){
				strNo = "";
			}else
			{
				strNo = dto.getStuNo();
			}if(dto.getClass_no()==null){
				classNo = "";
			}else{
				classNo = dto.getClass_no();
			}if(dto.getPassword()==null){
				password = "";
			}else
			{
				password = dto.getPassword();
			}
			if(!strNo.trim().equals("")&&!classNo.trim().equals("")&&!password.trim().equals("")){
				SQL ="SELECT * FROM STUDENT WHERE STU_NO ='"+strNo+ "'"+"AND CLASS_NO='"+classNo+"'"+" AND REMARK2='"+password+"'";
			}
			pstat=conn.prepareStatement(SQL);
			rst=pstat.executeQuery();
			while(rst.next()){
				   vo = new QueryVO();
				   vo.setStuNo(rst.getString("STU_NO"));
				   vo.setClass_no(rst.getString("CLASS_NO"));
				   vo.setPassword(rst.getString("REMARK2")); 
				   list.add(vo);
			}
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public int getCount(Object obj) throws SQLException {
		String SQL=null;
		QueryDTO dto=null;
		Integer integer = new Integer(0);
		PreparedStatement pstat=null;
		ResultSet rst=null;
		String teacherNum = "";
		String classno = "";
		String classroom = "";
		String schoolno = "";
		String dates = "";
		try{
			dto=(QueryDTO)obj;
			if(dto.getTeacher_no()==null){
				teacherNum = "";
			}else{
				teacherNum = dto.getTeacher_no();
			}if(dto.getHour_date()==null){
				dates = "";
			}else{
				dates = dto.getHour_date();
			}
			if(dto.getClass_no()==null){
				  classno = "";
			  }else{
				  classno = dto.getClass_no();
			  }
			if(dto.getClassroom_no()==null){
				  classroom = "";
			  }else{
				  classroom = dto.getClassroom_no();
			  }
			if(dto.getSchool_no()==null){
				  schoolno = "";
			  }else{
				  schoolno = dto.getSchool_no();
			  }
			 SQL="SELECT COUNT(*) FROM PKTABLE WHERE 1=1";
			if(!classno.trim().equals("")){
				SQL+=" AND CLASS_NO='"+dto.getClass_no()+"'";				
			}else if(!classroom.trim().equals("")){
				SQL+=" AND CLASSROOM='"+dto.getClassroom_no()+"'";
			}else if(!schoolno.trim().equals("")){
				SQL+=" AND SCHOOL_NO='"+dto.getSchool_no()+"'";
			}
			 if (!teacherNum.trim().equals("")){
					SQL+=" AND TEACHER_NO='"+dto.getTeacher_no()+"'";
				}else if (!dates.trim().equals("")){
					SQL+=" AND HOUR_DATE='"+dto.getHour_date()+"'";
				}
			pstat=conn.prepareStatement(SQL);
			rst=pstat.executeQuery();
			while(rst.next()){
				integer=rst.getInt(1);
			}
		}finally{
			if(rst!=null)rst.close();
			if(pstat!=null)pstat.close();
			SQL=null;
			dto=null; 
		}
		
		return integer;
	}

	public Object getMaxId(Object arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public Object insert(Object arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	public Object update(Object arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
}