My batis2
2023. 12. 23. 10:12ㆍDaily Codig Reminder
HashMap
List<HashMap> list= service.selectAllHashmap();
System.out.println(list);
for (HashMap dept : list) {
BigDecimal dno = (BigDecimal) dept.get("DEPTNO");//숫자 BigDecimal형태 저장
int deptno= dno.intValue();
String dname= (String) dept.get("DNAME");
String loc=(String) dept.get("LOC");
System.out.println(deptno+"\t"+dname+"\t"+loc);
}
System.out.println("=======================================");
for (HashMap dept : list) {
Set<String> keys=dept.keySet();
// System.out.println(keys);
int deptno=0;
String dname="";
String loc="";
for (String key : keys) {
if(key.equals("DEPTNO")) {
deptno= ((BigDecimal)dept.get(key)).intValue();
}
if(key.equals("DNAME")) {
dname= (String)dept.get(key);
}
if(key.equals("LOC")) {
loc= (String)dept.get(key);
}
}
System.out.println(deptno+"\t"+ dname+"\t"+ loc);
System.out.println("--------------------");
}
}
List<HashMap> list= service.selectAllHashmap();
System.out.println(list);
for (HashMap dept : list) {
BigDecimal dno = (BigDecimal) dept.get("DEPTNO");//숫자 BigDecimal형태 저장
int deptno= dno.intValue();
String dname= (String) dept.get("DNAME");
String loc=(String) dept.get("LOC");
System.out.println(deptno+"\t"+dname+"\t"+loc);
}
System.out.println("=======================================");
for (HashMap dept : list) {
Set<String> keys=dept.keySet();
// System.out.println(keys);
int deptno=0;
String dname="";
String loc="";
for (String key : keys) {
if(key.equals("DEPTNO")) {
deptno= ((BigDecimal)dept.get(key)).intValue();
}
if(key.equals("DNAME")) {
dname= (String)dept.get(key);
}
if(key.equals("LOC")) {
loc= (String)dept.get(key);
}
}
System.out.println(deptno+"\t"+ dname+"\t"+ loc);
System.out.println("--------------------");
}
}
public List<HashMap> selectAllHashmap(SqlSession session) {
List<HashMap> list = session.selectList("com.dept.DeptMapper2.selectAll2");
return list;
}
public List<Dept> selectDynamicChoose(SqlSession session, HashMap<String, String> map) {
List<Dept> list = session.selectList("com.dept.DeptMapper3.selectDynamicChoose",map);
return list;
}
<?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.dept.DeptMapper2">
<select id="selectDynamicDeptno" parameterType="hashmap" resultType="Dept">
select deptno, dname, loc
from dept
<if test="deptno != null">
where deptno= #{deptno} <!-- hashmap의 키를 이용한 값을 가져오기 -->
</if>
</select>
<select id="selectByHashMap" parameterType="hashmap" resultType="Dept">
select deptno, dname, loc
from dept
where deptno between #{key1} and #{key2}
</select>
<!-- HashMap으로 결과 얻기 -->
<select id="selectByDeptHashmap" resultType="hashmap" parameterType="int">
select deptno, dname, loc
from dept
where deptno= #{deptno}
</select>
<select id="deptRecordCount" resultType="int">
select count(*) from dept
</select>
<select id="selectAll" resultType="Dept">
select deptno, dname, loc
from dept
order by 1
</select>
<select id="selectAll2" resultType="hashmap">
select deptno, dname, loc
from dept
order by 1
</select>
</mapper>public class OralceMyBatisMain5 {
public static void main(String[] args) {
OracleMyBatisService service= new OracleMyBatisService();
int deptno=10;
HashMap<String, String> map = new HashMap<String, String>();
map.put("loc", "서울");
map.put("dname", "테스트"); //key값을 이용한 검사
//select * from dept where dname #{}
//where 절의 선택적 사용
List<Dept> list= service.selectDynamicChoose(map);
for (Dept dept : list) {
System.out.println(dept);
}
}
}
public class OracleMyBatisDAO {
// 1. 4가지 정보
public OracleMyBatisDAO() {
}
public List<Dept> selectDynamicDeptno(SqlSession session, HashMap<String, Integer> map){
System.out.println(map.get("deptno"));
List<Dept> list= session.selectList("com.dept.DeptMapper2.selectDynamicDeptno", map);
return list;
}
public List<Dept> selectByHashMap(SqlSession session, HashMap<String, Integer> map) {
List<Dept> list= session.selectList("selectByHashMap", map);
return list;
}
public HashMap selectByDeptnoHashMap(SqlSession session,int deptno) {
HashMap map=
session.selectOne("selectByDeptHashmap", deptno);
return map;
}
public int deptRecordCount(SqlSession session) {
int num= session.selectOne("deptRecordCount");
return num;
}
public void delete(SqlSession session, int deptno) {
int num = session.delete("deptDelete", deptno);
System.out.println("삭제된 갯수 ====" + num);
}
public Dept selectByDeptno(SqlSession session, int deptno) {
Dept dept = session.selectOne("com.dept.DeptMapper.selectByDeptno", deptno);
return dept;
}
public List<Dept> selectAll(SqlSession session) {
// <select id="selectAll" resultType="com.dto.dept">
// List<Dept> list= session.selectList("selectAll");
List<Dept> list = session.selectList("com.dept.DeptMapper2.selectAll");
return list;
}
public void insert(SqlSession session, Dept dept) {
int num = session.insert("com.dept.DeptMapper.deptInsert", dept);
System.out.println("추가된 레코드 ====" + num);
}
public void update(SqlSession session, Dept dept) {
int num = session.update("com.dept.DeptMapper.deptUpdate", dept);
System.out.println("업데이트 갯수====" + num);
}
public List<HashMap> selectAllHashmap(SqlSession session) {
List<HashMap> list = session.selectList("com.dept.DeptMapper2.selectAll2");
return list;
}
public List<Dept> selectDynamicChoose(SqlSession session, HashMap<String, String> map) {
List<Dept> list = session.selectList("com.dept.DeptMapper3.selectDynamicChoose",map);
return list;
}
}
public class OracleMyBatisService {
OracleMyBatisDAO dao;
public OracleMyBatisService() {
super();
// TODO Auto-generated constructor stub
dao = new OracleMyBatisDAO();
}
public List<Dept> selectDynamicDeptno(HashMap<String, Integer> map){
SqlSession session= MySqlSessionFactory.getSqlSession();
List<Dept> list= null;
try {
list= dao.selectDynamicDeptno(session, map);
}finally {
session.close();
}
return list;
}
public List<Dept> selectByHashMap(HashMap<String, Integer> map){
SqlSession session= MySqlSessionFactory.getSqlSession();
List<Dept> list= null;
try {
list=dao.selectByHashMap(session, map);
}finally {
session.close();
}
return list;
}
public HashMap selectByDeptnoHashMap(int deptno) {
SqlSession session= MySqlSessionFactory.getSqlSession();
HashMap map=null;
try {
map= dao.selectByDeptnoHashMap(session, deptno);
}finally {
session.close();
}
return map;
}
public int deptRecordCount() {
SqlSession session= MySqlSessionFactory.getSqlSession();
int num=0;
try {
num= dao.deptRecordCount(session);
}finally {
session.close();
}
return num;
}
public void delete(int deptno) {
SqlSession session= MySqlSessionFactory.getSqlSession();
try {
dao.delete(session, deptno);
session.commit();//명시적
}finally {
session.close();
}
}
public void update(Dept dept) {
SqlSession session= MySqlSessionFactory.getSqlSession();
try {
dao.update(session, dept);
session.commit();//명시적
}finally {
session.close();
}
}
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 ArrayList<Dept> select() throws SQLException{
//
// ArrayList<Dept> list= null;
// try {//con닫기
// con= DriverManager.getConnection(url, userid, passwd);
// list = dao.select();
// }finally {
//
// }
// return list;
// }
// public void insert(Dept xx) {
//
//
// try {//con닫기
//
// dao.insert( xx);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }finally {
//
// }
// }
// public void update(Dept xx) throws RecordNotFoundException {
// Connection con= null;
//
// try {//con닫기
//
// dao.update(con, xx);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }finally {
//
// }
// }
// public void delete(int i) {
//
//
// try {//con닫기
//
// dao.delete(con, i);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }finally {
//
// }
// }
public List<HashMap> selectAllHashmap() {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<HashMap> list = null;
try {
list = dao.selectAllHashmap(session);
} finally {
session.close();
}
return list;
}
public List<Dept> selectDynamicChoose(HashMap<String, String> map) {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.selectDynamicChoose(session,map);
} finally {
session.close();
}
return list;
}
}
<?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.dept.DeptMapper3">
<select id="com.dept.DeptMapper3.selectDynamicChoose" parameterType="hashmap"
resultType="Dept"> select deptno, dname, loc from dept <choose>
<when test="loc != null and dname != null">
where dname=#{dname} and loc=#{loc}
</when>
<when test="dname != null">
where dname=#{dname}
</when>
<when test="loc != null">
where loc=#{loc}
</when>
<otherwise>
<!-- where deptno = 10 -->
</otherwise>
</choose>
</select>
</mapper>
multiUpdate
public class OralceMyBatisMain5 {
public static void main(String[] args) {
OracleMyBatisService service= new OracleMyBatisService();
List<Integer> deptnoes2= Arrays.asList(12,13,50);
int num = service.multiUpdate(deptnoes2);
System.out.println("update 갯수: "+ num);
}
}
public class OracleMyBatisDAO {
// 1. 4가지 정보
public OracleMyBatisDAO() {
}
public int multiUpdate(SqlSession session, List<Integer> deptnoes2) {
int num= session.update("multiUpdate", deptnoes2);
return num;
}
}
public class OracleMyBatisService {
OracleMyBatisDAO dao;
public OracleMyBatisService() {
super();
// TODO Auto-generated constructor stub
dao = new OracleMyBatisDAO();
}
public int multiUpdate(List<Integer> deptnoes2) {
SqlSession session = MySqlSessionFactory.getSqlSession();
int num=0;
try {
num= dao.multiUpdate(session, deptnoes2);
session.commit();
}finally {
session.close();
}
return num;
}
}
<?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.dept.DeptMapper4">
<update id="multiUpdate" parameterType="arraylist">
update dept
set loc='제주'
where deptno in
<foreach item="item" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</update>
<select id="selectDynamicChoose" parameterType="hashmap"
resultType="Dept"> select deptno, dname, loc from dept <choose>
<when test="loc != null and dname != null">
where dname=#{dname} and loc=#{loc}
</when>
<when test="dname != null">
where dname=#{dname}
</when>
<when test="loc != null">
where loc=#{loc}
</when>
<otherwise>
<!-- where deptno = 10 -->
</otherwise>
</choose>
</select>
</mapper>
multiSelect
public class OralceMyBatisMain5 {
public static void main(String[] args) {
OracleMyBatisService service= new OracleMyBatisService();
List<Integer> deptnoes2= Arrays.asList(12,13,50);
int num = service.multiUpdate(deptnoes2);
System.out.println("update 갯수: "+ num);
List<Integer> deptnoes= Arrays.asList(10,20);
List<Dept> list= service.multiSelect(deptnoes);
for (Dept dept : list) {
System.out.println(dept);
}
}
}
public int multiUpdate(SqlSession session, List<Integer> deptnoes2) {
int num= session.update("multiUpdate", deptnoes2);
return num;
}
public List<Dept> multiSelect(SqlSession session, List<Integer> deptnoes) {
List<Dept> list = session.selectList("com.dept.DeptMapper4.multiSelect",deptnoes);
return list;
}
public int multiUpdate(List<Integer> deptnoes2) {
SqlSession session = MySqlSessionFactory.getSqlSession();
int num=0;
try {
num= dao.multiUpdate(session, deptnoes2);
session.commit();
}finally {
session.close();
}
return num;
}
public List<Dept> multiSelect(List<Integer> deptnoes) {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.multiSelect(session,deptnoes);
} finally {
session.close();
}
return list;
}
<?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.dept.DeptMapper4">
<update id="multiUpdate" parameterType="arraylist">
update dept
set loc='제주'
where deptno in
<foreach item="item" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</update>
<select id="selectDynamicChoose" parameterType="hashmap"
resultType="Dept"> select deptno, dname, loc from dept <choose>
<when test="loc != null and dname != null">
where dname=#{dname} and loc=#{loc}
</when>
<when test="dname != null">
where dname=#{dname}
</when>
<when test="loc != null">
where loc=#{loc}
</when>
<otherwise>
<!-- where deptno = 10 -->
</otherwise>
</choose>
</select>
<select id="multiSelect" parameterType="arraylist" resultType="Dept">
select deptno, dname, loc from dept where deptno IN
<foreach item="item" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
multiDelete
List<Dept> depts= Arrays.asList(new Dept(12,"",""),new Dept(13,"",""));
service.multiDelete(depts);
List<Dept> list= service.selectAll();
for (Dept dept : list) {
System.out.println(dept);
}
public void multiDelete(SqlSession session, List<Dept> depts) {
int num = session.delete("com.dept.DeptMapper4.multiDelete", depts);
System.out.println("삭제된 갯수 ====" + num);
}
public void multiDelete(List<Dept> depts) {
SqlSession session = MySqlSessionFactory.getSqlSession();
try {
dao.multiDelete(session, depts);
session.commit();
}finally {
session.close();
}
}
<delete id="multiDelete" parameterType="arraylist" >
delete from dept where deptno IN
<foreach item="item" collection="list" open="(" separator="," close=")">
#{item.deptno} <!-- dept 객체의 deptno 사용-->
</foreach>
</delete>
multiInsert
List<Dept> depts=
Arrays.asList(new Dept(12,"aa","aa"),new Dept(13,"bb","bb"));
service.multiInsert(depts);
List<Dept> list= service.selectAll();
for (Dept dept : list) {
System.out.println(dept);
}
}
public void multiInsert(SqlSession session, List<Dept> depts) {
int num = session.insert("com.dept.DeptMapper4.multiInsert", depts);
System.out.println("추가된 레코드 ====" + num);
}
public void multiInsert(List<Dept> depts) {
SqlSession session = MySqlSessionFactory.getSqlSession();
try {
dao.multiInsert(session, depts);
session.commit();
}finally {
session.close();
}
}
<insert id="multiInsert" parameterType="arraylist" >
insert all <!-- 주의-->
<foreach item="item" collection="list" >
into dept (deptno, dname, loc)
values (#{item.deptno},#{item.dname},#{item.loc}) <!-- dept 객체의 deptno 사용-->
</foreach>
select * from dual
<!-- 무의미한 서브퀄 작성 주의-->
</insert>selectTopN getDate
//5. TOP-N 분석 ==> 페이징 처리
List<Dept> deptList3= service.selectTopN(1,3); //idx 0부터 시작 3개
for (Dept dept : deptList3) {
System.out.println(">>>"+dept);
}
String date = service.getDate();
System.out.println(date);
}
public List<Dept> selectTopN(SqlSession session,int offset, int limit) {
List<Dept> list =
session.selectList("com.dept.DeptMapper5.topN",null,new RowBounds(offset, limit));
//id, 검색어(널) ,new RowBounds(시작 idx, 갯수)
return list;
}
public String getDate(SqlSession session) {
String date = session.selectOne("com.dept.DeptMapper5.getDate");
System.out.println("시간 ====" + date);
return date;
}
public List<Dept> selectTopN(int i, int j) { //i idx, j count값
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.selectTopN(session,i,j);
} finally {
session.close();
}
return list;
}
public String getDate() {
SqlSession session= MySqlSessionFactory.getSqlSession();
String date = null;
try {
dao.getDate(session);
}finally {
session.close();
}return date;
}
<mapper namespace="com.dept.DeptMapper5">
<select id="topN" resultType="Dept">
select deptno, dname, loc from dept
order by 1 asc
</select>
<select id="getDate" resultType="String">
select to_char (sysdate, 'yyyy/mm/dd')
from dual
</select>
selectTopN
//5. TOP-N 분석 ==> 페이징 처리
List<Dept> deptList3= service.selectTopN("CHICAGO",0,2); //idx 0부터 시작 3개
for (Dept dept : deptList3) {
System.out.println(">>>"+dept);
}
String date = service.getDate();
System.out.println(date);
}
public List<Dept> selectTopN(SqlSession session, String loc, int offset, int limit) {
List<Dept> list =
session.selectList("com.dept.DeptMapper5.topNLoc", loc, new RowBounds(offset, limit));
//id,"CHICAGO" ,new RowBounds(0,2)
return list;
}
public List<Dept> selectTopN(String loc, int i, int j) {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.selectTopN(session,loc,i,j); //검색어, 시작 idx, count
} finally {
session.close();
}
return list;
<select id="topNLoc" parameterType="string" resultType="Dept">
select deptno, dname, loc
from dept where loc = #{loc}
order by 1 asc
</select>
selectAll2
List<Dept> list2= service.selectAll2();
for (Dept dept : list2) {
System.out.println(dept);
}System.out.println("============");
}
public List<Dept> selectAll(SqlSession session) {
// <select id="selectAll" resultType="com.dto.dept">
// List<Dept> list= session.selectList("selectAll");
List<Dept> list = session.selectList("selectAll");
return list;
}
public List<Dept> selectAll2(SqlSession session) {
List<Dept> list2 = session.selectList("selectAll2");
return list2;
}
public List<Dept> selectAll() {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.selectAll(session);
} finally {
session.close();
}
return list;
}
public List<Dept> selectAll2() {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.selectAll2(session);
} finally {
session.close();
}
return list;
}
<mapper namespace="mapper5">
<select id="selectAll" resultType="com.dto.Dept"><!-- 결과를 Dept의 List형태로 -->
<![CDATA[
select deptno,dname, loc from dept where deptno >=90
]]>
</select>
<select id="selectAll2" resultType="com.dto.Dept"><!-- 결과를 Dept의 List형태로 -->
select deptno,dname, loc from dept where deptno>=90
</select>
multiSelectMap
HashMap<String, Integer> map = new HashMap<>();
map.put("key1", 10);
map.put("key2", 20);
List<Dept> list = service.multiSelectMap(map);
for (Dept dept : list) {
System.out.println(dept);
}
}
public List<Dept> multiSelectMap(SqlSession session, HashMap<String, Integer> map) {
List<Dept> list = session.selectList("com.dept.DeptMapper5.multiSelectMap",map);
return list;
}
public List<Dept> multiSelectMap(HashMap<String, Integer> map) {
SqlSession session = MySqlSessionFactory.getSqlSession();
List<Dept> list = null;
try {
list = dao.multiSelectMap(session,map);
} finally {
session.close();
}
return list;
}
<select id="multiSelectMap" parameterType="hashmap" resultType="Dept">
select deptno, dname, loc
from dept
where deptno In
(#{key1},#{key2})
</select>'Daily Codig Reminder' 카테고리의 다른 글
| jsp- 연산자 , 문장, 함수 (0) | 2023.12.24 |
|---|---|
| javascript (0) | 2023.12.24 |
| My Batis (0) | 2023.12.19 |
| JDBC 2 (0) | 2023.12.19 |
| JDBC (0) | 2023.12.14 |