hibernate中的hql语句，字符串时间范围筛选处理

一、疑难问题

1、数据表的业务时间是字符串类型。

2、如何通过hql语句进行业务时间范围筛选。

二、解决方法

1、将业务时间字符串类型转换成时间类型。

mysql中字符串转时间：str_to_date(‘字符串时间’,‘%Y-%m-%d %H:%i:%s’)；oracle中字符串转时间：to_date('字符串时间,‘yyyy-MM-dd hh24:mi:ss’)。

2、范围筛选：between … and …

三、具体代码

/**
 * 查询大数据数据列表
 * @param req 请求参数对象
 * @param res 响应参数对象
 * @param ctx
 * @throws SecurityException
 * @throws ServiceException
 */
public void getAllBigData(Map<String, Object> req, Map<String, Object> res,
                              Context ctx) throws SecurityException, ServiceException  {
	Map<String,Object> params = new HashMap<String, Object>();
	String where = getCnd(req,res,ctx);
	StringBuffer buffer = new StringBuffer();
	StringBuffer total = new StringBuffer();
	buffer.append(" FROM X");
	total.append(" FROM X");

	if (null != req.get("cnd")) {
		buffer.append(where);
		total.append(where);
	} else {
		buffer.append(" where 1=1");
		total.append(" where 1=1");
	}

	// 获取mpi标识
	Object mpiFlag = req.get("mpiFlag");
	if (null != mpiFlag){
		buffer.append(" and mpiFlag ='F'");
		total.append(" and mpiFlag ='F'");
	}

	// 获取解包标识
	Object unpackFlag = req.get("unpackFlag");
	if (null != unpackFlag){
		buffer.append(" and unpackFlag ='F'");
		total.append(" and unpackFlag ='F'");
	}

	// 获取业务开始时间
	Object effectiveTime_start = req.get("effectiveTime_start");
    // 获取业务结束时间
	Object effectiveTime_end = req.get("effectiveTime_end");

	if (null != effectiveTime_start && null != effectiveTime_end){
		// 转换为字符串类型的业务时间
		String effectiveTimeStartStr = effectiveTime_start.toString().concat(" 00:00:00";
		String effectiveTimeEndStr = effectiveTime_end.toString().concat(" 23:59:59";

		// 获取数据库配置db.properties
		Properties properties ;
		// 数据库的连接地址
		String dbUrl = null;
		try {
			properties = PropertiesLoaderUtils.loadAllProperties("platform/config/db.properties");
			dbUrl = properties.getProperty("db.url");
		}catch (IOException ioEx){
			ioEx.printStackTrace();
			logger.error("获取数据库配置db.properties异常："+ioEx.getMessage());
		}

		// 数据库的连接地址是包含mysql还是oracle
		if (null != dbUrl && dbUrl.contains("mysql")) {
			// mysql中字符串转时间：str_to_date()
			buffer.append(" and str_to_date(effectiveTime,'%Y-%m-%d %H:%i:%s') between str_to_date('" + effectiveTimeStartStr + "','%Y-%m-%d %H:%i:%s')");
			buffer.append(" and str_to_date('" + effectiveTimeEndStr + "','%Y-%m-%d %H:%i:%s')");
			total.append("  and str_to_date(effectiveTime,'%Y-%m-%d %H:%i:%s') between str_to_date('" + effectiveTimeStartStr + "','%Y-%m-%d %H:%i:%s')");
			total.append("  and str_to_date('" + effectiveTimeEndStr + "','%Y-%m-%d %H:%i:%s')");
		}
		
		if (null != dbUrl && dbUrl.contains("oracle")) {
			// oracle中字符串转时间：to_date()
			buffer.append(" and to_date(effectiveTime,'yyyy-MM-dd hh24:mi:ss') between to_date('" + effectiveTimeStartStr + "','yyyy-MM-dd hh24:mi:ss')");
			buffer.append(" and to_date('" + effectiveTimeEndStr + "','%Y-%m-%d %H:%i:%s')");
			total.append("  and to_date(effectiveTime,'yyyy-MM-dd hh24:mi:ss') between to_date('" + effectiveTimeStartStr + "','yyyy-MM-dd hh24:mi:ss')");
			total.append("  and to_date('" + effectiveTimeEndStr + "','yyyy-MM-dd hh24:mi:ss')");
		}
	}

	Integer page = Integer.parseInt(req.get("pageNo").toString());
	Integer pageSize = Integer.parseInt(req.get("pageSize").toString());
	Integer start = (page - 1) * pageSize;
	List list = SqlUtils.createQuery_NoPage(buffer, params, start, pageSize);
	Integer totalNum = SqlUtils.createQuery_NoPage(total,params).size();
	res.put("body", list);
	res.put("totalCount",totalNum);
}

四、总结

1、数据表字段类型设计，需要考虑好字段类型，选择合适的字段类型。

2、对于字段类型不一样的情况，需要考虑到如果修改字段类型影响大，那就选择转换字段类型。