JPA多表查询

JPA多表查询

DTO

dto是做表示层的，不需要和数据库中的表的字段完全一样，不用加@Entity注解；entity是完全和数据库表的字段一一对应。

package com.zsj.dto;


/**
 * @author zsj55
 */

public class HomeworkIsSubmitDTO {
    private String userName;
    private String isSubmit;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getIsSubmit() {
        return isSubmit;
    }

    public void setIsSubmit(String isSubmit) {
        this.isSubmit = isSubmit;
    }
}

Repository

package com.zsj.repository;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.List;
import java.util.Map;

/**
 * @author zsj55
 */
@Repository
public class DTODao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> queryHomeworkDTOListMap(String homeworkId){
        String sql = "select u.name, uh.homeworkmaster_id from user u left join user_homework uh on (u.id = uh.user_id and uh.homeworkmaster_id=?);";
        /**
        *数组中存放传给uh.homeworkmaster_id=?的值，homeworkId
        */
        Object[] args = {homeworkId};
        int[] argsTypes = {Types.VARCHAR};
        return this.jdbcTemplate.queryForList(sql, args, argsTypes);
    }
}

Service

public List<HomeworkIsSubmitDTO> getHomeworkDetailByHid(String hid){
        List<HomeworkIsSubmitDTO> dtoList = new ArrayList<HomeworkIsSubmitDTO>();
        List<Map<String, Object>> mapList = dtoDao.queryHomeworkDTOListMap(hid);
        for(int i = 0; i < mapList.size(); i++){
            HomeworkIsSubmitDTO homeworkIsSubmitDTO = new HomeworkIsSubmitDTO();
            String name = (String) mapList.get(i).get("name");
            homeworkIsSubmitDTO.setUserName(name);
            String homeworkmasterId = (String) mapList.get(i).get("homeworkmaster_id");
            if(homeworkmasterId != null && !"".equals(homeworkmasterId)){
                homeworkIsSubmitDTO.setIsSubmit("已提交");
            }
            else{
                homeworkIsSubmitDTO.setIsSubmit("未提交");
            }
            dtoList.add(homeworkIsSubmitDTO);
        }

        return dtoList;
    }

Controller

/**
*submitdetail/{hid} 地址栏传值id
*/
@RequestMapping("submitdetail/{hid}")
    public List<HomeworkIsSubmitDTO> getHomeworkDetailByHid(@PathVariable("hid") String hid){
        return homeworkService.getHomeworkDetailByHid(hid);
    }

地址栏

[外链图片转存失败(img-echBujSE-1565570781020)(C:\Users\zsj55\AppData\Roaming\Typora\typora-user-images\1565570753556.png)]

结果

Service.getHomeworkDetailByHid(hid);
}

**地址栏**

[外链图片转存中...(img-echBujSE-1565570781020)]



**结果**

[外链图片转存失败(img-GVbRvHaD-1565570781036)(C:\Users\zsj55\AppData\Roaming\Typora\typora-user-images\1565570735317.png)]