23.06.01 : myBatis / Annotation

이준영·2023년 6월 1일
Java
0

Spring - jdbc

  1. 직접 연결
    쿼리를 통하여 문자열
  2. JdbcTemplate(ORM) ---> Hibernate(ORM TOOL, JPA 구조)
    쿼리를 통하여 객체 저장(select 결과를 객체로)

🌼 JdbcTemplate으로 게시판 만들기

🌻 DAO 구조 만들기

🌻 1. List 부분

BoardDAO - List

	public ArrayList<BoardTO> boardList() {
		ArrayList<BoardTO> lists = (ArrayList<BoardTO>)jdbcTemplate.query(
				"select seq, subject, writer, date_format(wdate, '%Y-%m-%d') wdate, hit, datediff(now(), wdate) wgap from board order by seq desc", 
				new BeanPropertyRowMapper<BoardTO>(BoardTO.class));
		
		return lists;
				
	}

BoardController - List

	@RequestMapping("/list.do")
	public String List(HttpServletRequest request, HttpServletResponse response, Model model) {
		
		ArrayList<BoardTO> lists = dao.boardList();
		//System.out.println(lists.size());
		
		model.addAttribute("lists", lists);
		
		return "board_list1";
	}

🌻 2. write_ok 부분

BoardDAO - write_ok

public int boardWriteOk(BoardTO to) {
		int flag = 1;
		
		int result = jdbcTemplate.update(
				"insert into board values ( 0, ?, ?, ?, ?, ?, 0, ?, now() )",
					to.getSubject(), to.getWriter(), to.getMail(), to.getPassword(), 
					to.getContent(), to.getWip());
		
		if(result == 1) {
			flag = 0;
		}
		
		return flag;
	}

BoardController - write / write_ok

@RequestMapping("/write.do")
	public String write() {

		return "board_write1";
	}
	
	@RequestMapping("/write_ok.do")
	public String write_Ok(HttpServletRequest request, HttpServletResponse response, Model model) {
		
		BoardTO to = new BoardTO();
		
		to.setSubject( request.getParameter( "subject" ) );
		to.setWriter( request.getParameter( "writer" ) );
		to.setMail( "" ) ;
		if( !request.getParameter("mail1").equals("") 
				&& !request.getParameter("mail2").equals("") ) {
			to.setMail( request.getParameter( "mail1" ) + "@" + request.getParameter( "mail2" ) );	
		}
		
		to.setPassword( request.getParameter( "password" ) );
		to.setContent( request.getParameter( "content" ) );
		
		to.setWip( request.getRemoteAddr() );
		
		int flag = dao.boardWriteOk(to);
		
		model.addAttribute("flag", flag);
		
		return "board_write1_ok";
	}

🌻 3. view 부분

BoardDAO - view

public BoardTO boardView(BoardTO to) {
		
		int result = jdbcTemplate.update(
				"update board set hit=hit+1 where seq=?",
				to.getSeq());
		
		System.out.println("to1 : " + to);
		System.out.println("seq1 : " + to.getSeq());
		
		// 매개변수 to와 다름, 따라서 seq를 받아야 한다.
		to = jdbcTemplate.queryForObject(
				"select seq, subject, writer, mail, wip, wdate, hit, content from board where seq=?", 
				new BeanPropertyRowMapper<BoardTO>(BoardTO.class), to.getSeq());

		System.out.println("to2 : " + to);
		System.out.println("seq2 : " + to.getSeq());

		
		return to;
	}

to의 참조값이 다르다


BoardController - view

@RequestMapping("/view.do")
	public String view(HttpServletRequest request, HttpServletResponse response, Model model) {

		BoardTO to = new BoardTO();
		
		to.setSeq(request.getParameter("seq"));
		
		to = dao.boardView( to );
		
		model.addAttribute("to", to);  <-- board_view1에서 getAttribute로 받으면 된다.
		
		return "board_view1";
	}

🌻 4. modify / modifyOk 부분

BoardDAO - modify / modifyOk

public BoardTO boardModify(BoardTO to) {

		to = jdbcTemplate.queryForObject(
				"select seq, subject, writer, mail, content from board where seq=?", 
				new BeanPropertyRowMapper<BoardTO>(BoardTO.class), to.getSeq());

		return to;
	}
    
------------------------------------------------------------------------------------------------------

