간단 예제 따라하기 -MS-SQL 적용

예제 출처 : http://www.egovframe.org/wiki/doku.php?id=egovframework:dev2:clntinstall

예제를 따라하다보면

예제프로젝트를 e_demo 라고 하면

ms-sql 용으로 사용하기 위해 몇가지 작업이 필요합니다.

1) jtds 드라이버 추가합니다.

/e_demo/src/main/webapp/WEB-INF/lib/ 폴더에

다운로드 :  jtds-1.2.5.jar

2)context-datasource.xml 수정하기

/e_demo/src/main/resources/egovframework/spring/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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
   
    <!-- hsql 
     <bean id="dataSource" class="org.apache.commons.dbcp.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
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://db2:1621/rte"/>
        <property name="username" value="rte"/>
        <property name="password" value="rte01"/>
    </bean>
    -->
    <!-- oracle
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@db1:1521:egovfrm"/>
        <property name="username" value="rte"/>
        <property name="password" value="rte01"/>
    </bean>
    -->

    <!-- mssql -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver"/>
        <property name="url" value="jdbc:jtds:sqlserver://127.0.0.1:1444/db명"/>
        <property name="username" value="sa"/>
        <property name="password" value="비밀번호"/>
    </bean>
   

</beans>

4) sql-map-config.xml 수정

/e_demo/src/main/resources/egovframework/sqlmap/rte/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/rte/sample/EgovSample_Sample_SQL.xml"/> -->
    <!-- <sqlMap resource="egovframework/sqlmap/rte/sample/EgovSample_Sample_SQL_mysql.xml"/> -->
    <!-- <sqlMap resource="egovframework/sqlmap/rte/sample/EgovSample_Sample_SQL_oracle.xml"/> -->
    <sqlMap resource="egovframework/sqlmap/rte/sample/EgovSample_Sample_SQL_mssql.xml"/>

</sqlMapConfig>

5) EgovSample_Sample_SQL_mssql.xml 추가

