房地产管理系统

package cn.sxt.fdc.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.Date; import java.util.List; import cn.sxt.fdc.dao.CustomerDao; import cn.sxt.fdc.entity.Customer_Care; import cn.sxt.fdc.entity.Customer_Condition; import cn.sxt.fdc.entity.Customer_Info; import cn.sxt.fdc.entity.Customer_Source; import cn.sxt.fdc.entity.Customer_Type; import cn.sxt.fdc.entity.User_Info; import cn.sxt.fdc.util.BjsxtJdbc; import cn.sxt.fdc.util.JdbcUtil; import cn.sxt.fdc.util.SxtPageUtil; public class CustomerDaoImpl implements CustomerDao { Connection conn=null; PreparedStatement pst=null; ResultSet re=null; //查询客户信息 @Override public List selectCustomerDao(String selectType,String input,SxtPageUtil sxtPageUtil) { Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; List cusList=new ArrayList(); try { conn=BjsxtJdbc.getConnection(); StringBuilder sql=new StringBuilder("select i.*,c.condition_name,s.source_name,t.type_name,u.user_name from customer_info i " +"join customer_condition c on i.condition_id=c.condition_id " +"join customer_source s on i.source_id=s.source_id " +"join customer_type t on i.type_id=t.type_id " +"left join user_info u on i.user_id=u.user_id "); if((selectType!=null&&!"".equals(selectType))&&(input!=null&&!"".equals(input))){ if(selectType.equals("customer_name")){ sql.append("where customer_name = ? limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize() ); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); pstmt.setString(1, input); }else if(selectType.equals("condition_name")){ sql.append("where condition_name = ? limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize() ); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); pstmt.setString(1,input); }else if(selectType.equals("source_name")){ sql.append("where source_name =? limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize()); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); pstmt.setString(1, input); }else if(selectType.equals("type_name")){ sql.append("where type_name =? limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize()); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); pstmt.setString(1, input); }else if(selectType.equals("user_name")){ sql.append("where user_name =? limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize()); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); pstmt.setString(1, input); }else{ sql.append("where customer_company =? limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize()); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); pstmt.setString(1, input); } // pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); // pstmt.setString(1, "%"+input+"%"); }else if((selectType==null||"".equals(selectType))&&(input==null||"".equals(input))){ sql.append(" limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize()); pstmt=BjsxtJdbc.getPreparedStatement(conn, sql); }else{ return cusList; } rs=pstmt.executeQuery(); while(rs.next()){ int id = rs.getInt("customer_id"); // int cid = rs.getInt("condition_id"); // int sid = rs.getInt("source_id"); // int uid = rs.getInt("user_id"); // int tid=rs.getInt("type_id"); String name = rs.getString("customer_name"); Date date = rs.getDate("customer_addtime"); String company = rs.getString("customer_company"); String cname = rs.getString("condition_name"); String sname = rs.getString("source_name"); String tname = rs.getString("type_name"); String cremark = rs.getString("customer_remark"); String sex = rs.getString("customer_sex"); String mobile = rs.getString("customer_mobile"); String qq = rs.getString("customer_qq"); String job = rs.getString("customer_job"); String email = rs.getString("customer_email"); String uname=rs.getString("user_name"); List list=new ArrayList(); list.add(id); list.add(name); list.add(date); list.add(company); list.add(cname); list.add(sname); list.add(tname); list.add(cremark); list.add(sex); list.add(mobile); list.add(qq); list.add(job); list.add(email); list.add(uname); cusList.add(list); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return cusList; } //查询客户类型 @Override public void selectCustomerType(SxtPageUtil<Customer_Type> sxtPageUtil) { List<Customer_Type> customerTypeList = new ArrayList<>(); try { //创建连接 conn = JdbcUtil.getConnection(); StringBuilder sql = new StringBuilder("select * from customer_type where 1=1 "); sql.append(" limit "+ sxtPageUtil.getStart() +","+ sxtPageUtil.getSize()); System.out.println("CustomerDaoImpl.selectCustomerType()"+sql); pst =conn.prepareStatement(sql.toString()); //发送sql获取结果集 re= pst.executeQuery(); //处理结果 while(re.next()){ int id = re.getInt("type_id"); String uname = re.getString("type_name"); String used = re.getString("is_used"); //创建对象 Customer_Type customerType = new Customer_Type(id, uname, used); //把对象存放到集合中 customerTypeList.add(customerType); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JdbcUtil.closeAll(re, pst, conn); } sxtPageUtil.setList(customerTypeList); } //模糊查询 @Override public void likeSelectCustomerType(String name,SxtPageUtil<Customer_Type> sxtPageUtil) { List<Customer_Type> customerTypeList = new ArrayList<>(); try { //创建连接 conn = JdbcUtil.getConnection(); String sql="select * from customer_type "; sql+="where type_name like '%"+name+"%' limit "+ sxtPageUtil.getStart() +","+sxtPageUtil.getSize(); System.out.println("CustomerDaoImpl.selectCustomerType()"+sql); pst =conn.prepareStatement(sql.toString()); //发送sql获取结果集 re= pst.executeQuery(); //处理结果 while(re.next()){ int id = re.getInt("type_id"); String uname = re.getString("type_name"); String used = re.getString("is_used"); //创建对象 Customer_Type customerType = new Customer_Type(id, uname, used); //把对象存放到集合中 customerTypeList.add(customerType); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JdbcUtil.closeAll(re, pst, conn); } sxtPageUtil.setList(customerTypeList); } //添加客户类型 @Override public int addCustomer_Type(Customer_Type c) { int n=0; try { conn=JdbcUtil.getConnection(); String sql="insert into customer_type values(?,?)"; pst=conn.prepareStatement(sql.toString()); Object []objs={c.getType_name(),c.getIs_used()}; for (int i = 0; i < objs.length; i++) { pst.setObject(i+1, objs[i]); } n=pst.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JdbcUtil.closeAll(re, pst, conn); } return n; } //查询客户状态信息 @Override public List<Customer_Condition> selectConditionDao() { Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; List<Customer_Condition> clist=new ArrayList<Customer_Condition>(); try { conn=BjsxtJdbc.getConnection(); String sql="select * from customer_condition"; pstmt=BjsxtJdbc.getPreparedStateme