departmentdao.java

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

package dao;

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

import po.DepartmentPO;

public class DepartmentDAO {
	
	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_dept");
			while(rs.next())
			{
				DepartmentPO detpo=new DepartmentPO();
				detpo.setDept_id(rs.getInt("Dept_id"));
				detpo.setDeptname(rs.getString("deptname"));
				detpo.setDescreption(rs.getString("descreption"));
				al.add(detpo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
					    rs.close();
					if(state!=null)
						rs.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return al;
	}
	//查询一条记录
	public DepartmentPO findDepByID(int id)
	{
		DepartmentPO detpo=null;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_dept where Dept_id="+id);
			while(rs.next())
			{
				detpo=new DepartmentPO();
				detpo.setDept_id(rs.getInt("Dept_id"));
				detpo.setDeptname(rs.getString("deptname"));
				detpo.setDescreption(rs.getString("descreption"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
					    rs.close();
					if(state!=null)
						rs.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return detpo;
	}
	//更新数据
	public boolean departmentUpdate(DepartmentPO dm)
	{
		boolean isOK=false;
		
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			
			int i=state.executeUpdate("update Table_dept set deptname = '"+dm.getDeptname()+"',descreption = '"+dm.getDescreption()+"' where Dept_id="+dm.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 boolean AddDepartment(DepartmentPO dm)
	{
		boolean isOK=false;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			int i = state.executeUpdate("insert into Table_dept values("+dm.getDept_id()+",'"+dm.getDeptname()+"','"+dm.getDescreption()+"')");
			if(i>0)
			{
				isOK=true;
			}
		} 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();
				}
		}
		return isOK;
	}
	//取得ID
	public int getNextID()
	{
		int myID=0;
		conn = Tools.getConnection();
		try {
			state=conn.createStatement();
			rs=state.executeQuery("select max(Dept_id) myID from Table_dept");
			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;
	}
	//查找ID
	public int lookid(String Dept_name)
	{
		Statement state = null;
		ResultSet rs = null;
		int i = 0;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs = state.executeQuery("select Dept_id from Table_dept where deptname = '"+Dept_name+"'");
			if(rs.next())
			{
				i = rs.getInt("Dept_id");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return i;
	}
}