sql문
//회원가입 insertUser()
insert into user(id, name, password) values ('ssh0417', '손승현', '0417ssh');
//test 회원가입한 유저 지우고 싶을 때 mysql에 쓰세요
delete from user where userIdx = 6;
//로그인(1) checkIdAndPw()
select exists(select id, password from user where id = 'ssh0417' and password = '0417ssh');
//로그인(2) selectUser()
select userIdx
from user
where id='ssh0417' and password='0417ssh'
//유저 정보 조회 selectUserInfo()
select id, name, (select count(*) from clothes where userIdx = 1 group by userIdx) as numOfClth
from user
where userIdx = 1;
//유저 확인 checkUserExist()
select exists(select userIdx from user where userIdx = 1);
//아이디 중복 확인 checkIdOverlap()
select exists(select id from user where id='ssh0417');
//전체 옷 조회
select clthIdx, clthImgUrl
from clothes
where userIdx = 1;
//개별 옷 정보 조회
select clthImgUrl, bookmark, category, season
from clothes
where clthIdx = 2;
//즐겨찾기 된 옷 조회
select clthIdx, clthImgUrl
from clothes
where bookmark=true and userIdx = 1;
//옷 등록
insert into clothes(userIdx, clthImgUrl, bookmark, category, season) values (2,'/img2',true,'바치','겨울');
//옷 삭제
delete from clothes where clthIdx = 5;
//옷 수정
update clothes
set bookmark = false, category='롱패딩', season='겨울'
where clthIdx=3;
repository
//회원가입 insertUser()
String insertUserQuery = "insert into user(id, name, password) values (?, ?, ?)";
Object[] insertUserParams = new Object[] {postSignUpReq.getId(), postSignUpReq.getName(), postSignUpReq.getPassword()};
this.jdbcTemplate.update(insertUserQuery, insertUserParams);
//유저 확인 checkUserExist()
String checkUserExistQuery = "select exists(select userIdx from user where userIdx = ?)";
int checkUserExistParams = userIdx;
return this.jdbcTemplate.queryForObject(checkUserExistQuery, int.class, checkUserExistParams);
//로그인(2)