Java2023. 3. 26. 00:57

JAXEN을 이용하여 XML파싱

package xml;

import org.jaxen.XPath;
import org.jaxen.dom.DOMXPath;
import org.w3c.dom.Element;
import org.xml.sax.InputSource;

import javax.xml.parsers.DocumentBuilderFactory;
import java.io.StringReader;
import java.util.List;

public class JaxenMain {
    public static void main(String[] args) throws Exception {
        String xml = "<ROOT><META>메타정보</META><BOOKS><BOOK><AUTHOR>BONG</AUTHOR><TITLE>BONG'S STORY</TITLE></BOOK><BOOK><AUTHOR>HONG</AUTHOR><TITLE>HONG'S STORY</TITLE></BOOK><BOOK><AUTHOR>KIM</AUTHOR><TITLE>KIM'S STORY</TITLE></BOOK></BOOKS></ROOT>";

        XPath xpath = new DOMXPath("/ROOT/META");
        List<Element> elements = xpath.selectNodes(DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new InputSource(new StringReader(xml))).getDocumentElement());

        for (Element element : elements) {
            System.out.println(element.getNodeName());
            System.out.println(element.getTextContent());
        }

       xpath = new DOMXPath("/ROOT/BOOKS/BOOK/AUTHOR");
        elements = xpath.selectNodes(DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new InputSource(new StringReader(xml))).getDocumentElement());

        for (Element element : elements) {
            System.out.println(element.getNodeName());
            System.out.println(element.getTextContent());
        }

        xpath = new DOMXPath("/ROOT/BOOKS/BOOK/TITLE");
        elements = xpath.selectNodes(DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new InputSource(new StringReader(xml))).getDocumentElement());

        for (Element element : elements) {
            System.out.println(element.getNodeName());
            System.out.println(element.getTextContent());
        }
    }
}

 

 

[참고]

https://openai.com/blog/chatgpt

 

Introducing ChatGPT

We’ve trained a model called ChatGPT which interacts in a conversational way. The dialogue format makes it possible for ChatGPT to answer followup questions, admit its mistakes, challenge incorrect premises, and reject inappropriate requests.

openai.com

 

Posted by 이야봉
DB2022. 3. 9. 14:00

[목표]  '[코드]품명' 형태의 문자열에서 '코드' 추출하기 

o 예시 데이터
          [SM-G981N]갤럭시 S20
          [SM-G991N]갤럭시 S21
          [SM-S901N]갤럭시 S22

 

[쿼리]

SELECT SUBSTRING(A.PRODUCT FROM '\[(.*?)\]') AS PRODUCT_CODE,       
                 A.PRODUCT
FROM
      ( 
      	SELECT '[SM-G981N]갤럭시 S20' AS PRODUCT
        UNION ALL
      	SELECT '[SM-G991N]갤럭시 S21'
        UNION ALL
        SELECT '[SM-S901N]갤럭시 S22'
      ) A

 

[수행결과]

 

[참고]

https://popsql.com/learn-sql/postgresql/how-to-use-substring-with-regular-expressions-in-postgresql

 

How to Use substring() with Regular Expressions in PostgreSQL - PopSQL

 

popsql.com

 

http://daplus.net/regex-%EB%8C%80%EA%B4%84%ED%98%B8-%EC%82%AC%EC%9D%B4%EC%97%90%EC%84%9C-%ED%85%8D%EC%8A%A4%ED%8A%B8%EB%A5%BC-%EC%B6%94%EC%B6%9C%ED%95%98%EB%8A%94-%EC%A0%95%EA%B7%9C%EC%8B%9D/

 

[regex] 대괄호 사이에서 텍스트를 추출하는 정규식 - 리뷰나라

간단한 정규식 질문. 다음 형식의 문자열이 있습니다. this is a [sample] string with [some] special words. [another one] 대괄호 안의 단어를 추출하는 정규식은 무엇입니까? sample some another one 참고 : 사용 사례

daplus.net

 

