logdao.java

实现统一的人员日志管理系统管理后台

package com.tb.log.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.tb.log.factory.OracleDAOFactory;
import com.tb.log.model.bo.vo.log.ShowCheckLogVo;
import com.tb.log.model.dao.idao.ILogDAO;
import com.tb.log.model.dao.idao.IUserDAO;
import com.tb.log.model.po.Log;
import com.tb.log.model.po.Pro;
import com.tb.log.model.po.User;
import com.tb.log.system.SystemException;
import com.tb.log.util.pages.PageBean;

public class LogDAO implements ILogDAO{
	private int totalResults = 0;
	private int pageSize = 5 ;
	
	private static final String ADD_LOG = "insert into t_worklog values(scq_worklog_logid.nextval,?,?,?,?,?,?,?,?)";
	private static final String UPDATE_LOG = "update t_worklog set pro_id=? ,log_date=?,"
											+"log_desc=? ,log_plan=?,log_state=? where log_id=?";
	private static final String REMOVE_LOG = "delete from t_worklog where log_id=?";
	private static final String FIND_BY_ID= "select * from t_worklog where log_id=?";
	private static final String FIND_ALL = "select * from t_worklog";
	//String find_log_by_page = "select a.log_id,a.log_date,a.log_desc,a.log_plan,b.pro_name ,b.pro_state from t_worklog a,t_project b where a.pro_id=b.pro_id";
	private static final String FIND_BY_PROID = "select * from t_worklog where pro_id=?";
	
	private static final String FIND_BY_USERID = "select * from t_worklog where user_id=?";
	//翻页查询所有日志
	private static final String find_log_by_page = "select * from (select row_.*, rownum rownum_ from " +
			"(select a.log_id,a.log_date,a.log_desc,a.log_plan,a.user_id,b.pro_name ,b.pro_state " +
			"from t_worklog a,t_project b where a.pro_id=b.pro_id ) row_ where rownum <=?) where rownum_ >?";
	private static final String find_log_by_pageCount = "select count(*) from (select row_.*, rownum rownum_ from " +
			"(select a.log_id,a.log_date,a.log_desc,a.log_plan,b.pro_name ,b.pro_state " +
			"from t_worklog a,t_project b where a.pro_id=b.pro_id ) row_ )";
	private static final String REMOVE_BY_PROID = "delete from t_worklog where pro_id=?";
	
	public List findAll(int currentPage) throws SystemException {
		// TODO Auto-generated method stub
		return null;
	}

