package com.how2java.springboot.test;

import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.how2java.springboot.Application;
import com.how2java.springboot.dao.CategoryDAO;
import com.how2java.springboot.pojo.Category;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class TestJPA {

	@Autowired CategoryDAO dao;
	
	@Before
	public void before() {
		List<Category> cs=  dao.findAll();
		for (Category c : cs) {
			dao.delete(c);
		}
		
		for (int i = 0; i < 10; i++) {
			Category c = new Category();
			c.setName("category " + i);
			dao.save(c);
		}
		
	}
	
	@Test
	public void test1() {
		List<Category> cs=  dao.findAll();
		System.out.println("所有的分类信息：");
		for (Category c : cs) {
			System.out.println(c.getName());
		}
		System.out.println();
	}
	
	@Test
	public void test2() {
		System.out.println("查询名称是 \"category 1 \"的分类:");
		List<Category> cs=  dao.findByName("category 1");
		for (Category c : cs) {
			System.out.println("c.getName():"+ c.getName());
		}
		System.out.println();
	}
	@Test
	public void test3() {
		System.out.println("根据名称模糊查询，id 大于5, 并且名称正排序查询");
		List<Category> cs=  dao.findByNameLikeAndIdGreaterThanOrderByNameAsc("%3%",5);
		for (Category c : cs) {
			System.out.println(c);
		}
		System.out.println();
		
	}
}

package com.how2java.springboot.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.how2java.springboot.pojo.Category;

public interface CategoryDAO extends JpaRepository<Category,Integer>{

	public List<Category> findByName(String name);
	
	public List<Category> findByNameLikeAndIdGreaterThanOrderByNameAsc(String name, int id);
}

package com.how2java.springboot.pojo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "category_")
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    @Column(name = "id") 
	private int id;
    
    @Column(name = "name")
	private String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Category [id=" + id + ", name=" + name + "]";
	}
	
}

<style>
  table{
   border-collapse:collapse 
  }
  table.jpatable thead tr{ 
     background-color:lightgray;
  }
  td,th{
    border:1px solid black;
  }
</style>
<table class="jpatable">
<thead>
<tr>
<th>关键词</th>
<th>举例</th>
<th>生成的JPQL 语句片段</th>
</tr>
</thead>
<tbody>
<tr>
<td>And</td>
<td>findByLastnameAndFirstname</td>
<td>… where x.lastname = ?1 and x.firstname = ?2</td>
</tr>
<tr>
<td>Or</td>
<td>findByLastnameOrFirstname</td>
<td>… where x.lastname = ?1 or x.firstname = ?2</td>
</tr>
<tr>
<td>Is,Equals</td>
<td>findByFirstname,<br>findByFirstnameIs,<br>findByFirstnameEquals</td>
<td>… where x.firstname = ?1</td>
</tr>
<tr>
<td>Between</td>
<td>findByStartDateBetween</td>
<td>… where x.startDate between ?1 and ?2</td>
</tr>
<tr>
<td>LessThan</td>
<td>findByAgeLessThan</td>
<td>… where x.age < ?1</td>
</tr>
<tr>
<td>LessThanEqual</td>
<td>findByAgeLessThanEqual</td>
<td>… where x.age ⇐ ?1</td>
</tr>
<tr>
<td>GreaterThan</td>
<td>findByAgeGreaterThan</td>
<td>… where x.age > ?1</td>
</tr>
<tr>
<td>GreaterThanEqual</td>
<td>findByAgeGreaterThanEqual</td>
<td>… where x.age >= ?1</td>
</tr>
<tr>
<td>After</td>
<td>findByStartDateAfter</td>
<td>… where x.startDate > ?1</td>
</tr>
<tr>
<td>Before</td>
<td>findByStartDateBefore</td>
<td>… where x.startDate < ?1</td>
</tr>
<tr>
<td>IsNull</td>
<td>findByAgeIsNull</td>
<td>… where x.age is null</td>
</tr>
<tr>
<td>IsNotNull,NotNull</td>
<td>findByAge(Is)NotNull</td>
<td>… where x.age not null</td>
</tr>
<tr>
<td>Like</td>
<td>findByFirstnameLike</td>
<td>… where x.firstname like ?1</td>
</tr>
<tr>
<td>NotLike</td>
<td>findByFirstnameNotLike</td>
<td>… where x.firstname not like ?1</td>
</tr>
<tr>
<td>StartingWith</td>
<td>findByFirstnameStartingWith</td>
<td>… where x.firstname like ?1 (parameter bound with appended %)</td>
</tr>
<tr>
<td>EndingWith</td>
<td>findByFirstnameEndingWith</td>
<td>… where x.firstname like ?1 (parameter bound with prepended %)</td>
</tr>
<tr>
<td>Containing</td>
<td>findByFirstnameContaining</td>
<td>… where x.firstname like ?1 (parameter bound wrapped in %)</td>
</tr>
<tr>
<td>OrderBy</td>
<td>findByAgeOrderByLastnameDesc</td>
<td>… where x.age = ?1 order by x.lastname desc</td>
</tr>
<tr>
<td>Not</td>
<td>findByLastnameNot</td>
<td>… where x.lastname <> ?1</td>
</tr>
<tr>
<td>In</td>
<td>findByAgeIn(Collection<Age> ages)</td>
<td>… where x.age in ?1</td>
</tr>
<tr>
<td>NotIn</td>
<td>findByAgeNotIn(Collection<Age> age)</td>
<td>… where x.age not in ?1</td>
</tr>
<tr>
<td>True</td>
<td>findByActiveTrue()</td>
<td>… where x.active = true</td>
</tr>
<tr>
<td>False</td>
<td>findByActiveFalse()</td>
<td>… where x.active = false</td>
</tr>
<tr>
<td>IgnoreCase</td>
<td>findByFirstnameIgnoreCase</td>
<td>… where UPPER(x.firstame) = UPPER(?1)</td>
</tr>
</tbody>
</table>