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