냥쟝
Published 2023. 8. 12. 03:28
스프링 트랜젝션+pagenation 취준 note 2023/spring
반응형
package org.doit.ik;

import java.text.DateFormat;
import java.util.Date;
import java.util.Locale;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import lombok.extern.log4j.Log4j;
 
@Controller
@Log4j
@RequestMapping("/")
public class HomeController {
	
	public HomeController() {
		super();
	}
	
	@RequestMapping("index.htm")
	public  String home() throws Exception{
		return "index.jsp";
	}
	
	
}
package org.doit.ik.controller;

import java.io.File;
import java.io.FileInputStream;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.doit.ik.domain.NoticeVO;
import org.doit.ik.persistence.MemberShipService;
import org.doit.ik.persistence.NoticeDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

@Controller
@RequestMapping("/customer/*")
public class CustomerController {

	@Autowired
	private NoticeDao noticeDao ;
	
	
	
	@Autowired
	private MemberShipService memberShipService;
	
	// ?dir=customer/upload&file=${ notice.filesrc  }
		@RequestMapping( "/customer/download.htm")
		public void download(
				@RequestParam("dir")   String d
				, @RequestParam("file") String fname
				, HttpServletResponse response
				, HttpServletRequest request
				) throws Exception{ 

			response.setHeader("Content-Disposition","attachment;filename="+ new String(fname.getBytes(), "ISO8859_1"));		

			String fullPath = request.getServletContext().getRealPath(	d + "/" + fname);

			FileInputStream fin = new FileInputStream(fullPath);
			ServletOutputStream sout = response.getOutputStream();  
			byte[] buf = new byte[1024];
			int size = 0;
			while((size = fin.read(buf, 0, 1024)) != -1) {
				sout.write(buf, 0, size); 
			}
			fin.close();
			sout.close();

		}

	// ?seq=1&filesrc=ojdbc6.jar
	@GetMapping("/noticeDel.htm")
	public String noticeDel(@RequestParam("seq") String seq
			,@RequestParam("filesrc") String delFilesrc
			, HttpServletRequest request) throws Exception {
		// 1. 실제 업로드 경로에서 파일을 삭제
		String uploadRealPath =  request.getServletContext().getRealPath("/customer/upload");		
		File delFile = new File(uploadRealPath, delFilesrc);
		if( delFile.exists() )  delFile.delete();
		
		// 2. 테이블 레코드 삭제
		int deleteCount =  this.noticeDao.delete(seq);
		if (deleteCount == 1) {
			return "redirect:notice.htm";
		} else {
			return "redirect:noticeDetail.htm?seq="+ seq+"&error";
		} // if
	}

	// Post 방식 요청 + ("/customer/noticeEdit.htm")
		@RequestMapping(value = {"/customer/noticeEdit.htm"}, method = RequestMethod.POST)
		public String noticeEdit( 
				NoticeVO notice
				, @RequestParam("o_filesrc") String  oFilesrc 
				, HttpServletRequest request
				)  throws Exception{ 
			CommonsMultipartFile multiparftFile = notice.getFile();	 
			String uploadRealPath = null;
			if( !multiparftFile.isEmpty()  ) {  
				uploadRealPath =  request.getServletContext().getRealPath("/customer/upload"); 
				
				File delFile = new File(uploadRealPath, oFilesrc);
				if( delFile.exists()) {
					delFile.delete();
				} 
				
				String originalFilename = multiparftFile.getOriginalFilename();
				String filesystemName =  getFileNameCheck(uploadRealPath, originalFilename) ; 
				File dest = new File(uploadRealPath, filesystemName );
				multiparftFile.transferTo(dest) ;
				notice.setFilesrc(filesystemName);
			} else { 
				notice.setFilesrc(oFilesrc);
			} // 
 
			int rowCount = this.noticeDao.update(notice);
			if ( rowCount == 1 ) {   
				return  String.format( "redirect:noticeDetail.htm?seq=%s" ,  notice.getSeq() ) ;
			} else {
				return "redirect:notice.htm";
			}	

		}
	/*
	@PostMapping("/noticeEdit.htm")
	public String noticeEdit(
			NoticeVO notice
			) throws Exception {
		int updateCount = this.noticeDao.update(notice);
		if (updateCount == 1) {
			return "redirect:noticeDetail.htm?seq=" + notice.getSeq();
		} else {
			return "redirect:notice.htm";
		} // if
	}
	*/

