NutzCN Logo
问答 用自定义的SQL执行了一个多表查询,导致查询非常慢,TOMCAT自动中断了连接,急~~
发布于 2320天前 作者 qq_7fafbecc 1684 次浏览 复制 上一个帖子 下一个帖子
标签:

中间层代码:

	public NutMap queryBagInfo4Again(NutMap paramMap,Pager pager) {
		NutMap returnMap = new NutMap();
		List<DtoBag> rval = new ArrayList<DtoBag>();
		String sqlstr = "select a.BAGCODE,a.XYCODE,a.BDCODE,a.SPEC,a.UNIT,a.SOURCE,a.XYER,a.COLLECTIONTIME,a.VALIDDATETIME,"
				+ "(select userName from blood_dict_user c where c.userId = b.oprUser) OPRUSERTEXT, "
				+ "(select ABO  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) ABOPZ, "
				+ "(select RHD  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) RHDPZ ,"
				+ "b.ABO,b.RHD,b.OPRUSER,b.OPRTIME "
				+ "from BLOOD_DATA_BAGINFO a left join BLOOD_DATA_BAGBLOODAGAIN b on a.BAGCODE = b.BAGCODE where 1=1 ";
		//条件1
		if(paramMap.get("bagCode")!=null && !"".equals(paramMap.get("bagCode").toString().trim())){
			String bagCode = paramMap.get("bagCode").toString().trim();
			sqlstr = sqlstr + " and a.bagCode = '"+bagCode+"'";
		}
		//条件2,复检状态
		if(paramMap.get("cndAgainFlag")!=null && !"".equals(paramMap.get("cndAgainFlag").toString().trim())){
			String cndAgainFlag = paramMap.get("cndAgainFlag").toString().trim();
			if(Globals.BAG_AGAINFLAG_YES.equals(cndAgainFlag)){
				sqlstr = sqlstr + " and b.OPRUSER is not null";
			}else{
				sqlstr = sqlstr + " and b.OPRUSER is null";
			}
		}
		//条件3
		if(paramMap.get("cndBdType")!=null && !"".equals(paramMap.get("cndBdType").toString().trim())){
			String bdType = paramMap.get("cndBdType").toString().trim();
			sqlstr = sqlstr + " and a.BDCODE in (select BDCODE from BLOOD_DICT_BDSEGMENT c where c.BDTYPE = '"+bdType+"')";
		}
		
		//条件4
		if(paramMap.get("cndBdCode")!=null && !"".equals(paramMap.get("cndBdCode").toString().trim())){
			String cndBdCode = paramMap.get("cndBdCode").toString().trim();
			sqlstr = sqlstr + " and a.BDCODE = '"+cndBdCode+"'";
		}
		
		//条件5
		if(paramMap.get("cndInTimeBegin")!=null && !"".equals(paramMap.get("cndInTimeBegin").toString().trim())){
			String cndInTimeBegin = paramMap.get("cndInTimeBegin").toString().trim();
			sqlstr = sqlstr + " and b.INTIME >= '"+cndInTimeBegin+"'";
		}
		//条件6
		if(paramMap.get("cndInTimeEnd")!=null && !"".equals(paramMap.get("cndInTimeEnd").toString().trim())){
			String cndInTimeEnd = paramMap.get("cndInTimeEnd").toString().trim();
			sqlstr = sqlstr + " and b.INTIME <= '"+cndInTimeEnd+"'";
		}
		long total = Daos.queryCount(dao,sqlstr);
		//sqlstr = sqlstr + " order by a.intime desc ";
		returnMap.put("total", total);
		Sql sql = Sqls.create(sqlstr);
		sql.setPager(pager);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao.getEntity(DtoBag.class));
		dao.execute(sql);
		rval = sql.getList(DtoBag.class);
		returnMap.put("rows", rval);
		return returnMap;
	}

实体类:

public class DtoBag {
	
	//血袋编号
	@Readonly
	@Column("BAGCODE")
    private String bagCode;
	
	//来源代号
	@Readonly
	@Column("SOURCE")
    private String source;
	
	//血液成分
	@Readonly
	@Column("BDCODE")
    private String bdCode;
	
	//血型代号
	@Readonly
	@Column("XYCODE")
    private String xyCode;
	
	//规格
	@Readonly
	@Column("SPEC")
    private double spec;
	
	//单位
	@Readonly
	@Column("UNIT")
    private String unit;
	
	//采集时间
	@Readonly
	@Column("COLLECTIONTIME")
    private Date collectionTime;
	
	//失效时间
	@Readonly
	@Column("VALIDDATETIME")
    private Date validDateTime;	

后台的SQL

select a.BAGCODE,a.XYCODE,a.BDCODE,a.SPEC,a.UNIT,a.SOURCE,a.XYER,a.COLLECTIONTIME,a.VALIDDATETIME,(select userName from blood_dict_user c where c.userId = b.oprUser) OPRUSERTEXT, (select ABO  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) ABOPZ, (select RHD  from BLOOD_DICT_ABORHD c where c.XYCODE = a.XYCODE) RHDPZ ,b.ABO,b.RHD,b.OPRUSER,b.OPRTIME from BLOOD_DATA_BAGINFO a left join BLOOD_DATA_BAGBLOODAGAIN b on a.BAGCODE = b.BAGCODE where 1=1  order by a.intime desc

描述:ECLIPSE跟断点,一直卡在dao.execute(sql);这句,要7、8分钟才出来,而把SQL语句截出来,在sqlserver中跑,是非常快的,不存在瓶颈
很困惑,难道是这个JAVA实体类有问题?
从未使用NUTZ过程中碰到过这种问题??

13 回复

数据量很大?

我是说结果集

我分页了,20条,但是total总数2W多条

不加回调,看看要多久

怎么让DAO不加回调?

sql.setCallback注释掉

快很多,停顿个2、3秒,然后就执行完dao.execute(sql)这句了

看来并非你说的20条记录

可是我明明分了页啊

打印一下pager

我错了,前台没传分页参数过来,前台代码被人改掉了,浪费好多时间,哎

防守一下嘛

添加回复
请先登陆
回到顶部