코딩하는 야매 기타맨

ROWNUM을 이용한 데이터 기본동작

아래문장은 정상적으로 작동한다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT *
FROM (
    SELECT *
    FROM ROWNUM_TEST
    ORDER BY C1
)
WHERE ROWNUM >=1
AND ROWNUM <=5;

C1   C2   C3   
---- ---- ---- 
   1 C     101
   2 D     102
   3 E     103
   4 F     104
   5 G     105

5 rows selected.

아래문장은 정상적으로 작동하지 않는다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT *
FROM (
    SELECT *
    FROM ROWNUM_TEST
    ORDER BY C1
)
WHERE ROWNUM >= 6
AND ROWNUM <= 10;

C1   C2   C3   
---- ---- ---- 

0 rows selected.

ROWNUM       C1   C2   C3   
------------ ---- ---- ---- 
           1    1 C     101     ==> ROWNUM >= 6 AND ROWNUM <= 10 조건에서 조회되지 않고 버려짐.
           2    2 D     102     ==> 위의  행이 버려졌으므로 다시 ROWNUM은 1이 되고 조건에 의해 버려짐
           3    3 E     103     ==> 이하 동일
           4    4 F     104
           5    5 G     105
           6    6 H     106
           7    7 I     107
           8    8 J     108
           9    9 K     109
          10   10 L     110

위의 조회가 정상적으로 되려면 아래와 같이 조회한다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT C1, C2, C3 
FROM (
    SELECT ROWNUM RN
        , T1.*
    FROM (
        SELECT * FROM ROWNUM_TEST ORDER BY C1
    ) T1
)
WHERE RN >= 6 AND RN <= 10;

C1   C2   C3   
---- ---- ---- 
   6 H     106
   7 I     107
   8 J     108
   9 K     109
  10 L     110

5 rows selected.

NULL 조회시 성능개선

1. INDEX가 존재하는 컬럼을 WHERE 조건에 IS NULL로 조회하는 방법.

1.1 해당 컬럼에 Default Value를 지정해서 column_name IS NULL 조회 보다 column_name = default value 형태로 조회한다.

1.2 FBI(Function Based Index) 생성으로 조회시 Index를 사용할 수 있도록 변경한다.

2. INDEX가 존재하는 컬럼을 WHERE 조건에 IS NOT NULL로 조회하는 방법.

2.1 CHAR/VARCHAR2 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name > CHR(0) 형태로 조회한다.

2.2 DATE 타입의 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name > TO_DATE('19000101','YYYYMMDD') 형태로 조회한다.

(19000101은 해당 컬럼에 존재할 수 없는 이전 값)

2.3 NUMBER 타입의 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name >= 0 OR column_name < 0 형태로 조회한다.

WHERE절에 number, varchar2 형변환 우선순위

Rest API Service를 이용한 Angular4 Application Step2.

1. Angular Project를 생성한다.

- ng new user-app --routing 명령어로 생성(routing 모듈을 기본적으로 생성하는 옵션)

D:\itdev4u\angular>ng new user-app --routing
  create user-app/e2e/app.e2e-spec.ts (290 bytes)
  create user-app/e2e/app.po.ts (208 bytes)
  create user-app/e2e/tsconfig.e2e.json (235 bytes)
  create user-app/karma.conf.js (923 bytes)
  create user-app/package.json (1293 bytes)
  create user-app/protractor.conf.js (722 bytes)
  create user-app/README.md (1023 bytes)
  create user-app/tsconfig.json (363 bytes)
  create user-app/tslint.json (3012 bytes)
  create user-app/.angular-cli.json (1243 bytes)
  create user-app/.editorconfig (245 bytes)
  create user-app/.gitignore (544 bytes)
  create user-app/src/assets/.gitkeep (0 bytes)
  create user-app/src/environments/environment.prod.ts (51 bytes)
  create user-app/src/environments/environment.ts (387 bytes)
  create user-app/src/favicon.ico (5430 bytes)
  create user-app/src/index.html (294 bytes)
  create user-app/src/main.ts (370 bytes)
  create user-app/src/polyfills.ts (3114 bytes)
  create user-app/src/styles.css (80 bytes)
  create user-app/src/test.ts (642 bytes)
  create user-app/src/tsconfig.app.json (211 bytes)
  create user-app/src/tsconfig.spec.json (283 bytes)
  create user-app/src/typings.d.ts (104 bytes)
  create user-app/src/app/app-routing.module.ts (245 bytes)
  create user-app/src/app/app.module.ts (395 bytes)
  create user-app/src/app/app.component.html (1173 bytes)
  create user-app/src/app/app.component.spec.ts (1103 bytes)
  create user-app/src/app/app.component.ts (207 bytes)
  create user-app/src/app/app.component.css (0 bytes)
npm WARN deprecated nodemailer@2.7.2: All versions below 4.0.1 of Nodemailer are deprecated. See https://nodemailer.com/status/
npm WARN deprecated mailcomposer@4.0.1: This project is unmaintained
npm WARN deprecated socks@1.1.9: If using 2.x branch, please upgrade to at least 2.1.6 to avoid a serious bug with socket data flow
and an import issue introduced in 2.1.0
npm WARN deprecated node-uuid@1.4.8: Use uuid module instead
npm WARN deprecated buildmail@4.0.1: This project is unmaintained
npm WARN deprecated socks@1.1.10: If using 2.x branch, please upgrade to at least 2.1.6 to avoid a serious bug with socket data flow and an import issue introduced in 2.1.0

> uws@9.14.0 install D:\itdev4u\angular\user-app\node_modules\uws
> node-gyp rebuild > build_log.txt 2>&1 || exit 0


