public static final String SQL =
"SELECT trunc(t.xxxxxxx) AS 日期,\n" +
" SUM(t.xxxxxxxx * t.xxxxxxxxx) AS 总价,\n" +
" SUM(t.xxxxxxxxxxxxx) AS 总数,\n" +
" SUM(t.xxxxxxxxx * t.xxxxxxxxx) / SUM(t.xxxxxxxxx) AS 均价\n" +
" FROM xxxxxxxxxxxxxx t\n" +
" WHERE t.xxxxxxxx = ?\n" +
" AND t.xxxxxxxxxxx >=\n" +
" ?\n" +
" AND t.xxxxxxxxxx <=\n" +
" ?\n";
public List<Map<String, Object>> find(String type1, Date beginDate, Date endDate) {
List<Object> params = new ArrayList<Object>();
params.add(type1);
params.add(beginDate);
params.add(endDate);
Object[] values = params.toArray(new Object[params.size()]);
if (log.isDebugEnabled()) {
log.debug("===========> sql: " + SQL);
log.debug("===========> values: " + ArrayUtils.toString(values));
}
return findBySQLQuery(SQL, values);
}
public List<Map<String, Object>> findBySQLQuery(final String sql, final Object[] values) {
return (List<Map<String, Object>>) (getHibernateTemplate().executeWithNativeSession(
new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws
HibernateException {
Query query = session.createSQLQuery(sql)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
if (values != null) {
for (int i = 0; i < values.length; i++) {
query.setParameter(i, values[i]);
}
}
List results = query.list();
return results;
}
}));
}
public static final String SQL =
"SELECT trunc(t.xxxxxxx) AS 日期,\n" +
" SUM(t.xxxxxxxx * t.xxxxxxxxx) AS 总价,\n" +
" SUM(t.xxxxxxxxxxxxx) AS 总数,\n" +
" SUM(t.xxxxxxxxx * t.xxxxxxxxx) / SUM(t.xxxxxxxxx) AS 均价\n" +
" FROM xxxxxxxxxxxxxx t\n" +
" WHERE t.xxxxxxxx = ?\n" +
" AND t.xxxxxxxxxxx >=\n" +
" ?\n" +
" AND t.xxxxxxxxxx <=\n" +
" ?\n";
public List<Map<String, Object>> find(String type1, Date beginDate, Date endDate) {
List<Object> params = new ArrayList<Object>();
params.add(type1);
params.add(beginDate);
params.add(endDate);
Object[] values = params.toArray(new Object[params.size()]);
if (log.isDebugEnabled()) {
log.debug("===========> sql: " + SQL);
log.debug("===========> values: " + ArrayUtils.toString(values));
}
return findBySQLQuery(SQL, values);
}
public List<Map<String, Object>> findBySQLQuery(final String sql, final Object[] values) {
return (List<Map<String, Object>>) (getHibernateTemplate().executeWithNativeSession(
new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws
HibernateException {
Query query = session.createSQLQuery(sql)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
if (values != null) {
for (int i = 0; i < values.length; i++) {
query.setParameter(i, values[i]);
}
}
List results = query.list();
return results;
}
}));
}
数据库移植是瞎扯淡,就像一次编码到处运行一样。
|