查看hive session

转载

mob64ca1419a401 2024-12-20 22:35:46

文章标签 查看hive session ide List 自定义 文章分类 Hive 大数据

客户信息管理的历史订单查看

1、显示客户历史订单、及显示订单明细

OrderController

@Controller
  @RequestMapping("order")
  public class OrderController {
      @Autowired
      private IOrderService orderService;
  
      @PostMapping("list")
      public void list(int cusid, HttpServletResponse response,int page,int rows) throws IOException {
          response.setContentType("text/html;charset=UTF-8");
          DatagridResult datagridResult = orderService.list(cusid,page,rows);
          response.getWriter().print(JSON.toJSONStringWithDateFormat(datagridResult, "yyyy-MM-dd HH:mm:ss"));
      }
      @PostMapping("findById")
      public void findById(int id,HttpServletResponse response) throws IOException {
          response.setContentType("text/html;charset=UTF-8");
          TCustomerOrder order = orderService.findById(id);
          response.getWriter().print(JSON.toJSONStringWithDateFormat(order, "yyyy-MM-dd HH:mm:ss"));
      }
  
  }

IOrderService

public interface IOrderService {
      //客户历史订单查询分页
      DatagridResult list(int cusid, int page, int rows);
      //显示订单明细
      TCustomerOrder findById(int id);
  
  }

OrderServiceImpl（防止有事务加上注解Transactional）

@Service
  @Transactional
  public class OrderServiceImpl implements IOrderService {
      @Autowired
      private TCustomerOrderMapper orderMapper;
  
      @Override
      public DatagridResult list(int cusid, int page, int rows) {
          PageHelper.startPage(page,rows);
  
          TCustomerOrderExample orderExample = new TCustomerOrderExample();
          orderExample.createCriteria().andCusidEqualTo(cusid);
          List<TCustomerOrder> list = orderMapper.selectByExample(orderExample);
          PageInfo<TCustomerOrder> pageInfo = new PageInfo<>(list);
  
          DatagridResult datagridResult = new DatagridResult();
          datagridResult.setTotal(pageInfo.getTotal());
          datagridResult.setRows(list);
          return datagridResult;
      }
  
      @Override
      public TCustomerOrder findById(int id) {
          //除了这个也可以按最简单的
  //        return orderMapper.selectByPrimaryKey(id);
          TCustomerOrderExample orderExample = new TCustomerOrderExample();
          orderExample.createCriteria().andIdEqualTo(id);
          List<TCustomerOrder> tCustomerOrders = orderMapper.selectByExample(orderExample);
          return tCustomerOrders.get(0);
      }
  
  }

2、显示总金额（自定义mapper）+订购详情

OrderDetailsController（没有日期直接RestController）

@RestController
  @RequestMapping("orderDetails")
  public class OrderDetailsController {
      @Autowired
      private IOrderDetailsService orderDetailsService;
      @PostMapping("getTotalPrice")
      public Map getTotalPrice(int orderId){
          Map map = new HashMap();
          int totalMoney = orderDetailsService.getTotalPrice(orderId);
          map.put("totalMoney",totalMoney);
          return map;
      }
      @PostMapping("list")
      public DatagridResult list(int orderId, int page, int rows){
          return orderDetailsService.selectOrderDetilsList(orderId,page,rows);
      }
  }

IOrderDetailsService

public interface IOrderDetailsService {
      //订单明细总金额
      int getTotalPrice(int orderId);
      //显示订购详情
      DatagridResult selectOrderDetilsList(int orderId, int page, int rows);
  }

OrderDetailsServiceImpl（自定义mapper需要在TOrderDetailsMapper中自定义方法，下方代码）

@Service
  @Transactional
  public class OrderDetailsServiceImpl implements IOrderDetailsService {
      @Autowired
      private TOrderDetailsMapper orderDetailsMapper;
  
      @Override
      public int getTotalPrice(int orderId) {
          //select sum(sum) from t_order_details where orderId = ?
          //还是用的自定义mapper
          return orderDetailsMapper.getTotalPriceByOrderId(orderId);
      }
  
      @Override
      public DatagridResult selectOrderDetilsList(int orderId, int page, int rows) {
          PageHelper.startPage(page,rows);
          TOrderDetailsExample orderDetailsExample = new TOrderDetailsExample();
          orderDetailsExample.createCriteria().andOrderidEqualTo(orderId);
          List<TOrderDetails> list = orderDetailsMapper.selectByExample(orderDetailsExample);
          PageInfo<TOrderDetails> pageInfo = new PageInfo<>(list);
  
          DatagridResult datagridResult = new DatagridResult();
          datagridResult.setRows(list);
          datagridResult.setTotal(pageInfo.getTotal());
          return datagridResult;
      }
  }

