76일차 - 로그 설정 및 동적 쿼리 예제

log4jdbc.log4j2.properties

0.00MB

logback-spring.xml

0.00MB

log4jdbc.log4j2.properties : log4j 와 db driver 매핑

logback-spring.xml : 로그 설정 파일

---

log4jdbc.log4j2.properties

logback-spring.xml

<?xml version="1.0" encoding="UTF-8"?>


<!-- logback-spring.xml 을 통해 상세한 설정이 가능하다. -->
<!-- 60초마다 설정 파일의 변경을 확인 하여 변경시 갱신 -->
<configuration scan="true" scanPeriod="60 seconds">

	<!-- 패턴 등록 -->
	<property name="LOG_PATTERN" value="%-5level %d{yy-MM-dd HH:mm:ss}[%thread] [%logger{0}:%line] - %msg%n"/>

	<!-- Appenders :  CONSOLE -->
	<appender name="console" class="ch.qos.logback.core.ConsoleAppender">	
		<!-- 출력 패턴 -->
		<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
			<pattern>${LOG_PATTERN}</pattern> 		
		</encoder>
	</appender>
		
	<!-- SQL LOG 옵션 설정 (쿼리문과 걸린시간, 결과를 보도록 한다.)-->
	<!-- 커넥션의 연결과 종료를 로그에 남김 -->	
	<logger name="jdbc.connection" level="OFF"/>
	<!-- 실행되는 쿼리문을 로그로 남김 -->
	<logger name="jdbc.sqlonly" level="OFF"/>
	<!-- 쿼리문의 + 걸린 시간 -->
	<logger name="jdbc.sqltiming" level="INFO"/>
	<!-- 쿼리를 수행하기 까지의 상세한 내용 하나하나 다 남긴다(로그가 많이 길어진다) -->
	<logger name="jdbc.audit" level="OFF"/>
	<!-- SELECT 수행시 결과인 RESULTSET 를 보여준다 -->
	<logger name="jdbc.resultset" level="OFF"/>
	<!-- 결과값을 테이블 형식(표)으로 보여준다 -->
	<logger name="jdbc.resultsettabl e" level="INFO"/>	

   	<!-- Root Logger -->
	<root>
		<appender-ref ref="console" />
	</root>
    
</configuration>

application.propeties

spring.application.name=06_Board

# db connect infomation
# <beans:property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
# <beans:property name="url" value="jdbc:mariadb://localhost:3306/mydb"/>
spring.datasource.url=jdbc:log4jdbc:mariadb://localhost:3306/mydb
# <beans:property name="username" value="web_user"/>
spring.datasource.username=web_user
# <beans:property name="password" value="pass"/>
spring.datasource.password=pass

# mapper location
# <beans:bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
# 	<beans:property name="dataSource" ref="datasource"/>
# 		<beans:property name="mapperLocations">
# 			<beans:list>
# 				<beans:value>classpath:kr/co/photo/board/dao/*.xml</beans:value>
# 				<beans:value>classpath:kr/co/photo/member/dao/*.xml</beans:value>
# 			</beans:list>
# 		</beans:property>
# 	</beans:property>
# </beans:bean>
mybatis.mapper-locations=mapper/*_mapper.xml

# PORT
server.port=80

# ENCODING:UTF-8
server.servlet.encoding.charset=UTF-8
server.servlet.encoding.enabled=true
server.servlet.encoding.force=true

# JSP(PREFIX,SUFFIX)
spring.mvc.view.prefix=/views/
spring.mvc.view.suffix=.jsp

# alias-package
mybatis.type-aliases-package=kr.co.kjw.dto

# logging
logging.level.root=info

라이브러리 설치

<!-- log4jdbc -->		
<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc4.1 -->
<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
    <version>1.16</version>
</dependency>

---

동적 쿼리 예제

application.properties

spring.application.name=07_DynamicSQL

# port
server.port=80

# encoding
server.servlet.encoding.charset=UTF-8
server.servlet.encoding.enabled=true
server.servlet.encoding.force=true

# jsp
spring.mvc.view.prefix=/views/
spring.mvc.view.suffix=.jsp

# database
# <beans:property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
# <beans:property name="url" value="jdbc:mariadb://localhost:3306/mydb"/>
spring.datasource.url=jdbc:log4jdbc:mariadb://localhost:3306/mydb
# <beans:property name="username" value="web_user"/>
spring.datasource.username=web_user
# <beans:property name="password" value="pass"/>
spring.datasource.password=pass

# mybatis
mybatis.mapper-locations=mapper/*_mapper.xml
# mybatis.mapper-locations=mapper/**/*_mapper.xml
mybatis.type-aliases-package=kr.co.gudi.dto
# mybatis.type-aliases-package=kr.co.gudi.**.dto