> node-sass@4.8.3 install D:\itdev4u\angular\user-app\node_modules\node-sass
> node scripts/install.js

Cached binary found at C:\Users\lunajin\AppData\Roaming\npm-cache\node-sass\4.8.3\win32-x64-59_binding.node

> uglifyjs-webpack-plugin@0.4.6 postinstall D:\itdev4u\angular\user-app\node_modules\webpack\node_modules\uglifyjs-webpack-plugin
> node lib/post_install.js


> node-sass@4.8.3 postinstall D:\itdev4u\angular\user-app\node_modules\node-sass
> node scripts/build.js

Binary found at D:\itdev4u\angular\user-app\node_modules\node-sass\vendor\win32-x64-59\binding.node
Testing binary
Binary is fine
npm WARN rollback Rolling back ajv@4.11.8 failed (this is probably harmless): EPERM: operation not permitted, lstat 'D:\itdev4u\angular\user-app\node_modules\fsevents\node_modules'
npm WARN optional SKIPPING OPTIONAL DEPENDENCY: fsevents@1.1.3 (node_modules\fsevents):
npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for fsevents@1.1.3: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"})

added 1259 packages in 150.629s
'git'은(는) 내부 또는 외부 명령, 실행할 수 있는 프로그램, 또는
배치 파일이 아닙니다.
Project 'user-app' successfully created.

2. user module을 생성한다.

- ng generate module user --routing 명령어로 생성.

D:\itdev4u\angular\user-app>ng generate module user --routing
  create src/app/user/user-routing.module.ts (247 bytes)
  create src/app/user/user.module.ts (271 bytes)

3. Rest API Service를 호출할때 필요한 user service를 생성한다.

- ng generate service user/user 명령어로 user module내에 생성.

D:\itdev4u\angular\user-app>ng generate service user/user
  create src/app/user/user.service.spec.ts (362 bytes)
  create src/app/user/user.service.ts (110 bytes)

4. user모듈내에 sub component를 생성한다.

- user-list component : 사용자 목록 관련 component

- user-create component : 사용자 생성/수정 관련 component

- ng generate component user/user-list 명령어로 생성

- ng generate component user/user-create 명령어로 생성

D:\itdev4u\angular\user-app>ng generate component user/user-list
  create src/app/user/user-list/user-list.component.html (28 bytes)
  create src/app/user/user-list/user-list.component.spec.ts (643 bytes)
  create src/app/user/user-list/user-list.component.ts (280 bytes)
  create src/app/user/user-list/user-list.component.css (0 bytes)
  update src/app/user/user.module.ts (357 bytes)

D:\itdev4u\angular\user-app>ng generate component user/user-create
  create src/app/user/user-create/user-create.component.html (30 bytes)
  create src/app/user/user-create/user-create.component.spec.ts (657 bytes)
  create src/app/user/user-create/user-create.component.ts (288 bytes)
  create src/app/user/user-create/user-create.component.css (0 bytes)
  update src/app/user/user.module.ts (453 bytes)

5. Url 기반으로 관련페이지 리다이렉션등의 액션을 위해 user-routing.module.ts 파일을 수정한다.

- src/app/user/user-routing.module.ts 파일 수정.

import { NgModule } from '@angular/core';
import { Routes, RouterModule } from '@angular/router';
import { UserListComponent } from './user-list/user-list.component';
import { UserCreateComponent } from './user-create/user-create.component';

const routes: Routes = [
  {path: 'user', component: UserListComponent},
  {path: 'user/create', component: UserCreateComponent},
  {path: 'user/edit/:id', component: UserCreateComponent}
];

@NgModule({
  imports: [RouterModule.forChild(routes)],
  exports: [RouterModule]
})
export class UserRoutingModule { }

6. app.module.ts 파일에 UserModule을 import한다.

import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';

import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { UserModule } from './user/user.module';


@NgModule({
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    UserModule
  ],
  providers: [],
  bootstrap: [AppComponent]
})
export class AppModule { }

7. bootstrap4 project를 추가한다.

- npm install --save bootstrap font-awesome 명령어로 추가.

D:\itdev4u\angular\user-app>npm install --save bootstrap font-awesome
npm WARN bootstrap@4.1.0 requires a peer of jquery@1.9.1 - 3 but none is installed. You must install peer dependencies yourself.
npm WARN bootstrap@4.1.0 requires a peer of popper.js@^1.14.0 but none is installed. You must install peer dependencies yourself.
npm WARN optional SKIPPING OPTIONAL DEPENDENCY: fsevents@1.1.3 (node_modules\fsevents):
npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for fsevents@1.1.3: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"})

+ bootstrap@4.1.0
+ font-awesome@4.7.0
added 2 packages in 32.983s

8. bootstrap style을 적용한다.

- src/style.css 파일을 수정.

/* You can add global styles to this file, and also import other style files */
@import "~bootstrap/dist/css/bootstrap.min.css";
@import "~font-awesome/css/font-awesome.css";

9. 사용자정보를 표시할 User class를 생성한다.

- src/app/user/user.ts 파일을 생성.

export class User {
    id: number;
    firstname: string;
    lastname: string;
    email: string;

    constructor(id: number, firstname: string, lastname: string, email: string) {
        this.id = id;
        this.firstname = firstname;
        this.lastname = lastname;
        this.email = email;
    }
}

10.  Rest API와 연동하는 서비스를 수정한다.

- src/app/user/user.service.ts 파일 수정.

import { Injectable } from '@angular/core';
import { User } from './user';
import { Http, Response } from '@angular/http';
import 'rxjs/add/operator/map';
import 'rxjs/add/operator/catch';
import { Observable } from 'rxjs/Observable';

