[SEAM] 多条件查询的类/方法怎么写
 |
无双Rama
2009-03-17
我的查询条件有11个，当没有条件的时候 是查询所有的，有一个时，查询语句会加上where，有多个时 条件之间会加上and , 各条件允许为空 该怎么写呢 |
|
无双Rama
2009-03-17
public void showTable(){
String stdPN=""; String mfr=""; String mfrPN=""; String mfrPNDes=""; String state=""; String customer=""; String customerPN=""; String creator=""; String creatorDate=""; String buPN=""; String projectCode=""; boolean havawhere=false; String iswhere=""; try { if (part!=null) { //stdPN if(part.getStdPN()!=null&&!"".equals(part.getStdPN())){ stdPN=" p.stdPN=#{part.stdPN} "; havawhere=true; }else{ stdPN=""; } //mfr if(part.getMfr()!=null&&!"".equals(part.getMfr())){ mfr=" p.mfr=#{part.mfr} "; havawhere=true; }else{ mfr=""; } //mfrPN if (part.getMfrPN()!=null&&!"".equals(part.getMfrPN())) { mfrPN=" p.mfrPN=#{part.mfrPN} "; havawhere=true; } else { mfrPN=""; } //mfrPNDes if (part.getMfrPNDes()!=null&&!"".equals(part.getMfrPNDes())) { mfrPNDes=" p.mfrPNDes=#{part.mfrPNDes} "; havawhere=true; } else { mfrPNDes=""; } //state if (part.getState()!=null&&!"".equals(part.getState())) { state=" p.state=#{part.state} "; havawhere=true; } else { state=""; } //customer if (part.getCustomer()!=null&&!"".equals(part.getCustomer())) { customer=" p.customer=#{part.customer} "; havawhere=true; } else { customer=""; } //customerPN if (part.getCustomerPN()!=null&&!"".equals(part.getCustomerPN())) { customerPN=" p.customerPN=#{part.customerPN} "; havawhere=true; } else { customerPN=""; } //creator if (part.getCreator()!=null&&!"".equals(part.getCreator())) { creator=" p.creator=#{part.creator} "; havawhere=true; } else { creator=""; } //creatorDate if (part.getCreateorDate()!=null&&!"".equals(part.getCreateorDate())) { creatorDate=" p.creatorDate=#{part.creatorDate} "; havawhere=true; } else { creatorDate=""; } //buPN if (part.getBuPN()!=null&&!"".equals(part.getBuPN())) { buPN=" p.buPN=#{part.buPN} "; havawhere=true; } else { buPN=""; } //projectCode if (part.getProjectCode()!=null&&!"".equals(part.getProjectCode())) { projectCode=" p.projectCode=#{part.projectCode} "; havawhere=true; } else { projectCode=""; } } } catch (NullPointerException e) { // TODO: handle exception } if(havawhere) iswhere=" where "; String s1=iswhere + mfrPN + mfr + stdPN + mfrPNDes + state + customer + customerPN + creator + creatorDate + buPN + projectCode; String ss="select p from Part p "+ s1; table=em.createQuery(ss).getResultList(); } 我感觉这个写得太复杂了，而且没有加上and 该怎么改呢？ |
 |
dh_java
2009-03-17
用字符串拼接吧！
StringBuffer sb = new StringBuffer(); sb.append("select p from Part p where 1=1 "); sb.append(" and isnull(stdPN,'') like '%" + stdPN + "%'"); sb.append(" and isnull(mfr,'') like '%" + mfr + "%'"); sb.append(" and isnull(mfrPN,'') like '%" + mfrPN + "%'"); sb.append(" and isnull(mfrPNDes,'') like '%" + mfrPNDes + "%'"); sb.append(" and isnull(state,'') like '%" + state + "%'"); sb.append(" and isnull(customer,'') like '%" + customer + "%'"); sb.append(" and isnull(customerPN,'') like '%" + customerPN + "%'"); sb.append(" and isnull(creator,'') like '%" + creator + "%'"); sb.append(" and isnull(creatorDate,'') like '%" + creatorDate + "%'"); sb.append(" and isnull(buPN,'') like '%" + buPN + "%'"); sb.append(" and isnull(projectCode,'') like '%" + projectCode + "%'"); |
 |
Mrpublic
2009-03-17
你這個sb.append()方法時 如果只有一個查詢條件的話 會有and 哦？
select p from Part p where and ... lz,還是用我給你說的方法吧 
|
|
无双Rama
2009-03-17
Mrpublic 写道 你這個sb.append()方法時 如果只有一個查詢條件的話 會有and 哦？
select p from Part p where and ... lz,還是用我給你說的方法吧 你啥時候給了？貼出來嘛~~~~ |
|
无双Rama
2009-03-17
package com.fupl.action;
import java.util.*; import javax.ejb.Stateless; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.jboss.seam.ScopeType; import org.jboss.seam.annotations.Factory; import org.jboss.seam.annotations.In; import org.jboss.seam.annotations.Name; import org.jboss.seam.annotations.Out; import org.jboss.seam.annotations.Scope; import org.jboss.seam.annotations.datamodel.DataModel; import org.jboss.seam.annotations.datamodel.DataModelSelection; import org.jboss.seam.faces.FacesMessages; import com.fupl.domain.Part; /** * @author f3222*** * */ @Stateless @Name("dynamicselect") @Scope(ScopeType.SESSION) public class DynamicSelect implements Select { @DataModel private List<Part> table; @PersistenceContext private EntityManager em; int count=0; @DataModelSelection @In(required=false) @Out(required=false,scope = ScopeType.SESSION) private Part part;//part 為EntityBean private FacesMessages faceMessages; @SuppressWarnings("unchecked") @Factory("table") public void showTable(){ String stdPN=""; String mfr=""; String mfrPN=""; String mfrPNDes=""; String state=""; String customer=""; String customerPN=""; String creator=""; String creatorDate=""; String buPN=""; String projectCode=""; boolean havawhere=false; String iswhere=""; try { //判斷part對象是否為空 if (part!=null) { //當stdPN條件有值時，count加1,同時havawhere為true.(後面一樣) if(part.getStdPN()!=null&&!"".equals(part.getStdPN())){ count++; stdPN=" p.stdPN=#{part.stdPN} "; havawhere=true; }else{ stdPN=""; } //mfr if(part.getMfr()!=null&&!"".equals(part.getMfr())){ count++; mfr=" p.mfr=#{part.mfr} "; havawhere=true; }else{ mfr=""; } //mfrPN if (part.getMfrPN()!=null&&!"".equals(part.getMfrPN())) { count++; mfrPN=" p.mfrPN=#{part.mfrPN} "; havawhere=true; } else { mfrPN=""; } //mfrPNDes if (part.getMfrPNDes()!=null&&!"".equals(part.getMfrPNDes())) { count++; mfrPNDes=" p.mfrPNDes=#{part.mfrPNDes} "; havawhere=true; } else { mfrPNDes=""; } //state if (part.getState()!=null&&!"".equals(part.getState())) { count++; state=" p.state=#{part.state} "; havawhere=true; } else { state=""; } //customer if (part.getCustomer()!=null&&!"".equals(part.getCustomer())) { count++; customer=" p.customer=#{part.customer} "; havawhere=true; } else { customer=""; } //customerPN if (part.getCustomerPN()!=null&&!"".equals(part.getCustomerPN())) { count++; customerPN=" p.customerPN=#{part.customerPN} "; havawhere=true; } else { customerPN=""; } //creator if (part.getCreator()!=null&&!"".equals(part.getCreator())) { count++; creator=" p.creator=#{part.creator} "; havawhere=true; } else { creator=""; } //creatorDate if (part.getCreateorDate()!=null&&!"".equals(part.getCreateorDate())) { count++; creatorDate=" p.creatorDate=#{part.creatorDate} "; havawhere=true; } else { creatorDate=""; } //buPN if (part.getBuPN()!=null&&!"".equals(part.getBuPN())) { count++; buPN=" p.buPN=#{part.buPN} "; havawhere=true; } else { buPN=""; } //projectCode if (part.getProjectCode()!=null&&!"".equals(part.getProjectCode())) { count++; projectCode=" p.projectCode=#{part.projectCode} "; havawhere=true; } else { projectCode=""; } } } catch (NullPointerException e) { // TODO: handle exception } if(havawhere) iswhere=" where ";//當有任何一個條件有值時，查詢語句後面加上where String s1=iswhere + addAnd(mfrPN) + addAnd(mfr) + addAnd(stdPN) + addAnd(mfrPNDes) + addAnd(state) + addAnd(customer) + addAnd(customerPN) + addAnd(creator) + addAnd(creatorDate) + addAnd(buPN) + addAnd(projectCode); String ss="select p from Part p "+ s1; table=em.createQuery(ss).getResultList(); count=0; //每次查詢完以后清空； } /* * 當條件有值並且count大於1的時候，在條件後面加上 and ，並且 count減1，以便於讓最后一個條件后面沒有and */ public String addAnd(String str){ if(count>1&&str!=""&&str!=null){ str=str +" and "; count--; } return str; } } 已經完全解決了，感謝Mrpublic先生  
|
 |
hcyoo
2009-03-17
我一般都用三楼的写法,不管有没有条件,先加上个 where 1=1
后面有条件就可以直接写上 and col=value 而且查询要经常使用,字符串又很长的话用StringBuffer对提高性能是显而易见的 |
|
Mrpublic
2009-03-18
hcyoo 写道 我一般都用三楼的写法,不管有没有条件,先加上个 where 1=1
后面有条件就可以直接写上 and col=value 而且查询要经常使用,字符串又很长的话用StringBuffer对提高性能是显而易见的 where 1=1 是啥意思哦？ 這樣寫不會只有一個查詢條件時會有and出現？ 謝謝。。 |
 |
E-Hunter
2009-03-18
在request中有一个getParameterMap方法，能获取页面中的参数，把这个MAP放到一个方法里面，进行组合，这样就不用管页面里有几个参数了，遍历MAP就能得到所有的。然后用上面提到的方法组合一下就OK了，不过从MAP里面取出来的值全都是String[],需要特别处理一下。
|
 |
andyhan
2009-03-18
为什么不研究一下Seam的EntityQuery的处理方式？
|
