Write a 1=1 for the following conditions. This SQL should be spelled out in the program, the first thing in the program is not sure whether the following conditions are sure to have, in order to program simple first plus where 1=1, the back can be directly splicing. If you do not do this, you need to determine whether the first piece of the WHERE clause should be preceded by each of the conditions behind the stitching, to determine if you want to precede with and
public pageresults<ordervo> find (long oid, string name, string MOBILE,&NBSP;STRING&NBSP;OSTATUS,&NBSP;&NBSP;&NBSP;DATE&NBSP;TIME1,&NBSP;DATE&NBSP;TIME2) { This.usersessionfactorycustomer (); this.usersessionfactoryorder (); string querystring = "Select o.id as id ,o.status as ostatus,o.createdate as createDate ,o.paidDate as paidDate ,o.collectionToolSendDate as Collectiontoolsenddate ,o.collectiontoolsendlogisticid as collectiontoolsendlogisticid, o.finishdate as finishdate ,o.remark as remark , " + "O.amount as amount,o.thirdpartypayment as paymode,ol.companyname as companyname , " + " c.name as name,c.mobile as mobile,ol.address as address " + "from orders.orders o, customer.customer c,orders.order_logistics ol " + " where o.customerid=c.id and ol.id= o.collectiontoolsendlogisticid and o.valid=true "; string querycountstring = "Select count (*) from orders.orders o, customer.customer c, Orders.order_logistics ol where o.customerid=c.id and ol.id= O.collectiontoolsendlogisticid and o.valid=true "; if (oid!=null) { queryString += " and o.id = :oid"; querycountstring += " and o.id = :oid"; } if (name != null && ! "". Equals (name)) { querystring += " and c.name = :name"; queryCountString += " aNd c.name = :name "; } if (mobile != null && !" ". Equals (MOBILE)) { querystring += " and c.mobile = :mobile"; queryCountString += " and c.mobile = :mobile"; } if ( ostatus != null && ! "". Equals (ostatus)) { querystring += " and o.status = :ostatus"; queryCountString += " and o.status = :ostatus"; } if (time1!=null) { querystring += " and o.createDate >= : Time1 and o.createdate <= :time2 "; querycountstring += " and o.createdate >= :time1 and o.createdate <= :time2 "; } queryString += " order by o.createdate "; query query = this.getsession (). CreateSQLQuery ( queryString) .addscalar ("id", Standardbasictypes.long) .addscalar ("Ostatus") .addscalar ("CreateDate") .addscalar ("PaidDate") .addscalar ("Collectiontoolsenddate") .addscalar (" Collectiontoolsendlogisticid ", Standardbasictypes.long) .addscalar (" FinishDate ") .addscalar ("Remark") .addscalar ("name") . Addscalar ("mobile", standardbasictypes.string) .addscalar ("Address") .addscalar ("Amount") .addscalar ("CompanyName") .addscalar ( "Paymode") .setresulttransformer (Transformers.aliastobean (OrderVo.class)); Query querycount = this.getsEssion (). Createsqlquery (querycountstring); if (oid!=null) { Query.setparameter ("OID", oid); querycount.setparameter ("oid", oid); } if (name != null && ! "". Equals (name)) { query.setparameter ("name", name); Querycount.setparameter ("name", name); } if (mobile != null & & ! "". Equals (MOBILE) { query.setparameter ("mobile", mobile); Querycount.setparameter ("mobile", mobile); } if (ostatus != null && ! "". Equals (Ostatus)) { query.setparameter ("Ostatus", ostatus); Querycount.setparameter ("Ostatus", ostatus); } if (Time1!=null) { Query.setparameter ("time1", time1); query.setparameter ("Time2", time2); querycount.setparameter ("time1", time1); querycount.setparameter ("Time2", time2); } int size = systemcontext.getsize (); int Offset = systemcontext.getoffset (); query.setfirstresult (offset). Setmaxresults (size); list<ordervo> olist = query.list (); Pageresults<ordervo> olistpage = new pageresults<ordervo> (); Olistpage.setresults (olist); olistpage.setcurrentpage (offset); olistpage.setpagesize ( size); biginteger totalcount= (BigInteger) querycount.uniqueresult (); Olistpage.settotalcount (Totalcount.intvalue ()); return olistpage; }}
Where 1=2 is to let the recordset be empty, because it is only used to insert a row, you just need to get the data type of each property of the table.
Faster because it doesn't have to be compared to any field in the table
This article is from the "Nothing-skywalker" blog, please be sure to keep this source http://tianxingzhe.blog.51cto.com/3390077/1679353
where 1=1 1=2 function in SQL statements