모바일운전면허증 설치, 모바일신분증 설치 oracle

운영서버가 oracle이라서 oracle로 적용하였습니다.

table 생성 쿼리가 있긴 한데 oracle 용이 아닙니다. 

table 생성 쿼리 경로 : sp-with-sdk\src\main\resources\schema.sql

table 변경 내역
 테이블 명 : tb_service
   - 칼럼 type 변경 : 칼럼명(vp) >> clob
   - 칼럼명 변경 : mode >> modes  -예약어라서 mode는 안됨
   - type 변경 : timestamp >> date로 모두 변경

 

TB_SERVICE 생성 쿼리

CREATE TABLE "TB_SERVICE"
   ( "SVCCODE" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"PRESENTTYPE" NUMBER DEFAULT 1,
"ENCRYPTTYPE" NUMBER DEFAULT 2,
"KEYTYPE" NUMBER DEFAULT 2,
"AUTHTYPE" VARCHAR2(50 BYTE),
"SPNAME" VARCHAR2(100 BYTE),
"SERVICENAME" VARCHAR2(100 BYTE),
"ATTRLIST" VARCHAR2(100 BYTE),
"PREDLIST" VARCHAR2(100 BYTE),
"CALLBACKURL" VARCHAR2(100 BYTE),
"REGDT" DATE DEFAULT SYSDATE,
"UDTDT" DATE,
 CONSTRAINT "TB_SERVICE_PK" PRIMARY KEY ("SVCCODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

   COMMENT ON COLUMN "TB_SERVICE"."SVCCODE" IS '서비스코드';
   COMMENT ON COLUMN "TB_SERVICE"."PRESENTTYPE" IS '제공유형';
   COMMENT ON COLUMN "TB_SERVICE"."ENCRYPTTYPE" IS '암호화유형';
   COMMENT ON COLUMN "TB_SERVICE"."KEYTYPE" IS '키유형';
   COMMENT ON COLUMN "TB_SERVICE"."AUTHTYPE" IS '인증유형';
   COMMENT ON COLUMN "TB_SERVICE"."SPNAME" IS '서비스명';
   COMMENT ON COLUMN "TB_SERVICE"."SERVICENAME" IS 'sp이름';
   COMMENT ON COLUMN "TB_SERVICE"."ATTRLIST" IS '값 자체를 제출하는 영지식 증명 항목 리스트';
   COMMENT ON COLUMN "TB_SERVICE"."PREDLIST" IS '검증을 위한 조건을 제시하여 조건에 맞음을 검증하는 영지식 증명 항목 리스트';
   COMMENT ON COLUMN "TB_SERVICE"."CALLBACKURL" IS '콜백URL';
   COMMENT ON COLUMN "TB_SERVICE"."REGDT" IS '등록일시';
   COMMENT ON COLUMN "TB_SERVICE"."UDTDT" IS '수정일시';

 

TB_TRX_INFO 생성 쿼리

CREATE TABLE "TB_TRX_INFO"
   ( "TRXCODE" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"SVCCODE" VARCHAR2(50 BYTE),
"MODES" VARCHAR2(50 BYTE),
"DEVICEID" VARCHAR2(100 BYTE),
"BRANCHNAME" VARCHAR2(100 BYTE),
"NONCE" VARCHAR2(100 BYTE),
"ZKPNONCE" VARCHAR2(100 BYTE),
"VPVERIFYRESULT" VARCHAR2(1 BYTE) DEFAULT 'N',
"VP" CLOB,
"TRXSTSCODE" VARCHAR2(4 BYTE) DEFAULT '0001',
"PROFILESENDDT" DATE,
"IMGSENDDT" DATE,
"VPRECEPTDT" DATE,
"ERRORCN" VARCHAR2(4000 BYTE),
"REGDT" DATE DEFAULT SYSDATE,
"UDTDT" DATE,
 CONSTRAINT "TB_TRX_INFO_PK" PRIMARY KEY ("TRXCODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("VP") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES ) ;

   COMMENT ON COLUMN "TB_TRX_INFO"."TRXCODE" IS '거래코드';
   COMMENT ON COLUMN "TB_TRX_INFO"."SVCCODE" IS '서비스코드';
   COMMENT ON COLUMN "TB_TRX_INFO"."MODES" IS '모드';
   COMMENT ON COLUMN "TB_TRX_INFO"."DEVICEID" IS '기기ID';
   COMMENT ON COLUMN "TB_TRX_INFO"."BRANCHNAME" IS '지점명';
   COMMENT ON COLUMN "TB_TRX_INFO"."NONCE" IS 'NONCE(PRESENTTYPE=1)';
   COMMENT ON COLUMN "TB_TRX_INFO"."ZKPNONCE" IS 'ZKPNONCE(PRESENTTYPE=2)';
   COMMENT ON COLUMN "TB_TRX_INFO"."VPVERIFYRESULT" IS 'VP 검증 결과 여부';
   COMMENT ON COLUMN "TB_TRX_INFO"."VP" IS 'VP DATA';
   COMMENT ON COLUMN "TB_TRX_INFO"."TRXSTSCODE" IS '거래상태코드(0001: 서비스요청, 0002: PROFILE요청, 0003: VP 검증요청, 0004: VP 검증완료)';
   COMMENT ON COLUMN "TB_TRX_INFO"."PROFILESENDDT" IS 'PROFILE 송신일시(M310)';
   COMMENT ON COLUMN "TB_TRX_INFO"."IMGSENDDT" IS '이미지 송신일시(M320)';
   COMMENT ON COLUMN "TB_TRX_INFO"."VPRECEPTDT" IS 'VP 수신일시(M400)';
   COMMENT ON COLUMN "TB_TRX_INFO"."ERRORCN" IS '오류 내용';
   COMMENT ON COLUMN "TB_TRX_INFO"."REGDT" IS '등록일시';
   COMMENT ON COLUMN "TB_TRX_INFO"."UDTDT" IS '수정일시';

 

 

SERVICE_SQL.xml (insertSvc) 수정

• current_timestamp를 date로 변경

//변경 전
<insertid="insertSvc">
INSERTINTOtb_service
(svcCode        ,presentType            ,encryptType    ,keyType        ,authType
,spName         ,serviceName            ,attrList       ,predList       ,callBackUrl
,regDt                  ,udtDt)
VALUES
(#{svcCode}         ,#{presentType}     ,#{encryptType} ,#{keyType}     ,#{authType}
,#{spName}          ,#{serviceName}     ,#{attrList}    ,#{predList}    ,#{callBackUrl}
,current_timestamp()    ,current_timestamp())
</insert>

//변경 후
<insertid="insertSvc">
INSERTINTOtb_service
(svcCode        ,presentType            ,encryptType    ,keyType        ,authType
,spName         ,serviceName            ,attrList       ,predList       ,callBackUrl
,regDt              ,udtDt)
VALUES
(#{svcCode}         ,#{presentType}     ,#{encryptType} ,#{keyType}     ,#{authType}
,#{spName}          ,#{serviceName}     ,#{attrList}    ,#{predList}    ,#{callBackUrl}
,sysdate    		,sysdate)
</insert>

 

SERVICE_SQL.xml (selectTrxInfoSvc) 수정

• 소스코드 수정을 덜 하기 위해 mode를 "mode"로 변경

<select id="selectTrxInfoSvc" resultType="mip.mva.sp.comm.vo.TrxInfoSvcVO">
SELECT a.trxcode        AS trxcode
     , a.svcCode        AS svcCode
     , a.modes          AS "mode"
     , a.deviceId       AS deviceId
     , a.branchName     AS branchName
     , a.nonce          AS nonce
     , a.zkpNonce       AS zkpNonce
     , a.vpVerifyResult AS vpVerifyResult
     , a.trxStsCode     AS trxStsCode
     , a.profileSendDt  AS profileSendDt
     , a.imgSendDt      AS imgSendDt
     , a.vpReceptDt     AS vpReceptDt
     , a.errorCn        AS errorCn
     , a.regDt          AS regDt
     , a.udtDt          AS udtDt
     , b.presentType    AS presentType
     , b.encryptType    AS encryptType
     , b.keyType        AS keyType
     , b.authType       AS authType
     , b.spName         AS spName
     , b.serviceName    AS serviceName
     , b.attrList       AS attrList
     , b.predList       AS predList
     , b.callBackUrl    AS callBackUrl
  FROM tb_trx_info a
  JOIN tb_service b
    ON a.svcCode = b.svcCode
 WHERE trxcode = #{trxcode}
</select>

 

 

TRX_INFO_SQL. xml (selectTrxInfo) 수정

• selectTrxInfo에 modes를 "mode"로 변경
• insertTrxInfo에 mode를 modes로 변경
• NVL(#{trxStsCode}, '0001')를 변경
• current_timestamp를 date로 변경
• vpVerifyResult = NVL(#{vpVerifyResult}, 'N')

<?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="mip.mva.sp.comm.dao.TrxInfoDAO">

<select id="selectTrxInfo" resultType="mip.mva.sp.comm.vo.TrxInfoVO">
SELECT trxcode
 , svcCode
 , modes as "mode"
 , deviceId
 , branchName
     , nonce
     , zkpNonce
 , vpVerifyResult
 , trxStsCode
 , profileSendDt
 , imgSendDt
 , vpReceptDt
 , vp
 , errorCn
 , regDt
 , udtDt
  FROM tb_trx_info
 WHERE trxcode = #{trxcode}
</select>

<insert id="insertTrxInfo">
INSERT
  INTO tb_trx_info
(
       trxcode
 , svcCode
 , modes
 , deviceId
 , branchName
 , trxStsCode
 , regDt
)
VALUES
(
       #{trxcode}
 , #{svcCode}
 , #{mode}
 , #{deviceId}
 , #{branchName}
<if test="trxStsCode != null and !trxStsCode.equals('')">
 , #{trxStsCode}
</if>
<if test="trxStsCode == null or trxStsCode.equals('')">
 , '0001'
</if>
 , sysdate
)
</insert>
<update id="updateTrxInfo">
UPDATE tb_trx_info
   SET trxStsCode = #{trxStsCode}
<if test="trxStsCode eq '0002'.toString()">
     , profileSendDt = sysdate
</if>
<if test="trxStsCode eq '0003'.toString()">
     , vpReceptDt = sysdate
</if>
<if test="trxStsCode eq '0004'.toString()">
 , imgSendDt = sysdate
</if>
<if test="nonce != null and !nonce.equals('')">
     , nonce = #{nonce}
</if>
<if test="zkpNonce != null and !zkpNonce.equals('')">
     , zkpNonce = #{zkpNonce}
</if>
<if test="vp != null and !vp.equals('')">
     , vp = #{vp}
</if>
<if test="errorCn != null and !errorCn.equals('')">
     , errorCn = #{errorCn}
</if>
     , udtDt = sysdate
 WHERE trxcode = #{trxcode}
</update>

<delete id="deleteTrxInfo">
DELETE
  FROM tb_trx_info
 WHERE trxcode = #{trxcode}
</delete>

</mapper>