materialtypedao.java

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

package imis_mate.DAO;

import imis_mate.bean.MaterialTypeBean;
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 cmis.common.Encoding;

public class MaterialTypeDAO {
	//SQL语句
	private static final String INSERT_MATERIALTYPE_SQL =
		"INSERT INTO " +
		"Material_Type(MaterialTypeNo,MaterialTypeName,MaterialPaTypeNo,MaUnit,InMin,InMax,ReMark,MaTERIALSQ)" +
		"VALUES (?,?,?,?,?,?,?,MATERIALSQ.nextval)";
	private static final String UPDATE_MATERIALTYPE_SQL =
		"UPDATE Material_Type " +
		"SET materialTypeName = ?,materialPaTypeNo = ?,maUnit = ?,inMin = ?,inMax = ?,ReMark = ? " +
		"WHERE materialTypeNo = ?";
	private static final String DELETE_MATERIALTYPE_SQL = 
		"DELETE FROM Material_Type " +
		"WHERE MaterialTypeNo = ?";
	private static final String SELECT_MATERIALTYPE_SQL = 
		"SELECT MaterialTypeNo,MaterialTypeName,MaterialPaTypeNo,MaUnit,InMin,InMax,ReMark,materialSq " +
		"FROM Material_Type " +
		"WHERE MaterialTypeNo = ?";
	private static final String SELECT_MATERIALPATYPE_SQL = 
		"SELECT MaterialTypeNo " +
		"FROM Material_Type " +
		"WHERE MaterialPaTypeNo = ?";
	private static final String SEARCH_MATERIALTYPE_SQL = 
		"SELECT MaterialTypeNo,MaterialTypeName,MaterialPaTypeNo,MaUnit,InMin,InMax,ReMark " +
		"FROM Material_Type " +
		"WHERE ";
	private static final String SELECT_MATERIALSQ_SQL = "" +
			"SELECT MaterialSQ " +
			"FROM Material_Type " +
			"WHERE MaterialTypeNo = ? ";
	//方法
	public boolean insertMaterialType(MaterialTypeBean mateType) throws Exception {
		//插入物品类型
			boolean flag = false;
			Connection connection = null;
			PreparedStatement pStatement = null;
			try {
				connection = DBConnection.getConnection();
				pStatement = connection.prepareStatement(INSERT_MATERIALTYPE_SQL);
				
				pStatement.setString(1, mateType.getMaterialTypeNo());
				pStatement.setString(2, mateType.getMaterialTypeName());
				pStatement.setString(3, mateType.getMaterialPaTypeNo());
				pStatement.setString(4, mateType.getMaUnit());
				pStatement.setInt(5, mateType.getInMin());
				pStatement.setInt(6, mateType.getInMax());
				pStatement.setString(7, mateType.getReMark());
				
				int i = pStatement.executeUpdate();
				flag = i > 0 ? true : false;
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception();
			} finally {
				DBConnection.close(pStatement);
				DBConnection.close(connection);
			}
			return flag;
	}
	
