elecinfodaoimpl.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;



/**
 *
 * @author jyg_xiaogang
 */
public class ElecInfoDAOImpl implements ElecInfoDAO{
    
    private static final String GET_CUSTOMER_SQL =
            "SELECT * FROM ElecInfo WHERE UserId = ?";
    private static final String CREATE_CUSTOMER_SQL =
            "INSERT INTO ElecInfo (UserId,UserName,ElecType,UsedBy,Addr,Depa,ElecGre,RunCap,Remark,OrganId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String DELETE_CUSTOMER_SQL =
            "DELETE FROM ElecInfo WHERE UserId = ?";
    private static final String UPDATE_CUSTOMER_SQL =
            "UPDATE ElecInfo SET UserName=?, ElecType=?, UsedBy=?, Addr=?, Depa=?, ElecGre=?, RunCap=?, Remark=?,OrganId=? WHERE UserId = ?";
    private static final String SEARCH_CUSTOMER_SQL =
            "SELECT * FROM ElecInfo WHERE ";
    
    public boolean insertElecInfo(ElecInfoTO elecInfo) 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, elecInfo.getUserId());
            pStatement.setString(2, elecInfo.getUserName());
            pStatement.setString(3, elecInfo.getElecType());
            pStatement.setString(4, elecInfo.getUsedBy());
            pStatement.setString(5, elecInfo.getAddr());
            pStatement.setString(6, elecInfo.getDepa());
            pStatement.setString(7, elecInfo.getElecGre());
            pStatement.setString(8, elecInfo.getRunCap());
            pStatement.setString(9, elecInfo.getRemark());
            pStatement.setString(10, elecInfo.getBuyDep());
            
            
            int i = pStatement.executeUpdate();
            flag = i > 0 ? true : false;
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			db.freeConnection();
        }
        
        return flag;
    }
    
    public boolean updateElecInfo(ElecInfoTO elecInfo) 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, elecInfo.getUserName());
            pStatement.setString(2, elecInfo.getElecType());
            pStatement.setString(3, elecInfo.getUsedBy());
            pStatement.setString(4, elecInfo.getAddr());
            pStatement.setString(5, elecInfo.getDepa());
            pStatement.setString(6, elecInfo.getElecGre());
            pStatement.setString(7, elecInfo.getRunCap());
            pStatement.setString(8, elecInfo.getRemark());
            pStatement.setString(9, elecInfo.getBuyDep());                      
            pStatement.setString(10, elecInfo.getUserId());
            
            
        
            
            int i = pStatement.executeUpdate();
            flag = i > 0 ? true : false;
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
			db.freeConnection();
        }
        
        return flag;
    }
    
    public ElecInfoTO getElecInfo(String userId) throws Exception {
    	
        Connection connection = null;
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        ElecInfoTO elecInfo = 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,userId);
            rs = pStatement.executeQuery();
            
            if (rs.next()) {
            	elecInfo = new ElecInfoTO();
            	elecInfo.setUserId(userId);
            	elecInfo.setUserName(rs.getString("UserName"));
            	elecInfo.setElecType(rs.getString("ElecType"));
            	elecInfo.setUsedBy(rs.getString("UsedBy"));
            	elecInfo.setAddr(rs.getString("Addr"));
            	elecInfo.setDepa(rs.getString("Depa"));
                elecInfo.setElecGre(rs.getString("ElecGre"));
                elecInfo.setRunCap(rs.getString("RunCap"));
                elecInfo.setRemark(rs.getString("Remark"));
                elecInfo.setBuyDep(rs.getString("OrganId"));
                
            }
            
        } catch (SQLException e) {
        	e.printStackTrace();
            throw new Exception();
        } finally {
        	db.freeConnection();
        }
        
        return elecInfo;
    }
    
    public boolean deleteElecInfo(String userId) 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, userId);
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception();
        } finally {
        	db.freeConnection();
        }
        
        return flag;
    }
    
    public ArrayList searchElecInfos(ElecInfoTO 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);
        
        
        //System.out.print(search.getBuyDep());
        
        
        if (search.getBuyDep() != null) {
            criteriaSql.append("OrganId in ( Select organid from organ Connect by prior organid=suporganid Start with organid = " +
                    DBUtil.fixSqlFieldValue(search.getBuyDep()) + " ) AND ");
        }
        
        
        
        if (search.getUserId() != null) {
            criteriaSql.append("UserId LIKE '%" +
                    DBUtil.fixSqlFieldValue(search.getUserId()) + "%' AND ");
        }
        if (search.getUserName() != null) {
            criteriaSql.append("UserName LIKE '%" +
                    DBUtil.fixSqlFieldValue(search.getUserName()) + "%' AND ");
        }
        if (search.getUsedBy() != null) {
            criteriaSql.append("UsedBy LIKE '%" + DBUtil.fixSqlFieldValue(search.getUsedBy()) + "%'");
        }
        
        //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 UserName");
        
        try {
            connection = db.conn;
            statement = connection.createStatement();
            rs = statement.executeQuery(criteriaSql.toString());
            
            
            //System.out.println(criteriaSql.toString());
            
            
            while (rs.next()) {
            	ElecInfoTO elecInfo = new ElecInfoTO();
            	elecInfo.setUserId(rs.getString("UserId"));
            	elecInfo.setUserName(rs.getString("UserName"));
            	elecInfo.setElecType(rs.getString("ElecType"));
            	elecInfo.setUsedBy(rs.getString("UsedBy"));
            	elecInfo.setAddr(rs.getString("Addr"));
            	elecInfo.setDepa(rs.getString("Depa"));
                elecInfo.setElecGre(rs.getString("ElecGre"));
                elecInfo.setRunCap(rs.getString("RunCap"));
                elecInfo.setRemark(rs.getString("Remark"));
                elecInfo.setBuyDep(rs.getString("OrganId"));
                              

                arrayList.add(elecInfo);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception();
        } finally {
        	db.freeConnection();
        }
        
        return arrayList;
    }
}