@Injectable()
export class UserService {
  private apiUrl = 'http://localhost:8080/api';
  constructor(private http: Http) { }

  findAll(): Observable {
    console.log('Start findAll');
    return this.http.get(this.apiUrl + '/users')
      .map((res: Response) => res.json())
      .catch((error: any) => Observable.throw(error.json().error || 'Server error'));
  }

  findById(id: number): Observable {
    return null;
  }

  saveUser(user: User): Observable {
    return null;
  }

  deleteUserById(id: number): Observable {
    return null;
  }

  updateUser(user: User): Observable {
    return null;
  }
}

11. 전체사용자를 조회하는 서비스를 호출할 수 있게 user-list.component.ts 파일을 수정한다.

- src/app/user/user-list/user-list.component.ts 파일 수정.

import { Component, OnInit } from '@angular/core';
import { User } from '../user';
import { UserService } from '../user.service';

@Component({
  selector: 'app-user-list',
  templateUrl: './user-list.component.html',
  styleUrls: ['./user-list.component.css'],
  providers: [UserService]
})
export class UserListComponent implements OnInit {
  private users: User[];

  constructor(private userService: UserService) { }

  ngOnInit() {
    this.getAllUsers();
  }

  getAllUsers() {
    this.userService.findAll().subscribe(
      users => {
        this.users = users;
      },
      err => {
        console.log(err);
      }
    );
  }
}

12. 전체사용자 목록을 보여주는 template 파일을 수정한다.

- src/app/user/user-list/user-list.component.html 파일을 수정.

<div class="container">
  <div class="row">
    <div class="col">
      <section>
        <header class="header">
          <div class="row">
            <div class="col-md-4">
              <h1>Users</h1>
            </div>
            <div class="col-md-6">
 
            </div>
            <div class="col-md-2">
              <button type="button" class="btn btn-primary" (click)="redirectNewUserPage()">New User</button>
            </div>
          </div>
 
        </header>
      </section>
 
      <section class="main">
 
        <table class="table">
          <thead>
          <tr>
            <th>#</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>email</th>
            <th></th>
          </tr>
          </thead>
          <tbody>
          <tr *ngFor="let user of users">
            <th scope="row">{{user.id}}</th>
            <td>{{user.firstname}}</td>
            <td>{{user.lastname}}</td>
            <td>{{user.email}}</td>
            <td>
              <button type="button" class="btn btn-success" (click)="editUserPage(user)">Edit</button>
              <button type="button" class="btn btn-danger" (click)="deleteUser(user)">Delete</button>
            </td>
          </tr>
 
          </tbody>
        </table>
      </section>
    </div>
  </div>
</div>

13. app.module.ts 파일에 HttpModule을 import한다.

- src/app/app.module.ts 파일 수정.

import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';

import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { UserModule } from './user/user.module';
import { HttpModule } from '@angular/http';


@NgModule({
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    UserModule,
    HttpModule
  ],
  providers: [],
  bootstrap: [AppComponent]
})
export class AppModule { }

14. Spring에서 CROS를 활성화하기 위해 Rest API Service의 Controller파일에 설정을 추가한다.

- com.itdev4u.controller.UserController 파일에 @CrossOrigin(origins = http://localhost:4200) 추가.

package com.itdev4u.controller;

import java.util.List;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.itdev4u.exception.ResourceNotFoundException;
import com.itdev4u.model.User;
import com.itdev4u.repository.UserRepository;

@CrossOrigin(origins = "http://localhost:4200")
@RestController
@RequestMapping("/api")
public class UserController {
	@Autowired UserRepository userRepository;
	
	//Get All User
	@GetMapping("/users")
	public List getAllUsers() {
		return userRepository.findAll();
	}
	
	//Create a new User
	@PostMapping("/user")
	public User createUser(@Valid @RequestBody User user) {
		return userRepository.save(user);
	}
	
	//Get a Single User
	@GetMapping("/user/{id}")
	public User getUserById(@PathVariable(value = "id") Long userId) {
		return userRepository.findById(userId)
				.orElseThrow(()->new ResourceNotFoundException("User", "id", userId));
	}
	
	//Update User
	@PutMapping("/user/{id}")
	public User updateUser(@PathVariable(value = "id") Long userId
						, @Valid @RequestBody User userDetail) {
		User user = userRepository.findById(userId)
				.orElseThrow(()->new ResourceNotFoundException("User", "id", userId));
		
		user.setFirstname(userDetail.getFirstname());
		user.setLastname(userDetail.getLastname());
		user.setEmail(userDetail.getEmail());
		
		User updateUser = userRepository.save(user);
		
		return updateUser;
	}
	
	//Delete a User
	@DeleteMapping("/user/{id}")
	public ResponseEntity deleteUser(@PathVariable(value = "id") Long userId) {
		User user = userRepository.findById(userId)
				.orElseThrow(()->new ResourceNotFoundException("User", "id", userId));
		
		userRepository.delete(user);
		
		return ResponseEntity.ok().build();
	}
}

Rest API Service를 이용한 Angular4 Application Step1.

1. 사용자 정보를 저장할 Table / Sequence를 생성한다.

/* TABLE 생성 */
CREATE TABLE TB_USER 
(
  ID NUMBER NOT NULL 
, FIRSTNAME VARCHAR2(20) NOT NULL 
, LASTNAME VARCHAR2(20) NOT NULL 
, EMAIL VARCHAR2(50) 
, CONSTRAINT TB_USER_PK PRIMARY KEY (ID)
  ENABLE 
);

COMMENT ON COLUMN TB_USER IS '사용자관리 테이블';
COMMENT ON COLUMN TB_USER.ID IS 'Primary Key : SEQUENCE_USER 값을 사용';
COMMENT ON COLUMN TB_USER.FIRSTNAME IS 'first name';
COMMENT ON COLUMN TB_USER.LASTNAME IS 'last name';
COMMENT ON COLUMN TB_USER.EMAIL IS 'email';

/* SEQUENCE 생성 */
CREATE SEQUENCE SEQUENCE_USER INCREMENT BY 1 START WITH 1 MAXVALUE 999999 MINVALUE 1;

2. Eclipse에서 Spring Start Project를 선택해서 신규 Project를 생성한다. Project의 설정값은 아래와 같이 설정한다.

- Name : UserMgmtService

- Type : Maven

- Packaging : jar

- Group : com.itdev4u

- Artifact : UserMgmtService

- Package : com.itdev4u

- Dependencies : Web, JPA, DevTools

3. DataBase와 Mapping할 Entity class 생성한다.

package com.itdev4u.model;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.validation.constraints.NotBlank;

@Entity
@Table(name="TB_USER")
public class User implements Serializable {
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="user_seq")
	@SequenceGenerator(sequenceName="sequence_user", allocationSize=1, name="user_seq")
	private Long id;
	
	@NotBlank
	private String firstname;
	
	@NotBlank
	private String lastname;
	
	@NotBlank
	private String email;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getFirstname() {
		return firstname;
	}

	public void setFirstname(String firstname) {
		this.firstname = firstname;
	}

	public String getLastname() {
		return lastname;
	}

	public void setLastname(String lastname) {
		this.lastname = lastname;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}	
}