TOrderDetailsMapper中新增

public interface TOrderDetailsMapper {
      //查询订单总价
      int getTotalPriceByOrderId(int orderId);
  }

TOrderDetailsMapper.xml中写自定义SQL语句

最下方（#{orderId}里面的参数随便写不影响）

<select id="getTotalPriceByOrderId" parameterType="int" resultType="int">
        select sum(sum) from t_order_details where orderId = #{orderId}
  </select>

至此，历史订单查看完成

客户流失管理增删改查(增需要在mapper添加查询组件)

1、显示客户流失管理数据+显示客户流失基本信息

CustomerLossController

@Controller
 @RequestMapping("customerLoss")
 public class CustomerLossController {
     @Autowired
     private ICustomerLossService lossService;
     @PostMapping("list")
     public void list(HttpServletResponse response,TCustomerLoss customerLoss, int page, int rows) throws IOException {
         response.setContentType("text/html;charset=UTF-8");
         DatagridResult datagridResult = lossService.selectLossList(customerLoss,page,rows);
         String jsonString = JSON.toJSONStringWithDateFormat(datagridResult, "yyyy-MM-dd HH:mm:ss");
         response.getWriter().print(jsonString);
     }
     @PostMapping("findById")
     public void findById(HttpServletResponse response,int id) throws IOException {
         response.setContentType("text/html;charset=UTF-8");
         TCustomerLoss loss = lossService.findById(id);
         String jsonString = JSON.toJSONStringWithDateFormat(loss, "yyyy-MM-dd HH:mm:ss");
         response.getWriter().print(jsonString);
     }
 }

ICustomerLossService

public interface ICustomerLossService {
     //显示客户流失管理数据
     DatagridResult selectLossList(TCustomerLoss customerLoss, int page, int rows);
     //显示客户流失基本信息
     TCustomerLoss findById(int id);
 }

CustomerLossServiceImpl

@Service
 public class CustomerLossServiceImpl implements ICustomerLossService {
     @Autowired
     private TCustomerLossMapper lossMapper;
 
 
     @Override
     public DatagridResult selectLossList(TCustomerLoss customerLoss, int page, int rows) {
         PageHelper.startPage(page,rows);
         TCustomerLossExample lossExample = new TCustomerLossExample();
         TCustomerLossExample.Criteria criteria = lossExample.createCriteria();
         if (StringUtils.isNotBlank(customerLoss.getCusname())){
             criteria.andCusnameLike("%"+customerLoss.getCusname()+"%");
         }
         if (StringUtils.isNotBlank(customerLoss.getCusmanager())){
             criteria.andCusmanagerEqualTo(customerLoss.getCusmanager());
         }
         if (customerLoss.getState()!=null){
             criteria.andStateEqualTo(customerLoss.getState());
         }
         List<TCustomerLoss> lossList = lossMapper.selectByExample(lossExample);
         PageInfo<TCustomerLoss> pageInfo = new PageInfo<>(lossList);
         DatagridResult datagridResult = new DatagridResult();
         datagridResult.setTotal(pageInfo.getTotal());
         datagridResult.setRows(pageInfo.getList());
         return datagridResult;
     }
 
     @Override
     public TCustomerLoss findById(int id) {
         return lossMapper.selectByPrimaryKey(id);
     }
 }

2、客户流失暂缓措施管理增删改查

CustomerReprieveController

