尚硅谷大数据技术之电信客服

2. 新建类： Contact
   package com.atguigu.bean;

public class Contact {
private Integer id;
private String telephone;
private String name;

public Contact() {
    super();
}

public Contact(int id, String telephone, String name) {
    super();
    this.id = id;
    this.telephone = telephone;
    this.name = name;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getTelephone() {
    return telephone;
}

public void setTelephone(String telephone) {
    this.telephone = telephone;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

@Override
public String toString() {
    return "Contact{" +
            "id=" + id +
            ", telephone='" + telephone + '\'' +
            ", name='" + name + '\'' +
            '}';
}

}

3. 新建类：QueryInfo
   package com.atguigu.entries;

public class QueryInfo {
private String telephone;
private String year;
private String month;
private String day;

public QueryInfo() {
    super();
}

public QueryInfo(String telephone, String year, String month, String day) {
    super();
    this.telephone = telephone;
    this.year = year;
    this.month = month;
    this.day = day;
}

public String getTelephone() {
    return telephone;
}

public void setTelephone(String telephone) {
    this.telephone = telephone;
}

public String getYear() {
    return year;
}

public void setYear(String year) {
    this.year = year;
}

public String getMonth() {
    return month;
}

public void setMonth(String month) {
    this.month = month;
}

public String getDay() {
    return day;
}

public void setDay(String day) {
    this.day = day;
}

}

4. 新建类： CallLogDAO
   package com.atguigu.dao;

import com.atguigu.bean.CallLog;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

@Repository
public class CallLogDAO {
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public List<CallLog> getCallLogList(HashMap<String, String> hashMap){
//1、电话号码 2、年 3、日
String sql = "SELECT t3.id_date_contact, t3.id_date_dimension, t3.id_contact, t3.call_sum, t3.call_duration_sum , t3.telephone, t3.name, t4.year, t4.month, t4.day FROM (SELECT t2.id_date_contact, t2.id_date_dimension, t2.id_contact, t2.call_sum, t2.call_duration_sum , t1.telephone, t1.name FROM (SELECT id, telephone, name FROM tb_contacts WHERE telephone = :telephone ) t1 INNER JOIN tb_call t2 ON t1.id = t2.id_contact ) t3 INNER JOIN (SELECT id, year, month, day FROM tb_dimension_date WHERE year = :year AND day = :day ) t4 ON t3.id_date_dimension = t4.id ORDER BY t4.year, t4.month, t4.day;";
BeanPropertyRowMapper<CallLog> contactBeanPropertyRowMapper = new BeanPropertyRowMapper<>(CallLog.class);
List<CallLog> contactList = namedParameterJdbcTemplate.query(sql, hashMap, contactBeanPropertyRowMapper);
return contactList;
}

}

本教程由尚硅谷教育大数据研究院出品，如需转载请注明来源，欢迎大家关注尚硅谷公众号（atguigu）了解更多。