	// ?seq=1
	@GetMapping("/noticeEdit.htm")
	public String noticeEdit(
			@RequestParam("seq") String seq
			, Model model ) throws Exception {
		NoticeVO notice = this.noticeDao.getNotice(seq);
		model.addAttribute("notice", notice);
		return "noticeEdit.jsp";
	}

	//  a_3.txt
	private String getFileNameCheck(String uploadRealPath, String originalFilename) {
		int index = 1;		
		while( true ) {			
			File f = new File(uploadRealPath, originalFilename);			
			if( !f.exists() ) return originalFilename;			
			// upload 폴더에 originalFilename 파일이 존재한다는 의미         a.txt (4자리)
			String fileName = originalFilename.substring(0, originalFilename.length() - 4 );  //   a
			String ext =  originalFilename.substring(originalFilename.length() - 4 );  // .txt
			// asdfasf-3.txt
			originalFilename = fileName+"-"+(index)+ext;

			index++;
		} // while 
	}

	// p358  컨트롤러 메서드 파마라미터 ( 커맨드 객체 사용 예 )
	//@RequestMapping(value = "/noticeReg.htm", method = RequestMethod.POST)
	@PostMapping("/noticeReg.htm")
	public String noticeReg(  NoticeVO notice
			, HttpServletRequest request ) throws Exception{
		// 1. 첨부된 파일 유무 확인 후에 서버 파일 저장.
		CommonsMultipartFile multiparftFile =    notice.getFile();

		// isEmpty() 첨부파일 유무
		// 서버에 배포했을 때의 실제 경로
		String uploadRealPath = null;
		if( !multiparftFile.isEmpty()  ) {  // 첨부 파일 있는 경우.
			// 2. 첨부 파일 저장
			// uploadRealPath =  request.getRealPath("/customer/upload");
			uploadRealPath =  request.getServletContext().getRealPath("/customer/upload");

			// File saveDir = new File(uploadRealPath);
			// if (!saveDir.exists()) saveDir.mkdirs();

			System.out.println("> uploadRealPath : " + uploadRealPath);

			// originalFilename, filesystemName
			String originalFilename = multiparftFile.getOriginalFilename();
			// a.txt
			// a_1.txt
			// a_2.txt
			String filesystemName =  getFileNameCheck(uploadRealPath, originalFilename) ;

			File dest = new File(uploadRealPath, filesystemName );
			multiparftFile.transferTo(dest); // 실제 파일 저장

			// 3. notice.filesrc
			//        테이블 :  원래파일이름,    -1 시스템저장파일이름(O)  컬럼 생성
			notice.setFilesrc(filesystemName);
		} // if 

		// 로그인 인증 - 세션 
		notice.setWriter("jieun");
		
		//int insertCount = this.noticeDao.insert(notice);
		this.noticeDao.insertAndPointUpOfMember(notice, "jieun");
		int insertCount=1;
		if (  insertCount == 1 ) {
			// redirect:   == response.sendRedirect()
			return "redirect:notice.htm";
		} else {
			return "noticeReg.jsp?error";
		}

	}

	// notice.jsp 에서 링크태그 [글쓰기] 버튼을 클릭
	//@RequestMapping(value = "/noticeReg.htm", method = RequestMethod.GET)
	@GetMapping("/noticeReg.htm")
	public String noticeReg()  throws Exception{
		return "noticeReg.jsp";
	}


