吴裕雄天生自然Spring Boot@Query和@Modifying注解

1．@Query注解
使用@Query注解可以将JPQL语句直接定义在数据访问接口方法上，并且接口方法名不受查询关键字和关联查询命名规范约束。

2．@Modifying注解
可以使用@Modifying和@Query注解组合定义在数据访问接口方法上，进行更新查询操作。

@Query注解

public interface AuthorityRepository extends JpaRepository<Authority, Integer>{
    /**
     * 根据用户名查询用户所拥有的权限（关联查询）
     */
    @Query("select a from Authority a inner join a.userList u where u.username = ?1")
    public List<Authority> findByUserListUsername(String username);
}

/**
* 根据作者id查询文章信息（标题和内容）
*/
@Query("select new Map(a.title as title, a.content as content) from Article a where a.author.id = ?1 ")
public List<Map<String, Object>> findTitleAndContentByAuthorId(Integer id);

@Query("select a from Article a where a.author.aname = :aname1 and a.author.aname.id = :id1 ")
public List<Article> findArticleByAuthorAnameAndId(@Param("aname1") String aname, @Param("id1") Integer id);

@Modifying注解

    可以使用@Modifying和@Query注解组合定义在数据访问接口方法上，进行更新查询操作，示例代码如下：
     /**
     * 根据作者id删除作者
     */
    @Modifying
    @Query("delete from Author a where a.id = ?1")
    public int deleteAuthorByAuthorId(int id);

@Query和@Modifying注解的使用方法

1）创建持久化实体类
2）创建数据访问层
3）创建业务层
4）创建控制器类
5）运行

1）创建持久化实体类

创建名为com.ch.ch6_3.entity的包，并在该包中创建名为Article和Author的持久化实体类

package com.ch.ch6_3.entity;

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@Table(name = "author_table")
@JsonIgnoreProperties(value = { "hibernateLazyInitializer" })
public class Author implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    // 作者名
    private String aname;
    // 文章列表，作者与文章是一对多的关系
    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, targetEntity = Article.class, fetch = FetchType.LAZY)
    private List<Article> articleList;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getAname() {
        return aname;
    }

    public void setAname(String aname) {
        this.aname = aname;
    }

    public List<Article> getArticleList() {
        return articleList;
    }

    public void setArticleList(List<Article> articleList) {
        this.articleList = articleList;
    }
}

package com.ch.ch6_3.entity;

import java.io.Serializable;

import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Lob;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.Size;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@Table(name = "article_table")
@JsonIgnoreProperties(value = { "hibernateLazyInitializer" })
public class Article implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    // 标题
    @NotEmpty(message = "标题不能为空")
    @Size(min = 2, max = 50)
    @Column(nullable = false, length = 50)
    private String title;

    // 文章内容
    @Lob // 大对象，映射 为MySQL的Long文本类型
    @Basic(fetch = FetchType.LAZY)
    @NotEmpty(message = "内容不能为空")
    @Size(min = 2)
    @Column(nullable = false)
    private String content;

    // 所属作者，文章与作者是多对一的关系
    @ManyToOne(cascade = { CascadeType.MERGE, CascadeType.REFRESH }, optional = false)
    // 可选属性optional=false,表示author不能为空。删除文章，不影响用户
    @JoinColumn(name = "id_author_id") // 设置在article表中的关联字段(外键)
    @JsonIgnore
    private Author author;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Author getAuthor() {
        return author;
    }

    public void setAuthor(Author author) {
        this.author = author;
    }
}

2）创建数据访问层

创建名为com.ch.ch6_3.repository的包，并在该包中创建名为ArticleRepository和AuthorRepository的接口。
public interface ArticleRepository extends JpaRepository<Article, Integer>{
    /**
     * 根据作者id查询文章信息（标题和内容）
     */
    @Query("select new Map(a.title as title, a.content as content) from Article a where a.author.id = ?1 ")
    public List<Map<String, Object>> findTitleAndContentByAuthorId(Integer id);
    /**
     * 根据作者名和作者id查询文章信息
     */
    @Query("select a from Article a where a.author.aname = :aname1 and a.author.id = :id1 ")
    public List<Article> findArticleByAuthorAnameAndId(@Param("aname1") String aname, @Param("id1") Integer id);
}

package com.ch.ch6_3.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.ch.ch6_3.entity.Author;

public interface AuthorRepository extends JpaRepository<Author, Integer> {
    /**
     * 根据文章标题包含的内容，查询作者（关联查询）
     */
    @Query("select a from Author a  inner join  a.articleList t where t.title like %?1%")
    public Author findAuthorByArticleListtitleContaining(String title);

