2024-02-13

<mapper namespace="lprod">	
	<!-- map{"keyword":"" , "currentPage":1} -->
	<sql id="where">
		<if test="keyword!=null and keyword!=''">
			AND    (
					LPROD_GU LIKE '%' || #{keyword} || '%'
				OR  LPROD_NM LIKE '%' || #{keyword} || '%'
			)
		</if>
	</sql>
	
	<!-- map{"keyword":"" , "currentPage":1} -->
	<select id="list" resultType="lprodVO">
		SELECT LPROD_ID, LPROD_GU, LPROD_NM
		FROM   LPROD
		WHERE  1 = 1
		<include refid="where"></include>		
	</select>

 

sql로 선언한 쿼리문내의 id를 찾아서 include한다(where 절을 공통으로 선언해서 id값으로 삽입)

 

인라인뷰(서브쿼리가 FROM절 안에서 사용하는 경우)

서브쿼리의 종류와 각각의 차이점?(서브쿼리 , 스칼라 서브쿼리 , 인라인뷰)

select ROW_NUMBER() OVER (ORDER BY F.LPROD_ID DESC) RNUM , F.*
from (
SELECT LPROD_ID, LPROD_GU, LPROD_NM
      FROM   LPROD
      WHERE  1 = 1
      ) F;
// id순으로 정렬

 

// 페이징 처리를 위해
with T as(
select ROW_NUMBER() OVER (ORDER BY F.LPROD_ID DESC) RNUM , F.*
from (
SELECT LPROD_ID, LPROD_GU, LPROD_NM
      FROM   LPROD
      WHERE  1 = 1
      ) F
)
select T.*
from T
where t.rnum between 11 and 20;

 

LPROD_SQL.xml 쿼리문 수정

<!-- map{"keyword":"" , "currentPage":1} -->
<sql id="where">
    <if test="keyword!=null and keyword!=''">
        AND    (
                LPROD_GU LIKE '%' || #{keyword} || '%'
        OR		LPROD_NM LIKE '%' || #{keyword} || '%'
               )
    </if>
</sql>
	
<!-- map{"keyword":"" , "currentPage":1} -->
<select id="list" parameterType="hashMap" resultType="lprodVO">
    with t as(
        select ROW_NUMBER() OVER (ORDER BY F.LPROD_ID DESC) RNUM , F.*
        from (
        SELECT LPROD_ID, LPROD_GU, LPROD_NM
            FROM   LPROD
            WHERE  1 = 1
            ) F
        )
        select T.*
        from t
        where t.rnum between (#{currentPage} * 10) - ( 10 - 1 ) and (#{currentPage} * 10)
    <include refid="where"></include>		
</select>

 

LprodController의 list/listAjax메소드 수정(listAjax생략)

@RequestMapping(value="/list",method=RequestMethod.GET)
public String list(Model model, 
        @RequestParam(value="currentPage",required = false , defaultValue = "1") int currentPage ,
        @RequestBody(required=false) Map<String,Object> map) {
    String keyword = "";
    if(map!=null) {
        keyword = (String)map.get("keyword");
    } else {	//map에 keyword가 없을때(null)
        map = new HashMap<String , Object>();
        map.put("keyword" , "");
    }

    map.put("currentPage" , currentPage);

//  map{"keyword":"" , "currentPage":1};

    //1. LprodVO를 만들기
    //2. mybatisAlias.xml에 alias 추가
    //3. select
    List<LprodVO> lprodVOList = this.lprodService.list(map);
    log.info("list->lprodVOList : " + lprodVOList);

    model.addAttribute("lprodVOList", lprodVOList);

    //forwarding : jsp
    return "lprod/list";
}