从一个helloworld开始
建立好springboot的项目后
pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
配置文件
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/jpa?useSSL=FALSE&serverTimezone=GMT%2B8
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
jpa:
hibernate:
ddl-auto: update
show-sql: true
实体类
@Entity
@Data
@RequiredArgsConstructor
@AllArgsConstructor
@NoArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NonNull
private String name;
@NonNull
private String email;
}
仓库文件
public interface UserRepository extends CrudRepository<User,Long> {
}
controller
@RestController
public class UserController {
@Autowired
private UserRepository userRepository;
@SuppressWarnings("unchecked")
@Transactional
@GetMapping("/add")
public Result<String> addNewUser(@RequestParam("name") String name,@RequestParam("email") String email) {
User user = new User(name,email);
userRepository.save(user);
return Result.success("添加用户成功");
}
@GetMapping("/all")
@SuppressWarnings("unchecked")
public Result<Iterable<User>> getAllUsers() {
return Result.success(userRepository.findAll());
}
@GetMapping("/user")
@SuppressWarnings("unchecked")
public Result<User> getUser(@RequestParam("id") Long id) {
return Result.success(userRepository.findById(id));
}
}
也可以直接用名字查询
public interface UserAloneRepository extends Repository<User,Long> {
List<User> findByName(String name);
}
在UserController中添加
@Autowired
private UserAloneRepository userAloneRepository;
@GetMapping("/user/name")
@SuppressWarnings("unchecked")
public Result<User> getUserByName(@RequestParam("name") String name) {
return Result.success(userAloneRepository.findByName(name));
}
jpa的一个好处是可以不用手工去数据库建表,一运行就可以自动建表。
如果要分页和排序
public interface UserPagingAndSortingRepository extends PagingAndSortingRepository<User,Long> {
}
Controller中添加
/**
* 分页和排序
* @return
*/
@SuppressWarnings("unchecked")
@GetMapping("/page")
public Result<Page<User>> getAllUserByPage() {
return Result.success(userPagingAndSortingRepository.findAll(
new PageRequest(0,2,new Sort(new Sort.Order(Sort.Direction.ASC,"name")))
));
}
/**
* 排序
* @return
*/
@SuppressWarnings("unchecked")
@GetMapping("/sort")
public Result<Iterable<User>> getAllUserWithSort() {
return Result.success(userPagingAndSortingRepository.findAll(
new Sort(new Sort.Order(Sort.Direction.ASC,"name"))
));
}
我们来看一下他的总体继承关系结构图
其中SimpleJpaRepository是他们所有接口的实现类,而JpaRepository以上其实都是兼容noSql的接口,而只有JpaRepository以下才是对数据库特有的接口。
而使用的方法也是一样,只需要用一个自定义接口继承即可
public interface UserJpaRespository extends JpaRepository<User,Long> {
}
jpa有自己的根据方法名的查询生成器机制,例如之前的UserAloneRepository中的findByName,findBy是固定前缀,Name是属性名。我们来看几个and、or的查询。
public interface UserAloneRepository extends Repository<User,Long> {
List<User> findByName(String name);
List<User> findByNameAndEmail(String name,String email);
List<User> findByNameOrEmail(String name,String email);
}
Controller中的
@SuppressWarnings("unchecked")
@GetMapping("/and")
public Result<List<User>> getUserByNameAndEmail(@RequestParam("name") String name,@RequestParam("email") String email) {
return Result.success(userAloneRepository.findByNameAndEmail(name,email));
}
@SuppressWarnings("unchecked")
@GetMapping("/or")
public Result<List<User>> getUserByNameOrEmail(@RequestParam("name") String name,@RequestParam("email") String email) {
return Result.success(userAloneRepository.findByNameOrEmail(name,email));
}
我们从打印出来的日志可以看到
Hibernate: select user0_.id as id1_0_, user0_.email as email2_0_, user0_.name as name3_0_ from user user0_ where user0_.name=? and user0_.email=? Hibernate: select user0_.id as id1_0_, user0_.email as email2_0_, user0_.name as name3_0_ from user user0_ where user0_.name=? or user0_.email=?
其实就是where语句后面的and和or.
这种and以及or是可以无限接下去的多条件查询。现在我们来看一下去重和取前几个数据
public interface UserAloneRepository extends Repository<User,Long> {
List<User> findByName(String name);
List<User> findByNameAndEmail(String name,String email);
List<User> findByNameOrEmail(String name,String email);
//去重
List<User> findDistinctByName(String name);
//查找前2个
List<User> findTop2ByName(String name);
}
只要加上Distinct和Top数字就可以了
现在给User实体类增加两个字段
@Entity
@Data
@RequiredArgsConstructor
@AllArgsConstructor
@NoArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NonNull
private String name;
@NonNull
private String email;
private int age;
private boolean sex;
}
启动项目后,数据库会自动给user表增加上该字段
所有的jpa关键字如下
public interface UserAloneRepository extends Repository<User,Long> {
List<User> findByName(String name);
List<User> findByNameAndEmail(String name,String email);
List<User> findByNameOrEmail(String name,String email);
//去重
List<User> findDistinctByName(String name);
//查找前2个
List<User> findTop2ByName(String name);
//查找小于age参数的
List<User> findByAgeBefore(int age);
//查找小于age参数的
List<User> findByAgeLessThan(int age);
//查找小于等于age参数的
List<User> findByAgeLessThanEqual(int age);
//查找大于age参数的
List<User> findByAgeAfter(int age);
//查找大于age参数的
List<User> findByAgeGreaterThan(int age);
//查找大于等于age参数的
List<User> findByAgeGreaterThanEqual(int age);
//查找name为null的
List<User> findByNameIsNull();
//查找name不为null的
List<User> findByNameNotNull();
//查找like name的(此处不带%)
List<User> findByNameLike(String name);
//查找not like name的(此处不带%)
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);
//根据name查找,按照age排序
List<User> findByNameOrderByAgeDesc(String name);
//查找不等于age的
List<User> findByAgeNot(int age);
//根据age查找在ages集合内的
List<User> findByAgeIn(Set<Integer> ages);
//根据age查找不在ages集合内的
List<User> findByAgeNotIn(Set<Integer> ages);
//查找sex为1的
List<User> findBySexTrue();
//查找sex为0的
List<User> findBySexFalse();
//按照email查找,不区分大小写
List<User> findByEmailIgnoreCase(String email);
//按照name查找总数
long countByName(String name);
//按照name删除
//此处是根据查找出来的id一条一条删除的,所以在调用的时候必须加事务
long deleteByName(String name);
//按照name删除
//此处是根据查找出来的id一条一条删除的,所以在调用的时候必须加事务
List<User> removeByName(String name);
}
在删除时必须带上事务
@Transactional
@SuppressWarnings("unchecked")
@GetMapping("/delete")
public Result<String> deleteByName(@RequestParam("name") String name) {
userAloneRepository.deleteByName(name);
return Result.success("删除成功");
}
@Transactional
@SuppressWarnings("unchecked")
@GetMapping("/remove")
public Result<List<User>> removeByName(@RequestParam("name") String name) {
return Result.success(userAloneRepository.removeByName(name));
}
根据打印的日志可以看到其实是按照id来删除的
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_, user0_.sex as sex5_0_ from user user0_ where user0_.name=? Hibernate: delete from user where id=? Hibernate: delete from user where id=?