[JDBC]실행결과 SQL에 반영 안될때, 다양한 SQL문 Mybatis 이용하여 JDBC 적용 예제

 

+JDBC main에서 실행은 되지만, sql에 반영되지 않을때

마지막에 session.commit();을 해줘야 한다!

 

 

try~finally문에서 try문 마지막에 적어준다.

 

-

 

MyBatis_vre2 Project:

deptRecordCount class: 행 전체 갯수를 출력해라.

delete class: deptno기준으로 행 삭제해라.

update class:

selsectByDeptNameAndLoc class:

1.dao에서 부서이름, 지역을 이용 and select 완성 후 아래처럼 수정

-dao에서 dept를 검사 부서이름만 있는 경우

지역만 있는 경우

-둘다 있는 경우를 판단하여 Mapper.xml의 id를 다흐게 호출하여 select결과 리턴

-결과적으로 sql쿼리문 3개.

 

-DeptMapper class

Sql문 삽입 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

<?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="org.mybatis.example.BlogMapper">     <insert id="deptInsert" parameterType="com.dto.Dept">           insert into dept (deptno, dname, loc) values           (#{deptno}, #{dname}, #{loc})   </insert>      <select id="selectByDeptno" parameterType="int" resultType="com.dto.Dept">     select deptno, dname, loc from dept where deptno= #{deptno} </select>                                                                      <select id="selectAll" resultType="com.dto.Dept"><!-- 결과를 Dept의 List형태로 -->     select deptno, dname, loc from dept   </select>   <delete id="deptDelete" parameterType="com.dto.Dept"> delete from dept where deptno = #{deptno} </delete>   <update id="deptUpdate" parameterType="com.dto.Dept"> update dept set dname = #{dname}, loc = #{loc} where deptno = #{deptno} </update>     <select id="deptCount" resultType="int"> select count(*) from dept </select>   <select id="selectNnL2" resultType="com.dto.Dept"> select deptno, dname, loc from dept where dname=#{dname} and loc=#{loc} </select>   <select id="selectNoL" resultType="com.dto.Dept"> select deptno, dname, loc from dept where loc=#{loc}  </select>   <select id="selectNoL1" resultType="com.dto.Dept"> select deptno, dname, loc from dept where dname=#{dname} </select>   </mapper> Colored by Color Scripter

 

-OracleMyBatisDAO

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78

package com.dao;     import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;   import org.apache.ibatis.session.SqlSession;   import com.dto.Dept; import com.exception.RecordNotFoundException;   public class OracleMyBatisDAO {     // 1. 4가지 정보         public OracleMyBatisDAO() {          }     public Dept selectByDeptno(SqlSession session, int deptno) {         Dept dept= session.selectOne("selectByDeptno", deptno);                 return dept;     }     public List<Dept> selectAll(SqlSession session){         //<select id="selectAll" resultType="com.dto.dept">         List<Dept> list= session.selectList("selectAll");         return list;     }             public void insert(SqlSession session, Dept dept) {         int num= session.insert("deptInsert", dept);         System.out.println("추가된 레코드 ===="+ num);     }     public void delete(SqlSession session,int deptno) throws RecordNotFoundException{         int num= session.delete("deptDelete", deptno);         System.out.println("삭제된 갯수 ===="+ num);         if(num==0) {             throw new RecordNotFoundException("delete 값이 없습니다.");         }     }          public int deptRecordCount(SqlSession session) throws RecordNotFoundException{         int num = session.selectOne("deptCount");         System.out.println("전체 행의 갯수는 ==="+num);         if(num==0) {             throw new RecordNotFoundException("값이 없습니다.");}         return num;     }       public void update(SqlSession session, Dept dept) throws RecordNotFoundException {         int num = session.update("deptUpdate", dept);         System.out.println("수정된 갯수==="+ num);     if(num==0) {         throw new RecordNotFoundException("update 값이 없습니다.");     }     }          public List<Dept> selectByDeptNameAndLoc(SqlSession session,Dept dept) {     //1.dao에서 부서이름, 지역을 이용 and select 완성 후 아래처럼 수정     //dao에서 dept를 검사 부서이름만 있는 경우     //지역만 있는 경우     //둘다 있는 경우를 판단하여 Mapper.xml의 id를 다흐게 호출하여 select결과 리턴         List<Dept>list = null;         if(dept.getDname() != null && dept.getLoc()!=null) {             list = session.selectList("selectNnL2", dept);}         else if(dept.getLoc()==null) {          list = session.selectList("selectNoL1", dept);}         else if(dept.getDname()==null) {         list = session.selectList("selectNoL", dept);}                  return list;              }        }   Colored by Color Scripter

 

-service

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

package com.service;   import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;   import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory;   import com.config.MySqlSessionFactory; import com.dao.OracleMyBatisDAO; import com.dto.Dept; import com.exception.RecordNotFoundException;   public class OracleMyBatisService {       OracleMyBatisDAO dao;       public OracleMyBatisService() {         super();         // TODO Auto-generated constructor stub         dao = new OracleMyBatisDAO();}     public Dept selectByDeptno(int deptno) {         SqlSession session= MySqlSessionFactory.getSqlSession();         Dept dept=null;         try {          dept=    dao.selectByDeptno(session, deptno);         }finally {             session.close();         }         return dept;}     public void insert(Dept dept) {         SqlSession session = MySqlSessionFactory.getSqlSession();         try {             dao.insert(session, dept);             session.commit();//명시적으로 꼭 해주어야 함.          } finally {             session.close();         }}     public List<Dept> selectAll() {         SqlSession session = MySqlSessionFactory.getSqlSession();         List<Dept> list = null;         try {             list = dao.selectAll(session);         } finally {             session.close();         }         return list;        }     public int deptRecordCount() throws RecordNotFoundException {         SqlSession session = MySqlSessionFactory.getSqlSession();             int num = 0;         try {             num = dao.deptRecordCount(session);         } finally {             session.close();         }         return num;}     public void delete(int deptno) throws RecordNotFoundException {         SqlSession session = MySqlSessionFactory.getSqlSession();             try {             dao.delete(session, deptno);             session.commit();         } finally {             session.close();         }        }     public void update(Dept dept) throws RecordNotFoundException {         SqlSession session = MySqlSessionFactory.getSqlSession();             try {             dao.update(session, dept);             session.commit();         } finally {             session.close();         }}         public List<Dept> selsectByDeptNameAndLoc (Dept dept){         SqlSession session = MySqlSessionFactory.getSqlSession();         List<Dept> list = null;         try {             list = dao.selectByDeptNameAndLoc(session, dept);         } finally {             session.close();         }         return list;     }          }   Colored by Color Scripter

 

-main

실행해야 함수가 많아 두개로 나눔.

-main2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

import java.sql.SQLException; import java.util.ArrayList; import java.util.List;   import com.dto.Dept; import com.exception.RecordNotFoundException; import com.service.OracleMyBatisService;   public class OralceMyBatisMain2 {       public static void main(String[] args) {         OracleMyBatisService service= new OracleMyBatisService(); //        Dept dept= service.selectByDeptno(12);                  //service.deptRecordCount();                  Dept dept = new Dept(); //        dept.setDname("영업");         dept.setLoc("제주");                      List<Dept> list= service.selsectByDeptNameAndLoc(dept);         for(Dept dept2 : list) {                         System.out.println(dept2);         }                           //        List<Dept> list= service.selectAll(); //        for (Dept dept : list) { //            System.out.println(dept); //        }       }   }   Colored by Color Scripter

-main

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

import java.sql.SQLException; import java.util.ArrayList; import java.util.List;   import com.dto.Dept; import com.exception.RecordNotFoundException; import com.service.OracleMyBatisService;   public class OralceMyBatisMain {       public static void main(String[] args) throws RecordNotFoundException {         OracleMyBatisService service= new OracleMyBatisService();         //service.insert(new Dept(99, "개발","강남")); //        service.update(new Dept(99,"영업", "서울")); //        Dept dept= service.selectByDeptno(99);                                 service.delete(90);         service.update(new Dept(90,"개발","서울"));                    //System.out.println(dept); //        List<Dept> list= service.selectAll(); //        for (Dept dept : list) { //            System.out.println(dept); //        }       }   }   Colored by Color Scripter

 

-RecordNotFoundException

Exception 발생시 사용자 정의 메세지 정의