emailaccountdao.java

来自「功能是公安和学校的管理系统,用struts+hibernate+spring等匡」· Java 代码 · 共 299 行

package com.comingnet.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.db.DBUtils;
import com.db.KeyGenerator;

public class EmailAccountDAO {
	private Connection conn ;
	private PreparedStatement pstmt ;
	private ResultSet rs ;
	
	public int getCountOfAccountById(int userid,String userclass ,int xxbh){
		int count = 0 ;
		String sqlStr ="select count(*) from  yjzh where zhry =? and zhryclass=? and xxbh =? " ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1,userid) ;
			pstmt.setString(2,userclass) ;
			pstmt.setInt(3,xxbh) ;
			rs = pstmt.executeQuery() ;
			if(rs.next()) {
				count = rs.getInt(1) ;
			}
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			
		} catch (Exception e) {
			System.out.println("EmailAccountDAO.EmailAccountDAO----->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return count ;
	}
	
	public boolean AddEmailAccount(EmailAccount ea){
		boolean flag = false ;
		String sqlStr = "insert into yjzh (zhid,zhry,zhryclass,xxbh,zhmc,zhpop,zhsmtp,zhyh,zhmm,zhwjj,zhmr,zhdz,zhyz) values(?,?,?,?,?,?,?,?,?,?,?,?,?)" ;
		try {
			int zhid = KeyGenerator.getInstance().getNextKey("yjzh");
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1,zhid) ;
			pstmt.setInt(2,ea.getZhry()) ;
			pstmt.setString(3,ea.getZhryclass()) ;
			pstmt.setInt(4,ea.getXxbh()) ;
			pstmt.setString(5,ea.getZhmc()) ;
			pstmt.setString(6,ea.getZhpop()) ;
			pstmt.setString(7,ea.getZhsmtp()) ;
			pstmt.setString(8,ea.getZhyh()) ;
			pstmt.setString(9,ea.getZhmm()) ;
			pstmt.setInt(10,ea.getZhwjj()) ;
			pstmt.setInt(11,ea.getZhmr()) ;
			pstmt.setString(12,ea.getZhdz()) ;
			pstmt.setInt(13,ea.getZhyz()) ;
			pstmt.executeUpdate() ;
			
			flag = true ;
			DBUtils.closeStatement(pstmt) ;
			} catch (Exception e) {
			System.out.println("EmailAccountDAO.AddEmailAccount------>"+e) ;
			
		}finally{
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return flag ; 
	}
	public boolean EditEmailAccount(EmailAccount ea){
		boolean flag = false ;
		String sqlStr = "update yjzh set zhmc=?,zhpop=?,zhsmtp=?,zhyh=?,zhmm=?,zhwjj=?,zhmr=?,zhdz=?,zhyz =? where zhid=? " ;
		
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setString(1,ea.getZhmc()) ;
			pstmt.setString(2,ea.getZhpop()) ;
			pstmt.setString(3,ea.getZhsmtp()) ;
			pstmt.setString(4,ea.getZhyh()) ;
			pstmt.setString(5,ea.getZhmm()) ;
			pstmt.setInt(6,ea.getZhwjj()) ;
			pstmt.setInt(7,ea.getZhmr()) ;
			pstmt.setString(8,ea.getZhdz()) ;
			pstmt.setInt(9,ea.getZhyz()) ;
			pstmt.setInt(10,ea.getZhid()) ;
			
			pstmt.executeUpdate() ;
			
			flag = true ;
			DBUtils.closeStatement(pstmt) ;
			} catch (Exception e) {
			System.out.println("EmailAccountDAO.EditEmailAccount------>"+e) ;
			
		}finally{
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return flag ; 
	}
	
	public EmailAccount queryById(int zhid ){
		String sqlStr = "select * from yjzh where  zhid =? " ;
		EmailAccount ea = null ;
		
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1,zhid) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				ea = new EmailAccount() ;
				
				ea.setZhid(rs.getInt("zhid")) ;
				ea.setZhry(rs.getInt("zhry")) ;
				ea.setZhryclass(rs.getString("zhryclass")) ;
				ea.setXxbh(rs.getInt("xxbh")) ;
				ea.setZhmc(rs.getString("zhmc")) ;
				ea.setZhpop(rs.getString("zhpop")) ;
				ea.setZhsmtp(rs.getString("zhsmtp")) ;
				ea.setZhyh(rs.getString("zhyh")) ;
				ea.setZhmm(rs.getString("zhmm")) ;
				ea.setZhwjj(rs.getInt("zhwjj"))  ;
				ea.setZhmr(rs.getInt("zhmr")) ;
				ea.setZhdz(rs.getString("zhdz")) ;
				ea.setZhyz(rs.getInt("zhyz")) ;

			}
			
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			
		} catch (Exception e) {
			System.out.println("EmailAccountDAO.queryById------>"+e) ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return ea ;
	}
	public int getZhid(int userid,String userclass,int xxbh)  //取得yjzh表中的zhid
	{
		int zhid=0;
        try
        {
        	conn=DBUtils.getConnection();
        	pstmt=conn.prepareStatement("select zhid from yjzh where zhry=? and zhryclass=? and xxbh=? order by zhmr desc");
        	pstmt.setInt(1,userid);
        	pstmt.setString(2,userclass);
        	pstmt.setInt(3,xxbh);
        	rs=pstmt.executeQuery();
        	if(rs.next())
        	{
        		zhid=rs.getInt(1);
        	}
        }catch(Exception e)
        {
           System.out.println(e);	
        }finally
        {
        	DBUtils.closeResultSet(rs);
        	DBUtils.closeStatement(pstmt);
        	DBUtils.closeConnection(conn);
        }
		return zhid;
	}
	
	
	
	public ArrayList<EmailAccount> queryAll(int userid,String userclass ,int xxbh){
		ArrayList<EmailAccount> emailAccountList = null ;
		EmailAccount ea = null ;
		String sqlStr = "select * from yjzh where zhry =? and zhryclass=? and xxbh =?" ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1,userid) ;
			pstmt.setString(2,userclass) ;
			pstmt.setInt(3,xxbh) ;
			rs = pstmt.executeQuery() ;
			emailAccountList = new ArrayList<EmailAccount> ();
			while(rs.next()){
				ea = new EmailAccount() ;
				
				ea.setZhid(rs.getInt("zhid")) ;
				ea.setZhry(rs.getInt("zhry")) ;
				ea.setZhryclass(rs.getString("zhryclass")) ;
				ea.setXxbh(rs.getInt("xxbh")) ;
				ea.setZhmc(rs.getString("zhmc")) ;
				ea.setZhpop(rs.getString("zhpop")) ;
				ea.setZhsmtp(rs.getString("zhsmtp")) ;
				ea.setZhyh(rs.getString("zhyh")) ;
				ea.setZhmm(rs.getString("zhmm")) ;
				ea.setZhwjj(rs.getInt("zhwjj"))  ;
				ea.setZhmr(rs.getInt("zhmr")) ;
				ea.setZhdz(rs.getString("zhdz")) ;
				ea.setZhyz(rs.getInt("zhyz")) ;
				emailAccountList.add(ea) ;
			}
			
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("EmailAccountDAO.queryAll------>"+e) ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return emailAccountList ;
	}
	
	public boolean DeleteEmailAccount(int zhid){
		boolean flag = false ;
		String sqlStr = "delete  yjzh where zhid = ?" ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1,zhid) ;
			pstmt.executeUpdate() ;
			flag = true ;
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			
		} catch (Exception e) {
			System.out.println("EmailAccountDAO.deleteEmailAccount----->"+e) ;
			return flag ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return flag ;
	}
	
	//修改默认
	public boolean editZhmr(int userid,String userclass ,int xxbh){
		boolean flag = false ;
		String sqlStr = "update yjzh set zhmr=0 where  zhry =? and zhryclass=? and xxbh =? " ;
		try {
			conn = DBUtils.getConnection();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setInt(1, userid);
			pstmt.setString(2, userclass);
			pstmt.setInt(3, xxbh);
			pstmt.executeUpdate() ;
			flag = true ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("EmailAccountDAO.deleteEmailAccount----->"+e) ;
			return flag ;
		}finally{
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}		
		return flag ;		
	}
	
public List<HashMap> querywjj(int wjjry,String rylx,int xxbh ) {
		
		HashMap wjjmap;
		List<HashMap> wjjList = new ArrayList<HashMap>() ;
		String sqlStr = "select * from  yjwjj where wjjry = 0 or (wjjry=? and rylx=? and xxbh = ?)" ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1, wjjry) ;
			pstmt.setString(2, rylx) ;
			pstmt.setInt(3, xxbh) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				wjjmap = new HashMap()  ;
				wjjmap.put("id", rs.getInt("wjjid")) ;
				wjjmap.put("name",rs.getString("wjjmc")) ;
				
				wjjList.add(wjjmap) ;
			}
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("EmailAccountDAO.querywjj----------------------->"+e);
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		
		return wjjList;
	};
}