meishangliang
2016-06-15 06:35
采纳率: 33.3%
浏览 1.5k
已采纳

利用hibernate执行原始的sql,但是在java里面提示没有返回结果集

项目上都是利用hibernate的原始态调用sql,由于涉及的表比较多,所以在写sql查询出来的速度很慢,我们在优化的时候,把一些表的结果集放在临时表上了,这样提升了一些速度。但是我现在在java的源代码上拿不到结果集

原始的sql

select --商业分布统计
count(distinct vs.data_id ) totalNum ,
count(distinct vs.cell_id) cellNum,
v.level as lev
from
shannxi.dbo.v_cell_level v ,
shannxi.dbo.t_cell_value vs

where
vs.data_type = 1
AND v.ID=vs.cell_id
and vs.data_id in(
SELECT DISTINCT nn.id as stationId from
shannxi.dbo.bus_line bl,
shannxi.dbo.bus_station bs,
shannxi.dbo.bus_station_new nn
where bl.id in(select projectid from t_schememedia t where t.schemeid = 2) AND
bl.busid=bs.bus_id AND
bs.station_name = nn.station_name AND
bs.kind=nn.kind AND
abs(bs.lng-nn.lng)<0.05 AND
abs(bs.lat-nn.lat)<0.05
) and v.level is not null
group by v.level

现在利用临时表的sql

DECLARE @line table (row_id int IDENTITY(1,1),ID int ,busid varchar(50),key_name varchar(100) )
INSERT INTO @line(id,busid,key_name)
select ll.id,ll.busid,ll.key_name from newdection.dbo.t_schememedia sc
INNER JOIN shannxi.dbo.bus_line ll ON ll.id=sc.projectid WHERE schemeid=40
DECLARE @station table (row_id int IDENTITY(1,1) ,ID int ,station_name varchar(50) ,lng decimal(18,8),lat decimal(18,8),
regionid int, station_code varchar(20),level varchar(8),kind varchar(10),neighborhood_id int )
INSERT INTO @station( id,station_name,lng,lat,regionid,station_code,kind,level,neighborhood_id)
select distinct nn.id,nn.station_name,nn.lng,nn.lat,nn.regionid,nn.station_code,nn.kind,
nn.level,nn.neighborhood_id from (select ll.id,ll.busid,ll.key_name
from newdection.dbo.t_schememedia sc
INNER JOIN shannxi.dbo.bus_line ll ON ll.id=sc.projectid WHERE schemeid=40) ll
INNER JOIN shannxi.dbo.bus_station bs on ll.busid=bs.bus_id
INNER join shannxi.dbo.bus_station_new nn ON bs.station_name=nn.station_name

AND abs(bs.lng-nn.lng)<0.05 AND abs(bs.lat-nn.lat)<0.05
AND bs.kind=nn.kind

select cl.hous_level as lev, ss.num ,
COUNT(DISTINCT nn.ID ) totalNum ,
COUNT(DISTINCT cl.ID ) cell from @station nn
INNER JOIN shannxi.dbo.t_cell_value cc ON cc.data_type=1
AND cc.data_id=nn.ID
INNER join shannxi.dbo.v_cell_level cl ON cc.cell_id=cl.ID
AND cl.hous_level>=1,
(select COUNT(DISTINCT nn.ID ) num from @station nn
INNER JOIN shannxi.dbo.t_cell_value cc ON cc.data_type=1
AND cc.data_id=nn.ID INNER join shannxi.dbo.v_cell_level cl ON cc.cell_id=cl.ID
WHERE cl.hous_level>=1) ss
group by cl.hous_level , ss.num

结果在hibernate执行
Query query = super.getCurrentSession().createSQLQuery(sql.toString());
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
@SuppressWarnings("unchecked")
List> Typelist = query.list();
出现的结果错误是
hprose.common.HproseException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
但是我直接在数据库是有返回结果集的可以看到

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • meishangliang 2016-06-15 07:41
    已采纳

    刚才看了一下别人关于hibernate拿不到的结果集的问题,看了一下,解决了,,那是因为我优化的sql上有插入临时表的语句,所以就拿不到结果集。我个人猜想是不是hibernate在得到记录数据时判断有无影响行。 解决的办法是 在sql 的最前面 添加set nocount on 在最后面 添加set nocount off

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题