도도의 초록누리

This time, I will introduce how to use Oracle VirtualBox (GNU/GPL v2) in Linux.

How to install Oracle VirtualBox (GNU/GPL v2) on Linux.

The figure below introduces the method of OVA file (backup, restore) through Oracle VirtualBox.

It introduces the method of how to use Oracle VirtualBox on Linux and OVA backup and restore files.

[작성환경]

- MS 윈도우 10

- Oracle Databases 설치된 상태

[적용대상]

- Oracle Databases

예) C:\oraclexe\app\oracle\product\11.2.0\server\bin

오라클 설치한 경로를 찾아가보면 아래의 그림처럼 sqlplus의 위치를 찾을 수 있다.

[추가 방법]

-> 1. (컴퓨터 -> 속성 -> 환경변수 -> 시스템 변수 -> 새로만들기 

      "ORACLE_HOME" -> C:\oraclexe\app\oracle\product\11.2.0\server

   2. 시스템 변수 (Path 편집) -> %ORACLE_HOME%\bin

1. [JSP] 17. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (1), 2020-09-30
- https://yyman.tistory.com/1428

2. [JSP] 18. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (2), 2020-09-30
- https://yyman.tistory.com/1429

- 사용 프로그램: SQL Developer (Oracle)

- DB: Oracle Databases [11, 19g]

-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (BOARD)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE board
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    subject VARCHAR2(30),
    memo NCLOB,
    count NUMBER,
    regidate DATE
);

-- Sequence 정의
CREATE SEQUENCE board_sequence
START WITH 1
INCREMENT BY 1;

-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER board_trigger
BEFORE INSERT
    ON board
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT board_sequence.nextval INTO :NEW.ID FROM dual;
END;

/* 데이터 추가 */
INSERT INTO board (name, subject, memo, count, regidate) VALUES ('홍길동', '안녕하세요.', '메모메모', '0', '2020-09-29 11:11:00');

/* 데이터 등록 후 커밋할 것(대량 정보 처리 후) */
COMMIT;

-- 싱글 쿼리 (페이징)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1

-- 특정 싱글 쿼리 SQL
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board where subject like '%야해해%' order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1

CREATE TABLE foodmenu_tbl
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    price NUMBER,
    store_id NUMBER,
    cnt NUMBER,
    regidate DATE
);

-- Table 생성 (FOODSTORE_TBL)
CREATE TABLE foodstore_tbl
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    address VARCHAR2(30),
    regidate DATE
);

-- 2020-10-07 SQL Paging Upgrade

-- 조회 관련 태스트
select f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id
select count(*) from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id

-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (FOODMENU_TBL)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE foodmenu_tbl
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    price NUMBER,
    store_id NUMBER,
    cnt NUMBER,
    regidate DATE
);

-- Sequence 정의
CREATE SEQUENCE foodmenu_sequence
START WITH 1
INCREMENT BY 1;

-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER foodmenu_trigger
BEFORE INSERT
    ON foodmenu_tbl
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT foodmenu_sequence.nextval INTO :NEW.ID FROM dual;
END;



----------------------------------------------------------------------

[추가]
     <!-- MySQL - DataSource 셋팅 -->
    <bean id="dataSource2" class="com.mysql.cj.jdbc.MysqlDataSource">
        <property name="URL" value="${ORACLE_DB_URL}" />
        <property name="user" value="${ORACLE_DB_USERNAME}"/>
        <property name="password" value="${ORACLE_DB_PASSWORD}"/>
    </bean>

    <!-- <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> -->
 
     <!-- 2. Oracle - DataSource 셋팅 -->
    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
        <property name="URL" value="${ORACLE_DB_URL}" />
        <property name="user" value="${ORACLE_DB_USERNAME}"/>
        <property name="password" value="${ORACLE_DB_PASSWORD}"/>
        <property name="connectionCachingEnabled" value="true"/>
    </bean>

    <!-- 3. Apache DBCP -->

   <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
         <property name="driverClassName" value="${jdbc.driverClassName}" />
         <property name="url" value="${jdbc.url}" />
         <property name="username" value="${jdbc.username}" />
         <property name="password" value="${jdbc.password}" />
   </bean>


    <!-- 4. HikariCP -->
    <!--
    <bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
         <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
         <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:xe" />
         <property name="username" value="username" />
         <property name="password" value="password" />
    </bean>
 
    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
          <constructor-arg ref="hikariConfig"/>
    </bean>
     -->

    <!-- 5. MariaDB -->
    <!--
    <bean id="dataSource4" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="driverClass" value="org.mariadb.jdbc.Driver"/>
        <property name="url" value="jdbc:mariadb://localhost:3306/test"></property>
        <property name="username" value="root"/>
        <property name="password" value="test"/>
    </bean>
     -->
    
    <!-- 6. MyBatis // 트랜젝션 적용 버전 -->
    <!--
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource4"/>
        <property name="configLocation" value="classpath:mybatis/config.xml"/>
        <property name="mapperLocations" value="classpath:mybatis/sql/*.xml"></property>
    </bean>
   
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"/>
    </bean>
   
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource4"/>
    </bean>
   
    <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>
     -->