@RestController
 @RequestMapping("customerReprieve")
 public class CustomerReprieveController {
     @Autowired
     private ICustomerReprieveService reprieveService;
     @PostMapping("list")
     public DatagridResult list(int lossid){
         return reprieveService.selectReprieveByLossid(lossid);
     }
     @PostMapping("save")
     public TCustomerReprieve save(TCustomerReprieve reprieve){
         try {
             reprieveService.insertReprieve(reprieve);
             //查询主键去查
             return reprieve;
         }catch (Exception e){
             e.printStackTrace();
             return null;
         }
     }
     @PutMapping("update")
     public TCustomerReprieve update(TCustomerReprieve reprieve){
         try {
             reprieveService.updateReprieve(reprieve);
             return reprieve;
         }catch (Exception e){
             e.printStackTrace();
             return null;
         }
     }
     @DeleteMapping("delete")
     public Map delete(int id){
         Map map = new HashMap();
         try {
             reprieveService.deleteReprieve(id);
             map.put("success",true);
         }catch (Exception e){
             e.printStackTrace();
             map.put("success",false);
         }
         return map;
     }
 }

ICustomerReprieveService

public interface ICustomerReprieveService {
     //显示客户流失暂缓措施管理
     DatagridResult selectReprieveByLossid(int lossid);
     //保存暂缓措施
     void insertReprieve(TCustomerReprieve reprieve);
     //修改暂缓措施
     void updateReprieve(TCustomerReprieve reprieve);
     //删除暂缓措施
     void deleteReprieve(int id);
 }

CustomerReprieveServiceImpl

@Service
 public class CustomerReprieveServiceImpl implements ICustomerReprieveService {
     @Autowired
     private TCustomerReprieveMapper reprieveMapper;
 
     @Override
     public DatagridResult selectReprieveByLossid(int lossid) {
         TCustomerReprieveExample reprieveExample = new TCustomerReprieveExample();
         reprieveExample.createCriteria().andLossidEqualTo(lossid);
         List<TCustomerReprieve> list = reprieveMapper.selectByExample(reprieveExample);
         DatagridResult datagridResult = new DatagridResult();
         datagridResult.setRows(list);
         return datagridResult;
     }
 
     @Override
     public void insertReprieve(TCustomerReprieve reprieve) {
         //写了查询主键
         reprieveMapper.insertSelective(reprieve);
     }
 
     @Override
     public void updateReprieve(TCustomerReprieve reprieve) {
         reprieveMapper.updateByPrimaryKeySelective(reprieve);
     }
 
     @Override
     public void deleteReprieve(int id) {
         reprieveMapper.deleteByPrimaryKey(id);
     }
 }

注意：新增写了查询主键

TCustomerReprieveMapper.xml里

insert方法中 id为insertSelective里新增查询主键方法

<insert id="insertSelective" parameterType="com.galaxy.crm.bean.TCustomerReprieve" >
   <selectKey resultType="int" order="AFTER" keyProperty="id">
     select LAST_INSERT_ID()
   </selectKey>
   insert into t_customer_reprieve
   <trim prefix="(" suffix=")" suffixOverrides="," >

2-4行为新增代码

确认流失及@Json注解的使用

确认流失代码

CustomerLossController

@PostMapping("confirmLoss")
 @ResponseBody
 public Map confirmLoss(TCustomerLoss customerLoss){
     Map map = new HashMap();
     try {
         lossService.updateLoss(customerLoss);
         map.put("success",true);
     }catch (Exception e){
         e.printStackTrace();
         map.put("success",false);
     }
     return map;
 }

ICustomerLossService

public interface ICustomerLossService {
     //显示客户流失管理数据
     DatagridResult selectLossList(TCustomerLoss customerLoss, int page, int rows);
     //显示客户流失基本信息
     TCustomerLoss findById(int id);
     //更新客户流失状态
     void updateLoss(TCustomerLoss customerLoss);
 }

CustomerLossServiceImpl

@Override
 public void updateLoss(TCustomerLoss customerLoss) {
     //确认流失
     customerLoss.setState(1);
     //确认流失时间
     customerLoss.setConfirmlosstime(new Date());
     //根据条件选择性的更新
     lossMapper.updateByPrimaryKeySelective(customerLoss);
 }

@Json注解使用

1、修改之前写流失自己手动转json的格式：代码如下

之前的：（显示客户流失管理界面list代码）

CustomerLossController

