1、在数据库写好,多表关联Sql语句
SELECT a.*,b.id as item_id FROM tb_test a,tb_item b WHERE a.id = b.test_id AND a.id = '123'
2、在项目Dao层,开启本地查询(nativeQuery = true)
package com.tc.jpa.dao;
import com.tc.jpa.domain.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
import java.util.Map;
public interface UserRepository extends JpaRepository<User,String>, JpaSpecificationExecutor<User> {
// 多表查询+分页,自定义查询返回的结果集用 List<Map<String,object>> 接收
@Query(value = "SELECT a.*,b.id as item_id FROM tb_test a,tb_item b WHERE a.id = b.test_id AND a.id =?1",
nativeQuery = true)
public List<Map<String,Object>> userAndItemListMap(String id, Pageable pageable);
}
3、定义数据转换对象DTO(用来封装多表查询返回的数据)
package com.tc.jpa.dto;
import com.fasterxml.jackson.annotation.JsonProperty;
import lombok.Data;
@Data
public class UserDTO {
private String id;
private String username;
private String password;
private String name;
private String bz;
// 指定序列化的名称
@JsonProperty("itemId")
private String item_id;
}
4、service层
package com.tc.jpa.service;
import com.tc.jpa.dao.UserRepository;
import com.tc.jpa.dto.UserDTO;
import com.tc.jpa.utils.MapObjUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public List<UserDTO> findByUserAndItemList(String id,int page,int size){
// 分页初始化默认值
if (page <= 0){
page = 1;
}
if (size <= 0){
size = 10;
}
Pageable pageable = PageRequest.of(page - 1, size);
List<Map<String, Object>> mapList = userRepository.userAndItemListMap(id, pageable);
if (mapList == null || mapList.isEmpty()){
return null;
}
List<UserDTO> resultList = new ArrayList<>();
for (Map<String, Object> map: mapList){
UserDTO userDTO = MapObjUtil.map2Object(map,UserDTO.class);
resultList.add(userDTO);
}
return resultList;
}
}
5、MapObjUtil 装换工具类
package com.tc.jpa.utils;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class MapObjUtil {
/**
* 实体对象转成Map
*
* @param obj 实体对象
* @return
*/
public static Map<String, Object> object2Map(Object obj) {
Map<String, Object> map = new HashMap<String, Object>();
if (obj == null) {
return map;
}
Class clazz = obj.getClass();
Field[] fields = clazz.getDeclaredFields();
try {
for (Field field : fields) {
field.setAccessible(true);
map.put(field.getName(), field.get(obj));
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
/**
* Map转成实体对象
*
* @param map map实体对象包含属性
* @param clazz 实体对象类型
* @return
*/
public static <T> T map2Object(Map<String, Object> map, Class<T> clazz) {
if (map == null) {
return null;
}
T obj = null;
try {
obj = clazz.newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
for (Field field : fields) {
int mod = field.getModifiers();
if (Modifier.isStatic(mod) || Modifier.isFinal(mod)) {
continue;
}
field.setAccessible(true);
String filedTypeName = field.getType().getName();
if (filedTypeName.equalsIgnoreCase("java.util.date")) {
String datetimestamp = String.valueOf(map.get(field.getName()));
if (datetimestamp.equalsIgnoreCase("null")) {
field.set(obj, null);
} else {
field.set(obj, new Date(Long.parseLong(datetimestamp)));
}
} else {
field.set(obj, map.get(field.getName()));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}
}
6、controller层
package com.tc.jpa.controller;
import com.tc.jpa.dto.UserDTO;
import com.tc.jpa.response.model.ResultVO;
import com.tc.jpa.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/list/{id}/{page}/{size}")
public ResultVO userList(@PathVariable("id") String id,@PathVariable("page") int page,@PathVariable("size") int size){
List<UserDTO> userDTOList = userService.findByUserAndItemList(id, page, size);
return new ResultVO(true,20000,"查询成功",userDTOList);
}
}
7、统一数据响应格式封装
package com.tc.jpa.response.model;
import lombok.Data;
/**
* 统一响应标准
*/
@Data
public class ResultVO{
private boolean flag;//是否成功
private Integer code;// 返回码
private String message;//返回信息
private Object data;// 返回数据
public ResultVO() {
}
public ResultVO(boolean flag, Integer code, String message) {
this.flag = flag;
this.code = code;
this.message = message;
}
public ResultVO(boolean flag, Integer code, String message, Object data) {
this.flag = flag;
this.code = code;
this.message = message;
this.data = data;
}
}
8、测试
以上,个人整理。如有错漏之处,欢迎大家指正。谢谢!