求高手指点7张表的联合查询,都发了2天了,没人来么,高手哪里去了 本帖最后由 secondfirstlife 于 2013-08-22 23:14:09 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我晕说错了,是7张表,没人顶,我自己顶下,我写了一个方法不知道对不对,我用的是hibernate的jpa,代码如下:@Override public NewPageModule querySomeOne(String searchString, String informationTypeId, Integer dateType, Integer memberType,Integer pageSize) { String jpl="select i from Information i,InformationType it,UserCenterMember m "; StringBuffer sb=new StringBuffer(); InformationType informationType=informationTypeService.find(InformationType.class, informationTypeId); String ids=parseInformationTypevisitUrls(informationType.getStatus().getVisitUrl()); sb.append(jpl); if(GeneralUtil.isNotNull(searchString)){ sb.append(",InfoPropertyClass ipc,InfoPropertyName ipn, "); sb.append("InfoProperty ip,InfoPropertyValue ipv where "); sb.append("i.id=ipv.informationId and ipv.infoPropertyId=ip.id "); sb.append("and ip.infoPropertyNameId=ipn.id and ipn.infoPropertyClassId=ipc.id "); sb.append("and ipc.informationTypeId=ip.id "); sb.append("and i.informationTypeId=i.id and i.memberId=m.id "); sb.append("and ipv.value=?1 "); }else{ sb.append("where i.informationTypeId=i.id and i.memberId=m.id "); } sb.append("and it.status.visitUrl in("+ids+") "); if(GeneralUtil.isNotNull(memberType)){ sb.append("and m.status.isCommend=?2 "); } if(GeneralUtil.isNotNull(dateType)){ sb.append("and i.createTime >=?3 and i.createTime <=?4 "); } sb.append("order by i.status.isCommend desc,i.hits desc,i.createTime desc,i.id desc"); Query query=entityManager.createQuery(sb.toString()); if(GeneralUtil.isNotNull(searchString)){ query.setParameter(1, searchString); } if(GeneralUtil.isNotNull(memberType)){ query.setParameter(2, memberType);//个人还是商家 } if(GeneralUtil.isNotNull(dateType)){ Date now=new Date(System.currentTimeMillis()); if(dateType.intValue() == 1){//今天内 Date startDate=DateUtil .parseStringToDate(DateUtil.parseDateToString(now, "yyyy-MM-dd")+" 00:00:00" , "yyyy-MM-dd HH:mm:ss"); query.setParameter(3, startDate); }else if(dateType.intValue() == 2){//3天内 Calendar calendar = Calendar.getInstance(); Date date = calendar.getTime(); long lo = date.getTime()- 3*24*60*1000; date.setTime(lo); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date startDate=DateUtil .parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss"); query.setParameter(3, startDate); }else if(dateType.intValue() == 3){//一周内 Calendar calendar = Calendar.getInstance(); Date date = calendar.getTime(); long lo = date.getTime()- 7*24*60*1000; date.setTime(lo); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date startDate=DateUtil .parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss"); query.setParameter(3, startDate); }else if(dateType.intValue() == 4){//一个月内 Calendar calendar = Calendar.getInstance(); Date date = calendar.getTime(); long lo = date.getTime()- 30*24*60*1000; date.setTime(lo); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date startDate=DateUtil .parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss"); query.setParameter(3, startDate); }else if(dateType.intValue() == 5){//三个月内 Calendar calendar = Calendar.getInstance(); Date date = calendar.getTime(); long lo = date.getTime()- 90*24*60*1000; date.setTime(lo); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date startDate=DateUtil .parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss"); query.setParameter(3, startDate); } query.setParameter(4, now); } if(query.getResultList() != null && query.getResultList().size() > 0){ NewPageModule newpageModule = NewPageModuleUtil.createPage( pageSize, query.getResultList().size(), PageModuleContext .getOffset()); newpageModule.setPageList(query.setFirstResult( newpageModule.getBeginIndex()).setMaxResults( newpageModule.getPageSize()).getResultList()); return newpageModule; } return null; } 你这帖子让人没法看啊,本来看到这标题想帮你捧捧场,但是实在没法看我研究了半天都没弄清楚你要表达什么表结构你看这样表达表结构就清晰些:Table1{column1(PK)、column2、column3、column4、column5(FK)} jsp表单数据封装成对象再提交 struts2模型驱动提交,对象里有别的对象的数组引用,无法装填 为什么jsp中用jdbc连接数据库会有问题? 一个简单的日期生成问题 绝对挑战:google数据库面试题 如何用JAVA在客户端自动登录? 关于myEclipse连接Mysql找不到Driver类问题 将客户端的图片放到服务器端,不选择图片。 PHP or JSP 是个问题? Alfresco求教,有会的大神没,撒分了 javamail接收邮件中 内容中的图片无法显示 求解决......... 怎么在jsp中删除数据库中的字段?
@Override
public NewPageModule querySomeOne(String searchString,
String informationTypeId, Integer dateType, Integer memberType,Integer pageSize) {
String jpl="select i from Information i,InformationType it,UserCenterMember m ";
StringBuffer sb=new StringBuffer();
InformationType informationType=informationTypeService.find(InformationType.class, informationTypeId);
String ids=parseInformationTypevisitUrls(informationType.getStatus().getVisitUrl());
sb.append(jpl);
if(GeneralUtil.isNotNull(searchString)){
sb.append(",InfoPropertyClass ipc,InfoPropertyName ipn, ");
sb.append("InfoProperty ip,InfoPropertyValue ipv where ");
sb.append("i.id=ipv.informationId and ipv.infoPropertyId=ip.id ");
sb.append("and ip.infoPropertyNameId=ipn.id and ipn.infoPropertyClassId=ipc.id ");
sb.append("and ipc.informationTypeId=ip.id ");
sb.append("and i.informationTypeId=i.id and i.memberId=m.id ");
sb.append("and ipv.value=?1 ");
}else{
sb.append("where i.informationTypeId=i.id and i.memberId=m.id ");
}
sb.append("and it.status.visitUrl in("+ids+") ");
if(GeneralUtil.isNotNull(memberType)){
sb.append("and m.status.isCommend=?2 ");
}
if(GeneralUtil.isNotNull(dateType)){
sb.append("and i.createTime >=?3 and i.createTime <=?4 ");
}
sb.append("order by i.status.isCommend desc,i.hits desc,i.createTime desc,i.id desc");
Query query=entityManager.createQuery(sb.toString());
if(GeneralUtil.isNotNull(searchString)){
query.setParameter(1, searchString);
}
if(GeneralUtil.isNotNull(memberType)){
query.setParameter(2, memberType);//个人还是商家
}
if(GeneralUtil.isNotNull(dateType)){
Date now=new Date(System.currentTimeMillis());
if(dateType.intValue() == 1){//今天内
Date startDate=DateUtil
.parseStringToDate(DateUtil.parseDateToString(now, "yyyy-MM-dd")+" 00:00:00"
, "yyyy-MM-dd HH:mm:ss"); query.setParameter(3, startDate);
}else if(dateType.intValue() == 2){//3天内
Calendar calendar = Calendar.getInstance();
Date date = calendar.getTime();
long lo = date.getTime()- 3*24*60*1000;
date.setTime(lo);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date startDate=DateUtil
.parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss");
query.setParameter(3, startDate);
}else if(dateType.intValue() == 3){//一周内
Calendar calendar = Calendar.getInstance();
Date date = calendar.getTime();
long lo = date.getTime()- 7*24*60*1000;
date.setTime(lo);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date startDate=DateUtil
.parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss");
query.setParameter(3, startDate);
}else if(dateType.intValue() == 4){//一个月内
Calendar calendar = Calendar.getInstance();
Date date = calendar.getTime();
long lo = date.getTime()- 30*24*60*1000;
date.setTime(lo);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date startDate=DateUtil
.parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss");
query.setParameter(3, startDate);
}else if(dateType.intValue() == 5){//三个月内
Calendar calendar = Calendar.getInstance();
Date date = calendar.getTime();
long lo = date.getTime()- 90*24*60*1000;
date.setTime(lo);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date startDate=DateUtil
.parseStringToDate(sdf.format(date)+" 00:00:00", "yyyy-MM-dd HH:mm:ss");
query.setParameter(3, startDate);
}
query.setParameter(4, now);
}
if(query.getResultList() != null && query.getResultList().size() > 0){
NewPageModule newpageModule = NewPageModuleUtil.createPage(
pageSize, query.getResultList().size(), PageModuleContext
.getOffset());
newpageModule.setPageList(query.setFirstResult(
newpageModule.getBeginIndex()).setMaxResults(
newpageModule.getPageSize()).getResultList());
return newpageModule;
}
return null;
}
我研究了半天都没弄清楚你要表达什么表结构
你看这样表达表结构就清晰些:
Table1{column1(PK)、column2、column3、column4、column5(FK)}