public int boardModifyOk(BoardTO to) {
		int flag = 2;
		
		int result = jdbcTemplate.update(
				"update board set subject=?, mail=?, content=? where seq=? and password=?",
					to.getSubject(), to.getMail(), to.getContent(),
					to.getSeq(), to.getPassword());
		
		if(result == 1) {
			flag = 0;
		}
		else if(result == 0) {
			flag = 1;
		}
		
		return flag;
	}

BoardController modify / modifyOk

@RequestMapping("/modify.do")
	public String modify(HttpServletRequest request, HttpServletResponse response, Model model) {

		BoardTO to = new BoardTO();
		
		to.setSeq(request.getParameter("seq"));
		
		to = dao.boardModify( to );
		
		model.addAttribute("to", to);
		
		return "board_modify1";
	}
	
    
-----------------------------------------------------------------------------------------------------


    
	@RequestMapping("/modify_ok.do")
	public String modify_ok(HttpServletRequest request, HttpServletResponse response, Model model) {
		
		BoardTO to = new BoardTO();
		
		to.setSeq( request.getParameter( "seq" ) );
		to.setSubject( request.getParameter( "subject" ) );
		to.setMail( "" );
		if( !request.getParameter( "mail1" ).equals( "" ) && !request.getParameter( "mail2" ).equals( "" ) ) {
			to.setMail( request.getParameter( "mail1" ) + "@" + request.getParameter( "mail2" ) );
		}
		to.setPassword( request.getParameter( "password" ) );
		to.setContent( request.getParameter( "content" ) );
		
		
		int flag = dao.boardModifyOk(to);
		
		model.addAttribute("flag", flag);
		
		return "board_modify1_ok";
	}

🌻 5. delete / deleteOk 부분

BoardDAO - delete / deleteOk

public BoardTO boardDelete(BoardTO to) {

		to = jdbcTemplate.queryForObject(
				"select seq, subject, writer from board where seq=?", 
				new BeanPropertyRowMapper<BoardTO>(BoardTO.class), to.getSeq());

		return to;
	}
	
    
    -----------------------------------------------------------------------------------------------------
    
    
	public int boardDeleteOk(BoardTO to) {
		int flag = 2;
		
		int result = jdbcTemplate.update(
				"delete from board where seq=? and password=?",
					to.getSeq(), to.getPassword());
		
		if(result == 1) {
			flag = 0;
		}
		else if(result == 0) {
			flag = 1;
		}
		
		return flag;
	}

BoardController - delete / deleteOk

@RequestMapping("/delete.do")
	public String delete(HttpServletRequest request, HttpServletResponse response, Model model) {

		BoardTO to = new BoardTO();
		
		to.setSeq(request.getParameter("seq"));
		
		to = dao.boardDelete( to );
		
		model.addAttribute("to", to);
		
		return "board_delete1";
	}
	
    
---------------------------------------------------------------------------------------------------  
    
    
	@RequestMapping("/delete_ok.do")
	public String delete_ok(HttpServletRequest request, HttpServletResponse response, Model model) {
		
		BoardTO to = new BoardTO();
		
		to.setSeq( request.getParameter( "seq" ) );
		to.setPassword( request.getParameter( "password" ) );
		
		
		int flag = dao.boardDeleteOk(to);
		
		model.addAttribute("flag", flag);
		
		return "board_delete1_ok";
	}



🌼 MyBatis 사용

MyBatis용 두개 추가로 더 pom.xml에 넣기

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.5.13</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>2.0.7</version>
		</dependency>

root-context.xml에서 설정

	<!-- myBatisConfig.xml -->
	<bean id="sqlSeesionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="mapperLocations" value="classpath:mappers/mapper.xml" />
	</bean>
		
	
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
			<!-- sqlSessionFactory 통해 sqlSession 만들기 -->
     	 <constructor-arg ref="sqlSessionFactory"></constructor-arg>
	</bean>

설정 후에 mappers 폴더 만들고 mapper.xml 집어넣기 ( 그 후에 sqlSession 만드는 작업)

resource 폴더 등록하기 위해 servlet.context.xml 작성
(<resources 작성을 위해 beans:bean으로 바꿔서 함) 

기존에서

	beans = beans:beans 바꾸고 뒤에 mvc로 변경
	xmlns:beans="http://www.springframework.org/schema/beans" 추가
	xsi:schemaLocatio에 
	http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd 추가
<!--<resources mapping="/resources/**" location="/resources/" />  --> 추가 (변경 : x 사용 안됨)

후에 beans:로 다 바꿔주기


