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

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) */@Overridepublic Map<String, Integer> getClientLevelCount() {/*-- d 数据字典;c 分销商select d.name, count(client_level_id) as countfrom t_client c right outer join t_data_dict d on c.client_level_id = d.idwhere d.category = 'A'group by d.nameorder 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;}}