先描述一下需求……
我需要在JSP前端通过
input type=date 传入两个日期参数，然后查询出下表这2个日期之间的内容
比如传入 2017-10-1和2017-10-3 这样
/* Table: tbl_paymoney 付款表 /
/==============================================================*/
create table tbl_paymoney (
pay_id NUMBER(20) not null,
bat_id NUMBER(20),
pay_account NUMBER(10,2),
pay_pays NUMBER(10,2),
pay_state VARCHAR2(20),
pay_date DATE,
constraint PK_TBL_PAYMONEY primary key (pay_id)
);
comment on table tbl_paymoney is '付款表';
comment on column tbl_paymoney.pay_id is '付款ID';
comment on column tbl_paymoney.bat_id is '批次ID';
comment on column tbl_paymoney.pay_account is '赊账';
comment on column tbl_paymoney.pay_pays is '已付款金额';
comment on column tbl_paymoney.pay_state is '状态';
comment on column tbl_paymoney.pay_date is '日期';
先贴出报错吧
开始日期=2017-10-01
结束日期=2017-10-03
ipm date1=2017-10-01
ipm date2=2017-10-03
十月 06, 2017 10:53:33 上午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [springmvc] in context with path [/MedicalExaminationSystem] threw exception [Request processing failed; nested exception is org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.great.dao.UserDao.querySumMoney] with root cause
org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.great.dao.UserDao.querySumMoney
这个是action的接收类
@Controller
public class ReportAction {
@Autowired
IReportService Service;
@RequestMapping("/getPayM") // 通过两个日期查询两个日期内的金额
public ModelAndView GetPayMoney(Date date1, Date date2) {
ModelAndView modelAndView = new ModelAndView("Report");
System.out.println("开始日期=" + date1);
System.out.println("结束日期=" + date2);
List<PayMoney> list=Service.querySumMoney(date1, date2);
System.out.println("跳转信息发送到界面" + list);
if (list != null) {// 判断不为空跳转
System.out.println("返回");
modelAndView.addObject(list);
return modelAndView;
} else {
return modelAndView;
}
}
}
这个是接口
public interface IReportService {
public List<PayMoney> querySumMoney(Date date1,Date date2);
这个是实现类
这里的sys测试就打印不出来了
@Service
public class ReportServiceImp implements IReportService{
@Autowired
private UserDao UserDao;
@Override
public List<PayMoney> querySumMoney(Date date1, Date date2) {
System.out.println("ipm date1="+date1);
System.out.println("ipm date2="+date2);
List<PayMoney> list=UserDao.querySumMoney(date1, date2);
System.out.println("测试"+list);
if (list != null) {
return list;
}
return null;
}
}
userdao
@Repository
public interface UserDao {
public List<PayMoney> querySumMoney(Date date1,Date date2);//通过日期查询付款表内容
}
接下来是mapper
这里我感觉应该就是问题的源泉……
里面的pay_date1和2我感觉问题比较大……
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.great.dao.UserDao">
<select id="querySumMoney" resultType="com.great.entity.PayMoney" parameterType="com.great.entity.PayMoney" resultMap="BaseResultMap">
select sum(pay_account+pay_pays) from TBL_PAYMONEY
where to_char(tbl_paymoney.pay_date,'yyyy-mm-dd')
between '#{pay_date1,jdbcType=DATE}' and '#{pay_date2,jdbcType=DATE}';
</select>
</mapper>
最后是bean
public class PayMoney {
private Integer pay_id;// 付款ID
private Integer bat_id;// 批次ID
private Integer pay_account;// 赊账
private Integer pay_pays;// 已付款金额
private String pay_state;// 状态
private Date pay_date1;// 日期1
private Date pay_date2;// 日期2
public PayMoney() {
super();
}
public PayMoney(Integer pay_id, Integer bat_id, Integer pay_account, Integer pay_pays, String pay_state,
Date pay_date1, Date pay_date2) {
super();
this.pay_id = pay_id;
this.bat_id = bat_id;
this.pay_account = pay_account;
this.pay_pays = pay_pays;
this.pay_state = pay_state;
this.pay_date1 = pay_date1;
this.pay_date2 = pay_date2;
}
下面的tostring和getset就不贴了
原本只有一个date，因为老是找不到问题所以我自己改成了date1和date2但果然还是不行……
看了好几天感觉是mapper的语句有问题……但是在orcal上运行时可以查询到的……这里就很费解了，希望大大们能帮帮忙解解惑学校项目时间要到期了T_T
