incomedao.java

超市管理系统,jsp+javabean+sqlserver

package dao;

import java.sql.*;
import java.util.ArrayList;

import vo.incomeVO;

public class incomeDAO {
	
	Connection conn=null;
	Statement state=null;
	ResultSet rs=null;
    //查询所有
	public ArrayList findAll()
	{
		ArrayList al=new ArrayList();
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_income,Table_dept where Table_income.Dept_id(+)=Table_dept.Dept_id");
			while(rs.next())
			{
				
				incomeVO inpo=new incomeVO();
                
				inpo.setIncome_ID(rs.getInt("Income_ID"));
				inpo.setDept_id(rs.getInt("Dept_id"));
				inpo.setDeptname(rs.getString("deptname"));
				inpo.setDescreption(rs.getString("descreption"));
				inpo.setDaily_income(rs.getInt("Daily_income"));
				inpo.setBusiness_Date(rs.getString("Business_Date"));
				inpo.setLst_mod_timestemp(rs.getString("Lst_mod_timestemp"));
				al.add(inpo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return al;
	}
	//查询一条
	public incomeVO findincomeByID(int id)
	{
		incomeVO invo=new incomeVO();
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_income,Table_dept where Table_income.Dept_id=Table_dept.Dept_id and Table_income.Dept_id="+id);
			while(rs.next())
			{
				
				invo.setIncome_ID(rs.getInt("Income_ID"));
				invo.setDept_id(rs.getInt("Dept_id"));
				invo.setDeptname(rs.getString("deptname"));
				invo.setDescreption(rs.getString("descreption"));
				invo.setDaily_income(rs.getInt("Daily_income"));
				invo.setBusiness_Date(rs.getString("Business_Date"));
				invo.setLst_mod_timestemp(rs.getString("Lst_mod_timestemp"));
				
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return invo;
	}
	//更新一条
	public boolean incomeUpdate(incomeVO im)
	{
		boolean isOK=false;
		
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			//System.out.println("update Table_income set Dept_id="+im.getDept_id()+",Daily_income="+im.getDaily_income()+",Business_Date='"+im.getBusiness_Date()+"',Lst_mod_timestemp='"+im.getLst_mod_timestemp()+"' where Income_ID="+im.getIncome_ID());
			int i=state.executeUpdate("update Table_income set Daily_income="+im.getDaily_income()+" where Dept_id="+im.getDept_id());
			if(i>0)
			{
				isOK=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(state!=null)
					state.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return isOK;
	}
	//插入一条
	public void Addincome(String name,int sum)
	{
		DepartmentDAO ddao=new DepartmentDAO();
		incomeDAO idao=new incomeDAO();
		int idd=ddao.lookid(name);
		int id=idao.getNextID();
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			System.out.println("update Table_income set Daily_income="+sum+" where Dept_id="+id);
			int i = state.executeUpdate("insert into Table_income values("+id+","+idd+","+sum+",sysdate,sysdate)");
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(state!=null)
					state.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}
	//取得ID
	public int getNextID()
	{
		int myID=0;
		conn = Tools.getConnection();
		try {
			state=conn.createStatement();
			rs=state.executeQuery("select max(Dept_id) myID from Table_income");
			if(rs.next())
			{
				myID = rs.getInt("myID");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			
				try {
					if(rs!=null)
					rs.close();
					if(state!=null)
						state.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return ++myID;
	}
}