基于jsp-servlet的客户管理系统CRM.zip

package com.rlg.crm.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.rlg.crm.conn.UtilConnect; import com.rlg.crm.dao.CustomMessageDao; import com.rlg.crm.domain.Area; import com.rlg.crm.domain.Assort; import com.rlg.crm.domain.Customer; import com.rlg.crm.domain.CustomerRank; import com.rlg.crm.domain.Linkman; import com.rlg.crm.domain.Orders; import com.rlg.crm.servlet.CustomMessageListServlet; public class CustomMessageDaoImpl implements CustomMessageDao { @Override public void addCustomer(Customer customer) { // TODO Auto-generated method stub } public Customer getCustomerById(int id){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Customer cus = new Customer(); String sql = "select CUS_ID, CUS_ARE_ID,ARE_NAME,CUS_CK_ID,CK_NAME,CUS_NAME,CUS_NUMBER,CUS_ADDRESS,CUS_CHANGE_ORIGIN,CUS_CHANGE_DESCRIBE, "+ "CUS_SUCCESS_PROBABILITY,CUS_MANAGER,CUS_ALLOT_NAME,CUS_ALLOT_DATE,CUS_POSTAL_CODE,CUS_TELEPHONE,CUS_FASIMILE,CUS_URL,CUS_REGISTER_NUMBER,CUS_LEGAL_PERSON,"+ "CUS_REGISTER_MONEY,CUS_RMB,CUS_OPENING_BANK,CUS_BANK_ACCOUNT,CUS_CROWN_RENT,CUS_STATE_TAXES,CUS_RESUME,CUS_CREDITWORTHINESS,CUS_STATISFCING,CUS_STATE from "+ "customer c inner join area on area.ARE_ID = c.CUS_ARE_ID inner join customer_rank cr on cr.CK_ID =c.CUS_CK_ID "+ " where 1=1 and cus_id= " + id; try { conn = UtilConnect.getConn(); // 负责执行SQL语句 st = conn.prepareStatement(sql); rs = st.executeQuery(); // ResultSet是个集合，里面存的是数据库的表数据，以二维表的形式呈现， // rs.next也是一个游标，默认情况下游标是指的第一行数据之前，当第一次调用。next()的时候游标指的是第一行数据，再次调用，指的是下一行数据 while (rs.next()) { Area area = new Area(); CustomerRank cr=new CustomerRank(); cus.setCusId(rs.getInt(1)); area.setAreId(rs.getInt(2)); area.setAreName(rs.getString(3)); cus.setArea(area); cr.setCkId(rs.getInt(4)); cr.setCkName(rs.getString(5)); cus.setCustomerRank(cr); cus.setCusName(rs.getString(6)); cus.setCusNumber(rs.getString(7)); cus.setCusAddress(rs.getString(8)); cus.setCusChangeOrigin(rs.getString(9)); cus.setCusChangeDescribe(rs.getString(10)); cus.setCusSuccessProbability(rs.getString(11)); cus.setCusManager(rs.getString(12)); cus.setCusAllotName(rs.getString(13)); cus.setCusAllotDate(rs.getTimestamp(14)); cus.setCusPostalCode(rs.getString(15)); cus.setCusTelephone(rs.getString(16)); cus.setCusFasimile(rs.getString(17)); cus.setCusUrl(rs.getString(18)); cus.setCusRegisterNumber(rs.getString(19)); cus.setCusLegalPerson(rs.getString(20)); cus.setCusRegisterMoney(rs.getDouble(21)); cus.setCusRmb(rs.getDouble(22)); cus.setCusOpeningBank(rs.getString(23)); cus.setCusBankAccount(rs.getString(24)); cus.setCusCrownRent(rs.getString(25)); cus.setCusStateTaxes(rs.getString(26)); cus.setCusResume(rs.getString(27)); cus.setCusCreditworthiness(rs.getLong(28)); cus.setCusStatisfcing(rs.getLong(29)); cus.setCusState(rs.getLong(30)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { UtilConnect.close(rs); UtilConnect.close(st); UtilConnect.close(conn); } List<Linkman> linkmans =this.getLinkmanByWhere(" and lin_cus_id="+cus.getCusId()); cus.setLinkmans(linkmans); List<Assort> assorts=this.getAssortsByWhere(" and ass_cus_id="+cus.getCusId()); cus.setAssorts(assorts); List<Orders> orderses=this.getOrdersByWhere(" and ord_cust_id="+cus.getCusId()); cus.setOrderses(orderses); return cus; } @Override public List<Customer> getCustomByWhere(String where) { List<Customer> list = new ArrayList<Customer>(); Connection conn = null; PreparedStatement st = null; ResultSet rs = null; String sql = "select CUS_ID,CUS_NAME,CUS_ADDRESS,CUS_TELEPHONE,CUS_STATE from customer where 1=1 " + where; try { conn = UtilConnect.getConn(); // 负责执行SQL语句 st = conn.prepareStatement(sql); rs = st.executeQuery(); // ResultSet是个集合，里面存的是数据库的表数据，以二维表的形式呈现， // rs.next也是一个游标，默认情况下游标是指的第一行数据之前，当第一次调用。next()的时候游标指的是第一行数据，再次调用，指的是下一行数据 while (rs.next()) { Customer cus = new Customer(); Area area = new Area(); cus.setCusId(rs.getInt(1)); // area.setAreId(rs.getInt(2)); // area.setAreName(rs.getString(3)); // cus.setArea(area); cus.setCusName(rs.getString(2)); cus.setCusAddress(rs.getString(3)); cus.setCusTelephone(rs.getString(4)); cus.setCusState(rs.getLong(5)); list.add(cus); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { UtilConnect.close(rs); UtilConnect.close(st); UtilConnect.close(conn); } return list; } public List<Linkman> getLinkmanByWhere(String where) { List<Linkman> list = new ArrayList<Linkman>(); Connection conn = null; PreparedStatement st = null; ResultSet rs = null; String sql = "select * from linkman lk inner join customer on lk.LIN_CUS_ID=customer.CUS_ID where 1=1 " + where; try { conn = UtilConnect.getConn(); // 负责执行SQL语句 st = conn.prepareStatement(sql); rs = st.executeQuery(); // ResultSet是个集合，里面存的是数据库的表数据，以二维表的形式呈现， // rs.next也是一个游标，默认情况下游标是指的第一行数据之前，当第一次调用。next()的时候游标指的是第一行数据，再次调用，指的是下一行数据 while (rs.next()) { Linkman lk = new Linkman(); Customer cus=new Customer(); lk.setLinId(rs.getInt(1)); cus.setCusId(rs.getInt(2)); lk.setCustomer(cus); lk.setLinName(rs.getString(3)); lk.setLinSex(rs.getString(4)); lk.setLinTelephone(rs.getString(5)); lk.setLinPost(rs.getString(6)); lk.setLinMobile(rs.getString(7)); lk.setLinMemo(rs.getString(8)); list.add(lk); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { UtilConnect.close(rs); UtilConnect.close(st); UtilConnect.close(conn); } return list; } public List<Assort> getAssortsByWhere(String where) { List<Assort> list = new ArrayList<Assort>(); Connection conn = null; PreparedStatement st = null; ResultSet rs = null; String sql = "select * from assort ass inner join customer on ass.ASS_CUS_ID=customer.CUS_ID where 1=1 " + where; try { conn = UtilConnect.getConn(); // 负责执行SQL语句 st = conn.prepareStatement(sql); rs = st.executeQuery(); // ResultSet是个集合，里面存的是数据库的表数据，以二维表的形式呈现， // rs.next也是一个游标，默认情况下游标是指的第一行数据之前，当第一次调用。next()的时候游标指的是第一行数据，再次调用，指的是下一行数据 while (rs.next()) { Assort ass = new Assort(); Customer cus=new Customer(); ass.setAssId(rs.getInt(1)); cus.setCusId(rs.getInt(2)); ass.setCustomer(cus); ass.setAssDate(rs.getTimestamp(3)); ass.setAssPalce(rs.getString(4)); ass.setAssResume(rs.getString(5)); ass.setAssDetail(rs.getString(6)); ass.setAssMemo(rs.getString(7)); list.add(ass); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { UtilConnect.close(rs); UtilConnect.close(st); UtilConnect.close(conn); } retur