lend.java

jsp+javabean+sqlserver开发的设备管理系统

//设备领用类
package device;

import java.sql.ResultSet;
import java.util.Vector;

import devicecomm.DBOper;
import devicecomm.StringOper;


public class Lend {
	private int LendId;			// 借用编号
	private String Status;		// 状态（申请、领用和归还）
	private String Dept;	// 部门名称
	private String LEmplName;	// 借用人
	private String PostTime;	// 提交时间
	private String UserId;		// 提交人编号


	StringOper so = new StringOper();

//判断是否存在一个领用信息
    public boolean getLend()throws Exception
    {
        //int conditionNo = 0;

        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

		String sql = "select  PostTime,LendId,UserId,";
		sql+="LEmplName,Dept,Status from dev_Lend where LendId="+LendId;

        try
        {
            rs = o_DBOper.getResultSet(sql);
            if(rs.next())
            {
				setPostTime(so.ReplaceNull(rs.getString("PostTime")));
				setDept(so.ReplaceNull(rs.getString("Dept")));
				setLEmplName(so.ReplaceNull(rs.getString("LEmplName")));
				setUserId(so.ReplaceNull(rs.getString("UserId")));
				setStatus(so.ReplaceNull(rs.getString("Status")));
               return true;
            }
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
        return false;
    }
//得到多个设备领用信息
	public Vector getMoreLend() throws Exception
    {
        Vector v_Lend = new Vector();
		int conditionNo = 0;

        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

        String sql = "select PostTime,LendId,UserId,";
		sql+="LEmplName,Dept,Status from dev_Lend";
        String condition = " where ";
        try
        {
            if(LendId != 0)
            {
                condition += "LendId="+LendId;
                conditionNo++;
            }
            if(PostTime != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " PostTime='"+PostTime+"'";
                conditionNo++;
            }
			if(UserId != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " UserId='"+UserId+"'";
                conditionNo++;
            }
			if(Dept != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " Dept='"+Dept+"'";
                conditionNo++;
            }
			if(LEmplName != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " LEmplName='"+LEmplName+"'";
                conditionNo++;
            }
			if(Status != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " Status='"+Status+"'";
                conditionNo++;
            }
            if(conditionNo > 0)
            {
                sql += condition;
            }
            sql += " order by LendId desc";

            rs = o_DBOper.getResultSet(sql);
//	System.out.println(sql);
			while(rs.next())
            {
                Lend o_Lend2 = new Lend();
				o_Lend2.setLendId(rs.getInt("LendId"));
				o_Lend2.setPostTime(so.ReplaceNull(rs.getString("PostTime")));
				o_Lend2.setDept(so.ReplaceNull(rs.getString("Dept")));
				o_Lend2.setLEmplName(so.ReplaceNull(rs.getString("LEmplName")));
				o_Lend2.setUserId(so.ReplaceNull(rs.getString("UserId")));
				o_Lend2.setStatus(so.ReplaceNull(rs.getString("Status")));
                v_Lend.add(o_Lend2);
            }
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
        return v_Lend;
    }

//删除设备领用信息（原则上不能删除保存的纪录，但是可以删除旧的、无效的纪录，删除后数据不可恢复而且不对库存有任何影响）
	public void DeleteLend(int Lendid) throws Exception
    {
        DBOper o_DBOper = new DBOper();
		//ResultSet rs = null;

        String sql_dlt = "delete from dev_Lend where LendId =" + Lendid;
        try
        {
            //o_DBOper.getResultSet(sql_dlt);
			//删除领用设备明细列表
			sql_dlt = "delete from dev_lendlist where LendId=" + Lendid;
			o_DBOper.DataUpdate(sql_dlt);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }
//插入新领用表，返回领用编号
	public int CreateLend() throws Exception
    {
        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

        //String sql = "insert into dev_Lend(LendId,UserId,Dept,LEmplName,PostTime,Status) ";
		//sql =sql+"values(s_lendid.N/A,'"+UserId+"','"+Dept+"','"+LEmplName+"',getdate(),'申请')";
      String sql = "insert into dev_Lend(UserId,Dept,LEmplName,PostTime,Status) ";
		sql =sql+"values('"+UserId+"','"+Dept+"','"+LEmplName+"',getdate(),'申请')";
//System.out.println("INSERT SQL:"+sql);
        try
        {
            o_DBOper.getResultSet(sql);
			sql = "select max(LendId) as mid from dev_lend";
			rs = o_DBOper.getResultSet(sql);
			if(rs.next())
			{
				System.out.println(rs.getInt("mid"));
				return rs.getInt("mid");
			}
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
		return 1;
    }
//更新领用表信息，不包括状态更改
	public void UpdateLend() throws Exception
    {
        DBOper o_DBOper = new DBOper();
       // ResultSet rs = null;

        String sql = "update dev_Lend set Dept='"+Dept+"',LEmplName='"+LEmplName+"' ";
		sql = sql+" where LendId="+LendId;
	//System.out.println("UPDATE SQL :"+sql);
        try
        {
            o_DBOper.DataUpdate(sql);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }
//更新领用表状态为领用或者归还
	public void UpdateStatus(String sNew) throws Exception
    {
		// 取得领用编号
		int LendId = getLendId();
		// 连接数据库
		DBOper o_DBOper = new DBOper();
       // ResultSet rs = null;
		// 更新语句
        String sql = "Update dev_Lend set Status='" + sNew + "' where LendId="+LendId;
        try
        {
			// 执行更新语句
            o_DBOper.DataUpdate(sql);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }

	public void setLendId(int LendId){
        this.LendId = LendId;
    }
    public int getLendId(){
        return this.LendId;
    }
	public void setDept(String Dept){
        this.Dept = Dept;
    }
    public String getDept(){
        return this.Dept;
    }
	public void setLEmplName(String LEmplName){
        this.LEmplName = LEmplName;
    }
    public String getLEmplName(){
        return this.LEmplName;
    }
	public void setStatus(String Status){
        this.Status = Status;
    }
    public String getStatus(){
        return this.Status;
    }
	public void setPostTime(String PostTime){
        this.PostTime = PostTime;
    }
    public String getPostTime(){
        return this.PostTime;
    }

	public void setUserId(String UserId){
        this.UserId = UserId;
    }
    public String getUserId(){
        return this.UserId;
    }
 }