Hibernate查询之SQL查询，查询结果用new新对象的方式接受，hql查询，通过SQL查询的结果返回到一个实体中，查询不同表中内容，并将查到的不同表中的内容放到List中



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;
}



//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;
}
    
}

时间： 2024-12-21 23:39:14

Hibernate查询之SQL查询，查询结果用new新对象的方式接受，hql查询，通过SQL查询的结果返回到一个实体中，查询不同表中内容，并将查到的不同表中的内容放到List中的相关文章

hibernate可不可以根据一个实体类查询出数据库中是否存在该记录

问题描述 hibernate可不可以根据一个实体类查询出数据库中是否存在该记录 需要判断一条记录是否已经在数据库中是否存在重复记录,用hql的话传入的参数会比较多

java-Java Hibernate中查询取值

问题描述 Java Hibernate中查询取值 Sql语法是这样的:![图片说明] 用Hibernate查询的语法不知道有没有问题呢? 还有最后一个问题: 我查询后的值存在query里面了, 我又要怎么取值呢? 求大家帮帮忙 解决方案 你那ID写里面就是死值了,要么用"?"代替,要么用 "+ +" 抱起来,哪有你那么写的 解决方案二: query.list()吧,然后看返回值是什么类型就用什么类型去接收就OK了.推荐看看这个先,里面有你想要的内容:http://z

PL/SQL DEVELOPER中查询结果复制出来中文乱码如何解决

PL/SQL DEVELOPER中查询结果导出到EXCEL时中文显示正常,但直接复制出来中文显示为乱码,如何? 解决方案:切换到中文输入法状态,再复制,即可. 本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Sybase ASA中查询元信息的几个常用SQL函数

Sybase ASA中查询元信息的几个常用SQL函数: sp_column_privileges Unsupported sp_columns table-name [, table-owner ] [, table-qualifier] [, column-name] 返回指定列的数据类型 sp_databases Unsupported sp_datatype_info Unsupported sp_fkeys pktable_name [, pktable-owner][, pktable

关联-关于oracle中查询sql的优化

问题描述 关于oracle中查询sql的优化 在数据库中的某个表中有几百万条数据,现在要用这张表关联别的表去查询,查询速度很慢,如何优化才能使查询速度快一些. 解决方案 先优化SQL,然后再优化业务逻辑.最后优化数据库数据存储.起码得把SQL贴出来看看吧,几百万的数据不算多呀. 解决方案二: oracle的权值查询 解决方案三: 用一个相关联的ID,直接查那个表,这样就简单了!

在 SQL Server 中查询EXCEL 表中的数据遇到的各种问题

原文:在 SQL Server 中查询EXCEL 表中的数据遇到的各种问题 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 问题:消息 15281,级别 16,状态 1,第 1 行 SQL Server 阻止了对组件 'Ad Hoc Dis

SQL 2005 中查询或执行另外的数据库操作的方法

原文:SQL 2005 中查询或执行另外的数据库操作的方法  摘要: 如果,你想在一台数据库服务器上,查询另一个台数据服务器的数据该如何做呢?如果,你想在同一台数据服务器上,在不同的数据库之间查询数据,又该怎么办呢? 1.Synonym 2.openquery 3.Linked Servers      Server Object-->Linked Servers--> serverType 选择 SQL Server, 在 Security 中 选择"Be made using t

sql server-SQL server2008中查询结果如何导出成excel，含字段名称标题列。

问题描述 SQL server2008中查询结果如何导出成excel,含字段名称标题列. 每次查询结果用点击左上角然后另存为CSV都不含有标题或者点击左上角右键选连同右键一起复制,但数据量多粘贴时很容易乱码和excel卡死. 解决方案 自己写一个程序,循环遍历下,在输出数据前先输出标题列.导出csv就是和写文本文件一样的道理.要避免乱码就是注意中文的编码,避免卡死就使用多线程. 解决方案二: 你可以选择输出结果到文件,而不是到Grid 输出文件的格式在选项中有设置,可以设置分隔符为逗号,并选择包

PHP中查询SQL Server或Sybase时TEXT字段被截断的解决方法_php技巧

Author: Wenlong Wu 一.针对MS SQL SERVER数据库 有两种解决方案,如下: 修改php.ini来实现: 打开php.ini,可看到mssql.textsize,mssql.textlimit两个选项: ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textlimit = 4096 ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textsize = 4