carddao.java

oa办公管理系统。一些小型的企业办公管理用的系统。一个月废寝忘食的结果。

package com.soft.card.dao;

import java.sql.Connection;
import java.sql.Date;
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 com.soft.pagecut.PageableResultSet;
import com.soft.pagecut.pageable;
import com.soft.util.DBConn;
import com.soft.vo.Card;
import com.soft.vo.EquipmentInfo;

public class CardDAO {
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private int id=0;
	private ResultSet rs = null;
	private pageable res=null;
	
	int PageCount;
	int rowsCount;
	int PageRowsCount;
	
	int PageSize;
	int CurPage;
	public int getPageCount() {
		return PageCount;
	}

	

	public int getPageRowsCount() {
		return PageRowsCount;
	}

	

	public int getPageSize() {
		return PageSize;
	}

	public void setPageSize(int pageSize) {
		PageSize = pageSize;
	}

	public int getCurPage() {
		return CurPage;
	}

	public int getRowsCount() {
		return rowsCount;
	}

	public void setCurPage(int curPage) {
		CurPage = curPage;
	}
	public List getAllCard()
	{
		List<Card> lt = new ArrayList<Card>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        res=new PageableResultSet(st.executeQuery("select c.cardid,c.cardname,ch.cardholdername,c.companyname,c.name,c.address,c.officephone,c.mobilephone from card c,cardholder ch where c.classification=ch.cardholderid"));//构造一个Pageable
			res.setPageSize(5);//每页5个记录
            
			System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			res.gotoPage(getCurPage());//跳转到第n页
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			System.out.println("current page:"+res.getCurPage());
			for(int i=0; i<res.getPageRowsCount(); i++)
			
			{
			    Card emp = new Card();
			    //String iscanapplystr="否";
				int cardid=res.getInt("cardid");
				
				String cardname=res.getString("cardname");
				//int cardholder=res.getInt("classification");
				String cardholdername=res.getString("cardholdername");
				String companyname = res.getString("companyname");
				String customername=res.getString("name");
				String customeraddress=res.getString("address");
				String customeroffice=res.getString("officephone");
				String customermobile=res.getString("mobilephone");
		
				emp.setCardId(cardid);
				emp.setCardName(cardname);
				//emp.setCardHolder(cardholder);
				emp.setCardHoldername(cardholdername);
				emp.setCompanyName(companyname);
				emp.setName(customername);
				emp.setCustomerAddress(customeraddress);
				emp.setCustomerOfficePhone(customeroffice);
				emp.setCustomerMobilePhone(customermobile);
				
				lt.add(emp);
				res.next();
			}
			System.out.println(lt.size());
		    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;
	}
	
	public int addCard(String cardName,int cardHolder,String companyName,String name,String customerOccupatation,String customerAddress,String customerPostalCode,String customerOfficePhone,String customerMobilePhone,String customerEmail )
	{
		int i = 0;//初始值
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps=conn.prepareStatement("select max(cardid) from card");
			ResultSet rs=ps.executeQuery();
			if(!rs.next())
			{
				ps = conn.prepareStatement("insert  into card values("+1+",'"+cardName+"',"+cardHolder+",'"+companyName+"','"+name+"','"+customerOccupatation+"','"+customerAddress+"','"+customerPostalCode+"','"+customerOfficePhone+"','"+customerMobilePhone+"','"+customerEmail+"')");
			}
			else
			{
			   id=rs.getInt("max(cardid)");
			   id=id+1;
			   ps = conn.prepareStatement("insert  into card values("+id+",'"+cardName+"',"+cardHolder+",'"+companyName+"','"+name+"','"+customerOccupatation+"','"+customerAddress+"','"+customerPostalCode+"','"+customerOfficePhone+"','"+customerMobilePhone+"','"+customerEmail+"')");
			}
			i = ps.executeUpdate();
		    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();
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return i;
	}
	
	public int deleteCard(int id)
	{
		int i = 0;//初始值
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps=conn.prepareStatement("delete from card where cardid="+id+"");
			i = ps.executeUpdate();
		    conn.commit();
		}
		catch(Exception 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();
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
		
		
	}
	
	

}