Spring + JdbcTemplate + Oracle 사용 기초

사용 환경

• Windows 10
• Java 1.8
• Maven 3.8.2
• Spring Boot 2.5.4
• Oracle DB 19

Hello 프로젝트 따라하기

1. 프로젝트 생성

   브라우져에서 spring initializr를 방문하여 아래와 같이 입력하고 GENERATE 버튼을 클릭합니다.

   • Project: Maven Project
   • Language: Java
   • Spring Boot: 2.5.4
   • Project Meta:
     • Group: trvoid.jdbc
     • Artifact: basic-jdbc-example
     • Name: basic-jdbc-example
     • Package name: trvoid.jdbc
     • Packaging: Jar
     • Java: 8
   • Dependencies: JDBC API, Oracle Driver

   생성된 프로젝트 파일을 다운로드하여 압축을 풉니다. 프로젝트 폴더 구조는 아래와 같습니다.

   hello
     |-src
       |-main
         |-java
           |-trvoid.jdbc
             |-BasicJdbcExampleApplication.java
         |-resources
           |-application.properties
       |-test
         |-java
           |-trvoid.jdbc
             |-BasicJdcExampleApplicationTests.java
     |-pom.xml
   

   pom.xml 파일에서 의존성 항목과 빌드 플러그인을 확인할 수 있습니다.

   <dependencies>
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-jdbc</artifactId>
       </dependency>
   
       <dependency>
           <groupId>com.oracle.database.jdbc</groupId>
           <artifactId>ojdbc8</artifactId>
           <scope>runtime</scope>
       </dependency>
   </dependencies>
   
   <build>
       <plugins>
           <plugin>  
               <groupId>org.springframework.boot</groupId>  
               <artifactId>spring-boot-maven-plugin</artifactId>  
           </plugin>
       </plugins>
   </build>
   

   BasicJdbcExampleApplication.java 파일의 내용은 아래와 같습니다.

   package trvoid.jdbc;
   
   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   
   @SpringBootApplication
   public class BasicJdbcExampleApplication {
   
       public static void main(String[] args) {
           SpringApplication.run(BasicJdbcExampleApplication.class, args);
       }
   
   }
   

2. JdbcTemplate을 사용하는 @Repository 추가

   src/main/java/trvoid/jdbc/Car.java

   package trvoid.jdbc;
   
   public class Car {
       private int id;
       private String model;
       private String manufacturer;
   
       public Car(int id, String model, String manufacturer) {
           this.id = id;
           this.model = model;
           this.manufacturer = manufacturer;
       }
   
       // getters and setters
   
       @Override
       public String toString() {
           return String.format("id:%d, model:%s, manufacturer:%s", id, model, manufacturer);
       }
   }
   

   src/main/java/trvoid/jdbc/CarRepository.java

   package trvoid.jdbc;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.stereotype.Repository;
   
   import java.util.List;
   
   @Repository
   public class CarRepository {
       @Autowired
       private JdbcTemplate jdbcTemplate;
   
       public List<Car> findAll() {
           List<Car> result = jdbcTemplate.query(
                   "SELECT id, model, manufacturer FROM car",
                   (rs, rowNum) -> new Car(
                           rs.getInt("id"),
                           rs.getString("model"),
                           rs.getString("manufacturer")
                   )
           );
   
           return result;
       }
   }
   

3. 데이터베이스 질의 수행을 위해 CommandLineRunner 추가

   src/main/java/trvoid/jdbc/BasicJdbcExampleApplication.java

   package trvoid.jdbc;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.CommandLineRunner;
   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   
   import javax.sql.DataSource;
   import java.util.List;
   
   @SpringBootApplication
   public class BasicJdbcExampleApplication implements CommandLineRunner {
       @Autowired
       DataSource dataSource;
   
       @Autowired
       CarRepository carRepository;
       public static void main(String[] args) {
           SpringApplication.run(BasicJdbcExampleApplication.class, args);
       }
   
       @Override
       public void run(String... args) throws Exception {
           System.out.println("DataSource = " + dataSource);
   
           System.out.println("== CARS ==");
           List<Car> list = carRepository.findAll();
           list.forEach(x -> System.out.println(x));
           System.out.println("-- CARS --");
   
           System.exit(0);
       }
   }
   

4. 데이터 소스 설정

   src/main/resources/application.properties

   spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
   spring.datasource.username=your_db_username
   spring.datasource.password=your_db_password
   spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
   

5. 테이블 생성 및 데이터 입력 SQL 작성

   src/main/resources/schema.sql

   CREATE TABLE CAR (
       ID NUMBER(10) NOT NULL,
       MODEL VARCHAR2(100) NOT NULL,
       MANUFACTURER VARCHAR2(100) NOT NULL,
       CONSTRAINT CAR_PK PRIMARY KEY (ID)
   );
   

   src/main/resources/data.sql

   INSERT INTO "CAR" (ID, MODEL, MANUFACTURER) VALUES(1, 'J5','JELLY');  
   INSERT INTO "CAR" (ID, MODEL, MANUFACTURER) VALUES(2, 'W7','WISDOM');  
   INSERT INTO "CAR" (ID, MODEL, MANUFACTURER) VALUES(3, 'T9','TEAL');
   

6. Oracle 데이터베이스에서 테이블 생성 및 데이터 입력

   >sqlplus
   SQL> start src\main\resources\schema.sql
   SQL> start src\main\resources\data.sql
   SQL> commit;
   

7. 컴파일

   >mvn compile
   

8. 실행

   >mvn spring-boot:run
   

   출력 결과

   ...
   DataSource = HikariDataSource (HikariPool-1)
   == CARS ==
   id:1, model:J5, manufacturer:JELLY
   id:2, model:W7, manufacturer:WISDOM
   id:3, model:T9, manufacturer:TEAL
   -- CARS --
   ...
   

Written with StackEdit.