InspectDao.java
3.95 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package vion.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import vion.model.Inspect;
import java.util.Date;
import java.util.List;
@Repository
public class InspectDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private static final String SELECT_INSPECT_ALL = "SELECT * FROM tbl_inspect_info";
public List<Inspect> getInspectAll() {
return jdbcTemplate.query(SELECT_INSPECT_ALL, new BeanPropertyRowMapper<Inspect>(Inspect.class));
}
private static final String SELECT_INSPECT_BY_ID = "SELECT * FROM tbl_inspect_info WHERE id=?";
public Inspect getInspectByID(Integer id) {
List<Inspect> inspects= jdbcTemplate.query(SELECT_INSPECT_BY_ID,new Object[]{id}, new BeanPropertyRowMapper<Inspect>(Inspect.class));
if(inspects.size()>0){
return inspects.get(0);
}else{
return null;
}
}
private static final String SELECT_INSPECT_BY_PARM= "SELECT * FROM tbl_inspect_info WHERE account_id=? ";
public List<Inspect> getInspectByParm(Integer account_id, Integer store_id, Date startdate,
Date enddate, Integer status) {
StringBuffer getInspectSb = new StringBuffer(SELECT_INSPECT_BY_PARM);
if(store_id!=null){
getInspectSb.append(" and store_id = "+store_id+"");
}
if(startdate!=null){
getInspectSb.append(" and inspect_date>='"+startdate+"'");
}
if(enddate!=null){
getInspectSb.append(" and inspect_date<='"+enddate+"'");
}
if(status!=null){
getInspectSb.append(" and status="+status+"");
}
return jdbcTemplate.query(getInspectSb.toString(),new Object[]{account_id}, new BeanPropertyRowMapper<Inspect>(Inspect.class));
}
/*
private static final String SAVE_INSPECT= "INSERT INTO tbl_inspect_info(\n" +
" store_id, inspect_date, type, status, inspect_user, reviewer, \n" +
" audit_date, finish_date, remark)\n" +
" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
public Integer addInspect(Inspect inspect) {
return jdbcTemplate.update(SAVE_INSPECT,
new Object[]{
inspect.getStore_id(),inspect.getInspect_date(),inspect.getType(),inspect.getStatus(),
inspect.getInspect_user(),inspect.getReviewer(),inspect.getAudit_date(),
inspect.getFinish_date(),inspect.getRemark()});
}
private static final String UPDATE_INSPECT= "UPDATE tbl_inspect_info\n" +
" SET store_id=?, inspect_date=?, type=?, status=?, inspect_user=?, \n" +
" reviewer=?, audit_date=?, finish_date=?, modify_time=?, \n" +
" remark=?\n" +
" WHERE id=?;";
public Integer updateInspect(Inspect inspect) {
return jdbcTemplate.update(UPDATE_INSPECT,
new Object[]{
inspect.getStore_id(),inspect.getInspect_date(),inspect.getType(),inspect.getStatus(),
inspect.getInspect_user(),inspect.getReviewer(),inspect.getAudit_date(),
inspect.getFinish_date(),inspect.getModify_time(),inspect.getRemark(),
inspect.getId()});
}
private static final String UPDATE_INSPECT_STATUS= "UPDATE tbl_inspect_info\n" +
" SET status=?,reviewer=?, audit_date=?, finish_date=?, modify_time=? \n" +
" WHERE id=?;";
public Integer updateInspectStatus(Inspect inspect) {
return jdbcTemplate.update(UPDATE_INSPECT_STATUS,
new Object[]{
inspect.getStatus(),inspect.getReviewer(),inspect.getAudit_date(),
inspect.getFinish_date(),inspect.getModify_time(),inspect.getId()});
}*/
}