사용자 이름,사원번호 검색 페이징 수정 , 검색 쿼리의 변화

사용자 이름,사원번호 검색 페이징시 총 레코드의 수를 EMP.size();로 해놔서 지정해준 10개로 출력이 되었었음.

이런 에러를 모르다가 19일 월요일에 발견

검색된 데이터의 count가 필요했음 검색하다가 또다른 페이징쿼리를 발견. 

COUNT (*) OVER () AS row_count,

참고 : http://javaexpert.tistory.com/503

페이징시 총 레코드의 값을 알고싶을때 사용할수있다.

    SELECT *
    FROM (SELECT ROW_NUMBER () OVER 
    (ORDER BY ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) AS ROW_NUMBER,
    COUNT (*) OVER () AS row_count,
    E.ENAME,E.NAME,E.DCODE,BIREDAY,
    DE.DESCRIPTION AS DEPARTMENT,
    PO.DESCRIPTION AS POSITION,
    PR.DESCRIPTION AS PRESENT
    FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E
    WHERE DE.CODE = E.DEPARTMENT AND
    PO.CODE = E.POSITION AND
    PR.CODE = E.PRESENT
    AND NAME like '%'||#{NAME}||'%')
    WHERE ROW_NUMBER BETWEEN  ${startCount} AND ${endCount}
  
 

이렇게 쓰인다고 한다... 

그래서 searchVo에 row_count 를 추가해주고  record값을 emp.get(0).getRow_count(); 이렇게 주었다.

※배열에서 하나의 값을 꺼내올때

emp.get(0).getRow_count();  이렇게 꺼내온다는것을 알았돵   // 0부터시작하기때문에  젤 처음 값 뽑으려면 0으로...

배열.get(꺼내올 배열 순서값).get꺼내올데이터();

※

검색 쿼리의 변화

-- 정렬추가하기 전 userSearchName   코드테이블 조인
    <select id="userSearchName" resultMap="EmpSearchMapper"
        parameterType="com.eco.library.dto.Emp_SearchVo">
        SELECT E.ENAME,E.NAME,E.DCODE,BIREDAY,
        DE.DESCRIPTION AS DEPARTMENT,
        PO.DESCRIPTION AS POSITION,
        PR.DESCRIPTION AS PRESENT   
        FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E 
        WHERE 
        DE.CODE = E.DEPARTMENT AND
        PO.CODE = E.POSITION AND
        PR.CODE = E.PRESENT 
        AND NAME like '%'||#{NAME}||'%' 
    </select> 
    
-- 정렬추가 후
        <select id="userSearchName" resultMap="EmpSearchMapper"
        parameterType="map">
    <!--     SELECT E.ENAME,E.NAME,E.DCODE,BIREDAY,
        DE.DESCRIPTION AS DEPARTMENT,
        PO.DESCRIPTION AS POSITION,
        PR.DESCRIPTION AS PRESENT   
        FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E 
        WHERE 
        DE.CODE = E.DEPARTMENT AND
        PO.CODE = E.POSITION AND
        PR.CODE = E.PRESENT 
        AND NAME like '%'||#{NAME}||'%'
        order by ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>  -->
 

-- 페이징 추가
        SELECT
        AAA.*
        FROM(
        SELECT
        ROW_NUMBER() OVER (
        order by ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) RNUM,
        E.ENAME,E.NAME,E.DCODE,BIREDAY,
        DE.DESCRIPTION AS DEPARTMENT,
        PO.DESCRIPTION AS POSITION,
        PR.DESCRIPTION AS PRESENT
        FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E
        WHERE
        DE.CODE = E.DEPARTMENT AND
        PO.CODE = E.POSITION AND
        PR.CODE = E.PRESENT
        AND NAME like '%'||#{NAME}||'%'
        ) AAA
        WHERE
        AAA.RNUM
        BETWEEN ${startCount} AND ${endCount} -->
        

-- 최종 페이징추가 (count추가)
        SELECT *
    FROM (SELECT ROW_NUMBER () OVER 
    (ORDER BY ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) AS ROW_NUMBER,
    COUNT (*) OVER () AS row_count,
    E.ENAME,E.NAME,E.DCODE,BIREDAY,
    DE.DESCRIPTION AS DEPARTMENT,
    PO.DESCRIPTION AS POSITION,
    PR.DESCRIPTION AS PRESENT
    FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E
    WHERE DE.CODE = E.DEPARTMENT AND
    PO.CODE = E.POSITION AND
    PR.CODE = E.PRESENT
    AND NAME like '%'||#{NAME}||'%')
    WHERE ROW_NUMBER BETWEEN  ${startCount} AND ${endCount}
  
        
        
        
    </select>

-- 정렬추가하기 전 userSearchDcode
        <select id="userSearchDcode" resultMap="EmpSearchMapper"
        parameterType="com.eco.library.dto.Emp_SearchVo">
        SELECT *
        FROM EMPLOYEE WHERE DCODE like
        '%'||#{DCODE}||'%'
 

-- 정렬추가하기 전 userSearchDcode  코드테이블 조인
        SELECT E.ENAME,E.NAME,E.DCODE,BIREDAY,
        DE.DESCRIPTION AS DEPARTMENT,
        PO.DESCRIPTION AS POSITION,
        PR.DESCRIPTION AS PRESENT   
        FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E 
        WHERE DE.CODE = E.DEPARTMENT AND
        PO.CODE = E.POSITION AND
        PR.CODE = E.PRESENT 
        AND DCODE like    '%'||#{DCODE}||'%'
 
 
    </select> -->
    
-- 정렬추가 후 
    
    <select id="userSearchDcode" resultMap="EmpSearchMapper"
        parameterType="map">
        SELECT E.ENAME,E.NAME,E.DCODE,BIREDAY,
        DE.DESCRIPTION AS DEPARTMENT,
        PO.DESCRIPTION AS POSITION,
        PR.DESCRIPTION AS PRESENT   
        FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E 
        WHERE DE.CODE = E.DEPARTMENT AND
        PO.CODE = E.POSITION AND
        PR.CODE = E.PRESENT 
        AND DCODE like    '%'||${DCODE}||'%'
        order by ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if> -->

-- 페이징 추가
        
        SELECT
        AAA.*
        FROM(
        SELECT
        ROW_NUMBER() OVER (
        order by ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) RNUM,
            E.ENAME,E.NAME,E.DCODE,BIREDAY,
        DE.DESCRIPTION AS DEPARTMENT,
        PO.DESCRIPTION AS POSITION,
        PR.DESCRIPTION AS PRESENT   
        FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E 
        WHERE DE.CODE = E.DEPARTMENT AND
        PO.CODE = E.POSITION AND
        PR.CODE = E.PRESENT 
        AND DCODE like '%'||${DCODE}||'%'
        ) AAA
        WHERE
        AAA.RNUM
        BETWEEN ${startCount} AND ${endCount} -->

 
-- 최종 페이징추가 (count추가)    SELECT *
    FROM (SELECT ROW_NUMBER () OVER 
    (ORDER BY ${sidx}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) AS ROW_NUMBER,
    COUNT (*) OVER () AS row_count,
    E.ENAME,E.NAME,E.DCODE,BIREDAY,
    DE.DESCRIPTION AS DEPARTMENT,
    PO.DESCRIPTION AS POSITION,
    PR.DESCRIPTION AS PRESENT
    FROM CODE_TABLE PO,CODE_TABLE PR, CODE_TABLE DE ,EMPLOYEE E
    WHERE DE.CODE = E.DEPARTMENT AND
    PO.CODE = E.POSITION AND
    PR.CODE = E.PRESENT
    AND DCODE like '%'||${DCODE}||'%')
    WHERE ROW_NUMBER BETWEEN  ${startCount} AND ${endCount}
        
    </select> 

-- 정렬,코드테이블조인
<select id="userDetailedSearch" resultMap="EmpSearchMapper"
        parameterType="map">
         SELECT 
        E.ENAME,E.NAME,E.DCODE,E.BIREDAY,
        DE.DESCRIPTION  AS DEPARTMENT,
        PO.DESCRIPTION  AS POSITION,
        PR.DESCRIPTION  AS PRESENT
        FROM EMPLOYEE E JOIN CODE_TABLE PO ON (PO.CODE = E.POSITION) JOIN
        CODE_TABLE PR ON (PR.CODE = E.PRESENT) JOIN
        CODE_TABLE DE ON (DE.CODE = E.DEPARTMENT)
        WHERE
        DCODE like '%'||#{DCODE}||'%'
        AND NAME like '%'||#{NAME}||'%'
        AND ENAME like '%'||#{ENAME}||'%'
        AND PRESENT like '%'||#{PRESENT}||'%'
        AND POSITION like '%'||#{POSITION}||'%'
        AND DEPARTMENT like '%'||#{DEPARTMENT}||'%'
        order by ${SIDX}
        <if test="SORD == ('asc')">ASC</if>
        <if test="SORD == ('desc')">DESC</if> 
 
        
-- 페이징추가
        SELECT
        AAA.*
        FROM(
        SELECT
        ROW_NUMBER() OVER (
        order by ${SIDX}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) RNUM,
        E.ENAME,E.NAME,E.DCODE,E.BIREDAY,
        DE.DESCRIPTION  AS DEPARTMENT,
        PO.DESCRIPTION  AS POSITION,
        PR.DESCRIPTION  AS PRESENT
        FROM EMPLOYEE E JOIN CODE_TABLE PO ON (PO.CODE = E.POSITION) JOIN
        CODE_TABLE PR ON (PR.CODE = E.PRESENT) JOIN
        CODE_TABLE DE ON (DE.CODE = E.DEPARTMENT)
        WHERE
        DCODE like '%'||#{DCODE}||'%'
        AND NAME like '%'||#{NAME}||'%'
        AND ENAME like '%'||#{ENAME}||'%'
        AND PRESENT like '%'||#{PRESENT}||'%'
        AND POSITION like '%'||#{POSITION}||'%'
        AND DEPARTMENT like '%'||#{DEPARTMENT}||'%'
        ) AAA
        WHERE
        AAA.RNUM
        BETWEEN ${startCount} AND ${endCount}
        
        
    </select>
 

-- 최종 페이징추가 (count 추가)
<select id="userDetailedSearch" resultMap="EmpSearchMapper"
        parameterType="map">
    SELECT *
    FROM (SELECT ROW_NUMBER () OVER 
    (order by ${SIDX}
        <if test="sord == ('asc')">ASC</if>
        <if test="sord == ('desc')">DESC</if>
        ) AS ROW_NUMBER,
    COUNT (*) OVER () AS row_count,
        E.ENAME,E.NAME,E.DCODE,E.BIREDAY,
        DE.DESCRIPTION  AS DEPARTMENT,
        PO.DESCRIPTION  AS POSITION,
        PR.DESCRIPTION  AS PRESENT
        FROM EMPLOYEE E JOIN CODE_TABLE PO ON (PO.CODE = E.POSITION) JOIN
        CODE_TABLE PR ON (PR.CODE = E.PRESENT) JOIN
        CODE_TABLE DE ON (DE.CODE = E.DEPARTMENT)
        WHERE
        DCODE like '%'||#{DCODE}||'%'
        AND NAME like '%'||#{NAME}||'%'
        AND ENAME like '%'||#{ENAME}||'%'
        AND PRESENT like '%'||#{PRESENT}||'%'
        AND POSITION like '%'||#{POSITION}||'%'
        AND DEPARTMENT like '%'||#{DEPARTMENT}||'%')
    WHERE ROW_NUMBER BETWEEN ${startCount} AND ${endCount}
        
    </select>