# logging
logging.level.root=info

 

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.7.17</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>07_DynamicSQL</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>07_DynamicSQL</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.3.2</version>
		</dependency>
		
		<!-- mariadb -->
		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
			<artifactId>mariadb-java-client</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		<!-- jasper -->
		<!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-jasper -->
		<dependency>
		    <groupId>org.apache.tomcat</groupId>
		    <artifactId>tomcat-jasper</artifactId>
		    <version>9.0.85</version>
		</dependency>
		
		<!-- jstl -->
		<!-- https://mvnrepository.com/artifact/javax.servlet.jsp.jstl/jstl -->
		<!-- BOOT 에서는 일부 라이브러리에 대해서 버젼 관리를 지원한다 -->
		<dependency>
		    <groupId>javax.servlet</groupId>
		    <artifactId>jstl</artifactId>
		</dependency>
		
		<!-- log4jdbc -->
		<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc4.1 -->
		<dependency>
		    <groupId>org.bgee.log4jdbc-log4j2</groupId>
		    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
		    <version>1.16</version>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter-test</artifactId>
			<version>3.0.3</version>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

 

// @RequestMapping(value="/join.do", method=RequestMethod.POST)
@PostMapping(value = "join.do")
public ModelAndView joinDo() {
    // Model : 보낼 데이터를 담는 객체, 인터페이스이므로 무조건 컨트롤러에서 받아서 넘겨줘야한다
    // ModelAndView : Model + View, 객체이므로 사용하고 싶은 곳에서 객체화하여 사용가능하다
    logger.info("회원가입 요청");
    return null;
}

동적쿼리에서 IF문 사용방법

	<insert id="join" parameterType="member">
		INSERT INTO member (id,pw,name,age,gender,email)
		<choose>
			<when test="!email.equals('')">
				VALUES(#{id},#{pw},#{name},#{age},#{gender},#{email})
			</when>
			<otherwise>
				VALUES(#{id},#{pw},#{name},#{age},#{gender},'없음')
			</otherwise>
		</choose>		
	</insert>

when 은 if문과 같은 역할을하고 otherwise 는 else 과 같다

 

---

동적쿼리를 이용한 검색 기능

	<!-- 
		[MS-SQL] '%' + #{keyword} + '%' 
		[ORACLE] '%' || #{keyword} || '%'
		[MYSQL] CONCAT('%', #{keyword}, '%')
	-->
	<!-- where 태그는 if에 해당되는 내용이 없을땐 동작하지 않고, 있을 경우 where 문을 넣어준다 -->
	<select id="list" parameterType="map" resultType="member">
		SELECT * FROM member 
		<where>
			<if test="opt=='id' and !keyword.equals('')">
				id LIKE CONCAT('%', #{keyword}, '%')
			</if>
			<if test="opt=='name' and !keyword.equals('')">
				name LIKE CONCAT('%', #{keyword}, '%')
			</if>
			<if test="opt=='email' and !keyword.equals('')">
				email LIKE CONCAT('%', #{keyword}, '%')
			</if>
		</where>
	</select>

[MS-SQL] '%' + #{keyword} + '%'  : 더하기 기호로 문자열을 합칠 수 있다
[ORACLE] '%' || #{keyword} || '%' : 파이프라인으로 합칠 수 있음
[MYSQL] CONCAT('%', #{keyword}, '%') : CONCAT 으로 문자열을 합쳐주는 함수를 사용

where 태그는 if에 해당되는 내용이 없을땐 동작하지 않고, 있을 경우 where 문을 넣어준다

 

실행 결과이다

---

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

https://doublesprogramming.tistory.com/83

Spring - 프로젝트 생성

맛도리 블로그 글 2017년정도 스프링 프로젝트 시작부터 현재 배우는 단계까지 잘 정리되어있음

https://dingdingmin-back-end-developer.tistory.com/entry/Springboot-Test-%EC%BD%94%EB%93%9C-%EC%9E%91%EC%84%B1-1

Springboot Test 코드 작성

테스트 방법에 대해서 유용한 글