	public boolean updateMaterialType(MaterialTypeBean mateType) throws Exception {
		//更新物品类型
		
		boolean flag = false;
		Connection connection = null;
		PreparedStatement pStatement = null;
		
		try {
			connection = DBConnection.getConnection();
			
			pStatement = connection.prepareStatement(UPDATE_MATERIALTYPE_SQL);
			
			pStatement.setString(1, mateType.getMaterialTypeName());
			pStatement.setString(2, mateType.getMaterialPaTypeNo());
			pStatement.setString(3, mateType.getMaUnit());
			pStatement.setInt(4, mateType.getInMin());
			pStatement.setInt(5, mateType.getInMax());
			pStatement.setString(6, mateType.getReMark());
			pStatement.setString(7, mateType.getMaterialTypeNo());

			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		
		return flag;
	}
	public boolean deleteMaterialType(String materialTypeNo) throws Exception {
		//删除物品类型
		
		boolean flag = false;
		Connection connection = null;
		PreparedStatement pStatement = null;
		try {
			connection = DBConnection.getConnection();
			pStatement = connection.prepareStatement(DELETE_MATERIALTYPE_SQL);
			pStatement.setString(1, materialTypeNo);
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		return flag;
	}
	
	public MaterialTypeBean selectMaterialType(String materialTypeNo) throws Exception {
		//查询物品类型
		
		Connection connection = null;
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		MaterialTypeBean materialType = null;
		
		try {
			connection = DBConnection.getConnection();
			pStatement = connection.prepareStatement(SELECT_MATERIALTYPE_SQL);
			pStatement.setString(1,materialTypeNo);
			rs = pStatement.executeQuery();
			if(rs.next()) {
				
				materialType = new MaterialTypeBean();
				materialType.setMaterialTypeNo(rs.getString("materialTypeNo"));
				materialType.setMaterialTypeName(rs.getString("materialTypeName"));
				materialType.setMaterialPaTypeNo(rs.getString("materialPaTypeNo"));
				materialType.setMaUnit(rs.getString("maUnit"));
				materialType.setInMin(rs.getString("inMin"));
				materialType.setInMax(rs.getString("inMax"));
				materialType.setReMark(rs.getString("reMark"));
				materialType.setMaterialSQ(rs.getInt("materialSq"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		return materialType;
	}
	public ArrayList searchMaterialType(MaterialTypeBean mateType) throws Exception {
		//按条件查询物品类型
		ArrayList array = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		//Build the search criterias
		StringBuffer sql = new StringBuffer(512);
		sql.append(SEARCH_MATERIALTYPE_SQL);
		if(mateType.getMaterialTypeName() != null) {
			sql.append("MaterialTypeName LIKE '% " +
					DBUtil.fixSqlFieldValue(mateType.getMaterialTypeName()) + "%' AND ");
		}
		if(mateType.getMaterialPaTypeNo() != null) {
			sql.append("MaterialPaTypeNo LIKE '% " +
					DBUtil.fixSqlFieldValue(mateType.getMaterialPaTypeNo()) + "%' AND ");
		}
		if(mateType.getMaUnit() != null) {
			sql.append("MaUnit LIKE '% " +
					DBUtil.fixSqlFieldValue(mateType.getMaUnit()) + "%'");
		}
		
		//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()) {
				MaterialTypeBean mate = new MaterialTypeBean();
				mate.setMaterialTypeNo(rs.getString("materialTypeNo"));
				mate.setMaterialTypeName(rs.getString("materialTypeName"));
				mate.setMaterialPaTypeNo(rs.getString("materialPaTypeNo"));
				mate.setMaUnit(rs.getString("maUnit"));
				mate.setInMin(rs.getString("inMin"));
				mate.setInMax(rs.getString("inMax"));
				mate.setReMark(rs.getString("reMark"));
				
				array.add(mate);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(stmt);
			DBConnection.close(conn);
		}
		return array;
		
	}
	public String getMaterialTypeName(String materialTypeNo) throws Exception {
		//输入物品类型编号 返回物品类型名称
		Connection connection = null;
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		String materialTypeName = null;
		
		try {
			connection = DBConnection.getConnection();
			pStatement = connection.prepareStatement(SELECT_MATERIALTYPE_SQL);
			pStatement.setString(1,materialTypeNo);
			rs = pStatement.executeQuery();
			if(rs.next()) {
				materialTypeName = rs.getString("materialTypeName");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		return materialTypeName;
	}
	public boolean selectMaterialPaTypeNo(int materialSQ) throws Exception {
		//查询该物品下 是否有子的类型
		Connection connection = null;
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		MaterialTypeBean materialType = null;
		boolean isSon = false;
		
		try {
			connection = DBConnection.getConnection();
			pStatement = connection.prepareStatement(SELECT_MATERIALPATYPE_SQL);
			pStatement.setInt(1,materialSQ);
			rs = pStatement.executeQuery();
			if(rs.next()) {
				materialType = new MaterialTypeBean();
				materialType.setMaterialTypeNo(rs.getString("materialTypeNo"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		if(materialType!=null){
			isSon = true;
		}
		return isSon;
	}
	public int getMaterialsq(String materialTypeNo) throws Exception {
		//根据物品类型编号 返回materialSQ
		Connection connection = null;
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		int materialSq = -1;
		
		try {
			connection = DBConnection.getConnection();
			pStatement = connection.prepareStatement(SELECT_MATERIALSQ_SQL);
			pStatement.setString(1,materialTypeNo);
			rs = pStatement.executeQuery();
			if(rs.next()) {
				materialSq = rs.getInt("materialSq");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		return materialSq;
	} 
	public String getMaterialSQL(String materialTypeNo) {
		//根据物品类型编号 返回materialSQ
		Connection connection = null;
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		int materialSq = -1;
		String sql = null;
		
		try {
			connection = DBConnection.getConnection();
			pStatement = connection.prepareStatement(SELECT_MATERIALSQ_SQL);
			pStatement.setString(1,materialTypeNo);
			rs = pStatement.executeQuery();
			if(rs.next()) {
				materialSq = rs.getInt("materialSq");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(pStatement);
			DBConnection.close(connection);
		}
		sql = 	"(SELECT MaterialTypeNo FROM Material_Type "+
				"connect by prior materialsq = materialpatypeno "+ 
				"start with materialpatypeno = "+materialSq+") " +
				"OR materialTypeNo='"+materialTypeNo+"'";
		return sql;
	} 
}