Database

데이터베이스 서버가 많을 경우 작업을 하다보면 내가 지금 어느 서버에서 작업하는지 잠시 잊어버리고 엉뚱한 곳에서 쿼리를 실행하는 경우가 있습니다.

이런 상황을 방지하기 위해 DBeaver를 사용하는 경우 Server 별로 색상을 설정해 두면 혼동을 좀 줄일 수 있습니다.

이번 글에서는 간단하게 개발, 스테이징, 운영 이렇게 3가지의 경우만 설명하지만 색상 설정을 마음대로 할 수 있으므로 DB가 아주 많은 경우 종류별로 동일 하거나 비슷한 색상을 사용하여 설정할 수도 있습니다.

 

우선 어디서 색상을 설정해야 하는지 알아봅니다. 

DBeaver를 켜고 좌측에 있는 DB 목록 중 색상을 변경하려는 DB를 선택하고 마우스 우측 버튼을 클릭합니다.

 

 

 

 

팝업 메뉴에서 [Edit Connection]을 선택하여 설정 창을 오픈합니다.

 

 

좌측에서 Connection settings의 [General] 메뉴를 클릭 하고 우측에서 [Edit connection types] 버튼을 클릭하여 [연결 유형] 창을 오픈 합니다.

 

 

 

 

현재는 아무런 배경색이 없는 [Development]가 선택되어 있습니다.

색상 변경을 하려면 중간에 있는 [Color]의 우측 박스를 클릭하여 색상표를 오픈 합니다.

 

 

 

테스트로 노랑색을 선택했습니다.

 

 

 

[적용(A)] 버튼을 클릭하면

 

 

 

팝업 뒤로 보이는 SQL 편집 창이 노란색으로 변경된 것을 확인할 수 있습니다.

 

동일한 방법으로 스테이징과 운영은 기존에 있는 녹색과 주황색으로 선택을 하고 적용을 해보았습니다.

 

 

 

좌측에 각 DB별로 서로 다른 색상으로 표시되고 쿼리 창과 결과 창도 색상으로 구분이 되어 실수할 확률은 줄어들것 같습니다.

 

 

이번에는 자기만의 새로운 색상을 만들어 보도록 하겠습니다.

다시 [Edit connection types] 버튼을 클릭하여 [연결 유형] 창을 오픈 합니다.

 

 

연결 유형에서 리스트 좌측 하단에 (+)가 있는 파란색 아이콘을 클릭합니다.

 

 

 

[Connection type parameters] 항목에 있는 Name, Description, Color를 원하는대로 수정을 하고 [적용] 버튼을 클릭합니다.

 

 

 

 

이제 개발DB를 새로 생성한 [새로운 DB2]로 색상을 변경해 봅니다.

 

 

 

너무 색상이 진하긴 하지만 DB 구분은 확실하게 되는 군요.

이런 식으로 자신만의 색상 구분을 사용하면 될것 같습니다.

 

 

- copy coding -

저작자표시 비영리 변경금지 (새창열림)

'Database' 카테고리의 다른 글

MariaDB MySQL 일요일 또는 월요일 기준 주별 월별 통계  (0) 2024.05.05
MariaDB event scheduler Batch 작업  (0) 2024.02.17
MySQL 8.0.35 버전 Windows 설치  (0) 2023.11.12
Oracle 조회 결과가 없는 경우에도 리턴 값 받기  (0) 2023.09.04
postgresql dump 말고 database copy clone duplicate  (0) 2022.05.31

 

JDBC Batch는 프로젝트와 별개로 작업하고 실행을 할 수 있어서 수정사항 발생시 바로바로 적용을 할 수 있다는 점이 운영하는 측에서는 편한 작업이 될 수 있습니다.  그러나 Batch job의 개수가 많아지거나 Table 사이즈가 크거나 작업의 우선 순위가 있어야 하는 경우 등의 상황에서는 다른 방법과 병행해서 사용하는게 개인적으로는 좋았던 것 같습니다.

 

 

 

