SpringBootJPA使用及动态SQL Specification

本文深入探讨JPA命名查询、动态SQL及自定义SQL的使用技巧,涵盖单表与多表查询,详解如何利用Specification构建复杂查询条件,同时提供防SQL注入的方法,适合后端开发人员进阶学习。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

命名查询


关键字方法命名sql where字句
AndfindByNameAndPwdwhere name= ? and pwd =?
orfindByNameOrSexwhere name= ? or sex=?
Is,EqualsfindById,findByIdEqualswhere id= ?
BetweenfindByIdBetweenwhere id between ? and ?
LessThanfindByIdLessThanwhere id < ?
LessThanEqualsfindByIdLessThanEqualswhere id <= ?
GreaterThanfindByIdGreaterThanwhere id > ?
GreaterThanEqualsfindByIdGreaterThanEqualswhere id > = ?
AfterfindByIdAfterwhere id > ?
BeforefindByIdBeforewhere id < ?
IsNullfindByNameIsNullwhere name is null
IsNotNull, NotNullfindByNameNotNullwhere name is not null
LikefindByNameLikewhere name like ?
NotLikefindByNameNotLikewhere name not like ?
StartingWithfindByNameStartingWithwhere name like ‘?%’
EndingWithfindByNameEndingWithwhere name like ‘%?’
ContainingfindByNameContainingwhere name like ‘%?%’
OrderByfindByIdOrderByXDescwhere id=? order by x desc
NotfindByNameNotwhere name <> ?
InfindByIdIn(Collection<?> c)where id not in (?)
NotInfindByIdNotIn(Collection<?> c)where id in (?)
TruefindByAaaTruehere aaa = true
FalsefindByAaaFalsehere aaa = false
IgnoreCasefindByNameIgnoreCasewhere UPPER(name)=UPPER(?)

eg:

//查询第一条记录First
Account findFirstByOrderByCreateAtDesc();

//排序ASC DESC,以下分别按lastname升序,按age降序
User findFirstByOrderByChannelCodeAsc();
User findTopByOrderByChannelCodeDesc();

//带分页,Pageable为分页参数,实现类PageRequest,通过PageRequest.of(...)生成分页对象
Page queryFirst10ByVmCode(String vmCode, Pageable pageable);
Slice findTop3ByVmCode(String vmCode, Pageable pageable);

//带Sort排序,查询前10条
List findFirst10ByVmCode(String vmCode, Sort sort);

  • Sort和Pageable放方法参数列表的最后

动态SQL


单表动态条件查询

  • 前端传来N个字段,随机组合其中几个字段组成SQL进行查询
Page<Node> page;
Specification<Node> cation = (root, query, builder) -> {
        List<Predicate> predicates = new ArrayList<>();

        if (!StringUtils.isEmpty((nodeParam.getCompanyCode()))) {
            predicates.add(builder.equal(root.get("companyCode"), nodeParam.getCompanyCode()));
        }

        if (!StringUtils.isEmpty(nodeParam.getNodeName())) {
            predicates.add(builder.like(root.get("nodeName"), "%" + nodeParam.getNodeName() + "%"));
        }

        if (predicates.size() > 1) {
             return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        } else if (predicates.size() == 1) {
            return predicates.get(0);
        } else {
            return null;
        }
};
page = nodeRepository.findAll(cation, nodeParam.getPageable());

多表动态条件查询

  • 4张表动态SQL,前端传来N个字段,随机组合其中几个字段组成SQL进行查询
Specification<ErrorRender> cation = (root, criteriaQuery, builder) -> {
        List<Predicate> predicates = new ArrayList<>();

        predicates.add(builder.greaterThan(root.get("vmCode"), vmCode));
        predicates.add(builder.equal(root.get("status"), 0).not());
		
		//ErrorRender故障表和Vm表进行连接查询,在ErrorRender必须有一个private Vm vm属性
		//相当于 select * from error_render e left join vm v on e.vm=v.vm
        Join<ErrorRender, Vm> vmJoin = root.join("vm", JoinType.LEFT);

        //机型条件 相当于 v.vmTypeId >= 0
        if (vmTypeId >= 0) {
            predicates.add(builder.equal(vmJoin.get("vmTypeId"), vtId));
        }
		
		// 相当于(left on node n on v.node_id=n.node_id) as tmp left join org o on o.org_id= tmp.org_id
        Join<Vms, Orgas> orgJoin = vmJoin.join("node", JoinType.LEFT).join("org", JoinType.LEFT);

        //带有orgId查询
        Orgas org = orgasRepository.findOne(orgId);
		//相当于 o.hierarchy like org.getHierarchy() + "%"
        predicates.add(builder.like(orgJoin.get("hierarchy"), org.getHierarchy() + "%"));

        if (error != null && error != 0) {
            predicates.add(root.get("error").in(allErrorList));
        } 

        if (predicates.size() > 1) {
            return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        } else if (predicates.size() == 1) {
            return predicates.get(0);
        } else {
            return null;
        }
};

