1-2. 글쓰기에 따른 회원의 point 점수 증가시키기
-- 글쓰기와 point 증가를 분리하여 따로 생성한다.
(Eclipse)InterBoardDAO
int memberPointUpdate(String userid, int point);
(Eclipse)InterBoardDAO
@Override
public int memberPointUpdate(String userid, int point) {
int n = 0;
try {
// singleton 패턴에 입각한 수동 commit Connection 객체 얻어오기
conn = MyDBConnection.getConn();
String sql = " update jdbc_tbl_member set point = point + ? "
+ " where userid = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, point);
pstmt.setString(2, userid);
n = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
return n;
}
(Eclipse)InterBoardDAO
void commti();
void rollback();
commit
(Eclipse)BoardDAO
@Override
public void commti() {
conn = MyDBConnection.getConn();
try {
conn.commit();
} catch (SQLException e) { }
}
rollback
(Eclipse)BoardDAO
@Override
public void rollback() {
conn = MyDBConnection.getConn();
try {
conn.rollback();
} catch (SQLException e) {
}
}
-- catch에 아무것도 보여주지 않고 형식만 맞춰 주면 된다.
(Eclipse)BoardCtrl
int n1 = boardDao.boardWrite(bdto);
int n2 = 0;
if (n1 == 1) {
// 글쓰기가 성공되었다면 글쓴 사용자에게 포인트 10점을 증가시켜 주겠다.
n2 = boardDao.memberPointUpdate(userid, 10);
if (n2 == 0) {
boardDao.rollback();
} else {
do {
System.out.print("▷ 확인[Y]/취소[N] => ");
String yn = sc.nextLine();
if ("Y".equals(yn) || "y".equals(yn)) {
boardDao.commti(); // 글쓰기와 회원의 point 증가를 모두 commit
n = 1;
break;
}
else if ("N".equals(yn) || "n".equals(yn)) {
boardDao.rollback(); // 글쓰기와 회원의 point 증가를 모두 rollback
n = 0;
break;
}
else {
System.out.println(">> Y 또는 N만 입력하세요. <<");
}
} while (true);
}
}
return n;
-- 글쓰기를 한 후 데이터베이스 상에 포인트가 올랐음에도 이클립스에서 내 정보 보기를 했을 때 포인트가 증가되지 않은 현상 발생
(Eclipse)InterMemberCtrl
MemberDTO selectOneMemberByUserid(String userid);
(Eclipse)MemberCtrl
@Override
public MemberDTO selectOneMemberByUserid(String userid) {
MemberDTO mbrdto = null;
mbrdto = mbrdao.selectOneMemberByUserid(userid);
return mbrdto;
}
(Eclipse)InterMemberDAO
MemberDTO selectOneMemberByUserid(String userid);
(Eclipse)MemberDAO
@Override
public MemberDTO selectOneMemberByUserid(String parauserid) {
MemberDTO mbrdto = null;
try {
conn = MyDBConnection.getConn();
String sql = "select no, userid, passwd, name, address\n"
+ " , substr(birthday, 1, 4) || '-' || substr(birthday, 5, 2) || '-' || substr(birthday, 7) AS birthday\n"
+ " , to_char(registerday, 'yyyy-mm-dd hh24:mi:ss') AS registerday\n" + " , gender, point\n"
+ "from jdbc_tbl_member\n" + "where status = 1 and\n" + " userid = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, parauserid);
rs = pstmt.executeQuery();
if (rs.next()) {
int no = rs.getInt("no");
String userid = rs.getString("userid");
String passwd = rs.getString("passwd");
String name = rs.getString("name");
String address = rs.getString("address");
String birthday = rs.getString("birthday");
String registerday = rs.getString("registerday");
int gender = rs.getInt("gender");
int point = rs.getInt("point");
mbrdto = new MemberDTO();
mbrdto.setNo(no);
mbrdto.setUserid(userid);
mbrdto.setPasswd(passwd);
mbrdto.setName(name);
mbrdto.setAddress(address);
mbrdto.setBirthday(birthday);
mbrdto.setRegisterday(registerday);
mbrdto.setGender(gender);
mbrdto.setPoint(point);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return mbrdto;
}
(Eclipse)MemberNBoardMainApp
case "4":
if (loginuser == null)
System.out.println(">> 먼저 로그인 하세요. <<\n");
else
loginuser = mbrCtrl.selectOneMemberByUserid(loginuser.getUserid());
System.out.println(loginuser.showMemberInfo());
break;
2. 글 목록 조회
(Eclipse)MemberNBoardMainApp
case "2": // 글 목록 조회
List<BoardDTO> boardList = boardCtrl.selectAllBoardTitle();
System.out.println("-----------------------------------------------");
System.out.println("글번호 글쓴이 글제목 조회수 작성일자");
System.out.println("-----------------------------------------------");
for(BoardDTO bdto : boardList) {
System.out.println(bdto);
}
break;
(Eclipse)BoardDTO
private MemberDTO mbrdto;
public MemberDTO getMbrdto() {
return mbrdto;
}
public void setMbrdto(MemberDTO mbrdto) {
this.mbrdto = mbrdto;
}
(Eclipse)BoardDAO
@Override
public List<BoardDTO> selectAllBoardTitle() {
List<BoardDTO> boardList = null;
try {
// singleton 패턴에 입각한 수동 commit Connection 객체 얻어오기
conn = MyDBConnection.getConn();
// 글번호, 글쓴이(name), 글제목, 글조회수, 작성일자
String sql = "select B.no, M.name, B.title, B.readcount\n"+
" , writeday\n"+
"from jdbc_tbl_board B join jdbc_tbl_member m\n"+
"on B.fk_userid = M.userid\n"+
"where B.status = 1\n"+
"order by B.no desc";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
int cnt = 0;
while(rs.next()) {
cnt ++;
if(cnt == 1)
boardList = new ArrayList<BoardDTO>();
int no = rs.getInt("no");
String name = rs.getString("name");
String title = rs.getString("title");
int readcount = rs.getInt("readcount");
java.sql.Date writeday = rs.getDate("writeday");
BoardDTO bdto = new BoardDTO();
bdto.setNo(no);
MemberDTO mbrdto = new MemberDTO();
mbrdto.setName(name);
bdto.setMbrdto(mbrdto);
bdto.setTitle(title);
bdto.setReadcount(readcount);
bdto.setWriteday(writeday);
boardList.add(bdto);
} // end of while --------------
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
return boardList;
}
-- BoardDTO에 toString 재정의
(Eclipse)BoardDTO
@Override
public String toString() {
String info = no + " " + mbrdto.getName() + " " + title + " " + readcount+ " " + writeday;
return info;
}
글 목록에서 특정 글 번호에 해당하는 글 내용 보여주기
(Eclipse)MemberNBoardMainApp case "11" 의 case "2"
do {
System.out.print("\n▷ 조회할 글번호 => ");
String strNO = sc.nextLine();
try {
int no = Integer.parseInt(strNO);
System.out.println("\n ==== 내용물 ====");
if(contents != null)
System.out.println(contents + "\n");
else
System.out.println(">> 글번호 "+strNO+"에 해당하는 글은 존재하지 않습니다.\n");
break;
} catch (NumberFormatException e) {
System.out.println(">> 숫자만 입력하세요. <<");
}
} while (true);
-- strNO를 String으로 지정하면 try ~ catch를 사용하지 않아도 된다.
(Eclipse)InterBoardCtrl
String showContents(int no);
(Eclipse)BoardCtrl
@Override
public String showContents(int no) {
String contents = null;
contents = boardDao.showContents(no);
return contents;
}
(Eclipse)InterBaordDAO
String showContents(int no);
(Eclipse)BoardDAO
@Override
public String showContents(int no) {
String contents = null;
conn = MyDBConnection.getConn();
try {
String sql = "select contents\n"+
"from jdbc_tbl_board\n"+
"where no = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, no);
rs = pstmt.executeQuery();
while(rs.next()) {
contents = rs.getString("contents");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
return contents;
}
조회할 글번호에서 엔터를 입력하면 빠져나오기
(Eclipse)MemberNBoardMainApp case "11" 의 case "2"의 do~while
if("".equals(strNO))
break;
글을 조회했을 때 조회수 1 증가시키기
BoardCtrl
if (contents != null)
boardDao.updateReadCount(no)
InterBoardDAO
void updateReadCount(int no);
BoardDAO
@Override
public void updateReadCount(int no) {
try {
// singleton 패턴에 입각한 수동 commit Connection 객체 얻어오기
conn = MyDBConnection.getConn();
String sql = " update jdbc_tbl_board set readcount = readcount + 1 "
+ " where no = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, no);
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
}
문제▷▷ 다른 사람이 글을 조회했을 때만 조회수 1 증가시키기
InterBoardCtrl
String showContents(int no, String userid);
BoardCtrl
@Override
public String showContents(int no, String userid) {
String contents = null;
contents = boardDao.showContents(no);
if (contents != null)
boardDao.updateReadCount(no, userid); // 글 조회수 1 증가를 한다.
return contents;
}
BoardDAO
@Override
public void updateReadCount(int no, String userid) {
try {
conn = MyDBConnection.getConn();
String sql = " update jdbc_tbl_board set readcount = readcount + 1 "
+ " where no = ? and fk_userid != ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, no);
pstmt.setString(2, userid);
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
}
3. 글 목록 조회2(HashMap)
MemberNBoardMainApp
case "3": // 글 목록 조회2(HashMap)
List<HashMap<String, String>> mapList = boardCtrl.selectAllBoardTitle2();
System.out.println("-----------------------------------------------");
System.out.println("글번호 글쓴이 글제목 조회수 작성일자");
System.out.println("-----------------------------------------------\n");
for (Map<String, String> map: mapList) {
String info = map.get("no") + " " +
map.get("name") + " " +
map.get("title") + " " +
map.get("readcount") + " " +
map.get("writeday") +"\n";
System.out.println(info);
}
InterBoardCtrl
List<HashMap<String, String>> selectAllBoardTitle2();
BoardCtrl
@Override
public List<HashMap<String, String>> selectAllBoardTitle2() {
List<HashMap<String, String>> mapList = null;
mapList = boardDao.selectAllBoardTitle2();
return mapList;
}
BoardDAO
@Override
public List<HashMap<String, String>> selectAllBoardTitle2() {
List<HashMap<String, String>> mapList = null;
try {
conn = MyDBConnection.getConn();
// 글번호, 글쓴이(name), 글제목, 글조회수, 작성일자
String sql = "select B.no, M.name, B.title, B.readcount\n"+
" , B.writeday\n"+
"from jdbc_tbl_board B join jdbc_tbl_member m\n"+
"on B.fk_userid = M.userid\n"+
"where B.status = 1\n"+
"order by B.no desc";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
int cnt = 0;
while(rs.next()) {
cnt++;
if(cnt == 1) {
mapList = new ArrayList<HashMap<String, String>>();
}
int no = rs.getInt("no");
String name = rs.getString("name");
String title = rs.getString("title");
int readcount = rs.getInt("readcount");
java.sql.Date writeday = rs.getDate("writeday");
HashMap<String, String> map = new HashMap<String, String>();
map.put("no", String.valueOf(no));
map.put("name", name);
map.put("title", title);
map.put("readcount", String.valueOf(readcount));
map.put("wirteday", String.valueOf(writeday));
mapList.add(map);
}
6. 글 삭제하기
MemberNBoardMainApp
case "6":
mapList = boardCtrl.selectAllBoardTitle2();
n = boardCtrl.deleteBoard(loginuser.getUserid(), sc);
if(n==1)
System.out.println(">> 글삭제를 성공했습니다.");
else
System.out.println(">> 글삭제를 실패 또는 취소했습니다.");
InterBoardCtrl
int deleteBoard(String userid, Scanner sc);
BoardCtrl
@Override
public int deleteBoard(String userid, Scanner sc) {
int n = 0;
do {
System.out.print("▷ 삭제할 글번호 : ");
String strNO = sc.nextLine();
try {
int no = Integer.parseInt(strNO);
System.out.print("▷ 글암호 : ");
String writePasswd = sc.nextLine();
HashMap<String, String> paraMap = new HashMap<String, String>();
paraMap.put("userid", userid);
paraMap.put("no", String.valueOf(no));
paraMap.put("writePasswd", writePasswd);
n = boardDao.deleteBoard(paraMap);
if (n == 1) {
do {
System.out.print("▷ 확인[Y]/취소[N] => ");
String yn = sc.nextLine();
if ("Y".equals(yn) || "y".equals(yn)) {
boardDao.commit();
break;
}
else if ("N".equals(yn) || "n".equals(yn)) {
boardDao.commit();
n = 0;
break;
}
else {
System.out.println(">> Y 또는 N만 입력하세요. <<");
}
} while (true);
}
break;
} catch (NumberFormatException e) {
System.out.println(">> 숫자만 입력하세요. <<\n");
}
} while (true);
return n;
}
InterBoardDAO
int deleteBoard(HashMap<String, String> paraMap);
'수업내용' 카테고리의 다른 글
| [Day38][JavaScript] 데이터 타입 / 선택자 잡기 / window.onload (0) | 2019.10.18 |
|---|---|
| [Day34][JDBC] 글 검색 (0) | 2019.10.11 |
| [Day32][JDBC] 게시판 메뉴 / 글쓰기 / 글목록 조회 / 싱글톤 패턴 (0) | 2019.10.08 |
| [Day31][JDBC] 회원관리 및 글쓰기 / 글조회 / 글변경 / 글삭제 (0) | 2019.10.07 |
| [Day30][JDBC] 회원관리 및 글쓰기 / 글조회 / 글변경 / 글삭제 (0) | 2019.10.04 |
