项目上都是利用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)
但是我直接在数据库是有返回结果集的可以看到