1. [Spring-Framework] 36. Spring-JDBCTemplate - 트랜젝션 (어노테이션, Java 설정), 2020-10-10

- https://yyman.tistory.com/1461

IDE: Eclipse 2020-06

- Spring Framework 4.2.4 Releases

- Spring-JDBC

- Spring-TX

- Spring-Core

- AspeetJ Weaver

- AspectJ

DB: Oracle Databases 11g (Express Edition)

((중략) - 추가된 사항)
    
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${org.springframework-version}</version>
  </dependency>
  
    
  <!-- AspectJ -->
  <dependency>
   <groupId>org.aspectj</groupId>
   <artifactId>aspectjrt</artifactId>
   <version>${org.aspectj-version}</version>
  </dependency> 
  

  <!-- AspectJWeaver 추가 -->
  <dependency>
   <groupId>org.aspectj</groupId>
   <artifactId>aspectjweaver</artifactId>
   <version>${org.aspectj-version}</version>
  </dependency>
  

package com.website.example.aop;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Service;

//관점, 서비스
@Aspect
@Service
public class LogAdvisor {

   // 2단계 - 전 단계 시야)
 
  @Before("execution(* com.website.example.aop.service.AccountServiceAOP.*(..))")
  // @Before("execution(public void sum())")
  // 반환값 없어도 무방
  
   public void logBefore() {
           System.out.println("전 단계");

   }

 
}

package com.website.example.aop.service;

import java.sql.SQLException;

import com.website.example.vo.AccountVO;

public interface AccountServiceAOP {

 void accountCreate(AccountVO vo) throws SQLException;
 void accountTransfer(String sender, String receiver, int money) throws SQLException;
 
}

package com.website.example.aop.service;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import com.website.example.dao.AccountDAO;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.vo.AccountVO;

@Repository
@Transactional
public class AccountServiceImplAOP implements AccountServiceAOP{

 private AccountDAO accountDAO;
 private DataSource ds = null;
 
 public AccountServiceImplAOP(DataSource ds) {
  this.accountDAO = new AccountDAOImpl(ds);
  this.ds = ds;
 }

 @Override
 @Transactional(propagation=Propagation.NEVER)
 public void accountCreate(AccountVO vo) throws SQLException {
  accountDAO.createAccount(vo); 
  System.out.println("create CurrentTransactionName: " + TransactionSynchronizationManager.getCurrentTransactionName());
 }
 
 @Override
 @Transactional
 public void accountTransfer(String sender, String receiver, int money) throws SQLException {
  
     int balance = accountDAO.getBalance(sender); // 보내는 사람 잔액 체크
     
        if(balance >= money){ // 보내는 돈이 잔액보다 많으면
     
         System.out.println("transfer CurrentTransactionName: " + TransactionSynchronizationManager.getCurrentTransactionName() );
         
   accountDAO.minus(sender, money);
   accountDAO.plus(receiver, money);
   
        } else{

         System.out.println("돈 없음");
         //throw new NoMoneyException();
        }
  
 }

}

package com.website.example.common;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.context.annotation.Import;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@Import({DBConfigAOP.class})
@ComponentScan(basePackages = {"com.website.example"})
@EnableAspectJAutoProxy
//@ComponentScan(basePackages = {"com.local.example.beans", "com.local.example.advisor"})
public class RootConfigAOP {

}

package com.website.example.common;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.website.example.dao.AccountDAO;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.aop.service.AccountServiceAOP;
import com.website.example.aop.service.AccountServiceImplAOP;

@Configuration
@EnableTransactionManagement
public class DBConfigAOP {

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
   