	// ?seq=10
	@GetMapping("/noticeDetail.htm")
	   public String noticeDetail(
	         @RequestParam("seq") String seq
	         , Model model
	         ) throws Exception {
	      //조회수를 1증가하는 코딩
	      this.noticeDao.hitUp(seq);      
	      
	      NoticeVO notice =  this.noticeDao.getNotice(seq);  
	      model.addAttribute("notice", notice);   

	      return "noticeDetail.jsp";
	   }
	// ?page=2&field=title&query=홍길동
	@GetMapping("/notice.htm")
	public String notices(
			@RequestParam( value =  "page" , defaultValue = "1" ) int page
			, @RequestParam( value =  "field" , defaultValue = "title" ) String field
			, @RequestParam( value =  "query" , defaultValue = "" ) String query 
			, Model model) throws Exception {		 
		model.addAttribute("message", "hello world"); 		
		List<NoticeVO> list =  this.noticeDao.getNotices(page, field, query);		 
		model.addAttribute("list", list); 		
		return "notice.jsp";
	}

	/*
	// p 356
	// "컨트롤러 메서드"
	//@RequestMapping(value = {"/customer/notice.htm"})
	// @RequestMapping("/customer/notice.htm")
	@GetMapping("/customer/notice.htm")
    public ModelAndView notices(
    		  HttpServletRequest request
    		, HttpServletResponse response
    		, Model model
    		, HttpSession sesion) throws Exception {

		ModelAndView mav = new ModelAndView();
		mav.addObject("message", "hello world");
		// ?page=2&field=title&query=홍길동
		String ppage = request.getParameter("page");
		String pfield = request.getParameter("field");
		String pquery = request.getParameter("query");

		int page= 1;
		String field = "title";
		String query = "";

		if( ppage != null && !ppage.equals("") ) {  page = Integer.parseInt(ppage); }
		if( pfield != null && !pfield.equals("") ) {   field  = pfield; }
		if( pquery != null && !pquery.equals("") ) {   query  = pquery; }

		List<NoticeVO> list =  this.noticeDao.getNotices(page, field, query);		
		mav.addObject("list", list);

		mav.setViewName("notice.jsp");

		return mav;
	}
	 */

} // class
package org.doit.ik.domain;

import java.util.Date;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MemberVO {
	
	// member 테이블의 컬럼과 이름을 일치.
	// fields
	private String id;           // uid -> id 수정
	private String pwd;
	private String name;
	private String gender;
	private String birth;
	private String is_lunar; // isLunar -> is_lunar 수정
	private String cphone;  // 수정
	private String email;
	private String habit;
	private Date   regdate; // 수정
	 
	//트랜잭션 처리를 테스트 하기 위해 point 컬럼을 추가. -> 필드, getter, setter를 만든다.
	private int point;
	
	
	
} // class
package org.doit.ik.domain;

import java.util.Date;

import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class NoticeVO {
	
	// fields
	private String seq;
	private String title;
	private String writer;
	private String content;
	private Date regdate;
	private int hit; 
	private String filesrc;  // 수정
	
	// p445 참고
	// 스프링에서 지원하는 파일 업로드 기능을  (4)방법인 커맨드 객체를 이용하는 방법
	// <input type="file" id="txtFile" name="file" />
	private CommonsMultipartFile  file; 
		
} // clsss
package org.doit.ik.mapper;

public interface SampleMapper {
	
	String getTime();

}
package org.doit.ik.persistence;

import java.sql.SQLException;

import org.doit.ik.domain.MemberVO;
 
public interface MemberDao {
	
	// 회원 정보 얻어오는 메서드  
	public MemberVO getMember(String id) throws ClassNotFoundException, SQLException;
	 
	// 회원 가입 메서드 
	public int insert(MemberVO member) throws ClassNotFoundException, SQLException;
	 
}
package org.doit.ik.persistence;

import java.sql.SQLException;

import org.doit.ik.domain.MemberVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import lombok.extern.log4j.Log4j;

@Repository
@Log4j
public class MemberDaoImpl implements MemberDao{
	
	@Autowired
	private NamedParameterJdbcTemplate npJdbcTemplate;
	 
