elecdev_chkdaoimpl.java

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

package imis_elec;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import database.DBPoolManager;




public class ElecDev_ChkDAOImpl implements ElecDev_ChkDAO{
	private static final String GET_CUSTOMER_SQL =
		"SELECT * FROM ElecDev_Chk WHERE ElecDevChkId = ?";
	private static final String CREATE_CUSTOMER_SQL =
		 "INSERT INTO ElecDev_Chk (ElecDevChkId,ChkDevType,UserId,ElecDevRepFlag,ElecDevChkStaff,ElecDevChkContent,ElecDevChkDate,ReMark,Memo) VALUES (ElecDevChkId.Nextval, ?, ?, ?, ?, ?, to_date(?,'yy-mm-dd'), ?, ?)";
	private static final String DELETE_CUSTOMER_SQL =
        "DELETE FROM ElecDev_Chk WHERE ElecDevChkId = ?";
	private static final String UPDATE_CUSTOMER_SQL =
		"UPDATE ElecDev_Chk SET ChkDevType=?,UserId=?,ElecDevRepFlag=?,ElecDevChkStaff=?,ElecDevChkContent=?,ElecDevChkDate=to_date(?,'yy-mm-dd'),ReMark=?,Memo=? where ElecDevChkId=?";
	private static final String SEARCH_CUSTOMER_SQL =
        "select e1.userid,e1.ElecDevChkId,e1.ChkDevType,e2.elecdevtypename,e1.elecdevrepflag,e1.elecdevchkstaff,e1.elecdevchkcontent,e1.elecdevchkdate,e1.remark,e1.memo  "+
		"from ElecDev_Chk e1, ElecDev_Type e2 where e1.chkdevtype=e2.elecdevtypeno AND ";
	
	
	public boolean deleteElec_Chk(int ElecDevChkId) throws Exception {
		// TODO Auto-generated method stub
		
		boolean flag = false;
        //Connection connection = null;
		DBPoolManager db = new DBPoolManager();
        db.getConnection();
        PreparedStatement pStatement = null;
        try {
            //connection = DBConnection.getConnection();
            pStatement = db.conn.prepareStatement(DELETE_CUSTOMER_SQL);
            pStatement.setInt(1, ElecDevChkId);
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return flag;
        
	}

	public ElecDev_ChkTO getElec_Chk(int ElecDevChkId) throws Exception {
		//Connection connection = null;
		DBPoolManager db = new DBPoolManager();
        db.getConnection();
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        ElecDev_ChkTO elecDev_Chk = null;
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(GET_CUSTOMER_SQL);
            pStatement.setInt(1,ElecDevChkId);
            rs = pStatement.executeQuery();
            if (rs.next()) {
            	elecDev_Chk = new ElecDev_ChkTO();
            	elecDev_Chk.setElecDevChkId(rs.getInt("ElecDevChkId"));
            	elecDev_Chk.setChkDevType(rs.getInt("ChkDevType"));
            	elecDev_Chk.setUserId(rs.getString("UserId"));
            	elecDev_Chk.setElecDevRepFlag(rs.getInt("ElecDevRepFlag"));
            	elecDev_Chk.setElecDevChkStaff(rs.getString("ElecDevChkStaff"));
            	elecDev_Chk.setElecDevChkContent(rs.getString("ElecDevChkContent"));
            	elecDev_Chk.setElecDevChkDate(rs.getDate("ElecDevChkDate").toString());
            	elecDev_Chk.setReMark(rs.getInt("ReMark"));
            	elecDev_Chk.setMemo(rs.getString("Memo"));
            }
            
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(rs);
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
			db.freeConnection();
        }
        
        return elecDev_Chk;
        
	}

