[Mybatis] 19. Mybatis DB조회하기

package mybatisjava;

import java.util.Date;

public class Student {
	private int studno;
	private String name;	
	private String id;
	private int grade;
	private String jumin;
	private Date birthday;
	private String tel;
	private int height;
	private int weight;
	private int deptno1;
	private int deptno2;
	private int profno;
	//getter, setter, toString (오른쪽 마우스 클릭 -> source[Alt + Shift + S] -> getter, setter, toString)
	public int getStudno() {
		return studno;
	}
	public void setStudno(int studno) {
		this.studno = studno;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public int getGrade() {
		return grade;
	}
	public void setGrade(int grade) {
		this.grade = grade;
	}
	public String getJumin() {
		return jumin;
	}
	public void setJumin(String jumin) {
		this.jumin = jumin;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public int getHeight() {
		return height;
	}
	public void setHeight(int height) {
		this.height = height;
	}
	public int getWeight() {
		return weight;
	}
	public void setWeight(int weight) {
		this.weight = weight;
	}
	public int getDeptno1() {
		return deptno1;
	}
	public void setDeptno1(int deptno1) {
		this.deptno1 = deptno1;
	}
	public int getDeptno2() {
		return deptno2;
	}
	public void setDeptno2(int deptno2) {
		this.deptno2 = deptno2;
	}
	public int getProfno() {
		return profno;
	}
	public void setProfno(int profno) {
		this.profno = profno;
	}
	@Override
	public String toString() {
		return "Student [studno=" + studno + ", name=" + name + ", id=" + id + ", grade=" + grade + ", jumin=" + jumin
				+ ", birthday=" + birthday + ", tel=" + tel + ", height=" + height + ", weight=" + weight + ", deptno1="
				+ deptno1 + ", deptno2=" + deptno2 + ", profno=" + profno + "]";
	}
}

---

package mybatisjava;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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

import mapper.StudentMapper;

public class Main1 {
	private static SqlSessionFactory sqlMap;
	static { //static 초기화 블럭 : 클래스변수 초기화 담당
		InputStream input = null;
		try {
			//input : mybatis-config.xml의 내용 저장
			input = Resources.getResourceAsStream("mapper/mybatis-config.xml");
		} catch(IOException e) {
			e.printStackTrace();
		}
		//mybatis-config.xml 파일을 읽어, 결과들을 sqlMap 변수 저장.
		//1. db와 연결완료.
		//2. sql 구문들을 저장.
		sqlMap = new SqlSessionFactoryBuilder().build(input);
	}
	public static void main(String[] args) {
		SqlSession session = sqlMap.openSession();
		System.out.print("모든 학생 정보 조회하기");
		List<Student> list = session.getMapper(StudentMapper.class).select();
		for(Student s : list) System.out.println(s);
		
		System.out.print("1학년 학생 정보 조회하기");
		list = session.getMapper(StudentMapper.class).select1(1);
		for(Student s : list) System.out.println(s);
		System.out.print("2학년 학생 정보 조회하기");
		list = session.getMapper(StudentMapper.class).select1(2);
		for(Student s : list) System.out.println(s);
		
		System.out.println("9711학생의 정보 조회하기");
		Student st = session.getMapper(StudentMapper.class).select2(9711);
		System.out.println(st);
		System.out.println("홍길동학생의 정보 조회하기");
		st = session.getMapper(StudentMapper.class).select3("홍길동");
		System.out.println(st);
		
		System.out.println("성이 김씨인 학생의 정보 조회하기1");
		//select * from student where name like '김%'
		list = session.getMapper(StudentMapper.class).select4("김%");
		for(Student s : list) System.out.println(s);
		System.out.println("성이 김씨인 학생의 정보 조회하기2");
		list = session.getMapper(StudentMapper.class).select5("김");
		for(Student s : list) System.out.println(s);
	}
}

---

package mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import mybatisjava.Student;
/*
 * interface 방식으로 Mybatis 사용하기
 * 1. 인터페이스명.메서드명으로 sql컨테이너에 저장됨.
 * 2. 같은 인터페이스에 같은 이름의 메서드 불가. => 오버로딩 불가.
 * 3. 네임스페이스 : 패키지명.인터페이스명
 * 				  mapper.StudentMapper
 */
public interface StudentMapper {
	@Select("select * from student")
	public List<Student> select();

	@Select("select * from student where grade = #{grade}")
	public List<Student> select1(int grade);

	@Select("select * from student where studno = #{studno}")
	public Student select2(int studno);

	@Select("select * from student where name = #{name}")
	public Student select3(String name);

	@Select("select * from student where name like #{name}")
	public List<Student> select4(String name);

	@Select("select * from student where name like '${value}%'")
	public List<Student> select5(String name);
	
	/*
	 * #{value} : 매개변수의 값 출력 + 자료형의 의미 추가. ex) 홍길동 : #{name}  => where name = '홍길동'
	 * 													1	: #{grade} => where grade = 1
	 * 
	 * ${value} : 매개변수의 값만 출력				  ex) 홍길동 : ${name}  => where name = 홍길동   Error
	 * 													1	: ${grade} => where grade = 1
	 * 	${name} => map객체의 키값이거나, Bean클래스의 프로퍼티를 의미
	 */
}

---

package mybatisjava;

import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
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 mapper.StudentMapper;
import mapper.StudentMapper2;

public class Main2 {
	private static SqlSessionFactory sqlMap;
	private static Map<String, Object> map = new HashMap<>();
	static { 
		InputStream input = null;
		try {
			input = Resources.getResourceAsStream("mapper/mybatis-config.xml");
		
		} catch(IOException e) {
			e.printStackTrace();
		}
		sqlMap = new SqlSessionFactoryBuilder().build(input);
	}
	public static void main(String[] args) {
		SqlSession session = sqlMap.openSession();
		System.out.println("학생 전체 정보 조회하기");
		map.clear(); //map 객체의 모든 요소 제거하기.
		List<Student> list = session.getMapper(StudentMapper2.class).select3(map);
		for(Student s : list) System.out.println(s);
		
		System.out.print("1학년 학생 정보 조회하기");
		map.clear();
		map.put("grade", 1);
		list = session.getMapper(StudentMapper2.class).select3(map);
		for(Student s : list) System.out.println(s);
		
		System.out.print("키가 180이상인 학생 정보 조회하기");
		map.clear();
		map.put("height", 180);
		list = session.getMapper(StudentMapper2.class).select3(map); //height=180
		for(Student s : list) System.out.println(s);
		
		System.out.print("1학년 학생 중 키가 175이상인 학생 정보 조회하기");
		map.clear();
		map.put("grade", 1);
		map.put("height", 175);
		list = session.getMapper(StudentMapper2.class).select3(map);
		for(Student s : list) System.out.println(s);
		//---------------------------------------------------
		System.out.print("101,201,301 학과에 속한 학생 정보 조회하기");
		map.clear();
		List<Integer>mlist = Arrays.asList(101,201,301);
		map.put("datas", mlist);
		map.put("columns", "deptno1");
		list = session.getMapper(StudentMapper2.class).select4(map);
		for(Student s : list) System.out.println(s);
		
		System.out.print("몸무게가 63,70,82인 학생 정보 조회하기");
		map.clear();
		mlist = Arrays.asList(63,70,82);
		map.put("datas", mlist);
		map.put("columns", "weight");
		list = session.getMapper(StudentMapper2.class).select4(map);
		for(Student s : list) System.out.println(s);
	}
}

---

package mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Select;

import mybatisjava.Student;

public interface StudentMapper2 {
	//select * from student
	@Select({"<script>", 
				"select * from student", 
					"<if test='grade != null'> where grade = #{grade}</if>",
					"<if test='height != null'> where height >= #{height}</if>",
			 "</script>"})
	List<Student> select(Map<String, Object> map);
	/*
	 * map : null, empty 인 경우
	 * 			=> select * from student : 모든 레코드 조회
	 * map : grade = 1
	 * 			=> select * from student where grade = 1
	 * map : height >= 175
	 * 			=> select * from student where height >= 175
	 * map : grade = 1 , height >= 175
	 * 			=> select * from student where grade = 1 where height >= 180 : sql 문장 오류
	 */
//	@Select({"<script>", 
//				"select * From student", 
//				"<choose>"
//				+ "<when test='grade != null' and 'height != null'>"
//				+ "where grade = #{grade} and height >= #{height}</when>"
//				+ "<when test='grade != null'>where grade = #{grade}</when>"
//				+ "<when test='height != null'>where height >= #{height}</when>"
//				+ "</choose>",
//	 		"</script>"})
//	List<Student> select2(Map<String, Object> map);
	
	/* <trim prefix='where' prefixOverrides='AND || OR'>
	 * trim : 맨 앞에 있는 문자가 AND 또는 OR 일 경우 where 로 바꿔라.
	 * map : null, empty 인 경우
	 * 		 select * from student
	 * map: grade=1
	 * 		select * from student where grade=#{grade}
	 * map: height=175
	 * 		select * from student where height>=#{height}
	 * map: grade=1, height= 175
	 * 		select * from student where grade=#{grade} and height>=#{height} 
	 */	
	@Select({"<script>", 
		"select * from student", 
		"<trim prefix='where' prefixOverrides='AND || OR'>",
		"<if test='grade != null'>and grade = #{grade}</if>",
		"<if test='height != null'>and height >= #{height}</if>",
		"</trim>",
		"</script>"})
	List<Student> select3(Map<String, Object> map);
	
	@Select({"<script>", 
			"select * From student ",
			"<if test='datas != null'>where ${columns} in"
			+"<foreach collection='datas' item='d' separator=',' open='(' close=')'>#{d}</foreach></if>",
	"</script>"})
	List<Student> select4(Map<String, Object> map);	
	/*
	 * map : datas=[101,201,301],columns=deptno1
	 * 		select * From student where deptno1 in (101,201,301)
	 * 
	 * map : datas=[63,70,82],columns=weight
	 * 		select * From student where weight in (63,70,82)
	 */
	

}