区县街道二级联动（ibatis+spring+springMVC）

数据库：oracle

表结构：

     区县表（T_DISTRICT）：ID（pk），CODE（区号），NAME(区名)

     街道表（T_TOWN）：ID（pk），CODE（街号），NAME（街名），DIS_CODE（fk，对应区号）

     任务表（T_TASK）：ID（pk），NAME（任务名），DISTRICT（fk，对应区号），TOWN（fk，街道号），  STATE（状态），VALID（0无效，1有效），DETAILS（任务描述），USER（提交人）

字段类型就不写了。任务表里可以有多个相同任务但最多只有一个任务为有效，此时VALID=1.

目标：区县街道二级联动，已有任务关联的街道可见但不可选。

先从xml开始：1查询所有区号和区名

<sqlMap namespace="task">

<select id="getAllDistrict" resultClass="java.util.HashMap">
	<![CDATA[
	SELECT CODE,NAME FROM T_DISTRICT
	]]>
	</select>

2查询所有街道号和街道名

<select id="getAllTown" resultClass="java.util.HashMap">
	<![CDATA[
	SELECT CODE,NAME FROM T_TOWN
	]]>
	</select>

3根据已选区号来查询街道号和街道名

<select id="getTown" parameterClass="map" resultClass="java.util.HashMap">
	<![CDATA[
	SELECT A.CODE,A.NAME,B.VALID FROM T_TOWN A ,(SELECT TOWN,VALID FROM T_TASK WHERE VALID=1) B WHERE B.TOWN(+)=A.CODE AND A.DIS_CODE= #DIS_CODE#
	]]>
	</select>

这个查询思路说下：先是查询任务表里有效任务的街号与有效值，然后根据任务表的街号外键，左连接街道表去查街名和街号，最后再根据传入的区号参数去查需要的街号和街名....。

dao层

public interface TaskDao extends BaseDao<Object> {
	/**
	 * 查询关联镇信息
	 * @param param
	 * @throws Exception
	 */
	public List<HashMap<String, String>> getTown(HashMap<String, String> param) throws Exception;
	

	/**
	 * 查询所有区信息
	 * @throws Exception
	 */
	public List<HashMap<String, String>>getAllDistrict()throws Exception;
	
	
	/**
	 * 查询所有镇信息
	 * @throws Exception
	 */
	public List<HashMap<String, String>>getAllTown()throws Exception;
	

dao层实现类

@Repository
public class TaskDaoImp extends BaseDaoImpl<Object> implements TaskDao {

	@Autowired
	protected SqlMapClient sqlMapClient;

	@Override
	@AnnotationMethod(comment="查关联镇")
	public List<HashMap<String, String>> getTown(HashMap<String, String> param) throws Exception {
		return sqlMapClient.queryForList("task.getTown",param);

	}

	@Override
	@AnnotationMethod(comment="查所有区")
	public List<HashMap<String, String>> getAllDistrict() throws Exception {			
		return sqlMapClient.queryForList("task.getAllDistrict");
	}

	@Override
	@AnnotationMethod(comment="查所有镇")
	public List<HashMap<String, String>> getAllTown() throws Exception {
		return sqlMapClient.queryForList("task.getAllTown");
	}

}

service层

public interface TaskService {
	/**
	 * 查镇
	 * @param param
	 * @throws Exception
	 */

	public List<HashMap<String, String>> getTownService(HashMap<String, String> param) throws Exception;
	
	/**
	 * 查所有区
	 * @throws Exception
	 */
	public List<HashMap<String, String>>getAllDistrictService() throws Exception;
	
	
	/**
	 * 查所有镇
	 * @throws Exception
	 */
	public List<HashMap<String, String>>getAllTownService() throws Exception;

}
	

service层实现类

@Service
public class TaskServiceImpl implements TaskService {

	@Autowired
	TaskDao taskdao;
	
	@Override
	@AnnotationMethod(comment="查镇")
	public List<HashMap<String, String>> getTownService(HashMap<String, String> param) throws Exception {
		return taskdao.getTown(param);
	}

	@Override
	@AnnotationMethod(comment="查询所有区信息")
	public List<HashMap<String, String>> getAllDistrictService() throws Exception {
		return taskdao.getAllDistrict();
	}

	@Override
	@AnnotationMethod(comment="查询所有县信息")
	public List<HashMap<String, String>> getAllTownService() throws Exception {
		return taskdao.getAllTown();
	}

}

controller等下写，先展示页面

js核心代码：

  <script type="text/javascript">
    $(function(){  
      if(isNotNull($(":input[name=DISTRICT]"))){
        var district = $(":input[name=DISTRICT]");
        var street = $(":input[name=STREET]");
        district.change(function() {
          $.ajax({
            url: absurl("/task/getTownController"),
            cache: false, 
            data: {DISTRICT_CODE: (this.value != 0? this.value: '')}, 
            dataType: "json",
            success: function(data){
              if(data){
                street.empty();
                $.each(data, function(i, d) {
                  if (d.VALID == 1) {
                    $("<option disabled='true' value='" + d.CODE + "' label='" + d.NAME + "'>"+ d.NAME+ "</option>").appendTo(street);
                  } else {
                    $( "<option value='" + d.CODE + "' label='" + d.NAME + "'>" + d.NAME+ "</option>").appendTo(street);
                  }
                });
              }
            },
            error: function() {
              message(E[106], "系统异常，请联系系统管理员！");
            }
          }); 
        }).change();
      }

    });
  </script>

页面元素main.jsp

<body class="easyui-layout">
  <div region="center" border="false" class="form-body">
  <div class="mainform">
    <div class="form-panel-title">请选择区县街道</div>
    <div class="formdiv">
      <table class="formtable">
        <tbody>
          <tr>
            <td class="title tcol3">区县：</td>
            <td class="content ccol3">
              <select name="DISTRICT" >
                <c:forEach items="${DISTRICTLIST}" var="district">  
                   <option value="${district.CODE}">${district.NAME }</option>  
              </c:forEach>  
            </select>
          </td>
          <td class="title tcol3">街道：</td>
          <td class="content ccol3">
            <select name="STREET">
              <c:forEach items="${STREETLIST}" var="street">  
                <option value="${street.CODE}">${street.NAME}</option>  
              </c:forEach>  
            </select> 
          </td>
          </tr>
        </tbody>
      </table>
    </div>
  </div>
  </div>
</body>  

controller

@Controller
@RequestMapping(value = "/task")
public class TaskControl extends BaseController{
  
  @Resource
  TaskService taskService;
  
  @ResponseBody
  @RequestMapping(value = "/getTownController", produces = MediaType.APPLICATION_JSON_VALUE)
  public List<HashMap<String, String>> getControlTown(HttpServletRequest request) throws Exception{
    HashMap<String, String> param = new HashMap<String,String>();
    param.put("DISTRICT_CODE",request.getParameter("DISTRICT_CODE"));
    return taskService.getTownService(param);
  }
  
  
  @RequestMapping(value = "/xxx")//转向main.jsp的请求
  public String getAllDisCodeName(HttpServletRequest req,ModelMap mp) throws Exception{
    mp.addAttribute("DISTRICTLIST", taskService.getAllDistrictService());
    mp.addAttribute("STREETLIST", taskService.getAllTownService());
    return "main.jsp";
  }

到此一个简单的二级联动就完成了。