	public MemberVO getMember(String id) throws ClassNotFoundException, SQLException
	{
		String sql = " SELECT * "
				         + " FROM MEMBER "
				         + " WHERE id = :id ";
		  
		MapSqlParameterSource parameterSource = new MapSqlParameterSource();
		parameterSource.addValue("id", id );
		
		return this.npJdbcTemplate.queryForObject(sql, parameterSource, new BeanPropertyRowMapper<MemberVO>(MemberVO.class)); 
	}
	
	// 회원 가입 메서드 
	public int insert(MemberVO member) throws ClassNotFoundException, SQLException
	{
		String sql = "INSERT INTO MEMBER"
				+ "( ID, PWD, NAME, GENDER, BIRTH, IS_LUNAR, CPHONE, EMAIL, HABIT, REGDATE) "
				+ " VALUES( :id, :pwd, :name, :gender, :birth, :is_lunar, :cphone, :email, :habit , SYSDATE)";
		SqlParameterSource parameterSource = new BeanPropertySqlParameterSource( member  );			
		return this.npJdbcTemplate.update(sql,  parameterSource);
	}
}
package org.doit.ik.persistence;

import java.sql.SQLException;

import org.doit.ik.domain.NoticeVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

//@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.DEFAULT)



public interface MemberShipService {
	
	@Autowired
	
	private NoticeDao noticeDao;
	
	@Override
	
	public   void insertAndPointUpMember(NoticeVO notice, String id) throws ClassNotFoundException, SQLException {
		  insert(notice);
		  
		  noticeDao.insert(notice);
			
			 notice.setTitle(notice.getTitle()+"-2");

			  
			  
			  
			  //notice.setTitle(notice.getTitle()+"-2");
				
			 noticeDao.insert(notice);
				
		  
		  
			 
			 
	}
			 
			 
}
package org.doit.ik.persistence;

import org.springframework.beans.factory.annotation.Autowired;

public class MemberShipServiceImpl {

	
	@Autowired
	private NoticeDao noticeDao;
	
	
	
	
	
	
}
package org.doit.ik.persistence;

import java.sql.SQLException;
import java.util.List;

import org.doit.ik.domain.NoticeVO; 

 
public interface NoticeDao {	 
	
	// 검색한 결과의 총레코드 수 를 반환하는 메서드 
	public int getCount(String field, String query) throws ClassNotFoundException, SQLException;
	
	// 페이징 처리 + 공지사항 목록
	public List<NoticeVO> getNotices(int page, String field, String query) throws ClassNotFoundException, SQLException;
	
	// 공지사항 삭제
	public int delete(String seq) throws ClassNotFoundException, SQLException;
	
	// 공지사항 수정
	public int update( NoticeVO notice ) throws ClassNotFoundException, SQLException;
	
	// 해당 공지사항 상세보기. 
	public NoticeVO getNotice(String seq) throws ClassNotFoundException, SQLException;
	// 공지사항 글쓰기
	public int insert(NoticeVO notice) throws ClassNotFoundException, SQLException;

	public void insertAndPointUpOfMember(NoticeVO notice, String string); 
	
	/*
	 * // 트랜잭션 처리를 하기 위한 메서드 추가 //@Transactional public void
	 * insertAndPointUpOfMember(NoticeVO notice, String id) throws
	 * ClassNotFoundException, SQLException;
	 */
	
	
	
void hitUp(String seq);
int getHit(String seq);
	 
} // class
package org.doit.ik.persistence;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.doit.ik.domain.NoticeVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

// 객체 생성
@Repository
public class NoticeDaoImpl implements NoticeDao {

   @Autowired
   private NamedParameterJdbcTemplate npJdbcTemplate;

//   @Autowired
//   private DataSourceTransactionManager transactionManager;

   @Autowired
   private TransactionTemplate transactionTemplate;

