materialmaintaindao.java

JSP移动商品管理平台源代码.........

package imis_mate.DAO;

import imis_mate.bean.MaterialMaintainBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cmis.database.DBPoolManager;

public class MaterialMaintainDAO {
	private boolean flag;
	private DBPoolManager dbpool;
//	private PreparedStatement pstmt;
	private Statement st ;
	
	public MaterialMaintainDAO(){
		dbpool=new DBPoolManager();
	}
	
	public boolean save(MaterialMaintainBean mmb){
		flag = false;
		dbpool.getConnection();
		//String sql = "insert into Material_Maintain_Table(maintainID,materialName,maModel,maBrand,maMadeIn,materialTypeNo,maintainCount,MaintainDate,userCode,maintainDep,managerBy,useBy,roomNu,buildingNu,maintainCause,maintainFee,reMark) values(MaintainID.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		String sql = "insert into Material_Maintain_Table(maintainID,materialName,maModel,maBrand,maMadeIn,materialTypeNo,maintainCount,MaintainDate,userCode,maintainDep,managerBy,useBy,roomNu,buildingNu,maintainFee,maintainCause,reMark) values(MaintainID.nextval,'";
		sql = sql + mmb.getMaterialName()+"','";
		sql = sql + mmb.getMaModel()+"','";
		sql = sql + mmb.getMaBrand()+"','";
		sql = sql + mmb.getMaMadeIn()+"','";
		sql = sql + mmb.getMaterialTypeNo()+"',";
		sql = sql + mmb.getMaintainCount()+",to_date('";
		sql = sql + mmb.getMaintainDate()+"','yyyy-mm-dd'),'";
		sql = sql + mmb.getUserCode()+"',";
		sql = sql + mmb.getMaintainDep()+",'";
		sql = sql + mmb.getManagerBy()+"','";
		sql = sql + mmb.getUseBy()+"','";
		sql = sql + mmb.getRoomNu()+"','";
		sql = sql + mmb.getBuildingNu()+"',";
		sql = sql + mmb.getMaintainFee()+",'";
		sql = sql + mmb.getMaintainCause()+"','";
		sql = sql + mmb.getReMark()+"')";
		
		try {
			
			st = dbpool.conn.createStatement();
//			pstmt=dbpool.conn.prepareStatement(sql);
//			pstmt.setString(1, mmb.getMaterialName());
//			pstmt.setString(2, mmb.getMaModel());
//			pstmt.setString(3,mmb.getMaBrand());
//			pstmt.setString(4, mmb.getMaMadeIn());
//			pstmt.setInt(5, mmb.getMaterialTypeNo());
//			pstmt.setInt(6, mmb.getMaintainCount());
//			pstmt.setDate(7, mmb.getMaintainDate());
//			pstmt.setString(8, mmb.getUserCode());
//			pstmt.setInt(9, mmb.getMaintainDep());
//			pstmt.setString(10, mmb.getManagerBy());
//			pstmt.setString(11, mmb.getUseBy());
//			pstmt.setString(12, mmb.getRoomNu());
//			pstmt.setString(13, mmb.getBuildingNu());
//			pstmt.setString(14, mmb.getMaintainCause());
//			pstmt.setDouble(15, mmb.getMaintainFee());
//			pstmt.setString(16, mmb.getReMark());
			flag=st.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag=false;
			
		}finally{
			dbpool.freeConnection();
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st=null;
			}
		}
		
