venderdao.java

使用JSP+Servlet+Ajax的供应商管理系统（不包含数据库部分）

package com.aowin.scm.dao;

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

import com.aowin.scm.model.Vender;
import com.aowin.scm.util.DBConnection;
import com.aowin.scm.util.Constant;

public class VenderDAO {
	DBConnection conn = new DBConnection();
	ArrayList al = new ArrayList();
	Vender vender;
	int account;
    //供应商遍历
	public int queryAll(){
		String sql = "select VenderCode from vender";
		ResultSet rs = conn.executeQuery(sql);
		try {
			rs.last();
			account = rs.getRow();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		conn.close();
		return account;
	}
	//封装
	public ArrayList test(String sql)
	{
		ResultSet rs = conn.executeQuery(sql);
		try {
			while (rs.next()) {
				vender = new Vender();
				vender.setVenderCode(rs.getString("VenderCode"));
				vender.setName(rs.getString("Name"));
				vender.setPassword(rs.getString("Password"));
				vender.setContactor(rs.getString("Contactor"));
				vender.setAddress(rs.getString("Address"));
				vender.setPostCode(rs.getString("Postcode"));
				vender.setTel(rs.getString("Tel"));
				vender.setFax(rs.getString("Fax"));
				vender.setCreateDate(rs.getString("CreateDate"));
				al.add(vender);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		conn.close();
		return al;
	}
	
	public ArrayList quarry() {
		String sql = "select * from vender limit 0,"+Constant.PAGE_SIZE;
		return test(sql);
	}
	
    //供应商添加
	public int addItems(Vender vender) {
		String sql = "INSERT INTO `vender` (`VenderCode`, `Name`,`Password`, `Contactor`, `Address`, `Postcode`, `Tel`,`Fax`, `CreateDate`) VALUES ('"
				+ vender.getVenderCode()
				+ "','"
				+ vender.getName()
				+ "','"
				+ vender.getVenderCode()
				+ "','"
				+ vender.getContactor()
				+ "','" 
				+ vender.getAddress()
				+"','"
				+vender.getPostCode()
				+"','"
				+vender.getTel()
				+"','"
				+vender.getFax()
				+"','"
				+vender.getCreateDate()
				+"'"+")";
		conn.executeUpdate(sql);
		conn.close();
		return 1;
	}
	//供应商删除
	public int delItem(String VenderCode)
	{
		String sql = "delete from vender where VenderCode='"+VenderCode+"'";
		conn.executeUpdate(sql);
		conn.close();
		return 1;
	}
	//供应商修改
	public int modify(Vender vender)
	{
		String sql = "update vender set Name='"
			+vender.getName()
		    +"',Contactor='" 
		    +vender.getContactor()
		    +"',Address='"
		    +vender.getAddress()
		    +"',Postcode='"
		    +vender.getPostCode()
		    +"',Tel='"
		    +vender.getTel()
		    +"',Fax='"
		    +vender.getFax()
		    +"',CreateDate='"
		    +vender.getCreateDate()
		    +"'where VenderCode='"
		    +vender.getVenderCode()
		    +"'";
		conn.executeUpdate(sql);
		conn.close();
		return 1;
	}
	//检验供应商编号
	public boolean checkVenderCode(String venderCode)
	{   
		boolean flag = false;
		String sql = "select VenderCode from vender where VenderCode = '"+venderCode+"'";
		ResultSet rs = conn.executeQuery(sql);
		try {
			while(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		conn.close();
		return flag;
		
	}
	//查询供应商编号和供应商名称
	public ArrayList quallyVenderCode()
	{
		String sql = "select VenderCode,Name from vender ";
		ResultSet rs = conn.executeQuery(sql);
		
		try {
			while(rs.next()){
				vender = new Vender();
				vender.setVenderCode(rs.getString("VenderCode"));
				vender.setName(rs.getString("Name"));
				al.add(vender);
			} 
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		conn.close();
		return al;
	}
	//查询供应商编号
	public ArrayList query(String venderCode)
	{
		String sql = "select * from vender where VenderCode like '%"+venderCode+"%'";
		return test(sql);
	}
	//分页
	public ArrayList Pages(int pageNo)
	{
		String sql = "select * from vender limit "+(pageNo-1)*Constant.PAGE_SIZE+" ,"+Constant.PAGE_SIZE;
		return test(sql);
	}
	

}