第十五节:SpringBoot使用JPA访问数据库

少年恃险若平地，独倚长剑凌清秋。这篇文章主要讲述第十五节:SpringBoot使用JPA访问数据库相关的知识，希望能为你提供帮助。
pom.xml中添加依赖

< dependency> < groupId> org.springframework.boot< /groupId> < artifactId> spring-boot-starter-data-jpa< /artifactId> < /dependency> < dependency> < groupId> mysql< /groupId> < artifactId> mysql-connector-java< /artifactId> < /dependency>

application.properties 配置

spring.datasource.url=jdbc:mysql://localhost:3306/rumenz_springboot spring.datasource.username=root spring.datasource.password=root1234 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.jpa.hibernate.ddl-auto=update spring.sql.init.mode=always spring.sql.init.schema-locations=classpath:/ddl/user-book.sql spring.sql.init.data-locations=classpath:/ddl/user-book-data.sql

属性值	作用
create	每次加载hibernate时都会删除上一次的生成的表，然后根据你的model类再重新来生成新表，哪怕两次没有任何改变也要这样执行，这就是导致数据库表数据丢失的一个重要原因。
create-drop	每次加载hibernate时根据model类生成表，但是sessionFactory一关闭，表就自动删除。
update	最常用的属性，第一次加载hibernate时根据model类会自动建立起表的结构（前提是先建立好数据库），以后加载hibernate时根据 model类自动更新表结构，即使表结构改变了但表中的行仍然存在不会删除以前的行。要注意的是当部署到服务器后，表结构是不会被马上建立起来的，是要等应用第一次运行起来后才会。
validate	每次加载hibernate时，验证创建数据库表结构，只会和数据库中的表进行比较，不会创建新表，但是会插入新值。

属性值	作用
ALWAYS	始终初始化数据库。
EMBEDDED	仅初始化嵌入式数据库。
NEVER	永远不要初始化数据库。

spring.sql.init.schema-locations 指定建表的sql文件
spring.sql.init.data-locations指定数据sql文件

创建实体类

User.java @Getter @Setter @Builder @AllArgsConstructor @Entity //jpa必填 @DynamicInsert //填充默认值 @DynamicUpdate //填充默认值 @Table(name = "user") //jpa必填 @NoArgsConstructor public class User @Id //jpa必填 @GeneratedValue(strategy = GenerationType.IDENTITY) //jpa必填 private Integer id; private String name; private String domain; @Column(name = "age",columnDefinition = "tinyint default 0") private Integer age;

TABLE：使用一个特定的数据库表格来保存主键。
SEQUENCE：根据底层数据库的序列来生成主键，条件是数据库支持序列。
IDENTITY：主键由数据库自动生成（主要是自动增长型）
AUTO：主键由程序控制。

创建repository

package com.rumenz.lession15.controller.repository; import com.rumenz.lession15.controller.entity.User; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.repository.PagingAndSortingRepository; import org.springframework.stereotype.Repository; import java.util.List; import java.util.Optional; /** * @className: UserRepository * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/14 **/@Repository public interface UserRepository extends PagingAndSortingRepository< User,Integer> Optional< User> findById(Integer id); List< User> findDistinctUserByName(String name); Integer countUserByName(String name); List< User> readDistinctByName(String name); Page< User> findAllByName(String name, Pageable pageable);

interface RumenzRepository extends JpaRepository< Rumenz, Long> List< Rumenz> findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname); // 为查询启用 distinct 标志 List< Rumenz> findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname); List< Rumenz> findPeopleDistinctByLastnameOrFirstname(String lastname, String firstname); // 为单个属性启用忽略大小写 List< Rumenz> findByLastnameIgnoreCase(String lastname); // 为所有属性启用忽略大小写 List< Rumenz> findByLastnameAndFirstnameAllIgnoreCase(String lastname, String firstname); // 为查询启用静态 Order by List< Rumenz> findByLastnameOrderByFirstnameAsc(String lastname); List< Rumenz> findByLastnameOrderByFirstnameDesc(String lastname);

关键字	方法示例	JPQL snippet
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age < = ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age > = ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull, Null	findByAge(Is)Null	… where x.age is null
IsNotNull, NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname < > ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

CrudRepository提供CRUD的功能
PagingAndSortingRepository提供分页和排序功能
JpaRepository提供JPA相关的方法，如刷新持久化数据、批量删除等。

文章图片

service

package com.rumenz.lession15.controller.service; import com.rumenz.lession15.controller.entity.User; import org.springframework.data.domain.Page; import java.util.List; /** * @className: UserService * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/14 **/ public interface UserService Integer save(User user); User get(Integer id); List< User> listByName(String name); Integer countByName(String name); List< User> readDistinctByName(String name); Page< User> listByNamePage(String name, Integer page, Integer pageSize); //实现类package com.rumenz.lession15.controller.service.lmpl; import com.rumenz.lession15.controller.entity.User; import com.rumenz.lession15.controller.repository.UserRepository; import com.rumenz.lession15.controller.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Service; import java.util.List; import java.util.Optional; /** * @className: UserServiceImpl * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/14 **/ @Service public class UserServiceImpl implements UserService @Autowired UserRepository userRepository; @Override public Integer save(User user) User save = userRepository.save(user); return save.getId(); @Override public User get(Integer id) Optional< User> opt = userRepository.findById(id); return opt.isPresent()?opt.get():null; @Override public List< User> listByName(String name) List< User> res = userRepository.findDistinctUserByName(name); return res; @Override public Integer countByName(String name) return userRepository.countUserByName(name); @Override public List< User> readDistinctByName(String name) return userRepository.readDistinctByName(name); @Override public Page< User> listByNamePage(String name, Integer page, Integer pageSize) Sort sort = Sort.by("id").descending(); Pageable pageable= PageRequest.of(page-1, pageSize, sort); Page< User> res = userRepository.findAllByName(name, pageable); return res;

Controller

package com.rumenz.lession15.controller; import com.rumenz.lession15.controller.entity.User; import com.rumenz.lession15.controller.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import java.util.List; /** * @className: RumenzController * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/14 **/ @RestController @RequestMapping("/rumenz") public class RumenzController @Autowired UserService userService; //保存数据 //id=1的数据不存在就添加 //id=1的数据存在就更新 @RequestMapping("/save") public String save() User user=User.builder().id(1).name("入门小站123").domain("https://rumenz.com").build(); Integer save = userService.save(user); return save.toString(); //通过id查询数据 @GetMapping("/get") public User get(@RequestParam("id") Integer id) return userService.get(id); //带条件查询 @GetMapping("/listByName") public List< User> get(@RequestParam("name") String name) return userService.listByName(name); //按条件查询符合条件的数量 @GetMapping("/countByName") public Integer countByName(@RequestParam("name") String name) return userService.countByName(name); //带条件查询 @GetMapping("/readDistinctByName") public List< User> readDistinctByName(@RequestParam("name") String name) return userService.readDistinctByName(name); //分页查询 //带条件查询 @GetMapping("/listByNamePage") public Page< User> listByNamePage(@RequestParam("name") String name, @RequestParam("page") Integer page, @RequestParam("pageSize") Integer pageSize) return userService.listByNamePage(name,page,pageSize);

GitHub:https://github.com/mifunc/springboot/tree/main/lession15
Gitee:https://gitee.com/rumenz/springboot/tree/master/lession15
https://rumenz.com/rumenbiji/springboot-jpa-base.html