4. User 데이터에 Access하는 Repository Interface를 작성한다.

package com.itdev4u.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import com.itdev4u.model.User;

public interface UserRepository extends JpaRepository<User, Long> {

}

5. 사용자정의 Exception class를 작성한다.

package com.itdev4u.exception;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;

@ResponseStatus(value = HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends RuntimeException {
	private String resourceName;
	private String fieldName;
	private Object fieldValue;
	
	public ResourceNotFoundException(String resourceName, String fieldName, Object fieldValue) {
		super(String.format("%s not foumdwith $s : '%s'", resourceName, fieldName, fieldValue));
		this.resourceName = resourceName;
		this.fieldName = fieldName;
		this.fieldValue = fieldValue;
	}

	public void setResourceName(String resourceName) {
		this.resourceName = resourceName;
	}

	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}

	public void setFieldValue(Object fieldValue) {
		this.fieldValue = fieldValue;
	}
}

6. API Service 호출시 해당 작업을 담당할 controller class를 작성한다.

package com.itdev4u.controller;

import java.util.List;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.itdev4u.exception.ResourceNotFoundException;
import com.itdev4u.model.User;
import com.itdev4u.repository.UserRepository;

@RestController
@RequestMapping("/api")
public class UserController {
	@Autowired UserRepository userRepository;
	
	//Get All User
	@GetMapping("/users")
	public List getAllUsers() {
		return userRepository.findAll();
	}
	
	//Create a new User
	@PostMapping("/user/")
	public User createUser(@Valid @RequestBody User user) {
		return userRepository.save(user);
	}
	
	//Get a Single User
	@GetMapping("/user/{id}")
	public User getUserById(@PathVariable(value = "id") Long userId) {
		return userRepository.findById(userId)
			.orElseThrow(()->new ResourceNotFoundException("User", "id", userId));
	}
	
	//Update User
	@PutMapping("/user/{id}")
	public User updateUser(@PathVariable(value = "id") Long userId
			, @Valid @RequestBody User userDetail) {
		User user = userRepository.findById(userId)
			.orElseThrow(()->new ResourceNotFoundException("User", "id", userId));
		
		user.setFirstname(userDetail.getFirstname());
		user.setLastname(userDetail.getLastname());
		user.setEmail(userDetail.getEmail());
		
		User updateUser = userRepository.save(user);
		
		return updateUser;
	}
	
	//Delete a User
	@DeleteMapping("/user/{id}")
	public ResponseEntity<?> deleteUser(@PathVariable(value = "id") Long userId) {
		User user = userRepository.findById(userId)
			.orElseThrow(()->new ResourceNotFoundException("User", "id", userId));
		userRepository.delete(user);
		return ResponseEntity.ok().build();
	}
}

7. Client Tool을 이용해서 API Service 가 정상으로 작동하는지 Test한다.

- Test 결과 생략

Spring Boot, JPA, Oracle, Restful CRUD API 서비스 구축2

1. Note 데이터에 Access하는 Repository 생성한다.

package com.itdev4u.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import com.itdev4u.model.Note;

public interface NoteRepository extends JpaRepository<Note, Long> {

}

2. 사용자 정의 Exception class를 작성한다.

package com.itdev4u.exception;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ResponseStatus;
import lombok.Getter;

@ResponseStatus(value = HttpStatus.NOT_FOUND)
public class ResourceNotFoundException extends RuntimeException {
	private String resourceName;
	private String fieldName;
	private Object fieldValue;
	
	public ResourceNotFoundException(String resourceName, String fieldName, Object fieldValue) {
		super(String.format("%s not foumdwith $s : '%s'", resourceName, fieldName, fieldValue));
		this.resourceName = resourceName;
		this.fieldName = fieldName;
		this.fieldValue = fieldValue;
	}
	
	public String getResourceName() {
		return resourceName;
	}

	public void setResourceName(String resourceName) {
		this.resourceName = resourceName;
	}