@Controller
 @RequestMapping("customerLoss")
 public class CustomerLossController {
     @Autowired
     private ICustomerLossService lossService;
     @PostMapping("list")
     public void list(HttpServletResponse response,TCustomerLoss customerLoss, int page, int rows) throws IOException {
         response.setContentType("text/html;charset=UTF-8");
         DatagridResult datagridResult = lossService.selectLossList(customerLoss,page,rows);
         String jsonString = JSON.toJSONStringWithDateFormat(datagridResult, "yyyy-MM-dd HH:mm:ss");
         response.getWriter().print(jsonString);
     }

修改后：

CustomerLossController

@RestController
 @RequestMapping("customerLoss")
 public class CustomerLossController {
     @Autowired
     private ICustomerLossService lossService;
     @PostMapping("list")
     public DatagridResult list(TCustomerLoss customerLoss, int page, int rows){
         return lossService.selectLossList(customerLoss,page,rows);
     }

TCustomerLoss类中加注解使springMVC自动转json日期格式

TCustomerLoss

//使用springMVC自动转json日期格式
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
 private Date lastordertime;
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
 private Date confirmlosstime;

整个CustomerLossController都可以改

最后控制台代码（显示客户流失管理数据，显示客户流失基本信息，更新客户流失状态）

CustomerLossController

@RestController
 @RequestMapping("customerLoss")
 public class CustomerLossController {
     @Autowired
     private ICustomerLossService lossService;
     @PostMapping("list")
     public DatagridResult list(TCustomerLoss customerLoss, int page, int rows){
         return lossService.selectLossList(customerLoss,page,rows);
     }
     @PostMapping("findById")
     public TCustomerLoss findById(int id){
         return lossService.findById(id);
     }
     @PostMapping("confirmLoss")
     public Map confirmLoss(TCustomerLoss customerLoss){
         Map map = new HashMap();
         try {
             lossService.updateLoss(customerLoss);
             map.put("success",true);
         }catch (Exception e){
             e.printStackTrace();
             map.put("success",false);
         }
         return map;
     }
 }

注意：@Controller注解的话，使用自定义json需要加命名空间@ResponceBody

@RestController 不用加 ResponceBody

service层不变

之前开发的DevPlanController也可以改（这里就不写了，方法一样）

TCusDevPlan

//前台string后台date
 @DateTimeFormat(pattern = "yyyy-MM-dd")
 //后台date前台string
 @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
 private Date plandate;

DevPlanController

@RestController
 @RequestMapping("cusDevPlan")
 public class DevPlanController {
     @Autowired
     private IDevPlanService devPlanService;
     //restFul处理
     @GetMapping("{SaleChanceId}")
     public DatagridResult list(@PathVariable int SaleChanceId){
         //jackson:Spring MVC默认的  gson:谷歌的   jsonlib:民间大神的    fastjson:阿里的
         return devPlanService.selectPlanSaleChanceId(SaleChanceId);
     }
     @PostMapping("{SaleChanceId}")
     public TCusDevPlan save(@PathVariable int SaleChanceId, TCusDevPlan cusDevPlan){
         cusDevPlan.setSalechanceid(SaleChanceId);
         //插入数据库
         try {
             //service中使用查询主键将插入后的id直接赋值给参数
             devPlanService.saveDevPlan(cusDevPlan);
             return cusDevPlan;
         }catch (Exception e){
             e.printStackTrace();
             return null;
         }
     }
     @DeleteMapping("")
     public Map deleteRow(int id){
         Map map = new HashMap();
         try {
             devPlanService.deleteDevPlanById(id);
             map.put("success",true);
         }catch (Exception e){
             e.printStackTrace();
             map.put("success",false);
         }
         return map;
     }
     @PutMapping("")
     public TCusDevPlan updateDevPlan(TCusDevPlan devPlan){
         try {
             devPlanService.updateDevPlan(devPlan);
             return devPlan;
         }catch (Exception e){
             e.printStackTrace();
             return null;
         }
     }
 }

想一下如果都用这个注解转换json，那之前讲的fastJson是不是没用了？(有用，死循环来回转，内存溢出就报错了，这时候用fastjson的另一个注解)

这个注解加到类的属性上，前台会忽略这个属性不显示

//转换json时忽略注解属性
 @JsonIgnore
 private String cusname;

最后一个功能！用@Schedule注解完成定时器（比timmer强大，但是没有quartz全面）

Timmer 太简单了只能通过时间间隔去触发，因为这边想让每天凌晨两点去触发，不合适
quartz 体量太大了，配置起来麻烦，所以使用spring提供的3中的注解
@Scheduled

半年没下单的客户就变成流失客户（往customer_loss插入数据，state=0）
 1.定时任务
     1.1 Timmer
     1.2 quartz
     1.3 spring提供了 @Scheduled(cron="表达式")  二合一  比timmer强大，但是没有quartz全面
         1.创建自己的任务（建类@Component 建方法@Scheduled注解）
         2.在spring/springmvc配置文件中扫描到任务所在的包，并
         <task:annotation-driven/> 开启注解功能
         3.测试
 2.如何去检索
     1.检索半年没下单的客户
     select cus.khno cusNo,cus.name cusName,cus.cusManager,cusId,MAX(orderDate) lastOrderTime from t_customer_order co
     INNER JOIN t_customer cus
     ON co.cusId = cus.id and  cus.state != 0
     GROUP BY cusId
     HAVING ADDDATE(lastOrderTime,INTERVAL 6 MONTH) < NOW()
     2.修改customer表中的state属性0
     3.将检索的数据插入到customerloss表

开整：

新增一个与 crm同级的包

package com.galaxy.crm.task;
 
 import org.springframework.scheduling.annotation.Scheduled;
 import org.springframework.stereotype.Component;
 
 @Component
 public class MyTask {
     //每秒触发
     @Scheduled(cron = "* * * * * ?")
     public void confirmLoss(){
         //检索流失客户
         System.out.println("确认流失中......");
     }
 }

在springMVC里使用spring的定时任务非常的简单，如下：

1、applicationContext.xml中让spring通过@Component扫到自己建的MyTask

<!-- 开启自动扫描注解 扫@Service-->
 <context:component-scan base-package="com.galaxy.crm.service,com.galaxy.crm.task"></context:component-scan>

让扫两个，别图省事

2、在xml里加入task的命名空间

xmlns:task="http://www.springframework.org/schema/task"
http://www.springframework.org/schema/task
http://www.springframework.org/schema/task/spring-task-4.1.xsd

3、开启@Scheduled使用（3-4行）

<!-- 开启自动扫描注解 扫@Service-->
 <context:component-scan base-package="com.galaxy.crm.service,com.galaxy.crm.task"></context:component-scan>
 <!--开启@Scheduled使用-->
 <task:annotation-driven/>

讲了半天：sql如下

14-18最后的sql

#1.每个客户最后一次下单时间
 SELECT cusId,MAX(orderDate) FROM t_customer_order
 GROUP BY cusId
 #2.查询半年没下单的客户编号
 SELECT cusId,MAX(orderDate)lastOrderTime FROM t_customer_order
 GROUP BY cusId
 HAVING ADDDATE(lastOrderTime,INTERVAL 6 MONTH)< NOW()
 #2.2已子查询的方式实现(了解即可效率低，结果一样)
 SELECT * FROM (SELECT cusId,MAX(orderDate)lastOrderTime FROM t_customer_order
 GROUP BY cusId)result 
 WHERE ADDDATE(result.lastOrderTime,INTERVAL 6 MONTH) <NOW()
 
 #最后的sql语句(传t_customer_loss对象 t_customer里的起别名与loss名相同)
 SELECT cus.khno cusNo,cus.name cusName,cus.cusManager,cusId id,MAX(orderDate)lastOrderTime FROM t_customer_order ord
 INNER JOIN t_customer cus
 ON ord.cusId = cus.id AND cus.state != 0
 GROUP BY cusId
 HAVING ADDDATE(lastOrderTime,INTERVAL 6 MONTH) < NOW()

先修改几个之前的bug

CustomerController里新增/保存方法中state设成1，意思是创建正常state=1的客户

下边加了第9行，一行代码

@PostMapping("")
 public Map createCus(TCustomer customer){
     //赋值客户编号
     Date date = new Date();
     SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
     String format = sdf.format(date);
     customer.setKhno("KH"+format);
     //正常客户
     customer.setState(1);
     Map map = new HashMap<>();
     try {
         customerService.createCus(customer);
         map.put("success",true);
     }catch (Exception e){
         e.printStackTrace();
         map.put("success",false);
     }
     return map;
 }

显示界面（新增之前的查询界面）

CustomerServiceImpl新增一句代码，只显示正常客户，因为添加了定时器（新增第12-13行）

@Service
 public class CustomerServiceImpl implements ICustomerService {
     @Autowired
     private TCustomerMapper customerMapper;
 
     @Override
     public DatagridResult listByPage(int page, int rows,TCustomer customer) {
         //分页查询
         //1.设置分页条件
         PageHelper.startPage(page,rows);
         //2.查询数据库 list
         //只查正常的用户(一条并不能满足查询state=0)
         customer.setState(1);
         //根据前台输入的条件创建条件对象
         TCustomerExample example = new TCustomerExample();
         TCustomerExample.Criteria criteria = example.createCriteria();
         if (StringUtils.isNotBlank(customer.getKhno())){
             criteria.andKhnoEqualTo(customer.getKhno());
         }
         if (StringUtils.isNotBlank(customer.getName())){
             criteria.andNameLike("%"+customer.getName()+"%");
         }
         //只查正常的用户,忽略流失客户
         if (customer.getState()!=0){
             criteria.andStateEqualTo(customer.getState());
         }
         List<TCustomer> customerList = customerMapper.selectByExample(example);
         //3.通过list创建PageInfo对象
         PageInfo<TCustomer> pageInfo = new PageInfo<>(customerList);
         //4.根据PageInfo对象中的属性封装DatagridResult对象并返回
         DatagridResult datagridResult = new DatagridResult();
         datagridResult.setRows(pageInfo.getList());
         datagridResult.setTotal(pageInfo.getTotal());
         return datagridResult;
     }

接着整：配置完上边的后

task包下的MyTask类（这边写的是每天凌晨两点触发定时器检索半年没下单的客户）

@Component
 public class MyTask {
     @Autowired
     private ICustomerLossService customerLossService;
     //每天早上两点触发
     @Scheduled(cron = "0 0 2 * * ?")
     public void confirmLoss(){
         //检索流失客户
         customerLossService.selectConfirmLoss();
     }
 }

ICustomerLossService

public interface ICustomerLossService {
     //显示客户流失管理数据
     DatagridResult selectLossList(TCustomerLoss customerLoss, int page, int rows);
     //显示客户流失基本信息
     TCustomerLoss findById(int id);
     //更新客户流失状态
     void updateLoss(TCustomerLoss customerLoss);
     //检索半个月没下单客户
     void selectConfirmLoss();
 }

CustomerLossServiceImpl（注意：我们是自定义sql查询）selectConfirmLoss，点进去下方的xml文件

@Autowired
 private TCustomerMapper customerMapper;
 //检索半年没下单的用户
 @Override
 public void selectConfirmLoss() {
     //1.检索出用户 list<customerloss>
     List<TCustomerLoss> lossList = lossMapper.selectConfirmLoss();
     //2.根据id 修改customer表state=0 表示流失客户
     List<Integer> idList = new ArrayList<>();
     for (TCustomerLoss tCustomerLoss : lossList) {
         TCustomer tCustomer = new TCustomer();
         tCustomer.setId(tCustomerLoss.getId());
         tCustomer.setState(0);
         customerMapper.updateByPrimaryKeySelective(tCustomer);
         //3.将检索结果 插入customerLoss表中 state=0 表示暂缓流失
         //清空id
         tCustomerLoss.setId(null);
         tCustomerLoss.setState(0);
         lossMapper.insertSelective(tCustomerLoss);
     }
 }

TCustomerLossMapper.xml

最下方写自定义sql，上边写好的怼进来就行

<!--确认半年没下单的客户
 tcustomerLoss:别名
 cdata：转义字符
 -->
 <select id="selectConfirmLoss" resultType="tcustomerLoss">
   SELECT cus.khno cusNo,cus.name cusName,cus.cusManager,cusId id,MAX(orderDate)lastOrderTime FROM t_customer_order ord
   INNER JOIN t_customer cus
   ON ord.cusId = cus.id AND cus.state != 0
   GROUP BY cusId
   HAVING ADDDATE(lastOrderTime,INTERVAL 6 MONTH) <![CDATA[<]]> NOW()
 </select>