本篇用於統合曾經見過的SELECT寫法
可根據物件的欄位是否有值去加入到DB的statement(動態改變)
便利程度：★★★★(4/5滿分五顆星)
缺點：append的地方寫死了，這樣如果欄位超多的話 會累爆吧!!!
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
| private StringBuffer method(javaEntity argVO){
StringBuffer stmt = new StringBuffer();
UsersVo temp = (UsersVo) argVO;
if (temp != null)
{
StringBuffer sb = new StringBuffer();
if (temp.getUSERSID() != 0)
{
sb.append(" and UsersID=? ");
}
if (temp.getGROUPID() != 0)
{
sb.append(" and GROUPID=? ");
}
if (temp.getACCOUNT() != null
&& !temp.getACCOUNT().trim().equals(""))
{
sb.append(" and ACCOUNT=?");
}
if (temp.getDEPART() != null && !temp.getDEPART().trim().equals(""))
{
sb.append(" and DEPART=?");
}
if (temp.getPASWD() != null && !temp.getPASWD().trim().equals(""))
{
sb.append(" and PASWD=?");
}
if (temp.getISSECURITY() != null
&& !temp.getISSECURITY().trim().equals(""))
{
sb.append(" and ISSECURITY=?");
}
if (temp.getISSUPER() != null
&& !temp.getISSUPER().trim().equals(""))
{
sb.append(" and ISSUPER=?");
}
if (sb.length() > 0)
{
stmt.append(" where 1=1 ").append(sb.toString());
}
}
stmt.append(" order by UsersID");
return (stmt);
}
|
可根據物件值的特性，字串或數字或文字比對(動態改變)
便利程度:★★★★★(5/5滿分五星)
缺點：幾乎沒有缺點，這種方式非常方便，根據DB欄位名的key加上要塞入的value組成的Map。
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
| public List<Process> search(Map<String, Object> inquiry, Info info) throws ApplicationException {
try {
StringBuilder hql = new StringBuilder("from Process where dataStatus <> ?");
ArrayList<Object> values = new ArrayList<Object>();
values.add(PlatformProperty.DataStatus.DELETE.ordinal());
String cndi = " and ";
for (String column : inquiry.keySet()) {
Object data = inquiry.get(column);
if (data == null) {
continue;
}
if (data.getClass().isArray()) {
Object[] value = (Object[]) data;
hql.append(cndi).append(column).append(" in (").append(repeat("?", ", ", value.length)).append(") ");
values.addAll(Arrays.asList(value));
cndi = " and ";
} else if (data instanceof Integer) {
hql.append(cndi).append(column).append(" = ? ");
values.add(data);
cndi = " and ";
} else {
String value = (String) data;
if (StringUtils.isBlank(value)) {
continue;
}
hql.append(cndi).append(column);
hql.append(value.contains("%") ? " like ? " : " = ? ");
values.add(value);
cndi = " and ";
}
}
hql.append(" order by code");
return super.findByHQL(hql.toString(), values, info);
} catch (Exception e) {
throw new ApplicationException(e, info);
}
}
|