어제 하던것 마저

-------------------------------mybatis-config-------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "https://mybatis.org/dtd/mybatis-3-config.dtd">

<!-- 시작 태그 configuration -->
<configuration>
	<properties resource="db.properties"></properties>
->밑에 property이름 바꿨는데 여기에 어떻게 바꿨는지 적어놨어 확인해봐 

	<typeAliases>
		<typeAlias type="user.bean.UserDTO" alias="user"/> <!-- 별칭정하기 -->
	</typeAliases>
-> user.bean.UserDTO을 user로 별칭을 정해서 아래 파일에서 다바꿔줌

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}"/>
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.username}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="user/dao/userMapper.xml"/> 
           <!-- resource는 user.dao가 아닌 /로 경로를 표현 -->
           <!-- user.dao -> user/dao -->
	</mappers>
</configuration>

-------------------------------db.properties-------------------------------
파일 생성

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.username=c##java
jdbc.password=1234

-------------------------------userMapper.xml-------------------------------
<typeAliases>
		<typeAlias type="user.bean.UserDTO" alias="user"/> <!-- 별칭정하기 -->
</typeAliases>
-> user.bean.UserDTO을 user로 별칭을 정해서 아래 파일에서 다바꿔줌

↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ 

<mapper namespace="userSQL">

	<!-- 등록 -->
	<insert id="write" parameterType="user">   <!-- 자료형으로 받아야함 -->
		insert into usertable values(#{name}, #{id}, #{pwd})
	</insert>

	<!-- 출력 -->
	<select id="getUserList" resultType="user">
		select * from usertable
	</select>
	
	<!-- 수정 -->
	<select id="getUser" parameterType="java.lang.String" resultType="user">
		select * from usertable where id=#{id}
	</select>
	
	<update id="update" parameterType="user">
		update usertable set name=#{name}, pwd=#{pwd} where id = #{id}
	</update>
	
	<!-- 삭제 -->
	<delete id="delete" parameterType="String">
		delete from usertable where id=#{id}
	</delete>
	
	<select id="search1" resultType="user">
		select * from usertable where id=${*id*}
	</select>
	
</mapper>

-------------------------------userMapper.xml-------------------------------
package user.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserSearchService implements UserService{
	
	@Override
	public void execute() {

		System.out.println();
		
		Scanner scan = new Scanner(System.in);
		System.out.println("************");
		System.out.println("1. 이름 검색");
		System.out.println("2. 아이디 검색");
		System.out.println("************");
		System.out.print("번호 입력 : ");
		int num = scan.nextInt();
		
		System.out.println();
		
		String columnName = null;
		String value = null;
		
		if(num == 1) {
			System.out.print("검색할 이름 입력 : ");
			value = scan.next();
			columnName = "name";

			
		}else if(num == 2){
			System.out.print("검색할 아이디 입력 : ");
			value = scan.next();
			columnName = "id";
		}
		
		Map<String, String> map = new HashMap<String, String>();
		map.put("columnName", columnName);
		map.put("value", value);
		
		// DB
		UserDAO userDAO = new UserDAO();
		List<UserDTO> list = userDAO.search(map);
		
		// 응답
		for(UserDTO userDTO: list) {
		  System.out.println(userDTO.getName() +"\t"+ userDTO.getId() +"\t"+ userDTO.getPwd());
		}
	}
}

-------------------------------userMapper.xml-------------------------------

	public List<UserDTO> search(Map<String, String> map) {
		SqlSession sqlSession = sqlSessionFactoy.openSession(); // 생성
		List<UserDTO> list = sqlSession.selectList("userSQL.search", map);
		sqlSession.close();
		
		return list;
	}

-------------------------------userMapper.xml-------------------------------

<select id="search" parameterType="java.util.Map" resultType="user"> 
			 <!-- ★parameterType안에는 자료형★ 
             	  ★resultType="user" - DTO안에 넣어주세요~(user=user.bean.DTO) -->
		select * from usertable where 
		<if test="columnName == 'name'">
			name like '%' || #{value} || '%'
		</if>
		
		<if test="columnName == 'id'">
			id like '%' || #{value} || '%'
		</if>

</select>



추가적으로 위 조건식을 아래와 같이 바꿀 수도 있다. <c:choose>와 같다고 봐도 무방

select * from usertable where 
<choose>
	<when test="columnName == 'name'">
		name like '%' || #{value} || '%'
	</when>
	
	<otherwise>
		id like '%' || #{value} || '%'
	</otherwise>
</choose>


또는 


select * from usertable where ${columnName} like '%' || #{value} || '%'
로 쓸 수 있다.

---

MembeMVC프로젝트를 마이바티스로 바꾸기

-------------------------------mybatis-config.xml-------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "https://mybatis.org/dtd/mybatis-3-config.dtd">

<!-- 시작 태그 configuration -->
<configuration>
	<properties resource="db.properties"></properties>
	
	<typeAliases>
		<typeAlias type="member.bean.MemberDTO" alias="member"/> <!-- 별칭정하기 -->
		<typeAlias type="board.bean.BoardDTO" alias="board"/>
	</typeAliases>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}"/>
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.username}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="member/dao/memberMapper.xml"/>
		<mapper resource="board/dao/boardMapper.xml"/>
	</mappers>
