elecdevinfodaoimpl.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 ElecDevInfoDAOImpl implements ElecDevInfoDAO {
	
    private static final String GET_CUSTOMER_SQL =
        "SELECT * FROM ElecDev_info WHERE ElecDevNo = ?";
    
    private static final String CREATE_CUSTOMER_SQL =
        "INSERT INTO ElecDev_info (ElecEquiNo,ElecDevNo,UserId,ElecDevTypeNo,ElecDevName,ElecDevType,ElecDevPower,ElecDevSpec,ElecDevStatus,ElecDevAddr,ReMark,Memo) VALUES (ElecEquiNo.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    private static final String DELETE_CUSTOMER_SQL =
        "DELETE FROM ElecDev_info WHERE ElecDevNo = ?";

    private static final String UPDATE_CUSTOMER_SQL =
        "UPDATE ElecDev_info SET ElecDevNo=?, UserId=?, ElecDevTypeNo=?, ElecDevName=?, ElecDevType=?, ElecDevPower=?, ElecDevSpec=?, ElecDevStatus=?,ElecDevAddr=?, ReMark=?, Memo=? WHERE ElecDevNo = ?";

    private static final String SEARCH_CUSTOMER_SQL =
        "select e1.ElecEquiNo,e1.ElecDevNo,e1.userid,e1.elecdevtypeno,e1.elecdevname,e1.elecdevtype" + 
        ",e1.elecdevpower,e1.elecdevspec,e1.elecdevstatus,e1.elecdevaddr,e1.remark,e1.memo,e2.elecdevtypename" +
        " from elecdev_info e1,ElecDev_Type e2 where e1.elecdevtypeno=e2.elecdevtypeno AND ";	

	public boolean deleteElecDevInfo(String elecDevNo) throws Exception {
		
		boolean flag = false;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        pStatement = connection.prepareStatement(DELETE_CUSTOMER_SQL);
	        pStatement.setString(1, elecDevNo);
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
			
	    	pStatement.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	    
	}

	public ElecDevInfoTO getElecDevInfo(String elecDevNo) throws Exception {
		
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    ResultSet rs = null;
	    ElecDevInfoTO elecDevInfo = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        // Prepare a statement to insert a record
	        pStatement = connection.prepareStatement(GET_CUSTOMER_SQL);
	        pStatement.setString(1,elecDevNo);
	        rs = pStatement.executeQuery();
	        
	        if (rs.next()) {
	        	elecDevInfo = new ElecDevInfoTO();
	        	elecDevInfo.setElecEquiNo(rs.getInt("ElecEquiNo"));
	        	elecDevInfo.setElecDevNo(rs.getString("ElecDevNo"));
	        	elecDevInfo.setUserId(rs.getString("UserId"));
	        	elecDevInfo.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
	        	elecDevInfo.setElecDevName(rs.getString("ElecDevName"));
	        	elecDevInfo.setElecDevType(rs.getString("ElecDevType"));
	        	elecDevInfo.setElecDevPower(rs.getDouble("ElecDevPower"));
	        	elecDevInfo.setElecDevSpec(rs.getString("ElecDevSpec"));
	        	elecDevInfo.setElecDevStatus(rs.getInt("ElecDevStatus"));
	        	elecDevInfo.setElecDevAddr(rs.getString("ElecDevAddr"));
	        	elecDevInfo.setReMark(rs.getInt("ReMark"));
	        	elecDevInfo.setMemo(rs.getString("Memo"));
	        }
	        
	    	rs.close();
	    	pStatement.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return elecDevInfo;
	}

	public boolean insertElecDevInfo(ElecDevInfoTO elecDevInfo) throws Exception {
		
		boolean flag = false;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        //Prepare a statement to insert a record
	        pStatement = connection.prepareStatement(CREATE_CUSTOMER_SQL);
	        
	        pStatement.setString(1, elecDevInfo.getElecDevNo());
	        pStatement.setString(2, elecDevInfo.getUserId());
	        pStatement.setInt(3, elecDevInfo.getElecDevTypeNo());
	        pStatement.setString(4, elecDevInfo.getElecDevName());
	        pStatement.setString(5, elecDevInfo.getElecDevType());
	        pStatement.setDouble(6, elecDevInfo.getElecDevPower());
	        pStatement.setString(7, elecDevInfo.getElecDevSpec());
	        pStatement.setInt(8, elecDevInfo.getElecDevStatus());
	        pStatement.setString(9, elecDevInfo.getElecDevAddr());
	        pStatement.setInt(10, elecDevInfo.getReMark());
	        pStatement.setString(11, elecDevInfo.getMemo());
	        
	        int i = pStatement.executeUpdate();
	        flag = i > 0 ? true : false;
	        
	        pStatement.close();	        
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	}

	public ArrayList searchElecDevInfos(ElecDevInfoTO search) throws Exception {
		
	    ArrayList arrayList = new ArrayList();
	    Connection connection = null;
	    Statement statement = null;
	    ResultSet rs = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    //Build the search criterias
	    StringBuffer criteriaSql = new StringBuffer(512);
	    criteriaSql.append(SEARCH_CUSTOMER_SQL);
	    
	    if (search.getMemo() != null) {
	           criteriaSql.append(" UserId in ( select UserId from ElecInfo where  OrganId in ( Select organid from organ Connect by prior organid=suporganid Start with organid = " +
	                    DBUtil.fixSqlFieldValue(search.getMemo()) + " )) AND ");
	  	    }	    
	 	    
	    
	    if (search.getElecDevNo() != null) {
	        criteriaSql.append("ElecDevNo LIKE '%" +
	                DBUtil.fixSqlFieldValue(search.getElecDevNo()) + "%' AND ");
	    }
	    if (search.getUserId() != null) {
	        criteriaSql.append("UserId LIKE '%" +
	        		DBUtil.fixSqlFieldValue(search.getUserId()) + "%' AND ");
	    }

	    
	    //Remove unused 'And' & 'WHERE'
	    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 UserId,ElecDevNo");
	    try {
	        connection = db.conn;
	        statement = connection.createStatement();
	        rs = statement.executeQuery(criteriaSql.toString());
	        
	        while (rs.next()) {
	        	ElecDevInfoTO elecDevInfo = new ElecDevInfoTO();
	        	elecDevInfo.setElecEquiNo(rs.getInt("ElecEquiNo"));
	        	elecDevInfo.setElecDevNo(rs.getString("ElecDevNo"));
	        	elecDevInfo.setElecDevTypeName(rs.getString("elecdevtypename"));
	        	elecDevInfo.setUserId(rs.getString("UserId"));
	        	elecDevInfo.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
	        	elecDevInfo.setElecDevName(rs.getString("ElecDevName"));
	        	elecDevInfo.setElecDevType(rs.getString("ElecDevType"));
	        	elecDevInfo.setElecDevPower(rs.getDouble("ElecDevPower"));
	        	elecDevInfo.setElecDevSpec(rs.getString("ElecDevSpec"));
	        	elecDevInfo.setElecDevStatus(rs.getInt("ElecDevStatus"));
	        	if(elecDevInfo.getElecDevStatus() == 1) {
	        		elecDevInfo.setElecDevStatusName("好");
	        	} else if(elecDevInfo.getElecDevStatus() == 2) {
	        		elecDevInfo.setElecDevStatusName("需要维修");
	        	} else if(elecDevInfo.getElecDevStatus() == 3) {
	        		elecDevInfo.setElecDevStatusName("停用");
	        	} else if(elecDevInfo.getElecDevStatus() == 4) {
	        		elecDevInfo.setElecDevStatusName("报废");
	        	} else if(elecDevInfo.getElecDevStatus() == 5) {
	        		elecDevInfo.setElecDevStatusName("其他");
	        	}
	        	elecDevInfo.setElecDevAddr(rs.getString("ElecDevAddr"));
	        	elecDevInfo.setReMark(rs.getInt("ReMark"));
	        	elecDevInfo.setMemo(rs.getString("Memo"));
	
	            arrayList.add(elecDevInfo);
	        }
	        
	        rs.close();
	        statement.close();	        
	    } catch (SQLException e) {
	        e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return arrayList;
	}

	public boolean updateElecDevInfo(ElecDevInfoTO elecDevInfo) throws Exception {
		
		boolean flag = false;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    DBPoolManager db = new DBPoolManager();
	    db.getConnection();
	    
	    try {
	        connection = db.conn;
	        // Prepare a statement to insert a record
	        pStatement = connection.prepareStatement(UPDATE_CUSTOMER_SQL);            
	       
	        pStatement.setString(1, elecDevInfo.getElecDevNo());
	        pStatement.setString(2, elecDevInfo.getUserId());
	        pStatement.setInt(3, elecDevInfo.getElecDevTypeNo());
	        pStatement.setString(4, elecDevInfo.getElecDevName());
	        pStatement.setString(5, elecDevInfo.getElecDevType());
	        pStatement.setDouble(6, elecDevInfo.getElecDevPower());
	        pStatement.setString(7, elecDevInfo.getElecDevSpec());
	        pStatement.setInt(8, elecDevInfo.getElecDevStatus());
	        pStatement.setString(9, elecDevInfo.getElecDevAddr());
	        pStatement.setInt(10, elecDevInfo.getReMark());
	        pStatement.setString(11, elecDevInfo.getMemo());
	        pStatement.setString(12, elecDevInfo.getElecDevNo());
	        
	        int i = pStatement.executeUpdate();
	        flag = i > 0 ? true : false;
	        
	        pStatement.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	        throw new Exception();
	    } finally {
	    	db.freeConnection();
	    }
	    
	    return flag;
	}

}