Spring Data JPA使用JPQL与原生SQL进行查询的操作

JPQL语言（Java Persistence Query Language）是一种和SQL非常类似的中间性和对象化查询语言，它最终会被编译成针对不同底层数据库的SQL语言，从而屏蔽不同数据库的差异。

JPQL语言通过Query接口封装执行，Query 接口封装了执行数据库查询的相关方法。调用 EntityManager 的 Query、NamedQuery 及 NativeQuery 方法可以获得查询对象，进而可调用Query接口的相关方法来执行查询操作。

JPQL是面向对象进行查询的语言，可以通过自定义的JPQL完成UPDATE和DELETE操作。JPQL不支持使用INSERT。对于UPDATE或DELETE操作，必须使用注解 @Modifying 进行修饰。

package com.pjb.jpauserdemo.dao; import com.pjb.jpauserdemo.entity.UserInfo;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.query.Param;import org.springframework.stereotype.Repository;import java.util.List; /** * 用户信息数据库访问接口 * 使用JPQL语言 * @author pan_junbiao **/@Repositorypublic interface UserJpqlDao extends JpaRepository<UserInfo,Integer>{ /** * 根据用户姓名，查询用户信息 */ @Query('SELECT u FROM UserInfo u WHERE u.userName = ?1') public UserInfo getUserInfoByName(String name); /** * 根据用户姓名，模糊查询用户列表 */ @Query('SELECT u FROM UserInfo u WHERE u.userName like %:name%') public List<UserInfo> getUserListByName(String name); /** * 修改用户姓名 */ @Modifying @Query('UPDATE UserInfo u SET u.userName = :name WHERE u.userId = :id') public int updateUserName(@Param('id')int userId, @Param('name')String userName);}2、使用原生SQL语句进行查询

在使用原生SQL查询时，也使用@Query注解。此时，nativeQuery参数需要设置为true。

package com.pjb.jpauserdemo.dao;import com.pjb.jpauserdemo.entity.UserInfo;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.query.Param;import org.springframework.stereotype.Repository;import org.springframework.transaction.annotation.Transactional;import java.util.List; /** * 用户信息数据库访问接口测试类 * 使用原生SQL语言 * @author pan_junbiao **/@Repositorypublic interface UserSqlDao extends JpaRepository<UserInfo,Integer>{ /** * 根据用户ID，获取用户信息 */ @Query(value = 'SELECT * FROM tb_user WHERE user_id = :id',nativeQuery = true) public UserInfo getUserById(@Param('id')int userId); /** * 根据用户姓名，模糊查询用户列表 */ @Query(value = 'SELECT * FROM tb_user WHERE user_name LIKE %:userName%',nativeQuery = true) public List<UserInfo> getUserListByName(@Param('userName')String userName); /** * 修改用户姓名 */ @Modifying @Query(value = 'UPDATE tb_user SET user_name = :name WHERE user_id = :id',nativeQuery = true) public int updateUserName(@Param('id')int userId, @Param('name')String userName);}

可以看到，@Query与@Modifying这两个注解一起声明，可以定义个性化更新操作。

根据代码来解说：

@Query(value = 'select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, ' + 'lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, ' + 'bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version ' + 'e1.name as creator, bill.status' + 'from bill ' + 'left join lawyer_case on lawyer_case .case_no=bill.case_no ' + 'left join customer on customer.no=bill.customer_no ' + 'left join employee e1 on e1.id_=bill.creator ' + 'where IF (?1!=’’, customer_no=?1, 1=1) ' + 'and IF (?2!=’’, case_no=?2, 1=1) ' + 'and IF (?3!=’’, status=?3, 1=1) ' + 'and IF (?4!=’’, creator’%’,?4,’%’)), 1=1) ' + 'and create_by=?5 ' + 'ORDER BY ?#{#pageable} ', countQuery = 'select count(*) ' + 'from bill ' + 'left join lawyer_case on lawyer_case .case_no=bill.case_no ' + 'left join customer on customer.no=bill.customer_no ' + 'left join employee e1 on e1.id_=bill.creator ' + 'where IF (?1!=’’, customer_no=?1, 1=1) ' + 'and IF (?2!=’’, case_no=?2, 1=1) ' + 'and IF (?3!=’’, status=?3, 1=1) ' + 'and IF (?4!=’’, creator’%’,?4,’%’)), 1=1) ' + 'and create_by=?5 '+ 'ORDER BY ?#{#pageable} ', nativeQuery = true) Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator, String createBy, Pageable pageable);需要注意的方法有以下几点：

1、From 不支持重命名.

2、返回的是一个page<Object[]>，数组中只保存了数据，没有对应的key，只能根据返回数据的顺序，依次注入到DTO中。

3、对于使用分页，需要：“ORDER BY ?#{#pageable}”，可以直接传入一个pageable对象，会自动解析。

4、注意格式问题，很多时候就是换行的时候，没有空格。

5、仔细对应数据库中表字段，很多时候报某个字段找不到，就是因为字段名写错，和数据库中对应不上。

6、这是解决使用微服务，大量的数据都需要远程调用，会降低程序的性能。

7、使用Pageabel作为参数的时候，去进行分页。刚开始的时候，觉得还是一个可行的办法，但是得注意的时候，当需要排序的时候，是无法加入sort字段的。 会一直报错left*。

8、针对7的解决方案，把原生SQL的数据查询和countQuery分成两个查询方法。得到count，然后进行判断，若是等于0，则直接返回空集合；反之，则取获取数据。 需要自己进行分页计算，传入正确的pageNumber和pageSize。 大部分系统都是按照修改时间进行降序排序。 所以，order by可以写死。然后pageNumber和pageSize动态传入。 pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是从1开始，若0，则pageNumber=pageNumber * PageSize； 这样就可以保证数据的正确。

/*** pageInfos: 转换之后的数据。* pageable：传入的pageable.* totalPage: 第一条SQL算好的返回值。* 这样就可以统一的返回各种pageDTO。*/private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {return new PageImpl<>(pageInfos, pageable, totalPage); }

以上为个人经验，希望能给大家一个参考，也希望大家多多支持好吧啦网。