Query method for indeterminate parameters (multi-criteria query)

If you want to make a multi-condition query, but do not know what conditions the user actually set, so we will be in the editing of a multi-conditional query, we encounter such a problem.

Then we can solve it in the following ways:

Suppose a scene--such as: where Customer.class contains all of the following properties

　　　　　　　　　

Start solving the problem:

So we can write this in Customerdao:

public class customerdao{

Private Queryrunner QR = new Txqueryrunner (); Txqueryrunner for your own Write inheritance class--source code at the bottom

Public list<customer> query (Customer c) {

StringBuffer sql = new StringBuffer ("SELECT * from T_customer where 1=1"); Focus. This solves the problem of how the condition is spliced.

list<object> list = new arraylist<object> ();

if (C.getcname!=null&&!c.getcname.trim (). IsEmpty ()) {

　　　　　　Sql.append ("and cname =?"); /and front requires a space, separated from the previous content

　　　　　　List.add (c.getcname);//Add parameters to the list

}

if (C.getgender!=null&&!c.getgender.trim (). IsEmpty ()) {

　　　　Sql.append ("and gender =?"); /and front requires a space, separated from the previous content

List.add (C.getgender);

}

if (C.getcellphone!=null&&!c.getcellphone.trim (). IsEmpty ()) {

Sql.append ("and cellphone =?"); /and front requires a space, separated from the previous content

List.add (C.getcellphone);

}

if (C.getemail!=null&&!c.getemail.trim (). IsEmpty ()) {

Sql.append ("and email =?"); /and front requires a space, separated from the previous content

List.add (C.getemail);

}

Return Qr.update (Sql.tostring (), New beanlisthandler<customer> (Customer.class), List.toarray ());//return result

}

}

-----End-------

Attached: (Need to rely on: Dbutils.jar, mysql.jar,c3p0.jar+ configuration file, Mchange-commons.jar, Czk-tools-1.0.jar.)

1. t_customer--> refers to a table in the database that contains the CNAME, gender, cellphone, description

2. Txqueryrunner.class inherits the class of Queryrunner.class.

The code is as follows:

1  Public classTxqueryrunnerextendsqueryrunner{2     3 @Override4      Public int[] Batch (String sql, object[][] params)throwsSQLException {5Connection con =jdbcutils.getconnection ();6         int[] result =Super. Batch (con, SQL, params);7 jdbcutils.releaseconnection (con);8         returnresult;9     }Ten  One @Override A      Public<T> T query (String SQL, resultsethandler<t>rsh, Object ... params) -             throwsSQLException { -Connection con =jdbcutils.getconnection (); theT result =Super. Query (con, SQL, rsh,params); - jdbcutils.releaseconnection (con); -         returnresult; -     } +  - @Override +      Public<T> T query (String sql, resultsethandler<t> rsh)throwsSQLException { AConnection con =jdbcutils.getconnection (); atT result =Super. Query (con, SQL, rsh); - jdbcutils.releaseconnection (con); -         returnresult; -     } -  - @Override in      Public intUpdate (String sql, Object ... params)throwsSQLException { -Connection con =jdbcutils.getconnection (); to         intresult =Super. Update (con, SQL, params); + jdbcutils.releaseconnection (con); -         returnresult; the     } *  $ @OverridePanax Notoginseng      Public intUpdate (String sql, Object param)throwsSQLException { -Connection con =jdbcutils.getconnection (); the         intresult =Super. Update (con, SQL, param); + jdbcutils.releaseconnection (con); A         returnresult; the     } +  - @Override $      Public intUpdate (String SQL)throwsSQLException { $Connection con =jdbcutils.getconnection (); -         intresult =Super. Update (con, SQL); - jdbcutils.releaseconnection (con); the         returnresult; -     }Wuyi      the}

Query method for indeterminate parameters (multi-criteria query)