https://namu.wiki/w/%EC%82%BC%EC%84%B1%20%EA%B0%A4%EB%9F%AD%EC%8B%9C/%EC%A0%9C%ED%92%88%20%EB%AA%A9%EB%A1%9D#s-1.1

 

Posted by 이야봉
Env2022. 3. 9. 13:15
[Env] 무료 PostgreSQL 호스팅 서비스 - ElephantSQL

 

[서비스명]

ElephantSQL

https://www.elephantsql.com/

 

 

[사용방법]

1. 회원가입(Github 또는 Google 계정으로도 가능)

2. Team 생성

3. 약관동의

4. Instance 추가(생성)

 

5. 접속정보 확인

 

6. 접속정보 세팅

 

 

https://www.elephantsql.com/

 

ElephantSQL - PostgreSQL as a Service

Security is something we prioritize above anything else. A well built environment start with high coding standards that guard against attempted security breaches. Our system components undergo tests and source code reviews to assess the security before we

www.elephantsql.com

 

Posted by 이야봉
Java2021. 2. 16. 19:41

[예시]  주어진 3개의 매퍼 파일(XML)에서 테이블명과 뷰명을 추출하시오.
              - 테이블명  첫글자 : T_
              - 뷰명 첫글자 : V_

 

A.xml

SELECT *
FROM T_01

SELECT *
FROM T_02

SELECT *
FROM T_03

 

B.xml

SELECT *
FROM V_01

SELECT *
FROM V_02

SELECT *
FROM V_03

 

C.xml

INSERT INTO T_01
VALUES ('A');

SELECT *
FROM T_02

SELECT *
FROM V_02

 

 

추출과정

1. 위 3개의 파일형식을 XML(3개) => TXT(3개)로 변경

D:\FolderName>ren *.xml *.txt

 

2. TXT파일(3개) => TXT파일(1개)로 병합(ABC.txt)

  bluefive.pairsite.com/txtcollector.htm

 

 

3. ABC.txt(병합 완료)

d:\prac\xmlS\A.xml
************************************************************************
<!-- A.xml -->
SELECT *
FROM T_01

SELECT *
FROM T_02

SELECT *
FROM T_03


d:\prac\xmlS\B.xml
************************************************************************
<!-- B.xml -->

SELECT *
FROM V_01

SELECT *
FROM V_02

SELECT *
FROM V_03


d:\prac\xmlS\C.xml
************************************************************************
<!-- C.xml -->

INSERT INTO T_01
VALUES ('A');

SELECT *
FROM T_02

SELECT *
FROM V_02

 

Java로 추출작업 수행

4. txt파일을 FileReader로 한 Line씩 추출하여, List1에 담기

5. Line별로 시작단어가 T_ 이거나, V_인 경우를 List2에 담기

6. List2에서 중복제거

 

 

추출결과

T_01
T_02
T_03
V_01
V_02
V_03

 

 

 

 

참조링크

 

 

 bluefive.pairsite.com/txtcollector.htm

 

 

Text file merging tool - TXTcollector, freeware!

 

bluefive.pairsite.com

editorizer.tistory.com/240

 

파일 확장자 일괄 변경하는 방법을 알아보자

여기서 파일 확장자 일괄 변경이라는 것은 특정 파일을 변환하는 것이 아니라 파일 뒤에 붙는 확장자 이름을 바꾸는 것을 말합니다. 일반적으로 파일 확장자를 변경하려면 위 과정처럼 해당 파

editorizer.tistory.com

 

jeong-pro.tistory.com/69

 

자바 파일 입출력 (txt파일 한 문자씩, 한 줄씩, 한 번에 읽기)

자바 파일 입출력 원리 1. 파일 객체를 만든다. 2. 해당 파일을 열어 읽는다. (+읽은 내용 알아서 처리, 어디다 쓰든지 문자열을 바꾸던지 뭐 맘대로 하면 된다.) 3. 파일을 닫는다. 1) 한 문자씩 읽

jeong-pro.tistory.com

 

www.dante2k.com/444

 

