mysql 샘플 테이블 + 쿼리 연습 문제

http://java-school.net/jdbc/SQL-SELECT-Statement CREATE TABLE DEPT (     DEPTNO DECIMAL ( 2 ),     DNAME VARCHAR ( 14 ),     LOC VARCHAR ( 13 ),     CONSTRAINT PK_DEPT PRIMARY KEY ( DEPTNO ) ); CREATE TABLE EMP (     EMPNO DECIMAL ( 4 ),     ENAME VARCHAR ( 10 ),     JOB VARCHAR ( 9 ),     MGR DECIMAL ( 4 ),     HIREDATE DATE ,     SAL DECIMAL ( 7 , 2 ),     COMM DECIMAL ( 7 , 2 ),     DEPTNO DECIMAL ( 2 ),     CONSTRAINT PK_EMP PRIMARY KEY ( EMPNO ),     CONSTRAINT FK_DEPTNO FOREIGN KEY ( DEPTNO ) REFERENCES DEPT ( DEPTNO ) ); CREATE TABLE SALGRADE (     GRADE TINYINT ,     LOSAL SMALLINT ,     HISAL SMALLINT ); INSE...

Spring boot + MongoDB 페북 로그인, 회원가입 api


spring social 사용하지 않고 구현.


앱 클라이언트에서 넘겨준 access token 등의 값을 받아서 SocialAccount에 저장
 ->
 access token 유효한지 확인
 ->
 유효하면 회원 정보 받아오기(json)
 ->
 DB에서 회원 id검색해서 있으면 로그인 없으면 회원가입




/**
* 페이스북 프로필 정보 가져오기
* @param access_token
* @return 프로필 정보
*/
public Map<String, Object> getFacebookProfileInfo(String access_token){

String getProfileUrl = "https://graph.facebook.com/v2.10/me?fields=email,name,picture&access_token=" + access_token;
String profileInfoStr = "";



Map<String, Object> result = new HashMap<String, Object>();

try {

RestTemplate restTemplate = new RestTemplate();
//페이스북에서 프로필 정보 받아오기.. 여기서 에러나면 catch
logger.info("getProfileURl =====================>{} ",getProfileUrl);
profileInfoStr = restTemplate.getForObject(getProfileUrl, String.class);

//프로필정보 string -> json
JSONObject jsonObject = new JSONObject(profileInfoStr);

String id = (String) jsonObject.get("id");
String name = (String) jsonObject.get("name");
String picture = (String) ((JSONObject)((JSONObject) jsonObject.get("picture")).get("data")).get("url");

result.put("id", id);
result.put("name", name);
result.put("picture", picture);

}catch(HttpClientErrorException e) {

String responseBody = e.getResponseBodyAsString();
logger.error("responseBody :{}",responseBody);

throw new UnauthorizedException("토큰이 유효하지 않습니다.","토큰이 유효하지 않습니다.","4012");

}

return result;
}


/**
* 페이스북 로그인 처리
* @param socialMember
* @return
*/
public Map<String,Object> facebookLogin(SocialAccount socialMember){


Map<String,Object> result=new HashMap<String,Object>();
//회원 프로필 정보에서 토큰을 가져온다
Map<String, Object> profileIntoMap = getFacebookProfileInfo(socialMember.getAccessToken());

String providerId="facebook";
String providerUserId=(String)profileIntoMap.get("id");

//회원가입 유무 확인
SocialAccount socialAccount=socialRepository.findByProviderIdAndProviderUserId(providerId,providerUserId);
if(socialAccount==null) throw new NotFoundException("가입하지 않은 사용자입니다","가입하지 않은 사용자입니다","4042");

//기존 가입된 아이디로 위글 토큰발급
Members m=memberService.findById(socialAccount.getMemberObjectId());
String token= AuthSupport.createJsonWebToken(m.getMemberId(),m.getId());

result.put("id",m.getId());
result.put("accessToken", token);

return result;
}

/**페이스 북 회원 가입
* 회원가입
*/

public Map<String,Object> facebookJoin(SocialAccount socialMember){

Map<String,Object> result=new HashMap<String,Object>();
// 1. access_token 가져온다
Map<String, Object> profileIntoMap = getFacebookProfileInfo(socialMember.getAccessToken());

String providerId="facebook";
String providerUserId=(String)profileIntoMap.get("id");
String providerUserName=(String)profileIntoMap.get("name");
String providerUserPicure=(String)profileIntoMap.get("picture");

// 2.1 회원체크
SocialAccount socialAccount=socialRepository.findByProviderIdAndProviderUserId(providerId,providerUserId);
if(socialAccount!=null) throw new NotFoundException("이미 가입된 sns계정입니다.","이미 가입된 sns계정입니다.","4001");

// 2.2 회원가입 저장 - 20170804 수정 : status 추가
Members member=new Members();
member.setMemberId(providerId+"_"+providerUserId);
member.setMemberName(socialMember.getDisplayName());
member.setPassWord(RandomStringUtils.randomAlphabetic(8));
member.setPhoneNumber(socialMember.getPhoneNumber());
member.setUpdateTime(String.valueOf(System.currentTimeMillis()));
member.setStatus(MemberStatus.normal.ordinal());
member.setNationalCode(socialMember.getNationalCode());
if(socialMember.getBarcodeImage() !=null){

String webDir=uploadSupport.getYmdDirPath()+"/qrcode";
String parentPath=commonProperties.getUploadFileDir()+File.separator+webDir;
String newFileName=uploadSupport.makeFileName(socialMember.getBarcodeImage().getOriginalFilename());
uploadSupport.uploadFile(socialMember.getBarcodeImage(),parentPath,newFileName);
member.setQrCode(webDir+File.separator+newFileName);
}

Members members=memberRepository.save(member);

SocialAccount sm=new SocialAccount();
String objectId = members.getId();
sm.setProviderId(providerId);
sm.setProviderUserId(providerUserId);
sm.setMemberObjectId(objectId);
sm.setDisplayName(providerUserName);
sm.setImageUrl(providerUserPicure);
sm.setAccessToken(socialMember.getAccessToken());
sm.setSecret(socialMember.getSecret());
sm.setExpiredDate(socialMember.getExpiredDate());
sm.setCdate(String.valueOf(System.currentTimeMillis()));

socialRepository.save(sm);


String token= AuthSupport.createJsonWebToken(members.getMemberId(),members.getId());
 
result.put("accessToken", token);
result.put("id",members.getId());
 
return result;
}