/e_demo/src/main/resources/egovframework/sqlmap/rte/sample/EgovSample_Sample_SQL_mssql.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Sample">

    <typeAlias  alias="egovMap" type="egovframework.rte.psl.dataaccess.util.EgovMap"/>
    <typeAlias  alias="searchVO" type="egovframework.rte.sample.service.SampleDefaultVO"/>

    <resultMap id="sample" class="egovframework.rte.sample.service.SampleVO">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="description" column="description"/>
        <result property="useYn" column="use_yn"/>
        <result property="regUser" column="reg_user"/>
    </resultMap>

    <insert id="sampleDAO.insertSample_S">
        <![CDATA[
            INSERT INTO SAMPLE
                ( ID
                  , NAME
                  , DESCRIPTION
                  , USE_YN
                  , REG_USER )
            VALUES ( #id#
                  , #name#
                  , #description#
                  , #useYn#
                  , #regUser# )
        ]]>
    </insert>
   
    <update id="sampleDAO.updateSample_S">
        <![CDATA[
            UPDATE SAMPLE
            SET ID=#id#
                , NAME=#name#
                , DESCRIPTION=#description#
                , USE_YN=#useYn#
            WHERE ID=#id#
        ]]>
    </update>
   
    <delete id="sampleDAO.deleteSample_S">
        <![CDATA[
            DELETE FROM SAMPLE
            WHERE ID=#id#
        ]]>
    </delete>
   
    <select id="sampleDAO.selectSample_S" resultMap="sample">
        <![CDATA[
            SELECT
                ID, NAME, DESCRIPTION, USE_YN, REG_USER
            FROM SAMPLE
            WHERE ID=#id#
        ]]>
    </select>
   
<!--
    <select id="sampleDAO.selectSampleList_D" parameterClass="searchVO" resultClass="egovMap">

            SELECT
                /* ID id, NAME name, DESCRIPTION description, USE_YN useYn, REG_USER regUser */
                ID, NAME, DESCRIPTION, USE_YN, REG_USER
            FROM SAMPLE
            WHERE 1=1
            <isEqual prepend="AND" property="searchCondition" compareValue="0">
                ID = #searchKeyword#
            </isEqual>
            <isEqual prepend="AND" property="searchCondition" compareValue="1">
                NAME LIKE '%' + #searchKeyword# + '%'
            </isEqual>
            ORDER BY ID DESC
            limit #firstIndex#, #recordCountPerPage#   
    </select>
-->   
    <select id="sampleDAO.selectSampleList_D" parameterClass="searchVO" resultClass="egovMap">

            SELECT
                TOP $recordCountPerPage$
                ID, NAME, DESCRIPTION, USE_YN, REG_USER
            FROM SAMPLE
            WHERE 1=1
            <isEqual prepend="AND" property="searchCondition" compareValue="0">
                ID = #searchKeyword#
            </isEqual>
            <isEqual prepend="AND" property="searchCondition" compareValue="1">
                NAME LIKE '%' + #searchKeyword# + '%'
            </isEqual>
            AND ID NOT IN
            ( SELECT TOP $firstIndex$ ID FROM SAMPLE
                WHERE 1=1
                <isEqual prepend="AND" property="searchCondition" compareValue="0">
                    ID = #searchKeyword#
                </isEqual>
                <isEqual prepend="AND" property="searchCondition" compareValue="1">
                    NAME LIKE '%' + #searchKeyword# + '%'
                </isEqual>
                ORDER BY ID DESC )           
            ORDER BY ID DESC
    </select>

    <select id="sampleDAO.selectSampleListTotCnt_S" parameterClass="searchVO" resultClass="int">

            SELECT COUNT(*) totcnt
            FROM SAMPLE
            WHERE 1=1
            <isEqual prepend="AND" property="searchCondition" compareValue="0">
                ID = #searchKeyword#
            </isEqual>
            <isEqual prepend="AND" property="searchCondition" compareValue="1">
                NAME LIKE '%' + #searchKeyword# + '%'
            </isEqual>
    </select>

</sqlMap>

<!--
아주 간단하게 설명하겠다.
 board 라는 테이블에 num(pk), title, content 필드가 있다고 하면..
MY-SQL 쿼리문,
SELECT num(pk), title, content FROM board ORDER BY num(pk) DESC limit 시작인덱스, 표시할 갯수
아주 간단하다.. MS에서도 나와주기만을 바랄뿐..
하지만 MS-SQL에서도 방법은 있다.
MS-SQL 쿼리문,
SELECT TOP 표시할 갯수 num(pk), title, content FROM board WHERE num(pk) NOT IN (SELECT TOP 시작인덱스 num(pk) FROM board ORDER BY PK필드 DESC) ORDER BY num(pk) DESC
-->

6) sampledb_mssql.script 추가

/e_demo/DATABASE/db/sampledb_mssql.script

DROP TABLE IF EXISTS  SAMPLE ;

CREATE TABLE SAMPLE (
  ID VARCHAR(16) NOT NULL,
  NAME VARCHAR(50) DEFAULT NULL,
  DESCRIPTION VARCHAR(100) DEFAULT NULL,
  USE_YN CHAR(1) DEFAULT NULL,
  REG_USER VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (ID)
);


--DROP TABLE IF EXISTS  IDS ;

CREATE TABLE IDS (
  TABLE_NAME VARCHAR(16) NOT NULL,
  NEXT_ID DECIMAL(30) NOT NULL,
  PRIMARY KEY (TABLE_NAME)
);

INSERT INTO IDS VALUES('SAMPLE',0);

--COMMIT;

7) sampledb_data_mssql.script 추가

/e_demo/DATABASE/db/sampledb_data_mssql.script

INSERT INTO IDS VALUES('SAMPLE', 0);
--COMMIT;

[실행결과 캡쳐]