allcarddao.java

实现办公自动化系统

package com.oa.struts.perOffice.modle;
import java.util.List;
import java.sql.*;
import java.util.ArrayList;

import com.oa.struts.vo.AllCardInfo;
import com.oa.util.DBConn;
import com.oa.util.GetNames;


public class AllCardDAO {
	Connection con=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	DBConn db=null;
	

public AllCardDAO() {
		super();
		// TODO Auto-generated constructor stub
	}

//插入名片
public boolean insertAllcard(AllCardInfo ac)
{
	boolean result=false;
	try
	{
		String sql="insert into tb_namecard values(NAMECARD_SEQ.nextval,?,?,?,?,?,?,?,?,?)";
		db=new DBConn();
		con=db.getConnection();
		pstmt=con.prepareStatement(sql);
		pstmt.setInt(1,ac.getUsersex());
		pstmt.setString(2,ac.getCardname());
		pstmt.setInt(3,ac.getCardfileid());
		pstmt.setInt(4,ac.getUserid());
		pstmt.setString(5,ac.getContacttel());
		pstmt.setInt(6,ac.getIsshare());
		pstmt.setString(7,ac.getDeptname());
		pstmt.setString(8,ac.getAddress());
		pstmt.setString(9, ac.getEmail());
		
		int i=pstmt.executeUpdate();
		if(i>0)
		{
			
			result=true;
		}
			
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();		
		
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	
	return result;
}


//显示名片信息
public List  displayAllCard(int DeptID,String userName)
{
	DBConn tj = new DBConn();
	Connection conn = null; 
	Statement st = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	GetNames get=new GetNames();
	String deptName=get.getDeptName(DeptID);
	List<AllCardInfo> lt = new ArrayList<AllCardInfo>();
	try {
		
		conn=tj.getConnection();
		conn.setAutoCommit(false);
	    String sql = "select * from tb_namecard where 1=1";
	    if(userName!=null&&!userName.equals(""))
		{
			sql += "and  cardname like ?";
		}
		if(deptName!=null&&!deptName.equals(""))
		{
			sql +=" and deptName=?";
		}
	
		
		System.out.println("sql======="+sql);
		ps = conn.prepareStatement(sql);
		if(userName!=null&&!userName.equals(""))
		{
			ps.setString(1,"%"+userName+"%");
			if(deptName!=null&&!deptName.equals(""))
			{
				ps.setString(2,deptName);
			}
		}
		else
		{
			if(deptName!=null&&!deptName.equals(""))
			{
				ps.setString(1,deptName);
			}
		}
		rs = ps.executeQuery();
		int i=0;
		while(rs.next())
		{
			AllCardInfo info=new AllCardInfo();
		
			info.setUserid(rs.getInt("userID"));
			info.setCardname(rs.getString("cardName"));
			lt.add(i++,info);
		}
	    conn.commit();
	} catch (SQLException e) {
		
		try {
			if(conn!=null)
			{
				conn.rollback();//事务回滚
			}
		} catch (SQLException e1) {
			
			e1.printStackTrace();
		}
		e.printStackTrace();
	}
	finally
	{
		try 
		{
			if(conn!=null)
			{
				conn.close();
			}
			if(ps!=null)
			{
				ps.close();
			}
			if(rs!=null)
			{
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
	}
}
	return lt;
	
}


private void print(String string) {
	// TODO Auto-generated method stub
	
}

//删除Card根据id
public boolean deleteCard(int id)
{
	boolean result=false;
	try
	{
		String sql="delete from tb_namecard where id=?";
		db=new DBConn();
		con=db.getConnection();
		pstmt=con.prepareStatement(sql);
		pstmt.setInt(1,id);
		int i=pstmt.executeUpdate();
		if(i>0)
		{
			result=true;
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();
		}
		
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	
	return result;

}

//修改Card
public boolean updateCard(AllCardInfo ac)
{
	boolean result=false;
	try
	{
		String sql="update card set id=?,cardname=?";
		db=new DBConn();
		con=db.getConnection();
		pstmt=con.prepareStatement(sql);
		pstmt.setInt(1,ac.getId());
		pstmt.setString(2,ac.getCardname());
		
        
			
		int i=pstmt.executeUpdate();
		if(i>0)
		{
			result=true;
		}
			
		if(pstmt!=null) pstmt.close();
		if(con!=null) con.close();		
		
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	
	return result;
}




}