1.大区select和对应城市select:dao

最新推荐文章于 2025-08-23 16:56:09 发布

原创最新推荐文章于 2025-08-23 16:56:09 发布 · 424 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#dao #integer #string #join #sql #jdbc

下拉联动专栏收录该内容

5 篇文章

订阅专栏

本博客展示了如何使用SQL查询来统计不同级别的分销商数量，并获取特定大区及其下属地区的详细信息。通过构建合适的SQL语句，实现对数据的有效提取和分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;
	}

	/**
	 * 取得大区列表,装在map里,key:大区的id,value:大区的名字
	 * @return
	 */
	@Override
	public Map<Integer, String> getRegionList() {
		
		Map<Integer, String> regions = getProvincesByRegionId(10000);
		return regions;
	}

	/**
	 * 根据大区id取得相应的市
	 * @param id 大区id
	 * @return
	 */
	@Override
	public Map<Integer, String> getProvincesByRegionId(int id) {
		
		/*
			select id, name from t_client
			where pid= 10001
			order by id;
		 */
		// sql
		StringBuilder sql = new StringBuilder();
		
		sql.append("select id, name from t_client ")
			.append("where pid=? ")
			.append("order by id");
		
		// JDBC
		Connection connection = ConnectionManager.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		// 把查询的结果设在map中,key:相应的省或是分销商的id,value:相应的省或是分销商的名字
		Map<Integer, String> cities = new HashMap<Integer, String>();
		
		try {
			preparedStatement = connection.prepareStatement(sql.toString());
			
			// 给占位符赋值
			preparedStatement.setInt(1, id);
			
			// 执行查询
			resultSet = preparedStatement.executeQuery();
			
			// 把查询的结果设在map中,key:相应的省或是分销商的id,value:相应的省或是分销商的名字
			while (resultSet.next()) {
				cities.put(resultSet.getInt("id"), resultSet.getString("name"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("根据大区id取得相应的省或是分销商失败!");
			
		} finally {
			ConnectionManager.closeResultSet(resultSet);
			ConnectionManager.closeStatement(preparedStatement);
		}
		
		return cities;
	}
	
	

}