首先映射生成数据库中表对应的mapper和表名相同的java文件和表名后加个Example的java文件后。再创建一个表名+service和一个页面名+controller 的java文件。
首先在Mapper中
<select id="queryComplianIndexLastMinute" parameterType="java.util.Map" resultType="java.util.Map">
select * from (
(SELECT * FROM dim_warning_index)dim right join
(SELECT * FROM complain_index_total WHERE index_time BETWEEN DATE_SUB(#{calendar},INTERVAL 10 MINUTE) AND #{calendar}
AND time_dim_id = 1)total
ON dim.index_id=total.index_id
)
WHERE dim.index_type = 2
ORDER BY dim.index_order DESC
</select>
<select id="queryComplianIndexNowMinute" parameterType="java.util.Map" resultType="java.util.Map">
select * from (
(SELECT * FROM dim_warning_index)dim right join
(SELECT * FROM complain_index_total where index_time > DATE_SUB(#{now},INTERVAL 10 MINUTE)
AND time_dim_id = 1)total
ON dim.index_id=total.index_id
)
WHERE dim.index_type = 2
ORDER BY dim.index_order DESC
</select>
Select内是SQL语句,从数据库中查出表数据parameterType="java.util.Map"表示可以传进参数
resultType="java.util.Map"表示可以传出传出数据。其类型为Map类型。
Service层中
import com.asiainfo.aikcm.complaint.entity.WarningIndexDetail;
import com.asiainfo.system.base.service.impl.IServiceImpl;
import org.springframework.stereotype.Service;
import com.asiainfo.aikcm.complaint.entity.ComplainIndexTotal;
import com.asiainfo.aikcm.complaint.entity.ComplainIndexTotalExample;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import com.asiainfo.system.base.dao.IDAO;
/**
* Created by fumw on 2017/8/10.
* 投诉指标数据表的业务层
*/
@Service
public class ComplainIndexTotalService extends IServiceImpl<ComplainIndexTotal, ComplainIndexTotalExample, Long> {
@Autowired
private IDAO<ComplainIndexTotal,ComplainIndexTotalExample,String> dao;
/**
* @Description: 获取数据库中index_order为1的指标的最大时间
* @return
*/
public List<Map<String,Object>> queryComplianIndexMax(Map<String,Object> dimTime){
Map<String,Object> map=new HashMap<String,Object>();
map.put("dimTime",dimTime.get("dimTime"));
List<Map<String,Object>> resultList = dao.selectListByMap("queryComplianIndexMinuteMax", map);
return resultList;
}
/**
* @Description: 获取数据库中时间维度为实时的上月信息
* @return
*/
public List<Map<String,Object>> queryComplianIndexLastMinute(){
Map<String,Object> map=new HashMap<String,Object>();
Map<String,Object> dimTime =new HashMap<String,Object>();
dimTime.put("dimTime",1);
List<Map<String,Object>> resultTime = queryComplianIndexMax(dimTime);
Calendar calendar = Calendar.getInstance();
calendar.setTime((java.util.Date)resultTime.get(0).get("MAX(index_time)"));
calendar.add(Calendar.MONTH, -1);
map.put("calendar",calendar.getTime());
List<Map<String,Object>> resultList = dao.selectListByMap("queryComplianIndexLastMinute", map);
return resultList;
}
/**
* @Description: 获取数据库中时间维度为实时的本月信息
* @return
*/
public List<Map<String,Object>> queryComplianIndexNowMinute(){
Map<String,Object> map=new HashMap<String,Object>();
Map<String,Object> dimTime =new HashMap<String,Object>();
dimTime.put("dimTime",1);
List<Map<String,Object>> resultTime = queryComplianIndexMax(dimTime);
Calendar calendar = Calendar.getInstance();
calendar.setTime((java.util.Date)resultTime.get(0).get("MAX(index_time)"));
map.put("now",calendar.getTime());
List<Map<String,Object>> resultList = dao.selectListByMap("queryComplianIndexNowMinute", map);
return resultList;
}
在service层中
Map<String,Object>map=new HashMap<String,Object>();
新创建一个Map类型的变量map
map.put("now",calendar.getTime());
给map赋值。
List<Map<String,Object>>resultList = dao.selectListByMap("queryComplianIndexNowMinute", map);
将map传给mapper内方法后获取查询后的数据赋值给resultList,每一条数据为一个元素。
Controller层
import com.asiainfo.aikcm.complaint.entity.*;
import com.asiainfo.aikcm.complaint.service.ComplainIndexTotalService;
import com.asiainfo.aikcm.complaint.service.DimWarningIndexService;
import com.asiainfo.aikcm.complaint.service.WarningIndexDetailService;
import com.asiainfo.system.base.web.BaseController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
import javax.xml.crypto.Data;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Created by asus on 2017/7/31.
*/
@Controller
@RequestMapping("/ComplaintHotness")
public class ComplaintHotnessController extends BaseController {
@Autowired
private DimWarningIndexService DIMwarningservice;
@Autowired
private ComplainIndexTotalService ComplainService;
@Autowired
private WarningIndexDetailService WIDService;
@Autowired
private DimWarningIndexService DWIService;
@Autowired
private ComplainIndexTotalService CITService;
/**
*
* @Description: 查询指标数据
* @return
*/
@RequestMapping(value = "/traceComplainIndexTotal", method = RequestMethod.GET )
@ResponseBody
public Object traceComplainIndexTotal(@RequestParam Map<String, String> paramMap) throws ParseException {
List<Map<String, Object>> result = new ArrayList<>();
List<Map<String,Object>> result1 = new ArrayList<>();
List<Map<String,Object>> result2 = new ArrayList<>();
if(paramMap.get("time_dim_id1").equals("4")) {
result1 = CITService.queryComplianIndexLastDay();
result2 = CITService.queryComplianIndexNowDay();
}
if(paramMap.get("time_dim_id1").equals("1")) {
result1 = CITService.queryComplianIndexLastMinute();
result2 = CITService.queryComplianIndexNowMinute();
}
Map<Object,Map> map=new HashMap<>();
for(int j=0;j<result1.size();j++){
map.put(result1.get(j).get("index_id"),result1.get(j));
}
for(int i = 0; i < result2.size(); i++){
Map<String,Object> timeNow=new HashMap<>();
timeNow.put("index_order",result2.get(i).get("index_order"));
timeNow.put("ID",result2.get(i).get("ID"));
timeNow.put("index_value",result2.get(i).get("index_value"));
timeNow.put("index_name",result2.get(i).get("index_name"));
timeNow.put("index_id",result2.get(i).get("index_id"));
if(map.get(result2.get(i).get("index_id")) !=null){
timeNow.put("compare_value",Math.round(Double.parseDouble(result2.get(i).get("index_value") + "")/Double.parseDouble(map.get(result2.get(i).get("index_id")).get("index_value")+ "")* 100) / 100.00 );
}
result.add(timeNow);
}
return result;
}
@RequestMapping("/ComplaintHotness")为前端页面名
Controller层将Service传过来的数据进行接受并处理,指定好需要上传的view。
@RequestParam Map<String, String> paramMap是从前端页面传回来的值。
Js层
function getComplainIndexTotal(time_dim_id)
{
var params = {"time_dim_id1":time_idNow};
var url = "/ComplaintHotness/traceComplainIndexTotal" ;
$.ajax({
type : "GET",
url : url,
dataType : "html",
data:params,
cache : false,
success : function(data) {
var obj = eval('(' + data + ')');
complaintIndexObj = obj;
getDimWarningIndex();
for(var p = 1;p<7;p++){
document.getElementById("number_con" + p).innerHTML =0;
document.getElementById("div" +p).setAttribute('cid', 0);
document.getElementById("pernumber" + p ).innerHTML =
"<i class = 'iconfont icon-up-copy'> </i> <span > " +"0" +"%" + "</span>";
}
if(obj == null|| obj.length ==0){
}else {
for(var x = 0;x<obj.length;x++){
for(var y = 1;y<7;y++){
if(obj[x].index_order == y ){
document.getElementById("number_con" + y).innerHTML =obj[x].index_value;
document.getElementById("div" +y).setAttribute('cid', obj[x].index_id);
if (obj[x].compare_value - 1 >= 0) {
document.getElementById("pernumber" + y ).innerHTML =
"<i class = 'iconfont icon-up-copy'> </i> <span > " + Math.round(Math.abs(obj[x].compare_value - 1) * 100 * 100) / 100 + "</span>%";
} else if(obj[x].compare_value - 1 < 0){
document.getElementById("pernumber" + y ).innerHTML =
"<i class = 'iconfont icon-down'> </i> <span > " + Math.round(Math.abs(obj[x].compare_value - 1) * 100 * 100) / 100 + "</span>%";
}
break;
}
}
}
}
getWarningDetailsColor();
},
error : function(data, textstatus) {
alert("ComplainIndexTotal操作失败.");
}
});
}
URL即为Controller层设定好的两个值。Obj为传过来的list
最后在HTML界面内导入该js
dataType: "html",
转换成dataType:”json”可以省去
而直接用data代替obj