Spring(2023-04-14)

권단비·2023년 4월 14일
RestfulSpring게시판
0

IT

목록 보기
122/139

[조인 1:N]

[계산 application.properties]
#server port number
server.port = 8282

#datasource (oracle)
#spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=scott
spring.datasource.password=tiger

#MyBatis
#xml location
mybatis.mapper-locations=classpath:mappers/`**/*.xml
mybatis.type-aliases-package=edu.global.ex

#jsp 
spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp
---------------------------------------------------------------------
[계산 DeptVO.java]
package edu.global.ex.vo;
import java.util.List;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class DeptVO {
	private String deptno; // 부서번호
	private String dname; // 부서이름
	private String loc; // 지역

	List<EmpVO> empList; // DeptVO(1) : EmpVO(N)
}
---------------------------------------------------------------------
[계산 EmpDeptVO.java]
package edu.global.ex.vo;
import java.sql.Timestamp;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString // [EmpDeptVO(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=1980-12-17
			// 00:00:00.0, sal=800, comm=0, deptno=20, dname=RESEARCH, loc=DALLAS)]

public class EmpDeptVO {
	private int empno; // NOT NULL NUMBER(4)
	private String ename; // VARCHAR2(10)
	private String job;// VARCHAR2(9)
	private int mgr;// NUMBER(4)
	private Timestamp hiredate;// DATE
	private int sal;// NUMBER(7,2)
	private int comm;// NUMBER(7,2)
	private int deptno;// NUMBER(2)

	private String dname; // 부서이름
	private String loc; // 지역
}
---------------------------------------------------------------------
[계산 EmpVO.java]
package edu.global.ex.vo;
import java.sql.Timestamp;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class EmpVO {
	   private int empno;
	   private String ename;
	   private String job;
	   private int mgr;
	   private Date hiredate;
	   private int sal;
	   private int comm;
	   private int deptno;
}
---------------------------------------------------------------------
[계산 DeptMapper.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">
<!-- DeptMapper.java로 연결 -->
<mapper namespace="edu.global.ex.mapper.DeptMapper">
	<select id="getEmpDeptOneVOList" resultType="EmpDeptVO">
		select * from emp, dept where emp.deptno = dept.deptno
	</select>
	<!-- resultMap의 id는 개발자가 정하는 부분이다 -->
	<resultMap id="empMap" type="EmpVO">
		<!-- id : key값 -->
		<!-- column : DB의 column과 매핑함 -->
		<id property="empno" column="empno" />
		<result property="ename" column="ename" />
		<result property="job" column="job" />
		<result property="mgr" column="mgr" />
		<result property="hiredate" column="hiredate" />
		<result property="sal" column="sal" />
		<result property="comm" column="comm" />
		<result property="deptno" column="deptno" />
	</resultMap>
	<!-- property : setter함수 setDeptno();와 동일 -->
	<resultMap id="deptMap" type="DeptVO">
		<id property="deptno" column="deptno" />
		<result property="dname" column="dname" />
		<result property="loc" column="loc" />
		<!-- 1:1받기 | association -->
		<!-- 1:n받기 | collection | DeptVO의 List<EmpVO> empList; -->
		<collection property="empList" resultMap="empMap"></collection>
	</resultMap>
<select id="getEmpDeptList" resultMap="deptMap">
	select * from emp, dept where emp.deptno = dept.deptno
</select>

[계산 DeptMapper.java]
package edu.global.ex.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import edu.global.ex.vo.DeptVO;
import edu.global.ex.vo.EmpDeptVO;

@Mapper // MyBatis 연결을 알려주는 것
public interface DeptMapper { // interface를 DeptMapper.xml에서 구현한다. >> Mybatis
public List getEmpDeptOneVOList();
public List getEmpDeptList();

}

[계산 EmpController.java]
package edu.global.ex.controller;
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.RequestMapping;
import edu.global.ex.EmpService;
import edu.global.ex.vo.PageVO;
import edu.global.page.Criteria;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Slf4j // 로그를 사용하기 위한 어노테이션 : log.info("view_list()..");
@Controller // (@Component + 의미) BoardController board = new BoardController();
@RequestMapping("/emp/*") // jboard로 들어오는 모든 것들은 BoardController를 생성해라
@RequiredArgsConstructor
public class EmpController {

@Autowired // (주입)객체에 주소를 줌
private EmpService empService; // BoardService boardService = new BoardServiceImpl();

// http://localhost:8282/list2
// list2?pageNum=5&amount=10">1</a>
@GetMapping("/list2")
public String list2(Criteria cri, Model model) { // 커맨드 객체 첫 번째로 실행 : 객체생성
	// Criteria(1,10)
	log.info("list2() ..");
	log.info("list2() Criteria " + cri);

	model.addAttribute("emps", empService.getList(cri));

	// 페이징을 위한 처리
	int total = empService.getTotal();
	log.info("total" + total);

	model.addAttribute("pageMaker", new PageVO(cri, total));

	return "/emp/list2";
}

@GetMapping("/dept1")
public String dept1() { // 커맨드 객체 첫 번째로 실행 : 객체생성
	log.info("dept1() ..");

	System.out.println(empService.getEmpDeptOneVOList());

	return "/emp/emp_list2";
}

@GetMapping("/dept2")
public String dept2(Model model) { // 커맨드 객체 첫 번째로 실행 : 객체생성
	log.info("dept2() ..");

	System.out.println(empService.getEmpDeptList());
	model.addAttribute("empDeptList", empService.getEmpDeptList());

	return "/emp/empDept";
}

}

[계산 EmpService.java]
package edu.global.ex;
import java.util.List;
import edu.global.ex.vo.DeptVO;
import edu.global.ex.vo.EmpDeptVO;
import edu.global.ex.vo.EmpVO;
import edu.global.page.Criteria;

public interface EmpService {
public List getList(); // 리스트 메소드

public EmpVO get(int empno); // 글보기 메소드

// 페이징
public abstract int getTotal();
public abstract List<EmpVO> getList(Criteria criteria); // 리스트 메소드 오버로딩

//1:N 처리
public List<EmpDeptVO> getEmpDeptOneVOList();
public List<DeptVO> getEmpDeptList();

}

[계산 EmpServiceImpl.java]
package edu.global.ex;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import edu.global.ex.mapper.DeptMapper;
import edu.global.ex.mapper.EmpMapper;
import edu.global.ex.vo.DeptVO;
import edu.global.ex.vo.EmpDeptVO;
import edu.global.ex.vo.EmpVO;
import edu.global.page.Criteria;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Slf4j // 로그를 사용하기 위한 어노테이션 : log.info("view_list()..");
@Service
@RequiredArgsConstructor // Autowired에 생성자 주입해 줌
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper mapper;

@Autowired
private final DeptMapper deptMapper;

@Override
public List<EmpVO> getList() {
	log.info("getList()..");
	return mapper.getList();
}

@Override
public EmpVO get(int empno) {
	log.info("get(int empno)..");
	return mapper.read(empno);
}

@Override
public int getTotal() {
	log.info("getTotal()..");
	return mapper.getTotalCount();
}

@Override
public List<EmpVO> getList(Criteria criteria) {
	log.info("getList(Criteria criteria)");
	return mapper.getListWithPaging(criteria);
}

@Override
public List<EmpDeptVO> getEmpDeptOneVOList() {
	return deptMapper.getEmpDeptOneVOList();
}

@Override
public List<DeptVO> getEmpDeptList() {
	return deptMapper.getEmpDeptList();
}

}

[계산 empDept.jsp]
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

Insert title here
EMPNO ENAME JOB MGR HIREDATE DEPTNO DNAME LOC
${emp.empno} ${emp.ename} ${emp.job} ${emp.mgr} ${emp.hiredate} ${deptList.deptno} ${deptList.dname} ${deptList.loc}
``` >``` [결과값] ``` ![](https://velog.velcdn.com/images/hyphen/post/4b333217-3a9d-4b0a-b333-57e40a3dd586/image.png)

[다른 방법]

[계산 BoardMapper.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="edu.global.ex.mapper.BoardMapper">
	<select id="read" resultType="edu.global.ex.vo.BoardVO"
		parameterType="edu.global.ex.vo.BoardVO">
	<![CDATA[select * from mvc_board where bid = #{bid}]]>
		<!-- #{bid} : BoardVO get(BoardVO vo) 안에 있는 필드 이름 사용 가능 -->
	</select>
	<update id="update">
	<![CDATA[update mvc_board set bname=#{bname}, btitle=#{btitle}, bcontent=#{bcontent} 
             where bid =#{bid}]]>
		<!-- #{bid} : BoardService.java의 int modify(BoardVO vo) 안에 있는 필드 이름 사용 
			가능 -->
	</update>
	<delete id="delete">
	<![CDATA[delete mvc_board where bid= #{bid}]]>
	</delete>
	<insert id="insert">
	<![CDATA[ insert into mvc_board (bid, bname, btitle, bcontent, bhit, bgroup, bstep, bindent) 
            values (mvc_board_seq.nextval, #{bname}, #{btitle}, #{bcontent}, 0, mvc_board_seq.currval, 0, 0)]]>
	</insert>

	<!--댓글을 하나씩 미뤄서 정렬을 해줘야 하기 때문에 세로 정렬을 bstep에 +1로 한칸씩 미뤄내줘야한다. -->
	<update id="updateShape">
      <![CDATA[
          update mvc_board set bstep = bstep + 1 where bgroup =#{bgroup} and bstep > #{bstep}
      ]]>
	</update>

	<insert id="insertReply">
   <![CDATA[
      insert into mvc_board (bid, bname, btitle, bcontent, bgroup, bstep, bindent) 
      values (mvc_board_seq.nextval, #{bname}, #{btitle},#{bcontent}, 
                #{bgroup}, #{bstep}+1, #{bindent}+1)
   ]]>
	</insert>

	<!-- 페이징 관련 -->
	<select id="getTotalCount" resultType="int">
	<![CDATA[select count(*) from mvc_board]]>
	</select>

	<select id="getListWithPaging"
		resultType="edu.global.ex.vo.BoardVO">
      <![CDATA[
        SELECT * FROM (
              SELECT ROWNUM AS RNUM, A.* FROM (
                       SELECT
                             *
                         FROM
                             mvc_board 
                         order by bGroup desc, bStep asc   
             ) A WHERE ROWNUM <= #{pageNum} * #{amount}
         ) WHERE RNUM > (#{pageNum}-1) * #{amount}
      ]]>
	</select>
</mapper>
---------------------------------------------------------------------
[계산 BoardMapper.java]
package edu.global.ex.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import edu.global.ex.vo.BoardVO;
import edu.global.page.Criteria;

@Mapper // MyBatis 연결을 알려주는 것
public interface BoardMapper { // interface를 BoardMapper.xml에서 구현한다. >> Mybatis
	@Select("select * from mvc_board order by bGroup desc, bStep asc") // Mybatis 3버전 restful
	public List<BoardVO> getList();
	public BoardVO read(int bid); // 파라미터는 url로 넘어오는 bid값을 의미
	public int update(BoardVO board); // 글수정
	public int delete(BoardVO board); // 글삭제
	public int insert(BoardVO board); // 글작성

	//답글관리
	public void updateShape(BoardVO board); // 답글위치
	public void insertReply(BoardVO board); // 답글등록

	 //paging 관련
	 public abstract int getTotalCount();   
	 public abstract List<BoardVO> getListWithPaging(Criteria cri);
}

[restful]

json viewer

https://chrome.google.com/webstore/detail/json-viewer/gbmdgpbipfallnflgajpaliibnhdgobh?hl=ko

[Get | Post 방식]
게시판 수정 1번 url : /update?bid=1
게시판 삭제 20번 url : /delete?bid=20
  
[restful 처리]
게시판 삭제 20번 수정 또는 삭제 url : /boards/20
>> method 방식에 따라 [/boards/20] 수정되거나 삭제됨
>> html에서는 method 방식을 get, post만 지원함

[계산 application.properties]
#server port number
server.port = 8282

#datasource (oracle)
#spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=scott
spring.datasource.password=tiger

#MyBatis
#xml location
mybatis.mapper-locations=classpath:mappers/`**/*.xml
mybatis.type-aliases-package=edu.global.ex

#jsp 
spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp
---------------------------------------------------------------------
[계산 RestBoardController.java]
package edu.global.ex.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import edu.global.ex.BoardService;
import edu.global.ex.vo.BoardVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Slf4j // 로그를 사용하기 위한 어노테이션 : log.info("view_list()..");
@RequestMapping("/rboard/*") // jboard로 들어오는 모든 것들은 BoardController를 생성해라
@RequiredArgsConstructor
@RestController // 기존의 컨트롤러 문법과는 완전히 달라짐 | restful 처리를 위한 어노테이션
public class RestBoardController {

	@Autowired // (주입)객체에 주소를 줌
	private BoardService boardService; // BoardService boardService = new BoardServiceImpl();

	@GetMapping("/")
	public String rboard() { // String이면 html text로 보냄
		return "<h1>이제는 restful이다</h1>";
	}

	@GetMapping("/list") // List객체 json으로 리턴
	public List<BoardVO> list() {
		log.info("list()..");
		return boardService.getList();
	}
	//json으로 리턴된 데이터는 ajax로 받아온다.
	// ajax({ type:"GET", url : "http://localhost:8282/rboard/list",
	// success : function(result){}
}
---------------------------------------------------------------------
[계산 RestBoardController.java]
[결과값]

html표시 (http://localhost:8282/rboard/)

json표시 (http://localhost:8282/rboard/list)

ajax표시(http://localhost:8282/rest_list.html)

profile
권단비
이전 포스트

Spring(2023-04-13)

다음 포스트

Spring(2023-04-17)

0개의 댓글