	public String getFieldName() {
		return fieldName;
	}

	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}

	public Object getFieldValue() {
		return fieldValue;
	}

	public void setFieldValue(Object fieldValue) {
		this.fieldValue = fieldValue;
	}
}

3. controller 생성한다.

- @GetMapping("/notes") 어노테이션은 @RequestMapping(value="/notes", method=RequestMethod.GET)와 동일하다.

- @PostMapping("/notes") 어노테이션은 @RequestMapping(value="/notes", method=RequestMethod.POST)와 동일하다.

package com.itdev4u.controller;

import java.util.List;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.itdev4u.exception.ResourceNotFoundException;
import com.itdev4u.model.Note;
import com.itdev4u.repository.NoteRepository;

@RestController
@RequestMapping("/api")
public class NoteController {
	@Autowired NoteRepository noteRepository;
	
	// Get All Notes
	@GetMapping("notes")
	public List<Note> getAllNotes() {
		return noteRepository.findAll();
	}
	
	// Create a new Note
	@PostMapping("notes")
	public Note createNote(@Valid @RequestBody Note note) {
		return noteRepository.save(note);
	}
	
	// Get a Single Note
	@GetMapping("/notes/{id}")
	public Note getNoteById(@PathVariable(value = "id") Long noteId) {
		return noteRepository.findById(noteId)
					      .orElseThrow(() -> new ResourceNotFoundException("Note", "id", noteId));
	}
	
	// Update a Note
	@PutMapping("/notes/{id}")
	public Note updateNote(@PathVariable(value = "id") Long noteId
						 , @Valid @RequestBody Note noteDetails) {
		Note note = noteRepository.findById(noteId)
						    .orElseThrow(() -> new ResourceNotFoundException("Note", "id", noteId));
		
		note.setTitle(noteDetails.getTitle());
		note.setContent(noteDetails.getContent());
		
		Note updateNote = noteRepository.save(note);
		return updateNote;
	}
	
	// Delete a Note
	@DeleteMapping("/notes/{id}")
	public ResponseEntity<?> deleteNote(@PathVariable(value = "id") Long noteId) {
		Note note = noteRepository.findById(noteId)
						    .orElseThrow(() -> new ResourceNotFoundException("Note", "id", noteId));
		noteRepository.delete(note);
		
		return ResponseEntity.ok().build();
	}
}

4. Postman Tool을 이용하여 API Serivce를 Test한다.

- POST /api/notes API Test (생성)

- GET /api/notes API Test (전체조회)

- GET /api/notes/{noteId} API Test (단건조회)

- PUT /api/notes/{noteId} API Test (Update)

- DELETE /api/notes/{noteId} API Test (단건삭제)

Spring Boot, JPA, Oracle, Restful CRUD API 서비스 구축1

1. Eclipse에서 Spring Start Project를 선택해서 신규 Project를 생성한다. Project의 설정값은 아래와 같이 설정한다.

- Name : easy-notes

- Type : Maven

- Packaging : jar

- Group : com.itdev4u

- Artifact : easy-notes

- Package : com.itdev4u

- Dependencies : Web, JPA, DevTools

2. 생성된 Project의 디렉토리 구조는 아래 그림과 같이 생성된다.

3. 각 파일 및 디렉토리의 역활은 아래와 같다.

3-1. EasyNotesApplication.java : Spring Boot Project의 시작점이 되는 class이다.

3-2. resources : 정적 resource, 템플릿 등록정보등을 저장하는 디렉토리이다.

- resources/static : css, js, image등의 정적 resource파일을 저장하는 디렉토리이다.

- resources/templates : Spring에서 렌더링되는 서버측 템플릿 파일을 저장하는 디렉토리이다.

- resources/application.properties : Application의 전역속성을 포함한다. Spring은 이 파일에 정의된 속성을 읽은 후 Application을 설정한다.

- pom.xml : Project 에 필요한 Package 정보를 저장한다.

4. com.itdev4u.EasyNotesApplication.java 파일은 아래와 같다.
- @SpringBootApplication 어노테이션이 이 파일이 Spring Boot Project의 시작점이라는것을 지정한다.

package com.itdev4u;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class EasyNotesApplication {

	public static void main(String[] args) {
		SpringApplication.run(EasyNotesApplication.class, args);
	}
}

5. pom.xml 파일은 아래와 같다.
- Oracle은 별도로 Project 우클릭 > Import > Maven > Install or deploy an artifact to Maven repository 메뉴에서 등록을 함.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.itdev4u</groupId>
	<artifactId>easy-notes</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>easy-notes</name>
	<description>Rest Api JPA Test</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- Oracle 설정 -->	
		<!-- Oracle은  Install local repository 설정으로 import함 -->
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

6. resources/application.properties 파일에 Oracle정보와 Application에 필요한 정보를 작성한다.

#Oracle DataBase
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=scott
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver 

# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

#Create and Drop tables, sequences...
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

7. Oracle 에 Table 및 key값으로 사용될 Sequence를 생성한다.

/* Table 생성 */
CREATE TABLE NOTES 
(
  ID NUMBER NOT NULL 
, TITLE VARCHAR2(200) NOT NULL 
, CONTENT VARCHAR2(4000) NOT NULL 
, CREATEDAT TIMESTAMP 
, UPDATEDAT TIMESTAMP 
, CONSTRAINT NOTES_PK PRIMARY KEY (ID) ENABLE 
);

COMMENT ON COLUMN NOTES.ID IS 'Primary Key : Sequence';
COMMENT ON COLUMN NOTES.TITLE IS '제목';
COMMENT ON COLUMN NOTES.CONTENT IS '내용';
COMMENT ON COLUMN NOTES.CREATEDAT IS '생성일';
COMMENT ON COLUMN NOTES.UPDATEDAT IS '수정일';

