-
[Spring] Maria DB 연결+SQL LOG기초/SPRING 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 [폴더생성]
src > main > resources *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
'기초 > SPRING' 카테고리의 다른 글
[Spring] Login Session 추가 (0) 2020.09.28 [Spring] Mysql 계층적 쿼리구조 (0) 2020.09.23 Spring - Interceptor 적용 (0) 2020.08.28 Spring - Servelt 설정변경 (0) 2020.08.28 부트스트랩 적용 및 resources 지정 (0) 2020.08.28