Echarts使用（附MySQL列转行示例）

最新推荐文章于 2022-01-12 22:43:43 发布

原创最新推荐文章于 2022-01-12 22:43:43 发布 · 423 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#echarts #mysql #java

MySQL学习之路同时被 3 个专栏收录

19 篇文章

订阅专栏

Java

9 篇文章

订阅专栏

前端

2 篇文章

订阅专栏

本文介绍如何使用Echarts进行数据可视化，并提供了一种从MySQL数据库中将列数据转换为行数据的方法，用于创建动态图表。文章详细展示了如何在网页上嵌入Echarts图表，包括饼图和折线图的实现过程，以及通过后端服务获取数据并格式化为Echarts所需格式的具体代码示例。

Echarts使用（附MySQL列转行示例）

echarts官网

页面引入

<script src="/echarts.min.js"></script>

或者网上路径

<script src="https://cdn.staticfile.org/echarts/4.3.0/echarts.min.js"></script>

html

<div id="echartsLine" style="width: 600px;height:300px;margin-left: 20px;"></div>

script

function initEchartsPie() {
    $.post('../echarts/findEchartsPie','',function (data) {
        var echartsPie = echarts.init(document.getElementById('echartsPie'));
        echartsPie.setOption({
            tooltip: {
                trigger: 'item',
                formatter: '{a} <br/>{b}: {c} ({d}%)'
            },
            legend: {
                orient: 'vertical',
                left: 10,
                data: data.legend
            },
            series: [
                {
                    name: '每月交易总额',
                    type: 'pie',
                    selectedMode: 'single',
                    radius: [0, '30%'],

                    label: {
                        position: 'inner'
                    },
                    labelLine: {
                        show: false
                    },
                    data: data.series1
                },
                {
                    name: '每月交易次数',
                    type: 'pie',
                    radius: ['40%', '55%'],
                    label: {
                        formatter: '{a|{a}}{abg|}\n{hr|}\n  {b|{b}：}{c}  {per|{d}%}  ',
                        backgroundColor: '#eee',
                        borderColor: '#aaa',
                        borderWidth: 1,
                        borderRadius: 4,
                        // shadowBlur:3,
                        // shadowOffsetX: 2,
                        // shadowOffsetY: 2,
                        // shadowColor: '#999',
                        // padding: [0, 7],
                        rich: {
                            a: {
                                color: '#999',
                                lineHeight: 22,
                                align: 'center'
                            },
                            // abg: {
                            //     backgroundColor: '#333',
                            //     width: '100%',
                            //     align: 'right',
                            //     height: 22,
                            //     borderRadius: [4, 4, 0, 0]
                            // },
                            hr: {
                                borderColor: '#aaa',
                                width: '100%',
                                borderWidth: 0.5,
                                height: 0
                            },
                            b: {
                                fontSize: 16,
                                lineHeight: 33
                            },
                            per: {
                                color: '#eee',
                                backgroundColor: '#334455',
                                padding: [2, 4],
                                borderRadius: 2
                            }
                        }
                    },
                    data: data.series2
                }
            ]
        });
    })
}

controller

@RequestMapping("findEchartsLineByDate")
@ResponseBody
public Map findEchartsLineByDate(String  startTime,String endTime){
    return echartsService.findEchartsLineByDate(startTime,endTime);
}

service

