8月12日 JPA复杂查询

复杂查询

使用 JdbcTemplate
Repository

package com.zz.repository;

import java.sql.Types;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

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

@Repository
public class DTODao {
	@Resource
    private JdbcTemplate jdbcTemplate;
	
	 public List<Map<String, Object>> queryHomeWorkDTOListMap(String homework_id) {

	        String sql="select u.name,uh.homework_id from user u left join user_homework uh on (u.id=uh.user_id and uh.homework_id=?)";
	        Object[] args = { homework_id };

	        int[] argTypes = { Types.VARCHAR };

	        return this.jdbcTemplate.queryForList(sql, args, argTypes);

	    }


}

Service

package com.zz.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.zz.dto.HomeWorkDTO;
import com.zz.repository.DTODao;

@Service
public class HomeWorkService {
	
	@Resource
	DTODao dtodao;
	
	/**
	 * 根据主表id 查询提交情况
	 * @return
	 */
	public ArrayList<HomeWorkDTO> getHomeworkdetailByMasterId2(String mid){
		ArrayList<HomeWorkDTO> ls=new ArrayList<HomeWorkDTO>();
		List<Map<String, Object>> listmap=dtodao.queryHomeWorkDTOListMap(mid);
		for(int i=0;i<listmap.size();i++){
			String name=(String) listmap.get(i).get("name");
			String homid=(String) listmap.get(i).get("homework_id");
			HomeWorkDTO dto=new HomeWorkDTO();
			dto.setUserName(name);
			
			
			if(homid==null||"".equals(homid)){
				dto.setIsSubmit("未提交");
			}else{
				dto.setIsSubmit("已提交");
			}
			ls.add(dto);
			
		}
		return ls;
		
	}

}

controller

package com.zz.controller;

import java.util.ArrayList;

import javax.annotation.Resource;

import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.zz.dto.HomeWorkDTO;
import com.zz.service.HomeWorkService;

@RestController
@RequestMapping("hc")
public class HomeWorkController {
	@Resource
	HomeWorkService hservice;
	
	//springboot controller地址传值：
	//1, 在拦截路径上用{参数名字}  2，使用@PathVariable(参数名字) 获取参数值
	@RequestMapping("submitdetail/{id}")
	public ArrayList<HomeWorkDTO> getHomeworkdetailByMasterId2(@PathVariable("id") String mid){
		return hservice.getHomeworkdetailByMasterId2(mid);
	}

}