SpringBoot JPA複雑クエリー2

	private String name;
	private String subject;
	//       teamId,         team_id
	private int teamId;
	@Id
	private int id;
	private double score;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.zz.entity.Student;


public interface StudentRepository extends JpaRepository{
	//          ，        
	public List findBySubjectOrderByScoreDesc(String subject);
	
	//   ，   
	public Page findBySubjectOrderByScoreDesc(String sub,Pageable pageable);
	
	//    JPQL（JPQL  Java       ），      ，           entity       
	//         s,   *
	@Query("select s from Student s where s.teamId=?1 and s.name=?2")
	public Student getStudent(int teamId,String name);
	
	//nativeQuery=true     SQL
	//        ，                      
	@Query(value="select*from student where team_id=?1 and name=?2",nativeQuery=true)
	public Student getStudentsssdfds1(int tid,String name);
	
	//      Entity,   Object[]    
	@Query(value="select subject,count(*) from student group by subject",nativeQuery=true)
	public List

import java.util.List;

import javax.annotation.Resource;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.zz.entity.Student;
import com.zz.repository.StudentRepository;

@Service
public class StudentService {
	@Resource
	StudentRepository studentRepository;
	//          ，        
	public List findBySubjectOrderByScoreDesc(String subject){
		return studentRepository.findBySubjectOrderByScoreDesc(subject);
	}

	 //    
	public Page findBySubjectOrderByScoreDesc(String sub,String startpage,String limit){
	Pageable pageable = PageRequest.of(Integer.parseInt(startpage), Integer.parseInt(limit));
	return studentRepository.findBySubjectOrderByScoreDesc(sub, pageable);
	}

/*
	 *     
	 * Sort.Direction.DESC       
	 * Sort.Direction.ASC        
	 */
	public Page showall(int page,int size){
		Sort sort=new Sort(Sort.Direction.DESC,"id");
		Pageable pageable= PageRequest.of(page, size,sort);
		return memberRepository.findAll(pageable);
	}

	//JPQL  
	public Student getStudent(int teamId,String name) {
	return studentRepository.getStudent(teamId, name);
	}
}

import java.util.List;

import javax.annotation.Resource;

import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.zz.entity.Student;
import com.zz.service.StudentService;

@RestController
@RequestMapping("student")
public class StudentController {
	@Resource
	StudentService studentService;
	@RequestMapping("sub/{subject}")
	public List showBySub(@PathVariable("subject") String sub){
		return studentService.findBySubjectOrderByScoreDesc(sub);
	}

	@RequestMapping("pagesub/{subject}/{start}/{limit}")
	public Page showPageBySub(@PathVariable("subject") String subject,@PathVariable("start") String start,@PathVariable("limit") String limit){
		return studentService.findBySubjectOrderByScoreDesc(subject,start,limit);
	}
	
	@RequestMapping("getstudent/{teamId}/{name}")
	public Student showPageBySub(@PathVariable("teamId") Integer teamId,@PathVariable("name") String name){
		return studentService.getStudent(teamId, name);
	}
}

import java.util.List;

import javax.annotation.Resource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.zz.entity.Student;
import com.zz.repository.StudentRepository;

@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentTest {
	@Resource
	StudentRepository studentRepository;
	@Test
	public void t1(){
		Student s=studentRepository.getStudent(6, "  ");
		System.out.println(s.getSubject());
	}

	@Test
	public void t2(){
		List

/**
		 *      ，       null    ，    （         ）
	     *  JPA      @Query()   sql        id UId       
         *        ，  @Modifying @Transactional     
		 * @param member  
		 * @return int        
		 */
		@Modifying
		@Query("update tb_member tm set "+
		"tm.name=CASE WHEN:#{#m.name} IS NULL THEN tm.name ELSE:#{#m.name} END,"+
		"tm.sex=CASE WHEN:#{#m.sex} IS NULL THEN tm.sex ELSE:#{#m.sex} END,"+
		"tm.age=CASE WHEN:#{#m.age} <=0 THEN tm.age ELSE:#{#m.age} END,"+
		"tm.phone=CASE WHEN:#{#m.phone} IS NULL THEN tm.phone ELSE:#{#m.phone} END "+
		"where tm.id=:#{#m.id}")
		public int update(@Param("m") Member member);