    @Bean
    public DataSource dataSource() {
     
     DataSource dataSource = new MyDataSourceFactory().getOracleDataSource();
     
     /* Apache DBCP
     
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/testDb?useUnicode=true&characterEncoding=utf8");
        dataSource.setUsername("test");
        dataSource.setPassword("test123!@#");
       
        */
        return dataSource;
    }
   
    @Bean
    public AccountDAO accountDAOImpl() {
     
     AccountDAO dao = new AccountDAOImpl(dataSource());
     return dao;
    }
   
    @Bean
    public AccountServiceAOP accountServiceImplAOP() {
     
     AccountServiceAOP service = new AccountServiceImplAOP(dataSource());
     return service;
    }
   
}

package com.website.example.unit;

import java.sql.SQLException;

import java.sql.Timestamp;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.website.example.common.MyDataSourceFactory;
import com.website.example.common.RootConfigAOP;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.aop.service.AccountServiceAOP;
import com.website.example.aop.service.AccountServiceImplAOP;
import com.website.example.vo.AccountVO;

class MainTestAOP {

 @Test
 void test() throws SQLException {
  
       @SuppressWarnings("resource")
        ApplicationContext applicationContext = new AnnotationConfigApplicationContext(RootConfigAOP.class);
  
        AccountServiceAOP service = (AccountServiceAOP) applicationContext.getBean("accountServiceImplAOP");
  
  AccountVO vo = new AccountVO();
  
  // 1. 계정 생성
  vo.setName("홍길동2");
  vo.setBalance(10000);
  vo.setRegidate(Timestamp.valueOf("2020-01-20 11:05:20"));
  service.accountCreate(vo);
  
  // 2. 계정 생성
  vo.setName("홍길자2");
  vo.setBalance(0);
  vo.setRegidate(Timestamp.valueOf("2020-01-20 23:05:20"));
  service.accountCreate(vo);
  
  // 3. 거래 처리
  service.accountTransfer("홍길동", "홍길자", 500);
  
  
 }

}

[작성 환경]

- IDE: Eclipse 2020-06
- Spring Framework 4.2.4 RELEASES

(중략)
  
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${org.springframework-version}</version>
  </dependency>

(중략)

package com.website.example.board;

import java.sql.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.website.example.common.MyDataSourceFactory;
import com.website.example.model.FoodMenuVO;
import com.website.example.model.FoodMenuViewVO;

public class BoardDAOSpring {
 
 // Spring Framework - JDBC
 private JdbcTemplate jdbcTemplate = null;
 
 private final String FOODMENU_INSERT = "insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values(?, ?, ?, ?, ?)";
 private final String BOARD_UPDATE = "update board set subject=?, memo=? where id=?";
 private final String FOODMENU_DELETE = "delete foodmenu_tbl where id=?";
 private final String BOARD_GET = "select * from board where id=?";
 
 private final String BOARD_LIST = "SELECT * FROM ( " +
                "SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM ( " +
                   "SELECT f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 " +
                "where f1.store_id = f2.id order by f1.id desc " +
                ") Z WHERE ROWNUM <= ? " +
                ") WHERE RNUM >= ?";
 
      private final String BOARD_FULL_COUNT = "select count(*) from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id";

      // 변형을 할 필요가 있음. (태스트용)

      public BoardDAOSpring() {

              MyDataSourceFactory sourceFactory = new MyDataSourceFactory();
              DataSource ds = sourceFactory.getOracleDataSource();
              this.jdbcTemplate = new JdbcTemplate(ds);
      }
 
      public List<FoodMenuViewVO> getList(){
 
          // 코드 간결하게 작성가능해짐.
          System.out.println("Spring JDBC - GetBoardList()");
         //return jdbcTemplate.query(BOARD_LIST, new BoardRowMapper());
  
          Object args[] = {10, 1};
  
          return jdbcTemplate.query(BOARD_LIST, args, new FoodMenuViewRowMapper());

       // return null;

     }
 
     public int getCount() {
  
          int result = 0;
          FoodMenuVO vo =  jdbcTemplate.queryForObject(BOARD_FULL_COUNT, new FoodMenuViewCntRowMapper());
          result = vo.getId();
  
          System.out.println("갯수:" + result);
  
         return result;
  
     }
 
