른록노트
[Spring] 전자정부 프레임워크 MSSQL 연결 (3.10) 본문
1. 기본 예제 프로젝트에서 진행
참고사이트 - https://llnote.tistory.com/753
2. 소스 파일 수정
2.1. maven pom.xml 설정 파일 수정
https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc/6.4.0.jre8
<dependencies>
<!-- 표준프레임워크 실행환경 -->
<dependency>
<groupId>egovframework.rte</groupId>
<artifactId>egovframework.rte.ptl.mvc</artifactId>
<version>${egovframework.rte.version}</version>
<exclusions>
<exclusion>
<artifactId>commons-logging</artifactId>
<groupId>commons-logging</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>egovframework.rte</groupId>
<artifactId>egovframework.rte.psl.dataaccess</artifactId>
<version>${egovframework.rte.version}</version>
</dependency>
<dependency>
<groupId>egovframework.rte</groupId>
<artifactId>egovframework.rte.fdl.idgnr</artifactId>
<version>${egovframework.rte.version}</version>
</dependency>
<dependency>
<groupId>egovframework.rte</groupId>
<artifactId>egovframework.rte.fdl.property</artifactId>
<version>${egovframework.rte.version}</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
<dependency>
<groupId>org.antlr</groupId>
<artifactId>antlr</artifactId>
<version>3.5</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.4.0.jre8</version>
</dependency>
<!-- hsqldb
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.5.0</version>
</dependency>
-->
<!-- mysql이나 oracle DB 사용시 아래 설정 추가
<dependency>
<groupId>com.googlecode.log4jdbc</groupId>
<artifactId>log4jdbc</artifactId>
<version>1.2</version>
<exclusions>
<exclusion>
<artifactId>slf4j-api</artifactId>
<groupId>org.slf4j</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
<dependency>
<groupId>ojdbc</groupId>
<artifactId>ojdbc</artifactId>
<version>14</version>
<scope>system</scope>
<systemPath>${basedir}/src/main/webapp/WEB-INF/lib/ojdbc-14.jar</systemPath>
</dependency>
-->
</dependencies>2.2. src/main/resources/globals.properties 파일 생성
DbType=mssql
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=[mssql 서버]
jdbc.username=[아이디]
jdbc.password=[비밀번호]2.3. 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:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">
<!-- globals.properties 파일에 등록된 DB정보를 사용하여 datasource 정보를 정의한다. -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:/egovframework/globals.properties</value>
</list>
</property>
</bean>
<!-- datasource 설정(propertyConfigurer 활용) : 다른곳에서도 dataSource라는 이름으로 사용할 수 있게해 줌-->
<alias name="dataSource-${DbType}" alias="dataSource" />
<alias name="dataSource-${DbType}" alias="egov.dataSource" />
<!-- Mysql (POM에서 commons-dbcp, mysql-connector-java 관련 라이브러리 설정 ) -->
<bean id="dataSource-mssql" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 테스트 실행용
<jdbc:embedded-database id="dataSource" type="HSQL">
<jdbc:script location= "classpath:/db/sampledb.sql"/>
</jdbc:embedded-database>
-->
<!-- hsql (테스트용 메모리 DB)
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="net.sf.log4jdbc.DriverSpy"/>
<property name="url" value="jdbc:log4jdbc:hsqldb:hsql://localhost/sampledb"/>
<property name="username" value="sa"/>
</bean>
-->
<!-- Mysql (POM에서 commons-dbcp, mysql-connector-java 관련 라이브러리 설정 )
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/example" />
<property name="username" value="user"/>
<property name="password" value="password"/>
</bean>
-->
<!-- oracle (POM에서 commons-dbcp, ojdbc(라이센스 사항으로 별도로 배포되지 않음) 관련 라이브러리 설정)
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:example" />
<property name="username" value="user"/>
<property name="password" value="password"/>
</bean>
-->
</beans>2.4. context-sqlMap.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd">
<!-- SqlMap setup for iBATIS Database Layer -->
<!-- datasource 설정(propertyConfigurer 활용) -->
<bean id="sqlMapClient" class="egovframework.rte.psl.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation" value="classpath:/egovframework/sqlmap/example/sql-map-config.xml"/>
<property name="configLocations">
<list>
<value>classpath:/egovframework/sqlmap/config/${DbType}/*.xml</value>
</list>
</property>
<property name="dataSource" ref="dataSource-${DbType}"/>
</bean>
</beans>2.5. 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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">
<!-- SqlSession setup for MyBatis Database Layer -->
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:/egovframework/sqlmap/config/${DbType}/sql-mapper-config.xml" />
<property name="mapperLocations" value="classpath:/egovframework/sqlmap/config/${DbType}/mappers/*.xml" />
</bean>
<!-- MapperConfigurer setup for MyBatis Database Layer with @Mapper("deptMapper") in DeptMapper Interface -->
<bean class="egovframework.rte.psl.dataaccess.mapper.MapperConfigurer">
<property name="basePackage" value="egovframework.example.sample.service.impl" />
</bean>
</beans>2.6. src/main/resources/egovframework/sqlmap/config/mssql 폴더 추가 후 src/main/resources/egovframework/sqlmap/example 폴더의 내용을 복사하여 mssql 폴더에 붙혀넣고 mssql폴더 안에 sql-map-config.xml 파일 수정
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<sqlMap resource="egovframework/sqlmap/config/mssql/sample/EgovSample_Sample_SQL.xml"/> <!-- mapper 폴더는 mybatis, sample 폴더는 ibatis -->
</sqlMapConfig>2.7. DB에 테이블 생성 후 selectSampleList이 작성된 *SQL.xml 수정
src/main/resources/egovframework/sqlmap/config/mssql/mappers/EgovSample_Sample.SQL.xml
<select id="selectSampleList" parameterType="searchVO" resultType="egovMap">
SELECT
ID, NAME, DESCRIPTION, USE_YN, REG_USER
FROM SAMPLE
WHERE 1=1
<if test="searchKeyword != null and searchKeyword != ''">
<choose>
<when test="searchCondition == 0">
AND ID LIKE '%' || #{searchKeyword} || '%'
</when>
<when test="searchCondition == 1">
AND NAME LIKE '%' || #{searchKeyword} || '%'
</when>
</choose>
</if>
ORDER BY ID ASC
OFFSET #{firstIndex} ROWS
FETCH NEXT #{recordCountPerPage} ROWS ONLY
</select>src/main/resources/egovframework/sqlmap/config/mssql/sample/EgovSample_Sample.SQL.xml
<select id="sampleDAO.selectSampleList" parameterClass="searchVO" resultClass="egovMap">
SELECT
ID, NAME, DESCRIPTION, USE_YN, REG_USER
FROM SAMPLE
WHERE 1=1
<isEqual prepend="AND" property="searchCondition" compareValue="0">
ID LIKE '%' || #searchKeyword# || '%'
</isEqual>
<isEqual prepend="AND" property="searchCondition" compareValue="1">
NAME LIKE '%' || #searchKeyword# || '%'
</isEqual>
ORDER BY ID DESC
OFFSET #firstIndex# ROWS
FETCH NEXT #recordCountPerPage# ROWS ONLY
</select>3. 서버 실행하여 확인
4. mapper xml 파일에서 등록, 수정, 삭제 쿼리 mssql에 맞게 쿼리 수정
참고사이트
https://devhj.tistory.com/17
https://www.egovframe.go.kr/wiki/doku.php?id=egovframework:let:configration&s%5B%5D=propertiesservice
반응형
Comments