Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to customize sql statements in java jpa". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Java jpa custom sql statement

This article is just to record myself again and learn the use of jpa, the framework's native parsing method name is more suitable for single table operations, custom sql query can solve all problems, record some custom sql syntax records for subsequent reference.

1. Multi-table association query with condition @ Query (value = "SELECT b FROM QyVideo a JOIN YjQyXx b ON a.qyId = b.id AND a.cameraId =? 1") 2. Clear the table @ Transactional@Modifying@Query (value = "truncate table yj_qy_xx", nativeQuery = true)

Note: when you update, truncate, or delete, you must annotate the method with @ Modifying and @ Transactional to let ORM know that you are writing now.

3. Fuzzy query @ Query ("select p from WhpzxryzsXxPo p where p.ryxm like concat ('%',? 1 recording%') and p.cyyxqq > =? 2") 4. The query result is VO

Contains two entity classes

Query (value = "SELECT new com.kun.aqsczt.vo.FxjzfbVo (u, seventinfo) FROM SSmsInfo u left join SEventInfo seventinfo on u.referId = seventinfo.eventId WHERE (: referType IS NULL OR: referType IS''OR u.referType =: referType) AND (: isSend IS NULL OR: isSend IS''OR u.isSend =: isSend)") 5. Use @ Param to annotate injection parameters

Paging query

Query (value = "SELECT a FROM CEiWorkaccMaybe a" + "WHERE (: psnName IS NULL OR: psnName IS''OR a.psnName LIKE%: psnName%)" + "AND (: commName IS NULL OR: commName IS''OR a.commName LIKE%: commName%)" + "AND (: idCard IS NULL OR: idCard IS'OR a.idCard LIKE%: idCard%)" + "AND (: doctorDateStart IS NULL OR: doctorDateStart IS''OR a.doctorDate > =: doctorDateStart) "+" AND (: doctorDateEnd IS NULL OR: doctorDateEnd IS''OR a.doctorDate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.