📄 headermyselfdao.java
字号:
package edu.yinhe.mis.model;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import edu.yinhe.mis.dto.HeaderMyselfDTO;
import edu.yinhe.mis.vo.HeaderMyselfVO;
import edu.yinhe.mis.vo.TeacherVO;
import edu.yinhe.system.model.BaseDAO;
import edu.yinhe.system.vo.LoginVO;
public class HeaderMyselfDAO extends BaseDAO{
/**
* @author 宋玉萍
* 查询自我评估表数据
*/
public Object delete(Object arg0) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
HeaderMyselfDTO dto = (HeaderMyselfDTO )arg0;
List list = new ArrayList();
HeaderMyselfVO vo = null;
String SQL = null;
try {
SQL = "SELECT ID,TABLE_NAME,TABLE_NO,WEEKREPORT,SELFAPPRAISE,MINISTERAPPRAISE,IDIOGRAPH" +
",EVALUATE_DATE,COMMENT,LISTER FROM HEADERMYSELF WHERE 1=1 AND " +
"IDIOGRAPH IN ( SELECT TEACHER_NAME FROM TEACHER WHERE TEACHER_NAME IN ( " +
"SELECT TEACHER_NAME FROM TEACHER WHERE SUPER_TEACHER_NO IN " +
"(SELECT TEACHER_NO FROM TEACHER WHERE SUPER_TEACHER_NO='"+dto.getRemark2()+"') OR " +
"SUPER_TEACHER_NO='"+dto.getRemark2()+"' OR TEACHER_NO='"+dto.getRemark2()+"') " +
" AND TEACHER_STATES='在职')";
if(dto.getTeacherName() != null && !"".equals(dto.getTeacherName())){
SQL = SQL + " AND IDIOGRAPH LIKE '%"+dto.getTeacherName()+"%' ";
}
if(dto.getStartdate()!=null&&!"".equals(dto.getStartdate())){
SQL=SQL+" AND EVALUATE_DATE BETWEEN '"+dto.getStartdate()+"' AND '"+dto.getEnddate()+"'";
}
SQL = SQL + " ORDER BY EVALUATE_DATE DESC";
pstmt = conn.prepareStatement(SQL);
rs = pstmt.executeQuery();
while(rs.next()){
vo = new HeaderMyselfVO();
vo.setId(rs.getInt("ID"));
vo.setTableName(rs.getString("TABLE_NAME"));
vo.setTableNo(rs.getString("TABLE_NO"));
vo.setEvaluateDate(rs.getString("EVALUATE_DATE"));
vo.setWeekreport(rs.getString("WEEKREPORT"));
vo.setSelfappraise(rs.getInt("SELFAPPRAISE"));
vo.setMinisterappraise(rs.getInt("MINISTERAPPRAISE"));
vo.setIdiograph(rs.getString("IDIOGRAPH"));
vo.setConnent(rs.getInt("COMMENT"));
vo.setLister(rs.getString("LISTER"));
list.add(vo);
}
}catch (SQLException e) {
e.printStackTrace();
}finally{
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
SQL = null;
dto = null;
}
return list;
}
public Object find() throws SQLException {
return null;
}
/**
* @author 姜娟
* 得到老师所有信息
*/
public Object find(Object arg0) throws SQLException {
HeaderMyselfDTO dto = (HeaderMyselfDTO)arg0;
PreparedStatement pstmt = null;
int nIndex = 1;
TeacherVO tvo = null;
List list = new ArrayList();
ResultSet rs = null;
StringBuffer SQL = new StringBuffer("");
SQL.append("SELECT teacher_no,teacher_name,gender,teacher_post ,teacher_states FROM teacher where 1=1");
if(dto.getCurrentPage()!=null&&!"".equals(dto.getCurrentPage())&&
dto.getPageSize()!=null&&!"".equals(dto.getPageSize())){
int curr=(Integer.parseInt(dto.getCurrentPage())-1)*Integer.parseInt(dto.getPageSize());
SQL.append(" limit "+curr+","+Integer.parseInt(dto.getPageSize()));
}
try {
pstmt = conn.prepareStatement(SQL.toString());
rs = pstmt.executeQuery();
while(rs.next()){
tvo = new TeacherVO();
tvo.setTeacher_no(rs.getString("teacher_no"));
tvo.setTeacher_name(rs.getString("teacher_name"));
tvo.setGender(rs.getString("gender"));
tvo.setTeacher_post(rs.getString("teacher_post"));
tvo.setTeacher_states(rs.getString("teacher_states"));
list.add(tvo);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
SQL = null;
}
return list;
}
public Object findAll() throws SQLException {
// TODO Auto-generated method stub
return null;
}
/**
* @author 姜娟
* 校长查看整体报表
* 自我评估表与老师表相关联
*/
@SuppressWarnings("unchecked")
public Object findAll(Object arg0) throws SQLException {
HeaderMyselfDTO tdto = (HeaderMyselfDTO)arg0;
HeaderMyselfVO hvo =null;
@SuppressWarnings("unused")
PreparedStatement pstmt = null;
@SuppressWarnings("unused")
int nIndex = 1;
ArrayList alist = new ArrayList();
//定义存放结果集的ResultSet
ResultSet rs = null;
StringBuffer strBuf=new StringBuffer("");
strBuf.append("SELECT t.teacher_name ,hm.idiograph idiograph,avg(hm.SELFAPPRAISE) selfappraise,avg(hm.MINISTERAPPRAISE) ministerappraise" +
" FROM teacher t left join headermyself hm" +
" on t.teacher_name = hm.IDIOGRAPH group by t.teacher_name ");
if(tdto.getCurrentPage()!=null&&!"".equals(tdto.getCurrentPage())&&
tdto.getPageSize()!=null&&!"".equals(tdto.getPageSize())){
int curr=(Integer.parseInt(tdto.getCurrentPage())-1)*Integer.parseInt(tdto.getPageSize());
strBuf.append(" limit "+curr+","+Integer.parseInt(tdto.getPageSize()));
}
Statement st = conn.createStatement();
rs = st.executeQuery(strBuf.toString());
while(rs.next()){
hvo = new HeaderMyselfVO();
hvo.setIdiograph(rs.getString("idiograph"));
hvo.setSelfappraise(rs.getInt("selfappraise"));
hvo.setMinisterappraise(rs.getInt("ministerappraise"));
alist.add(hvo);
}
return alist;
}
/**
* @author 宋玉萍
* 根据登录信息获得老师编号
*/
public Object findById(Object arg0) throws SQLException {
String teano = "";
LoginVO vo = (LoginVO)arg0;
PreparedStatement pstmt = null;
ResultSet rs = null;
String SQL = "SELECT TEACHER_NO FROM TEACHER WHERE TEACHER_NAME IN (SELECT USER_NAME FROM USERS WHERE USER_ID='"+vo.getId()+"' )";
try{
pstmt = conn.prepareStatement(SQL);
rs = pstmt.executeQuery();
if(rs.next()){
teano = (String )rs.getString("TEACHER_NO");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(pstmt!=null) pstmt=null;
if(rs!=null) rs=null;
}
return teano;
}
/**
* @author 姜娟
* 老师所属部门
*/
public Object findByObject(Object arg0) throws SQLException {
ArrayList list = new ArrayList();
HeaderMyselfDTO dto = (HeaderMyselfDTO)arg0;
PreparedStatement pstmt = null;
int nIndex = 1;
HeaderMyselfVO dvo = null;
ResultSet rs = null;
StringBuffer SQL = new StringBuffer("");
SQL.append("SELECT t.teacher_name,d.DEPARTMENT_NAME FROM teacher t left join department d" +
" on t.DEPARTMENT_NO=d.DEPARTMENT_NO ");
try {
if(dto.getDepartname()!=null&&!"".equals(dto.getDepartname())){
SQL.append(" and d.dispartname ='"+dto.getDepartname()+"'" );
}
Statement st= conn.createStatement();
rs = st.executeQuery(SQL.toString());
while(rs.next()){
dvo = new HeaderMyselfVO();
dvo.setDepartname(rs.getString("DEPARTMENT_NAME"));
dvo.setTeacherName(rs.getString("teacher_name"));
list.add(dvo);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
SQL = null;
}
return list;
}
/**
* @author 姜娟
* 得到老师数目
*/
public int getCount(Object arg0) throws SQLException {
HeaderMyselfDTO tdto = (HeaderMyselfDTO)arg0;
PreparedStatement pstmt = null;
int count =0;
//定义存放结果集的ResultSet
ResultSet rs = null;
StringBuffer strBuf=new StringBuffer("");
strBuf.append("select count(*) icount FROM teacher t");
pstmt = conn.prepareStatement(strBuf.toString());
rs = pstmt.executeQuery();
if(rs.next()){
count = rs.getInt(1);
}
return count;
}
public Object getMaxId(Object arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
/**
* @author 宋玉萍
* 给组长自我评估表插入数据
*/
public Object insert(Object arg0) throws SQLException {
PreparedStatement ps = null;
boolean isInsert=false;
HeaderMyselfDTO dto=(HeaderMyselfDTO)arg0;
int nIndex=1;
String strSQL="INSERT INTO HEADERMYSELF(TABLE_NAME,TABLE_NO,WEEKREPORT,SELFAPPRAISE," +
"MINISTERAPPRAISE,IDIOGRAPH,EVALUATE_DATE,COMMENT,LISTER) VALUES(?,?,?,?,?,?,?,?,?)";
try {
ps=conn.prepareStatement(strSQL);
ps.setString(nIndex++, dto.getTableName());
ps.setString(nIndex++, dto.getTableNo());
ps.setString(nIndex++, dto.getWeekerport());
ps.setInt(nIndex++, dto.getSelfappraise());
ps.setInt(nIndex++, dto.getMinisterappraise());
ps.setString(nIndex++, dto.getIdiograph());
ps.setString(nIndex++, dto.getEvaluateDate());
ps.setInt(nIndex++, dto.getConnent());
ps.setString(nIndex++, dto.getLister());
int i=ps.executeUpdate();
if(i!=0){
isInsert=true;
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(ps!=null) ps=null;
strSQL=null;
}
return isInsert;
}
public Object update(Object arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -