使用jpa进行两表联查时总会有字段名相同,所以需要用别名进行区分;

例子:

department表同时包含子级id和父级id:

查询语句为:


   
  1. select d.id,d.name,d.description,d.parent_id,temp.name as parent_name 
  2. from department d,department temp 
  3. where d.parent_id=? 1 and if(d.parent_id= -1,d.id=temp.id,d.parent_id=temp.id)

此时希望同时获取部门信息、部门的父级id和父级部门名称,但不希望在department表在新建一列parent_name,[email protected]个要映射到数据库表中的字段;

[email protected]

[email protected],数据库不会创建parent_name字段,上述查询也无法获取值;

[email protected],数据库会创建parent_name字段,上述查询可以获取值;

[email protected],数据库不会创建parent_name字段,上述查询可以获取值;

关键代码如下:


   
  1. package cn.test.bean;
  2. import java.util.List;
  3. import javax.persistence.Entity;
  4. import javax.persistence.GeneratedValue;
  5. import javax.persistence.GenerationType;
  6. import javax.persistence.Id;
  7. import javax.persistence.Table;
  8. import javax.persistence.Transient;
  9. /**
  10. * 部门实体类
  11. *
  12. */
  13. @Entity
  14. @Table(name= "department")
  15. public class Department {
  16. @Id
  17. @GeneratedValue(strategy=GenerationType.IDENTITY)
  18. private Long id;
  19. private String name;
  20. private Long parentId;
  21. private String description;
  22. private String parentName;
  23. @Transient
  24. private List<Department> childrens;
  25. public Long getId() {
  26. return id;
  27. }
  28. public void setId(Long id) {
  29. this.id = id;
  30. }
  31. public String getName() {
  32. return name;
  33. }
  34. public void setName(String name) {
  35. this.name = name;
  36. }
  37. public Long getParentId() {
  38. return parentId;
  39. }
  40. public void setParentId(Long parentId) {
  41. this.parentId = parentId;
  42. }
  43. public String getDescription() {
  44. return description;
  45. }
  46. public void setDescription(String description) {
  47. this.description = description;
  48. }
  49. public String getParentName() {
  50. return parentName;
  51. }
  52. @Transient
  53. public void setParentName(String parentName) {
  54. this.parentName = parentName;
  55. }
  56. public List<Department> getChildrens() {
  57. return childrens;
  58. }
  59. public void setChildrens(List<Department> childrens) {
  60. this.childrens = childrens;
  61. }
  62. }

   
  1. package cn.test.dao;
  2. import java.util.List;
  3. import org.springframework.data.jpa.repository.JpaRepository;
  4. import org.springframework.data.jpa.repository.Query;
  5. import cn.test.bean.Department;
  6. public interface DepartmentRepository extends JpaRepository<Department, Long>{
  7. @Query(value= "select d.id,d.name,d.description,d.parent_id,temp.name as parent_name " +
  8. "from department d,department temp " +
  9. "where d.parent_id=?1 and if(d.parent_id=-1,d.id=temp.id,d.parent_id=temp.id)",nativeQuery= true)
  10. public List<Department> testAs(Long parentId);
  11. }

原文地址:https://blog.csdn.net/woshihm/article/details/93721300


发布评论

分享到:

IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Spring data jpa hibernate:查询异常java.sql.SQLException: Column '列名' not found知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。