230214 Spring Boot 데이터베이스 연결, SQL문 실행 로그 추가

데이터베이스 연결

스프링부트의 autoconfig기능은 데이터베이스 연결과 커넥션풀 연결을 자동 처리한다.

mybatis, mybatis-spring, spring-jdbc를
Mybatis-spring-boot-start로 자동설정 가능.

• DataSource를 자동감지
• SqlSessionFactory 자동생성
• @Mapper 어노테이션이 있는 인터페이스를 스캔하고 자동연결

gradle에 모듈 집어넣고

• 이때, 버전을 반드시 명시해야 한다. springboot버전과 같은 버전대로.
• 2.2.2버전으로.

implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.2'

application.properties에 마이바티스 설정만 하면 끝.

###########마이바티스 관련 설정###############
# 단축명으로 사용할 클래스의 패키지명
mybatis.type-aliases-package=com.simple.basic.command
# 매퍼 xml의 위치-classpath:/ 리소스 폴더의 하위를 나타냄
mybatis.mapper-locations=classpath:/mapper/*.xml

위치를 classpath아래로 지정했으니 resources아래에 mapper패키지 생성.
test에 만들더라도 경로는 base경로를 따라가주도록.
마찬가지로 mapper는 xml과 interface의 이름을 동일하게.
@Mapper를 인터페이스에 꼭 달아주어야 한다.

mapper.xml에 

<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

선언부 달아주기.

 

 

TestMapper.xml

<?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.simple.basic.mapper.TestMapper">
  
  	<select id="getTime" resultType="string">
  		select now()
  	</select>
  
  </mapper>

 

TestMapper.java

package com.simple.basic.mapper;

import org.apache.ibatis.annotations.Mapper;

@Mapper//스프링 부트에서는 매퍼 인터페이스에 매퍼 어노테이션을 반드시 붙여야 한다.
public interface TestMapper {

	public String getTime();
	
}

 

MybatisTest.java

package com.simple.basic;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import com.simple.basic.mapper.TestMapper;

@SpringBootTest
public class MybatisTest {
	
	@Autowired
	TestMapper testMapper;
	
	@Test
	public void testCode01() {
		System.out.println("현재시간 : "+testMapper.getTime());
	}
	
}

히카리커넥션풀도 자동임

---

SQL문 실행 로그 기능 더하기

SQL이 복잡해지면 구문적 에러를 찾기 힘들다.
SQL로그를 출력해두면 개발환경에서 SQL을 사용하기 용이하다.
단, 개발단계에서만 사용하고 운영단계에선 사용하지 않게 원상복구시킨다.

1.gradle에 dependecy 추가

-maven repository에서 log4jdbc 검색
-Log4Jdbc Log4j2 JDBC 4 1에서 가장 최신 버전으로

//sql로그
implementation 'org.bgee.log4jdbc-log4j2:log4jdbc-log4j2-jdbc4.1:1.16'

---

2.Database config 변경

-application.properties에서

spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:mysql://localhost:3306/spring?serverTimezone=Asia/Seoul
spring.datasource.username=spring
spring.datasource.password=spring

추가, 위에껀 잠깐 각주처리

 

 

 

application.properties

server.port=8383

############# 데이터베이스 연결, 커넥션 풀 자동으로 연결 #############
#local dev
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/spring?serverTimezone=Asia/Seoul
#spring.datasource.username=spring
#spring.datasource.password=spring

spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:mysql://localhost:3306/spring?serverTimezone=Asia/Seoul
spring.datasource.username=spring
spring.datasource.password=spring

###########마이바티스 관련 설정###############
# 단축명으로 사용할 클래스의 패키지명
mybatis.type-aliases-package=com.simple.basic.command
# 매퍼 xml의 위치-classpath:/ 리소스 폴더의 하위를 나타냄
mybatis.mapper-locations=classpath:/mapper/*.xml




## jsp를 뷰로 사용하려면 리졸버 뷰 선언
#spring.mvc.view.prefix=/WEB-INF/views/
#spring.mvc.view.suffix=.jsp

각주처리한 건 개발환경이 끝나고 운영환경이 됐을 때 풀어준다.

---

3.log4jdbc.log4j2.properties생성(resources아래에)

-이름을 반드시 지키기.
-file로 생성해서 넣으면 됨.

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
log4jdbc.dump.sql.maxlinelength=0
선언

 

 

log4jdbc.log4j2.properties

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
log4jdbc.dump.sql.maxlinelength=0

---

4.logback.xml설정(resources아래에)

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
	<appender name="STDOUT"
		class="ch.qos.logback.core.ConsoleAppender">
		<encoder>
			<pattern>%d{yyyyMMdd HH:mm:ss.SSS} [%thread] %-3level %logger{5} - %msg %n</pattern>
		</encoder>
	</appender>
	<logger name="jdbc" level="OFF" />
	<logger name="jdbc.sqlonly" level="OFF" />
	<logger name="jdbc.sqltiming" level="DEBUG" />
	<logger name="jdbc.audit" level="OFF" />
	<logger name="jdbc.resultset" level="OFF" />
	<logger name="jdbc.resultsettable" level="DEBUG" />
	<logger name="jdbc.connection" level="OFF" />
	<root level="INFO">
		<appender-ref ref="STDOUT" />
	</root>
</configuration>

옵션설명

• jdbc.sqlonly : SQL문만을 로그로 남기며, PreparedStatement일 경우 관련된 argument 값으로 대체된 SQL문이 보여진다.
• jdbc.sqltiming : SQL문과 해당 SQL을 실행시키는데 수행된 시간 정보(milliseconds)를 포함한다.
• jdbc.audit : ResultSet을 제외한 모든 JDBC 호출 정보를 로그로 남긴다.
• jdbc.resultset : ResultSet을 포함한 모든 JDBC 호출 정보를 로그로 남긴다.
• jdbc.resultsettable : SQL 결과 조회된 데이터의 table을 로그로 남긴다.

이외에도 여러 기능들이 있다. 구글링

 

---

여기까지 해주면 위의 MybatisTest.java출력문이

20230214 18:26:05.203 [main] INFO o.s.b.t.c.SpringBootTestContextBootstrapper - Neither @ContextConfiguration nor @ContextHierarchy found for test class [com.simple.basic.MybatisTest], using SpringBootContextLoader 
20230214 18:26:05.213 [main] INFO o.s.t.c.s.AbstractContextLoader - Could not detect default resource locations for test class [com.simple.basic.MybatisTest]: no resource found for suffixes {-context.xml, Context.groovy}. 
20230214 18:26:05.214 [main] INFO o.s.t.c.s.AnnotationConfigContextLoaderUtils - Could not detect default configuration classes for test class [com.simple.basic.MybatisTest]: MybatisTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration. 
20230214 18:26:05.403 [main] INFO o.s.b.t.c.SpringBootTestContextBootstrapper - Found @SpringBootConfiguration com.simple.basic.BootBasicApplication for test class com.simple.basic.MybatisTest 
20230214 18:26:05.584 [main] INFO o.s.b.t.c.SpringBootTestContextBootstrapper - Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.boot.test.autoconfigure.restdocs.RestDocsTestExecutionListener, org.springframework.boot.test.autoconfigure.web.client.MockRestServiceServerResetTestExecutionListener, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcPrintOnlyOnFailureTestExecutionListener, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverTestExecutionListener, org.springframework.boot.test.autoconfigure.webservices.client.MockWebServiceServerTestExecutionListener, org.springframework.boot.test.mock.mockito.MockitoTestExecutionListener, org.springframework.boot.test.mock.mockito.ResetMocksTestExecutionListener, org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.event.ApplicationEventsTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener, org.springframework.test.context.event.EventPublishingTestExecutionListener] 
20230214 18:26:05.614 [main] INFO o.s.b.t.c.SpringBootTestContextBootstrapper - Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@4baf352a, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@1bb1fde8, org.springframework.test.context.event.ApplicationEventsTestExecutionListener@15eebbff, org.springframework.boot.test.mock.mockito.MockitoTestExecutionListener@22d6f11, org.springframework.boot.test.autoconfigure.SpringBootDependencyInjectionTestExecutionListener@30990c1b, org.springframework.test.context.support.DirtiesContextTestExecutionListener@2453f95d, org.springframework.test.context.transaction.TransactionalTestExecutionListener@44828f6b, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@2dbe250d, org.springframework.test.context.event.EventPublishingTestExecutionListener@553f1d75, org.springframework.boot.test.autoconfigure.restdocs.RestDocsTestExecutionListener@6e1d8f9e, org.springframework.boot.test.autoconfigure.web.client.MockRestServiceServerResetTestExecutionListener@3e34ace1, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcPrintOnlyOnFailureTestExecutionListener@62fe6067, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverTestExecutionListener@4f071df8, org.springframework.boot.test.autoconfigure.webservices.client.MockWebServiceServerTestExecutionListener@4de41af9, org.springframework.boot.test.mock.mockito.ResetMocksTestExecutionListener@56ace400] 

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v2.7.8)

20230214 18:26:06.267 [main] INFO c.s.b.MybatisTest - Starting MybatisTest using Java 17.0.6 on DESKTOP-FH84UA5 with PID 12984 (started by IBK in C:\Users\IBK\OneDrive\바탕 화면\Course\boot\work\BootBasic) 
20230214 18:26:06.270 [main] INFO c.s.b.MybatisTest - No active profile set, falling back to 1 default profile: "default" 
20230214 18:26:06.291 [background-preinit] INFO o.h.v.i.u.Version - HV000001: Hibernate Validator 6.2.5.Final 
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
20230214 18:26:10.768 [main] INFO c.s.b.MybatisTest - Started MybatisTest in 5.076 seconds (JVM running for 6.991) 
20230214 18:26:11.902 [main] INFO c.z.h.HikariDataSource - HikariPool-1 - Starting... 
20230214 18:26:12.716 [main] INFO c.z.h.HikariDataSource - HikariPool-1 - Start completed. 
20230214 18:26:12.786 [main] DEBUG j.sqltiming -  com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
1. select now()
 {executed in 36 msec} 
20230214 18:26:12.822 [main] INFO j.resultsettable - 
|--------------------|
|now()               |
|--------------------|
|2023-02-14 18:26:12 |
|--------------------|
 
현재시간 : 2023-02-14 18:26:12
20230214 18:26:12.916 [SpringApplicationShutdownHook] INFO c.z.h.HikariDataSource - HikariPool-1 - Shutdown initiated... 
20230214 18:26:12.951 [SpringApplicationShutdownHook] INFO c.z.h.HikariDataSource - HikariPool-1 - Shutdown completed.

로 바뀐다. 해석하기 편해짐.

 

---

추가 공부할 것.

JPA, hibernate

JPA란
-인터페이스의 모음이다. 즉 실제로 동작하는 것이 아니다
-개발자가 JPA를 사용하면, JPA내부에서 JDBC API를 사용하여 SQL을 호출하여 DB와 통신한다
-즉 개발자가 SQL을 적지 않더라도 자동으로 처리한다.


hibernate
-Hibernate는 JPA의 구현체 중 하나이다.
-Hibernate가 지원하는 메서드 내부에서는 JDBC API가 동작하고 있으며,
단지 개발자가 직접 SQL을 작성하지 않을 뿐이다.