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

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;

    }

}

package com.atguigu.dao;

import com.atguigu.bean.Contact;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import org.springframework.stereotype.Repository;

import java.util.HashMap;

import java.util.List;

@Repository

public class ContactDAO {

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Autowired

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<Contact> getContacts(){

        String sql = “SELECT id, telephone, name FROM tb_contacts;”;

        BeanPropertyRowMapper<Contact> contactBeanPropertyRowMapper = new BeanPropertyRowMapper<>(Contact.class);

        List<Contact> contactList = jdbcTemplate.query(sql, contactBeanPropertyRowMapper);

        return contactList;

    }

    public List<Contact> getContactWithId(HashMap<String, String> hashMap){

        String sql = “SELECT id, telephone, name FROM tb_contacts WHERE id = :id;”;

        BeanPropertyRowMapper<Contact> contactBeanPropertyRowMapper = new BeanPropertyRowMapper<>(Contact.class);

        List<Contact> contactList = namedParameterJdbcTemplate.query(sql, hashMap, contactBeanPropertyRowMapper);

        return contactList;

    }

}