Spring Boot之JPA杂记Inner Join

最新推荐文章于 2024-06-07 14:44:56 发布

kuangniaokuang

最新推荐文章于 2024-06-07 14:44:56 发布

阅读量1.2w

点赞数

分类专栏： SpringBoot

本文链接：https://blog.csdn.net/kuangniaokuang/article/details/79566717

版权

SpringBoot 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

只为了实现一个很简单的操作：

select t1.id,t1.studentname,t2.gradename
from student t1
inner join grade t2
on t1.gradeid=t2.id

先创建了两个Entity

@Entity
public class Grade {
	@Id
	@GeneratedValue
	private Long id;
	private String gradename;
	//省略set get
	public Grade(Long id, String gradename) {
		super();
		this.id = id;
		this.gradename = gradename;
	}
}

@Entity
public class Student {
	@Id
	@GeneratedValue
	private Long id;
	private Long gradeid;
	private String studentname;
	//省略set get
	public Student(Long id, Long gradeid, String studentname) {
		super();
		this.id = id;
		this.gradeid = gradeid;
		this.studentname = studentname;
	}
}

因为返回的结果有id,studentname,gradename与这两个Entity都不同，所以又新建了一个StudentResult的POJO

public class StudentResult {
	Long id;
	String studentname, gradename;
	public StudentResult(Long id, String studentname, String gradename) {
		super();
		this.id = id;
		this.studentname = studentname;
		this.gradename = gradename;
	}
}

数据库数据如下

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓疯狂尝试↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

尝试一

repository

public interface StudentRepository extends JpaRepository<Student, Long> {
	@Query(nativeQuery = true,
               value = "select t1.id,t1.studentname,t2.gradename from student t1 inner join grade t2 on t1.gradeid=t2.id")
	List<StudentResult> getStudentResults();
}

controller

@RestController
@RequestMapping("/student")
public class StudentController {
	@Autowired
	StudentRepository studentRepository;
	@RequestMapping("/result")
	public List<StudentResult> getStudentResults() {
		List<StudentResult> studentResults = studentRepository.getStudentResults();
		return studentResults;
	}
}

Failed to convert from type [java.lang.Object[]] to type [cn.labsys.pmsys.entity.StudentResult]

2018-03-15 13:56:51.769 ERROR 9484 --- [nio-9090-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.Object[]] to type [cn.labsys.pmsys.entity.StudentResult] for value '{1, 1年级小学生A, 一年级}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.math.BigInteger] to type [cn.labsys.pmsys.entity.StudentResult]] with root cause

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.math.BigInteger] to type [cn.labsys.pmsys.entity.StudentResult]

主要意思就是没有办法从Object[]转成StudentResult。

那如果直接返回Object[]呢。代码如下：

repository

@Query(nativeQuery = true, value = "select t1.id,t1.studentname,t2.gradename from student t1 inner join grade t2 on t1.gradeid=t2.id")
	List<Object> getObjectResults();

controller 做相应的更改

url访问结果为

[[1,"1年级小学生A","一年级"],[3,"1年级小学生B","一年级"],[2,"2年级小学生","二年级"]]

ε=(´ο｀*)))唉，不是自己想要的，漂亮的json键值对类型呀。。。。

注：nativeQuery = true 本地模式，利用的什么数据库，就要用该数据库对应的语句来编写。

尝试二

使用下JPQL是不是会好一些呢

注：JPQL中对应的是Entity类，此处的Student，Grade首字母都应该为大写

多个学生一个班级，一个班级多个学生典型的 ManyToOne VS OneToMany

错误用法一：Path expected for join!

先展示一个JPQL的JOIN错误用法

@Query("select t1.id,t1.studentname,t2.gradename from Student t1 inner join Grade t2 on t1.gradeid=t2.id ")
List<StudentResult> getStuResults();

Path expected for join!

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: 
Path expected for join! [select t1.id,t1.studentname,t2.gradename from cn.labsys.pmsys.entity.Student t1 inner join Grade t2 on t1.gradeid=t2.id ]

这个Join用的有些问题呀。。。

错误用法二：Repeated column in mapping for entity

ManyToOne

