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;
}
}
1.案例:查询各个级别分销商的数量(饼图和柱状图)-Dao
最新推荐文章于 2020-07-13 15:36:25 发布