JPA的自适应查询

构造查询语句是一件麻烦的事,可能经常因为这样那样的手误造成查询失败,另外由于查询中可能使用不同的查询条件,特意写了个查询语句构造器,如下:

 

 

/**/ /*
 * 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();
    }

 

 

 

/**/ /*
 * 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("//.");