   // 검색한 결과의 총레코드 수 를 반환하는 메서드
   public int getCount(String field, String query) throws ClassNotFoundException, SQLException {
      // 컬럼명은 ?로 못준다
      String sql = "SELECT COUNT(*) CNT " + " FROM NOTICES " + " WHERE " + field + " LIKE :query";

      MapSqlParameterSource parameterSource = new MapSqlParameterSource();
      parameterSource.addValue("query", "%" + query + "%");

      return this.npJdbcTemplate.queryForObject(sql, parameterSource, Integer.class);
   }

   // 페이징 처리 + 공지사항 목록
   public List<NoticeVO> getNotices(int page, String field, String query) throws ClassNotFoundException, SQLException {

      int srow = 1 + (page - 1) * 15; // 1, 16, 31, 46, 61, ... an = a1 + (n-1)*d
      int erow = 15 + (page - 1) * 15; // 15, 30, 45, 60, 75, ...

      String sql = " SELECT * " + "  FROM ( " + "                 SELECT ROWNUM NUM, N.* " + "                 FROM ("
            + "                          SELECT * " + "                          FROM NOTICES "
            + "                          WHERE " + field + " LIKE :query "
            + "                   ORDER BY REGDATE DESC" + "                    ) N" + "      ) "
            + " WHERE NUM BETWEEN :srow AND :erow ";

      Map<String, Object> paramMap = new HashMap<String, Object>();
      paramMap.put("query", "%" + query + "%");
      paramMap.put("srow", srow);
      paramMap.put("erow", erow);

      return this.npJdbcTemplate.query(sql, paramMap, new BeanPropertyRowMapper<NoticeVO>(NoticeVO.class));
      // query 자체가 arraylist를 돌려줌 - 돌려주는 객체 타입 정해주는게 BeanPropertyRowMapper
      // List<NoticeVO> list = this.jdbcTemplate.query(sql, new Object[] { "%" + query
      // + "%", srow, erow }, new BeanPropertyRowMapper<NoticeVO>(NoticeVO.class));

      // return list;
   }

   // 공지사항 삭제
   public int delete(String seq) throws ClassNotFoundException, SQLException {

      String sql = " DELETE  FROM NOTICES " + " WHERE SEQ = :seq ";

      MapSqlParameterSource parameterSource = new MapSqlParameterSource();
      parameterSource.addValue("seq", seq);

      return this.npJdbcTemplate.update(sql, parameterSource);
      // return this.jdbcTemplate.update(sql, seq);
   }

   // 공지사항 수정
   public int update(NoticeVO notice) throws ClassNotFoundException, SQLException {

      String sql = "UPDATE NOTICES " + " SET TITLE = :title, CONTENT = :content, FILESRC = :filesrc "
            + " WHERE SEQ = :seq ";

      SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(notice);

      /*
       * MapSqlParameterSource parameterSource = new MapSqlParameterSource();
       * parameterSource.addValue("title", notice.getTitle());
       * parameterSource.addValue("content", notice.getContent());
       * parameterSource.addValue("filesrc", notice.getFilesrc());
       * parameterSource.addValue("seq", notice.getSeq());
       */
      return this.npJdbcTemplate.update(sql, parameterSource);
      // return this.jdbcTemplate.update(sql, notice.getTitle(), notice.getContent(),
      // notice.getFilesrc(), notice.getSeq());
   }

   // 해당 공지사항 상세보기.
   public NoticeVO getNotice(String seq) throws ClassNotFoundException, SQLException {
      String sql = " SELECT * " + " FROM NOTICES " + " WHERE SEQ = :seq ";

      MapSqlParameterSource parameterSource = new MapSqlParameterSource();
      parameterSource.addValue("seq", seq);

      return this.npJdbcTemplate.queryForObject(sql, parameterSource,
            new BeanPropertyRowMapper<NoticeVO>(NoticeVO.class));
      // return this.jdbcTemplate.queryForObject(sql, new Object[] { seq }, new
      // BeanPropertyRowMapper<NoticeVO>(NoticeVO.class));
   }

