比如来看一下这样的一条SQL语句,这是一个三张表的多表查询,显然在JPA中用一个实体类是接受不了这些参数的
select
t1.id as chapterId ,
t1.name as chapterName ,
t2.id as unitId,
t2.name as unitName ,
t3.id as lessonId,
t3.name as lessonName
from t_chapter t1
LEFT JOIN t_unit t2 on t1.id =t2.chapter_id
LEFT JOIN t_lession t3 on t3.unit_id =t2.id
where t1.id= '4028b4816305ea91016305eec24f0000'
and t2.id='4028b4816305f1a6016305f423180000'
and t3.id= '4028b4816306007b016306020bb80000';
Repository
/**
* Created by ZhuPengWei on 2018/5/11.
*/
public interface TestRepository extends JpaRepository<ProductInfo, String > {
@Query(
value = "select \n" +
"t1.id as chapterId ,t1.name as chapterName ,t2.id as unitId,t2.name as unitName ,t3.id as lessonId,t3.name as lessonName\n" +
"from t_chapter t1\n" +
"LEFT JOIN t_unit t2 on t1.id =t2.chapter_id\n" +
"LEFT JOIN t_lession t3 on t3.unit_id =t2.id \n" +
"where t1.id= :chapterId \n" +
"and t2.id=:unitId \n" +
"and t3.id= :lessonId",
nativeQuery = true
)
List<Object[]> select(@Param("chapterId") String chapterId, @Param("unitId") String unitId, @Param("lessonId") String lessonId);
}
好下面到单元测试
/**
* Created by ZhuPengWei on 2018/5/11.
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestRepositoryTest {
@Autowired
private TestRepository testRepository;
@Test
public void select() throws Exception {
List<Object[]> select = testRepository.select("4028b4816305ea91016305eec24f0000", "4028b4816305f1a6016305f423180000", "4028b4816306007b016306020bb80000");
List<TestView> testViews = castEntity(select, TestView.class);
Assert.assertTrue(testViews.size() > 0);
}
//转换实体类
public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz) throws Exception {
List<T> returnList = new ArrayList<T>();
if(CollectionUtils.isEmpty(list)){
return returnList;
}
Object[] co = list.get(0);
Class[] c2 = new Class[co.length];
//确定构造方法
for (int i = 0; i < co.length; i++) {
if(co[i]!=null){
c2[i] = co[i].getClass();
}else {
c2[i]=String.class;
}
}
for (Object[] o : list) {
Constructor<T> constructor = clazz.getConstructor(c2);
returnList.add(constructor.newInstance(o));
}
return returnList;
}
自定义实体
/**
* Created by ZhuPengWei on 2018/5/11.
*/
@Data
public class TestView {
private String chapterId;
private String chapterName;
private String unitId;
private String unitName;
private String lessonId;
private String lessonName;
public TestView() {
}
public TestView(String chapterId, String chapterName, String unitId, String unitName, String lessonId, String lessonName) {
this.chapterId = chapterId;
this.chapterName = chapterName;
this.unitId = unitId;
this.unitName = unitName;
this.lessonId = lessonId;
this.lessonName = lessonName;
}
}
需要注意的是 SQL语句的字段顺序一定要与实体类字段的顺序保持一致,否则会出现参数封装错误的情况