/* Sequence 생성 */
CREATE SEQUENCE SEQUENCE_NOTES INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 MINVALUE 1;

8. Spring에서 DataBase와 매핑할 Entity class를 생성한다.

- com.itdev4u.model.Note.java 파일을 생성한 후 아래와 같이 작성한다.

package com.itdev4u.model;

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.NotBlank;

import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@Table(name="NOTES")
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = {"createdAt", "updatedAt"}, allowGetters = true)
public class Note implements Serializable {
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "notes_seq")
	@SequenceGenerator(sequenceName = "sequence_notes", allocationSize = 1, name="notes_seq")
	private Long id;
	
	@NotBlank
	private String title;
	
	@NotBlank
	private String content;
	
	
	@Column(nullable = false, updatable = false)
	//@Temporal(TemporalType.TIMESTAMP)
	@CreatedDate
	private Timestamp createdat;
	
	@Column(nullable = false, updatable = false)
	//@Temporal(TemporalType.TIMESTAMP)
	@LastModifiedDate
	private Timestamp updatedat;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public Timestamp getCreatedat() {
		return createdat;
	}

	public void setCreatedat(Timestamp createdat) {
		this.createdat = createdat;
	}

	public Timestamp getUpdatedat() {
		return updatedat;
	}

	public void setUpdatedat(Timestamp updatedat) {
		this.updatedat = updatedat;
	}	
}

9. Note class에서 Model을 정의할때 AuditingEntityListener.class를 지정했으므로 EasyNotesApplication.java 파일에 해당 어노테이션 추가

package com.itdev4u;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing //추가
public class EasyNotesApplication {

	public static void main(String[] args) {
		SpringApplication.run(EasyNotesApplication.class, args);
	}
}

Decode 함수2

[출처] SQL튜닝의 시작(박성호,오수영 지음)

다음 SQL문은 :B1 값이 Null이면 전체를 조회하고, :B1 이 Null이 아닌 경우에는 EMPNO를 :B1 값으로 조회하는 SQL이다.

SELECT *
FROM SALES_TEST A
WHERE EMPNO = DECODE(:B1, NULL, A.EMPNO, :B1)

※ EMPNO는 선택도가 좋은 Column이고 Index가 존재하므로 :B1이 Not Null이라면 Index를 사용하는것이 효율적이고, :B1이 Null이라면 조건절이 EMPNO = A.EMPNO가 되므로 전체 데이터를 읽어야 하므로 Table Full Scan이 가장 효율적인 방법이 된다.

SELECT /*+ INDEX(A IDX_SALES_TEST_02) */
    *
FROM SALES_TEST A
WHERE EMPNO = :BI
AND :B1 IS NOT NULL
UNION ALL
SELECT /*+ FULL(B) */
    *
FROM SALES_TEST B
WHERE EMPNO = EMPNO
AND :B1 IS NULL

※ 위의 SQL문에서 SALES_TEST Table을 2번 Access하는것 처럼 보이지만 WHERE절에 AND :B1 IS NULL(IS NOT NULL) 조건에 의해 :B1의 값에 따른 SQL수행은 실제로 하나의 조건에 대해서만 실행이 된다.

Decode 함수1

[출처] SQL튜닝의 시작(박성호,오수영 지음)

1. Test를 위한 Table 생성 및 샘플 데이터 입력.

CREATE TABLE SALES_TEST (
    SALE_DT CHAR(8),            /* 판매일자 */
    EMPNO NUMBER,               /* 사원번호 */
    DEPTNO NUMBER,              /* 부서번호 */
    TARGET NUMBER NOT NULL,     /* 목표 판매량 */
    SALECNT NUMBER NOT NULL,    /* 실제 판매량 */    
    SALE_DESC VARCHAR2(200)     /* 목표와 실제 판매량에 대한 상세내용 */
);

INSERT INTO SALES_TEST VALUE
SELECT '20180301'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 710)
    , MOD(LEVEL, 400)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,710)||' ==> ' || MOD(LEVEL, 400)
FROM DUAL
CONNECT BY LEVEL <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180302'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 300)
    , MOD(LEVEL, 100)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,310)||' ==> ' || MOD(LEVEL, 100)
FROM DUAL
CONNECT BY LEVEL+1128 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180303'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 128)
    , MOD(LEVEL, 98)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,128)||' ==> ' || MOD(LEVEL, 98)
FROM DUAL
CONNECT BY LEVEL+528 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180304'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 238)
    , MOD(LEVEL, 900)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,238)||' ==> ' || MOD(LEVEL, 900)
FROM DUAL
CONNECT BY LEVEL+278 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180305'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 897)
    , MOD(LEVEL, 1258)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,897)||' ==> ' || MOD(LEVEL, 1258)
FROM DUAL
CONNECT BY LEVEL+278 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180306'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 70)
    , MOD(LEVEL, 40)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,70)||' ==> ' || MOD(LEVEL, 40)
FROM DUAL
CONNECT BY LEVEL+134 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180307'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 8548)
    , MOD(LEVEL, 500)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,8548)||' ==> ' || MOD(LEVEL, 500)
FROM DUAL
CONNECT BY LEVEL+38119 <= 100000
;

COMMIT;

CREATE INDEX IDX_SALES_TEST_01 ON SALES_TEST(SALE_DT);
CREATE INDEX IDX_SALES_TEST_02 ON SALES_TEST(EMPNO);
EXEC dbms_stats.gather_table_stats('SCOTT', 'SALES_TEST');

2. 각 일자별 판매목표와 판매량 데이터를 colunm형태로 조회하는데 스칼라서브 쿼리를 이용해서 조회하는 경우.

