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
@Transactional
public 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;
}
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;
}
}