Spring boot REST API + MySQL(오라클, mybatis)

참고한 영상

www.youtube.com/watch?v=QzHkJsALmyw

 

디펜던시는 이것 추가

mysql 디펜던시와 mybatis 디펜던시를 사용하기 때문에 넣어줌

버전은 삭제해줘도 된다고함

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.3</version>
		</dependency>

추가한 뒤 

 

여기에 username과 password를 적어줌

여기서는 root와 123456

mybatis를 사용하면 sql과 관련된 복잡한 설정없이 이것만 작성해줘도 된다고 함

spring.datasource.url=jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Seoul
spring.datasource.username=root
spring.datasource.password=123456

 

 

workbench로 테이블을 하나 만들었음

 

 

대충 데이터 몇개 넣어줌

 

 

매핑해주는 인터페이스를 만듦

어노테이션으로 sql기능을 만들어주고 controller에서 받은 값을 param으로 받아서 DB에 CRUD 해줌

package com.Study.Study01.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.Study.Study01.model.UserProfile;

@Mapper
public interface UserProfileMapper {

	@Select("SELECT * FROM userprofile WHERE id=${id}")
	UserProfile getUserProfile(@Param("id") String id);
	
	@Select("SELECT * FROM userprofile")
	List<UserProfile> getAllUserProfile();
	
	@Insert("INSERT INTO userprofile(name, phone, address) VALUES(#{name},#{phone},#{address})")
	int addUserProfile(@Param("name") String name, 
			@Param("phone") String phone, 
			@Param("address") String address);
	
	@Update("UPDATE userprofile SET name=#{name}, phone=#{phone}, address=#{address} WHERE id=#{id}")
	int updateUserProfile(@Param("id") String id,
			@Param("name") String name, 
			@Param("phone") String phone, 
			@Param("address") String address);
	
	@Delete("DELETE FROM userprofile WHERE id=#{id}")
	int deleteUserProfile(@Param("id") String id);
	
}

 

package com.Study.Study01.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.Study.Study01.mapper.UserProfileMapper;
import com.Study.Study01.model.UserProfile;
import com.fasterxml.jackson.databind.ObjectMapper;

@RestController
public class UserController {

	private UserProfileMapper mapper;
	private Map<String, UserProfile> userMap;
	
	public UserController(UserProfileMapper mapper) {
		this.mapper = mapper;
	}
	
	@PostConstruct
	public void init() {
		userMap = new HashMap<String, UserProfile>();
	}
	
	@GetMapping("/user/{id}")
	public UserProfile getUserProfile(@PathVariable("id") String id) {
		return mapper.getUserProfile(id);
	}
	
	
	@GetMapping("/user")
	public List<UserProfile> getAllUserProfile() {
		return mapper.getAllUserProfile();
	}
	
	@ResponseBody
	@PostMapping("/user")
	public String addUserProfile(@RequestBody HashMap<String, Object> map) {
		ObjectMapper objectMapper = new ObjectMapper();
		UserProfile profile = objectMapper.convertValue(map, UserProfile.class);
		mapper.addUserProfile(profile.name, profile.phone, profile.address);
		
		return "생성 완료";
	}
	
	@PutMapping("/user")
	public String updateUserProfile(@RequestBody HashMap<String, Object> map) {
		ObjectMapper objectMapper = new ObjectMapper();
		UserProfile profile = objectMapper.convertValue(map, UserProfile.class);
		mapper.updateUserProfile(profile.id,profile.name, profile.phone, profile.address);
		
		return "갱신 완료";
	}
	
	@DeleteMapping("/user")
	public String deleteUserProfile(@RequestBody HashMap<String, Object> map) {
		ObjectMapper objectMapper = new ObjectMapper();
		UserProfile profile = objectMapper.convertValue(map, UserProfile.class);
		mapper.deleteUserProfile(profile.id);
		
		return "삭제 완료";
	}
	
}

 

mybatis는 이렇게 어노테이션으로 작성하는 것보단

xml에 써야한다고 좋다고 함

 

jpa(mybatis와 비슷함)도 써서 연동할 수 있는데 한번 해볼것

 

다시 해볼 것

1. mybatis xml로 해보기

2. jpa 써보기