daocstcustomerdao.java

J2EE客户关系管理系统!!!!!!!!!!!!!!!!!!!!

package com.accp.DAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.accp.VO.VOCstCustomerVO;
import com.accp.DB.DBConnection;


public class DAOCstCustomerDAO {
	public List<VOCstCustomerVO> findBy(String str){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select cust_no,cust_name from cst_customer where cust_status !=3 and cust_no like '%"+str+"%'";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCstNo(rs.getString(1));
				cstcustomervo.setCustName(rs.getString(2));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;
	}
	
	public List<VOCstCustomerVO> findBy2(String str){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select cust_no,cust_name from cst_customer where cust_status !=3 and cust_name like '%"+str+"%'";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCstNo(rs.getString(1));
				cstcustomervo.setCustName(rs.getString(2));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;
	}
	
	public List<VOCstCustomerVO> findAnd1(){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select svr_cust_name from cst_service where 1=1 and svr_satisfy <3";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCustName(rs.getString(1));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;	
	}
	
	public List<VOCstCustomerVO> findAnd2(){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select svr_cust_name from cst_service where 1=1 and svr_due_id > 0 and svr_satisfy < 3 ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCustName(rs.getString(1));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;	
	}
	
	public List<VOCstCustomerVO> findAnd3(){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select svr_cust_name from cst_service where 1=1 and svr_satisfy >= 3";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCustName(rs.getString(1));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;	
	}
	
	public List<VOCstCustomerVO> findAnd4(){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select cst_customer.cust_name from orders_line,cst_customer where odd_order_id in (select odr_id from orders where cust_id=cst_customer.cust_no and orders.odr_date like '%%')group by cst_customer.cust_name";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCustName(rs.getString(1));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;	
	}
	
	public List<VOCstCustomerVO> findAnd5(){
		List ls = new ArrayList();
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select svr_cust_name from cst_service where 1=1 and svr_deal_id > 0 and svr_satisfy < 3";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				VOCstCustomerVO cstcustomervo = new VOCstCustomerVO();
				cstcustomervo.setCustName(rs.getString(1));
				ls.add(cstcustomervo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.ConnectionClose(con);
		}
		return ls;	
	}
}