Repository接口是SpringDataJPA中为我们提供的所有接口中的顶层接口。
Repository提供了两种查询方式的支持:
- 基于方法名称规则查询
- 基于@Query注解查询
创建项目04-spring-data-jpa-repository
pom.xml
<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">
<modelVersion>4.0.0</modelVersion>
<groupId>com.bjsxt</groupId>
<artifactId>04-spring-data-jpa-repository</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<java.version>1.8</java.version>
<spring.version>4.3.23.RELEASE</spring.version>
<hibernate.version>5.1.17.Final</hibernate.version>
<mysql.version>5.1.47</mysql.version>
<c3p0.version>0.9.2.1</c3p0.version>
<junit.version>4.12</junit.version>
<lombok.version>1.18.6</lombok.version>
<hibernate-entitymanager.version>5.0.7.Final</hibernate-entitymanager.version>
<spring-data-jpa.version>1.7.0.RELEASE</spring-data-jpa.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>${hibernate.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>${c3p0.version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>${hibernate-entitymanager.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>${spring-data-jpa.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/spring-data-test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
#jdbc.url=jdbc:mysql://localhost:3306/spring-data-test 这个保存到数据库中会导致乱码
jdbc.driver.class=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.password=123456
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
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.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.11.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">
<!-- 配置读取properties文件的工具类 -->
<context:property-placeholder location="classpath:jdbc.properties" />
<!-- 配置c3p0数据库连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="driverClass" value="${jdbc.driver.class}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.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.bjsxt.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.bjsxt"></context:component-scan>
<!-- Spring Data JPA的配置 base-package扫描dao接口所在的包 -->
<jpa:repositories base-package="com.bjsxt.dao"></jpa:repositories>
</beans>
1 方法名称命名规则查询
1.1 规则:findBy(关键字) + 属性名称(属性名称的首字母大写)+ 查询条件(首字母大写)
关键字 | 方法命名 | sql where 子句 |
And | findByNameAndPwd | where name= ? and pwd= ? |
Or | findByNameOrSex | where name= ? or sex= ? |
Is,Equal | findById,findByIdIs,findByIdEqual | where id = ? |
Between | findByIdBetween | where id between ? and ? |
LessThan | findByIdLessThan | where id < ? |
LessThanEqual | findByIdLessThanEqual | where id <= ? |
GreaterThan | findByIdGreaterThan | where id > ? |
GreaterThanEqual | findByIdGreaterThanEqual | where id >= ? |
After | findByIdAfter | where id > ? |
Before | findByIdBefore | where id < ? |
IsNull | findByNameIsNull | where name is null |
IsNotNull, NotNull | findByNameNotNull | where name is not null |
Like | findByNameLike | where name like ? |
NotLike | findByNameNotLike | where name not like ? |
StartingWith | findByNameStartingWith | where name like '?%' |
EndWith | findByNameEndWith | where name like '%?' |
Containing | findByNameContaining | where name like '%?%' |
OrderBy | findByIdOrderByXDesc | where id = ? order by x desc |
Not | findByNameNot | where name <> ? |
In | findByIdIn(Collection<?> c) | where id in (?) |
NotIn | findByIdNotIn(Collection<?> c) | where id not in (?) |
True | findByAaaTrue | where aaa = true |
False | findByAaaFalse | where aaa = false |
IgnoreCase | findByNameIgnoreCase | where UPPER(name) = UPPER(?) |
缺点:如果查询条件多的话,会导致方法名称特长。
2 编写dao
package com.bjsxt.dao;
import java.util.List;
import org.springframework.data.repository.Repository;
import com.bjsxt.pojo.Users;
public interface UsersDao extends Repository<Users, Long> {
List<Users> findByUserNameIs(String userName);
List<Users> findByUserNameLike(String userName);
List<Users> findByUserNameIsNotNull();
}
3 编写测试类
package com.bjsxt.dao.impl;
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.bjsxt.dao.UsersDao;
import com.bjsxt.pojo.Users;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class RepositoryTest {
@Autowired
private UsersDao usersDao;
/**
* Users(id=5, userName=路飞-海贼王, age=28)
*/
@Test
public void test1() {
/**
* 判断相等的条件,有三种方式表示: 1 什么都不写,默认的就是相等判断 2 IS 3 Equal
*/
List<Users> userList = this.usersDao.findByUserNameIs("路飞-海贼王");
for (Users users : userList) {
System.out.println(users);
}
}
/**
* 根据username做模糊查询 Users(id=5, userName=路飞-海贼王, age=28)
*/
@Test
public void test2() {
List<Users> userList = this.usersDao.findByUserNameLike("路飞%");
for (Users users : userList) {
System.out.println(users);
}
}
/**
* Hibernate: select users0_.id as id1_0_, users0_.age as age2_0_,
* users0_.user_name as user_nam3_0_ from ts_users users0_ where
* users0_.user_name is not null
*/
@Test
public void test3() {
List<Users> userList = this.usersDao.findByUserNameIsNotNull();
for (Users users : userList) {
System.out.println(users);
}
}
}
2 基于@Query注解的查询
2.1 通过JPQL语句查询
JPQL:通过Hibernate的HQL演变而来,它和HQL语法及其相似;如:@Query(value = "from Users where userName = ?")
2.2 通过SQL语句查询
如:@Query(value = "select * from ts_users where user_name = ?", nativeQuery = true)
编写dao
package com.bjsxt.dao;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import com.bjsxt.pojo.Users;
/**
* 基于@Query注解的查询
*
* @author guozi
*
*/
public interface UsersDaoByQueryAnnotation extends Repository<Users, Long> {
@Query(value = "from Users where userName = ?")
List<Users> queryByUserNameUseJPQL(String userName);
@Query(value = "from Users where userName like ?")
List<Users> queryByUserNameLikeUseJPQL(String userName);
@Query(value = "from Users where userName = ? and age = ?")
List<Users> queryByUserNameAndAge(String userName, Integer age);
/**
* nativeQuery:默认是false,表示不开启sql查询,表示是否对value中的语句做转义
*/
@Query(value = "select * from ts_users where user_name = ?", nativeQuery = true)
List<Users> queryByUserNameUseSQL(String userName);
@Query(value = "select * from ts_users where user_name like ?", nativeQuery = true)
List<Users> queryByUserNameLikeUseSQL(String userName);
@Query(value = "select * from ts_users where user_name = ? and age = ?", nativeQuery = true)
List<Users> queryByUserNameAndAgeUseSQL(String userName, Integer age);
}
编写测试类
package com.bjsxt.dao;
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.bjsxt.pojo.Users;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class UsersDaoByQueryAnnotationTest {
@Autowired
private UsersDaoByQueryAnnotation usersDaoByQueryAnnotation;
/**
* Hibernate: select users0_.id as id1_0_, users0_.age as age2_0_,
* users0_.user_name as user_nam3_0_ from ts_users users0_ where
* users0_.user_name=?
*/
@Test
public void testQueryByUserNameUseJPQL() {
List<Users> userList = this.usersDaoByQueryAnnotation.queryByUserNameUseJPQL("路飞-海贼王");
for (Users users : userList) {
System.out.println(users);
}
}
/**
* Hibernate: select users0_.id as id1_0_, users0_.age as age2_0_,
* users0_.user_name as user_nam3_0_ from ts_users users0_ where
* users0_.user_name like ?
*
*/
@Test
public void testQueryByUserNameLikeUseJPQL() {
List<Users> userList = this.usersDaoByQueryAnnotation.queryByUserNameLikeUseJPQL("路飞%");
for (Users users : userList) {
System.out.println(users);
}
}
/**
* Hibernate: select users0_.id as id1_0_, users0_.age as age2_0_,
* users0_.user_name as user_nam3_0_ from ts_users users0_ where
* users0_.user_name=? and users0_.age=?
*
*/
@Test
public void testQueryByUserNameAndAge() {
List<Users> userList = this.usersDaoByQueryAnnotation.queryByUserNameAndAge("路飞-海贼王", 28);
for (Users users : userList) {
System.out.println(users);
}
}
/**
* Hibernate: select * from ts_users where user_name = ?
*/
@Test
public void testQueryByUserNameUseSQL() {
List<Users> userList = this.usersDaoByQueryAnnotation.queryByUserNameUseSQL("路飞-海贼王");
for (Users users : userList) {
System.out.println(users);
}
}
/**
* Hibernate: select * from ts_users where user_name like ?
*/
@Test
public void testQueryByUserNameLikeUseSQL() {
List<Users> userList = this.usersDaoByQueryAnnotation.queryByUserNameLikeUseSQL("路飞%");
for (Users users : userList) {
System.out.println(users);
}
}
/**
* Hibernate: select * from ts_users where user_name = ? and age = ?
*/
@Test
public void testQueryByUserNameAndAgeSQL() {
List<Users> userList = this.usersDaoByQueryAnnotation.queryByUserNameAndAgeUseSQL("路飞-海贼王", 28);
for (Users users : userList) {
System.out.println(users);
}
}
}
3 @Query更新操作
创建dao接口
package com.bjsxt.dao;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import com.bjsxt.pojo.Users;
/**
* 基于@Query注解的更新操作
*
* @author guozi
*
*/
public interface UsersDaoByQueryAnnotationUpdate extends Repository<Users, Long> {
@Query(value = "update Users set age = ? where id = ?")
@Modifying // 表示当前是一个更新语句
void updateAgeById(Integer age, Long id);
}
创建测试类
package com.bjsxt.dao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class UsersDaoByQueryAnnotationUpdateTest {
@Autowired
private UsersDaoByQueryAnnotationUpdate usersDaoByQueryAnnotationUpdate;
/**
* Hibernate: update ts_users set age=? where id=?
* 1 不加@Transactional会抛异常:Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query
* 2 在测试方法中,如果使用事务,不加@Rollback(false)会默认回滚
*/
@Test
@Transactional
@Rollback(false)
public void testUpdateAgeById() {
this.usersDaoByQueryAnnotationUpdate.updateAgeById(18, 5l);
}
}