   // 공지사항 글쓰기
   /*
    * public int insert(NoticeVO notice) throws ClassNotFoundException,
    * SQLException {
    * 
    * String sql = "INSERT INTO NOTICES" +
    * " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES" +
    * " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)"
    * ;
    * 
    * SqlParameterSource parameterSource = new
    * BeanPropertySqlParameterSource(notice);
    * 
    * return this.npJdbcTemplate.update(sql, parameterSource); // return
    * this.jdbcTemplate.update(sql, notice.getTitle(), notice.getContent(),
    * notice.getWriter(), notice.getFilesrc()); }
    */

   @Override
   @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.DEFAULT)
   public int insert(NoticeVO notice) throws ClassNotFoundException, SQLException {
      // A. 공지사항 쓰기
      String sql = "INSERT INTO NOTICES" + " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES"
            + " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)";

      SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(notice);
      int insertCount = this.npJdbcTemplate.update(sql, parameterSource);

      // B. 포인트 증가
      sql = " UPDATE member " + " SET point = point + 1 " + " WHERE id = :id ";

      MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
      mapSqlParameterSource.addValue("id", "동절레");

      int updateCount = this.npJdbcTemplate.update(sql, mapSqlParameterSource);

      return updateCount;
   }



/*   @Override
   @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.DEFAULT)
   public void insertAndPointUpOfMember(NoticeVO notice, String id) throws ClassNotFoundException, SQLException {

      insert(notice);
      notice.setTitle(notice.getTitle()  + "- 2");
      insert(notice);
*/
      /*
       * // 어노테이션 사용 - 트랜잭션처리 String sql = "INSERT INTO NOTICES" +
       * " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES" +
       * " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)"
       * ;
       * 
       * SqlParameterSource parameterSource = new
       * BeanPropertySqlParameterSource(notice);
       * 
       * int insertCount = this.npJdbcTemplate.update(sql, parameterSource);
       * 
       * 
       * //포인트 증가 작업 sql = " update member set point = point + 1 where id = :id ";
       * MapSqlParameterSource paramSource = new MapSqlParameterSource();
       * paramSource.addValue("id", id); int updateCount =
       * this.npJdbcTemplate.update(sql, paramSource);
       */

      /*
       * 선언적 트랜잭션 처리 + 설정 파일(tx:advice 처리) String sql = "INSERT INTO NOTICES" +
       * " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES" +
       * " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)"
       * ;
       * 
       * SqlParameterSource parameterSource = new
       * BeanPropertySqlParameterSource(notice);
       * 
       * int insertCount = this.npJdbcTemplate.update(sql, parameterSource);
       * 
       * 
       * //포인트 증가 작업 sql = " update member set point = point + 1 where id = :id ";
       * MapSqlParameterSource paramSource = new MapSqlParameterSource();
       * paramSource.addValue("id", id); int updateCount =
       * this.npJdbcTemplate.update(sql, paramSource);
       */

      /*
       * //공지사항 쓰기 String sql = "INSERT INTO NOTICES" +
       * " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES" +
       * " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)"
       * ;
       * 
       * SqlParameterSource parameterSource = new
       * BeanPropertySqlParameterSource(notice);
       * 
       * int insertCount = this.npJdbcTemplate.update(sql, parameterSource);
       * 
       * 
       * //포인트 증가 작업 sql = " update member set point = point + 1 where id = :id ";
       * MapSqlParameterSource paramSource = new MapSqlParameterSource();
       * paramSource.addValue("id", id); int updateCount =
       * this.npJdbcTemplate.update(sql, paramSource);
       */

      // 트랜잭션 매니저 등록
      /*
       * String sql = "INSERT INTO NOTICES" +
       * " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES" +
       * " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)"
       * ; String sql2 = " update member set point = point + 1 where id = :id ";
       * TransactionDefinition definition = new DefaultTransactionDefinition();
       * TransactionStatus status =
       * this.transactionManager.getTransaction(definition); try { //공지사항 쓰기
       * SqlParameterSource parameterSource = new
       * BeanPropertySqlParameterSource(notice); int insertCount =
       * this.npJdbcTemplate.update(sql, parameterSource); //포인트 증가 작업
       * MapSqlParameterSource paramSource = new MapSqlParameterSource();
       * paramSource.addValue("id", id); int updateCount =
       * this.npJdbcTemplate.update(sql2, paramSource); //커밋
       * this.transactionManager.commit(status); } catch (Exception e) { //롤백
       * this.transactionManager.rollback(status); } }
       */
      // 트랜잭션 템블릿 사용
      /*
       * String sql = "INSERT INTO NOTICES" +
       * " ( SEQ, TITLE, CONTENT, WRITER, REGDATE, HIT, FILESRC) " + " VALUES" +
       * " ( (SELECT MAX(TO_NUMBER(SEQ))+1 FROM NOTICES), :title, :content, :writer, SYSDATE, 0, :filesrc)"
       * ; String sql2 = " update member set point = point + 1 where id = :id ";
       * 
       * this.transactionTemplate.execute(new TransactionCallbackWithoutResult() {
       * 
       * @Override protected void doInTransactionWithoutResult(TransactionStatus
       * status) { SqlParameterSource parameterSource = new
       * BeanPropertySqlParameterSource(notice); try { int insertCount =
       * npJdbcTemplate.update(sql, parameterSource); //포인트 증가 작업
       * MapSqlParameterSource paramSource = new MapSqlParameterSource();
       * paramSource.addValue("id", id); int updateCount = npJdbcTemplate.update(sql2,
       * paramSource);
       * 
       * } catch (Exception e) { // TODO: handle exception }
       * 
       * } });
       */

 //조회수 증가 메소드
   @Override
   @Transactional
   public void hitUp(String seq) {
      String sql = " UPDATE notices "
            +" SET hit = hit + 1 "
            + " WHERE seq=:seq ";
      MapSqlParameterSource paramSource = new MapSqlParameterSource();
      paramSource.addValue("seq", "seq");   
      this.npJdbcTemplate.update(sql, paramSource);
      
   }
   
   @Override
   //@Transactional(isolation = Isolation.DEFAULT) 
   //격리레벨을 default를 주겠다는 것은 DBMS(오라클) 격리수준을 그대로 유지하겠다.
   //@Transactional(isolation = Isolation.READ_COMMITTED)
   //commit된 것만 읽겠다.=> 커밋을 한 후에 처리를 하겠다.(반드시, 커밋된것만 읽어감)
   //@Transactional(isolation = Isolation.READ_UNCOMMITTED)
   //커밋이 되지 않아도 읽어가겠다.(커밋이 안된상황도 읽어감)
   //@Transactional(isolation = Isolation.REPEATABLE_READ)
   // 
   //@Transactional(isolation = Isolation.SERIALIZABLE)
   //팬텀리더 상황 X
   public int getHit(String seq) {
      String sql = "SELECT hit "
            + " FROM notices "
            + " WHERE seq = :seq ";
      Map<String, Object> paramMap = new HashMap<String, Object>();
      paramMap.put("seq", seq);
      return this.npJdbcTemplate.queryForObject(sql, paramMap, Integer.class);
   }
   
   } // insertAndPointUpOfMember

 // class

'취준 note 2023 > spring' 카테고리의 다른 글

스프링 핵심내용정리  (1) 2023.09.28
스프링AOP  (0) 2023.08.12
스프링 di  (0) 2023.08.12
스프링 시큐리티  (0) 2023.08.11
spring master - chap2 스프링빈 의존성 주입(IOC)  (0) 2023.01.23
profile

냥쟝

@냥쟝

포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!

검색 태그

인터페이스
인스턴스
자료구조
국비
객체
미라클
spring
암생물학
자바의정석
코딩테스트
객체지향
스프링
쌍용 교육센터
알고리즘
항체공학
반복문
native sql
김영한
코드로 보는 스프링부트
메서드
java
변수
자바
상속
클래스
p53
배열
유전학
jpa
조건문

티스토리툴바