    /**
     * 根据作者id删除作者
     */
    @Modifying
    @Query("delete from Author a where a.id = ?1")
    public int deleteAuthorByAuthorId(int id);
}

package com.ch.ch6_3.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.ch.ch6_3.entity.Article;

public interface ArticleRepository extends JpaRepository<Article, Integer> {
    /**
     * 根据作者id查询文章信息（标题和内容）
     */
    @Query("select new Map(a.title as title, a.content as content) from Article a where a.author.id = ?1 ")
    public List<Map<String, Object>> findTitleAndContentByAuthorId(Integer id);

    /**
     * 根据作者名和作者id查询文章信息
     */
    @Query("select a from Article a where a.author.aname = :aname1 and a.author.id = :id1 ")
    public List<Article> findArticleByAuthorAnameAndId(@Param("aname1") String aname, @Param("id1") Integer id);
}

3）创建业务层

创建名为com.ch.ch6_3.service的包，并在该包中创建名为AuthorAndArticleService的接口和接口实现类AuthorAndArticleServiceImpl。

package com.ch.ch6_3.service;

import java.util.List;
import java.util.Map;

import com.ch.ch6_3.entity.Article;
import com.ch.ch6_3.entity.Author;

public interface AuthorAndArticleService {
    public List<Map<String, Object>> findTitleAndContentByAuthorId(Integer id);

    public List<Article> findArticleByAuthorAnameAndId(String aname, Integer id);

    public Author findAuthorByArticleListtitleContaining(String title);

    public int deleteAuthorByAuthorId(int id);
}

package com.ch.ch6_3.service;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ch.ch6_3.entity.Article;
import com.ch.ch6_3.entity.Author;
import com.ch.ch6_3.repository.ArticleRepository;
import com.ch.ch6_3.repository.AuthorRepository;

@Service
@Transactional
public class AuthorAndArticleServiceImpl implements AuthorAndArticleService {
    @Autowired
    private AuthorRepository authorRepository;
    @Autowired
    private ArticleRepository articleRepository;

    @Override
    public List<Map<String, Object>> findTitleAndContentByAuthorId(Integer id) {
        return articleRepository.findTitleAndContentByAuthorId(id);
    }

    @Override
    public List<Article> findArticleByAuthorAnameAndId(String aname, Integer id) {
        return articleRepository.findArticleByAuthorAnameAndId(aname, id);
    }

    @Override
    public Author findAuthorByArticleListtitleContaining(String title) {
        return authorRepository.findAuthorByArticleListtitleContaining(title);
    }

    @Override
    public int deleteAuthorByAuthorId(int id) {
        return authorRepository.deleteAuthorByAuthorId(id);
    }
}

4）创建控制器类

创建com.ch.ch6_3.controller的包，并在该包中创建名为TestOneToManyController的控制器类。

package com.ch.ch6_3.controller;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.ch.ch6_3.entity.Article;
import com.ch.ch6_3.entity.Author;
import com.ch.ch6_3.service.AuthorAndArticleService;

@RestController
public class TestOneToManyController {
    @Autowired
    private AuthorAndArticleService authorAndArticleService;

    @RequestMapping("/findTitleAndContentByAuthorId")
    public List<Map<String, Object>> findTitleAndContentByAuthorId(Integer id) {
        return authorAndArticleService.findTitleAndContentByAuthorId(id);
    }

    @RequestMapping("/findArticleByAuthorAnameAndId")
    public List<Article> findArticleByAuthorAnameAndId(String aname, Integer id) {
        return authorAndArticleService.findArticleByAuthorAnameAndId(aname, id);
    }

    @RequestMapping("/findAuthorByArticleListtitleContaining")
    public Author findAuthorByArticleListtitleContaining(String title) {
        return authorAndArticleService.findAuthorByArticleListtitleContaining(title);
    }

    @RequestMapping("/deleteAuthorByAuthorId")
    public int deleteAuthorByAuthorId(int id) {
        return authorAndArticleService.deleteAuthorByAuthorId(id);
    }
}

首先，运行Ch63Application主类。然后，通过“http://localhost:8089/ch6_3/findTitleAndContentByAuthorId?id=1”查询作者id为1的文章标题和内容。

package com.ch.ch6_3;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Ch63Application {
    public static void main(String[] args) {
        SpringApplication.run(Ch63Application.class, args);
    }
}