1. TIL
A. Config Customizing
기존의 설정에 관련된 파일을 xml로 관리했지만 java파일로 재구성
1. WebConfig
package com.spring.configuration;
import javax.servlet.Filter;
import org.springframework.web.filter.CharacterEncodingFilter;
import org.springframework.web.filter.HiddenHttpMethodFilter;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;
public class WebConfig extends AbstractAnnotationConfigDispatcherServletInitializer {
@Override
protected Class<?>[] getRootConfigClasses() {
return new Class[] {RootConfig.class};
}
@Override
protected Class<?>[] getServletConfigClasses() {
return new Class[] {ServletConfig.class};
}
@Override
protected String[] getServletMappings() {
return new String[] {"/"};
}
@Override
protected Filter[] getServletFilters() {
// encoding filter
CharacterEncodingFilter encodingFilter = new CharacterEncodingFilter();
encodingFilter.setEncoding("UTF-8");
// hiddenHttpMethodFilter
HiddenHttpMethodFilter hiddenHttpMethodFilter = new HiddenHttpMethodFilter();
return new Filter[] {encodingFilter, hiddenHttpMethodFilter};
}
}2. RootConfig
DBConfiguration을 여기에 정의해도 되지만 분리하는게 더 바람직함
package com.spring.configuration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
@Configuration
@ComponentScan(basePackages = {"com.spring.*"})
@MapperScan(basePackages = {"com.spring.mapper"})
public class RootConfig {
}3. ServletConfig
package com.spring.configuration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.ViewResolverRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import com.spring.interceptor.SessionInterceptor;
@Configuration
@EnableWebMvc
@ComponentScan("com.spring.mvc")
public class ServletConfig implements WebMvcConfigurer{
// void addResourceHandlers(ResourceHandlerRegistry registry)
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
WebMvcConfigurer.super.addResourceHandlers(registry);
registry.addResourceHandler("/**").addResourceLocations("/resources/");
}
// void configureViewResolvers(ViewResolverRegistry registry)
@Override
public void configureViewResolvers(ViewResolverRegistry registry) {
WebMvcConfigurer.super.configureViewResolvers(registry);
registry.jsp("/WEB-INF/views/", ".jsp");
}
// default void addInterceptors(InterceptorRegistry registry)
@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(new SessionInterceptor())
.addPathPatterns("/dept/***")
.addPathPatterns("/logout")
.excludePathPatterns("/main")
.excludePathPatterns("/login");
}
@Bean
public CommonsMultipartResolver multipartResolver() {
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver();
multipartResolver.setDefaultEncoding("UTF-8");
return multipartResolver;
}
}4. DBConfig (mybatis)
package com.spring.configuration;
import java.io.InputStream;
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 org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class DBConfiguration {
static String resource = "/mybatis-config.xml";
@Bean
public static SqlSessionFactory getSqlSessionFactory() {
SqlSessionFactoryBuilder sqlSessionFactoryBuilder;
SqlSessionFactory sqlSEssionFactory = null;
InputStream is = null;
try {
is = Resources.getResourceAsStream(resource);
sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
sqlSEssionFactory = sqlSessionFactoryBuilder.build(is);
}catch (Exception e) {
e.printStackTrace();
}
return sqlSEssionFactory;
}
@Bean
public static SqlSession getSqlSession() {
SqlSession sqlSession = null;
sqlSession = getSqlSessionFactory().openSession();
return sqlSession;
}
}
B. Dynamic SQL
전달되는 파라미터에 따른 동적인 SQL Query를 생성하는 문법
<if> : test 속성을 만족하면 해당 쿼리 추가(단일 조건)
- "test" 속성의 값에는 '<' 문자가 포함되면 안됨 (대체식으로 lt, gt, lte, gte 사용)
CDATA 태그는 XML 문서 안에 특수 문자를 포함시킬 때 사용
- 단일 처리 :
<![CDATA[ 사용될 부호 ]]> - 다중 처리 :
<select id="getUser" resultMap="User">
<![CDATA[ SELECT * FROM user WHERE id > 3 ]]> </select>trim
-
prefixOverrides: 하위 요소 처리 후 내용의 맨 앞에 해당 문자열이 있다면 삭제
-
suffixOverrides: 하위 요소 처리 후 내용의 맨 뒤에 해당 문자열이 있다면 삭제
-
prefix: 하위 요소 처리 후 내용이 있다면 가장 앞에 붙임
-
suffix: 하위 요소 처리 후 내용이 있다면 가장 뒤에 붙임
1. Controller
객체를 Builder 패턴으로 만들거나 HashMap을 이용해서 만들 수 있음
// if
@RequestMapping(value = "/api/dynamic-sql", method = RequestMethod.GET)
public String getAPIDynamicSQL() throws Exception {
System.out.println("getAPIDynamicSQL()");
String result = "";
// getDynamicIf
HashMap<String, Integer> map1 = new HashMap<String, Integer>();
map1.put("deptno", 10);
List<Dept> result1 = deptMapper.getDynamicDeptno(map1);
// getDynamicChoose
// SELECT * FROM WHERE dname = "SALES" loc = "DALLAS"
// Choose : 조건에 만족하는 첫번째 값을 가져옴
HashMap<String, String> map2 = new HashMap<String, String>();
map2.put("loc", "DALLAS");
map2.put("dname", "SALES");
List<Dept> result2 = deptMapper.getDynamicChoose(map2);
// getDynamicWhereTrim
HashMap<String, String> map3 = new HashMap<String, String>();
map3.put("dname", "SALES");
map3.put("loc", "DALLAS");
List<Dept> result3 = deptMapper.getDynamicWhereTrim(map3);
// Dept 객체
List<Dept> result4 = deptMapper.getDynamicWhereTrim2(new Dept(0, null, "DALLAS"));
// set : UPDATE dept SET loc "LA" WHERE deptno = 40
HashMap<String, Object> map5 = new HashMap<String, Object>();
map5.put("deptno", 40);
map5.put("dname", null);
map5.put("loc", "LA");
Integer result5 = deptMapper.updateDynamicSet(map5);
// foreach : SELECT, UPDATE, DELETE
// SELECT * FROM dept WHERE deptno in (10, 20, 30)
List<Integer> list6 = Arrays.asList(10, 20, 30);
List<Dept> result6 = deptMapper.getDynamicForeachDeptno(list6);
// foreach : INSERT
/*
INSERT ALL
INTO dept (deptno, dname, loc) VALUES (deptno, dname, loc)
INTO dept (deptno, dname, loc) VALUES (deptno, dname, loc)
SELECT * FROM dual;
*/
List<Dept> list7 = Arrays.asList(
new Dept(77, "PIZZA", "JEJU"),
new Dept(78, "DUPBAB", "SEOUL"));
Integer result7 = deptMapper.insertDynamicForeachDeptList(list7);
result = result7.toString();
return result;
}2. Mapper.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="com.spring.mapper.DeptMapper">
<!-- 반환 객체 타입 - Map -->
<resultMap type="com.spring.dto.Dept" id="selectDeptMap">
<result column="deptno" property="deptno"/>
<result column="dname" property="dname"/>
<result column="loc" property="loc"/>
</resultMap>
<!-- getDynamicDeptno -->
<select id="getDynamicDeptno" parameterType="hashmap" resultType="com.spring.dto.Dept">
SELECT deptno, dname, loc FROM dept
<if test="deptno != null">
WHERE deptno = #{deptno}
</if>
</select>
<!-- getDynamicChoose -->
<select id="getDynamicChoose" parameterType="hashmap" resultType="com.spring.dto.Dept">
SELECT deptno, dname, loc FROM dept
<choose>
<when test="dname != null">
WHERE dname = #{dname}
</when>
<when test="loc != null">
WHERE loc = #{loc}
</when>
<otherwise></otherwise>
</choose>
</select>
<!-- getDynamicWhereTrim -->
<select id="getDynamicWhereTrim" parameterType="hashmap" resultType="com.spring.dto.Dept">
SELECT deptno, dname, loc FROM dept
<trim prefix="WHERE" prefixOverrides="OR |AND">
<if test="dname != null">
dname = #{dname}
</if>
<if test="loc != null">
AND loc = #{loc}
</if>
</trim>
</select>
<!-- getDynamicWhereTrim2 -->
<select id="getDynamicWhereTrim2" resultType="com.spring.dto.Dept">
SELECT deptno, dname, loc FROM dept
<trim prefix="WHERE" prefixOverrides="OR |AND">
<if test="dname != null">
dname = #{dname}
</if>
<if test="loc != null">
AND loc = #{loc}
</if>
</trim>
</select>
<!-- updateDynamicSet -->
<update id="updateDynamicSet" parameterType="hashmap">
UPDATE dept
<!-- <set>
<if test="dname != null">
dname = #{dname}
</if>
<if test="loc != null">
,loc = #{loc}
</if>
</set> -->
<trim prefix="SET" suffixOverrides=",">
<if test="dname != null">
dname = #{dname},
</if>
<if test="loc != null">
loc = #{loc}
</if>
</trim>
WHERE deptno = #{deptno}
</update>
<!-- getDynamicForeachDeptno -->
<select id="getDynamicForeachDeptno" resultType="com.spring.dto.Dept">
SELECT deptno, dname, loc FROM dept
<where>
<foreach item="deptno" collection="list"
open="deptno IN (" separator="," close=")">
#{deptno}
</foreach>
</where>
</select>
<!-- insertDynamicForeachDeptList -->
<insert id="insertDynamicForeachDeptList" parameterType="arrayList">
INSERT ALL
<foreach item="dept" collection="list">
INTO DEPT (deptno, dname, loc)
VALUES (#{dept.deptno}, #{dept.dname}, #{dept.loc})
</foreach>
SELECT * FROM dual
</insert>
</mapper>3. Mapper.java
@Mapper
public interface DeptMapper {
// getDynamicDeptno
public List<Dept> getDynamicDeptno(HashMap<String, Integer> map1) throws SQLException;
// getDynamicChoose
public List<Dept> getDynamicChoose(HashMap<String, String> map2) throws SQLException;
// getDynamicWhereTrim
public List<Dept> getDynamicWhereTrim(HashMap<String, String> map3) throws SQLException;
// getDynamicWhereTrim2
public List<Dept> getDynamicWhereTrim2(Dept dept) throws SQLException;
// updateDynamicSet
public Integer updateDynamicSet(HashMap<String, Object> map5) throws SQLException;
// getDynamicForeachDeptno
public List<Dept> getDynamicForeachDeptno(List<Integer> list6) throws SQLException;
// insertDynamicForeachDeptList
public Integer insertDynamicForeachDeptList(List<Dept> list7) throws SQLException;
}
2. 에러
<trim prefix="WHERE" prefixOverrides="OR |AND">
"OR |AND" 띄어쓰기로 "OR | AND"로 사용될 경우 데이터를 못찾는 경우가 발생함
3. 보완 해야 할 것
Config 커스터마이징은 xml에서 java 파일로 관리하도록 바뀐 것이므로 내용을 외우기 보다는 흐름에 맞는 어노테이션, 오버라이딩으로 설정을 관리했음
4. 느낀점
trim으로 WHERE의 조건을 다양하게 줄 수 있었음 (동적으로 쿼리를 사용 가능)
foreach는 기존 기능처럼 반복을 수행하는 로직으로 활용됨
open="(" separator="," close=")" 를 사용하여 특정 블록에 반복 로직을 따로 정할 수 있었음
잘해볼게요