Spring STEP 10 - MyBatisSimple

★ 새 프로젝트

New > Spring Legacy Project > Spring MVC Project > "MyBatisSimpleTest" > "com.test.simple" > Finish

 

■ 기초 셋팅

• https://github.com/pinnpublic/class/wiki/%EC%8A%A4%ED%94%84%EB%A7%81-%ED%94%84%EB%A1%9C%EC%A0%9D%ED%8A%B8-%EC%9D%BC%EA%B4%84-%EC%A0%81%EC%9A%A9

스프링 프로젝트 일괄 적용

• 1. pom.xml 셋팅 설정
• 2. ojdbc6.jar
• 3. web.xml 셋팅 설정
• 4. root-context.xml 셋팅 설정
• 5. XML Mapper(x)
• 6. log4jdbc.log4j2.properties 생성 및 셋팅
• 7. log4j.xml 설정 추가
• 8. Mapper 인터페이스 만들기

- src/main/java > "com.test.mapper" > TimeMapper.java(I)
- SQL 실행하는 담당자

• 9. Mapper 인터페이스를 인식

- root-context.xml 수정
-> NameSpace -> Mybatis-spring

• 10. src/main/resources > 인터페이스 매퍼가 있는 패키지와 동일한 구조의 폴더를 생성한다.

TimeMapper.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="com.test.mapper.TimeMapper">

	<select id="getTime2" resultType="String">
		select to_char(sysdate, 'hi24:mi:ss') from dual
	</select>

</mapper>

 

■ TimeMapper.java ->

• 인터페이스 매퍼 + 어노테이션(SQL)

//인터페이스 매퍼 + 어노테이션(SQL)
@Select("select sysdate from dual")
public String getTime();

• XML 매퍼 파일 사용하기

//XML 매퍼 파일 사용하기
public String getTime2();

 

★ CRUD 응용

 

■ 데이터베이스

MyBatisSimpleTest -> "script.sql"

• DB 코드

create table tblAddress (
    seq number primary key,
    name varchar2(30) not null,
    age number(3) not null,
    address varchar2(300) not null
);

create sequence seqAddress;

create table tblEmail (
    seq number primary key,
    email varchar2(100) not null,
    pseq number not null references tblAddress(seq)
);

create sequence seqEmail;

 

■ 파일 생성

- "com.test.controller" > "AddressController.java"

- "com.test.service" > "AddressService.java"(I)
                     > "AddressServiceImpl.java"(I)
                     
- "com.test.mapper" > "AddressMapper.java"(I) == AddressDAO 역할

- "com.test.domain" > "AddressDTO.java"
                    > "EmailDTO.java"

- src/main/resources > com > test > mapper > "AddressMapper.xml"

- views > "list.jsp"
        > "add.jsp"
        > "view.jsp"

• scan 추가 -> servlet-context.xml

<context:component-scan base-package="com.test.controller" />
<context:component-scan base-package="com.test.service" />

 

■ DTO 작업

• AddressDTO.java

@Data
public class AddressDTO {
	
	private String seq;
	private String name;
	private String age;
	private String address;

	private List<EmailDTO> email;
}

• EmailDTO.java

@Data
public class EmailDTO {
	
	private String seq; 
	private String email;
	private String pseq;
	
}

 

■ 기능별 구현

• list.do - 1. 직접 구현 ver

---------------AddressController.java------------------------
@GetMapping("/list.do")
public String list(Model model) {

    List<AddressDTO> list = service.list();

    model.addAttribute("list", list);

    return "list";
}
-----------------AddressService.java-----------------------------
public List<AddressDTO> list();
-----------------AddressServiceImplement.java--------------------
@Override
public List<AddressDTO> list() {

    //tblAddress(1) : tblEmail(N)
    //1. 직접 구현
    //2. MyBatis

    //1. 직접 구현 방식
    List<AddressDTO> list = mapper.list();

    for (AddressDTO dto : list) {

        List<EmailDTO> elist = mapper.elist(dto.getSeq());
        
        dto.setEmail(elist);

    }

    return null;
}
----------------AddressMapper----------------------------------
public List<AddressDTO> list();
public List<EmailDTO> elist(String seq);
---------------AddressMapper.xml------------------------------
<select id="list" resultType="com.test.domain.AddressDTO">
		
    select * from tblAddress
        order by seq asc

</select>
<select id="elist" resultType="com.test.EmailDTO">

    select * from tblEmail
        where pseq = #{pseq}
            order by seq asc

</select>

• list.do - 2. MyBatis ver

---------------AddressController.java------------------------
@GetMapping("/list.do")
public String list(Model model) {

    List<AddressDTO> list = service.list();

    model.addAttribute("list", list);

    return "list";
}
-----------------AddressService.java-----------------------------
public List<AddressDTO> list();
-----------------AddressServiceImplement.java--------------------
@Override
public List<AddressDTO> list() {

    //tblAddress(1) : tblEmail(N)
    //1. 직접 구현
    //2. MyBatis

    List<AddressDTO> list = mapper.list2();

    return null;
}
----------------AddressMapper----------------------------------
public List<AddressDTO> list2();
---------------AddressMapper.xml------------------------------
<resultMap type="com.test.domain.AddressDTO" id="addressMap">
    <id property="seq" column="seq"/>
    <result property="seq" column="seq" />
    <result property="name" column="name" />
    <result property="age" column="age" />
    <result property="address" column="address" />
    <collection property="email" resultMap="emailMap" />
</resultMap>

<resultMap type="com.test.domain.EmailDTO" id="emailMap">
    <id property="seq" column="seq"/>
    <result property="seq" column="seq"/>
    <result property="email" column="email"/>
    <result property="pseq" column="pseq"/>
</resultMap>

<select id="list2" resultMap="addressMap">

    select 
        a.seq,
        a.name,
        a.age,
        a.address,
        e.email
    from tblAddress a
        left outer join tblEmail e
            on a.seq = e.pseq
                order by a.seq asc

</select>

• addok.do

---------------AddressController.java------------------------
@PostMapping("/addok.do")
public String addok(Model model, AddressDTO dto, String[] emailList) {

    System.out.println(dto);
    System.out.println(Arrays.toString(emailList));

    service.add(dto, emailList);

    return "redirect:/list.do";
}
-----------------AddressService.java-----------------------------
public void add(AddressDTO dto, String[] emailList);
public void addEmail(EmailDTO edto);
-----------------AddressServiceImplement.java--------------------
@Override
public void add(AddressDTO dto, String[] emailList) {

    mapper.add(dto);

}
----------------AddressMapper----------------------------------
public void add(AddressDTO dto);
---------------AddressMapper.xml------------------------------
<insert id="add" parameterType="AddressDTO">
	
    <selectKey keyProperty="seq" order="BEFORE" resultType="String">
        select seqAddress.nextVal from dual
    </selectKey>

    insert into tblAddress (seq, name, age, address)
        values (#{seq}, #{name}, #{age}, #{address})

</insert>

<insert id="addEmail">
    insert into tblEmail (seq, email, pseq) values (seqEmail.nextVal, #{email}, #{pseq})
</insert>

 

■ 실행 결과

• 주소록 보기

• 주소록 추가