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

       记得毕业前的那会，学习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"
		  }
		});
	});

    

    效果：

     

   

   这样的话，一个可拓展的插件就开发好了。

    

   下次遇到这样的工作，我就可以节省很多的时间了，呵呵~

    

    

      

   

 

评论

1 楼 songbgi 2013-09-18  

能升级下么
现在都struts2 mybatis了