Jfinal数据库基本操作(三) 高级查询

UserService类

//find类 第一个参数是sql语句,第二个参数是需要查询的关键字
public static UserModel findbyMobile(String mobile) {
String sql = "select * from user where mobile = ?";
return DAO.findFirst(sql, mobile);
}

public static List<UserModel> findbySex(int sex) {
String sql = "select * from user where sex = ?";
return DAO.find(sql,sex);
}

 

public static List<UserModel> find(Integer sex,String name,String address) {
StringBuffer sql = new StringBuffer("select * from user");
boolean addWhere = false;
List<Object> parList = new ArrayList<Object>();
if (sex !=null) {
if(addWhere == false) {
addWhere = true;
sql.append(" WHERE ");
}else {
sql.append(" AND ");
}
sql.append("sex = ? ");
parList.add(sex);

}
if (name !=null) {
if(addWhere == false) {
addWhere = true;
sql.append(" WHERE ");
}else {
sql.append(" AND ");
}
sql.append("name like ? ");
parList.add(name+"%");

}
if (address !=null) {
if(addWhere == false) {
addWhere = true;
sql.append(" WHERE ");
}else {
sql.append(" AND ");
}
sql.append(" address like ? ");
parList.add(address+"%");

}

return DAO.find(sql.toString(), parList.toArray());
}

 //使用封装方法查询

public static List<UserModel> find(Integer sex,String name,String address) {
StringBuffer sql = new StringBuffer("select * from user");
boolean addWhere = false;
List<Object> parList = new ArrayList<Object>();
addWhere = AppendFiled(sex, "sex",sql, addWhere, parList);
addWhere = AppendLike(name, "name",sql, addWhere, parList);
addWhere = AppendLike(address, "address",sql, addWhere, parList);

return DAO.find(sql.toString(), parList.toArray());
}

private static boolean AppendLike(String name, String filed,StringBuffer sql, boolean addWhere, List<Object> parList) {
if (name !=null) {
if(addWhere == false) {
addWhere = true;
sql.append(" WHERE ");
}else {
sql.append(" AND ");
}
sql.append(filed+" like ? ");
parList.add(name+"%");

}
return addWhere;
}

private static boolean AppendFiled(Integer sex,String filed, StringBuffer sql, boolean addWhere, List<Object> parList) {
if (sex !=null) {
if(addWhere == false) {
addWhere = true;
sql.append(" WHERE ");
}else {
sql.append(" AND ");
}
sql.append(filed+" = ? ");
parList.add(sex);

}
return addWhere;
}

 

 

 UserController类

public void action2() {
// UserModel user = UserService.findbyMobile("123456789");
// renderText("name:"+user.getStr("name"));

List<UserModel> user1 = UserService.findbySex(1);
for (UserModel u : user1) {
System.out.println("name:"+ u.getStr("name"));
}
renderText(">>>>OK>>>");
}

 

List<UserModel> user2 = UserService.find(null, "h", "广");

for (UserModel u : user2) {
System.out.println("name:"+ u.getStr("name"));
}
renderText(">>>>OK>>>");