package com.springdata.study.repository; 
import java.util.List; 
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param; 
import com.springdata.study.entitys.Person;
 
//1.實際上Repository是一個口接口，沒有提供任何方法，是一個標記接口
//2.實現(xiàn)了Repository接口就會被spring IOC容器識別為Repository Bean
//  會被納入IOC容器中
//3.Repository接口也可以同@RepositoryDefinition 注解代替，效果是一樣的
//4.接口中的泛型：第一個是那個實體類的Repository，第二個是實體類的主鍵的類型
//@RepositoryDefinition(domainClass=Person.class,idClass=Integer.class)
 
/**
 * 在Repository接口中申明方法 1.申明方法需要符合一定的規(guī)范 2.查詢方法需要以 find | read | get開頭 3.涉及查詢條件時
 * 需要用條件關(guān)鍵字連接 4.屬性首字母大寫 5.支持級聯(lián)屬性
 * 6.AddressId若當前實體類中有屬性，則優(yōu)先使用該屬性，若想要使用級聯(lián)屬性，需要用下劃線隔開Address_Id
 */ 
public interface PersonRepositoiry extends Repository<Person, Integer> {
 // select p from Person where p.name = ?
 Person getByName(String name);
 
 List<Person> findByNameStartingWithAndIdLessThan(String name, Integer id);
 
 // where name like %? and id < ?
 List<Person> findByNameEndingWithAndIdLessThan(String name, Integer id);
 
 // where email in ? age < ?
 List<Person> readByEmailInOrAgeLessThan(List<String> emails, int age);
 
 // 級聯(lián)屬性查詢
 // where address.id > ?
 List<Person> findByAddress_IdGreaterThan(Integer is);
 
 // 可以使用@Query注解在其value屬性中寫JPA語句靈活查詢
 @Query("SELECT p FROM Person p WHERE p.id = (SELECT max(p2.id) FROM Person p2)")
 Person getMaxIdPerson();
 
 // 在@Query注解中使用占位符
 @Query(value = "SELECT p FROM Person p where p.name = ?1 and p.email = ?2")
 List<Person> queryAnnotationParam1(String name, String email);
 
 // 使用命名參數(shù)傳遞參數(shù)
 @Query(value = "SELECT p FROM Person p where p.name = :name")
 List<Person> queryAnnotationParam2(@Param("name") String name);
 
 // SpringData可以在參數(shù)上添加%
 @Query("SELECT p FROM Person p WHERE p.name LIKE %?1%")
 List<Person> queryAnnotationLikeParam(String name);
 
 // SpringData可以在參數(shù)上添加%
 @Query("SELECT p FROM Person p WHERE p.name LIKE %:name%")
 List<Person> queryAnnotationLikeParam2(@Param("name")String name);
 
 //在@Query注解中添加nativeQuery=true屬性可以使用原生的SQL查詢
 @Query(value="SELECT count(*) FROM jpa_person", nativeQuery=true)
 long getTotalRow(); 
}

package com.springdata.study.test; 
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List; 
import javax.sql.DataSource; 
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.springdata.study.entitys.Person;
import com.springdata.study.repository.PersonRepositoiry;
import com.springdata.study.service.PersonService;
 
public class DataSourceTest { 
 private ApplicationContext applicationContext;
 private PersonService personService;
 private PersonRepositoiry personRepositoiry;
 
 {
  applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  personRepositoiry = applicationContext.getBean(PersonRepositoiry.class);
  personService = applicationContext.getBean(PersonService.class);
 }
 
 @SuppressWarnings("resource")
 @Test
 public void testDataSource() throws SQLException {
  ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  DataSource dataSource = applicationContext.getBean(DataSource.class);
  System.out.println(dataSource.getConnection());
 }
 
 @Test
 public void testSpringdata() {
  Object person = personService.getPerson("LQF");
  System.out.println(person);
 }
 
 @Test
 public void testFindByNameStartingWithAndIdLessThan() {
  List<Person> persons = personRepositoiry.findByNameStartingWithAndIdLessThan("g", 6);
  persons = personRepositoiry.findByNameEndingWithAndIdLessThan("g", 6);
  System.out.println(persons);
 }
 
 @Test
 public void testReadByEmailInOrAgeLessThan() {
  List<Person> persons = personRepositoiry.readByEmailInOrAgeLessThan(Arrays.asList("123@qq.com"), 25);
  System.out.println(persons);
 }
 
 @Test
 public void testFindByAddressIdGreaterThan() {
  personRepositoiry.findByAddress_IdGreaterThan(1);
 }
 
 @Test
 public void testGetMaxIdPerson() {
  Person person = personRepositoiry.getMaxIdPerson();
  System.out.println(person);
 }
 
 @Test
 public void testQueryAnnotationParam() {
  List<Person> persons = personRepositoiry.queryAnnotationParam1("liqingfeng", "123@qq.com");
  System.out.println(persons);
 }
 
 @Test
 public void testQueryAnnotationParam2() {
  List<Person> persons = personRepositoiry.queryAnnotationParam2("lqf");
  System.out.println(persons);
 }
 
 @Test
 public void testQueryAnnotationLikeParam() {
  List<Person> persons = personRepositoiry.queryAnnotationLikeParam2("li");
  System.out.println(persons);
 }
 
 @Test
 public void testGetTotalRow() {
  long count = personRepositoiry.getTotalRow();
  System.out.println(count);
 } 
}

@Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
List<Book> findByPriceRange(long price1, long price2);

@Query(value = "select name,author,price from Book b where b.name like %:name%")
List<Book> findByNameMatch(@Param("name") String name);

@Query(value = "select * from book b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);

@Query(value = "select name,author,price from Book b where b.name = :name AND b.author=:author AND b.price=:price")
List<Book> findByNamedParam(@Param("name") String name, @Param("author") String author,
        @Param("price") long price);

public interface BookQueryRepositoryExample extends Repository<Book, Long>{
       @Query(value = "select * from #{#entityName} b where b.name=?1", nativeQuery = true)
       List<Book> findByName(String name);
}

public interface BookQueryRepositoryExample extends Repository<Book, Long> {
    @Query(value = "select * from Book b where b.name=?1", nativeQuery = true) 
    List<Book> findByName(String name);// 此方法sql將會報錯(java.lang.IllegalArgumentException)，看出原因了嗎,若沒看出來，請看下一個例子
    @Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
    List<Book> findByPriceRange(long price1, long price2);
    @Query(value = "select name,author,price from Book b where b.name like %:name%")
    List<Book> findByNameMatch(@Param("name") String name);
    @Query(value = "select name,author,price from Book b where b.name = :name AND b.author=:author AND b.price=:price")
    List<Book> findByNamedParam(@Param("name") String name, @Param("author") String author,
            @Param("price") long price);
}

@Query(value = "select * from book b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);