java String型时间类型与数据库中Data进行比较

在执行sql语句过程中，需要通过前台获取的String类型数据与数据库中的Data型数据进行比较，从而查询满足某段时间的数据：

例：通过从前台获取String类型时间，从数据库中查询在此时间之前的订单

　　解决思路：将获取的String型时间转换为Long类型的时间戳（单位为s），执行查询语句时，写sql过程中，将获取的数据库中的时间转换为时间戳，两者进行比较。

Controller层：

public Object getAllOrdersForMember(
@RequestParam(value = "createTime", required = false, defaultValue = "newDate") String createTime,
@RequestParam(value = "goodsOrder2Id", required = false, defaultValue = "") String goodsOrder2Id,
HttpServletRequest request) {
/**
* Parameter check
*/
Member member = memberService.checkIdAndToken(request);
if (member == null) {
return new APIResult(GlobalConstantsUtil.GENERAL_ERROR, "登录失效");
}

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = null;
// 如果传入时间，获取传入时间的时间戳，如果未传入时间，获取当前时间的时间戳
try {
if (createTime.equals("newDate")) {
date = new Date();
}
date = simpleDateFormat.parse(createTime);
} catch (ParseException e1) {
return new APIResult(GlobalConstantsUtil.GENERAL_ERROR, "获取时间失败");
}
Long createTime1 = date.getTime() / 1000;

List<GoodsOrder2> goodsOrderList = null;

try {
goodsOrderList = goodsOrderTwoService.findByMemberIdAndCreateTime(member.getId(), createTime1,
goodsOrder2Id);
} catch (Exception e) {
return new APIResult(GlobalConstantsUtil.GENERAL_ERROR, "获取字段失败");
}
if (goodsOrderList == null || goodsOrderList.size() == 0) {
return null;
}
Map<String, Object> map = new HashMap<String, Object>();
map.put("orderList", goodsOrderList);
return APIResult.createSuccess(map);
}

service层：

public List<GoodsOrder2> findByMemberIdAndCreateTime(String memberId, Long createTime1, String goodsOrder2Id) {
return goodsOrderTwoRepository.findByMemberIdAndCreateTime(memberId, createTime1, goodsOrder2Id);
}

repository层：

@Query(value = "SELECT * FROM goods_order2 WHERE member_id = ?1 AND UNIX_TIMESTAMP(create_time)<= ?2 AND id != ?3 ORDER BY create_time DESC LIMIT 0,10;", nativeQuery = true)
List<GoodsOrder2> findByMemberIdAndCreateTime(String memberId, Long createTime1, String goodsOrder2Id);

注意：UNIX_TIMESTAMP(create_time) 为将数据库中的Data型数据转为Long型的时间戳，单位：s；

在数据库中执行结果如下：