SELECT '2018/03/01' AS SALEDT_0301
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180301') AS TARGET_0301
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180301') AS SALE_0301
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180302') AS TARGET_0302
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180302') AS SALE_0302
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180303') AS TARGET_0303
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180303') AS SALE_0303
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180304') AS TARGET_0304
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180304') AS SALE_0304
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180305') AS TARGET_0305
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180305') AS SALE_0305
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180306') AS TARGET_0306
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180306') AS SALE_0306
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180307') AS TARGET_0307
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180307') AS SALE_0307
FROM DUAL

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.40       0.44          0      13334          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.42       0.46          0      13334          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
 100000   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1025 pr=0 pw=0 time=114193 us cost=954 size=1224847 card=94219)
 100000    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=33887 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
 100000   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1025 pr=0 pw=0 time=58695 us cost=954 size=1224847 card=94219)
 100000    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=17263 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=988 pr=0 pw=0 time=0 us)
  98872   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=988 pr=0 pw=0 time=57429 us cost=954 size=1224847 card=94219)
  98872    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=278 pr=0 pw=0 time=14069 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=988 pr=0 pw=0 time=0 us)
  98872   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=988 pr=0 pw=0 time=54871 us cost=954 size=1224847 card=94219)
  98872    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=278 pr=0 pw=0 time=13685 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=981 pr=0 pw=0 time=0 us)
  99472   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=981 pr=0 pw=0 time=55488 us cost=954 size=1224847 card=94219)
  99472    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=14959 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=981 pr=0 pw=0 time=0 us)
  99472   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=981 pr=0 pw=0 time=64438 us cost=954 size=1224847 card=94219)
  99472    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=19945 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1018 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1018 pr=0 pw=0 time=75046 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=280 pr=0 pw=0 time=15725 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1018 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1018 pr=0 pw=0 time=55357 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=280 pr=0 pw=0 time=12273 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1032 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1032 pr=0 pw=0 time=60983 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=14702 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1032 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1032 pr=0 pw=0 time=55869 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=13807 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=976 pr=0 pw=0 time=0 us)
  99866   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=976 pr=0 pw=0 time=58307 us cost=954 size=1224847 card=94219)
  99866    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=13554 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=976 pr=0 pw=0 time=0 us)
  99866   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=976 pr=0 pw=0 time=55878 us cost=954 size=1224847 card=94219)
  99866    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=14193 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=647 pr=0 pw=0 time=0 us)
  61881   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=647 pr=0 pw=0 time=34903 us cost=954 size=1224847 card=94219)
  61881    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=176 pr=0 pw=0 time=8693 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=647 pr=0 pw=0 time=0 us)
  61881   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=647 pr=0 pw=0 time=34008 us cost=954 size=1224847 card=94219)
  61881    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=176 pr=0 pw=0 time=9716 us cost=266 size=0 card=94219)(object id 74707)
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

3. 각 일자별 판매목표와 판매량 데이터를 colunm형태로 조회하는데 Decode함수를 이용해서 조회하는 경우.

SELECT '2018/03' AS SALEDT_03
    , SUM(DECODE(SALE_DT, '20180301', TARGET, 0)) AS TARGER_0301
    , SUM(DECODE(SALE_DT, '20180301', SALECNT, 0)) AS SALE_0301
    , SUM(DECODE(SALE_DT, '20180302', TARGET, 0)) AS TARGER_0302
    , SUM(DECODE(SALE_DT, '20180302', SALECNT, 0)) AS SALE_0302
    , SUM(DECODE(SALE_DT, '20180303', TARGET, 0)) AS TARGER_0303
    , SUM(DECODE(SALE_DT, '20180303', SALECNT, 0)) AS SALE_0303
    , SUM(DECODE(SALE_DT, '20180304', TARGET, 0)) AS TARGER_0304
    , SUM(DECODE(SALE_DT, '20180304', SALECNT, 0)) AS SALE_0304
    , SUM(DECODE(SALE_DT, '20180305', TARGET, 0)) AS TARGER_0305
    , SUM(DECODE(SALE_DT, '20180305', SALECNT, 0)) AS SALE_0305
    , SUM(DECODE(SALE_DT, '20180306', TARGET, 0)) AS TARGER_0306
    , SUM(DECODE(SALE_DT, '20180306', SALECNT, 0)) AS SALE_0306
    , SUM(DECODE(SALE_DT, '20180307', TARGET, 0)) AS TARGER_0307
    , SUM(DECODE(SALE_DT, '20180307', SALECNT, 0)) AS SALE_0307
FROM SALES_TEST

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.48       0.52          0       4856          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.49       0.53          0       4856          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4856 pr=0 pw=0 time=0 us)
 659535   TABLE ACCESS FULL SALES_TEST (cr=4856 pr=0 pw=0 time=125558 us cost=1346 size=11212095 card=659535)

4. 각 일자별 판매목표와 판매량 데이터를 colunm형태로 조회하는데 Decode함수를 이용하는데 불필요한 Default 제거.