</configuration>

-------------------------------memberMapper.xml-------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
 
<mapper namespace="memberSQL">
	<!-- 로그인 -->
	<select id="login" parameterType="java.util.Map" resultType="member">
		select * from member where id = #{id} and pwd = #{pwd}
	</select>
</mapper>

-------------------------------MemberDAO.java-------------------------------
수정

package member.dao;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


import member.bean.MemberDTO;

public class MemberDAO {

	private SqlSessionFactory sqlSessionFactoy;
	
	public MemberDAO() {
		try {
		//InputStream inputStream = Resources.getResourceAsStream(mybatis-config.xml);
		Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); // 모든 리소스 파일 읽어옴
		
		sqlSessionFactoy = new SqlSessionFactoryBuilder().build(reader); // 생성
		
		}catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public MemberDTO login(String id, String pwd){
		SqlSession sqlSession = sqlSessionFactoy.openSession(); // 생성
		
		Map<String, String> map = new HashMap<String, String>();
		map.put("id", id);
		map.put("pwd", pwd);
		
		MemberDTO memberDTO = sqlSession.selectOne("memberSQL.login", map);
		sqlSession.close();		
		
		return memberDTO;
	}
		
}

-------------------------------boardMapper.xml------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="boardSQL">	
	<!-- 글쓰기 -->
	<insert id="boardWrite" parameterType="java.util.Map">
		insert into board(seq,
						  id,
						  name,
						  email,
						  subject,
						  content,
						  ref)
					values(seq_board.nextval, 
		   				  #{id},
		   				  #{name},
		   				  #{email},
		   				  #{subject},
		   				  #{content},
		   				  seq_board.nextval)
	</insert>
	
	
	<!-- 목록 -->
	<select id="getBoardList" parameterType="java.util.Map" resultType="board">

<=에서 <가 태그로 읽혀 버려서
		<!-- select * from (select rownum rn, tt.* from 
					  (select * from board order by seq desc)tt
					  )where rn between #{startNum} and #{endNum} -->
					  
다른 방법
		<!-- select * from (select rownum rn, tt.* from 
					  (select * from board order by seq desc)tt
					  )where rn >= #{startNum} and rn &lt;= #{endNum} -->
또 다른 방법
		<![CDATA[
		select * from (select rownum rn, tt.* from 
					  (select * from board order by seq desc)tt
					  )where rn >= #{startNum} and rn <= #{endNum}
					  ]]>
	</select>
	
	<!-- 총글수 -->
	<select id="getTotalA" resultType="int">
		select count(*) from board
	</select>
	
	<!-- 작성한 글 확인 -->
	<select id="getBoard" parameterType="int" resultType="board">
		select * from board where seq = #{seq}
	</select>
	
</mapper>

-------------------------------BoardDAO.java-------------------------------

package board.dao;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import board.bean.BoardDTO;


public class BoardDAO {
	
	private SqlSessionFactory sqlSessionFactoy;

	public BoardDAO() {
		try {
			//InputStream inputStream = Resources.getResourceAsStream(mybatis-config.xml);
			Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); // 모든 리소스 파일 읽어옴
			
			sqlSessionFactoy = new SqlSessionFactoryBuilder().build(reader); // 생성
			
			}catch (IOException e) {
				e.printStackTrace();
			}
		}
	
	public void boardWrite(Map<String, String> map) {
		SqlSession sqlSession = sqlSessionFactoy.openSession();
		sqlSession.insert("boardSQL.boardWrite", map);
		sqlSession.commit();
		sqlSession.close();
		
	}
	
	public List<BoardDTO> getBoardList(int startNum, int endNum){
		SqlSession sqlSession = sqlSessionFactoy.openSession(); // 생성
		
		Map<String, Integer> map = new HashMap<String, Integer>();
		map.put("startNum", startNum);
		map.put("endNum", endNum);
		
		List<BoardDTO> list = sqlSession.selectList("boardSQL.getBoardList", map);
		sqlSession.close();	
		
		return list;
	}
	
	public int getTotalA(){
		SqlSession sqlSession = sqlSessionFactoy.openSession(); // 생성
		int totalA = sqlSession.selectOne("boardSQL.getTotalA");
		sqlSession.close();	
		
		return totalA;
	}
	
	public BoardDTO getBoard(int seq){
		SqlSession sqlSession = sqlSessionFactoy.openSession(); // 생성
		BoardDTO boardDTO = sqlSession.selectOne("boardSQL.getBoard", seq);
		sqlSession.close();
		
		return boardDTO;
	}
}

Spring

스프링 전용 이클립스