构造查询语句是一件麻烦的事,可能经常因为这样那样的手误造成查询失败,另外由于查询中可能使用不同的查询条件,特意写了个查询语句构造器,如下:
/**/
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package assistance;
import java.util.Enumeration;
import java.util.Hashtable;
/** */ /**
*
* @author wave
*/
public class ConstructQuery ... {
private String select="";
private String from="";
private String groupBy="";
private String orderBy="";
private String query="";
private Hashtable where = new Hashtable();
private Hashtable has=new Hashtable();
private String natureLinks="";
public ConstructQuery(Hashtable h) ...{
where = h;
}
public ConstructQuery(String select, String from, Hashtable where,String natureLinks,String groupBy,Hashtable has,String orderBy) ...{
if (select != null && !select.isEmpty() && !from.isEmpty() && from != null) ...{
this.select = select;
this.from = from;
this.where=where;
this.groupBy = groupBy;
this.orderBy = orderBy;
this.has=has;
this.natureLinks=natureLinks;
this.buildSelect();
this.buildFrom();
this.buildWhere();
this.buildNatureLinks();
this.buildGroupBy();
this.buildHas();
this.buildOrderBy();
}
}
public void buildSelect() ...{
query = "SELECT";
space();
query+=select;
}
public void buildFrom() ...{
space();
query+="FROM";
space();
query+=from;
}
public void buildWhere() ...{
if (where.size()>0) ...{
space();
query+="WHERE";
space();
Enumeration e = where.keys();
String and = "";
while (e.hasMoreElements()) ...{
and+=" AND";
String pre = (String) e.nextElement();
and+=" ";
and+=pre;
and+="=:";
String all[] = pre.split("/.");
String last="";
if(all.length>0)...{
last = all[all.length-1];
}
and+=last;
}
and=and.substring(5);
query += and;
}
}
public void buildGroupBy() ...{
if (groupBy != null && !groupBy.isEmpty()) ...{
space();
query+="GROUP BY";
space();
query+=groupBy;
}
}
public void buildHas() ...{
if(has.size()>0&&groupBy!=null&&!groupBy.isEmpty())...{
space();
query+="HAS";
space();
String and="";
Enumeration e=has.keys();
while (e.hasMoreElements()) ...{
and+=" AND";
String pre = (String) e.nextElement();
and+=" ";
and+=pre;
and+="=:";
String all[] = pre.split("/.");
String last="";
if(all.length>0)...{
last = all[all.length-1];
}
and+=last;
}
and=and.substring(5);
query += and;
}
}
public void buildOrderBy() ...{
if (orderBy != null && !orderBy.isEmpty()) ...{
space();
query+="ORDER BY";
space();
query+=orderBy;
}
}
public String getFrom() ...{
return from;
}
public void setFrom(String from) ...{
this.from = from;
}
public String getGroupBy() ...{
return groupBy;
}
public void setGroupBy(String groupBy) ...{
this.groupBy = groupBy;
}
public String getOrderBy() ...{
return orderBy;
}
public void setOrderBy(String orderBy) ...{
this.orderBy = orderBy;
}
public String getQuery() ...{
return query;
}
public void setQuery(String query) ...{
this.query = query;
}
public String getSelect() ...{
return select;
}
public void setSelect(String select) ...{
this.select = select;
}
public Hashtable getHas() ...{
return has;
}
public void setHas(Hashtable has) ...{
this.has = has;
}
public Hashtable getWhere() ...{
return where;
}
public void setWhere(Hashtable where) ...{
this.where = where;
}
public void space() ...{
query+=" ";
}
public void buildNatureLinks()...{
space();
query+=natureLinks;
}
}
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package assistance;
import java.util.Enumeration;
import java.util.Hashtable;
/** */ /**
*
* @author wave
*/
public class ConstructQuery ... {
private String select="";
private String from="";
private String groupBy="";
private String orderBy="";
private String query="";
private Hashtable where = new Hashtable();
private Hashtable has=new Hashtable();
private String natureLinks="";
public ConstructQuery(Hashtable h) ...{
where = h;
}
public ConstructQuery(String select, String from, Hashtable where,String natureLinks,String groupBy,Hashtable has,String orderBy) ...{
if (select != null && !select.isEmpty() && !from.isEmpty() && from != null) ...{
this.select = select;
this.from = from;
this.where=where;
this.groupBy = groupBy;
this.orderBy = orderBy;
this.has=has;
this.natureLinks=natureLinks;
this.buildSelect();
this.buildFrom();
this.buildWhere();
this.buildNatureLinks();
this.buildGroupBy();
this.buildHas();
this.buildOrderBy();
}
}
public void buildSelect() ...{
query = "SELECT";
space();
query+=select;
}
public void buildFrom() ...{
space();
query+="FROM";
space();
query+=from;
}
public void buildWhere() ...{
if (where.size()>0) ...{
space();
query+="WHERE";
space();
Enumeration e = where.keys();
String and = "";
while (e.hasMoreElements()) ...{
and+=" AND";
String pre = (String) e.nextElement();
and+=" ";
and+=pre;
and+="=:";
String all[] = pre.split("/.");
String last="";
if(all.length>0)...{
last = all[all.length-1];
}
and+=last;
}
and=and.substring(5);
query += and;
}
}
public void buildGroupBy() ...{
if (groupBy != null && !groupBy.isEmpty()) ...{
space();
query+="GROUP BY";
space();
query+=groupBy;
}
}
public void buildHas() ...{
if(has.size()>0&&groupBy!=null&&!groupBy.isEmpty())...{
space();
query+="HAS";
space();
String and="";
Enumeration e=has.keys();
while (e.hasMoreElements()) ...{
and+=" AND";
String pre = (String) e.nextElement();
and+=" ";
and+=pre;
and+="=:";
String all[] = pre.split("/.");
String last="";
if(all.length>0)...{
last = all[all.length-1];
}
and+=last;
}
and=and.substring(5);
query += and;
}
}
public void buildOrderBy() ...{
if (orderBy != null && !orderBy.isEmpty()) ...{
space();
query+="ORDER BY";
space();
query+=orderBy;
}
}
public String getFrom() ...{
return from;
}
public void setFrom(String from) ...{
this.from = from;
}
public String getGroupBy() ...{
return groupBy;
}
public void setGroupBy(String groupBy) ...{
this.groupBy = groupBy;
}
public String getOrderBy() ...{
return orderBy;
}
public void setOrderBy(String orderBy) ...{
this.orderBy = orderBy;
}
public String getQuery() ...{
return query;
}
public void setQuery(String query) ...{
this.query = query;
}
public String getSelect() ...{
return select;
}
public void setSelect(String select) ...{
this.select = select;
}
public Hashtable getHas() ...{
return has;
}
public void setHas(Hashtable has) ...{
this.has = has;
}
public Hashtable getWhere() ...{
return where;
}
public void setWhere(Hashtable where) ...{
this.where = where;
}
public void space() ...{
query+=" ";
}
public void buildNatureLinks()...{
space();
query+=natureLinks;
}
}
要求查询字段由Hashtable where提供,格式是:
key为查询比较的字段,value是对该查询字段的赋值
比如这么一条语句
select e,p from Employee e JOIN e.productCollection p where e.name=:name and p.project=:project
只要调用
Hashtable where=new Hashtable();
where.put("e.name","a value you want to search for");
where.put("p.project","a value you want to search for");
ConstructQuery cq=new ConstructQuery("e,p","Employee e JOIN e.productCollection p",where,"","",has,"");
String query=cq.getQuery();
下面有样例:
public
List
<
DetailProduct
>
getDetailProduct(Hashtable where)
...
{
String select = "NEW assistance.DetailProduct(p.productId,m.modelType,p.brand,p.alias,p.code,p.unitPrice,p.stocks,m.type,m.transition,m.connectType,m.structure,m.material,m.bottomMaterial,m.pressure)";
String from = "Model m JOIN m.productCollection p";
String nature = "";
String groupBy = "";
String orderBy = "";
Hashtable has = new Hashtable();
ConstructQuery query = new ConstructQuery(select, from, where, nature, groupBy, has, orderBy);
String actionQuery = query.getQuery();
javax.persistence.Query q = em.createQuery(actionQuery);
if (where != null) ...{
Enumeration e = where.keys();
while (e.hasMoreElements()) ...{
String key = (String) e.nextElement();
String all[] = key.split("/.");
String last = "";
if (all.length > 0) ...{
last = all[all.length - 1];
Object o = where.get(key);
if (o instanceof Date) ...{
q.setParameter(last, (Date) o, TemporalType.DATE);
} else if (o instanceof Calendar) ...{
q.setParameter(last, (Calendar) o, TemporalType.TIME);
} else ...{
q.setParameter(last, o);
}
}
}
}
return q.getResultList();
}
String select = "NEW assistance.DetailProduct(p.productId,m.modelType,p.brand,p.alias,p.code,p.unitPrice,p.stocks,m.type,m.transition,m.connectType,m.structure,m.material,m.bottomMaterial,m.pressure)";
String from = "Model m JOIN m.productCollection p";
String nature = "";
String groupBy = "";
String orderBy = "";
Hashtable has = new Hashtable();
ConstructQuery query = new ConstructQuery(select, from, where, nature, groupBy, has, orderBy);
String actionQuery = query.getQuery();
javax.persistence.Query q = em.createQuery(actionQuery);
if (where != null) ...{
Enumeration e = where.keys();
while (e.hasMoreElements()) ...{
String key = (String) e.nextElement();
String all[] = key.split("/.");
String last = "";
if (all.length > 0) ...{
last = all[all.length - 1];
Object o = where.get(key);
if (o instanceof Date) ...{
q.setParameter(last, (Date) o, TemporalType.DATE);
} else if (o instanceof Calendar) ...{
q.setParameter(last, (Calendar) o, TemporalType.TIME);
} else ...{
q.setParameter(last, o);
}
}
}
}
return q.getResultList();
}
/**/
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package testmain;
import business.OrderRemote;
import java.util.Hashtable;
import java.util.Properties;
import javax.naming.InitialContext;
import javax.naming.NamingException;
/** */ /**
*
* @author wave
*/
public class Main ... {
/** *//**
* @param args the command line arguments
*/
public static void main(String[] args) ...{
Main obj = new Main();
OrderRemote query = (OrderRemote) obj.lookupQueryBean("business.OrderRemote");
Hashtable h=new Hashtable();
h.put("m.modelType", "kkk");
h.put("m.transition","everything");
query.getDetailProduct(h);
// TODO code application logic here
}
private Object lookupQueryBean(String beanName) ...{
try ...{
Properties props = new Properties();
props.setProperty("java.naming.factory.initial",
"com.sun.enterprise.naming.SerialInitContextFactory");
props.setProperty("java.naming.factory.url.pkgs",
"com.sun.enterprise.naming");
props.setProperty("java.naming.factory.state",
"com.sun.corba.ee.impl.presentation.rmi.JNDIStateFactoryImpl");
// optional. Defaults to localhost. Only needed if web server is running
// on a different host than the appserver
props.setProperty("org.omg.CORBA.ORBInitialHost", "localhost");
// optional. Defaults to 3700. Only needed if target orb port is not 3700.
props.setProperty("org.omg.CORBA.ORBInitialPort", "3700");
InitialContext ic = new InitialContext(props);
return ic.lookup(beanName);
} catch (NamingException ne) ...{
java.util.logging.Logger.getLogger(getClass().getName()).log(java.util.logging.Level.SEVERE, "exception caught", ne);
throw new RuntimeException(ne);
}
}
}
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package testmain;
import business.OrderRemote;
import java.util.Hashtable;
import java.util.Properties;
import javax.naming.InitialContext;
import javax.naming.NamingException;
/** */ /**
*
* @author wave
*/
public class Main ... {
/** *//**
* @param args the command line arguments
*/
public static void main(String[] args) ...{
Main obj = new Main();
OrderRemote query = (OrderRemote) obj.lookupQueryBean("business.OrderRemote");
Hashtable h=new Hashtable();
h.put("m.modelType", "kkk");
h.put("m.transition","everything");
query.getDetailProduct(h);
// TODO code application logic here
}
private Object lookupQueryBean(String beanName) ...{
try ...{
Properties props = new Properties();
props.setProperty("java.naming.factory.initial",
"com.sun.enterprise.naming.SerialInitContextFactory");
props.setProperty("java.naming.factory.url.pkgs",
"com.sun.enterprise.naming");
props.setProperty("java.naming.factory.state",
"com.sun.corba.ee.impl.presentation.rmi.JNDIStateFactoryImpl");
// optional. Defaults to localhost. Only needed if web server is running
// on a different host than the appserver
props.setProperty("org.omg.CORBA.ORBInitialHost", "localhost");
// optional. Defaults to 3700. Only needed if target orb port is not 3700.
props.setProperty("org.omg.CORBA.ORBInitialPort", "3700");
InitialContext ic = new InitialContext(props);
return ic.lookup(beanName);
} catch (NamingException ne) ...{
java.util.logging.Logger.getLogger(getClass().getName()).log(java.util.logging.Level.SEVERE, "exception caught", ne);
throw new RuntimeException(ne);
}
}
}
由于被CSDN改了代码,上面的split函数应该是split("//.");