		return flag;
	}
	
	public boolean update(MaterialMaintainBean mmb){
		flag=false;
		dbpool.getConnection();
//		String sql = "update Material_Maintain_Table set materialName=?,maModel=?,maBrand=?,maMadeIn=?,materialTypeNo=?,maintainCount=?,MaintainDate=?,userCode=?,maintainDep=?," +
//				"managerBy=?,useBy=?,roomNu=?,buildingNu=?,maintainCause=?,maintainFee=?,reMark=? where maintainID=?";
		String sql = "update Material_Maintain_Table set materialName='";
		sql = sql + mmb.getMaterialName()+"',maModel='";
		sql = sql + mmb.getMaModel()+"',maBrand='";
		sql = sql + mmb.getMaBrand()+"',maMadeIn='";
		sql = sql + mmb.getMaMadeIn()+"',materialTypeNo='";
		sql = sql + mmb.getMaterialTypeNo()+"',maintainCount=";
		sql = sql + mmb.getMaintainCount()+",MaintainDate=to_date('";
		sql = sql + mmb.getMaintainDate()+"','yyyy-mm-dd'),userCode='";
		sql = sql + mmb.getUserCode()+"',maintainDep=";
		sql = sql + mmb.getMaintainDep()+",managerBy='";
		sql = sql + mmb.getManagerBy()+"',useBy='";
		sql = sql + mmb.getUseBy()+"',roomNu='";
		sql = sql + mmb.getRoomNu()+"',buildingNu='";
		sql = sql + mmb.getBuildingNu()+"',maintainFee=";
		sql = sql + mmb.getMaintainFee()+",maintainCause='";
		sql = sql + mmb.getMaintainCause()+"',reMark='";
		sql = sql + mmb.getReMark()+"'where maintainID=";
		sql = sql + mmb.getMaintainID();
		
	

		
		try {
			st = dbpool.conn.createStatement();
//			pstmt=dbpool.conn.prepareStatement(sql);
//			pstmt.setString(1, mmb.getMaterialName());
//			pstmt.setString(2, mmb.getMaModel());
//			pstmt.setString(3,mmb.getMaBrand());
//			pstmt.setString(4, mmb.getMaMadeIn());
//			pstmt.setInt(5, mmb.getMaterialTypeNo());
//			pstmt.setInt(6, mmb.getMaintainCount());
//			pstmt.setDate(7, mmb.getMaintainDate());
//			pstmt.setString(8, mmb.getUserCode());
//			pstmt.setInt(9, mmb.getMaintainDep());
//			pstmt.setString(10, mmb.getManagerBy());
//			pstmt.setString(11, mmb.getUseBy());
//			pstmt.setString(12, mmb.getRoomNu());
//			pstmt.setString(13, mmb.getBuildingNu());
//			pstmt.setString(14, mmb.getMaintainCause());
//			pstmt.setDouble(15, mmb.getMaintainFee());
//			pstmt.setString(16, mmb.getReMark());
//			pstmt.setInt(17, mmb.getMaintainID());
		//	System.out.println(sql);
			flag=st.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			dbpool.freeConnection();
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st=null;
			}
		}
		return flag;
	}
	
	public boolean delete(int maintainID){
		flag =false;
		dbpool.getConnection();
		String sql = "delete from Material_Maintain_Table where maintainID="+maintainID;

		try {
			st = dbpool.conn.createStatement();
			flag=st.executeUpdate(sql)>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			dbpool.freeConnection();
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st=null;
			}
		}
		
		
		return flag;
	}
	public ArrayList getMaterialMaintainBean(){
		ArrayList list = new ArrayList();
		MaterialMaintainBean mmb = new MaterialMaintainBean();
		dbpool.getConnection();
		String sql = "select MaintainID,MaterialName,MaModel,MaBrand,MamadeIn,MaterialTypeNo,MaintainCount,MaintainDate,UserCode,MaintainDep,ManagerBy,UseBy,RoomNu,BuildingNu,MaintainCause,MaintainFee,ReMark from Material_Maintain_Table";
		Statement stmt;
		try {
			stmt = dbpool.conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				mmb.setMaintainID(rs.getInt("MaintainID"));
				if(rs.getString("MaterialName")==null){
					mmb.setMaterialName("");
				}else{
					mmb.setMaterialName(rs.getString("MaterialName").trim());
				}
				if(rs.getString("MaModel")==null){
					mmb.setMaModel("");
				}else{
					mmb.setMaModel(rs.getString("MaModel").trim());
				}
				if(rs.getString("MaBrand")==null){
					mmb.setMaBrand("");
				}else{
					mmb.setMaBrand(rs.getString("MaBrand").trim());
				}
				if(rs.getString("MaMadeIn")==null){
					mmb.setMaMadeIn("");
				}else{
					mmb.setMaMadeIn(rs.getString("MaMadeIn").trim());
				}
				mmb.setMaterialTypeNo(rs.getString("MaterialTypeNo"));
				mmb.setMaintainCount(rs.getInt("MaintainCount"));
				if(rs.getString("MaintainDate")==null){
					mmb.setMaintainDate("");
				}else{
					mmb.setMaintainDate(rs.getString("MaintainDate").trim());
				}
				if(rs.getString("UserCode")==null){
					mmb.setUserCode("");
				}else{
					mmb.setUserCode(rs.getString("UserCode").trim());
				}
				mmb.setMaintainDep(rs.getInt("MaintainDep"));
				if(rs.getString("ManagerBy")==null){
					mmb.setManagerBy("");
				}else{
					mmb.setManagerBy(rs.getString("ManagerBy").trim());
				}
				if(rs.getString("UseBy")==null){
					mmb.setUseBy("");
				}else{
					mmb.setUseBy(rs.getString("UseBy").trim());
				}
				if(rs.getString("RoomNu")==null){
					mmb.setRoomNu("");
				}else{
					mmb.setRoomNu(rs.getString("RoomNu").trim());
				}
				if(rs.getString("BuildingNu")==null){
					mmb.setBuildingNu("");
				}else{
					mmb.setBuildingNu(rs.getString("BuildingNu").trim());
				}
				if(rs.getString("MaintainCause")==null){
					mmb.setMaintainCause("");
				}else{
					mmb.setMaintainCause(rs.getString("MaintainCause").trim());
				}
				mmb.setMaintainFee(rs.getDouble("MaintainFee"));
				if(rs.getString("ReMark")==null){
					mmb.setReMark("");