新建
1 三大Java ORM框架之比较
目前Java编程语言开发体系比较流行的对象关系映射(ORM)有Mybatis、Hibernate和Spring Data Jpa。
1.1 Mybatis
MyBatis本是Apache的一个开源项目iBatis，2010年这个项目由 Apache Software Foundation迁移到了Google Code，并且改名为 MyBatis。Mybatis着力于POJO与SQL之间的映射关系，可以进行更为细致的SQL，使用起来非常灵活，初学者上手简单、容易掌握，学习时间成本较低，因而深受开发者青睐，目前在国内市场占有率最高，比较适合互联网应用公司的API场 景。
1.2 Hibernate
Hibernate是一个开放源代码的对象关系映射框架，对JDBC进行 了非常轻量级的对象封装，使得Java程序员可以随心所欲地使用对象 编程思维来操纵数据库，并且对象有自己的生命周期，着力对象与对 象之间的关系，有自己的HQL查询语言，所以数据库移植性很好。Hibernate是完备的ORM框架，是符合JPA规范的。Hibernate有自己的 缓存机制。从上手的角度来说比较难，比较适合企业级的应用系统开发。
1.3 Spring Data JPA
可以理解为JPA规范的再次封装抽象，底层还是使用了Hibernate 的JPA技术实现，引用JPQL(Java Persistence Query Language)查 询语言，属于Spring整个生态体系的一部分。随着Spring Boot和 Spring Cloud在市场上的流行，Spring Data JPA也逐渐进入大家的 视野，它们组成有机的整体，使用起来比较方便，加快了开发的效 率，使开发者不需要关心和配置更多的东西，完全可以背靠在Spring 的完整生态标准实现下自己在数据库操作层面的业务需求。最重要的一点是JPA上手简单，开发效率高，对对象的支持 比较好，又有很大的灵活性，市场的认可度越来越高。
2 JPA介绍及开源实现 2.1 JPA简介JPA是Java Persistence API的简称，中文名为Java持久层API， 是JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实 体对象持久化到数据库中。Sun引入新的JPA ORM规范出于两个原因：其一，简化现有Java EE和Java SE应用开发工作；其二，Sun希望整合ORM技术，实现天下 归一。
JPA包括以下3方面的内容：
(1)一套API标准。在javax.persistence的包下面，用来操作 实体对象，执行CRUD操作，框架在后台替代我们完成所有的事情，开 发者从烦琐的JDBC和SQL代码中解脱出来。 (2)面向对象的查询语言：Java Persistence Query Language(JPQL)。这是持久化操作中很重要的一个方面，通过面向 对象而非面向数据库的查询语言查询数据，避免程序的SQL语句紧密 耦合。
(3)ORM(object/relational metadata)元数据的映射。JPA 支持XML和JDK5.0注解两种元数据的形式，元数据描述对象和表之间 的映射关系，框架据此将实体对象持久化到数据库表中。
2.2 学习Spring Data JPA需要掌握的接口及实现类 2.2.1 七个Repository接口 1) Repository(org.springframework.data.repository) 该接口的源码如下，继承该接口的接口或类都能被视作数据库访问层对象Dao ，类似于Mybatis中的MaperInterface接口@Indexed
public interface Repository<T, ID> {
}
import java.util.Optional;@NoRepositoryBeanpublic interface CrudRepository<T, ID> extends Repository<T, ID> { //保存一条记录到数据库,入参为与表对应的实体类对象 S save(S var1); //保存所有记录到数据库，入参为一个迭代器 Iterable saveAll(Iterable var1); //根据Id查找，返回Optional类型对象 OptionalfindById(ID var1); //判断数据库表中是否存在ID为指定值的记录 boolean existsById(ID var1); //查询数据库表中所有记录 IterablefindAll(); //查询数据库表中指定ID集合的记录 IterablefindAllById(Iterable var1); //查询数据库表中所有记录 long count(); //根据主键删除 void deleteById(ID var1); //删除数据库表中匹配上映射对象的记录 void delete(T var1); //删除数据库表中匹配上指定数据库映射对象集合的所有记录 void deleteAll(Iterable extends T> var1); //删除表中所有记录 void deleteAll();}3) PagingAndSortingRepository(org.springframework.data.repository)
分页查询接口，该接口继承了CrudRepository接口，并新增了根据分页查询所有记录的抽象方法@NoRepositoryBeanpublic interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> { //排序查询 IterablefindAll(Sort var1); //分页查询 PagefindAll(Pageable var1);}public interface QueryByExampleExecutor<T> { //查询符合条件的第一条记录,var1实例中的属性值会放在sql语句中的where后面过滤 <S extends T> Optional<S> findOne(Example<S> var1); //查询符合条件的所有记录 <S extends T> Iterable<S> findAll(Example<S> var1); //查询符合条件的所有记录，并按指定排序返回 <S extends T> Iterable<S> findAll(Example<S> var1, Sort var2); //分页查询符合条件的所有记录 <S extends T> Page<S> findAll(Example<S> var1, Pageable var2); //查询符合条件的记录条数 <S extends T> long count(Example<S> var1); //判断是否存在符合条件记录 <S extends T> boolean exists(Example<S> var1);}5) JpaRepository(org.springframework.data.jpa.repository)
该接口同时继承了PagingAndSortingRepository和QueryByExampleExecutor两个接口@NoRepositoryBeanpublic interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> { //查询所有记录 ListfindAll(); //查询所有记录，并排序返回 ListfindAll(Sort var1); //查询指定主键集合的结果集 ListfindAllById(Iterable var1); //批量保存 List saveAll(Iterable var1); //刷新，相当于提交操作 void flush(); //保存并刷新 S saveAndFlush(S var1); //批量删除 void deleteInBatch(Iterable var1); //删除所有 void deleteAllInBatch(); //根据主键获取一条记录 T getOne(ID var1); //插叙符合条件所有记录 List findAll(Example var1); //查询符合条件记录，并按指定顺序返回 List findAll(Example var1, Sort var2);}6) JpaSpecificationExecutor(org.springframework.data.jpa.repository)
//这个接口提供根据指定查询条件查找结果集
public interface JpaSpecificationExecutor<T> { //查询符合条件的一条记录，入参可为空 OptionalfindOne(@Nullable Specification var1); //查询符合指定条件所有数据，入参可为空 ListfindAll(@Nullable Specification var1); //分页查询符合指定条件所有数据，入参可为空 PagefindAll(@Nullable Specification var1, Pageable var2); //查询符合指定条件所有数据，并按指定排序后返回，入参可为空 ListfindAll(@Nullable Specification var1, Sort var2); //查询符合指定条件的记录数，入参可为空 long count(@Nullable Specification var1);}这个接口提供根据语言查询的方法
public interface QuerydslPredicateExecutor<T> { OptionalfindOne(Predicate var1); IterablefindAll(Predicate var1); IterablefindAll(Predicate var1, Sort var2); IterablefindAll(Predicate var1, OrderSpecifier... var2); IterablefindAll(OrderSpecifier... var1); PagefindAll(Predicate var1, Pageable var2); long count(Predicate var1); boolean exists(Predicate var1);}2.2 两个实现类
1) SimpleJpaRepository (org.springframework.data.jpa.repository.support) 这个实现类实现了2.1中第5和第6个接口，由于第5个接口JpaRepository继承了PagingAndSortingRepository和QueryByExampleExecutor两个接口，因此该实现类也就实现了2.1中第1-6个接口中的所有抽象方法。由于该类源码较多，此处不再贴上，感兴趣的读者可自行在IDEA中通过搜索SimpleJpaRepository类点击进入查看源码。 2) QueryDslJpaRepository(org.springframework.data.jpa.repository.suppor) 该类继承了 SimpleJpaRepository 实现类并实现了 QuerydslPredicateExecutor 接口 以上7个接口和2个实现的关系图如下：
EntityManager (javax.persistence)
EntityManagerImpl (org.hibernate.jpa.internal)
3 Spring Data JPA操作Mysql数据库体验
以Spring Boot和Spring Jdbc为技术场景，选用MySQL数据库来做演示 本项目在SpringBoot之路(一)：构建你的第一个Restful Web Service项目的基础之上进行 Mysql数据库5.6版本，安装在开发机器上 3.1 添加mysql和spring-data-jpa的依赖<dependency> <groupId>mysqlgroupId> <artifactId>mysql-connector-javaartifactId> <scope>runtimescope> dependency> <dependency> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-starter-data-jpaartifactId>dependency>3.2 Mysql数据库中建表使用Navicat数据库连接客户端连接Mysql数据库服务器，使用root用户及登录密码，连接成功后，使用mysql schema；然后新建查询窗口，执行如下sql脚本新建user_info表和roles两个表1)在第1个查询窗口中执行创建用户表的sql脚本use mysql;create table user_info( user_name varchar(30) not null comment '用户名', user_id bigint(6) auto_increment comment '用户ID', user_sex char(1) comment '用户性别', user_role varchar(30) comment '用户角色', tel_num bigint(11) not null comment '用户手机号码', email varchar(50) comment '用户邮箱地址', reg_date date comment '用户注册日期', birth_day date not null comment '出生日期', created_by varchar(50) default 'system' comment '创建人', created_time datetime default now() comment '创建时间' , last_updated_by varchar(50) comment '最后更新人', last_updated_time datetime comment '最后更新时间', primary key (user_id))engine=InnoDB default CHARSET=utf8;alter table user_info add (user_name_cn varchar(30),password varchar(50));create unique index user_info_user_name_uidx on user_info(user_name);create unique index idx_user_name_cn on user_info(user_name_cn);2)新建第2个查询窗口，执行创建用户角色表的sql脚本
use mysql;create table roles( role_id varchar(30) comment '角色ID', role_name varchar(50) not null comment '角色名', created_by varchar(50) default 'system', created_time datetime default now(), primary key (role_id))engine=InnoDB default CHARSET=utf8;3) 新建第3个查询窗口执行添加用户角色数据
insert into roles(role_id, role_name,created_by)values('Admin','管理员','x_heshengfu');insert into roles(role_id, role_name,created_by)values('SystemAdmin','系统管理员','x_heshengfu');insert into roles(role_id, role_name,created_by)values('Developer','开发人员','x_heshengfu');insert into roles(role_id, role_name,created_by)values('Guest','普通客户','x_heshengfu');commit;4)新建第5个查询窗口添加用户数据
Insert into user_info(user_name, user_sex, user_role, tel_num, email, reg_date, birth_day,created_time)values('ZhangSan','M','Developer',13100001001,'zhangsan@163.com','2018-10-10','1990-05-18','2020-03-13 23:45:35');Insert into user_info(user_name, user_sex, user_role, tel_num, email, reg_date, birth_day,created_time)values('LiSi','M','Admin',13100001002,'lisi@163.com','2018-06-10','1989-07-12','2020-03-13 23:48:30');Insert into user_info(user_name, user_sex, user_role, tel_num, email, reg_date, birth_day,created_time)values('WangHua','F','Developer',13100001003,'wanghua@163.com','2018-09-10','1992-06-01','2020-03-13 23:51:18');commit;#用户密码使用md5加密update user_info set user_name_cn='张三',password=md5('zs1234!'), last_updated_by='admin',last_updated_time=now()where user_name='ZhangSan';update user_info set user_name_cn='李四',password=md5('ls1234!'), last_updated_by='admin',last_updated_time=now()where user_name='LiSi';update user_info set user_name_cn='王花',password=md5('wh1234!'), last_updated_by='admin',last_updated_time=now()where user_name='WangHua';insert user_info(user_name,password,user_name_cn,user_sex, user_role, tel_num, email, reg_date, birth_day, last_updated_by, last_updated_time)values('x_heshengfu',md5('hsf1234!'),'贺盛福','M','SystemAdmin',18600001234,'x_heshengfu@phfund.com.cn','2020-03-10','1990-12-18','x_heshengfu',now());insert user_info(user_name,password,user_name_cn,user_sex,user_role, tel_num, email, reg_date, birth_day, last_updated_by, last_updated_time)values('admin',md5('adm1234#'),'管理员','M','Admin',18600001235,'admin@phfund.com.cn','2020-03-10','1990-01-01','x_heshengfu',now());insert user_info(user_name,password,user_name_cn,user_sex, user_role, tel_num, email, reg_date, birth_day, last_updated_by, last_updated_time)values('userA',md5('usr1234#'),'用户A','M','Guest',18600001234,'userA@phfund.com.cn','2020-03-10','1990-09-09','x_heshengfu',now());以上sql也可以在IntelliJ IDEA中登录数据源mysql客户端后一起执行插入数据后，Navicat执行查询用户表sql效果如如下：
3.3 配置mysql数据库连接池和jpa启动参数在application.properties和application-dev.properties两个文件中文件中配置如下启动参数 #application.propertiesserver.port=8088server.servlet.context-path=/apiBoot#项目启动时程序会根据@Entity注解装饰的属性自动建表，#如果在数据库中提前建好了表需要将下面的属性设置为none,否则项目启动时会有警告信息spring.jpa.hibernate.ddl-auto=none#日志中显示sql语句spring.jpa.show-sql=true#数据库方言，必配项，否则启动报错spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect#激活dev环境spring.profiles.active=dev#application-dev.properties#spring.datasource.url属性值中必须配置serverTimezone，否则启动报错spring.datasource.url=jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf8&autoReconnect=true&useSSL=false&allowMultiQueries=truespring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.username=rootspring.datasource.password=password12343.4 新建与user_info表对应的pojo类UserInfo实体类 @Entity(name="user_info")@NoArgsConstructorpublic class UserInfo implements Serializable { @Id @Column(name = "user_id") @GeneratedValue(strategy = GenerationType.AUTO) @Getter @Setter private Long userId; @Column(name="user_name") @Getter @Setter private String userName; @Getter @Setter private String password; @Column(name="user_name_cn") @Getter @Setter private String userNameCn; @Column(name="userSex") @Getter @Setter private String userSex; @Column(name="user_role") @Getter @Setter private String userRole; @Column(name="tel_Num") @Getter @Setter private Long telNum; @Getter @Setter private String email; @Column(name="reg_date") @Getter @Setter private String regDate; @Column(name="birth_day") @Getter @Setter private String birthDay; @Column(name="created_by") @Getter @Setter private String createdBy; @Column(name="created_time") @Getter @Setter private String createdTime; @Column(name="last_updated_by") @Getter @Setter private String lastUpdatedBy; @Column(name="last_updated_time") @Getter @Setter private String lastUpdatedTime;}实体类中所有setter和getter方法均使用lombok注解实现；
当实体类名与表名不一致时，在@Entity注解中的name属性中标注表名(本例中user_info表带有下划线，与UserInfo实体类名不一致，因而需要用name属性关联)3.5 新建UserRepository接口并继承PagingAndSortingRepositorypublic interface UserRepository extends PagingAndSortingRepository { UserInfo findUserInfoByUserName(String userName); UserInfo findUserInfoByTelNumEquals(Long telNum); UserInfo findUserInfoByEmail(String email); List findUserInfosByUserNameLike(String userName); List findUserInfosByUserNameCnLike(String userNameCn);}spring dada jpa框架中有个根据方法名的查询生成器机制，对于在存储库的实体上构建约束查询很有用,该机制的方法前缀有find...By...,count...By...和get...By...。可以通过在继承自jpa框架自带的接口中自定义带有查询关键字并与实体类属性名关联的抽象方法，让读者不用手写快映射sql就能实现数据库访问层操作，这些关键字和属性最终都会被jpa框架中实现类转换成JPQL表达式。spring-data-jpa中的关键字列表如下：| 关键字 | 示例 | JPQL表达式 |
| 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 | findByAgeIsNull | … 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 (参数后面通常带个%符号) |
| EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1(参数前面带%符号) |
| Containing | findByFirstnameContaining | … where x.firstname like ?1(参数前后均带有%符号) |
| 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) |
3.6 新建UserService 接口及其实现类public interface UserService { UserInfo save(UserInfo userInfo); List saveAll(List users); UserInfo queryById(Long userId); UserInfo queryByUserName(String userName); UserInfo findByTelNum(Long telNum); UserInfo findByEmail(String email); List queryByLikeUserName(String userName); List queryLikeByUserNameCn(String userNameCn);}在实现类中注入UserRepository接口完成数据库访问操作@Service@Slf4jpublic class UserServiceImpl implements UserService { @Autowired private UserRepository userRepository; @Transactional @Override public UserInfo save(UserInfo userInfo) { try { //数据入库前对密码进行加密 String password = EncryptionUtil.md5Encrypt(userInfo.getPassword()); userInfo.setPassword(password); } catch (Exception e) { log.error("encrypt password failed",e); return null; } return userRepository.save(userInfo); } @Transactional @Override public List saveAll(List users) { try{ for(UserInfo userInfo: users){ String password = EncryptionUtil.md5Encrypt(userInfo.getPassword()); userInfo.setPassword(password); } }catch (Exception e){ log.error("encrypt password failed",e); return null; } return (List) userRepository.saveAll(users); } @Override public UserInfo queryById(Long userId) { return userRepository.findById(userId).get(); } @Override public UserInfo queryByUserName(String userName) { return userRepository.findUserInfoByUserName(userName); } @Override public UserInfo findByTelNum(Long telNum) { return userRepository.findUserInfoByTelNumEquals(telNum); } @Override public UserInfo findByEmail(String email) { return userRepository.findUserInfoByEmail(email); } @Override public List queryByLikeUserName(String userName) { return userRepository.findUserInfosByUserNameLike(userName); } @Override public List queryLikeByUserNameCn(String userNameCn) { return userRepository.findUserInfosByUserNameCnLike(userNameCn); }}注意：数据库的写操作时需要在方法上添加声明式事物注解由于涉及到用户的密码等敏感信息的保存时需要加密，因此需要在pom.xml中加入加密工具类
的依赖项;同时为了引入fastJson序列化对象，引入了阿里的FastJson工具依赖 commons-codec commons-codec 1.10 com.alibaba fastjson 1.2.56 EncryptionUtil类代码如下public class EncryptionUtil { /** * md5加密，不带秘钥 * @param clear 明文 * @return * @throws Exception */ public static String md5Encrypt(String clear)throws Exception{ //转字符串成密文 String cipher = DigestUtils.md5Hex(clear); return cipher; }}3.7 新建控制器类UserInfoController@RestController@RequestMapping("/user")@Slf4jpublic class UserInfoController { @Autowired private UserService userInfoService; //保存单个用户信息。 @PostMapping("/info") public ServiceResponse saveUserInfo(@RequestBody UserInfo userInfo){ log.info("userInfo={}", JSON.toJSON(userInfo)); ServiceResponse response = new ServiceResponse<>(); UserInfo userInfo1 = userInfoService.save(userInfo); response.setData(userInfo1); return response; } //保存多个用户信息。 @PostMapping("/infos") public ServiceResponse> saveAllUserInfos(@RequestBody List userInfos){ log.info("userInfos={}",JSON.toJSON(userInfos)); ServiceResponse> response = new ServiceResponse<>(); List userInfoList = userInfoService.saveAll(userInfos); response.setData(userInfoList); return response; } //通过用户名查找单个用户信息。 @GetMapping("/info") public ServiceResponse queryByUserName(@RequestParam("userName") String userName){ log.info("userName={}",userName); ServiceResponse response = new ServiceResponse<>(); UserInfo userInfo = userInfoService.queryByUserName(userName); response.setData(userInfo); return response; } //通过用户ID查找单个用户信息。。 @GetMapping(value = "/findById") public ServiceResponse queryByUserId(@RequestParam("userId") Long userId){ log.info("userId={}",userId); ServiceResponse response = new ServiceResponse<>(); UserInfo userInfo = userInfoService.queryById(userId); response.setData(userInfo); return response; } 通过用户手机号码查找用户信息。 @GetMapping("/findByTelNum") public ServiceResponse findByTelNum(@RequestParam("telNum") Long telNum){ log.info("telNum={}",telNum); ServiceResponse response = new ServiceResponse<>(); UserInfo userInfo = userInfoService.findByTelNum(telNum); response.setData(userInfo); return response; } //通过用户邮箱查找用户。 @GetMapping("/findByEmail") public ServiceResponse findByEmail(@RequestParam("email") String email){ ServiceResponse response = new ServiceResponse<>(); log.info("email={}",email); UserInfo userInfo = userInfoService.findByEmail(email); response.setData(userInfo); return response; } //根据用户账号模糊查找多个用户。 @GetMapping("/findByUserNameLike") public ServiceResponse> findByUserNameLike(@RequestParam("userName") String userName){ ServiceResponse> response = new ServiceResponse<>(); log.info("userName={}",userName); List userInfoList = userInfoService.queryByLikeUserName(userName); response.setData(userInfoList); return response; } //通过用户中文名查找多个用户。 @GetMapping("/findByUserNameCnLike") public ServiceResponse> findByUserNameCnLike(@RequestParam("userNameCn") String userNameCn){ log.info("userNameCn={}",userNameCn); ServiceResponse> response = new ServiceResponse<>(); List userInfoList=userInfoService.queryLikeByUserNameCn(userNameCn); response.setData(userInfoList); return response; }}以上控制器中包含了大部分的添加和查询用户接口4 测试 启动项目后，使用postman插件或者app测试项目启动成功后控制台打印信息如下： . ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.1.4.RELEASE)2020-04-29 01:03:30.125 INFO 3748 --- [ main] c.example.bootdemo.BootDemoApplication : Starting BootDemoApplication on heshengfu1211 with PID 3748 (D:\SpringBootProject\boot-demo\target\classes started by HP in D:\SpringBootProject\boot-demo)2020-04-29 01:03:30.128 INFO 3748 --- [ main] c.example.bootdemo.BootDemoApplication : The following profiles are active: dev2020-04-29 01:03:31.025 INFO 3748 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.2020-04-29 01:03:31.129 INFO 3748 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 92ms. Found 1 repository interfaces.2020-04-29 01:03:31.571 INFO 3748 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$97d92722] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)2020-04-29 01:03:32.104 INFO 3748 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8088 (http)2020-04-29 01:03:32.132 INFO 3748 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]2020-04-29 01:03:32.132 INFO 3748 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.17]2020-04-29 01:03:32.141 INFO 3748 --- [ main] o.a.catalina.core.AprLifecycleListener : Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.5].2020-04-29 01:03:32.141 INFO 3748 --- [ main] o.a.catalina.core.AprLifecycleListener : APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].2020-04-29 01:03:32.141 INFO 3748 --- [ main] o.a.catalina.core.AprLifecycleListener : APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true]2020-04-29 01:03:32.278 INFO 3748 --- [ main] o.a.catalina.core.AprLifecycleListener : OpenSSL successfully initialized [OpenSSL 1.1.1a 20 Nov 2018]2020-04-29 01:03:32.485 INFO 3748 --- [ main] o.a.c.c.C.[.[localhost].[/apiBoot] : Initializing Spring embedded WebApplicationContext2020-04-29 01:03:32.485 INFO 3748 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 2301 ms2020-04-29 01:03:32.681 INFO 3748 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...2020-04-29 01:03:33.019 INFO 3748 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.2020-04-29 01:03:33.197 INFO 3748 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [ name: default ...]2020-04-29 01:03:33.320 INFO 3748 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {5.3.9.Final}2020-04-29 01:03:33.322 INFO 3748 --- [ main] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found2020-04-29 01:03:33.561 INFO 3748 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}2020-04-29 01:03:33.849 INFO 3748 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL57Dialect2020-04-29 01:03:34.651 INFO 3748 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'2020-04-29 01:03:35.475 INFO 3748 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'2020-04-29 01:03:35.525 WARN 3748 --- [ main] aWebConfiguration$JpaWebMvcConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning2020-04-29 01:03:35.825 INFO 3748 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8088 (http) with context path '/apiBoot'2020-04-29 01:03:35.827 INFO 3748 --- [ main] c.example.bootdemo.BootDemoApplication : Started BootDemoApplication in 6.322 seconds (JVM running for 11.047)4.1 测试根据用户ID查询用户查询方法选择GET，右侧输入栏输入URL：http://localhost:8088/apiBoot/user/findById?userId=1点击蓝色“send”会在下面的Body栏以json的格式中显示查询结果
4.2 测试添加单个用户信息
查询方法选择POST，右侧输入栏输入URL：http://localhost:8088/apiBoot/user/info请求体参数类型选择raw，数据格式选择JSON输入如下请求体参数，然后点击右侧的send按钮{ "userName":"GuanYu", "password":"gy1234!", "userNameCn":"关羽", "userSex":"M", "userRole":"Admin", "telNum":15200001306, "email":"guanyu@163.com", "regDate":"2018-01-01", "birthDay":"1968-10-01", "createdBy":"system", "createdTime":"2020-04-29 10:35:00"}
接口响应体信息如下：
{ "status": 200, "message": "ok", "data": { "userId": 9, "userName": "GuanYu", "password": "b3ea37a9f80a602792303728617ac533", "userNameCn": "关羽", "userSex": "M", "userRole": "Admin", "telNum": 15200001306, "email": "guanyu@163.com", "regDate": "2018-01-01", "birthDay": "1968-10-01", "createdBy": "system", "createdTime": "2020-04-29 10:35:00", "lastUpdatedBy": null, "lastUpdatedTime": null }}4.3 测试批量添加在postman中新建一个请求窗口，请求类型选择POST,URL: http://localhost:8088/apiBoot/user/infos参数类型同样选择raw，在请求体参数输入框中输入下面的JSON对象数组
[{ "userName":"LiuBei", "password":"lb1234!", "userNameCn":"刘备", "userSex":"M", "userRole":"Admin", "telNum":15200001307, "email":"liubei@163.com", "regDate":"2017-10-09", "birthDay":"1966-12-01" },{ "userName":"ZhangFei", "password":"zf1234!", "userNameCn":"张飞", "userSex":"M", "userRole":"Admin", "telNum":15200001308, "email":"zhangfei@163.com", "regDate":"2018-03-05", "birthDay":"1969-08-01" },{ "userName":"ZhuGeLiang", "password":"zgl1234!", "userNameCn":"诸葛亮", "userSex":"M", "userRole":"Admin", "telNum":15200001309, "email":"zhugeliang@163.com", "regDate":"2019-03-06", "birthDay":"1972-10-08" },{ "userName":"CaoCao", "password":"cc1234!", "userNameCn":"曹操", "userSex":"M", "userRole":"SystemAdmin", "telNum":15200001310, "email":"caocao@163.com", "regDate":"2016-05-10", "birthDay":"1964-10-01" }]返回结果如下：
{ "status": 200, "message": "ok", "data": [ { "userId": 20, "userName": "LiuBei", "password": "0395c8114c4d2c5b64ca41da09c151d3", "userNameCn": "刘备", "userSex": "M", "userRole": "Admin", "telNum": 15200001307, "email": "liubei@163.com", "regDate": "2017-10-09", "birthDay": "1966-12-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 21, "userName": "ZhangFei", "password": "956c5c8200854fb09c24ec10144747d0", "userNameCn": "张飞", "userSex": "M", "userRole": "Admin", "telNum": 15200001308, "email": "zhangfei@163.com", "regDate": "2018-03-05", "birthDay": "1969-08-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 22, "userName": "ZhuGeLiang", "password": "cea79d52d2117875eb9d377bfe68f65e", "userNameCn": "诸葛亮", "userSex": "M", "userRole": "Admin", "telNum": 15200001309, "email": "zhugeliang@163.com", "regDate": "2019-03-06", "birthDay": "1972-10-08", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 23, "userName": "CaoCao", "password": "7482de119bcb4cd42dff30ae44cf66e1", "userNameCn": "曹操", "userSex": "M", "userRole": "SystemAdmin", "telNum": 15200001310, "email": "caocao@163.com", "regDate": "2016-05-10", "birthDay": "1964-10-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null } ]插入数据时可以在控制台中看到下面这些sql执行信息
ibernate: select next_val as id_val from hibernate_sequence for updateHibernate: update hibernate_sequence set next_val= ? where next_val=?Hibernate: select next_val as id_val from hibernate_sequence for updateHibernate: update hibernate_sequence set next_val= ? where next_val=?以上信息说明插入user_id列时使用了名称为hibernate_sequence的自增序列，每次向数据库插入一条记录后都会更新序列为了方便后面的分页查询，可使用批量插入多添加几条数据5 分页与排序查询5.1 分页与排序查询源码解读在PagingAndSortingRepository接口中下面两个方法专门用于排序和分页查询
/** * Returns all entities sorted by the given options. * * @param sort * @return all entities sorted by the given options */ Iterable findAll(Sort sort); /** * Returns a {@link Page} of entities meeting the paging restriction provided in the {@code Pageable} object. * * @param pageable * @return a page of entities */ Page findAll(Pageable pageable);用于排序方法入参 Sort实例的构造方法有以下几个
/** 所有属性均按指定顺序排序*Direction为枚举常量，分为ASC和DESC两个枚举*properties为任意长度对应列名的属性数组*/public Sort(Direction direction, String... properties){....}//同上，properties为列表public Sort(Direction direction, List properties){...}//properties对应列均按默认升序排列public static Sort by(String... properties){....}//在Order中定义各列排序方式public static Sort by(List orders){...}//同上，orders为任意长度数组public static Sort by(Order... orders){...}用于分页方法的入参Pageable实现类PageRequest实例的主要构造方法如下：
//只分页，page: 第page页；size: 每页大小public static PageRequest of(int page, int size){...}//分页并排序，page和size同上；Sort排序方式public static PageRequest of(int page, int size, Sort sort){...}//分页并按指定属性的排序方式返回public static PageRequest of(int page, int size, Direction direction, String... properties){...}5.2 使用示例1) UserService接口中新加排序查找和分页查找的抽象方法
List findAllOrderBy(String propName,String order);Page findAllByPage(int page, int pageSize);2)在UserService接口的实现类UserServiceImpl实现抽象方法
@Override public List findAllOrderBy(String propName, String order) { Sort.Direction direction; if("DESC".equalsIgnoreCase(order)){ direction = Sort.Direction.DESC; }else{ direction = Sort.Direction.ASC; } Sort sort = new Sort(direction,propName); return (List) userRepository.findAll(sort); } @Override public Page findAllByPage(int page, int pageSize) { PageRequest pageRequest = PageRequest.of(page,pageSize); return userRepository.findAll(pageRequest); }3) 在UserController类中添加排序查找所有用户和分页查找所有的路由方法@GetMapping("findAll/orderBy") public ServiceResponse> findAllOrderBy(@RequestParam("propName") String propName,String order){ log.info("property={},order={}",propName,order); if(StringUtils.isEmpty(propName)){ propName = "userId"; } if(StringUtils.isEmpty(order)){ order="ASC"; } ServiceResponse> response = new ServiceResponse<>(); List userInfoList = userInfoService.findAllOrderBy(propName,order); response.setData(userInfoList); return response; } @GetMapping("findAll/byPage/{page}/{pageSize}") public ServiceResponse> findAllByPage(@PathVariable("page") int page, @PathVariable("pageSize") int pageSize){ if(page==0) page=1; if(pageSize==0) pageSize=10; if(pageSize>500) pageSize=500; log.info("page={},pageSize={}",page,pageSize); ServiceResponse> response = new ServiceResponse<>(); Page pageInfo = userInfoService.findAllByPage(page,pageSize); response.setData(pageInfo); return response; }5.3 排序和分页查找测试重启项目后，在postman中调用两个接口测试即可如下面按用户的birthDay属性升序排序查询所有的部分响应信息URL: http://localhost:8088/apiBoot/user/findAll/orderBy?propName=birthDay&order=ASC请求类型：GET响应信息前3条{ "status": 200, "message": "ok", "data": [ { "userId": 23, "userName": "CaoCao", "password": "7482de119bcb4cd42dff30ae44cf66e1", "userNameCn": "曹操", "userSex": "M", "userRole": "SystemAdmin", "telNum": 15200001310, "email": "caocao@163.com", "regDate": "2016-05-10", "birthDay": "1964-10-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 27, "userName": "CaoRen", "password": "526bb98a916bd78c04eb7152c1639b33", "userNameCn": "曹仁", "userSex": "M", "userRole": "Admin", "telNum": 15200001314, "email": "caoren@163.com", "regDate": "2016-05-12", "birthDay": "1965-04-03", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 20, "userName": "LiuBei", "password": "0395c8114c4d2c5b64ca41da09c151d3", "userNameCn": "刘备", "userSex": "M", "userRole": "Admin", "telNum": 15200001307, "email": "liubei@163.com", "regDate": "2017-10-09", "birthDay": "1966-12-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }]} 分页查询URL: http://localhost:8088/apiBoot/user/findAll/byPage/1/10请求类型: GET响应信息如下：{ "status": 200, "message": "ok", "data": { "content": [ { "userId": 21, "userName": "ZhangFei", "password": "956c5c8200854fb09c24ec10144747d0", "userNameCn": "张飞", "userSex": "M", "userRole": "Admin", "telNum": 15200001308, "email": "zhangfei@163.com", "regDate": "2018-03-05", "birthDay": "1969-08-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 22, "userName": "ZhuGeLiang", "password": "cea79d52d2117875eb9d377bfe68f65e", "userNameCn": "诸葛亮", "userSex": "M", "userRole": "Admin", "telNum": 15200001309, "email": "zhugeliang@163.com", "regDate": "2019-03-06", "birthDay": "1972-10-08", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 23, "userName": "CaoCao", "password": "7482de119bcb4cd42dff30ae44cf66e1", "userNameCn": "曹操", "userSex": "M", "userRole": "SystemAdmin", "telNum": 15200001310, "email": "caocao@163.com", "regDate": "2016-05-10", "birthDay": "1964-10-01", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 24, "userName": "SunSahngXiang", "password": "ce5940b19c484d77e580f1b0a0f80b24", "userNameCn": "孙赏香", "userSex": "F", "userRole": "Developer", "telNum": 15200001311, "email": "sunshangxiang@163.com", "regDate": "2018-12-10", "birthDay": "1978-06-08", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 25, "userName": "ZhouYu", "password": "8b9e0e71284ee5110b98ea9f3ecef61d", "userNameCn": "周瑜", "userSex": "M", "userRole": "Developer", "telNum": 15200001312, "email": "zhouyu@163.com", "regDate": "2018-04-05", "birthDay": "1972-08-10", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 26, "userName": "SunQuan", "password": "b1431950094adca92c5c56282cd304cc", "userNameCn": "孙权", "userSex": "M", "userRole": "Admin", "telNum": 15200001313, "email": "sunquan@163.com", "regDate": "2019-07-06", "birthDay": "1975-10-08", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 27, "userName": "CaoRen", "password": "526bb98a916bd78c04eb7152c1639b33", "userNameCn": "曹仁", "userSex": "M", "userRole": "Admin", "telNum": 15200001314, "email": "caoren@163.com", "regDate": "2016-05-12", "birthDay": "1965-04-03", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null }, { "userId": 28, "userName": "DianWei", "password": "7e27436cc8f4b850d24e4547867d80a5", "userNameCn": "典韦", "userSex": "M", "userRole": "Developer", "telNum": 15200001315, "email": "dianwei@163.com", "regDate": "2016-06-10", "birthDay": "1968-04-08", "createdBy": null, "createdTime": null, "lastUpdatedBy": null, "lastUpdatedTime": null } ], "pageable": { "sort": { "sorted": false, "unsorted": true, "empty": true }, "offset": 10, "pageSize": 10, "pageNumber": 1, "unpaged": false, "paged": true }, "totalElements": 18, "last": true, "totalPages": 2, "number": 1, "size": 10, "sort": { "sorted": false, "unsorted": true, "empty": true }, "numberOfElements": 8, "first": false, "empty": false }按指定列名排序的分页查找读者可自行使用下面这个构造方法实现public static PageRequest of(int page, int size, Sort sort){...}6 小结1)本文主要介绍了spring-boot-web项目中整合spring-data-jpa的访问数据库的用法，着重介绍了自定义Repository继承自CrudRepository和PagingAndSortingRepository时操作数据库的具体用法；2)用spring-data-jpa访问数据库具有简单易用，可通过继承JPA接口使用现成的接口方法及按关键字查询的方法，具有无需手写sql等优势；3)文末介绍了大多数业务场景中需要用到的排序查找和分页查找的具体用法，spring-data-jpa更高级的用法笔者会继续撰文告诉读者具体如何使用---------END---------7 参考文档张振华著<>spring-data-jpa官方文档：https://docs.spring.io/spring-data/jpa/docs/2.1.17.RELEASE/reference/html/推荐阅读SpringBoot之路(一)：构建你的第一个Restful Web Service
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
