mybatis 검색.->totalCount 바꾸기.

	<select id="getListWithPaging" resultType="org.moominzero.domain.BoardVO">
		<![CDATA[select R1.* FROM(
			SELECT * FROM tbl_board 
     		   order by bno desc     	
				) R1  ]]>
			 	<trim prefix="where (" suffix=")">
				<foreach collection="typeArr" item="type" separator="OR">
					<if test="type=='T'.toString()">
					title like concat('%',#{keyword},'%')
					</if>		
					<if test="type=='C'.toString()">
					content like concat('%',#{keyword},'%')
					</if>	
					<if test="type=='W'.toString()">
					writer like concat('%',#{keyword},'%')
					</if>			
				</foreach>
				</trim> 
				<include refid="criteria"></include>
				<![CDATA[
				
			LIMIT #{amount} OFFSET #{skipCount}]]>
	
	</select>

select R1.* FROM(
SELECT * FROM tbl_board  
         order by bno desc         
) R1 LIMIT 10 OFFSET 0;

 

검색추가시

 

- select R1.* FROM( SELECT * FROM tbl_board order by bno desc ) R1 where ( title like concat('%','%새로%','%') 
OR content like concat('%','%새로%','%') OR writer like concat('%','%새로%','%') ) LIMIT 10 OFFSET 

 

 

 

select R1.* FROM(
SELECT * FROM tbl_board  
         order by bno desc         
) R1 where (title like '%test%' or content like '%test%' ) LIMIT 10 OFFSET 0;

 

 

-> trim으로 where ( 를 prefix, )를 suffix로 잡았다.

 

검색시 totalcount 갯수도 바뀌게된다.

 

우선 sql Fragment

include를 하자

<!-- sql조각선언 -->
	<sql id="criteria">
			<trim prefix="where (" suffix=")">
				<foreach collection="typeArr" item="type" separator="OR">
					<if test="type=='T'.toString()">
					title like concat('%',#{keyword},'%')
					</if>		
					<if test="type=='C'.toString()">
					content like concat('%',#{keyword},'%')
					</if>	
					<if test="type=='W'.toString()">
					writer like concat('%',#{keyword},'%')
					</if>			
				</foreach>
				</trim>
	</sql>

 

<select id="getListWithPaging" resultType="org.moominzero.domain.BoardVO">
		<![CDATA[select R1.* FROM(
			SELECT * FROM tbl_board 
     		   order by bno desc     	
				) R1  ]]>		
				<include refid="criteria"></include>
				<![CDATA[
				
			LIMIT #{amount} OFFSET #{skipCount}]]>
	
	</select>

 

 

이제 검색시 total Count 해보자.

select count(*) from tbl_board where bno>0;
select count(*) from tbl_board where (title like '%새로%' )and bno>0;

 

검색 조건이 추가시 => 원래 붙는 where을 없애주고 마지막에 and를 붙여준다.

<select id="getTotalCount" resultType ="int">
		select count(*) from tbl_board where
		<trim prefixOverrides="where" suffix="and"><include refid="criteria"></include></trim> bno >0
	</select>

prefixOveerides 로 where 선언과 suffix로 and를 선언해주었다.

 

 

 

이제 테스트

	@Test
	public void testTotalCount() {
		Criteria cri = new Criteria();
		cri.setType("TCW");
		cri.setKeyword("%새로%");
		int count =mapper.getTotalCount(cri);
	
		log.info(count);
	}

 

select count(*) from tbl_board where ( title like concat('%','%새로%','%') OR content like concat('%','%새로%','%') 
OR writer like concat('%','%새로%','%') ) and bno >0

 

 

정상작동한다!