빨리 올라오거라
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<!-- 총 게시물 수 -->
<select id="boardListCount" parameterType="com.icia.web.model.HiBoard" resultType="Long">
SELECT
COUNT(A.HIBBS_SEQ) AS CNT
FROM
TBL_HIBOARD A, TBL_USER B
WHERE
B.USER_ID = A.USER_ID
<if test='searchType != null and searchType != "" and searchValue != null and searchValue != ""'>
<choose>
<when test='searchType == "1"'>
AND
B.USER_NAME LIKE '%' || #{searchValue} || '%'
</when>
<when test='searchType == "2"'>
AND
A.HIBBS_TITLE LIKE '%' || #{searchValue} || '%'
</when>
<when test='searchType == "3"'>
AND
DBMS_LOB.INSTR(A.HIBBS_CONTENT, #{searchValue}) > 0
</when>
</choose>
</if>
</select>
1
2
//게시물 총수
public long boardListCount(HiBoard hiBoard);
page
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
INSERT INTO TBL_HIBOARD (
HIBBS_SEQ,
USER_ID,
HIBBS_GROUP,
HIBBS_ORDER,
HIBBS_INDENT,
HIBBS_TITLE,
HIBBS_CONTENT,
HIBBS_READ_CNT,
REG_DATE,
HIBBS_PARENT
) VALUES (
#{hiBbsSeq},
#{userId},
#{hiBbsSeq},
#{hiBbsOrder},
#{hiBbsIndent},
#{hiBbsTitle},
#{hiBbsContent},
0,
SYSDATE,
0
)
</insert>
HIBBS SEQ 와 HIBBS GROUP이있는데
메인글 순서로 나와야하기때문에
HIBBS GROUP 으로 디센딩한다음
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT
A.HIBBS_SEQ AS HIBBS_SEQ,
NVL(A.USER_ID, '') AS USER_ID,
NVL(B.USER_NAME, '') AS USER_NAME,
NVL(B.USER_EMAIL, '') AS USER_EMAIL,
NVL(A.HIBBS_GROUP, 0) AS HIBBS_GROUP,
NVL(A.HIBBS_ORDER, 0) AS HIBBS_ORDER,
NVL(A.HIBBS_INDENT, 0) AS HIBBS_INDENT,
NVL(A.HIBBS_TITLE, '') AS HIBBS_TITLE,
NVL(A.HIBBS_CONTENT, '') AS HIBBS_CONTENT,
NVL(A.HIBBS_READ_CNT, 0) AS HIBBS_READ_CNT,
NVL(TO_CHAR(A.REG_DATE, 'YYYY.MM.DD HH24:MI:SS'), '') AS REG_DATE,
NVL(A.HIBBS_PARENT, 0) AS HIBBS_PARENT
FROM
TBL_HIBOARD A, TBL_USER B
WHERE
B.USER_ID = A.USER_ID
AND
B.USER_NAME LIKE '%한%'
AND
A.HIBBS_TITLE LIKE '%제%'
AND
DBMS_LOB.INSTR(A.HIBBS_CONTENT, '제') > 0
ORDER BY A.HIBBS_GROUP DESC, A.HIBBS_ORDER ASC
여기서
1
ORDER BY A.HIBBS_GROUP DESC, A.HIBBS_ORDER ASC
여기부분을 잘봐야한다
ROWNUM이 실행된다음 ORDERBY가 실행된다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT
HIBBS_SEQ,
USER_ID,
USER_NAME,
USER_EMAIL,
HIBBS_GROUP,
HIBBS_ORDER,
HIBBS_INDENT,
HIBBS_TITLE,
HIBBS_CONTENT,
HIBBS_READ_CNT,
REG_DATE,
HIBBS_PARENT
FROM(SELECT A.HIBBS_SEQ AS HIBBS_SEQ,
NVL(A.USER_ID, '') AS USER_ID,
NVL(B.USER_NAME, '') AS USER_NAME,
NVL(B.USER_EMAIL, '') AS USER_EMAIL,
NVL(A.HIBBS_GROUP, 0) AS HIBBS_GROUP,
NVL(A.HIBBS_ORDER, 0) AS HIBBS_ORDER,
NVL(A.HIBBS_INDENT, 0) AS HIBBS_INDENT,
NVL(A.HIBBS_TITLE, '') AS HIBBS_TITLE,
NVL(A.HIBBS_CONTENT, '') AS HIBBS_CONTENT,
NVL(A.HIBBS_READ_CNT, 0) AS HIBBS_READ_CNT,
NVL(TO_CHAR(A.REG_DATE, 'YYYY.MM.DD HH24:MI:SS'), '') AS REG_DATE,
NVL(A.HIBBS_PARENT, 0) AS HIBBS_PARENT
FROM
TBL_HIBOARD A, TBL_USER B
WHERE
B.USER_ID = A.USER_ID
AND
B.USER_NAME LIKE '%한%'
AND
A.HIBBS_TITLE LIKE '%제%'
AND
DBMS_LOB.INSTR(A.HIBBS_CONTENT, '제') > 0
ORDER BY A.HIBBS_GROUP DESC, A.HIBBS_ORDER ASC);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT
ROWNUM AS RNUM,
HIBBS_SEQ,
USER_ID,
USER_NAME,
USER_EMAIL,
HIBBS_GROUP,
HIBBS_ORDER,
HIBBS_INDENT,
HIBBS_TITLE,
HIBBS_CONTENT,
HIBBS_READ_CNT,
REG_DATE,
HIBBS_PARENT
FROM(SELECT A.HIBBS_SEQ AS HIBBS_SEQ,
NVL(A.USER_ID, '') AS USER_ID,
NVL(B.USER_NAME, '') AS USER_NAME,
NVL(B.USER_EMAIL, '') AS USER_EMAIL,
NVL(A.HIBBS_GROUP, 0) AS HIBBS_GROUP,
NVL(A.HIBBS_ORDER, 0) AS HIBBS_ORDER,
NVL(A.HIBBS_INDENT, 0) AS HIBBS_INDENT,
NVL(A.HIBBS_TITLE, '') AS HIBBS_TITLE,
NVL(A.HIBBS_CONTENT, '') AS HIBBS_CONTENT,
NVL(A.HIBBS_READ_CNT, 0) AS HIBBS_READ_CNT,
NVL(TO_CHAR(A.REG_DATE, 'YYYY.MM.DD HH24:MI:SS'), '') AS REG_DATE,
NVL(A.HIBBS_PARENT, 0) AS HIBBS_PARENT
FROM
TBL_HIBOARD A, TBL_USER B
WHERE
B.USER_ID = A.USER_ID
AND
B.USER_NAME LIKE '%한%'
AND
A.HIBBS_TITLE LIKE '%제%'
AND
DBMS_LOB.INSTR(A.HIBBS_CONTENT, '제') > 0
ORDER BY A.HIBBS_GROUP DESC, A.HIBBS_ORDER ASC);
이거 순서를 잘보자 ROWNUM이 가장
페이징하는거 SQL문을 완벽하게 할줄아야한다 ROWNUM이 가져오는 시점은 가장 바깥쪽에서
ROWNUM은 WEHER절까지만 데이터를 가져온다
그후 올려온 순서데로
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
HiboardDao xml 수정
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<!-- 게시물 리스트-->
<select id="boardList" parameterType="com.icia.web.model.HiBoard" resultMap="hiBoardResultMap">
SELECT
HIBBS_SEQ,
USER_ID,
USER_NAME,
USER_EMAIL,
HIBBS_GROUP,
HIBBS_ORDER,
HIBBS_INDENT,
HIBBS_TITLE,
HIBBS_CONTENT,
HIBBS_READ_CNT,
REG_DATE,
HIBBS_PARENT
FROM (SELECT ROWNUM AS RNUM,
HIBBS_SEQ,
USER_ID,
USER_NAME,
USER_EMAIL,
HIBBS_GROUP,
HIBBS_ORDER,
HIBBS_INDENT,
HIBBS_TITLE,
HIBBS_CONTENT,
HIBBS_READ_CNT,
REG_DATE,
HIBBS_PARENT
FROM(SELECT A.HIBBS_SEQ AS HIBBS_SEQ,
NVL(A.USER_ID, '') AS USER_ID,
NVL(B.USER_NAME, '') AS USER_NAME,
NVL(B.USER_EMAIL, '') AS USER_EMAIL,
NVL(A.HIBBS_GROUP, 0) AS HIBBS_GROUP,
NVL(A.HIBBS_ORDER, 0) AS HIBBS_ORDER,
NVL(A.HIBBS_INDENT, 0) AS HIBBS_INDENT,
NVL(A.HIBBS_TITLE, '') AS HIBBS_TITLE,
NVL(A.HIBBS_CONTENT, '') AS HIBBS_CONTENT,
NVL(A.HIBBS_READ_CNT, 0) AS HIBBS_READ_CNT,
NVL(TO_CHAR(A.REG_DATE, 'YYYY.MM.DD HH24:MI:SS'), '') AS REG_DATE,
NVL(A.HIBBS_PARENT, 0) AS HIBBS_PARENT
FROM
TBL_HIBOARD A, TBL_USER B
WHERE
B.USER_ID = A.USER_ID
<if test='searchType != null and searchType != "" and searchValue != null and searchValue != ""'>
<choose>
<when test='searchType == "1"'>
AND
B.USER_NAME LIKE '%'|| #{searchValue} ||'%'
</when>
<when test='searchType == "2"'>
AND
A.HIBBS_TITLE LIKE '%'|| #{searchValue} ||'%'
</when>
<when test='searchType == "3"'>
AND
DBMS_LOB.INSTR(A.HIBBS_CONTENT, #{searchValue}) > 0
</when>
</choose>
</if>
ORDER BY A.HIBBS_GROUP DESC, A.HIBBS_ORDER ASC))
WHERE RNUM <![CDATA[>=]]> #{startRow}
AND RNUM <![CDATA[<=]]> #{endRow}
</select>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Repository("hiBoardDao")
public interface HiBoardDao
{
//게시물 등록
public int boardInsert(HiBoard hiBoard);
//게시물 첨부파일 등록
public int boardFileInsert(HiBoardFile hiBoardFile);
//게시물 총수
public long boardListCount(HiBoard hiBoard);
//게시물 리스트
public List<HiBoard> boardList(HiBoard hiBoard);
}
java.util 리스트를 임포트해준다
HiboardService boardList 메서드 추가
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//게시물 리스트
public List<HiBoard> boardList(HiBoard hiBoard)
{
List<HiBoard> list = null;
try
{
list = hiBoardDao.boardList(hiBoard);
}
catch(Exception e)
{
logger.error("[HiBoardService] boardList Exception", e);
}
return list;
}
HiboardController 한줄 추가
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
if(totalCount > 0)
{
paging = new Paging("/board/list", totalCount, LIST_COUNT, PAGE_COUNT, curPage, "curPage");
paging.addParam("searchType", searchType);
paging.addParam("searchValue", searchValue);
paging.addParam("curPage", curPage);
search.setStartRow(paging.getStartRow());
search.setEndRow(paging.getEndRow());
list = hiBoardService.boardList(search); 여기부분을 추가한다
}
model.addAttribute("list", list); 여기부분을 추가한다
model.addAttribute("searchType", searchType); 여기부분을 추가한다
model.addAttribute("searchValue", searchValue); 여기부분을 추가한다
model.addAttribute("curPage", curPage); 여기부분을 추가한다
model.addAttribute("paging", paging); 여기부분을 추가한다
return "/board/list";
}