Skip to content

Instantly share code, notes, and snippets.

@hearimm

hearimm/Mybatis-GetId-From-OracleDB.sql

Last active August 29, 2017 17:07
Show Gist options
  • Save hearimm/276bdf8f693cc4bd5a6198cd0ebbd998 to your computer and use it in GitHub Desktop.
Save hearimm/276bdf8f693cc4bd5a6198cd0ebbd998 to your computer and use it in GitHub Desktop.
Download ZIP
Mybatis-GetId-From-OracleDB
Raw
Mybatis-GetId-From-OracleDB.sql
SELECT COLUMN_NAME
,'<result property="'
||LOWER(SUBSTR(COLUMN_NAME, 0, 1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME), '_', ''), 2)
||'" column="'
||COLUMN_NAME
||'"/>' AS MYBATIS_RESULTMAP
,LOWER(SUBSTR(COLUMN_NAME, 0, 1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME), '_', ''), 2) AS UNDERSCORE_TO_CAMELCASE
,'#{'
|| LOWER(SUBSTR(COLUMN_NAME, 0, 1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME), '_', ''), 2)
|| '}' AS MYBATIS_COL_ID
,',' || COLUMN_NAME || ' = ' ||
CASE
WHEN DATA_TYPE = 'DATE' THEN 'NVL2('
END
||'#{'
|| LOWER(SUBSTR(COLUMN_NAME, 0, 1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME), '_', ''), 2)
|| '}'
|| CASE
WHEN DATA_TYPE = 'DATE' THEN ',TO_DATE(#{'
||LOWER(SUBSTR(COLUMN_NAME, 0, 1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME), '_', ''), 2)
|| '},''YYYYMMDDHH24MISS''), NULL)'
END AS MYBATIS_COL_ID_LONG
,DATA_TYPE
,DATA_PRECISION
,DATA_SCALE
,'private '
|| CASE
WHEN DATA_TYPE <> 'NUMBER' THEN 'String'
WHEN DATA_SCALE = 0 THEN 'int'
ELSE 'float'
END
|| ' '
|| LOWER(SUBSTR(COLUMN_NAME, 0, 1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME), '_', ''), 2)
|| ';' AS INIT_JAVA_VO
FROM ALL_TAB_COLS a
WHERE 1 = 1
AND TABLE_NAME = 'ADC_DL_MODEL_APLY_LOG'
COLUMN_NAME MYBATIS_RESULTMAP UNDERSCORE_TO_CAMELCASE MYBATIS_COL_ID MYBATIS_COL_ID_LONG DATA_TYPE DATA_PRECISION DATA_SCALE INIT_JAVA_VO
------------------------------ ------------------------------------------------------------------------------------------- ------------------------------ --------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- -------------- ---------- ----------------------------------------------
DL_MODEL_ID <result property="dlModelId" column="DL_MODEL_ID"/> dlModelId #{dlModelId} ,DL_MODEL_ID = #{dlModelId} VARCHAR2 private String dlModelId;
LINE_ID <result property="lineId" column="LINE_ID"/> lineId #{lineId} ,LINE_ID = #{lineId} VARCHAR2 private String lineId;
CLSF_SVR_ID <result property="clsfSvrId" column="CLSF_SVR_ID"/> clsfSvrId #{clsfSvrId} ,CLSF_SVR_ID = #{clsfSvrId} VARCHAR2 private String clsfSvrId;
DL_MODEL_APLY_DT <result property="dlModelAplyDt" column="DL_MODEL_APLY_DT"/> dlModelAplyDt #{dlModelAplyDt} ,DL_MODEL_APLY_DT = NVL2(#{dlModelAplyDt},TO_DATE(#{dlModelAplyDt},'YYYYMMDDHH24MISS'), NULL) DATE private String dlModelAplyDt;
DL_MODEL_APLYR_ID <result property="dlModelAplyrId" column="DL_MODEL_APLYR_ID"/> dlModelAplyrId #{dlModelAplyrId} ,DL_MODEL_APLYR_ID = #{dlModelAplyrId} VARCHAR2 private String dlModelAplyrId;
FST_CRER_ID <result property="fstCrerId" column="FST_CRER_ID"/> fstCrerId #{fstCrerId} ,FST_CRER_ID = #{fstCrerId} VARCHAR2 private String fstCrerId;
FST_CRE_DT <result property="fstCreDt" column="FST_CRE_DT"/> fstCreDt #{fstCreDt} ,FST_CRE_DT = NVL2(#{fstCreDt},TO_DATE(#{fstCreDt},'YYYYMMDDHH24MISS'), NULL) DATE private String fstCreDt;
FNL_UPDR_ID <result property="fnlUpdrId" column="FNL_UPDR_ID"/> fnlUpdrId #{fnlUpdrId} ,FNL_UPDR_ID = #{fnlUpdrId} VARCHAR2 private String fnlUpdrId;
FNL_UPD_DT <result property="fnlUpdDt" column="FNL_UPD_DT"/> fnlUpdDt #{fnlUpdDt} ,FNL_UPD_DT = NVL2(#{fnlUpdDt},TO_DATE(#{fnlUpdDt},'YYYYMMDDHH24MISS'), NULL) DATE private String fnlUpdDt;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment