查询

来源：互联网发布：罅隙歌词知乎编辑：程序博客网时间：2024/04/28 11:10

<SPAN style="FONT-SIZE: x-small">先定义一个枚举，配置条件查询是什么类型的，like = 还是between and。目前实现的是这三种
-------------------------------------------------------------------------
public enum CType {
like,equal,between
}
-------------------------------------------------------------------------
定义annotation，用于注解在字段上面
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ConditionType {
CType value();
String andField() default "";
String queryField() default "";
}
-------------------------------------------------------------------------
根据bean的字段注解生成查询语句
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
public class HqlCreator {
public static void createHql(StringBuilder hql,Object object,List<Object> params)
{
try {
//加载class
Class clazz=object.getClass();
//获取私有字段
Field[] fields=clazz.getDeclaredFields();
//遍历字段
for (Field field : fields) {
//如果字段上有 ConditionType 的注解
if(field.isAnnotationPresent(ConditionType.class))
{
//获取注解的value值
ConditionType conditionType=field.getAnnotation(ConditionType.class);
CType ct=conditionType.value();
//调用get方法
Object objValue=invokeGetMethodByFieldName(field.getName(), object);
//获取字段类型
Class typeClass=field.getType();
if(objValue==null)
continue;
//检查字段是否为空
if(!checkTypeNull(typeClass,objValue)){
continue;
}
//根据注解类型生成相应的语句和参数
if(ct.equals(CType.equal))
{
hql.append(" and "+ field.getName() +" = ?");
params.add(objValue);
}
else if(ct.equals(CType.like))
{
hql.append(" and "+ field.getName() +" like ?");
params.add(Utils.returnLikeString(objValue.toString()));
}
else if(ct.equals(CType.between))
{
hql.append(" and " +conditionType.queryField()+ " between ? and ?");
params.add(objValue);
Object andValue=invokeGetMethodByFieldName(conditionType.andField(), object);
params.add(andValue);
}
}
System.out.println("生成hql语句为："+hql.toString());
}
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
}
}
//调用get方法
public static Object invokeGetMethodByFieldName(String fieldName,Object object)
{
fieldName=fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
Method m=null;
try {
m = object.getClass().getMethod("get"+fieldName, null);
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Object objValue=null;
try {
objValue = m.invoke(object, null);
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return objValue;
}
private static boolean checkTypeNull(Class typeClass,Object objValue)
{
boolean flag=true;
if(typeClass.equals(String.class))
{
if(objValue==null || Utils.isEmpty(objValue+""))
flag=false;
}
if(typeClass.equals(Long.class))
{
if(objValue==null || Long.parseLong(objValue.toString())==0L)
flag=false;
}
//后续如果出现类型判断有误，在此添加
return flag;
}
}
-------------------------------------------------------------------------
这个是用户查询时必要的几个参数，封转成bean啦
import java.util.List;
public class Paging {
private String hql;
private List<Object> params;
private int start;
private int limit;
省略get、set
}
-------------------------------------------------------------------------
这个就是吧 paging 这个bean进行查询的
public class CommonsDAO extends HibernateDaoSupport{
private static final Logger log = Logger.getLogger(CommonsDAO.class);
public List findByPageBean(final Paging page){
if(page==null){
return null;
}
List list = null;
log.debug("分页查询");
try {
list = this.getHibernateTemplate().executeFind(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createQuery(page.getHql());
if(page.getParams()!=null)
{
for (int i = 0; i < page.getParams().size(); i++) {
query.setParameter(i, page.getParams().get(i));
}
}
if(page.getLimit()!=0)
{
query.setFirstResult(page.getStart());
query.setMaxResults(page.getLimit());
}
List list = query.list();
return list;
}
});
log.debug("分页查询成功");
} catch (Exception e) {
e.printStackTrace();
log.error("分页查询失败",e);
throw new RuntimeException("findByPageBean");
}
return list;
}
public int findTotal(final Paging page) {
int total=0;
if(page==null)
{
return total;
}
log.debug("查询记录总数");
try {
total = Integer.parseInt((this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createQuery(page.getHql());
if(page.getParams()!=null)
{
for (int i = 0; i < page.getParams().size(); i++) {
query.setParameter(i, page.getParams().get(i));
}
}
Object o=query.uniqueResult();
if(o!=null){
return Integer.parseInt(o.toString());
}
return 0;
}
}).toString()));
log.debug("查询记录总数成功");
} catch (Exception e) {
e.printStackTrace();
log.error("查询记录总数失败",e);
throw new RuntimeException("findTotal");
}
return total;
}
-------------------------------------------------------------------------
现在我们写一个封装查询条件的bean
import java.util.Date;
public class ZcjzclBean {
/**
* @Fields cphm : 车牌号码
*/
@ConditionType(CType.like)
private String cphm;
/**
* @Fields jg : 价格
*/
@ConditionType(value=CType.between,andField="jgEnd",queryField="jg")
private Double jgStart;
private Double jgEnd;
/**
* @Fields lc : 里程
*/
@ConditionType(value=CType.between,andField="lcEnd",queryField="lc")
private Long lcStart;
private Long lcEnd;
/**
* @Fields lcdj : 里程单价
*/
@ConditionType(value=CType.between,andField="lcdjEnd",queryField="lcdj")
private Double lcdjStart;
private Double lcdjEnd;
/**
* @Fields bylc : 保养里程
*/
@ConditionType(value=CType.between,andField="bylcEnd",queryField="bylc")
private Long bylcStart;
private Long bylcEnd;
/**
* @Fields fzrid : 负责人id
*/
@ConditionType(CType.equal)
private Long fzrid;
/**
* @Fields sjid : 司机id
*/
@ConditionType(CType.equal)
private Long sjid;
/**
* @Fields gg : 规格
*/
@ConditionType(CType.like)
private String gg;
/**
* @Fields xh : 型号
*/
@ConditionType(CType.like)
private String xh;
/**
* @Fields zws : 座位数
*/
@ConditionType(value=CType.between,andField="zwsEnd",queryField="zws")
private Long zwsStart;
private Long zwsEnd;
/**
* @Fields ys : 颜色
*/
@ConditionType(CType.equal)
private String ys;
/**
* @Fields gmrq : 购买日期
*/
@ConditionType(value=CType.between,andField="gmrqEnd",queryField="gmrq")
private Date gmrqStart;
private Date gmrqEnd;
/**
* @Fields jzrq : 建账日期
*/
@ConditionType(value=CType.between,andField="jzrqEnd",queryField="jzrq")
private Date jzrqStart;
private Date jzrqEnd;
/**
* @Fields zcjzrid : 资产建账人id
*/
@ConditionType(CType.equal)
private Long zcjzrid;
省略get、set
-------------------------------------------------------------------------
编写查询代码
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
public class ZcjzclServiceImpl implements ZcjzclService {
public static final Logger log=Logger.getLogger(ZcjzclServiceImpl.class);
private CommonsDAO commonsDAO;
public List<TZcjzcl> queryByCondition(ZcjzclBean bean,int start,int limit)
{
try {
log.debug(" ");
Paging page=getPage( bean, start, limit, false);
List<TZcjzcl> cls=commonsDAO.findByPageBean(page);
return cls;
} catch (RuntimeException e) {
log.error(" 异常");
throw e;
}
}
public int queryTotalByCondition(ZcjzclBean bean, int start, int limit) {
try {
log.debug(" ");
Paging page=getPage( bean, start, limit, true);
int total=commonsDAO.findTotal(page);
return total;
} catch (RuntimeException e) {
log.error(" 异常",e);
throw e;
}
}
private Paging getPage(ZcjzclBean bean, int start, int limit,boolean isTotal)
{
try {
log.debug("组装查询语句及条件");
List<Object> params=new ArrayList<Object>();
StringBuilder hql=new StringBuilder();
if(isTotal)
hql.append("select count(*) from TZcjzcl where 1=1 ");
else
hql.append("from TZcjzcl where 1=1 ");
if(bean!=null)
{
HqlCreator.createHql(hql, bean, params);
}
Paging page=new Paging();
page.setHql(hql.toString());
page.setStart(start);
page.setLimit(limit);
page.setParams(params);
return page;
} catch (RuntimeException e) {
log.error("组装查询语句及条件异常",e);
throw e;
}
}
省略get、set
}
-------------------------------------------------------------------------
要加什么条件直接在查询条件bean里面加就完啦。
</SPAN>