@Entity
public class Student {
	@Id
	@GeneratedValue
	private Long id;
	private Long gradeid;
	private String studentname;
	@ManyToOne//变化部分
	@JoinColumn(name="gradeid",referencedColumnName="id")//变化部分
	private Grade grade;//变化部分
	//省略set get
	public Student(Long id, Long gradeid, String studentname) {
		super();
		this.id = id;
		this.gradeid = gradeid;
		this.studentname = studentname;
	}
}

Repeated column in mapping for entity

Repeated column in mapping for entity: cn.labsys.pmsys.entity.Student column:
gradeid (should be mapped with insert="false" update="false")

错误用法三：could not resolve property:

修改刚才的ManyToOne

@Entity
public class Student {
	@Id
	@GeneratedValue
	private Long id;
	private String studentname;
	@ManyToOne
	@JoinColumn(name = "gradeid", referencedColumnName = "id")
	private Grade grade;
	//省略set get 此处没有写grade的set和get方法
	public Student(Long id, String studentname, Grade grade) {//Grade变化
		super();
		this.id = id;
		this.grade = grade;
		this.studentname = studentname;
	}
}//删除了对gradeid的定义

再写一个JPQL的Join错误写法

@Query("select t1.id,t1.studentname,t2.gradename from Student t1 join t1.grade t2 on t1.gradeid=t2.id")
List<StudentResult> getStuResults();

could not resolve property:

could not resolve property: gradeid of: cn.labsys.pmsys.entity.Student
[select t1.id,t1.studentname,t2.gradename from cn.labsys.pmsys.entity.Student t1 join t1.grade t2 on t1.gradeid=t2.id]

错误用法四：No aliases found in result tuple! Make sure your query defines aliases!

再次更改Repository中的写法

	@Query("select t1.id,t1.studentname,t2.gradename from Student t1 join t1.grade t2")
	List<StudentResult> getStuResults();

No aliases found in result tuple! Make sure your query defines aliases!

java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!

没有别名，没有别名，没有别名。。。。

正确用法！！↓↓！！

@Query("select NEW cn.labsys.pmsys.entity.StudentResult(t1.id,t1.studentname,t2.gradename) from Student t1 join t1.grade t2")

@Query("select NEW cn.labsys.pmsys.entity.StudentResult(t1.id,t1.studentname,t2.gradename) from Student t1 join t1.grade t2")
List<StudentResult> getStuResults();//NEW大小写都可

url终于能返回我想要的值了！

[{"id":1,"studentname":"1年级小学生A","gradename":"一年级"},{"id":3,"studentname":"1年级小学生B","gradename":"一年级"},{"id":2,"studentname":"2年级小学生","gradename":"二年级"}]

注：

1.因为Student类中，没有写grade的set，get方法，所以此时用StudentRepository中的findAll方法，得到的结果是：

[{"id":1,"studentname":"1年级小学生A"},{"id":2,"studentname":"2年级小学生"},{"id":3,"studentname":"1年级小学生B"}]

2.在实际应用过程中，很多情况需要对Student中的gradeid进行编辑，自然就需要写grade的get和set方法

此时的findAll得到的结果

[{"id":1,"studentname":"1年级小学生A","grade":{"id":1,"gradename":"一年级"}},{"id":2,"studentname":"2年级小学生","grade":{"id":2,"gradename":"二年级"}},{"id":3,"studentname":"1年级小学生B","grade":{"id":1,"gradename":"一年级"}}]

后台跑的sql语句为

Hibernate: select student0_.id as id1_8_, student0_.gradeid as gradeid3_8_, student0_.studentname as studentn2_8_ from student student0_
Hibernate: select grade0_.id as id1_1_0_, grade0_.gradename as gradenam2_1_0_ from grade grade0_ where grade0_.id=?
Hibernate: select grade0_.id as id1_1_0_, grade0_.gradename as gradenam2_1_0_ from grade grade0_ where grade0_.id=?

kuangniaokuang

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
Spring Boot之JPA杂记Inner Join

只为了实现一个很简单的操作：select t1.id,t1.studentname,t2.gradenamefrom student t1inner join grade t2on t1.gradeid=t2.id先创建了两个Entity@Entitypublic class Grade { @Id @GeneratedValue private Long id; private ...
复制链接

扫一扫