	public boolean insertElec_Chk(ElecDev_ChkTO elecDev_Chk) throws Exception {
		
		boolean flag = false;
		//Connection connection = null;
		DBPoolManager db = new DBPoolManager();
        db.getConnection();
        PreparedStatement pStatement = null;
        try {
            //connection = DBConnection.getConnection();
            //Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(CREATE_CUSTOMER_SQL);
            
            pStatement.setInt(1, elecDev_Chk.getChkDevType());
            pStatement.setString(2,elecDev_Chk.getUserId());
            pStatement.setInt(3, elecDev_Chk.getElecDevRepFlag());
            pStatement.setString(4, elecDev_Chk.getElecDevChkStaff());
            pStatement.setString(5, elecDev_Chk.getElecDevChkContent());
            pStatement.setString(6, elecDev_Chk.getElecDevChkDate());
            pStatement.setInt(7, elecDev_Chk.getReMark());
            pStatement.setString(8,elecDev_Chk.getMemo());
            
            
            int i = pStatement.executeUpdate();
            
            
            flag = i > 0 ? true : false;
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return flag;
	}

	public ArrayList searchElec_Chk(ElecDev_ChkTO elecDev_Chk,String endDate) throws Exception {
		 ArrayList arrayList = new ArrayList();
	        //Connection connection = null;
		 	DBPoolManager db = new DBPoolManager();
	        db.getConnection();
	        Statement statement = null;
	        ResultSet rs = null;
	        StringBuffer criteriaSql = new StringBuffer(512);
	        criteriaSql.append(SEARCH_CUSTOMER_SQL);
	        
	        if (elecDev_Chk.getElecDevChkDate() != null && !elecDev_Chk.getElecDevChkDate().equals("") && endDate != null && !endDate.equals("")) {
		        criteriaSql.append("ElecDevChkDate between (to_date('" +
		                DBUtil.fixSqlFieldValue(elecDev_Chk.getElecDevChkDate()) + "','yyyy-mm-dd')) AND (to_date('" + 
		                DBUtil.fixSqlFieldValue(endDate) + "','yyyy-mm-dd'))" + " AND ");
		    }
	        
	        if (elecDev_Chk.getElecDevChkId() !=-1) {
	            criteriaSql.append("ElecDevChkId=" +
	                   elecDev_Chk.getElecDevChkId() + " AND ");
	        }
	        
	        if (elecDev_Chk.getChkDevType() !=-1) {
	            criteriaSql.append("ChkDevType=" +
	                   elecDev_Chk.getChkDevType() + " AND ");
	        }
	        
	        
	        if (!elecDev_Chk.getUserId().equals("")) {
	            criteriaSql.append("UserId='" + elecDev_Chk.getUserId() + "' AND ");
	        } 
	         
	       
	       
	         
	       
	        if (criteriaSql.substring(criteriaSql.length()-5).
		            equals(" AND "))
		        criteriaSql.delete(criteriaSql.length()-5,
		                criteriaSql.length()-1);
		    if (criteriaSql.substring(criteriaSql.length()-7).
		            equals(" WHERE "))
		        criteriaSql.delete(criteriaSql.length()-7,
		                criteriaSql.length()-1);
		    criteriaSql.append("order by e1.userid,e1.elecdevchkdate");
		   
		    
		    try {
		        //connection = DBConnection.getConnection();
		        statement = db.conn.createStatement();
		        rs = statement.executeQuery(criteriaSql.toString());
		        
		        while (rs.next()) {
		        	ElecDev_ChkTO elecDev_ChkTO = new ElecDev_ChkTO();
		        	elecDev_ChkTO.setElecDevChkId(rs.getInt("ElecDevChkId"));
		        	elecDev_ChkTO.setChkDevType(rs.getInt("ChkDevType"));
		        	elecDev_ChkTO.setUserId(rs.getString("UserId"));
		        	elecDev_ChkTO.setElecDevRepFlag(rs.getInt("ElecDevRepFlag"));
		        	if(elecDev_ChkTO.getElecDevRepFlag()==1)
		        	{
		        		elecDev_ChkTO.setElecDevRepFlagName("需要维修");
		        	}
		        	if(elecDev_ChkTO.getElecDevRepFlag()==2)
		        	{
		        		elecDev_ChkTO.setElecDevRepFlagName("不需要 ");
		        	}
		        	elecDev_ChkTO.setElecDevChkStaff(rs.getString("ElecDevChkStaff"));
		        	elecDev_ChkTO.setElecDevChkContent(rs.getString("ElecDevChkContent"));
		        	elecDev_ChkTO.setElecDevChkDate(rs.getDate("ElecDevChkDate").toString());
		        	elecDev_ChkTO.setReMark(rs.getInt("ReMark"));
		        	elecDev_ChkTO.setMemo(rs.getString("Memo"));
		        	elecDev_ChkTO.setChkDevTypeName(rs.getString("Elecdevtypename"));
		
		            arrayList.add(elecDev_ChkTO);
		        }
		
		    } catch (SQLException e) {
		        e.printStackTrace();
		        throw new Exception();
		    } finally {
				//DBConnection.close(rs);
				//DBConnection.close(statement);
		    	db.freeConnection();
		    }
		    
		    return arrayList;
		}
	        
	
	        

	public boolean updateElec_Chk(ElecDev_ChkTO elecDev_Chk) throws Exception {
		
		boolean flag = false;
        //Connection connection = null;
		DBPoolManager db = new DBPoolManager();
        db.getConnection();
        PreparedStatement pStatement = null;
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(UPDATE_CUSTOMER_SQL);  
            pStatement.setInt(1, elecDev_Chk.getChkDevType());
            pStatement.setString(2,elecDev_Chk.getUserId());
            pStatement.setInt(3, elecDev_Chk.getElecDevRepFlag());
            pStatement.setString(4, elecDev_Chk.getElecDevChkStaff());
            pStatement.setString(5, elecDev_Chk.getElecDevChkContent());
            pStatement.setString(6, elecDev_Chk.getElecDevChkDate());
            pStatement.setInt(7, elecDev_Chk.getReMark());
            pStatement.setString(8,elecDev_Chk.getMemo());
            pStatement.setInt(9,elecDev_Chk.getElecDevChkId());
            
            int i = pStatement.executeUpdate();
            flag = i > 0 ? true : false;
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return flag;
	}
	

}