<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="
	http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd
	http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
	http://www.springframework.org/schema/context 
	http://www.springframework.org/schema/context/spring-context-4.3.xsd">
	
	
	<resources mapping="/resources/**" location="/resources/" />
	 
	<!-- 패키지 지정하여 그 패키지 안 검색 -->	 
	<context:component-scan base-package="config" />
	<context:component-scan base-package="model1" />
	
	<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<beans:property name="prefix" value="/WEB-INF/views/"/>
		<beans:property name="suffix" value=".jsp"/>
	</beans:bean>
	
</beans:beans>

mapper.xml에 namespace 설정 / JdbcController 생성하고 작성 / index 실행

@Controller
public class JdbcController {

	@Autowired
	private SqlSession sqlSession;
	
	@RequestMapping("/jdbc1.do")
		public String jdbc() {
		
		System.out.println("jdbc1() 호출" + sqlSession);
		
		return "jdbc1";
	}



🌻 응용 : myBatis로 emp 사원 정보 뽑기

mapper.xml 설정하기

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">
	<!-- xml처럼 sql문 작성 -->
	<select id="selectNow" resultType="String">
		select now() as now
	</select>
	<select id="selectName" resultType="model1.EmpTO">
		select * from emp where ename like 's%' 
	</select>
</mapper>

EmpTO 생성 후 EmpDAO 생성

package model1;

import java.util.ArrayList;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class EmpDAO {

	@Autowired
	private SqlSession sqlSession; 
	
	public ArrayList<EmpTO> selectName() {
		return new ArrayList<>(sqlSession.selectList("selectName"));
	}
}

MyBatisController 생성

package config;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import model1.EmpDAO;
import model1.EmpTO;
import model1.MyBatisDAO;

@Controller
public class MyBatisController {

	@Autowired
	private SqlSession sqlSession;
	
	@Autowired
	private EmpDAO dao;
	
	
	@RequestMapping("/jdbc2.do")
	public String jdbc2() {

		ArrayList<EmpTO> result = dao.selectName();
		System.out.println("결과 : " + result);

	    for (EmpTO to : result) {
	        System.out.println(to.getEmpno());
	        System.out.println(to.getEname());
	        System.out.println(to.getJob() + "\n");
	    }
	    
		return "jdbc2";
	}
	
}

index에서 실행



🌼 myBatis - annotation 구조

myBatis에서 mapper나 mapper-scan과 같은 annotation 추가 할 수 있다.

mapper 패키지 추가하여 인터페이스 만들고 @mapper 통하여 작업한다.

SqlMapperInter.java (인터페이스)

package mapper;

import java.util.ArrayList;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import model1.EmpTO;

@Mapper    <-- mapper.xml대신에 annotation으로 사용
public interface SqlMapperInter {	
	@Select("select * from emp where ename like 's%'")
	public abstract ArrayList<EmpTO> selectName();
}

dao에서 mapper 선언하고 리턴하는 메서드 설정

package model1;

import java.util.ArrayList;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import mapper.SqlMapperInter;

@Repository
@MapperScan("mapper")
public class EmpDAO {

	@Autowired
	private SqlMapperInter mapper;
	
	public ArrayList<EmpTO> selectName() {
		return mapper.selectName();
	}
}

ConfigController에서 받아서 사용

package config;

import java.util.ArrayList;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import mapper.SqlMapperInter;
import model1.EmpDAO;
import model1.EmpTO;
import model1.JdbcDAO;

@Controller
@MapperScan("mapper")
public class ConfigController {

	@Autowired
	private SqlMapperInter mapper;

	
	@Autowired
	private EmpDAO dao1;
	
	
	@RequestMapping("/jdbc2.do")
	public String jdbc2() {
		System.out.println("jdbc2() call : " + mapper);
		
		ArrayList<EmpTO> result = dao1.selectName();
		for(EmpTO to : result) {
			System.out.println(to.getEmpno());
			System.out.println(to.getEname());
			System.out.println(to.getJob());
		}
		
		return "jdbc2";
	}
}



🌻 응용 : mapper, annotation으로 게시판 작업

위의 jdbctemplate과 변경점, mapper 추가 / dao 변경 말곤 다 동일함

mapper패키지 - BoardMapperInter 생성 후 작성

package mapper;

import java.util.ArrayList;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import model1.BoardTO;

@Mapper
public interface BoardMapperInter {

	@Select("select seq, subject, writer, date_format(wdate, '%Y-%m-%d') wdate, hit, datediff(now(), wdate) wgap from board order by seq desc")
	public ArrayList<BoardTO> boardList();
	