JPA对SQL支持


删除数据(delete)(HQL)

//删除需要添加@Modifying注解,@Transactional可加可不加,加上后,将以repository类中的事务为主
@Modifying
@Query("delete from VmModelUnitRelation v where v.vmModelId=?1")
void deleteByVmModelId(Long vmModelId);

//可在仓库类中定义SQL语句,在@Query中引用
String FIND_VM_MODELID = "delete from VmModelUnitRelation v where v.vmModelId=?1";

@Query(FIND_VM_MODELID)  //FIND_VM_MODELID可继续添加查询条件
void deleteByVmModelId(Long vmModelId);

修改数据(update)(HQL)

//更新需要添加@Modifying注解 命名参数绑定,对参数顺序要求不严格
@Modifying
@Query(value = "update Vm v set v.deviceCode = :deviceCode where c.id = :vmCode")
public void updateDeviceCode(@Param("vmCode") String vmCode, String deviceCode);  

@Modifying
@Query(value = "update Vm v set v.deviceCode = ?2 where c.id = ?1")
public void updateDeviceCode(String vmCode, String deviceCode);

单表查询(HQL)

@Query("select v from Vm v where v.vmCode = ?1 and channelCode = ?2")
User findByVmCodeAndChannelCode(String vmCode, Integer channelCode)

//分页
@Query("select v from Vm v where v.vmCode = ?1 and channelCode = ?2")
User findByVmCodeAndChannelCode(String vmCode, Integer channelCode, Pageable pageable)

多表连接查询并支持分页(原生SQL)

  • 只需要设置nativeQuery为true
@Query(value = "SELECT vm.* FROM vm AS vm LEFT JOIN vm_auailiary AS auailiary ON vm.vm_code != auailiary.vm_code WHERE vm.node_id=:nodeId AND vm.host_type=:hostType",
            countQuery = "SELECT count(vm.vm_code) FROM vm AS vm LEFT JOIN vm_auailiary AS auailiary ON vm.vm_code != auailiary.vm_code WHERE vm.node_id=:nodeId AND vm.host_type=:hostType",
            nativeQuery = true)
Page<Vm> findByNodeIdAndVmCodeNotIn(@Param("nodeId") String nodeId, @Param("hostType")Integer hostType, Pageable pageable);

自定义SQL和防SQL注入


  • 使用原生SQL
@Service
public class JdbcTemplateService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate nameJdbcTemplate;

    /**
     * 防止SQL注入(适用于参数占位符为 ? 的参数语句,如果参数为命名绑定,则使用Map设置参数)
     * @param sql     sql语句
     * @param params  按 ? 顺序添加参数
     * @param clazz   返回List包含对象class
     */
    public <T> List<T> queryList(String sql, @Nullable Object[] params, Class<T> clazz){
        return jdbcTemplate.queryForList(sql, params, clazz);
    }

    /**
     * 防止SQL注入,适用于参数为命名绑定形式(如 vmCode = :vmCode) (推荐)
     * @param sql       sql语句
     * @param paramMap  参数Map,key为绑定的命名参数(:后面的名称,如上为vmCode)
     * @param clazz     返回List包含对象class
     */
    public <T> List<T> queryList(String sql, Map<String, Object> paramMap, Class<T> clazz){
        if(paramMap == null){
            paramMap = new HashMap<>();
        }
        return nameJdbcTemplate.query(sql, paramMap, new BeanPropertyRowMapper<>(clazz));
    }

    /**
     * 查询单个对象
     * @param sql    sql语句
     * @param clazz  对象class
     */
    public <T> T queryOne(String sql, Class<T> clazz){
        return jdbcTemplate.queryForObject(sql, clazz);
    }

    /**
     * @param sql    sql语句
     * @return       List列表中是一个个返回对象,Map的key为字段名,value为对应字段值
     */
    public List<Map<String, Object>> queryList(String sql){
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 查询指定返回对象类型列表(不能防止SQL注入)
     * @param sql    sql语句
     * @param clazz  返回List包含对象class
     */
    public <T> List<T> queryList(String sql, Class<T> clazz){
        return jdbcTemplate.queryForList(sql, clazz);
    }
}

动态SQL超级工具(推荐)

【JPA-SPEC】https://github.com/wenhao/jpa-spec/blob/master/README_CN.md

作者劝语

  • 请务必先使用JPA命名查询
  • 命名查询无法解决请使用JPA的动态SQL
  • 多表联合查询在对性能有较大提升的情况下,可使用JPA的@Query
  • 在动态SQL和JPA的@Query均不好解决的情况下,方可使用jdbc(务必使用防SQL注入方法)
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值