String 데이터에서 split() 메소드 사용시 띄어쓰기 인식하기

 자바에서 문자열을 split하는 경우가 꽤 됩니다. 뭐.. 데이터 파싱을 위해서 자르기도 하고, 필요에 따라 사용하는데요..  이런 경우 "ABC DEF" 같이 중간에 띄어쓰기가 있는데 이를 인식하여 자르

www.dante2k.com

 

m.blog.naver.com/pgh7092/221130579329

 

JAVA 자바 리스트(List) 중복 제거하는 3가지 방법

자바에서 중복을 제거하는 방법은 여러 가지가 있다. 본 포스팅에서는 총 3가지 방법에 대해서 설명한다. 1...

blog.naver.com

 

Posted by 이야봉
DB2020. 11. 1. 15:06

[실습] (부서 & 직함별)로 자녀 수 합, 급여 합, 보너스 합 조회
          ※ 급여 합, 보너스 합은 행을 열로 변환(PIVOT)하여 표현

 

WITH EMPP AS 
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS, 3 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS, 4 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS, 3 AS CHLDN FROM DUAL
),
/* NON-PIVOT 결과 */
A AS (
   SELECT DEPTNO,
          JOB,
          SUM(CHLDN) AS CHLDN
   FROM EMPP
   GROUP BY DEPTNO, JOB
),
/* PIVOT 결과 */
B AS 
(
	SELECT *
	FROM 
	(
		SELECT 
		       DEPTNO, -- GROUP BY 
		       JOB,    -- GROUP BY (JOIN 불가)
		       JOB J,  -- JOIN용
		       SAL,    -- SUM(SAL)
		       BONUS   -- SUM(BONUS)
		   FROM EMPP
	)
	PIVOT
	(
		SUM(SAL) AS SAL, 
		SUM(BONUS) AS BNS
		FOR JOB IN ( 'CLERK' AS C, 
                      'ANALYST' AS A,
                       'PRESIDENT' AS P,
                       'SALESMAN' AS S,
                       'MANAGER' AS M
                       )
	) 
) /* End of B */
SELECT A.DEPTNO AS A_DEPTNO,
       A.JOB AS A_JOB,
       A.CHLDN,
       B.*
FROM A,  /* NON-PIVOT 결과 */ 
     B   /* PIVOT 결과 */
WHERE A.DEPTNO = B.DEPTNO
  AND A.JOB = B.J
ORDER BY A_DEPTNO, A_JOB;

WITH절의 EMPP 테이블 데이터

 

쿼리 결과

 

참조
http://www.yes24.com/Product/Goods/46218018
http://blog.naver.com/PostView.nhn?blogId=silentis&logNo=220943488844

 

 

Posted by 이야봉
DB2020. 10. 31. 23:06

[실습] 부서 & 직함별로 급여 합, 보너스 합, 인원 수 출력
              ※ 급여 합은 행을 열로 전환한 결과 포함

WITH EMP AS
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS FROM DUAL
)
SELECT DEPTNO, 
       JOB,
       SUM(SAL) AS SUM_SAL,              /* (부서 & 직함별) 급여 합 */
       SUM(CASE WHEN JOB = 'ANALYST'     /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
            THEN SAL
       END) AS SUM_SAL_ANALYST,
       SUM(CASE WHEN JOB = 'CLERK'        /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_CLERK,
       SUM(CASE WHEN JOB = 'MANAGER'      /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_MANAGER,
       SUM(CASE WHEN JOB = 'SALESMAN'     /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
       	        THEN SAL
       END) AS SUM_SAL_SALESMAN, 
       SUM(CASE WHEN JOB = 'PRESIDENT'    /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_PRESIDENT,
       SUM(BONUS) AS SUM_BONUS,          /* (부서 & 직함별) 보너스 합 */
       COUNT(*) AS CNT                   /* (부서 & 직함별) 인원 수 */
FROM EMP 
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

 

WITH절의 EMP 데이터

 

쿼리 결과

CASE 문을 DECODE로 변경 (CASE문 방식과 결과는 동일)

WITH EMP AS
(
	...
    ...
    ...
)
SELECT DEPTNO, 
       JOB,
       SUM(SAL) AS SUM_SAL,             						 /* (부서 & 직함별) 급여 합 - NON-PIVOT */       
       SUM(DECODE(JOB, 'ANALYST', SAL)) AS SUM_SAL_ANALYST,      /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'CLERK', SAL)) AS SUM_SAL_CLERK,          /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'MANAGER', SAL)) AS SUM_SAL_MANAGER,      /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'SALESMAN', SAL)) AS SUM_SAL_SALESMAN,    /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'PRESIDENT', SAL)) AS SUM_SAL_PRESIDENT,  /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(BONUS) AS SUM_BONUS,          						 /* (부서 & 직함별) 보너스 합 - NON-PIVOT */
       COUNT(*) AS CNT                  						 /* (부서 & 직함별) 인원 수  - NON-PIVOT */
