elecdev_typedaoimpl.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_TypeDAOImpl implements ElecDev_TypeDAO{
	private static final String GET_CUSTOMER_SQL =
		"SELECT * FROM ElecDev_Type WHERE ElecDevTypeName = ?";
	private static final String CREATE_CUSTOMER_SQL =
		 "INSERT INTO ElecDev_Type (ElecDevTypeNo,ElecDevTypeName,ReMark) VALUES (ElecDevTypeNo.Nextval, ?,?)";
	private static final String DELETE_CUSTOMER_SQL =
        "DELETE FROM ElecDev_Type WHERE ElecDevTypeNo = ?";
	private static final String UPDATE_CUSTOMER_SQL =
		"UPDATE ElecDev_Type SET ElecDevTypeName=?,ReMark=? where ElecDevTypeNo=?";
	private static final String SEARCH_CUSTOMER_SQL =
        "SELECT * FROM ElecDev_Type WHERE ";

	
	public ElecDev_TypeTO getElecDev_Type(int  ElecDevTypeNo) throws Exception {
		//Connection connection = null;
		 DBPoolManager db = new DBPoolManager();
	     db.getConnection();
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        ElecDev_TypeTO elecDev_Type = null;
        String sql = "SELECT * FROM ElecDev_Type WHERE ElecDevTypeNo = ?";
        
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(sql);
            pStatement.setInt(1,ElecDevTypeNo);
            rs = pStatement.executeQuery();
            if (rs.next()) {
            	elecDev_Type = new ElecDev_TypeTO();
            	elecDev_Type.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
            	elecDev_Type.setElecDevTypeName(rs.getString("ElecDevTypeName"));
            	elecDev_Type.setReMark(rs.getInt("ReMark"));
            
            }
            
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(rs);
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return elecDev_Type;
	}
	public ElecDev_TypeTO getElecDev_Type(String ElecDevTypeName) throws Exception {
		//Connection connection = null;
		 DBPoolManager db = new DBPoolManager();
	     db.getConnection();
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        ElecDev_TypeTO elecDev_Type = null;
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement(GET_CUSTOMER_SQL);
            pStatement.setString(1,ElecDevTypeName);
            rs = pStatement.executeQuery();
            if (rs.next()) {
            	elecDev_Type = new ElecDev_TypeTO();
            	elecDev_Type.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
            	elecDev_Type.setElecDevTypeName(rs.getString("ElecDevTypeName"));
            	elecDev_Type.setReMark(rs.getInt("ReMark"));
            
            }
            
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(rs);
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return elecDev_Type;
	}
	
	public ElecDev_TypeTO getElecDevTypeName(String ElecDevTypeName) throws Exception {
		//Connection connection = null;
		 DBPoolManager db = new DBPoolManager();
	     db.getConnection();
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        ElecDev_TypeTO elecDev_Type = null;
        try {
            //connection = DBConnection.getConnection();
            // Prepare a statement to insert a record
            pStatement = db.conn.prepareStatement("SELECT * FROM ElecDev_Type WHERE ElecDevTypeName = ?");
            pStatement.setString(1,ElecDevTypeName);
            rs = pStatement.executeQuery();
            if (rs.next()) {
            	elecDev_Type = new ElecDev_TypeTO();
            	elecDev_Type.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
            	elecDev_Type.setElecDevTypeName(rs.getString("ElecDevTypeName"));
            	elecDev_Type.setReMark(rs.getInt("ReMark"));
            
            }
            
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			//DBConnection.close(rs);
			//DBConnection.close(pStatement);
			//DBConnection.close(connection);
        	db.freeConnection();
        }
        
        return elecDev_Type;
	}

	public boolean deleteElecDev_Type(int ElecDevTypeNo) throws Exception {
		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, ElecDevTypeNo);
			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 boolean insertElecDev_Type(ElecDev_TypeTO ElecDev_Type) 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.setString(1,ElecDev_Type.getElecDevTypeName());
            pStatement.setInt(2, ElecDev_Type.getReMark());
            
            
            
            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 searchElecDev_Type(ElecDev_TypeTO ElecDev_Type) 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_Type.getElecDevTypeNo() !=-1) {
            criteriaSql.append("ElecDevTypeNo=" +
            		ElecDev_Type.getElecDevTypeNo() + " AND ");
        }
        
        if (!ElecDev_Type.getElecDevTypeName().equals("")) {
            criteriaSql.append("ElecDevTypeName='" +
            		ElecDev_Type.getElecDevTypeName() + "' AND ");
        }
        
        
        if (ElecDev_Type.getReMark() !=-1) {
            criteriaSql.append("ReMark='" + ElecDev_Type.getReMark()+"'");
        } 
         
       
        
         
       
        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);
	    try {
	        //connection = DBConnection.getConnection();
	        statement = db.conn.createStatement();
	        rs = statement.executeQuery(criteriaSql.toString());
	        
	        while (rs.next()) {
	        	ElecDev_TypeTO elecDev_TypeTO = new ElecDev_TypeTO();
	        	elecDev_TypeTO.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
	        	elecDev_TypeTO.setElecDevTypeName(rs.getString("ElecDevTypeName"));
	        	elecDev_TypeTO.setReMark(rs.getInt("ReMark"));
	        	
	
	            arrayList.add(elecDev_TypeTO);
	        }
	
	    } catch (SQLException e) {
	        e.printStackTrace();
	        throw new Exception();
	    } finally {
			//DBConnection.close(rs);
			//DBConnection.close(statement);
	    	db.freeConnection();
	    }
	    
	    return arrayList;
	}

	public boolean updateElecDev_Type(ElecDev_TypeTO ElecDev_Type) 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.setString(1,ElecDev_Type.getElecDevTypeName());
            pStatement.setInt(2, ElecDev_Type.getReMark());
            pStatement.setInt(3, ElecDev_Type.getElecDevTypeNo());
            
            
            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;
	}

}