도도의 초록누리

* 참고로 스프링 프레임워크는 셋팅이 시작의 반이라고 보면 될 것 같다.

[작업 환경]

IDE: Eclipse 2020-06

Framework: Spring Framework 4.2.4 RELEASE

spring-jdbc

spring-core

spring-tx

mysql-connector-java (8.0.21)

oracle jdbc (WEB-INF/lib에 넣어줘야 함.)

OpenJDK 15 (프로젝트에 적용한 자바 버전은 1.8로 셋팅하였음.)

 <properties>
  <java-version>1.8</java-version>
  <org.springframework-version>4.2.4.RELEASE</org.springframework-version>
  <org.aspectj-version>1.6.10</org.aspectj-version>
  <org.slf4j-version>1.6.6</org.slf4j-version>
 </properties>

  (중략)

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

  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.21</version>
  </dependency>

-- Transaction 실습 DB (은행 - Account)
-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (FOODMENU_TBL)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE account_tbl
(
    idx NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    balance NUMBER,
    regidate TIMESTAMP
);

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

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

MYSQL_DB_URL=jdbc:mysql://localhost:3306/dbanme?useUnicode=true&characterEncoding=utf8
MYSQL_DB_USERNAME=
MYSQL_DB_PASSWORD=
ORACLE_DB_URL=jdbc:oracle:thin:@127.0.0.1:1521:xe
ORACLE_DB_USERNAME=HR
ORACLE_DB_PASSWORD=1234

package com.website.example.vo;

import java.sql.Timestamp;;

public class AccountVO {
 
      private int idx;
      private String name;
      private int balance;
      private Timestamp regidate;
 
      public int getIdx() {
          return idx;
      }
 
      public void setIdx(int idx) {
          this.idx = idx;
      }
 
      public String getName() {
          return name;
      }
 
      public void setName(String name) {
          this.name = name;
      }
 
      public int getBalance() {
          return balance;
     }

     public void setBalance(int balance) {
          this.balance = balance;
     }

     public Timestamp getRegidate() {
         return regidate;
     }
 
     public void setRegidate(Timestamp regidate) {
         this.regidate = regidate;
     }
 
}

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.service.AccountService;
import com.website.example.service.AccountServiceImpl;

@Configuration
@EnableTransactionManagement

public class DBConfig {

    @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 AccountService accountServiceImpl() {
     
     AccountService service = new AccountServiceImpl(dataSource());
     return service;
    }
   
}

package com.website.example.common;

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

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

}

package com.website.example.common;

import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import com.mysql.cj.jdbc.MysqlDataSource;

import oracle.jdbc.pool.OracleDataSource;

public class MyDataSourceFactory {
 
 private InputStream is = null;
    private Properties props = null;
 
 public MyDataSourceFactory()  {
  
        String resource = "db.properties";
        this.is = getClass().getClassLoader().getResourceAsStream(resource);
        this.props = new Properties();
       
 }
 
 public DataSource getMySQLDataSource() {
  
        MysqlDataSource mysqlDS = null;
       
        try {
         
            props.load(is);
            mysqlDS = new MysqlDataSource();
            mysqlDS.setURL(props.getProperty("MYSQL_DB_URL"));
            mysqlDS.setUser(props.getProperty("MYSQL_DB_USERNAME"));
            mysqlDS.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));
           
        } catch (IOException e) {
            e.printStackTrace();
        }
       
        return mysqlDS;
       
    }
    
    public DataSource getOracleDataSource(){
     
        OracleDataSource oracleDS = null;
       
        try {
         
            props.load(is);
            oracleDS = new OracleDataSource();
            oracleDS.setURL(props.getProperty("ORACLE_DB_URL"));
            oracleDS.setUser(props.getProperty("ORACLE_DB_USERNAME"));
            oracleDS.setPassword(props.getProperty("ORACLE_DB_PASSWORD"));
           
           
        } catch (IOException e) {
         
            e.printStackTrace();
           
        } catch (SQLException e) {
         
            e.printStackTrace();
           
        }
       
        return oracleDS;
       
    }

}

