supplydao.java

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

package imis_mate.DAO;

import imis_mate.bean.MaterialBean;
import imis_mate.bean.SupplyBean;

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

public class SupplyDAO {
	//sql语句
	private static final String SEARCH_MATERIAL_SQL = "" +
		"SELECT SupplyNo,SupplyName,Tele,Addr,AttPerson,Acccount,Bank,Remark " +
		"FROM Supply " +
		"WHERE ";

	public ArrayList<SupplyBean> searchSupply(SupplyBean supply) throws Exception {
		// 按条件查询供应商信息
		ArrayList<SupplyBean> array = new ArrayList<SupplyBean>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		//Build the search criterias
		StringBuffer sql = new StringBuffer(512);
		sql.append(SEARCH_MATERIAL_SQL);
		if(supply.getSupplyName() != null && supply.getSupplyName().trim().equals("") != true) {
			sql.append("SupplyName LIKE '%" +
					DBUtil.fixSqlFieldValue(supply.getSupplyName()) + "%' AND ");
		}
		if(supply.getAttPerson() != null && supply.getAttPerson().trim().equals("") != true) {
			sql.append("AttPerson LIKE '%" +
					DBUtil.fixSqlFieldValue(supply.getAttPerson()) + "%' AND ");
		}		
		if(supply.getTele() != null && supply.getTele().trim().equals("") != true) {
			sql.append("Tele LIKE '%" +
					DBUtil.fixSqlFieldValue(supply.getTele()) + "%' AND ");
		}		
		if(supply.getAddr() != null && supply.getAddr().trim().equals("") != true) {
			sql.append("Addr LIKE '%" +
					DBUtil.fixSqlFieldValue(supply.getAddr()) + "%'");
		}
		
		//Remove unused 'AND' & 'WHERE'
		if(sql.substring(sql.length()-5).equals(" AND ")) {
			sql.delete(sql.length()-5, sql.length()-1);
		}
		if(sql.substring(sql.length()-7).equals(" WHERE ")) {
			sql.delete(sql.length()-7, sql.length()-1);
		}
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			
			while(rs.next()) {
				SupplyBean supply2 = new SupplyBean();
				supply2.setSupplyNo(rs.getInt("supplyNo"));
				supply2.setSupplyName(rs.getString("supplyName"));
				supply2.setTele(rs.getString("tele"));
				supply2.setAddr(rs.getString("addr"));
				supply2.setAttPerson(rs.getString("attPerson"));
				supply2.setAcccount(rs.getString("acccount"));
				supply2.setBank(rs.getString("bank"));
				supply2.setRemark(rs.getString("remark"));
				array.add(supply2);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(stmt);
			DBConnection.close(conn);
		}
		return array;	
	}
	
	//方法
	
}