jpa学习教程（一）---原生自定义sql的写法

在spring boot中写入jpa。代码如下：

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import ls.smartcity.alipay.entity.jpa.LikeMenuViewEntity;

@Repository
public interface LikeAppMenuViewJpaRepo extends JpaRepository<LikeAppMenuViewEntity, String> {

    @Query(value = "select distinct (u.app_name), u.url, u.params, u.lvl, u.parent, u.app_code from like_app_menu_view u where u.app_name like ?1 and u.region = ?2 and u.enable = ?3 "+
                " and EXISTS(SELECT 1 from like_app_menu_view e where app_name=u.app_name and region=u.region GROUP BY url HAVING MAX(app_code) = u.app_code )", nativeQuery = true)
    List<List<String>> queryFuncName(String name, String region, boolean enable, Pageable pageable);

    @Query(value = "select distinct (u.app_name) from like_app_menu_view u where u.app_name like ?1 and u.region = ?2 and u.enable = ?3 "+
            " and EXISTS(SELECT 1 from like_app_menu_view e where app_name=u.app_name and region=u.region GROUP BY url HAVING MAX(app_code) = u.app_code )", nativeQuery = true)
    List queryList(String name, String region, boolean enable);

  }

 

代码说明

1、创建一个接口，该接口主要是负责调用sql的方法，接口要继承接口JpaRepository。

2、JpaRepository<LikeAppMenuViewEntity, String>。LikeAppMenuViewEntity这个对应表的实体类。String是表的实体类主键的数据类型。

3、 List<List<String>> queryFuncName(String name, String region, boolean enable, Pageable pageable);   接口中调用表的方法名字

4、sql的写法@Query(value ="sql语句",nativeQuery = true)注解的写法。

5、Pageable 这个接口，是用来分页的。org.springframework.data.domain.Pageable

6、参数匹配

①用?号进行匹配，例如?1，代表第一个参数。

例如：List<List<String>> queryFuncName(String name, String region, boolean enable, Pageable pageable);   这个方法

u.app_name like ?1 就是 name

②接口方法List<Map<String,Object>> queryAllSubscribeByPersonRegion(@Param("idno") String idno, @Param("region")String region, @Param("channel")String channel );  这样

注解： @Query(value = "SELECT vv.rownum, vv.app_code as appCode, vv.app_name as appName, vv.tag, vv.tag_note as tagNote," + 
                    "vv.region, vv.icon, vv.idx, vv.url, vv.menu_type as menuType, vv.lvl as level, vv.params, "+
                    " (case when isnull(m.id)<>'0' then 0 else 1 end) flag  from \n"+
                    "(SELECT @rownum \\:= @rownum+ 1 AS rownum, v.* \n " + 
                      " from (SELECT @rownum\\:=0) t, v_subscribe v) vv \n " + 
                       "LEFT JOIN my_subscribe m on(vv.app_code=m.app_code and vv.region=m.region and vv.menu_type = m.menu_type and m.idno= :idno and m.region = :region and m.channel = :channel) where vv.region = :region ORDER BY rownum ",nativeQuery = true)

这种事用【:名称】进行匹配。这种匹配参数的方法更好。

 

以上都是检索的写法。如果是增加、删除、更新的操作，需要加入注解@Modifying，例如下面：

    @Modifying
    @Query(value = "insert into t_sys_org_user(org_id,user_id) values(?1,?2)",nativeQuery = true)
    int addUserToOrg(Long orgId,Long userId);

 

 @Modifying
    @Query(value = "delete from t_sys_org_user where org_id=?1 and user_id=?2",nativeQuery = true)
    int deleteUserFromOrg(Long orgId,Long userId);

 

@Modifying
    @Query(value = "update t_sys_user set status=0 where user_id=?1",nativeQuery = true)
    int updateUserStatus(Long userId);

分别是插入，删除，修改。