package com.website.example.dao;

import java.sql.SQLException;

import com.website.example.vo.AccountVO;

public interface AccountDAO {
 
       void createAccount(AccountVO vo) throws SQLException;
       int getBalance(String name);
       void minus(String name, int money) throws SQLException;
       void plus(String name, int money) throws SQLException;
 
}

package com.website.example.dao;

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

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.website.example.vo.AccountVO;

@Service
public class AccountRowMapper implements RowMapper<AccountVO>  {

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

             vo.setIdx(rs.getInt(1));
             vo.setName(rs.getString(2));
             vo.setBalance(rs.getInt(3));
             vo.setRegidate(rs.getTimestamp(4));
  
             return vo;
      }

}

package com.website.example.dao;

import java.sql.SQLException;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import com.website.example.vo.AccountVO;

public class AccountDAOImpl implements AccountDAO {

       // Spring Framework - JDBC
       private JdbcTemplate jdbcTemplate = null;
       private DataSource ds = null;
 
       private final String INSERT = "insert into account_tbl(name, balance, regidate) values(?, ?, ?)";
       private final String SELECT_BALANCE = "select * from account_tbl where name = ?";
       private final String UPDATE_MINUS = "update account_tbl set balance = (select balance from account_tbl where name = ?) - ? " +
          " where name = ?";
       private final String UPDATE_PLUS = "update account_tbl set balance = (select balance from account_tbl where name = ?) + ? " +
          " where name = ?";
 
       public AccountDAOImpl(DataSource ds) {
             this.jdbcTemplate = new JdbcTemplate(ds);
             this.ds = ds;
       }
 
       public void createAccount(AccountVO vo) throws SQLException {
   
             this.jdbcTemplate.update(INSERT, vo.getName(), vo.getBalance(), vo.getRegidate());  
       }
 
       public int getBalance(String name){
     
             Object[] args = {name};
     
             AccountVO vo = this.jdbcTemplate.queryForObject(SELECT_BALANCE, args, new AccountRowMapper());
       
             int result = vo.getBalance();
     
             return result;
       }

    
       public void minus(String name, int money) throws SQLException{
     
              this.jdbcTemplate.update(UPDATE_MINUS, name, money, name);
       
       }
   
       public void plus(String name, int money) throws SQLException{

            /*
            // 예외 발생시키기
            if(true){
                throw new RuntimeException("런타임 에러");
            }
             */
     
            this.jdbcTemplate.update(UPDATE_PLUS, name, money, name);
       }
 
}

package com.website.example.service;

import java.sql.SQLException;

import com.website.example.vo.AccountVO;

public interface AccountService {

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

package com.website.example.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 com.website.example.dao.AccountDAO;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.vo.AccountVO;

@Repository
@Transactional
public class AccountServiceImpl implements AccountService{

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

      @Override
       public void accountCreate(AccountVO vo) throws SQLException {
             this.accountDAO.createAccount(vo);  
       }
 
      @Override
      public void accountTransfer(String sender, String receiver, int money) throws SQLException {
  
            int balance = accountDAO.getBalance(sender); // 보내는 사람 잔액 체크
      
            if(balance >= money){ // 보내는 돈이 잔액보다 많으면
     
                 accountDAO.minus(sender, money);
                 accountDAO.plus(receiver, money);
              
        } else{

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

}

package com.website.example.unit;

import java.sql.SQLException;

import java.sql.Timestamp;

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

import com.website.example.common.RootConfig;
import com.website.example.service.AccountService;
import com.website.example.vo.AccountVO;

class MainTest {

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

}

[Spring-Framework] 37. Spring-JDBCTemplate - 트랜젝션 (어노테이션, Java - AOP)

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