package com.ucap.netcheck.dao.impl;
import java.util.ArrayList;import java.util.List;
import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import org.springframework.transaction.annotation.Transactional;
import com.ucap.netcheck.combination.beans.MainPageResultCombinationBean;import com.ucap.netcheck.common.Page;import com.ucap.netcheck.common.dao.BaseDAO;import com.ucap.netcheck.dao.ISingleRejectDao;import com.ucap.netcheck.entity.MainPageScanFail;import com.ucap.netcheck.entity.MainPageScanResult;import com.ucap.netcheck.exception.DAOException;
/** * @Title: CheckMainPageResultDaoImpl.java * @Package com.ucap.netcheck.dao.impl * @Description: * @author * @date 2015-4-8 下午5:04:34 * @version V1.0 */@Repository@Transactionalpublic class SingleRejectDaoImpl extends BaseDAO implements ISingleRejectDao {
@Autowired private SessionFactory sessionFactory; @SuppressWarnings("unchecked") public MainPageScanResult queryMainPageScanResultUnique(Object... params) { try { String sql = "FROM MainPageScanResult m " + "WHERE m.errorPercent > 5.0 " + "and m.innerUUID =? " + "and m.taskId =? "; Session session = sessionFactory.getCurrentSession(); List<MainPageScanResult> list = session.createQuery(sql) .setParameter(0,(String)params[0]) .setParameter(1,(Integer)params[1]) .list(); if (!list.isEmpty()) { return list.get(0); } } catch (Exception e) { e.printStackTrace(); } return null; } /** * queryMainPageScanFailById(通过这个方法实现查找首页扫描失败的结果) * * @Title: queryMainPageScanFailById * @Description: * @param @param pageNo * @param @param pageRow * @param @param id * @param @return 设定文件 * @return Page 返回类型 * @throws */ public Page queryMainPageScanFailById(int pageNo, int pageRow,Integer id) { try { List<Object> params = new ArrayList<Object>(); String hql = "FROM MainPageScanFail m WHERE m.mainPageScanId = ? "; params.add(id); Page page = this.queryByPage(hql, pageNo, pageRow, params); return page; } catch (Exception e) { throw new DAOException("分页查询所有->ERROR", e); } } @SuppressWarnings("unchecked") public List<MainPageScanFail> queryMainPageScanFailListById(Integer id) { try { String hql = "FROM MainPageScanFail m WHERE m.mainPageScanId =:mainPageScanId "; List<MainPageScanFail> list = sessionFactory.getCurrentSession() .createQuery(hql) .setParameter("mainPageScanId", id) .list(); return list; } catch (Exception e) { e.printStackTrace(); } return null; }
/** * queryMainPageScaneResultStatistics(这个方法实现对这个表的信息进行统计) * * @Title: queryMainPageScaneResultStatistics * @Description: 这个方法实现对这个表的信息进行统计 * @param @param objs * @param @return 设定文件 * @return Page<MainPageScanResult> 返回类型 * @throws */ @SuppressWarnings("unchecked") public MainPageResultCombinationBean queryMainPageScaneResultStatistics( Object... objs) { try { String hql = "SELECT new com.ucap.netcheck.combination.beans.MainPageResultCombinationBean(" + " min(m.taskStartTime),max(m.taskEndTime),count(m.innerUUID),max(m.totalScanNum),max(m.failNum)) " + " FROM " + MainPageScanResult.class.getSimpleName() + " m WHERE m.innerUUID = ? " + " AND m.taskId = ? ";
List<MainPageResultCombinationBean> list = sessionFactory .getCurrentSession().createQuery(hql) .setParameter(0, (String) objs[0]).setParameter(1, objs[1]) .list(); return (!list.isEmpty() ? (MainPageResultCombinationBean) list .get(0) : null); } catch (Exception e) { throw new DAOException("分页查询所有->ERROR", e); } }
@SuppressWarnings({ "rawtypes", "unchecked"}) @Override public Page queryMainPageUpdateResultByPage(int pageNo, int pageRow, Object... objs) { try { StringBuilder sql = new StringBuilder( "SELECT " + "cm.id AS id," + "cm.TASK_Id AS taskId," + "cm.innerUUID AS innerUUID," + "cm.checkSysTaskId AS checkSysTaskId," + "cm.URL AS URL," + "cm.first_channel AS firstChannel," + "cm.second_channel AS secondChannel," + "cm.chanel_name AS chanelName," + "cm.TITLE AS title," + "cm.IMG_Url AS imgURL," + "cm.lastModifyTime AS lastModifyTime, " + "cm.SHOW_TIME AS showTime," + "cm.scanTime AS scanTime," + "cm.isReject AS isReject " + "FROM cpu_mp AS cm " + "WHERE cm.innerUUID =? " + "AND cm.TASK_ID =? " + "AND cm.isReject = 1 " ); Query query = sessionFactory .getCurrentSession() .createSQLQuery(sql.toString()) .setParameter(0, (String) objs[0]) .setParameter(1, (Integer)objs[1]);
Page page = new Page(); page.setPageNum(pageNo); page.setNumPerPage(pageRow); page.setAllRows(query.list().size()); int allPages = page.getAllRows() / page.getNumPerPage(); if (page.getAllRows() % page.getNumPerPage() == 0) { page.setAllPages(allPages); // 总页数 } else { page.setAllPages(allPages + 1); // 总页数 } query.setFirstResult((pageNo - 1) * pageRow); query.setMaxResults(pageRow); //集合 List<Object[]> list = query.list(); page.setRecordList(list); return page; } catch (Exception e) { throw new DAOException("分页查询所有->ERROR", e); } } @SuppressWarnings({"unchecked"}) @Override public List<Object[]> queryMainPageUpdateResultByCondition(Object... objs) { try { StringBuilder sql = new StringBuilder( "SELECT " + "cm.id AS id," + "cm.TASK_Id AS taskId," + "cm.innerUUID AS innerUUID," + "cm.checkSysTaskId AS checkSysTaskId," + "cm.URL AS URL," + "cm.first_channel AS firstChannel," + "cm.second_channel AS secondChannel," + "cm.chanel_name AS chanelName," + "cm.TITLE AS title," + "cm.IMG_Url AS imgURL," + "cm.lastModifyTime AS lastModifyTime, " + "cm.SHOW_TIME AS showTime," + "cm.scanTime AS scanTime," + "cm.isReject AS isReject " + "FROM cpu_mp AS cm " + "WHERE cm.innerUUID =? " + "AND cm.TASK_ID =? " + "AND cm.isReject = 1 " ); Query query = sessionFactory .getCurrentSession() .createSQLQuery(sql.toString()) .setParameter(0, (String) objs[0]) .setParameter(1, (Integer)objs[1]);
//集合 List<Object[]> list = query.list(); return list; } catch (Exception e) { e.printStackTrace(); } return null; }
}
========================================================
通过SQL将查询的结果返回到一个Bean中
@SuppressWarnings({ "unchecked" }) @Override @Transactional(propagation = Propagation.REQUIRED) public Page<CommunityActivity> queryJoinedActivity(int pageNo, int pageRow, int userId) { // 分页实体 Page<CommunityActivity> page = new Page<CommunityActivity>(); try { page.setPageNum(pageNo); page.setNumPerPage(pageRow); Session session = sessionFactory.getCurrentSession(); String sql = "select ac.* from dm_commuinty_activity" + " ac where ac.id in(select t.activity_id from DM_ACTIVITY_USER t " + " where t.user_id = " + userId + ")";
// 查询 Query query = session.createSQLQuery(sql).addEntity( CommunityActivity.class);
// 总记录数 page.setAllRows(query.list().size()); // page.setAllRows(((Number) query.uniqueResult()).intValue());
query.setFirstResult((pageNo - 1) * pageRow); query.setMaxResults(pageRow); // 集合 page.setRecordList(query.list());
int allPages = page.getAllRows() / page.getNumPerPage(); if (page.getAllRows() % page.getNumPerPage() == 0) { page.setAllPages(allPages); // 总页数 } else { page.setAllPages(allPages + 1); // 总页数 } } catch (Exception ex) { throw new DAOException("分页查询所有->ERROR", ex); } return page; }===========================
查询不同表中内容，并将查到的不同表中的内容放到List中
@Override @Transactional(propagation = Propagation.REQUIRED) public List getAttrAndDeadResult(int taskId, int targetID) { Session session = sessionFactory.getCurrentSession();// String sql = "select d.depth, p.imgUrl, d.point, d.scanTime, "// + "d.title, d.url, p.parentTitle, p.parentUrl,d.id,d.urlType,d.code "// + "from deadurlresult d, checkresult c, deadurl_parent_info p "// + "where d.taskId=c.taskId and d.id=p.deadId and d.taskId=:tid and c.targetId=:tgID "// + "and (d.urlType=0 or d.urlType=1 or d.urlType=2 or d.urlType=3 "// + "or d.urlType=4 or d.urlType=7 or d.urlType=8) "; String sql = "SELECT d.depth, p.imgUrl, d.point, d.scanTime, d.title, d.url, p.parentTitle, p.parentUrl,d.id,d.urlType,d.code " + "FROM " + "(SELECT * FROM deadurlresult c WHERE " + "c.taskId=:tid " + "AND (c.urlType=0 OR c.urlType=1 OR c.urlType=2 OR c.urlType=3 " + "OR c.urlType=4 OR c.urlType=7 OR c.urlType=8)" + ") d LEFT JOIN deadurl_parent_info p ON d.id=p.deadId"; Query sqlQuery = session.createSQLQuery(sql); sqlQuery.setParameter("tid", taskId); //sqlQuery.setParameter("tgID", targetID); return sqlQuery.list(); }
=================================================================
/** * 通过这方法实现爆粗广告日志信息 */ @Transactional(propagation = Propagation.REQUIRED) public List<AdvertiseStatisticResult> gainStatisticResult(Integer adPageId, Date startDate, Date endDate) { try { List<AdvertiseStatisticResult> dataResults = new ArrayList<AdvertiseStatisticResult>(); String sql = "select t2.ad_position_name,count(t.AD_POS_ID) " + "from DM_ADVERTISE_ACCESS_LOG t,Dm_Advertise_Position t2 " + "WHERE t.adpageid = ? " + "AND t2.id = t.ad_pos_id " + "group by t2.ad_position_name,t.ad_pos_id"; List list = sessionFactory.getCurrentSession().createSQLQuery(sql) .setParameter(0, adPageId) //.setParameter(1, startDate) //.setParameter(2, endDate) .list(); if (!list.isEmpty()) { for (int i = 0; i < list.size(); i++) { AdvertiseStatisticResult asr = new AdvertiseStatisticResult(); asr.setName((String)((Object[])list.get(i))[0]); asr.setY(Integer.valueOf((((Object[])list.get(i))[1]).toString()).intValue()); //这里的Y是int类型的，这里不能直接通过（Integer）的方式进行强制转换得到，否则出现
//java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.Integer dataResults.add(asr); } } return dataResults; } catch (Exception e) { e.printStackTrace(); } return null; }
========================================================================================================== 查询的内容转成制定的实体： // 附件 @Override @Transactional(propagation = Propagation.REQUIRED) public List<AffixBean> getAffix() { Session session = sessionFactory.getCurrentSession(); String hql = "select a.nrid,a.file_type,a.file_path,a.file_name from dm_affix a"; Query query = session.createSQLQuery(hql) .addScalar("nrid", StandardBasicTypes.INTEGER) .addScalar("file_type", StandardBasicTypes.STRING) .addScalar("file_path", StandardBasicTypes.STRING) .addScalar("file_name", StandardBasicTypes.STRING) .setResultTransformer(Transformers.aliasToBean(AffixBean.class)); List<AffixBean> list = query.list(); return list; } //AffixBean的内容如下： package com.kuman.cartoon.common.bean; public class AffixBean { /** * 内容ID */ private Integer nrid; /** * 文件路径 */ private String file_path; /** * 文件类型 */ private String file_type; /** * 文件名 */ private String file_name; public Integer getNrid() { return nrid; } public void setNrid(Integer nrid) { this.nrid = nrid; } public String getFile_path() { return file_path; } public void setFile_path(String file_path) { this.file_path = file_path; } public String getFile_type() { return file_type; } public void setFile_type(String file_type) { this.file_type = file_type; } public String getFile_name() { return file_name; } public void setFile_name(String file_name) { this.file_name = file_name; } } 相关资源：hibernate将本地SQL查询结果封装成对象（最终）