오늘 기록해 놓으려는 소스는 SELECT 문을 사용해 key값의 데이터가 기존에 입력 되어있는지 확인하고 존재하면 UPDATE, 없으면 INSERT하는 단순 작업입니다.

 

1. DB 접속

먼저 DB 접속을 정의합니다.

 

Connection conn = null;
Class.forName("org.mariadb.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mariadb://127.0.0.1:3306", "db_id","db_passwd");
conn.setAutoCommit(false);

 

Driver는 사용하는 DB에 맞게 수정하면 됩니다.

 

2. Select 사용

 

기존에 입력된 데이터가 있는지 확인을 하기위해 함수를 하나 만들어서 결과를 리턴 받습니다.

int iCnt = getInsertCnt(conn, yesterday);

DB 접속 정보와 날짜를 파라미터로 데이터가 있는지 조회하는 쿼리입니다.

 

PreparedStatement pstmt = null;
int nCnt = 0;
ResultSet rs = null;
                    
try {
 
           StringBuilder sql = new StringBuilder();
           sql.append("SELECT count(*) AS CNT FROM test.statistics WHERE INSERT_DT = ?");
          
           pstmt = conn.prepareStatement(sql.toString());
           pstmt.setString(1, yesterDay);
                        
           rs = pstmt.executeQuery();
           while(rs.next()) {
               nCnt =  rs.getInt(1);
               System.out.println("=== CNT : " + rs + "/" + nCnt);
           }
           rs.close();
           pstmt.close();
}

 

PreparedStatement : 쿼리를 높은 효율성으로 반복적으로 사용하기위한 저장 객체.

ResultSet : SQL 쿼리를 실행한 결과를 저장하는 객체.

nCnt : SELECT 결과의 개수를 저장하려고 만든 변수입니다.

 

쿼리 생성시 사용하는 변수를 물음표(?)로 표시하고 값을 대입해서 쿼리를 완성하게 됩니다.

변수가 변경되면 쿼리는 수정하지 않고 변수 값만 수정해주면 반복해서 쿼리를 사용할 수 있게됩니다.

물음표에 해당하는 값을 대입하는데 사용되는 메소드는 아래처럼 정수, 실수, 문자등을 구분해서 대입하게 됩니다.

 

 

public void setInt(int index, int value)
public void setString(int index, String value)
public void setFloat(int index, float value)
public void setDouble(int index, double value)

 

index는 물음표의 순번으로 첫번째 물음표는 1이 됩니다.

 

쿼리를 실행하는 메소드는 여러 종류가 있는데 여기서는 SELECT 결과를 ResultSet에 담아야 하기때문에 리턴 값이 ResultSet인 메소드 executeQuery()를 선택해서 사용했습니다.

 

좀더 자세한 PreparedStatement 관련 메소드는 아래 링크를 참조하면 도움이 됩니다.

https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html

 

JDBC는 업무 로직이 복잡하면 어려워지는거고 실제 사용법은 간단합니다.  SELECT문 하나만으로 필요한 내용 설명은 거의 설명이 되는군요.

 

 

3. Insert / update

 

Insert문도 PreparedStatement 와 변수 대입을 위한 setString()을 사용합니다.

