javascript
006_Spring Data JPA基于方法名称命名规则查询
1. Repository接口?
1.1. Repository接口是Spring Data JPA中為我我們提供的所有接口中的頂層接口。
1.2. Repository提供了兩種查詢方式的支持?
1.2.1. 基于方法名稱命名規則查詢。
1.2.2. 基于@Query注解查詢。
2. 方法名稱命名規則查詢
2.1. 規則: findBy(關鍵字)+屬性名稱(屬性名稱的首字母大寫)+查詢條件(首字母大寫)。
2.2. 方法名稱命名規則查詢列表
| 關鍵字 | 方法命名 | sql語句 |
| findByName(String name) | where name=? | |
| Is | findByNameIs(String name) | where name=? |
| Equals | findByNameEquals(String name) | where name=? |
| And | findByNameAndSex(String name, String sex) | where name=? and sex=? |
| Or | findByIdOrName(Integer id, String name) | where id=? or name=? |
| Between | findByIdBetween(Integer x, Integer y) | where id between ? and ? |
| LessThan | findByIdLessThan(Integer x) | where id<? |
| LessThanEqual | findByIdLessThanEqual(Integer x) | where id<=? |
| GreaterThan | findByIdGreaterThan(Integer x) | where id>? |
| GreaterThanEqual | findByIdGreaterThanEqual(Integer x) | where id>=? |
| After | findByIdAfter(Integer x) | where id>? |
| Before | findByIdBefore(Integer x) | where id<? |
| IsNull | findByNameIsNull() | where name is null |
| IsNotNull | findByNameIsNotNull() | where name is not null |
| NotNull | findByNameNotNull() | where name is not null |
| Like | findByNameLike(String name) | where name like ? |
| NotLike | findByNameNotLike(String name) | where name not like ? |
| StartingWith | findByNameStartingWith(String name) | where name like ?% |
| EndingWith | findByNameEndingWith(String name) | where name like %? |
| Containing | findByNameContaining(String name) | where name like %?% |
| OrderBy | findBySexOrderByIdDesc(String sex) | where sex=? order by id desc |
| Not | findByNameNot(String name) | where name <> ? |
| In | findByIdIn(Collection<Integer> c) | where id in (?) |
| NotIn | findByIdNotIn(Collection<Integer> c) | where id not in (?) |
3. 方法名稱命名規則查詢例子?
3.1. 創建一個名為spring-data-jpa-naming的Java項目, 同時添加相關jar包, 并添加JUnit能力。
3.2. 新建User.java?
package com.bjbs.pojo;import java.io.Serializable; import java.util.Date; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import org.springframework.format.annotation.DateTimeFormat;@Entity // 指定該類是實體類 @Table(name = "user") // 指定數據庫表名(表名和實體類對應) public class User implements Serializable {private static final long serialVersionUID = 1L;@Id // 指定為主鍵@GeneratedValue(strategy = GenerationType.IDENTITY) // 指定主鍵生成策略@Column(name = "id") // 指定表中列名(列名和屬性名對應)private Integer id;@Column(name = "name")private String name;@Column(name = "sex")private String sex;@Column(name = "birthday")@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")private Date birthday;@Column(name = "address")private String address;public User() {}public User(String name, String sex, Date birthday, String address) {this.name = name;this.sex = sex;this.birthday = birthday;this.address = address;}public User(Integer id, String name, String sex, Date birthday, String address) {this.id = id;this.name = name;this.sex = sex;this.birthday = birthday;this.address = address;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address+ "]";} }3.3. 新建UserRepository.java, 實現Repository
package com.bjbs.dao;import java.util.Collection; import java.util.List; import org.springframework.data.repository.Repository; import com.bjbs.pojo.User;/*** 參數一T: 當前需要映射的實體; 參數二 T: 當前映射的實體中的id的類型*/ public interface UserRepository extends Repository<User, Integer> {/*** 判斷相等*/User findByName(String name);User findByNameIs(String name);User findByNameEquals(String name);/*** And語句*/User findByNameAndSex(String name, String sex);/*** Or語句*/List<User> findByIdOrName(Integer id, String name);/*** Between語句*/List<User> findByIdBetween(Integer x, Integer y);/*** < x*/List<User> findByIdLessThan(Integer x);/*** <= x*/List<User> findByIdLessThanEqual(Integer x);/*** > x*/List<User> findByIdGreaterThan(Integer x);/*** >= x*/List<User> findByIdGreaterThanEqual(Integer x);/*** > x*/List<User> findByIdAfter(Integer x);/*** < x*/List<User> findByIdBefore(Integer x);/*** IsNull語句*/List<User> findByNameIsNull();/*** IsNotNull語句*/List<User> findByNameIsNotNull();/*** NotNull語句*/List<User> findByNameNotNull();/*** Like語句*/List<User> findByNameLike(String name);/*** NotLike語句*/List<User> findByNameNotLike(String name);/*** Like name%*/List<User> findByNameStartingWith(String name);/*** Like %name*/List<User> findByNameEndingWith(String name);/*** Like %name%*/List<User> findByNameContaining(String name);/*** OrderBy語句*/List<User> findBySexOrderByIdDesc(String sex);/*** Not語句*/List<User> findByNameNot(String name);/*** In語句*/List<User> findByIdIn(Collection<Integer> c);/*** NotIn語句*/List<User> findByIdNotIn(Collection<Integer> c); }3.4. 新建UserService.java
package com.bjbs.service;import java.util.Collection; import java.util.List; import com.bjbs.pojo.User;public interface UserService {User findByName(String name);User findByNameIs(String name);User findByNameEquals(String name);User findByNameAndSex(String name, String sex);List<User> findByIdOrName(Integer id, String name);List<User> findByIdBetween(Integer x, Integer y);List<User> findByIdLessThan(Integer x);List<User> findByIdLessThanEqual(Integer x);List<User> findByIdGreaterThan(Integer x);List<User> findByIdGreaterThanEqual(Integer x);List<User> findByIdAfter(Integer x);List<User> findByIdBefore(Integer x);List<User> findByNameIsNull();List<User> findByNameIsNotNull();List<User> findByNameNotNull();List<User> findByNameLike(String name);List<User> findByNameNotLike(String name);List<User> findByNameStartingWith(String name);List<User> findByNameEndingWith(String name);List<User> findByNameContaining(String name);List<User> findBySexOrderByIdDesc(String sex);List<User> findByNameNot(String name);List<User> findByIdIn(Collection<Integer> c);List<User> findByIdNotIn(Collection<Integer> c); }3.5. 新建UserServiceImpl.java
package com.bjbs.service.impl;import java.util.Collection; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.bjbs.dao.UserRepository; import com.bjbs.pojo.User; import com.bjbs.service.UserService;@Service @Transactional public class UserServiceImpl implements UserService {@Autowiredprivate UserRepository userRepository;@Overridepublic User findByName(String name) {return userRepository.findByName(name);}@Overridepublic User findByNameIs(String name) {return userRepository.findByNameIs(name);}@Overridepublic User findByNameEquals(String name) {return userRepository.findByNameEquals(name);}@Overridepublic User findByNameAndSex(String name, String sex) {return userRepository.findByNameAndSex(name, sex);}@Overridepublic List<User> findByIdOrName(Integer id, String name) {return userRepository.findByIdOrName(id, name);}@Overridepublic List<User> findByIdBetween(Integer x, Integer y) {return userRepository.findByIdBetween(x, y);}@Overridepublic List<User> findByIdLessThan(Integer x) {return userRepository.findByIdLessThan(x);}@Overridepublic List<User> findByIdLessThanEqual(Integer x) {return userRepository.findByIdLessThanEqual(x);}@Overridepublic List<User> findByIdGreaterThan(Integer x) {return userRepository.findByIdGreaterThan(x);}@Overridepublic List<User> findByIdGreaterThanEqual(Integer x) {return userRepository.findByIdGreaterThanEqual(x);}@Overridepublic List<User> findByIdAfter(Integer x) {return userRepository.findByIdAfter(x);}@Overridepublic List<User> findByIdBefore(Integer x) {return userRepository.findByIdBefore(x);}@Overridepublic List<User> findByNameIsNull() {return userRepository.findByNameIsNull();}@Overridepublic List<User> findByNameIsNotNull() {return userRepository.findByNameIsNotNull();}@Overridepublic List<User> findByNameNotNull() {return userRepository.findByNameIsNotNull();}@Overridepublic List<User> findByNameLike(String name) {return userRepository.findByNameLike(name);}@Overridepublic List<User> findByNameNotLike(String name) {return userRepository.findByNameNotLike(name);}@Overridepublic List<User> findByNameStartingWith(String name) {return userRepository.findByNameStartingWith(name);}@Overridepublic List<User> findByNameEndingWith(String name) {return userRepository.findByNameEndingWith(name);}@Overridepublic List<User> findByNameContaining(String name) {return userRepository.findByNameContaining(name);}@Overridepublic List<User> findBySexOrderByIdDesc(String sex) {return userRepository.findBySexOrderByIdDesc(sex);}@Overridepublic List<User> findByNameNot(String name) {return userRepository.findByNameNot(name);}@Overridepublic List<User> findByIdIn(Collection<Integer> c) {return userRepository.findByIdIn(c);}@Overridepublic List<User> findByIdNotIn(Collection<Integer> c) {return userRepository.findByIdNotIn(c);}}3.6. 新建TestUserRepository.java
package com.bjbs.test;import java.util.ArrayList; import java.util.Collection; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import com.bjbs.pojo.User; import com.bjbs.service.UserService;@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:applicationContext.xml") public class TestUserRepository {@Autowiredprivate UserService userService;/*** 查詢姓名等于'曹操'的用戶*/@Testpublic void findByName() {User user = userService.findByName("曹操");System.out.println(user);}/*** 查詢姓名等于'曹操'的用戶*/@Testpublic void findByNameIs() {User user = userService.findByNameIs("曹操");System.out.println(user);}/*** 查詢姓名等于'曹操'的用戶*/@Testpublic void findByNameEquals() {User user = userService.findByNameEquals("曹操");System.out.println(user);}/*** 查詢姓名等于'曹操'并且性別等于'男'的用戶*/@Testpublic void findByNameAndSex() {User user = userService.findByNameAndSex("曹操", "男");System.out.println(user);}/*** 查詢id等于37或的姓名等于'曹操'所有用戶*/@Testpublic void findByIdOrName() {List<User> list = userService.findByIdOrName(37, "曹操");for (User user : list) {System.out.println(user);}}/*** 查詢id在30-35之間的所有用戶(包括id是30和35的用戶)*/@Testpublic void findByIdBetween() {List<User> list = userService.findByIdBetween(30, 35);for (User user : list) {System.out.println(user);}}/*** 查詢id<33的所有用戶*/@Testpublic void findByIdLessThan() {List<User> list = userService.findByIdLessThan(33);for (User user : list) {System.out.println(user);}}/*** 查詢id<=33的所有用戶*/@Testpublic void findByIdLessThanEqual() {List<User> list = userService.findByIdLessThanEqual(33);for (User user : list) {System.out.println(user);}}/*** 查詢id>55的所有用戶*/@Testpublic void findByIdGreaterThan() {List<User> list = userService.findByIdGreaterThan(55);for (User user : list) {System.out.println(user);}}/*** 查詢id>=55的所有用戶*/@Testpublic void findByIdGreaterThanEqual() {List<User> list = userService.findByIdGreaterThanEqual(55);for (User user : list) {System.out.println(user);}}/*** 查詢id>55的所有用戶*/@Testpublic void findByIdAfter() {List<User> list = userService.findByIdAfter(55);for (User user : list) {System.out.println(user);}}/*** 查詢id<33的所有用戶*/@Testpublic void findByIdBefore() {List<User> list = userService.findByIdBefore(33);for (User user : list) {System.out.println(user);}}/*** 查詢姓名為Null的所有用戶*/@Testpublic void findByNameIsNull() {List<User> list = userService.findByNameIsNull();for (User user : list) {System.out.println(user);}}/*** 查詢姓名不是空的所有用戶*/@Testpublic void findByNameIsNotNull() {List<User> list = userService.findByNameIsNotNull();for (User user : list) {System.out.println(user);}}/*** 查詢姓名不是空的所有用戶*/@Testpublic void findByNameNotNull() {List<User> list = userService.findByNameNotNull();for (User user : list) {System.out.println(user);}}/*** 查詢姓名包含'河公'的所有用戶*/@Testpublic void findByNameLike() {List<User> list = userService.findByNameLike("%河公%");for (User user : list) {System.out.println(user);}}/*** 查詢姓名不是以'操'結尾的所有用戶*/@Testpublic void findByNameNotLike() {List<User> list = userService.findByNameNotLike("%操");for (User user : list) {System.out.println(user);}}/*** 查詢姓名以'李'開頭的所有用戶*/@Testpublic void findByNameStartingWith() {List<User> list = userService.findByNameStartingWith("李");for (User user : list) {System.out.println(user);}}/*** 查詢姓名以'操'結尾的所有用戶*/@Testpublic void findByNameEndingWith() {List<User> list = userService.findByNameEndingWith("操");for (User user : list) {System.out.println(user);}}/*** 查詢姓名包含'河公'的所有用戶*/@Testpublic void findByNameContaining() {List<User> list = userService.findByNameContaining("河公");for (User user : list) {System.out.println(user);}}/*** 查詢性別是'女'的所有用戶并按id倒序排列*/@Testpublic void findBySexOrderByIdDesc() {List<User> list = userService.findBySexOrderByIdDesc("女");for (User user : list) {System.out.println(user);}}/*** 查詢姓名不是'曹操'的所有用戶*/@Testpublic void findByNameNot() {List<User> list = userService.findByNameNot("曹操");for (User user : list) {System.out.println(user);}}/*** 查詢id是35 42 43 44的這幾個用戶*/@Testpublic void findByIdIn() {Collection<Integer> c = new ArrayList<Integer>();c.add(35);c.add(42);c.add(43);c.add(44);List<User> list = userService.findByIdIn(c);for (User user : list) {System.out.println(user);}}/*** 查詢id不是35 42 43 44的所有用戶*/@Testpublic void findByIdNotIn() {Collection<Integer> c = new ArrayList<Integer>();c.add(35);c.add(42);c.add(43);c.add(44);List<User> list = userService.findByIdNotIn(c);for (User user : list) {System.out.println(user);}} }3.7. 在src下新建application.properties
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://192.168.25.138:3306/StudyMybatis?useSSL=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.datasource.username=root spring.datasource.password=lyw1234563.8. 在src下新建applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns="http://www.springframework.org/schema/beans"xmlns:context="http://www.springframework.org/schema/context"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:jpa="http://www.springframework.org/schema/data/jpa"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"><!-- 配置讀取properties文件的工具類 --><context:property-placeholder location="classpath:application.properties" /><!-- 配置c3p0數據庫連接池 --><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="jdbcUrl" value="${spring.datasource.url}" /><property name="driverClass" value="${spring.datasource.driverClassName}" /><property name="user" value="${spring.datasource.username}" /><property name="password" value="${spring.datasource.password}" /></bean><!-- Spring整合JPA 配置EntityManagerFactory --><bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"><property name="dataSource" ref="dataSource" /><property name="jpaVendorAdapter"><bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"><!-- hibernate相關的屬性的注入 --><!-- 配置數據庫類型 --><property name="database" value="MYSQL" /><!-- 正向工程 自動創建表 --><!-- <property name="generateDdl" value="true" /> --><!-- 顯示執行的SQL --><property name="showSql" value="true" /></bean></property><!-- 掃描實體的包 --><property name="packagesToScan"><list><value>com.bjbs.pojo</value></list></property></bean><!-- 配置Hibernate的事務管理器 --><bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"><property name="entityManagerFactory" ref="entityManagerFactory" /></bean><!-- 配置開啟注解事務處理 --><tx:annotation-driven transaction-manager="transactionManager" /><!-- 配置springIOC的注解掃描 --><context:component-scan base-package="com.bjbs.service" /><!-- Spring Data JPA 的配置 --><!-- base-package: 掃描dao接口所在的包 --><jpa:repositories base-package="com.bjbs.dao" /> </beans>3.9. 數據庫user表
3.10. 查詢姓名等于'曹操'的用戶?
3.11. 查詢姓名等于'曹操'的用戶?
3.12. 查詢姓名等于'曹操'的用戶?
3.13. 查詢姓名等于'曹操'并且性別等于'男'的用戶?
3.14. 查詢id等于37或的姓名等于'曹操'多個用戶?
3.15. 查詢id在30-35之間的所有用戶(包括id是30和35的用戶)?
3.16. 查詢id<33的所有用戶?
3.17. 查詢id<=33的所有用戶?
3.18. 查詢id>55的所有用戶?
3.19. 查詢id>=55的所有用戶?
3.20. 查詢id>55的所有用戶?
3.21. 查詢id<33的所有用戶?
3.22. 查詢姓名為Null的所有用戶
?3.23. 查詢姓名不是空的所有用戶?
3.24. 查詢姓名不是空的所有用戶
3.25. 查詢姓名包含'河公'的所有用戶
3.26. 查詢姓名不是以'操'結尾的所有用戶?
?3.27. 查詢姓名以'李'開頭的所有用戶
3.28. 查詢姓名以'操'結尾的所有用戶?
3.29. 查詢姓名包含'河公'的所有用戶
3.30. 查詢性別是'女'的所有用戶并按id倒序排列
3.31. 查詢姓名不是'曹操'的所有用戶?
?3.32. 查詢id是35 42 43 44的這幾個用戶
?3.33. 查詢id不是35 42 43 44的所有用戶
總結
以上是生活随笔為你收集整理的006_Spring Data JPA基于方法名称命名规则查询的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 005_Spring Data JPA条
- 下一篇: 007_Spring Data JPA