Add, delete, modify, and query concentration camps

Combined Query

Key points:

1. upload function parameters

2. Check whether the parameter is null. If it is not null, spell the SQL statement.

If (name! = NULL &&! "". Equals (name )){
Querysql. append ("and Em. cn like? ");
}

3. Set a parameter for the SQL statement, set an initial value for the location parameter, and then make a judgment. If it is not empty, set the parameter and add the location to 1.

Int Pos = 1;

If (name! = NULL &&! "". Equals (name )){
Pst. setstring (Pos ++, "%" + name + "% ");
}

Complete code:

Public static list finduserbygroup (string erptype, long orgid, string name, string number, string [] groupname, int begin, int end) throws systemexception {
List result = NULL;
Connection conn = NULL;
Preparedstatement PST = NULL;
Resultset rs = NULL;
Stringbuffer querysql = new stringbuffer ();

Querysql. append ("select *");
Querysql. append ("from (select rownum RN ,");
Querysql. append ("em. userid ,");
Querysql. append ("em. u_id screenname ,");
Querysql. append ("em. cn fullname ");
Querysql. append ("from ecp_user_mapping em ,");
Querysql. append ("users_orgs uo ");

If (groupname! = NULL & groupname. length> 0 ){
Querysql. append (", usergroup ug ,");
Querysql. append ("users_usergroups uug ");
}

Querysql. append ("where 1 = 1 ");
Querysql. append ("and Em. erp_type =? ");
Querysql. append ("and Em. userid = uo. userid ");
Querysql. append ("and exists (select * from Organization _ org where org. organizationid = uo. organizationid ");
Querysql. append ("Start with org. organizationid =? ");
Querysql. append ("connect by prior org. organizationid = org. parentorganizationid )");

If (name! = NULL &&! "". Equals (name )){
Querysql. append ("and Em. cn like? ");
}

If (number! = NULL &&! "". Equals (number )){
Querysql. append ("and Em. u_id like? ");
}

If (groupname! = NULL & groupname. length> 0 ){
Querysql. append ("and uug. userid (+) = em. userid ");
Querysql. append ("and UG. usergroupid (+) = uug. usergroupid ");
Querysql. append ("and (");
For (INT I = 0; I <groupname. length; I ++ ){
If (I = 0 ){
Querysql. append ("ug. Name =? ");
} Else {
Querysql. append ("or ug. Name =? ");
}
}
Querysql. append (")");
}
Querysql. append ("order by em. u_id, Em. cn ");
Querysql. append (") t ");
Querysql. append ("where T. Rn? And? ");
System. Out. println (querysql. tostring ());
Try {
Conn = getconnection ();
Pst = conn. preparestatement (querysql. tostring ());
Int Pos = 1;
Pst. setstring (Pos ++, erptype );
Pst. setlong (Pos ++, orgid );
If (name! = NULL &&! "". Equals (name )){
Pst. setstring (Pos ++, "%" + name + "% ");
}

If (number! = NULL &&! "". Equals (number )){
Pst. setstring (Pos ++, "%" + number + "% ");
}
If (groupname! = NULL & groupname. length> 0 ){
For (INT I = 0; I <groupname. length; I ++ ){
Pst. setstring (Pos ++, groupname [I]);
}
}

Pst. setint (Pos ++, begin );
Pst. setint (Pos ++, end );
Rs = pst.exe cutequery ();

Result = util. resultset2javabean (RS, userinfobean. Class );

} Catch (exception e ){
E. printstacktrace ();
Throw new systemexception (E );
} Finally {
Close (Conn, Pst, RS );
}
Return result;
}