PreparedStatement pstmt = null;
                    
                     try {
                               
                    StringBuilder sql = new StringBuilder();
                    sql.append("INSERT INTO test.statistics (");
                    sql.append("  INSERT_DT");
                    sql.append(", VISIT_CNT");
                    sql.append(", PAGE_VIEW_CNT");
                    sql.append(", INST_DT");
                    sql.append(", INST_ID");
                    sql.append(") VALUES (");
                    sql.append(" ?");
                    sql.append(", (select 1 from dual)");
                    sql.append(", (select 2 from dual)");
                    sql.append(", date_format(now(), '%Y%m%d%H%i%s')");
                    sql.append(", 'tester'");
                    sql.append(")");
                   
                    pstmt = conn.prepareStatement(sql.toString());
                    pstmt.setString(1, yesterDay);
 

 

Select에서는 쿼리 실행을 위해 pstmt.executeQuery()를 사용하고 Insert와 Update에서는 addBatch()와 executeBatch()을 사용했습니다.

addBatch()는 쿼리를 바로 실행하지 않고 쿼리 구문을 메모리에 올려두었다가, 실행 명령(executeBatch)이 있으면 한번에 DB쪽으로 쿼리를 보내어 처리를 하게 됩니다.  즉, 한 번의 SQL 수행으로 대량의 로우를 동시에 insert / update / delete를 진행할 수 있어 대용량 처리를 하는데 적합한 방법입니다.

여기서는 달랑 하나 처리했는데 동일한 작업을 진행하려면 loop 문을 이용해서 작업을 하면 됩니다.

 

간단히 설명이 끝났습니다.

 

아래 소스를 보면 좀더 파악이 쉬우리라 생각하며 소스를 첨부합니다.

 

 

package com.example.demo.test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
 
public class Job {
 
           public static void main(String[] args) {
                     
                     Connection conn = null;
 
               try {
                Class.forName("org.mariadb.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mariadb://127.0.0.1:3306", "root","will99");
                conn.setAutoCommit(false);
               
                String yesterday = getYesterday();
               
               
                      int iCnt = getInsertCnt(conn, yesterday);
               
                      if(iCnt < 1) {
                                 System.out.println("======  insert ==================");
                       insertData(conn, yesterday);
                      } else {
                                 System.out.println("======  update ==================");
                         updateData(conn, yesterday);
                      }
                      
                      conn.close();
                 
               } catch (Exception e) {
               
                  e.printStackTrace();
               } finally {
                if(conn != null) {
                 try {
                      conn.close();
                 } catch (Exception e) {}
                }
               }
           }
          
           private static int getInsertCnt(Connection conn, String yesterDay) {
                     PreparedStatement pstmt = null;
                     int nCnt = 0;
                     ResultSet rs = null;
                    
                     try {
                               
                                StringBuilder sql = new StringBuilder();
                         sql.append("SELECT count(*) AS CNT FROM test.statistics WHERE INSERT_DT = ?");
          
                         pstmt = conn.prepareStatement(sql.toString());
                         pstmt.setString(1, yesterDay);
                        
                         rs = pstmt.executeQuery();
                         while(rs.next()) {
                              nCnt =  rs.getInt(1);
                                    System.out.println("=== CNT : " + rs + "/" + nCnt);
                         }
                         rs.close();
                         pstmt.close();
 
              
                     } catch (Exception e) {
                      
                    e.printStackTrace();
                 } finally {
                      if(pstmt != null) {
                                 try {
                                 pstmt.close();
                                 } catch (Exception e) {}
                       }
        }
 
                     return nCnt;
           }
          
           private static void insertData(Connection conn, String yesterDay) {
                     PreparedStatement pstmt = null;
                    
                     try {
                               
                    StringBuilder sql = new StringBuilder();
                    sql.append("INSERT INTO test.statistics (");
                    sql.append("  INSERT_DT");
                    sql.append(", VISIT_CNT");
                    sql.append(", PAGE_VIEW_CNT");
                    sql.append(", INST_DT");
                    sql.append(", INST_ID");
                    sql.append(") VALUES (");
                    sql.append(" ?");
                    sql.append(", (select 1 from dual)");
                    sql.append(", (select 2 from dual)");
                    sql.append(", date_format(now(), '%Y%m%d%H%i%s')");
                    sql.append(", 'tester'");
                    sql.append(")");
                   
                    pstmt = conn.prepareStatement(sql.toString());
                    pstmt.setString(1, yesterDay);
                   
                    pstmt.addBatch();
                   
                    pstmt.clearParameters();
 
                    pstmt.executeBatch();
                   
                    pstmt.clearBatch();
                   
                    conn.commit();
                   
                    pstmt.close();
                  
                 } catch (Exception e) {
                      
                    e.printStackTrace();
                 } finally {
                      if(pstmt != null) {
                                 try {
                                 pstmt.close();
                                 } catch (Exception e) {}
                      }
                 }
           }
          
           private static void updateData(Connection conn, String yesterDay) {
                     PreparedStatement pstmt = null;
                    
                     try {
                               
                    StringBuilder sql = new StringBuilder();
                    sql.append("UPDATE test.statistics SET ");
                    sql.append("VISIT_CNT = (select 1 from dual)");
                    sql.append(", PAGE_VIEW_CNT = (select 1 from dual)");
                    sql.append(", UPDT_DT = date_format(now(), '%Y%m%d%H%i%s')");
                    sql.append(", UPDT_ID = 'tester'");
                    sql.append("where INSERT_DT = ?");
                   
                    pstmt = conn.prepareStatement(sql.toString());
                    pstmt.setString(1, yesterDay);
 
                    pstmt.addBatch();
                   
                    pstmt.clearParameters();
 
                    pstmt.executeBatch();
                   
                    pstmt.clearBatch();
                   
                    conn.commit();
                   
                    pstmt.close();
                  
                 } catch (Exception e) {
                      
                    e.printStackTrace();
                 } finally {
                      if(pstmt != null) {
                                 try {
                                 pstmt.close();
                                 } catch (Exception e) {}
                      }
                 }
           }
          
           private static String getYesterday() {
                     Date today = new Date();
                     SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                     String fmDate = sdf.format(today);
                     System.out.println("=== today : " + fmDate);
                    
                     Calendar cal = Calendar.getInstance();
                     cal.setTime(today);
                    
                     cal.add(Calendar.DATE, -1);
                     String fmDate2 = sdf.format(cal.getTime());
                     System.out.println("=== yesterday : " + fmDate2);
                    
                     return fmDate2;
           }
 
}

 

 - copy coding -

저작자표시 비영리 변경금지 (새창열림)

'Java' 카테고리의 다른 글

java.util.zip.ZipEntry 이용하여 압축 파일 생성하기  (0) 2024.05.30
JQuery 이용 datepicker 사용방법 순차적 설명  (0) 2024.03.14
HTML form tag 값 설정하기 정리  (0) 2024.01.28
HTML form tag에서 value text 가져오기 정리  (0) 2024.01.17
[Java] stream과 filter 이용 List, Map 에서 필요 데이터 추출  (0) 2022.10.01

 

AWS의 RDS는 귀찮은 DB Server 구축을 몇분만에 만들어서 바로 사용 가능하도록 해주는데요 아래는 Amazon RDS 프리티어에 대한 소개 문구 입니다.

 

Amazon RDS용 AWS 프리 티어
·         MySQL, MariaDB, PostgreSQL, Oracle BYOL 또는 SQL Server(SQL Server Express Edition 실행)를 실행하는 Amazon RDS 단일 AZ db.t2.micro 인스턴스를 750시간 무료 사용 – 매월 지속적으로 DB 인스턴스를 실행하기에 충분한 시간
·         20GB의 범용(SSD) DB 스토리지
·         자동 데이터베이스 백업과 사용자 실행 DB 스냅샷을 위한 백업 스토리지 20GB
이러한 서비스 외에도 Amazon RDS에서 DB 인스턴스를 구축하고 관리할 수 있는 AWS Management Console이 무료로 제공됩니다.
 

 

20GB 이내의 크기라면 프로젝트에 따라 다르겠지만 초기 데이터가 없는 경우라면 개발단계에서는 충분한 크기인것 같습니다.

 

 

1. RDS Database 선택

 

RDS를 생성 하려면 먼저 aws에 로그인을 하고 검색을 하면 바로 찾을 수가 있습니다.

 

 

검색창에 rds를 입력하고 우측에 나오는 RDS를 클릭하여 생성 단계 페이지로 이동 합니다.

 

 

[데이터베이스 생성] 버튼을 클릭하면 데이터베이스 생성페이지로 이동하여 생성을 시작 합니다.

 

 

여기에서 사용하려는 데이터베이스와 버전을 선택합니다.

 

생성방식 : 표준생성

엔진 유형 : 자신이 원하는 Database를 선택해 줍니다.  저는 PostgreSQL를 선택했습니다.

버전 : 희망하는 DB 버전 선택

 

 

템프릿은 무료로 테스트만 하려는거라서 [프리 티어]를 선택 합니다. 프리티어를 선택하면 하단의 가용성 및 내구성은 비활성화 됩니다.

 

 

2. RDS Database 설정

 

 

DB 인스턴스 식별자는 고유한 인스턴스명을 만드는것으로 자신이 RDS를 어떤 목적으로 생성하는지 확인할 수 있도록 만들면 됩니다.  실제 사용할 DB Name은 나중에 입력하는 항목이 나오니 혼동하지 않으면 됩니다.  물론 DB명으로 똑같이 입력해도 되겠죠.

마스터 사용자 정보는 DB에 접속할 ID와 비밀번호 입니다. 이건 DB 접속에 사용되는 ID/PW 정보이니 잘 기입하고 기억해 둡니다.

 

 

DB Server의 사양을 선택하는 것으로 RDS 설명 페이지에서 무료 인스턴스는 db.t2.micro 로 되어 있는데 db.t3.micro가 선택되어 있습니다.

이전 세대 클래스 포함을 활성화해서 t2를 찾아도 안나오는걸 봐서 t3으로 변경되었나 봅니다.

 

 

할당된 스토리지가 기본값으로 200이 되어있는데 무료가 20GB이라고 했으니 낮추고 스토리지 자동조정 활성화도 체크 되어있는데 체크를 해제 해야 합니다.  사용중 데이터 용량이 모자라면 자동으로 확장하는건데 확장하는 만큼 과금 됩니다.

 

 

3. RDS Database 연결 및 보안 설정

 

 

연결 설정은 RDS를 신규로 작성해서 사용하거나 기존에 EC2를 생성했고 EC2에서 연결해서 사용하려는 것인지에 따라 선택을 달리 하면 됩니다.

위에 있는 이미지는 RDS를 신규로 생성해서 사용하는 설정 입니다.  외부에서 RDS에 접속도 가능하게 하려는 경우 위와같이 설정을 해줍니다.

 

만일 기존에 생성한 EC2가 있고 EC2와 연결해서 사용하도록 설정을 하려면 아래처럼 설정해 줍니다.

 

 

VPC 보안그룹을 기존에 생성한 EC2와 동일하게 사용해서 퍼블릭 엑세스 항목을 보면 아예 EC2에서만 접속이 되도록 고정이 되어있습니다. 

 

 

위에서 설정 항목 입력시 입력한 마스터 사용자이름과 마스터 암호를 사용해서 접속하기 위해 암호 인증 방법을 선택 합니다.

 

 

4. RDS Database DB name 설정

 

 

초기 데이터베이스 이름이 DB name으로 여기에 사용할 데이터베이스 이름을 입력하면 됩니다.

이제 맨 하단으로 이동을 합니다.

 

 

5. RDS Database 생성

 

 

중간에 나오는 값들은 대부분 추가요금을 내야하는 설정이므로 기본으로 두고 [데이터베이스 생성] 버튼을 클릭 합니다.

잠시 생성되는데 시간이 소요되니 다른일을 하다 옵니다. 

 

 

이제 RDS가 생성이 되었고 인스턴스가 사용가능하게 되었습니다.

EC2를 통한 접속이거나 외부 연결이 가능한 접속이거나 이제 DB에 Table을 만들어서 바로 사용하면 됩니다.

 

- copy coding -

저작자표시 비영리 변경금지 (새창열림)

'Cloud' 카테고리의 다른 글

[OCI] Oracle Cloud 평생 무료 서버 사용을 위한 회원 가입  (0) 2023.04.14
[AWS] Amazon Cloud EC2 CentOS Root Password 분실/재설정  (0) 2023.03.01
[AWS] EC2 putty, FileZilla 연결 (Amazon Cloud )  (0) 2022.12.25
[AWS] EC2 CentOS7 Instance 생성 (Amazon Cloud )  (0) 2022.12.24
[AWS] Amazon Cloud 요금 계산기 사용 방법  (0) 2022.12.17

 

PostgreSQL 데이터베이스를 dump 명령등을 사용해서 파일로 저장하면 이전에 insert 해놓은 자료들을 찾아보기가 어렵습니다. 이런 경우는 DB의 이름을 변경해서 그대로 복사해 놓으면 자료를 찾아보기도 쉽고 작업하기도 편리 합니다.  

postgresql의 database를 복사하기 위한 작업을 해보겠습니다.

 

먼저 알고 있는 명령어를 사용해서 작업을 시작해 봅니다.

 

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 TestDB    | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
 postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
 template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

 

리스트에 나오는 TestDB를 다른 이름으로 복사하는 명령어는 아래와 같습니다.

 

CREATE DATABASE [new database] WITH TEMPLATE [old database] OWNER [owner];

 

TestDB를 TestDB2022라는 DB로 복사하려면

 

CREATE DATABASE TestDB2022 WITH TEMPLATE TestDB OWNER postgres;

 

이렇게 명령어를 입력하면 되겠네요.

 

 

postgres=# CREATE DATABASE TestDB2022 WITH TEMPLATE TestDB OWNER postgres;
오류:  "TestDB" 템플릿 데이터베이스 없음
postgres=#

 

그렇지만 대부분 오류가 발생하게 됩니다.

이유는 오류 내용과 같이 템플릿 데이터베이스가 아니기 때문 입니다.

 

postgres=# select datname, datistemplate from pg_database;
  datname  | datistemplate
-----------+---------------
 postgres  | f
 TestDB    | f
 template1 | t
 template0 | t
(4 rows)

 

TestDB의 datistemplate 값이 f(false)입니다.

true로 변경을 합니다.

 

postgres=# ALTER DATABASE "TestDB" WITH IS_TEMPLATE = true;
ALTER DATABASE
postgres=#

 

이제 다시 TestDB를 복사해 봅니다.

 

postgres=# CREATE DATABASE "TestDB2022" WITH TEMPLATE "TestDB";
CREATE DATABASE
postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 TestDB     | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
 TestDB2022 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
 postgres   | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
 template0  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(5 rows)

 

 

복사가 잘 진행 되었습니다.

명령문에 CREATE DATABASE "TestDB2022" WITH TEMPLATE "TestDB";

쌍따옴표를 붙인 이유는 대소문자에 따라 Database 이름을 찾지 못하는 경우가 있습니다.

 

ALTER DATABASE "TestDB" WITH IS_TEMPLATE = false;

 

복사가 완료되면 기존 데이터베이스는 다시 template를 false로 변경해 놓으면 작업은 완료 됩니다.

 

- copy coding -

저작자표시 비영리 변경금지 (새창열림)

'Database' 카테고리의 다른 글

MySQL 8.0.35 버전 Windows 설치  (0) 2023.11.12
Oracle 조회 결과가 없는 경우에도 리턴 값 받기  (0) 2023.09.04
psql: could not connect to server /var/run/postgresql/.s.PGSQL.5432  (0) 2022.05.26
[MSSQL] SQL Server DB LINK 만들고 사용하기  (0) 2021.04.25
PostgreSQL 13.2에 PostGIS 3.1로 SHP 파일 import  (0) 2021.04.22

123

+ Recent posts

티스토리툴바