FROM EMP 
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

 

 

GROUP BY 조건을 변경(JOB을 그룹핑 제외)하여 쿼리 수행

WITH EMP AS
(
   ...
   ...
   ...
)
SELECT DEPTNO, 
     --  JOB,   /* JOB을 그룹핑 제외 */
       SUM(SAL) AS SUM_SAL,              
       SUM(CASE WHEN JOB = 'ANALYST'    
            THEN SAL
       END) AS SUM_SAL_ANALYST,
          ...
          ...
          ...
       SUM(BONUS) AS SUM_BONUS,         
       COUNT(*) AS CNT                  
FROM EMP 
GROUP BY DEPTNO --,  JOB    /* JOB을 그룹핑 제외 */
ORDER BY DEPTNO --,  JOB    /* JOB을 그룹핑 제외 */

JOB을 그룹핑 제외 후 쿼리 수행 : 결과 ROW 수가 달라짐

 

 

 

참조
booksr.co.kr/www.yes24.com/Product/Goods/46218018

acthouse.blogspot.com/2016/02/how-to-create-scott-schema-and-default.html

 

 

 

Posted by 이야봉
DB2020. 10. 31. 13:53

GROUPING SETS()

그룹핑 조건이 여러 개일 경우 유용


[실습] STUDENT 테이블에서 학년 또는 학과별로 인원 수, 키의 합계, 몸무게의 합계를 동시에 출력 

 

STUDENT 테이블 데이터

쿼리

SELECT GRADE       AS "학년",
       DEPTNO1     AS "학과",
       COUNT(*)    AS /* (학년 or 학과별) */ "인원 수",
       SUM(HEIGHT) AS /* (학년 or 학과별) */ "키의 합계",
       SUM(WEIGHT) AS /* (학년 or 학과별) */ "몸무게의 합계"
FROM STUDENT 
GROUP BY GROUPING SETS(GRADE, DEPTNO1)
ORDER BY GRADE, DEPTNO1

 

쿼리 결과

 

 

책 링크
booksr.co.kr/www.yes24.com/Product/Goods/46218018
 

오라클 SQL과 PL/SQL

SQL과 PL/SQL에 대한 가장 쉽고 친절한 입문서SQL과 PL/SQL에 대한 내용은 방대하며 어려운 내용들이 많기 때문에 처음 공부하는 학습자는 스스로 공부하기가 쉽지 않다. 이 책은 방대한 SQL과 PL/SQL에

www.yes24.com

 

Posted by 이야봉
DB2020. 10. 27. 19:21

SQL 공부를 위해 구입한 책

「SQL의 컨셉 for ORACLE」의 실습용 모델 ERD

 

 

영문으로 테이블과 컬럼을 생성한 후,  책에 제시된 한글로 COMMENT를 작성.

 

 

책 링크
「SQL의 컨셉 for ORACLE」

 

비팬&비팬북스 | SQL의 컨셉 for ORACLE

책소개 이 책은 SQL 초중급자를 대상으로 한다. SQL의 기본 개념과 문법을 알고, 실제로 사용하고 있지만 조금 부족한 DB 관리자나 개발자를 위한 책이다. 이 책의 가장 큰 장점은 다양한 실무 경력

www.bpan.com

 

 

 

Posted by 이야봉