      public void insertTest() {
  
          FoodMenuVO vo = new FoodMenuVO();

  
          // 약 10만 개
          // insert 후에 commit 할 것
          /*

          for(int j = 0; j < 2000 ; j++) {
                for ( int i = 0; i < 50; i++) {
   
                       vo.setName("하하하하1234" + i);
                       vo.setPrice(1000);
                       vo.setStore_id(1);
                       vo.setCnt(0);
                       vo.setRegidate(Date.valueOf("2020-01-03"));
                       vo.setStore_id(1);
    
                       jdbcTemplate.update(FOODMENU_INSERT, vo.getName(),
                              vo.getPrice(), vo.getStore_id(),
                              vo.getCnt(), vo.getRegidate());
    
                }
        }// end of if
  */
  
        vo.setName("야해해");
        vo.setPrice(1000);
        vo.setStore_id(1);
        vo.setCnt(0);
        vo.setRegidate(Date.valueOf("2020-01-05"));
        vo.setStore_id(1);
  
        Object[] args = {vo.getName(),
                 vo.getPrice(),
                 vo.getStore_id(),
                 vo.getCnt(),
                 vo.getRegidate()};
  
        jdbcTemplate.update(FOODMENU_INSERT, args);
  
 }
 

      // 글 삭제
      public void deleteFoodMenu(FoodMenuVO vo) {
  
             System.out.println("===> Spring JDBC로 deleteBoard() 기능 처리");
             jdbcTemplate.update( FOODMENU_DELETE, vo.getId() );
  
      }
 
 
}

package com.website.example.board;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.website.example.model.FoodMenuVO;
import com.website.example.model.FoodMenuViewVO;

public class FoodMenuRowMapper implements RowMapper<FoodMenuVO>  {

        @Override
        public FoodMenuVO mapRow(ResultSet rs, int rowNum) throws SQLException {
  
              FoodMenuVO vo = new FoodMenuVO();

              vo.setId(rs.getInt(1));
              vo.setName(rs.getString(2));
              vo.setPrice(rs.getInt(3));
              vo.setStore_id(rs.getInt(4));
              vo.setCnt(rs.getInt(5));
              vo.setRegidate(rs.getDate(6));
  
              return vo;
        }

}

package com.website.example.board;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.website.example.model.FoodMenuVO;

public class FoodMenuViewCntRowMapper implements RowMapper<FoodMenuVO>  {

        @Override
        public FoodMenuVO mapRow(ResultSet rs, int rowNum) throws SQLException {
  
               FoodMenuVO vo = new FoodMenuVO();
               vo.setId(rs.getInt(1));
               // System.out.println(rs.getInt(1));
  
               return vo;
       }

}

1. [Spring-Framework] 35. Spring-JDBCTemplate - 트랜젝션 (어노테이션 X), 2020-10-09

- https://yyman.tistory.com/1460

[PseudoCode]

public void insertMember(){

    (연결자)
    Connection conn = .....;
    
    try{
        conn.open(); // (연결)

        for ( 1 to 10000 ){

           String sql = "insert into ........";

           conn.executeQuery(sql);

        }

     }catch(Exception e){

     }finally{
           conn.close(); // (종료)
     }

}

[태스트 환경]

1. MS Windows 10

2. Oracle 11g (Express Edition, XE)

ora-12519 TNS:no appropriate handler found

1. 명령 프롬프트 실행하기

   cmd

2. 관리자 계정 접속 명령어

   sqlplus "/as sysdba"

3. 프로세스 조회하기
   SQL> SELECT * FROM v$resource_limit where resource_name='processes';

4. 프로세스 조정하기 크기
   SQL> ALTER SYSTEM SET PROCESSES=500 SCOPE=spfile; -- 200으로 늘려줌

5. 서버 재시작
   SQL> shutdown immediate; --셧다운
   SQL> startup; --재시작

sqlplus "/as sysdba"

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

SQL> ALTER SYSTEM SET PROCESSES=500 SCOPE=spfile;

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

SQL> commit;

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

SQL> shutdown immediate;

SQL> startup;

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

[작성 환경]

1. MS윈도우 10

2. 포멧 후 깨끗한 상태

경로: C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\
찾아야 하는 파일명: scott.sql

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql
SQL> alter user scott identified by tiger;

SQL> commit;

SQL> conn scott/tiger
SQL> show user;

SQL> select * from tab;

SQL> exit

c:\Users\{사용자계정명}>sqlplus
Enter user-name: system
Enter password: (비밀번호 입력하기)

SQL> alter user hr account unlock;