기초/SPRING
[Spring] Maria DB 연결+SQL LOG
장동규
2020. 9. 9. 17:37
src > pom.xml
목적 : DB연결에 필요한 Dependency 추가
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
<!-- Mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- Spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- Connection Pool -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!-- pretty sql log (로그이쁘게보이게) -->
<dependency>
<groupId>org.lazyluke</groupId>
<artifactId>log4jdbc-remix</artifactId>
<version>0.2.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.15</version>
<exclusions>
<exclusion>
<groupId>javax.mail</groupId>
<artifactId>mail</artifactId>
</exclusion>
<exclusion>
<groupId>javax.jms</groupId>
<artifactId>jms</artifactId>
</exclusion>
<exclusion>
<groupId>com.sun.jdmk</groupId>
<artifactId>jmxtools</artifactId>
</exclusion>
<exclusion>
<groupId>com.sun.jmx</groupId>
<artifactId>jmxri</artifactId>
</exclusion>
</exclusions>
<scope>runtime</scope>
</dependency>
AbstractDAO 생성
목적 : SQL함수 제어
public class AbstractDAO {
protected Log log = LogFactory.getLog(AbstractDAO.class);
@Autowired
private SqlSessionTemplate sqlSession;
protected void printQueryId(String queryId) {
if (log.isDebugEnabled()) {
log.debug("\t QueryId \t: " + queryId);
}
}
public Object insert(String queryId, Object params) {
printQueryId(queryId);
return sqlSession.insert(queryId, params);
}
public Object update(String queryId, Object params) {
printQueryId(queryId);
return sqlSession.update(queryId, params);
}
public Object delete(String queryId, Object params) {
printQueryId(queryId);
return sqlSession.delete(queryId, params);
}
public Object selectOne(String queryId) {
printQueryId(queryId);
return sqlSession.selectOne(queryId);
}
public Object selectOne(String queryId, Object params) {
printQueryId(queryId);
return sqlSession.selectOne(queryId, params);
}
@SuppressWarnings("rawtypes")
public List selectList(String queryId) {
printQueryId(queryId);
return sqlSession.selectList(queryId);
}
@SuppressWarnings("rawtypes")
public List selectList(String queryId, Object params) {
printQueryId(queryId);
return sqlSession.selectList(queryId, params);
}
}
src/main/resources
> config [폴더생성]
> spring [폴더생성]
> context-mapper.xml [파일생성]
> context-datasource.xml [파일생성]
> mybatis-config-xml [파일생성]
> sql [폴더생성]
*context-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:/sql/**/*_SQL.xml" />
<property name="configLocation" value="classpath:/config/spring/mybatis-config.xml" />
</bean>
<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSession" />
</bean>
</beans>sqlsession > mapperLocations > value에 자신의 sql폴더에 맞는 경로 입력
*context-datasource.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:task="http://www.springframework.org/schema/task"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task.xsd
">
<!-- MySQL -->
<bean id="dataSourceProxy" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://IPADRESS:3306/DBNAME"></property>
<property name="username" value="DB_ID"></property>
<property name="password" value="DB_PW"></property>
</bean>
<!-- SQL LOG -->
<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
<constructor-arg ref="dataSourceProxy" />
<property name="logFormatter">
<bean class="net.sf.log4jdbc.tools.Log4JdbcCustomFormatter">
<property name="loggingType" value="MULTI_LINE" />
</bean>
</property>
</bean>
<context:component-scan base-package="com.donggyu.project" />
</beans>
base-package : 자신의 패키지 이름 작성
아이디 패스워드, 주소, 스키마는 자신의 데이터에 맞게 입력
ex)
<property name="url" value="jdbc:mariadb://192.168.0.1/testDB" />
<property name="username" value="admin" />
<property name="password" value="pwAdmin1234!" />
*mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- http://www.mybatis.org/mybatis-3/ko/configuration.html -->
<setting name="cacheEnabled" value="true" />
<setting name="lazyLoadingEnabled" value="false" />
<setting name="multipleResultSetsEnabled" value="true" />
<setting name="useColumnLabel" value="true" />
<setting name="useGeneratedKeys" value="false" />
<setting name="defaultExecutorType" value="SIMPLE" />
<setting name="defaultStatementTimeout" value="25000" />
<setting name="mapUnderscoreToCamelCase" value="true" />
<setting name="jdbcTypeForNull" value="VARCHAR" />
<!-- <setting name="callSettersOnNulls" value="true" /> -->
</settings>
<!-- MyBatis mapper 파일이 추가될 때마다 지정해줘야 . <mappers> <mapper resource="com/edu/mapper/bbs/bbs.xml"/>
<mapper resource="com/edu/mapper/comment/comment.xml"/> </mappers> -->
</configuration>
*Log4j.xml 수정
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration
xmlns:log4j="http://jakarta.apache.org/log4j/">
<!-- Appenders -->
<appender name="console"
class="org.apache.log4j.ConsoleAppender">
<param name="Target" value="System.out" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p: %c - %m%n" />
</layout>
</appender>
<appender name="console-infolog"
class="org.apache.log4j.ConsoleAppender">
<param name="Target" value="System.out" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %5p %m%n" />
</layout>
</appender>
<!-- log SQL with timing information, post execution -->
<!-- 쿼리 실행 시간을 보여주는 인포 -->
<!-- <logger name="jdbc.sqltiming" additivity="false"> <level value="INFO"
/> <appender-ref ref="console-infolog" /> </logger> -->
<!-- info : 메모리 적게먹음 (sysout,debug 메모리 많이쳐먹음) -->
<!-- Application Loggers -->
<logger name="com.donggyu.project" additivity="false">
<level value="info" />
<appender-ref ref="console" />
</logger>
<!-- Query Loggers -->
<!-- 이쁘게 정렬 해주는 인포 -->
<logger name="jdbc.sqlonly" additivity="false">
<level value="INFO" />
<appender-ref ref="console-infolog" />
</logger>
<logger name="jdbc.resultsettable" additivity="false">
<level value="WARN" />
<appender-ref ref="console" />
</logger>
<logger name="org.springframework.core" additivity="false">
<level value="WARN" />
<appender-ref ref="console-infolog" />
</logger>
<logger name="org.springframework.beans" additivity="false">
<level value="INFO" />
<appender-ref ref="console-infolog" />
</logger>
<logger name="org.springframework.context" additivity="false">
<level value="INFO" />
<appender-ref ref="console-infolog" />
</logger>
<logger name="org.springframework.web" additivity="false">
<level value="WARN" />
<appender-ref ref="console-infolog" />
</logger>
<!-- Root Logger -->
<root>
<priority value="off" />
<appender-ref ref="console" />
</root>
</log4j:configuration>
유의 사항
1. DB connector의 경우 버전을 맞춰줘야 정상적으로 실행
제일 많이 나는오류 class not found, DB version 52.0, connect fail