カテゴリー: multipleSelect

MultipleSelect 使い方 (4) Java

投稿日: 投稿者:

大まかな流れとしては、

  1. サーブレットで各マルチセレクト条件値CSVテキストをリクエストパラメータで受信
  2. サーブレットで、1.がnullであるか判別してフラグにして、操作用クラスにセットする
  3. マルチセレクト条件値CSVテキストは、リストにして操作用クラスにセットする
  4. 操作用クラスの検索処理メソッドで、最初にセットされた項目別フラグがtrueの場合、一時テーブルの削除追加を行う
  5. MyBatisの指定idの検索SQLを実行する際に、フラグがtrueの場合、一時テーブルをジョインしたSQL、falseの場合ジョインしないSQLを実行

となります。

MultipleSelect 使い方 (3) MyBatis SQL

投稿日: 投稿者:

検索条件を各項目の一時テーブルに入れて、ジョインするかしないかは、パラメータで指示してます。

[ SQL例 ]


 <!-- 船スペックマスタリスト --> 
 <!-- 使用 -->
  <select id="selVesselSpecList2" parameterType="ParamVesselSpec" resultType="VesselSpec">
    select * from (
		select
			v.ukfld as id, v.ukfld as imo,
			
			v.Name as vessel, 
      coalesce(fc_acc6m(v.Name), '') as acc6m,
    
			coalesce(en.cnt_en, 0) as cnt_en, 
      coalesce(`in`.cnt_in, 0) as cnt_in,     
			coalesce(eo.cnt_eo, 0) as cnt_eo, 			
			coalesce(io.cnt_io, 0) as cnt_io, 
      coalesce(ua.cnt_ua, 0) as cnt_ua, 
			
			v.TEU, v.`Year`, v.Speed, v.GrossT, v.DeadW, v.`call`, v.Cntry,  
		
			v.tm_add, v.tm_updt, 
			greatest(v.tm_add, v.tm_updt, coalesce(mt.TM_ADD, '2000-01-01 00:00:00')) as tm_lastupdt,
		
			v.is_manadd,
    
      coalesce(mt.LENGTH_OVERALL, 0) as `length`,
			coalesce(mt.BREADTH_EXTREME, 0) as width,
			coalesce(mt.DRAUGHT, 0) as draught,
    
      coalesce(round(mt.LENGTH_OVERALL, 0), 0) as `length_int`,
			coalesce(round(mt.BREADTH_EXTREME, 0), 0) as width_int,
			coalesce(round(mt.DRAUGHT, 0), 0) as draught_int,
    
			COALESCE(mt.LIQUID_OIL, 0) AS liquid_oil,
			COALESCE(mt.FUEL_CONSUMPTION, '') AS fuel_consumption,
			COALESCE(mt.OWNER, '') AS owner,			
			COALESCE(mt.MANAGER, '') AS manager,
			COALESCE(mt.MANAGER_OWNER, '') AS manager_owner    
			
			
		from 
			vessel2 v
    
      left outer join vessel_mt mt on (v.ukfld = mt.IMO) 
    
      <if test="!isTEUselAll">
        inner join tmp_imo_teu tt on (
        v.ukfld = tt.IMO and tt.ID_SESSION = #{idSession} and tt.NM_GRID = 'specs' 
        )
      </if>
      <if test="!isYearselAll">
        inner join tmp_imo_year yt on (
        v.ukfld = yt.IMO and yt.ID_SESSION = #{idSession} and yt.NM_GRID = 'specs' 
        )        
      </if>
      <if test="!isSpeedselAll">
        inner join tmp_imo_speed st on (
        v.ukfld = st.IMO and st.ID_SESSION = #{idSession} and st.NM_GRID = 'specs' 
        )        
      </if>
      
      <if test="!isLengthselAll">
        inner join tmp_imo_length lg on (
        mt.IMO = lg.IMO and lg.ID_SESSION = #{idSession} and lg.NM_GRID = 'specs' 
        )        
      </if>
      <if test="!isWidthselAll">
        inner join tmp_imo_width wd on (
        mt.IMO = wd.IMO and wd.ID_SESSION = #{idSession} and wd.NM_GRID = 'specs' 
        )        
      </if>
      <if test="!isDraughtselAll">
        inner join tmp_imo_draught dr on (
        mt.IMO = dr.IMO and dr.ID_SESSION = #{idSession} and dr.NM_GRID = 'specs' 
        )        
      </if>
      
    
			left outer join (
			select imo, count(*) as cnt_en from sch_export_source group by imo
			) en 
			on (v.ukfld = en.imo)
			
			left outer join (
			select imo, count(*) as cnt_in from sch_import_source group by imo
			) `in` 
			on (v.ukfld = `in`.imo)
		      
      left outer join (
			select imo, count(*) as cnt_eo from sch_export_source_old group by imo
			) eo 
			on (v.ukfld = eo.imo)
      
      left outer join (
			select imo, count(*) as cnt_io from sch_import_source_old group by imo
			) io 
			on (v.ukfld = io.imo)    
      
			left outer join (
			select u.vessel, count(u.id) as cnt_ua from sch_useradd u group by vessel
			) ua on (v.Name = ua.vessel)             
      
    where 
      1 = 1 and v.Name like concat('%', #{vsllk}, '%') and
      ukfld like concat('%', #{imolk}, '%')
      <if test="isModOnly">
        and is_manadd = 1
      </if>
    ) a
    order by
      ${sidx} ${sord}  
    limit ${skip}, ${pgrec}  
  </select>

[ 使っているテーブル ]

船マスタ


CREATE TABLE `vessel2` (
	`Name` VARCHAR(36) NOT NULL DEFAULT '' COMMENT '船名',
	`call` VARCHAR(9) NOT NULL DEFAULT '' COMMENT '無線局呼出符号',
	`Year` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '建造年',
	`Speed` FLOAT NOT NULL DEFAULT '0' COMMENT '速力',
	`TEU` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '積載TEU',
	`GrossT` INT(11) NOT NULL DEFAULT '0' COMMENT 'グロス重量',
	`DeadW` INT(11) NOT NULL DEFAULT '0' COMMENT '重量重量',
	`Cntry` VARCHAR(2) NOT NULL DEFAULT '' COMMENT '船籍国コード',
	`ukfld` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
	`tm_add` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	`tm_updt` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '最終更新時刻',
	`is_manadd` TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'ユーザー更新フラグ',
	`ChgName` VARCHAR(36) NOT NULL DEFAULT '0' COMMENT '参照結合用変換船名',
	PRIMARY KEY (`ukfld`),
	INDEX `vessel2_Name` (`Name`),
	INDEX `vessel2_ChgName` (`ChgName`)
)
COMMENT='船マスタ (利用)'
COLLATE='utf8_general_ci'
ENGINE=MyISAM

一時テーブル (一部)

・ID_SESSION はTomcatのセッションIDでユーザー別
・ほっておくと肥大するので、夜中に全レコード削除


CREATE TABLE `tmp_imo_teu` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
	`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
	`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
	`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	PRIMARY KEY (`ID`),
	INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
	INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
	INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5627
;

CREATE TABLE `tmp_imo_year` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
	`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
	`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
	`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	PRIMARY KEY (`ID`),
	INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
	INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
	INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

[ 一時テーブル削除追加用SQL ]

・テーブル名を ${tmptbl} のパラメータで渡すことで、まとめてます。


 <!-- マルチセレクト用一時テーブル更新 -->
 <!-- 削除 -->
 <delete id="delIMOTmpAll" parameterType="MyBatisParent">
   delete from ${tmptbl} where ID_SESSION = #{idSession} and NM_GRID = #{nmGrid}
 </delete>
 <!-- 追加 -->
 <insert id="insAIMOTmp" parameterType="MyBatisParent">
   insert into ${tmptbl}
   (ID_SESSION, NM_GRID, IMO)
   select #{idSession}, #{nmGrid}, 
   ukfld
   from ${vslmsttbl}
   where ${vslColumn} between #{insMinVal} and #{insMaxVal}
 </insert>
 <insert id="insAIMOTmpmt" parameterType="MyBatisParent">
   insert into ${tmptbl}
   (ID_SESSION, NM_GRID, IMO)
   select #{idSession}, #{nmGrid}, IMO
   from ${vslmsttbl}
   where ${vslColumn} between #{insMinVal} and #{insMaxVal}
 </insert>

MultipleSelect 使い方 (2) Javascript の設定

投稿日: 投稿者:

[ js, cssのセット ]


    <!-- MultipleSelect -->
    <script type="text/javascript" src="Script/jquery.multiple.select.js"></script>
    <link rel='stylesheet' href='CSS/multiple-select.css' />

[ コードサンプル ]


      // マルチセレクト全選択数用 //
      /* この変数は、外のfunctionで使うのでload前	 :/
      var teuselAllCnt;
      var yearselAllCnt;
      var speedselAllCnt;
      var lengthselAllCnt;
      var widthselAllCnt;
      var draughtselAllCnt;

        
        // マルチプルセレクト //
        $('#teusel, #yearsel, #speedsel, #lengthsel, #widthsel, #draughtsel').multipleSelect({
          //$('#filesel, #sheetsel, #suplsel, #psnsel').multipleSelect({
          allSelected: 'All',
          //height: '500px'
        });

        // 全選択して項目数を調べる //
        $('#teusel').multipleSelect('checkAll');
        $('#yearsel').multipleSelect('checkAll');
        $('#speedsel').multipleSelect('checkAll');
        $('#lengthsel').multipleSelect('checkAll');
        $('#widthsel').multipleSelect('checkAll');
        $('#draughtsel').multipleSelect('checkAll');

        teuselAllCnt = $('#teusel').multipleSelect('getSelects', 'value').length;
        yearselAllCnt = $('#yearsel').multipleSelect('getSelects', 'value').length;
        speedselAllCnt = $('#speedsel').multipleSelect('getSelects', 'value').length;
        lengthselAllCnt = $('#lengthsel').multipleSelect('getSelects', 'value').length;
        widthselAllCnt = $('#widthsel').multipleSelect('getSelects', 'value').length;
        draughtselAllCnt = $('#draughtsel').multipleSelect('getSelects', 'value').length;



/****** ロード時は常に全選択状態なので、全選択フラグを全部オンにする */

        // **** 船スペックグリッド **** //
        $("#specs").jqGrid({
          url: "GetVesselSpecList",
          //editurl: "paramCheck",
          editurl: "UpdateAVesselSpec",
          mtype: 'post',
          datatype: "json",
          serializeGridData: function (postData) {
            //postData['mindt'] = '2017-12-01';
            //postData['idsql'] = 'selDailyBatchLogList';
            postData['vsllk'] = vsllklast;
            postData['imolk'] = imolklast;
            postData['isTEUSelAll'] = '1';
            postData['isYearSelAll'] = '1';
            postData['isSpeedSelAll'] = '1';
            postData['isLengthSelAll'] = '1';
            postData['isWidthSelAll'] = '1';
            postData['isDraughtSelAll'] = '1';
            postData['teuselext'] = "";
            postData['yearselext'] = "";
            postData['speedselext'] = "";
            postData['lengthselext'] = "";
            postData['widthselext'] = "";
            postData['draughtselext'] = "";
            postData['isModOnly'] = '0';

            //postData['rows'] = rownum; 
            return postData;
          },

/*************************************************************/
	
        // == フィルターボタンを押した時 == //
        $('#fltbtn').on('click', function () {

          var teuselcnt = $('#teusel').multipleSelect('getSelects', 'value').length;
          var yearselcnt = $('#yearsel').multipleSelect('getSelects', 'value').length;
          var speedselcnt = $('#speedsel').multipleSelect('getSelects', 'value').length;
          var lengthselcnt = $('#lengthsel').multipleSelect('getSelects', 'value').length;
          var widthselcnt = $('#widthsel').multipleSelect('getSelects', 'value').length;
          var draughtselcnt = $('#draughtsel').multipleSelect('getSelects', 'value').length;

          // グリッドの更新 //
          $('#specs').jqGrid('setGridParam', {
            page: 1,
            serializeGridData: function (postData) {
              
              // 単純なので、JSONにしないで配列文字列にして送信 //
              /* 最後に "" を必ずつける */
              postData['teuselext'] = $('#teusel').multipleSelect("getSelects", "value") + "";
              postData['yearselext'] = $('#yearsel').multipleSelect("getSelects", "value") + "";
              postData['speedselext'] = $('#speedsel').multipleSelect("getSelects", "value") + "";
              postData['lengthselext'] = $('#lengthsel').multipleSelect("getSelects", "value") + "";
              postData['widthselext'] = $('#widthsel').multipleSelect("getSelects", "value") + "";
              postData['draughtselext'] = $('#draughtsel').multipleSelect("getSelects", "value") + "";

              // 全選択 or Not を送信用 //
              postData['isTEUSelAll'] = teuselcnt == teuselAllCnt ? '1' : '0';
              postData['isYearSelAll'] = yearselcnt == yearselAllCnt ? '1' : '0';
              postData['isSpeedSelAll'] = speedselcnt == speedselAllCnt ? '1' : '0';
              postData['isLengthSelAll'] = lengthselcnt == lengthselAllCnt ? '1' : '0';
              postData['isWidthSelAll'] = widthselcnt == widthselAllCnt ? '1' : '0';
              postData['isDraughtSelAll'] = draughtselcnt == draughtselAllCnt ? '1' : '0';

              postData['vsllk'] = $('#nameinp').val();
              postData['imolk'] = $('#imoinp').val();
              return postData;
            }
          }).trigger('reloadGrid');

          // ローカルストレージに保存 //
          // これは使ってないが、今後の為 //
          if (localStorage) {
            var savemap = {};
            savemap['teuselext'] = $('#teusel').multipleSelect("getSelects", "value");
            savemap['yearselext'] = $('#yearsel').multipleSelect("getSelects", "value");
            savemap['speedselext'] = $('#speedsel').multipleSelect("getSelects", "value");
            savemap['lengthselext'] = $('#lengthsel').multipleSelect("getSelects", "value");
            savemap['widthselext'] = $('#widthsel').multipleSelect("getSelects", "value");
            savemap['draughtselext'] = $('#draughtsel').multipleSelect("getSelects", "value");
            savemap['vsllk'] = $('#nameinp').val();
            savemap['imolk'] = $('#imoinp').val();
            localStorage.setItem('vesselsch_specs_filter', JSON.stringify(savemap));
          }

        });

MultipleSelect 使い方 (1) リスト用アイテムの用意

投稿日: 投稿者:

MultipleSelect を使うと、エクセルのフィルターのような、複数アイテムの指定による検索が可能となり、検索機能が大幅に向上します。

[ 設置画面例 ]

[ アイテム用SQL例 ]

グループ化した各範囲値を結果レコードにする。concatで区切った左がselectタグのvalue、右がテキスト


 <!-- 船スペックフィルター用 -->
 <select id="selTEU1000CmbList" resultType="String">
		/* TEU 1000 */
		
		select
			concat(
			concat(truncate(v.TEU, -3), '-',truncate(v.TEU, -3) + 999), ',',
			concat(cast(truncate(v.TEU, -3) as char), ' - ',
			cast(truncate(v.TEU, -3) + 999 as char), ' X ', count(Name))
			)
		from
			vessel2 v
		group by
			truncate(v.TEU, -3)
 </select>
 <select id="selYear5CmbList" resultType="String">		
		/* Year 5 */
		
		select
			concat(
			concat(min(v.Year), '-', max(v.year)), ',',
			cast(concat(min(v.Year), ' - ', max(v.year)) as char), ' X ', count(Name)
			)
		from
			vessel2 v
		group by
			floor(v.Year / 5);
	</select>

[ SQL実行結果例 ]

0-999,0 – 999 X 545
1000-1999,1000 – 1999 X 411
2000-2999,2000 – 2999 X 165
3000-3999,3000 – 3999 X 67
4000-4999,4000 – 4999 X 287
5000-5999,5000 – 5999 X 154
6000-6999,6000 – 6999 X 118
7000-7999,7000 – 7999 X 28
8000-8999,8000 – 8999 X 229
9000-9999,9000 – 9999 X 135
10000-10999,10000 – 10999 X 83
11000-11999,11000 – 11999 X 38
12000-12999,12000 – 12999 X 14
13000-13999,13000 – 13999 X 140
14000-14999,14000 – 14999 X 78
15000-15999,15000 – 15999 X 19
16000-16999,16000 – 16999 X 9
17000-17999,17000 – 17999 X 6
18000-18999,18000 – 18999 X 26
19000-19999,19000 – 19999 X 29
20000-20999,20000 – 20999 X 31
21000-21999,21000 – 21999 X 8

[ selectタグ例 (multiple未適用) ]