public Map findEchartsLineByDate(String  startTime,String endTime) {

    List<String> dateList = null;
    //根据前台传来的时间值获取需要展示的月份集合
    if (StringUtils.isEmpty(startTime) && StringUtils.isEmpty(endTime)){
        //获取时间段内所有月份
        dateList = DateUtils.getFormerlyMonths(6);
    }else {
        //默认近半年
        try {
            dateList = DateUtils.getMonthBetween(startTime,endTime);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //拼接sql语句
    StringBuffer stringBuffer = new StringBuffer(" select ");
    for (int i = 0; i < dateList.size(); i++) {
        if (i == dateList.size()-1){
            stringBuffer.append("max(CASE  WHEN DATE_FORMAT(transaction_time,'%Y-%m')='"+dateList.get(i)+"' then (SELECT sum(transaction_amount) FROM echarts WHERE user_id =4 and DATE_FORMAT(transaction_time,'%Y-%m')='"+dateList.get(i)+"'  GROUP BY DATE_FORMAT(transaction_time,'%Y-%m')='"+dateList.get(i)+"') else 0 end ) '"+dateList.get(i)+"'");
        }else {
            stringBuffer.append("max(CASE  WHEN DATE_FORMAT(transaction_time,'%Y-%m')='"+dateList.get(i)+"' then (SELECT sum(transaction_amount) FROM echarts WHERE user_id =4 and DATE_FORMAT(transaction_time,'%Y-%m')='"+dateList.get(i)+"'  GROUP BY DATE_FORMAT(transaction_time,'%Y-%m')='"+dateList.get(i)+"') else 0 end ) '"+dateList.get(i)+"',");
        }
    }
    stringBuffer.append("FROM echarts");
    String sql = stringBuffer.toString();
    HashMap<String,Double> echartsLineCount = echartsMapper.findEchartsLineByDate(sql);
    //循环日期集合   根据日期集合为key获取map中对应的value值
    List seriesList = new ArrayList();
    for (int i = 0; i < dateList.size(); i++){
        String key = dateList.get(i);
        Double aLong = echartsLineCount.get(key);
        seriesList.add(aLong);
    }
    //将折线图需要的数据放入map中返回
    HashMap result = new HashMap();
    result.put("xAxis",dateList);
    result.put("series",seriesList);
    return result;
}

mapper

@Select("${value}")
HashMap<String, Double> findEchartsLineByDate(String sql);

@Select("SELECT DATE_FORMAT(transaction_time,'%Y-%m') from echarts where transaction_time>DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY DATE_FORMAT(transaction_time,'%Y-%m') ORDER BY transaction_time")
List<String> findDateList();

@Select("SELECT DATE_FORMAT(transaction_time,'%Y-%m') as name,sum(transaction_amount) as value from echarts where transaction_time>DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY DATE_FORMAT(transaction_time,'%Y-%m') ORDER BY transaction_time")
List<EchartsDto> findSumList();

@Select("SELECT DATE_FORMAT(transaction_time,'%Y-%m') as name,count(1) as value from echarts where transaction_time>DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY DATE_FORMAT(transaction_time,'%Y-%m') ORDER BY transaction_time")
List<EchartsDto> findCountList();

sql示例

select 
		max(CASE  WHEN DATE_FORMAT(transaction_time,'%Y-%m')='2019-06' then (SELECT sum(transaction_amount) FROM echarts WHERE user_id =4 and DATE_FORMAT(transaction_time,'%Y-%m')='2019-06'  GROUP BY DATE_FORMAT(transaction_time,'%Y-%m')='2019-06') else 0 end ) '2019-06',
		max(CASE  WHEN DATE_FORMAT(transaction_time,'%Y-%m')='2019-07' then (SELECT sum(transaction_amount) FROM echarts WHERE user_id =4 and DATE_FORMAT(transaction_time,'%Y-%m')='2019-07'  GROUP BY DATE_FORMAT(transaction_time,'%Y-%m')='2019-07') else 0 end ) '2019-07',
		max(CASE  WHEN DATE_FORMAT(transaction_time,'%Y-%m')='2019-08' then (SELECT sum(transaction_amount) FROM echarts WHERE user_id =4 and DATE_FORMAT(transaction_time,'%Y-%m')='2019-08'  GROUP BY DATE_FORMAT(transaction_time,'%Y-%m')='2019-08') else 0 end ) '2019-08'
				FROM echarts;

select order_type,
          <foreach collection="list" item="date" separator=",">
          max(CASE  WHEN DATE_FORMAT(upte_date,'%Y-%m-%d')=#{date} then (SELECT count(*) FROM t_order WHERE order_type =1 and DATE_FORMAT(upte_date,'%Y-%m-%d')=#{date}) else 0 end ) #{date}+'-1',
          max(CASE  WHEN DATE_FORMAT(upte_date,'%Y-%m-%d')=#{date} then (SELECT count(*) FROM t_order WHERE order_type =2 and DATE_FORMAT(upte_date,'%Y-%m-%d')=#{date}) else 0 end ) #{date}+'-2',
          max(CASE  WHEN DATE_FORMAT(upte_date,'%Y-%m-%d')=#{date} then (SELECT count(*) FROM t_order WHERE order_type =3 and DATE_FORMAT(upte_date,'%Y-%m-%d')=#{date}) else 0 end ) #{date}+'-3'
          </foreach>
          FROM t_order GROUP BY update_date;