jQuery+strus1+ibatis+db2三级联动插件

sunkezai

　　记得毕业前的那会，学习jQuery Ajax写过一个三级联动的简单js。现在工作中又是遇到这样的需求。本来想去plugins.jquery.com找找看，但是还是没有找到无限贴近需求的插件。以前写的那个可是说不是插件，只是通用的JS而已，为了不重复造轮子，写下现在开发的过程，顺便学习下插件的开发。
　　DB2建表语句：(很简单的一对多关系)

CREATE TABLE DB2INST1.HK_DISTRICT1(
DISTR1_IDINTEGERNOT NULLNOT NULLGENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
DISTR1_NAMEVARCHAR(50),
DISTR1_ENAMEVARCHAR(50)
)
IN USERSPACE1;
ALTER TABLE DB2INST1.HK_DISTRICT1
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
ALTER TABLE DB2INST1.HK_DISTRICT1
ADD PRIMARY KEY
(DISTR1_ID);

--TABLE HK_DISTRICT2
CREATE TABLE DB2INST1.HK_DISTRICT2(
DISTR2_IDINTEGERNOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
DISTR2_NAMEVARCHAR(50),
DISTR2_ENAMEVARCHAR(50),
PARENT INTEGER NOT NULL
)
IN USERSPACE1;
ALTER TABLE DB2INST1.HK_DISTRICT2
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
ALTER TABLE DB2INST1.HK_DISTRICT2
ADD PRIMARY KEY
(DISTR2_ID);
ALTER TABLE DB2INST1.HK_DISTRICT2
ADD FOREIGN KEY
(PARENT)
REFERENCES DB2INST1.HK_DISTRICT1
(DISTR1_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

--TABLE HK_DISTRICT3
CREATE TABLE DB2INST1.HK_DISTRICT3(
DISTR3_IDINTEGERNOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
DISTR3_NAMEVARCHAR(50),
DISTR3_ENAMEVARCHAR(50),
PARENT INTEGER NOT NULL
)
IN USERSPACE1;
ALTER TABLE DB2INST1.HK_DISTRICT3
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
ALTER TABLE DB2INST1.HK_DISTRICT3
ADD PRIMARY KEY
(DISTR3_ID);
ALTER TABLE DB2INST1.HK_DISTRICT3
ADD FOREIGN KEY
(PARENT)
REFERENCES DB2INST1.HK_DISTRICT2
(DISTR2_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
　　ibatis (sql.xml)通过SELECT配置一对多关系

<sqlMap namespace="HK_District">
<typeAlias alias="Distric1Vo" type="com.amway.site.vo.Distric1Vo"/>
<typeAlias alias="Distric2Vo" type="com.amway.site.vo.Distric2Vo"/>
<typeAlias alias="Distric3Vo" type="com.amway.site.vo.Distric3Vo"/>
<cacheModel type="LRU" id="district-cache">
<flushInterval minutes="10"/>
<property name="size" value="1000"/>
</cacheModel>
<resultMap id="district_1_map" class="Distric1Vo">
<result property="distr1Id" column="DISTR1_ID"/>
<result property="distrName" column="DISTR1_NAME"/>
<result property="distrEname" column="DISTR1_ENAME"/>
<result property="district2s" column="DISTR1_ID" select="find_distr2s_by_parentId"/>
</resultMap>
<resultMap id="district_2_map" class="Distric2Vo">
<result property="distr2Id" column="DISTR2_ID"/>
<result property="distrName" column="DISTR2_NAME"/>
<result property="distrEname" column="DISTR2_ENAME"/>
<result property="district3s" column="DISTR2_ID" select="find_distr3s_by_parentId"/>
</resultMap>
<resultMap id="district_3_map" class="Distric3Vo">
<result property="distr3Id" column="DISTR3_ID"/>
<result property="distrName" column="DISTR3_NAME"/>
<result property="distrEname" column="DISTR3_ENAME"/>
</resultMap>
<select id="find_all_district" resultMap="district_1_map"
cacheModel="district-cache">
SELECT DISTR1_ID,
DISTR1_NAME,
DISTR1_ENAME
FROM DB2INST1.HK_DISTRICT1
WITH UR
</select>
<select id="find_distr2s_by_parentId" resultMap="district_2_map"
cacheModel="district-cache">
SELECT DISTR2_ID,
DISTR2_NAME,
DISTR2_ENAME
FROM DB2INST1.HK_DISTRICT2
WHERE PARENT = #value#
WITH UR
</select>
<select id="find_distr3s_by_parentId" resultMap="district_3_map"
cacheModel="district-cache">
SELECT DISTR3_ID,
DISTR3_NAME,
DISTR3_ENAME
FROM DB2INST1.HK_DISTRICT3
WHERE PARENT = #value#
WITH UR
</select>
</sqlMap>
　　POJO:很简单的对应表的字段，同时第一个VO包含第二个VO的list,第二个VO包含第三个VO的List。如下：

      //Distric1Vo
private Integer distr1Id;
private String distrName;
private String distrEname;
private List<Distric2Vo> district2s;
//Distric2Vo
private Integer distr2Id;
private String distrName;
private String distrEname;
private List<Distric2Vo> district3s;
//Distric3Vo
private Integer distr3Id;
private String distrName;
private String distrEname;
　　以上就可以使用Ibatis捞出所有的数据：List<Distric1Vo>  list  包含了三个表的数据，这样做的好处就不需要每次联动的时候
　　都去后台捞。当然你也使用java 静态机制 不要重复的查询数据
　　strus1 action:

req.setCharacterEncoding("UTF-8");//设置编码格式，否则会乱码
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();//strus1使用IO操作，写入到页面
List <Distric1Vo> list = DistrictManager.getInstance().getAllDistrict();
Gson gson = new Gson();
System.out.println(gson.toJson(list));
out.print(gson.toJson(list));
//需要用到gson.jar或者json-lib-2.4-jdk15以便很好的把list转换成json
return null;
　　插件代码，见附件。
　　使用方法：

$(function(){
$("#test").hkDistrictSelector();
});
　　支持参数options：
　　{
　　language: 'zh'//语言zh，en，分别查询数据库的英文字段。
　　,styleSheet:{
　　"width":"100px"
　　,"margin-left":"10px"
　　}//select的样式css支持jquery的css api参数格式
　　,isCache:false//是否缓存，暂时未去实现
　　,ajaxJsonUrl: "listDistrict.do"//jquery Ajax请求的strus1的action地址
　　,ajaxData : "method=unspecified"//Ajax请求的strus1的method参数 
　　}
　　参数用法：

$(function(){
$("#test").hkDistrictSelector({
language: 'en'
,styleSheet:{
"width":"100px"
,"margin-left":"10px"
}
});
});
　　效果：
　　

   
　　这样的话，一个可拓展的插件就开发好了。
　　下次遇到这样的工作，我就可以节省很多的时间了，呵呵~