1.案例:查询各个级别分销商的数量(饼图和柱状图)-Dao

最新推荐文章于 2020-07-13 15:36:25 发布

种子之家

最新推荐文章于 2020-07-13 15:36:25 发布

阅读量895

点赞数

分类专栏： JFreeChart 文章标签： integer join jdbc sql class c

本文链接：https://blog.csdn.net/seeds_home/article/details/6898516

版权

JFreeChart 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

package com.bjpowernode.drp.statreport.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.bjpowernode.drp.statreport.dao.StatReportDao;
import com.bjpowernode.drp.util.ConnectionManager;
import com.bjpowernode.drp.util.DaoException;

public class StatReportDaoImpl implements StatReportDao {

	/**
	 * 统计各个级别分销商数量
	 * @return Map(key="一级分销商", value=100;key="二级分销商"， value=200,key="三级分销商", value=300)
	 */
	@Override
	public Map<String, Integer> getClientLevelCount() {

		/*
			-- d 数据字典;c 分销商
			select d.name, count(client_level_id) as count
			from t_client c right outer join t_data_dict d on c.client_level_id = d.id
			where d.category = 'A'
			group by d.name
			order by d.name
		*/
		
		// sql,统计各个级别分销商数量
		StringBuilder sql = new StringBuilder();
		
		sql.append("select d.name, count(client_level_id) as count ")
			.append("from t_client c right outer join t_data_dict d on c.client_level_id = d.id ")
			.append("where d.category = 'A' ")
			.append("group by d.name ")
			.append("order by d.name");
		
		// JDBC  
		Connection connection = ConnectionManager.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		// Map(key="一级分销商", value=100;key="二级分销商"， value=200,key="三级分销商", value=300)
		Map<String, Integer> clientLevelCount = new HashMap<String, Integer>();
		
		
		try {
			preparedStatement = connection.prepareStatement(sql.toString());
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				clientLevelCount.put(resultSet.getString("name"), resultSet.getInt("count"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("统计各个级别分销商数量失败!");
			
		} finally {
			ConnectionManager.closeResultSet(resultSet);
			ConnectionManager.closeStatement(preparedStatement);
			
		}
		return clientLevelCount;
	}

}