JPA对关键字查询的学习
数据库
Java代码
#Entity
@Entity
@Table(name = "t_user")
@Getter
@Setter
public class User implements Serializable{
@Id
@GeneratedValue
private Long id;
private String name;
private String hobby;
}
#repository
public interface UserRepository extends JpaSpecificationExecutor<User>, Repository<User, Long>{
User findOne(Long id);
}
#service
public interface UserService {
Page<User> findBySearch(String search);
User findOne(Long id);
User findOneSpec(Long id);
}
service.impl
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepository;
@Override
public Page<User> findBySearch(final String search) {
Pageable pageable = new PageRequest(0, 20);
Specification<User> spec = new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
String wrapSearch = "%" + search + "%";
Path<Long> $id = root.get("id");
Path<String> name = root.get("name");
Path<String> hobby = root.get("hobby");
Predicate p1 = cb.like(root.get("name").as(String.class), wrapSearch);
Predicate p2 = cb.like(root.get("hobby").as(String.class), wrapSearch);
Predicate p3 = cb.equal($id, 1L);
Predicate p4 = cb.or(p1, p2);
Predicate p = cb.and(p3, p4);
query.where(p);//这种方式使用JPA的API设置了查询条件，所以不需要再返回查询条件Predicate给Spring Data Jpa，故最后return null;即可。
return null;
}
};
return userRepository.findAll(spec, pageable);
}
@Override
public User findOne(Long id){
return userRepository.findOne(id);
}
@Override
public User findOneSpec(final Long id) {
return userRepository.findOne(new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Path<Long> $id = root.get("id");
query.where(cb.equal($id, id));
return null;
}
});
}
}
#SpringBootApplication
@SpringBootApplication
public class SpringBootJpaCriteria {
public static void main(String[] args) {
SpringApplication.run(SpringBootJpaCriteria.class, args);
}
}
application.properties
logging.level.cn.linuxcrypt=debug
# database config
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-active=100
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.test-while-idle=true
spring.datasource.validation-interval=5000
# JPA (JpaBaseConfiguration, HibernateJpaAutoConfiguration)
spring.jpa.properties.hibernate.archive.autodetection=class
spring.jpa.database=MYSQL
spring.data.jpa.repositories.enabled=true
logging.level.org.hibernate.sql=debug
spring.datasource.url=jdbc:mysql://localhost:3307/jpa?allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
test
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration( classes = SpringBootJpaCriteria.class)
public class UserServiceImplTest {
private static final Logger LOGGER = LoggerFactory.getLogger(UserServiceImplTest.class);
@Resource
private UserService userService;
@Test
public void testFindBySearch() throws Exception {
String search = "小";
ObjectMapper om = new ObjectMapper();
om.setSerializationInclusion(JsonInclude.Include.NON_NULL);
Page<User> page = userService.findBySearch(search);
String json = om.writeValueAsString(page);
LOGGER.debug("User List: {}\nsearch: {}", json, search);
}
@Test
public void testFindOne() throws Exception {
String search = "小";
ObjectMapper om = new ObjectMapper();
om.setSerializationInclusion(JsonInclude.Include.NON_NULL);
User user = userService.findOne(1L);
String json = om.writeValueAsString(user);
LOGGER.debug("User: {}\nLong id: {}", json, search);
}
@Test
public void testFindOneSpec() throws Exception {
String search = "小";
ObjectMapper om = new ObjectMapper();
om.setSerializationInclusion(JsonInclude.Include.NON_NULL);
User user = userService.findOneSpec(1L);
String json = om.writeValueAsString(user);
LOGGER.debug("User: {}\nLong id: {}", json, search);
}
}
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>Learn</artifactId>
<groupId>cn.linuxcrypt</groupId>
<version>1.0</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-jpa</artifactId>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>1.3.6.RELEASE</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
补充：级联对象查询，根据用户id查询
@Override
public Predicate toPredicate(Root<Category> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Path<User> user = root.get("user");
Path<Long> id = user.get("id");
if(org.apache.commons.lang3.StringUtils.isNotBlank(search)){
String wrapSearch = CommonAttributes.addPercentInAbout(search);
Path<String> name = root.get("name");
Predicate p1 = cb.equal(id, userId);
Predicate p2 = cb.like(name, wrapSearch);
Predicate p = cb.and(p1, p2);
query.where(p);
}else{
Predicate p = cb.equal(id, userId);
query.where(p);
}
return null;
}
