复杂查询springbootJPA js小记

Repository

package com.zz.repository;

import java.sql.Date;
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=?)";
       // 参数数组，按照顺序放入sql中的？占位符
        Object[] args = { homework_id };
     // 参数数组数据类型，跟上面数组的参数顺序对应，用来设置参数的数据类型
        int[] argTypes = { Types.VARCHAR };

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

    }
 
 
 public List<Map<String, Object>> getHomewokmasterByDateAndType(long sreachDate,String type){
		String sql="select * from homeworkmaster where open_date=? and type=?";
       Date date=new Date(sreachDate);
       // 参数数组，按照顺序放入sql中的？占位符
        Object[] args = { date,type };
     // 参数数组数据类型，跟上面数组的参数顺序对应，用来设置参数的数据类型
        int[] argTypes = {Types.DATE, Types.VARCHAR };

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

    }

}

Service

package com.zz.service;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
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;
import com.zz.util.DateUtil;

@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;
	
}


 public ArrayList<HomeWorkDTO>  getHomewokmasterByDateAndType(long sreachDate,String type){
	 ArrayList<HomeWorkDTO> ls=new ArrayList<HomeWorkDTO>();
	 List<Map<String, Object>> listmap=dtodao.getHomewokmasterByDateAndType(sreachDate, type);
		for(int i=0;i<listmap.size();i++){
			String id=(String) listmap.get(i).get("id");
			String title=(String) listmap.get(i).get("title");
			String type1=(String) listmap.get(i).get("type");
			String content=(String) listmap.get(i).get("content");
			System.out.println( listmap.get(i).get("update_time"));
			SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String update_timestr= listmap.get(i).get("update_time").toString();
			String open_datestr=listmap.get(i).get("open_date").toString();
			HomeWorkDTO hk=new HomeWorkDTO();
			try {
				java.util.Date udate=s.parse(update_timestr);
				hk.setUpdate_time(DateUtil.tranceToSqlDate(udate));
				
				java.util.Date udate2=s.parse(open_datestr);
				hk.setOpen_date(DateUtil.tranceToSqlDate(udate2));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			System.out.println("***********"+hk.getUpdate_time());
			hk.setId(id);
			hk.setType(type1);
			hk.setContent(content);
			ls.add(hk);
			
		}
	 
	 return ls;
 }

}

controller

package com.zz.controller;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

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);
}

@RequestMapping("getbydatetype/{date}/{type}")
 public ArrayList<HomeWorkDTO>  getHomewokmasterByDateAndType(@PathVariable("date") long sreachDate,@PathVariable("type") String type){
	SimpleDateFormat sformat=new SimpleDateFormat("yyyy-MM-dd");
	Date d=null;
	try {
		d=sformat.parse("2019-07-27");
	} catch (ParseException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	 return hservice.getHomewokmasterByDateAndType(d.getTime(), type);
 }

}

让按钮用js触发点击事件，type改成button