forked from maxliaops/Java_Web_Examples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathQueryDAO.java
More file actions
257 lines (246 loc) · 11.7 KB
/
QueryDAO.java
File metadata and controls
257 lines (246 loc) · 11.7 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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
package com.dao;
import java.util.List;
import com.actionForm.StockMainViewForm;
import com.core.MySession;
import org.hibernate.Query;
import com.actionForm.LoanForm;
import com.actionForm.IfForm;
import org.hibernate.Session;
import com.actionForm.GoodsForm;
import com.actionForm.BranchForm;
import com.actionForm.DamageForm;
import org.hibernate.SQLQuery;
import com.actionForm.GetUseForm;
import java.util.Set;
public class QueryDAO {
private Session session = null;
//查询采购单详细信息
public StockMainViewForm stockDetail(int id) {
session = MySession.openSession(); //打开Session
StockMainViewForm stockMVF = new StockMainViewForm();
try {
stockMVF = (StockMainViewForm) session.get(StockMainViewForm.class,
id);
System.out.println("采购详细信息的大小：" + stockMVF.getStockDetail().size());
} catch (Exception e) {
System.out.println("查询采购单详细信息时出错：" + e.getMessage());
} finally {
MySession.closeSession(session);
}
return stockMVF;
}
//查询借出单详细信息
public LoanForm loanDetail(int id) {
session = MySession.openSession(); //打开Session
LoanForm loanF = null;
try {
loanF = (LoanForm) session.get(LoanForm.class,
id);
} catch (Exception e) {
System.out.println("查询借出单详细信息时出错：" + e.getMessage());
} finally {
MySession.closeSession(session);
}
return loanF;
}
//查询部门报损信息
public List damageQuery(IfForm ifForm) {
session = MySession.openSession(); //打开Session
String str = ifCompose(ifForm);
String sql = "select {damage.*},{goods.*},{branch.*} from tb_damage damage inner join tb_goods goods on damage.goodsid=goods.id inner join tb_branch branch on damage.branchid=branch.id ";
List list = null;
if (str == null) {
sql = sql + " order by damage.createTime desc";
} else {
sql = sql + " WHERE " + str +
" order by damage.createTime desc";
}
session = MySession.openSession(); //打开Session
System.out.println("SQL=" + sql);
try {
SQLQuery query = session.createSQLQuery(sql);
query.addEntity("goods", GoodsForm.class); //将数据表与实体类关联在一起
query.addEntity("damage", DamageForm.class);
query.addEntity("branch", BranchForm.class);
list = query.list();
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询时出现的错误信息：" + e.getMessage());
} finally {
MySession.closeSession(session); //关闭Session
}
return list;
}
//查询部门领用信息
public List getuseQuery(IfForm ifForm) {
session = MySession.openSession(); //打开Session
String str = ifCompose(ifForm);
String sql = "select {getuse.*},{goods.*},{branch.*} from tb_getuse getuse inner join tb_goods goods on getuse.goodsid=goods.id inner join tb_branch branch on getuse.branchid=branch.id ";
List list = null;
if (str == null) {
sql = sql + " order by getuse.createTime desc";
} else {
sql = sql + " WHERE " + str +
" order by getuse.createTime desc";
}
session = MySession.openSession(); //打开Session
System.out.println("SQL=" + sql);
try {
SQLQuery query = session.createSQLQuery(sql);
query.addEntity("goods", GoodsForm.class); //将数据表与实体类关联在一起
query.addEntity("getuse", GetUseForm.class);
query.addEntity("branch", BranchForm.class);
list = query.list();
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询时出现的错误信息：" + e.getMessage());
} finally {
MySession.closeSession(session); //关闭Session
}
return list;
}
//部门领用、报损信息汇总
public List getusedamageTotal(IfForm ifForm,String form,String cfield) {
session = MySession.openSession(); //打开Session
String str = ifCompose(ifForm);
String sql="";
List list = null;
if (str == null) {
sql = "select sum(form."+cfield+") as amount,b.name,g.name,g.spec,g.price,g.unit,g.producer "+
"FROM "+form+" form JOIN form.branch b JOIN form.goods g "+
"GROUP BY b.name,g.name,g.spec,g.price,g.unit,g.producer";
} else {
sql ="select sum(form."+cfield+") as amount,b.name,g.name,g.spec,g.price,g.unit,g.producer "+
"FROM "+form+" form JOIN form.branch b JOIN form.goods g WHERE "+str+" "+
"GROUP BY b.name,g.name,g.spec,g.price,g.unit,g.producer";
}
System.out.println("****************:"+sql);
session = MySession.openSession(); //打开Session
System.out.println("SQL=" + sql);
try {
list = session.createQuery(sql).list();
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询时出现的错误信息：" + e.getMessage());
} finally {
MySession.closeSession(session); //关闭Session
}
return list;
}
// 查询部门汇总信息
public List branchTotal(IfForm ifForm) {
session = MySession.openSession(); //打开Session
String str = ifCompose(ifForm);
List list = null;
String sql="select g.name,g.spec,g.unit,g.price,t.branchname,t.lyamount,t.bsamount,(t.lyamount-t.bsamount) zyamount from tb_goods g inner join ( "+
"select goodsid,branchname,sum(bsamount) bsamount,sum(lyamount) lyamount from(" +
"select getuse.goodsid,0 as bsamount,sum(getuse.number) as lyamount,branch.name as branchname from tb_getuse getuse inner join " +
" tb_branch branch on getuse.branchid=branch.id group by branch.name,getuse.goodsid " +
"union " +
"select damage.goodsid,sum(damage.damagenum) as bsamount,0 as lyamount,branch.name as branchname from tb_damage damage inner join " +
" tb_branch branch on damage.branchid=branch.id group by branch.name,damage.goodsid " +
") as t group by goodsid,branchname) as t on g.id=t.goodsid";
if (str != null) {
sql="select g.name,g.spec,g.unit,g.price,t.branchname,t.lyamount,t.bsamount,(t.lyamount-t.bsamount) zyamount from tb_goods g inner join ( "+
"select goodsid,branchname,sum(bsamount) bsamount,sum(lyamount) lyamount from(" +
"select getuse.goodsid,0 as bsamount,sum(getuse.number) as lyamount,branch.name as branchname from tb_getuse getuse inner join " +
" tb_branch branch on getuse.branchid=branch.id and getuse."+str+" group by branch.name,getuse.goodsid " +
"union " +
"select damage.goodsid,sum(damage.damagenum) as bsamount,0 as lyamount,branch.name as branchname from tb_damage damage inner join " +
" tb_branch branch on damage.branchid=branch.id and damage."+str+" group by branch.name,damage.goodsid " +
") as t group by goodsid,branchname) as t on g.id=t.goodsid";
// sql="select goods.name,goods.spec,goods.unit,goods.price,totle.branchname,totle.lyamount,totle.bsamount,totle.zyamount from tb_goods goods inner join ( "+
// " select ly.*, isnull(bsamount,0) as bsamount,(ly.lyamount-isnull(bsamount,0))as zyamount from (select getuse.goodsid,count(getuse.number)"+
// " as lyamount,branch.name as branchname from tb_getuse getuse"+
// " inner join tb_branch branch on getuse.branchid=branch.id group by branch.name,getuse.goodsid) as ly left join "+
// " (select damage.goodsid,count(damage.damagenum) as bsamount,branch.name as branchname from tb_damage damage inner join"+
// " tb_branch branch on damage.branchid=branch.id and damage."+str+" group by branch.name,damage.goodsid) as bs on ly.goodsid=bs.goodsid and ly.branchname=bs.branchname) as totle on goods.id=totle.goodsid";
}
try {
SQLQuery query = session.createSQLQuery(sql);
list=query.list();
for(int i=0;i<list.size();i++){
System.out.println(list.get(i));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询时出现的错误信息：" + e.getMessage());
} finally {
MySession.closeSession(session); //关闭Session
}
return list;
}
//通用查询
public List stockQuery(IfForm ifForm, String tablename, int state) {
String str = ifCompose(ifForm);
System.out.println("STR=" + str + "******************" + tablename);
String hql = "";
List list = null;
if (str == null) {
if (state == 3) { //state为3时代表查询全部数据
hql = "From " + tablename + " order by createTime desc";
} else {
hql = "From " + tablename + " where state=" + state +
" order by createTime desc";
}
} else {
hql = "From " + tablename + " WHERE " + str +
" order by createTime desc";
}
session = MySession.openSession(); //打开Session
System.out.println("HQL=" + hql);
try {
Query query = session.createQuery(hql);
list = query.list();
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询时出现的错误信息：" + e.getMessage());
} finally {
MySession.closeSession(session); //关闭Session
}
return list;
}
/***************************组合查询条件字符串的方法***************************************/
//组合查询条件字符串的方法
public String ifCompose(IfForm ifForm) {
String str = null;
String flag[] = ifForm.getFlag(); //定义一个字符型数组，获取表单中复选框提交的数据
if (flag != null) {
str = "";
for (int i = 0; i < flag.length; i++) {
str = str + oneIf(ifForm, flag[i]);
if (i < flag.length - 1) {
str = str + " and ";
}
}
}
return str;
}
//根据传递的参数组合单个条件的方法
public String oneIf(IfForm ifForm, String flagv) {
String str = null;
if ("a".equals(flagv)) { //按指定字段查询
if (ifForm.getF() != null) {
str = ifForm.getF() + " like '%" +
ifForm.getKey() + "%'"; //此处需要进行转码
}
System.out.println("按指定字段查询：" + str);
}
if ("b".equals(flagv)) { //按时间段查询
String sdate = ifForm.getSdate();
String edate = ifForm.getEdate();
if (sdate != null && edate != null) {
str = "createTime between '" + sdate + "' and '" + edate +
" 23:59:59'";
}
System.out.println("日期" + str);
}
if ("c".equals(flagv)) { //按状态查询
int state = ifForm.getState();
str = "state=" + state + "";
System.out.println("状态：" + str);
}
return str;
}
/***************************************************************************/
}