java怎么实现查询出符合条件的数据返回到页面显示

[u010086518]

public Page queryAll(LzInformationSources informationSources,LzAdmin admin)
{
String insCompany_Q = informationSources.getInsCompany_Q();
List<Object> paraList = new ArrayList<Object>();

StringBuilder sql = new StringBuilder(
" select"
+" a.INS_ID,"
+" hh.numbers, " //号码
+" a.INS_FREQUENCY, " //频率
+" a.INS_COMPANY," //企业名称
+" a.INS_CONTACT_NAME," //联系人姓名
+" a.INS_SEX," //性别
+" ff.names ," //产品
+" a.INS_QQ," //qq
+" d.ZHN_FUNCTION ," //职能
+" e.ZHW_POSITION ," //职位
+" f.phone_fen "
+" from LZ_INFORMATION_SOURCES a "
+" left join "
+" ( "
+" select distinct gg.TEL_INS_ID, "
+" ("
+" select kk.TEL_NUM + ',' "
+" from LZ_TELNUMBER kk "
+" where kk.TEL_INS_ID = gg.TEL_INS_ID "
+" for xml path('')"
+" ) numbers "
+" from LZ_TELNUMBER gg "
+" ) hh on a.INS_ID = hh.TEL_INS_ID "
+" left join LZ_ZHINENG d on a.ZHN_ID = d.ZHN_ID "
+" left join LZ_ZHIWEI e on a.ZHW_ID = e.ZHW_ID "
+" left join lz_admin f on a.admin_id = f.admin_id "
+" left join "
+" ( "
+" select distinct aa.INS_ID, "
+" ("
+" select dd.pro_cn_name + ',' "
+" from LZ_INFOR_PRO cc "
+" join lz_product dd on cc.pro_id = dd.pro_id "
+" and cc.INS_ID = aa.INS_ID "
+" for xml path('')"
+" ) names "
+" from LZ_INFOR_PRO aa "
+" join lz_product bb on aa.pro_id = bb.pro_id "
+" ) ff on a.INS_ID = ff.INS_ID "
+" where a.admin_id in ("+ directAdminString+")"
);

StringBuilder sqlCnt = new StringBuilder(
" select count(*) "
+" from LZ_INFORMATION_SOURCES a "
+" left join "
+" ( "
+" select distinct gg.TEL_INS_ID, "
+" ("
+" select kk.TEL_NUM + ',' "
+" from LZ_TELNUMBER kk "
+" where kk.TEL_INS_ID = gg.TEL_INS_ID "
+" for xml path('')"
+" ) numbers "
+" from LZ_TELNUMBER gg "
+" ) hh on a.INS_ID = hh.TEL_INS_ID "
+" left join LZ_ZHINENG d on a.ZHN_ID = d.ZHN_ID "
+" left join LZ_ZHIWEI e on a.ZHW_ID = e.ZHW_ID "
+" left join lz_admin f on a.admin_id = f.admin_id "
+" left join "
+" ( "
+" select distinct aa.INS_ID, "
+" ("
+" select dd.pro_cn_name + ',' "
+" from LZ_INFOR_PRO cc "
+" join lz_product dd on cc.pro_id = dd.pro_id "
+" and cc.INS_ID = aa.INS_ID "
+" for xml path('')"
+" ) names "
+" from LZ_INFOR_PRO aa "
+" join lz_product bb on aa.pro_id = bb.pro_id "
+" ) ff on a.INS_ID = ff.INS_ID "
+" where a.admin_id in ("+ directAdminString+") "
);

if( insCompany_Q != null && insCompany_Q.length() > 0 )
{
sql.append(" and a.INS_COMPANY like ? ");
sqlCnt.append(" and a.INS_COMPANY like ? ");
paraList.add("%" + insCompany_Q + "%");
}
sql.append(" order by a.INS_ID desc ");

Page page = new Page(sql.toString(), sqlCnt.toString(),
informationSources.getCurrentPage(), informationSources.getPageSize(),
paraList.toArray());
page.setPageSize(1000);
jdbcDao.queryForPage(page);
if(page.getResultList().size() == 0)
{
return page;
}
String sql1_2 ="";
for(int i =0 ; i < page.getResultList().size(); i++)
{
String startime = "";
String endtime = "";
Calendar jintian = Calendar.getInstance();
Calendar mingtian = Calendar.getInstance();

//电话号码
String number = page.getResultList().get(i).get("numbers").toString().trim().substring(0, page.getResultList().get(i).get("numbers").toString().length()-1);
String[] arr_numbers = number.split(",");
String panduan = "";
StringBuilder arrNumString = new StringBuilder(" and (");
for(int q = 0; q < arr_numbers.length; q++)
{
String num = arr_numbers[q].trim();
num = num.replace("-", "");
panduan += "( a.cal_other_code = '" + num + "' and a.CAL_CODE_INDEX = '" + num.substring(num.length()-4, num.length()) + "') or" ;
}
panduan = panduan.substring(0, panduan.length()-4 );
arrNumString = arrNumString.append(panduan);
arrNumString.append("))");
//从日志表中查询所需要的两列数据的sql
String sql1_1=
" select "
+ page.getResultList().get(i).get("INS_ID").toString() +" as INS_ID ,"
+" isnull(case when COUNT(*) = 0 then 'a1' end ,0 ) as er"
+" from LZ_CALL_LOG a "
+" where a.CAL_CALL_DIRECT = 2 "
+" and a.CAL_CONNECT_TIME_LEN > 0 "
+" and a.CAL_EXT_CODE = " + page.getResultList().get(i).get("phone_fen") +""
+ arrNumString
+" and a.CAL_DATE_INDEX > = " + startime + " and a.CAL_DATE_INDEX < " + endtime+"" ;
sql1_2 += (sql1_1 + " union all");
}
sql1_2 = sql1_2.substring(0, sql1_2.length()-9);
List<Map<String,Object>> lst = jdbcDao.queryForList(sql1_2);
List<Map<String, Object>> lstPageList = page.getResultList();
for(int g = 0; g < lstPageList.size(); g++)
{
Map<String ,Object > mapa = lstPageList.get(g);
String[] telnum = mapa.get("numbers").toString().trim().split(",");
String lianjie = "";
for(int q = 0 ; q < telnum.length ; q++)
{
lianjie += " <a href=\"javascript:Ext_NumCall('"+telnum[q].trim()+"');\">"+telnum[q].trim()+"</a>, " ;
}
mapa.put("numbers", lianjie);
for(int n = 0;n < lst.size();n++ )
{
Map<String ,Object > mapb = lst.get(n);
if(((Integer)mapb.get("INS_ID")).compareTo((Integer) mapa.get("INS_ID"))==0)
{
mapa.put("er", (String)mapb.get("er"));
mapa.put("cishu", (Integer)mapb.get("cishu"));
}
}
if(mapa.get("er").toString().compareTo("a1")==0)
{
mapa.put("numbers", lianjie);
mapa.put("INS_ID", mapa.get("INS_ID"));
lstPageList.set(g, mapa);
}
//这个是查询出所有没有打过的电话
}
page.setResultList(lstPageList);
return page;
}



