MySQL和Oracle数据库sql查询日期比较条件的差异

背景

最近项目中有个需求.需要查询更新时间大于发布时间的对象;同时还要适配MySQL和Oracle两种数据库;

实现

思路

1.对传进来的时间参数进行格式化;
2.通过读取配置文件来判断是MySQL数据库还是Oracle数据库;
3.分别使用不同的sql语句拼接

 SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String dateString = formatter.format(lastupdate);
        String hql = "";

        List<Object> values = new ArrayList<>();

        try{
            if (Constant.MYSQL.equals(sopConfigReader.getDatabase())) {
                hql = "from Epgposition t where t.type != '12' and t.epgpage.epgpageid = " + pageid;
                hql += " and t.lastupdatedate > str_to_date(?,'%Y/%m/%d %H:%i:%s')";
                hql += " and t.status != '0' and t.status != '2' ";
            }else {
                hql = "from Epgposition t where t.type != '12' and t.epgpage.epgpageid = " + pageid;
                hql += " and t.lastupdatedate > to_date(to_char(?),'yyyy/mm/dd hh24:mi:ss')";
                hql += " and t.status != '0' and t.status != '2' ";
            }
            values.add(dateString);
        }catch(Exception e){
            log.error(e.getMessage());
        }

区别

1.mysql

hql += " and t.lastupdatedate > str_to_date(?,'%Y-%m-%d %H:%i:%s')";

2.oracle

hql += " and t.lastupdatedate > to_date(to_char(?),'yyyy/mm/dd hh24:mi:ss')";

3.主要区别
对日期的处理上两者有所不同
mysql采用str_to_date(?,时间格式)
oracle采用to_date(to_char(?),时间格式)