cartholderdao.java

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

package com.soft.cartholder.dao;
import com.soft.vo.*;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
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.*;

public class cartHolderDAO {
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private pageable res=null;
	int PageCount;
	int rowsCount;
	int PageRowsCount;
	int id=0;
	
	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 getcardHolder()
	{
		List<CartholderInfo> lt = new ArrayList<CartholderInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        res=new PageableResultSet(st.executeQuery("select * from CardHolder"));//构造一个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++)
			{
				CartholderInfo ch = new CartholderInfo();
				
				int cardHolderId=res.getInt("cardHolderId");
				String cardHolderName=res.getString("cardHolderName");
				
				int belongTo=res.getInt("belongTo");
				
			
				ch.setBelongTo(belongTo);
				ch.setCardHoldID(cardHolderId);
				ch.setCartHoldName(cardHolderName);
			
				lt.add(ch);
				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 addCardHolder(String cardHolderName,int belongTo)
	{
	
		int i = 0;//初始值
		
		
		
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps=conn.prepareStatement("select max(cardHolderId) from CardHolder");
			ResultSet rs=ps.executeQuery();
			if(!rs.next())
			{
				ps = conn.prepareStatement("insert  into CardHolder (cardHolderId,cardHolderName,belongTo) values("+1+",'"+cardHolderName+"',"+belongTo+")");
			}
			else
			{
			   id=rs.getInt("max(cardHolderId)");
			   id=id+1;
			   ps = conn.prepareStatement("insert  into CardHolder (cardHolderId,cardHolderName,belongTo) values("+id+",'"+cardHolderName+"',"+belongTo+")");
			}
			i = ps.executeUpdate();
			System.out.println("iii=====:"+i);
		    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 delCardHolder(int cardHolderid)
	{
		int i = 0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps = conn.prepareStatement("delete from CardHolder where cardHolderId=?");
			ps.setInt(1,cardHolderid);
			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();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	
	public int updateCartHolder(int belongTo,String cardHolderName ) throws UnsupportedEncodingException
	{
		int i = 0;
		
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			cardHolderName=new String(cardHolderName.getBytes("ISO8859_1"),"utf-8");
     		System.out.println("------------sql----------");
			ps = conn.prepareStatement("update  cardholder set cardholdername=? where cardholderid=?");
			//ps.setInt(1,isShare);
			ps.setString(1, cardHolderName);
			ps.setInt(2,belongTo);
			
			
			//st=conn.createStatement();
			
			//String sql="update  CardHolder set cardHolderName='"+cardHolderName+"' where belongTo="+belongTo+"";
			
			//officephone=new  String(officephone.getBytes( "ISO8859_1"), "utf-8");
			
			
			//i=st.executeUpdate(sql);
			i = ps.executeUpdate();
			System.out.println("-----------------i==========="+i);
		    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 i;
	}
	
	public List searchCardHolder(int cardHolderId)
	{
		List<CartholderInfo> lt = new ArrayList<CartholderInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select * from CardHolder where cardHolderId=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,cardHolderId);
			/*if(userName!=null&&!userName.equals(""))
			{
				sql += "and u.username like ?";
			}
			if(userNo!=null&&!userNo.equals(""))
			{
				sql +=" and u.userno=?";
			}
			ps = conn.prepareStatement(sql);
			if(userName!=null&&!userName.equals(""))
			{
				ps.setString(1,"%"+userName+"%");
				if(userNo!=null&&!userNo.equals(""))
				{
					ps.setInt(2,Integer.parseInt(userNo));
				}
			}
			else
			{
				if(userNo!=null&&!userNo.equals(""))
				{
					ps.setInt(1,Integer.parseInt(userNo));
				}
			}*/
			rs = ps.executeQuery();
			while(rs.next())
			{
				CartholderInfo ch = new CartholderInfo();
				int cardHoldID=rs.getInt("cardHolderId");
				String cartHoldName=rs.getString("cardHolderName");
				int belongTo=rs.getInt("belongTo");

				ch.setBelongTo(belongTo);
				ch.setCardHoldID(cardHoldID);
				
				ch.setCartHoldName(cartHoldName);
				
				lt.add(ch);
			}
			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;
	}
}