想问一下，最后怎么实现返回到页面上是所有没有打过电话的数据，包括统计出来的条数

[u010086518]

引用 3 楼 ky_min 的回复:

不好意思，楼主筛选一下需要用到的字段，或者不筛选也可以，
重要的是，给些测试数据，然后期望结果

满足以上条件的话，来论坛是秒解决问题的~~

希望点击完显示未拨打号码后，那些已拨打的所有数据都隐藏了

[u010086518]

就只能看到这种的数据

[还在加载中灬]

你还是把你的表结构贴出来吧

[u010086518]

引用 1 楼 ky_min 的回复:

你还是把你的表结构贴出来吧

一共有两个表 /*呼叫系统记录日志表*/ /**/ if exists (select * from sysobjects where id = object_id(N'[LZ_CALL_LOG]') and OBJECTPROPERTY(id, N'IsUserTable')= 1) DROP TABLE LZ_CALL_LOG GO CREATE TABLE LZ_CALL_LOG( CAL_ID INT not null identity(1,1) , CAL_DATE_INDEX VARCHAR(10) , CAL_CODE_INDEX VARCHAR(10) , CAL_LOG_AREA CHAR(1) , CAL_CALL_DATE_TIME DATETIME , CAL_CALL_DIRECT INT , CAL_CALL_INDEX INT , CAL_CALL_RESULT INT , CAL_CALL_TIME_LEN INT , CAL_CONNECT_DATE_TIME DATETIME , CAL_CONNECT_TIME_LEN INT , CAL_DISCONNECT_TIME DATETIME , CAL_EXT_CODE VARCHAR(20) , CAL_CALL_ID INT , CAL_OTHER_CODE VARCHAR(200) , CAL_SESSION_ID VARCHAR(100) , CAL_DESC VARCHAR(400) , CAL_REGISTOR INT , CAL_RECORD_FILE VARCHAR(200) , CAL_REGISTDATE DATETIME default getdate() , CAL_IS_INSIDE CHAR(1) , CAL_IS_LOCAL CHAR(1) , CAL_IS_MOBILE CHAR(1) , CAL_URL VARCHAR(200) PRIMARY KEY(CAL_ID)); /*信息源*/ /**/ if exists (select * from sysobjects where id = object_id(N'[LZ_INFORMATION_SOURCES]') and OBJECTPROPERTY(id, N'IsUserTable')= 1) DROP TABLE LZ_INFORMATION_SOURCES GO CREATE TABLE LZ_INFORMATION_SOURCES( INS_ID INT NOT NULL identity(1,1) , INS_COMPANY VARCHAR(100) NOT NULL, INS_CONTACT_NAME VARCHAR(100) NOT NULL, INS_FUNCTION VARCHAR(100) NOT NULL, INS_POSITION VARCHAR(100) NOT NULL, INS_EDITOR VARCHAR(50) NOT NULL, INS_DESC VARCHAR(400) , INS_CREAT_TIME DATETIME NOT NULL default getdate() , INS_UPD_TIME DATETIME default getdate() , INS_UPD_REGISTOR INT PRIMARY KEY(INS_ID)); GO

[还在加载中灬]

不好意思，楼主筛选一下需要用到的字段，或者不筛选也可以， 重要的是，给些测试数据，然后期望结果 满足以上条件的话，来论坛是秒解决问题的~~

[u010086518]

已成功自己解决，ok

[zhoujingzhoujing1]

怎么解决的啊？