	@Insert("insert into board values(0,#{subject}, #{writer}, #{mail}, #{password}, #{content}, 0, #{wip}, now())")
	public int boardWrite(BoardTO to);
	
	@Select("select seq, subject, writer, mail, wip, wdate, hit, content from board where seq= #{seq}")
	public BoardTO boardView(BoardTO to);
	
	@Update("update board set hit=hit+1 where seq=#{seq}")
	public int view_hit(BoardTO to);
	
	@Select("select seq, subject, writer, mail, content from board where seq = #{seq}")
	public BoardTO Modify(BoardTO to);
	
	@Update("update board set  subject = #{subject}, mail = #{mail}, content = #{content} where seq = #{seq} and password = #{password}")
	public int ModifyOk(BoardTO to);
	
	@Select("select seq, subject, writer, mail, content from board where seq = #{seq}")
	public BoardTO Delete(BoardTO to);

	@Delete("delete from board where seq = #{seq} and password = #{password}")
	public int DeleteOk(BoardTO to);
}

후에 DAO 수정

dao

package model1;

import java.util.ArrayList;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import mapper.BoardMapperInter;


@Repository
@MapperScan("mapper")
public class BoardDAO {
	@Autowired
	private BoardMapperInter mapper;

	public ArrayList<BoardTO> boardList() {
		ArrayList<BoardTO> lists = mapper.boardList();
		
		return lists;
				
	}
	
	public int boardWriteOk(BoardTO to) {
		int flag = 1;
		
		int result = mapper.boardWrite(to);
		
		if(result == 1) {
			flag = 0;
		}
		
		return flag;
	}
	
	public BoardTO boardView(BoardTO to) {
		
		mapper.view_hit(to);
		
		// 매개변수 to와 다름, 따라서 seq를 받아야 한다.
		to = mapper.boardView(to);
		
		return to;
	}
	
	public BoardTO boardModify(BoardTO to) {

		to = mapper.Modify(to);
		
		return to;
	}
	
	public int boardModifyOk(BoardTO to) {
		int flag = 2;
		
		int result = mapper.ModifyOk(to);
		
		
		if(result == 1) {
			flag = 0;
		}
		else if(result == 0) {
			flag = 1;
		}
		
		return flag;
	}
	
	public BoardTO boardDelete(BoardTO to) {

		to = mapper.Delete(to);
		
		return to;
	}
	
	public int boardDeleteOk(BoardTO to) {
		int flag = 2;
		
		int result = mapper.DeleteOk(to);
		
		if(result == 1) {
			flag = 0;
		}
		else if(result == 0) {
			flag = 1;
		}
		
		return flag;
	}
	
}



🌼 file 업로드

🌻 cos 라이브러리 받고 pom.xml 넣기

<!-- https://mvnrepository.com/artifact/servlets.com/cos -->
		<dependency>
			<groupId>servlets.com</groupId>
			<artifactId>cos</artifactId>
			<version>05Nov2002</version>
		</dependency>

🌻 web-app에 upload 폴더 만들기 / form.jsp 작업

<body>
form.jsp
<br><br>
<form action="form_ok.do" method="post" enctype="multipart/form-data">
파일 <input type="file" name="upload"/>
<input type="submit" value="파일 전송" />
</form>

🌻 fileController 작업

@Controller
public class filecontroller {

	
	@RequestMapping("/form.do")
	public String form( HttpServletRequest request, HttpServletResponse response, Model model ) {
		
		return "form";
	}
	
	@RequestMapping("/form_ok.do")
	public String form_ok( HttpServletRequest request, HttpServletResponse response, Model model ) {
		
		String uploadPath = "C:/Java/spring-workspace/file/src/main/webapp/upload";
		int maxFileSize = 2 * 1024 * 1024;
		String encType= "utf-8";
		
		try {
			MultipartRequest multi
			= new MultipartRequest(request, uploadPath, maxFileSize, encType, new DefaultFileRenamePolicy());
			
			System.out.println(multi.getOriginalFileName("upload"));
			//form.jsp의 name 이름 넣기
			System.out.println(multi.getFilesystemName("upload"));
		} catch (IOException e) {
			System.out.println("[에러] " + e.getMessage());
		}
		
		return "form_ok";
	}
}

profile
이준영
끄적끄적
이전 포스트

23.05.31 : spring mvc, JdbcTemplate

다음 포스트

23.06.02 :

0개의 댓글