SELECT '2018/03' AS SALEDT_03
    , SUM(DECODE(SALE_DT, '20180301', TARGET)) AS TARGER_0301
    , SUM(DECODE(SALE_DT, '20180301', SALECNT)) AS SALE_0301
    , SUM(DECODE(SALE_DT, '20180302', TARGET)) AS TARGER_0302
    , SUM(DECODE(SALE_DT, '20180302', SALECNT)) AS SALE_0302
    , SUM(DECODE(SALE_DT, '20180303', TARGET)) AS TARGER_0303
    , SUM(DECODE(SALE_DT, '20180303', SALECNT)) AS SALE_0303
    , SUM(DECODE(SALE_DT, '20180304', TARGET)) AS TARGER_0304
    , SUM(DECODE(SALE_DT, '20180304', SALECNT)) AS SALE_0304
    , SUM(DECODE(SALE_DT, '20180305', TARGET)) AS TARGER_0305
    , SUM(DECODE(SALE_DT, '20180305', SALECNT)) AS SALE_0305
    , SUM(DECODE(SALE_DT, '20180306', TARGET)) AS TARGER_0306
    , SUM(DECODE(SALE_DT, '20180306', SALECNT)) AS SALE_0306
    , SUM(DECODE(SALE_DT, '20180307', TARGET)) AS TARGER_0307
    , SUM(DECODE(SALE_DT, '20180307', SALECNT)) AS SALE_0307
FROM SALES_TEST

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.36       0.39          0       4856          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.37       0.40          0       4856          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4856 pr=0 pw=0 time=0 us)
 659535   TABLE ACCESS FULL SALES_TEST (cr=4856 pr=0 pw=0 time=102904 us cost=1336 size=11212095 card=659535)

5. Decode함수의 Default 값 제거에 따른 오류데이터 발생 가능성을 NVL함수로 제거.

SELECT '2018/03' AS SALEDT_03
    , NVL(SUM(DECODE(SALE_DT, '20180301', TARGET)),0) AS TARGER_0301
    , NVL(SUM(DECODE(SALE_DT, '20180301', SALECNT)),0) AS SALE_0301
    , NVL(SUM(DECODE(SALE_DT, '20180302', TARGET)),0) AS TARGER_0302
    , NVL(SUM(DECODE(SALE_DT, '20180302', SALECNT)),0) AS SALE_0302
    , NVL(SUM(DECODE(SALE_DT, '20180303', TARGET)),0) AS TARGER_0303
    , NVL(SUM(DECODE(SALE_DT, '20180303', SALECNT)),0) AS SALE_0303
    , NVL(SUM(DECODE(SALE_DT, '20180304', TARGET)),0) AS TARGER_0304
    , NVL(SUM(DECODE(SALE_DT, '20180304', SALECNT)),0) AS SALE_0304
    , NVL(SUM(DECODE(SALE_DT, '20180305', TARGET)),0) AS TARGER_0305
    , NVL(SUM(DECODE(SALE_DT, '20180305', SALECNT)),0) AS SALE_0305
    , NVL(SUM(DECODE(SALE_DT, '20180306', TARGET)),0) AS TARGER_0306
    , NVL(SUM(DECODE(SALE_DT, '20180306', SALECNT)),0) AS SALE_0306
    , NVL(SUM(DECODE(SALE_DT, '20180307', TARGET)),0) AS TARGER_0307
    , NVL(SUM(DECODE(SALE_DT, '20180307', SALECNT)),0) AS SALE_0307
FROM SALES_TEST

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.37       0.41          0       4856          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.38       0.41          0       4856          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4856 pr=0 pw=0 time=0 us)
 659535   TABLE ACCESS FULL SALES_TEST (cr=4856 pr=0 pw=0 time=99064 us cost=1336 size=11212095 card=659535)

Spring RESTFul Web Service

1. STS에서 Spring Start Project에서 새로운 Project를 생성한다.

※ New Spring Starter Project 설정창에서 아래와 같이 입력한다.

- Name : restApiFirst

- Group : com.itdev4u

- Artifact : restApiFirst

- Description : Rest Api Test

- Package : com.itdev4u

※ New Spring Start Project Dependencies 설정창에서 선택사항은 아래와 같다.

- Web

2. STS에서 Spring Start Project에서 새로운 Project를 생성한 후 pom.xml 파일을 수정한다.

- 아래는 pom.xml 파일이다.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.itdev4u</groupId>
	<artifactId>restApiFirst</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>restApiFirst</name>
	<description>Rest Api Test</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

3. SpringBoot가 실행될때 처음으로 실행될 class를 확인한다.

- @SpringBootApplication 어노테이션이 지정된 파일이 SpringBoot가 실행될때 최초로 실행되는 파일이다.

package com.itdev4u;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class RestApiFirstApplication {

	public static void main(String[] args) {
		SpringApplication.run(RestApiFirstApplication.class, args);
	}
}

4. Client에게 전송할 데이터 model class파일을 작성한다.

- com.itdev4u.model.Greeting.java 파일을 생성한다.

package com.itdev4u.model;

public class Greeting {
	private final long id;
	private final String content;
	
	public Greeting(long id, String content) {
		this.id = id;
		this.content = content;
	}
	
	public long getId() {
		return id;
	}
	
	public String getContent() {
		return content;
	}
}

5. Client가 요청할 url에 대한 controller class파일을 작성한다.

- com.itdev4u.controller.GreetingController.java

package com.itdev4u.controller;

import java.util.concurrent.atomic.AtomicLong;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.itdev4u.model.Greeting;

@RestController
public class GreetingController {
	private static final String template = "Hello, %s!";
	private final AtomicLong counter = new AtomicLong();
	
	@RequestMapping("/greeting")
	public Greeting greeting(@RequestParam(value="name", defaultValue="World") String name) {
		return new Greeting(counter.incrementAndGet(), String.format(template, name));
	}
}

6. Project를 Spring Boot App로 Start한 후 브라우져나 Client Tool로 Test한다.

- 본인은 postman을 이용해서 Test함.

※ http://localhost:8080/greeting

==> {"id":5,"content":"Hello, World!"}

※ http://localhost:8080/greetion?name=itdev4u

==> {"id":6,"content":"Hello, itdev4u!"}