	public List findByCondition(String consql, int currentPage)
			throws SystemException {
		List logList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(consql);
			psm.setInt(1, pageSize*currentPage);
			psm.setInt(2, pageSize*(currentPage-1));
			ResultSet rs = psm.executeQuery();
			Log log = null;
			while(rs.next()){
				log = new Log(rs.getInt(1) , rs.getInt(2) ,rs.getInt(3) ,rs.getString(4),rs.getString(5),
						rs.getString(6) ,rs.getString(7),rs.getString(8) ,rs.getString(9));
				logList.add(log);
			}
		} catch (SQLException e) {
			String err = "SQLException in LogDAo():findByCondition()---"+e;
			throw new SystemException(err);
		}
		return logList;
	}
	
	public List findCheckByCondition(String consql, int currentPage)throws SystemException {
		List logList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(consql);
			psm.setInt(1, pageSize*currentPage);
			psm.setInt(2, pageSize*(currentPage-1));
			ResultSet rs = psm.executeQuery();
			ShowCheckLogVo log = null;
//			a.log_id,a.log_date,a.log_desc,a.log_plan,a.user_id,a.log_state,b.pro_name ,b.pro_state,c.user_name
//			 log_id, user_id,user_name, log_date, String log_desc,
//			String log_plan, String log_state, String pro_name
			while(rs.next()){
				log = new ShowCheckLogVo(rs.getInt(1) , rs.getInt(5) ,rs.getString(9) ,rs.getString(2),rs.getString(3),
						rs.getString(4) ,rs.getString(6),rs.getString(7));
				logList.add(log);
			}
		} catch (SQLException e) {
			String err = "SQLException in LogDAo():findByCondition()---"+e;
			throw new SystemException(err);
		}
		return logList;
	}
	
	public PageBean getCurrentPageByCondition(String sql) throws SystemException{
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(sql);
			ResultSet rs = psm.executeQuery();
			if (rs.next()) {
				totalResults = rs.getInt(1);
			}
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return new PageBean(totalResults, pageSize);
	}

	public Object findById(String id) throws SystemException {
		Connection conn = OracleDAOFactory.getConnection();
		Log log = null;
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_BY_ID);
			psm.setString(1, id);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				log = new Log(rs.getInt(1) , rs.getInt(2) ,rs.getInt(3) ,rs.getString(4),rs.getString(5),
					rs.getString(6) ,rs.getString(7),rs.getString(8) ,rs.getString(9));
			}
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:findById()--"+e;
			throw new SystemException(err);
		}
		return log;
	}
	
	public Object findByProId(String Proid) throws SystemException {
		Connection conn = OracleDAOFactory.getConnection();
		Log log = null;
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_BY_PROID);
			psm.setString(1, Proid);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				log = new Log(rs.getInt(1) , rs.getInt(2) ,rs.getInt(3) ,rs.getString(4),rs.getString(5),
					rs.getString(6) ,rs.getString(7),rs.getString(8) ,rs.getString(9));
			}
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:findByProId()--"+e;
			throw new SystemException(err);
		}
		return log;
	}
	
	public List findbyUserid(String user_id)throws SystemException{
		List logList = new ArrayList();
		Log log = null;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_BY_USERID);
			psm.setString(1, user_id);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				log = new Log(rs.getInt(1) , rs.getInt(2) ,rs.getInt(3) ,rs.getString(4),rs.getString(5),
					rs.getString(6) ,rs.getString(7),rs.getString(8) ,rs.getString(9));
				logList.add(log);
			}
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:findByProId()--"+e;
			throw new SystemException(err);
		}
		return logList;
	}
	
	
	

	public void remove(Object obj) throws SystemException {
		Log log = (Log)obj;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(REMOVE_LOG);
			psm.setInt(1, log.getLog_id());
			
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:save()--"+e;
			throw new SystemException(err);
		}
	}

	public void save(Object obj) throws SystemException {
		Log log = (Log)obj;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(ADD_LOG);
			psm.setInt(1, log.getUser_id());
			psm.setInt(2,log.getPro_id() );
			psm.setString(3, log.getLog_date()) ;
			psm.setString(4,log.getWrite_date());
			psm.setString(5, log.getLog_desc());
			psm.setString(6, log.getLog_plan());
			psm.setString(7, log.getSum_question());
			psm.setString(8, log.getLog_state());
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:save()--"+e;
			throw new SystemException(err);
		}
	}

	public void update(Object obj) throws SystemException {
		Log log = (Log)obj;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(UPDATE_LOG);
			
			psm.setInt(1,log.getPro_id() );
			psm.setString(2, log.getLog_date()) ;
			psm.setString(3 ,log.getLog_desc());
			psm.setString(4, log.getLog_plan());
			psm.setString(5, log.getLog_state());
			psm.setInt(6, log.getLog_id());
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:save()--"+e;
			throw new SystemException(err);
		}
	}
	
	public void removeByProid(int pro_id) throws SystemException {
		
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(REMOVE_BY_PROID);
			psm.setInt(1, pro_id);
			
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in LogDAO:save()--"+e;
			throw new SystemException(err);
		}
	}
	
	public static void main(String[] args) {
		String sql = "select * from (select row_.*, rownum rownum_ from (select a.log_id,a.log_date,a.log_desc,a.log_plan,a.user_id,a.log_state,b.pro_name ,b.pro_state,c.user_name from t_worklog a,t_project b ,t_user c where a.pro_id=b.pro_id and a.user_id=c.user_id ) row_ where rownum <=?) where rownum_ >?";
	    try {
	    	//List logList = new LogDAO().findCheckByCondition(sql, 1);
	    	new LogDAO().removeByProid(5533);
			System.out.println();
		} catch (SystemException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	
}