记得毕业前的那会,学习jQuery Ajax写过一个三级联动的简单js。现在工作中又是遇到这样的需求。本来想去plugins.jquery.com找找看,但是还是没有找到无限贴近需求的插件。以前写的那个可是说不是插件,只是通用的JS而已,为了不重复造轮子,写下现在开发的过程,顺便学习下插件的开发。
DB2建表语句:(很简单的一对多关系)
CREATE TABLE DB2INST1.HK_DISTRICT1(
DISTR1_ID INTEGER NOT NULL NOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
DISTR1_NAME VARCHAR(50),
DISTR1_ENAME VARCHAR(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_ID INTEGER NOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
DISTR2_NAME VARCHAR(50),
DISTR2_ENAME VARCHAR(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_ID INTEGER NOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
DISTR3_NAME VARCHAR(50),
DISTR3_ENAME VARCHAR(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" } }); });
效果:
这样的话,一个可拓展的插件就开发好了。
下次遇到这样的工作,我就可以节省很多的时间了,呵呵~