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); } }