oracle与mysql转换工具类
import org.apache.commons.lang3.math.NumberUtils; public class DBTUtil { public static String to_char(String val , int format){ String returnValue = ""; if (val.contains("@")){ int day = NumberUtils.toInt(val.substring(1,val.length())); val = currentDate(day); } if (format == 1) { if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y-%m-%d')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y-%m-%d')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyy-mm-dd')"; } } else if (format == 2) { if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y-%m-%d %T')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y-%m-%d %H:%M:%S')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyy-mm-dd hh24:mi:ss')"; } } else if (format == 3) { if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y-%m-%d %H:%i')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y-%m-%d %H:%M')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyy-mm-dd hh24:mi')"; } } else if (format == 4) { if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y-%m-%d %H')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y-%m-%d %H')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyy-mm-dd hh24')"; } }else if(format==5){ if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y%m%d')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y%m%d')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyymmdd')"; } }else if(format==6){ if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y%m')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y%m')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyymm')"; } } else if (format == 7) { if (DbUtil.isMysql()) { returnValue = "date_format(" + val + ",'%H')"; } else if (DbUtil.isInformix()) { returnValue = "to_char(" + val + ", '%H')"; } else if (DbUtil.isOracle()) { returnValue = "to_char(" + val + ",'hh24')"; } }else if(format==8){ if (DbUtil.isMysql()) { returnValue = "date_format("+val+",'%Y')"; } else if (DbUtil.isInformix()) { returnValue = "to_char("+val+", '%Y')"; } else if (DbUtil.isOracle()) { returnValue = "to_char("+val+",'yyyy')"; } } return returnValue; } public static String current() { String returnValue = null ; if (DbUtil.isInformix()) { returnValue = "current"; } else if (DbUtil.isOracle()) { returnValue = "sysdate"; } else if (DbUtil.isMysql()) { returnValue = "now()"; } return returnValue; } public static String current(int days) { String returnValue = null ; if (DbUtil.isInformix()) { returnValue = "current"; } else if (DbUtil.isOracle()) { returnValue = " sysdate + "+days; } else if (DbUtil.isMysql()) { returnValue = " DATE_ADD(NOW(),INTERVAL "+(days)+" DAY)"; } return returnValue; } public static String currentStr(String days) { String returnValue = null ; if (DbUtil.isInformix()) { returnValue = "current"; } else if (DbUtil.isOracle()) { returnValue = " sysdate + " + days; } else if (DbUtil.isMysql()) { returnValue = " DATE_ADD(NOW(),INTERVAL "+(days)+" DAY)"; } return returnValue; } /** * 当前日期之前、之后时间 * @param days * @return */ public static String currentDate(int days) { String returnValue = null ; if (DbUtil.isOracle()) { returnValue = " trunc(sysdate +"+days+") "; } else if (DbUtil.isMysql()) { returnValue = " DATE_SUB(CURDATE(),INTERVAL -"+(days)+" DAY) "; } return returnValue; } public static String truncBeforeDayMonthFormat(int days){ if (DbUtil.isOracle()) { return " TRUNC(SYSDATE-"+days+", 'MM') " ; }else { return " DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())+"+days+" DAY) " ; } } /** * 向日期添加指定的时间间隔 * @param pMonth * @param month * @return */ public static String addMonth(String pMonth, int month){ String returnValue = null ; if (DbUtil.isOracle()) { returnValue = " trunc(add_months(" + pMonth + " ," + month + "),'dd') "; } else if (DbUtil.isMysql()) { returnValue = "DATE_ADD(" + pMonth + ",INTERVAL " + (month) + " MONTH)"; } return returnValue; } /** * 返回to_date(),只返回日期，不包括时间 * * @return */ public static String to_date(String value) { if (value == null || "".equals(value)) { return ""; } String returnValue = ""; // value : '2007-01-01 01:01:01' OR value : '2007-01-01' String[] datetime = value.split(" "); if (datetime.length == 1) { if (DbUtil.isMysql()) { returnValue = "str_to_date('" + value + "','%Y-%m-%d')"; } else if (DbUtil.isInformix()) { returnValue = "to_date('" + value + "', '%Y-%m-%d')"; } else if (DbUtil.isOracle()) { returnValue = "to_date('" + value + "','yyyy-mm-dd')"; } } else if (datetime.length == 2) { if (DbUtil.isMysql()) { returnValue = "str_to_date('" + value + "','%Y-%m-%d %T')"; } else if (DbUtil.isInformix()) { returnValue = "to_date('" + value + "', '%Y-%m-%d %H:%M:%S')"; } else if (DbUtil.isOracle()) { returnValue = "to_date('" + value + "','yyyy-mm-dd hh24:mi:ss')"; } } return returnValue; } public static String to_date(String val , int format) { String returnValue = ""; if (format == 1) { if (DbUtil.isMysql()) { returnValue = "str_to_date("+val+",'%Y-%m-%d')"; } else if (DbUtil.isInformix()) { returnValue = "to_date("+val+", '%Y-%m-%d')"; } else if (DbUtil.isOracle()) { returnValue = "to_date('"+val+"','yyyy-mm-dd')"; } } else if (format == 2) { if (DbUtil.isMysql()) { returnValue = "str_to_date("+val+",'%Y-%m-%d %T')"; } else if (DbUtil.isInformix()) { returnValue = "to_date("+val+", '%Y-%m-%d %H:%M:%S')"; } else if (DbUtil.isOracle()) { returnValue = "to_date('"+val+"','yyyy-mm-dd hh24:mi:ss')"; } }else if(format == 3){ if (DbUtil.isMysql()) { returnValue = "str_to_date("+val+",'%Y-%m')"; } else if (DbUtil.isInformix()) { returnValue = "to_date("+val+", '%Y-%m')"; } else if (DbUtil.isOracle()) { returnValue = "to_date('"+val+"','yyyy-mm')"; } } return returnValue; } /** * 有些数据库字段传进来，不加引号，用此方法 * @param val * @param format * @return */ public static String to_sql_date(String val , int format) { String returnValue = ""; if (format == 1) { if (DbUtil.isMysql()) { returnValue = "str_to_date("+val+",'%Y-%m-%d')"; } else if (DbUtil.isInformix()) { returnValue = "to_date("+val+", '%Y-%m-%d')"; } else if (DbUtil.isOracle()) { returnValue = "to_date("+val+",'yyyy-mm-dd')"; } } else if (format == 2) { if (DbUtil.isMysql()) { returnValue = "str_to_date("+val+",'%Y-%m-%d %T')"; } else if (DbUtil.isInformix()) { returnValue = "to_date("+val+", '%Y-%m-%d %H:%M:%S')"; } else if (DbUtil.isOracle()) { returnValue = "to_date("+val+",'yyyy-mm-dd hh24:mi:ss')"; } }else if(format == 3){ if (DbUtil.isMysql()) { returnValue = "str_to_date("+val+",'%Y-%m')"; } else if (DbUtil.isInformix()) { returnValue = "to_date("+val+", '%Y-%m')"; } else if (DbUtil.isOracle()) { returnValue = "to_date("+val+",'yyyy-mm')"; } } return returnValue; } public static String to_date(int format) { String returnValue = ""; if (format == 1) { if (DbUtil.isMysql()) { returnValue = " str_to_date(?,'%Y-%m-%d') "; } else if (DbUtil.isInformix()) { returnValue = " to_date(?, '%Y-%m-%d') "; } else if (DbUtil.isOracle()) { returnValue = " to_date(?,'yyyy-mm-dd') "; } } else if (format == 2) { if (DbUtil.isMysql()) { returnValue = " str_to_date(?,'%Y-%m-%d %T') "; } else if (DbUtil.isInformix()) { returnValue = " to_date(?, '%Y-%m-%d %H:%M:%S') "; } else if (DbUtil.isOracle()) { returnValue = " to_date(?,'yyyy-mm-dd hh24:mi:ss') "; } }else if (format == 3) { if (DbUtil.isMysql()) { returnValue = " str_to_date(?,'%Y%m%d') "; } else if (DbUtil.isInformix()) { returnValue = " to_date(?, '%Y%m%d') "; } else if (DbUtil.isOracle()) { returnValue = " to_date(?,'yyyymmdd') "; } } return returnValue; } private static int getDatePatternType(String pattern){ return pattern.split(" ").length ; } public static String date_pattern(String pattern) { if (DbUtil.isMysql()) { if (!pattern.startsWith("%")) return pattern; if (1 == getDatePatternType(pattern)) return "%Y-%m-%d"; return "%Y-%m-%d %T"; } else if (DbUtil.isInformix()) { if (pattern.startsWith("%")) return pattern; if (1 == getDatePatternType(pattern)) return "%Y-%m-%d"; return "%Y-%m-%d %H:%M:%S"; } else { if (!pattern.startsWith("%")) return pattern; if (1 == getDatePatternType(pattern)) return "yyyy-mm-dd"; return "yyyy-mm-dd hh24:mi:ss"; } } /** * *1.MySQL采用地址方式处理主键,主键字段不用显示出来 *2.Oracle采用字段显式方式字段与序列关联处理 * * @param field 数据库字段 * @return */ public static String seqF(String field){ if (DbUtil.isMysql()) return "" ; else return field+"," ; } /** * *1.MySQL采用地址方式处理主键,主键字段不用显示出来 *2.Oracle采用字段显式方式字段与序列关联处理 * * @param val 数据库字段 * @return */ public static String seqV(String val){ if (DbUtil.isMysql()) return "" ; else return val+".nextval ," ; } public static String andRownum(String row) { String sql = ""; // boolean isNumberic = DataUtil.isNumeric(row) ; if (DbUtil.isOracle()) { sql = " and rownum<=" + row; } else if (DbUtil.isMysql()) { sql = " limit " + row; } return sql; } public static String andEqRownum(String row) { String sql = ""; // boolean isNumberic = DataUtil.isNumeric(row) ; if (DbUtil.isOracle()) { sql = " and rownum=" + row; } else if (DbUtil.isMysql()) { sql = " limit " + row; } return sql; } public static String month1stDate(){ if (DbUtil.isOracle()) { return " TRUNC(SYSDATE, 'MM') " ; }else { return " DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) " ; } } public static String timeCmp(String f1 , String opt , String f2){ if (DbUtil.isOracle()) { return f1+ opt + f2 ; }else{ return "UNIX_TIMESTAMP("+f1+")"+ opt + "UNIX_TIMESTAMP("+f2+")"; } } public static String timesub(String end_date, String start_date){ if(DbUtil.isMysql()){ return "timestampdiff(second,"+start_date+","+end_date+")"; }else{ return "ceil(("+end_date+"-"+start_date+") * 24 * 60 * 60)"; } } /** * decode转换 * @param field * @param cond1 * @param v1 * @param strings * @return */ public static String decode(String field , String cond1 , String v1 , String...strings ){ StringBuilder sql = new StringBuilder() ; if (DbUtil.isOracle()) { sql.append("decode(").append(field).append(",").append(cond1).append(",").append(v1) ; if(strings != null){ for(int i= 0 ; i < strings.length ; i++){ sql.append(",").append(strings[i]) ; } } sql.append(")") ; }else { sql.append("if(").append(field) ; if(cond1.equalsIgnoreCase("null")){ sql.append(" is null ").append(",").append(v1) ; }else{ sql.append("=").append(cond1).append(",").append(v1) ; } if(strings != null){ if(strings.length == 1){ sql.append(",").append(strings[0]) ; }else { int ifNum = 0 ; for(int i= 0 ; i < strings.length ; i++){ ifNum ++ ; sql.append(",").append("if(").append(field) ; cond1 = strings[i] ; i++ ; v1 = strings[i] ; if(cond1.equalsIgnoreCase("null")){ sql.append(" is null ").append(",").append(v1) ; }else{ sql.append("=").append(cond1).append(",").append(v1) ; } if(i == strings.length-1){ sql.append(",").append("null") ; }else if(i+2 == strings.length-1){ i+=2 ; sql.append(",").append(strings[i]) ; }else if(i ==strings.length-2 ){ i++ ; sql.append(",").append(strings[i]) ; } } for(int i = 0 ; i<ifNum ; i++){ sql.append(")") ; } } } sql.append(")") ; } return sql.toString() ; } public static String addDays(String val, int days) { String returnValue = null ; if (DbUtil.isOracle()) { returnValue = " "+ val+days; } else if (DbUtil.isMysql()) { returnValue = " DATE_ADD("+val+",INTERVAL "+(days)+" DAY) "; } return returnValue; } public static void main(String[] a ){ System.out.println(to_char("@-1", 1)); } /*替换nvl函数*/ public static String nvl(){ if (DbUtil.isOracle()){ return "NVL"; } if(DbUtil.isMysql()){ return "IFNULL"; } return null; } /*替换to_char函数*/ public static String toChar(String val){ if (DbUtil.isOracle()){ return "to_char("+ val +")"; } if(DbUtil.isMysql()){ return "cast(" + val + " as char)"; } return null; } /*替换listagg函数*/ public static String listagg(String val1, String val2){ if (DbUtil.isOracle()){ return "listagg(" + val1 + ", ',') within group(order by " + val2 + ")"; } if(DbUtil.isMysql()){ return "group_concat(" + val1 + ", ',')"; } return null; } /*替换cast函数*/ public static String castTrans(String val){ if (DbUtil.isOracle()){ return "cast(" + val + " as NVARCHAR2(10))"; } if(DbUtil.isMysql()){ return "cast(" + val + " as char(10))"; } return null; } /*begin end函数*/ public static String beginEnd(int format){ if (DbUtil.isOracle()){ if(format == 1) { return "begin"; } else if(format == 2) { return ";end;"; } } if(DbUtil.isMysql()){ return null; } return null; } public static String toNumber(String val){ if (DbUtil.isOracle()){ return "TO_NUMBER ("+ val +")"; } if(DbUtil.isMysql()){ return "cast("+ val + " AS DECIMAL (16))"; } return null; } public static String toTrunc(int num,String val){ if (DbUtil.isOracle()){ return "TRUNC("+ num +"/"+ val +")"; } if(DbUtil.isMysql()){ return "FLOOR("+ num +"/"+ val +")"; } return null; } }
*mapper.xml引用工具类方法例如：
<if test="endDateStr!=null and endDateStr!=''"> and ${@com.ztesoft.contract.base.util.db.DBTUtil@to_char("t.create_Date",1)} <![CDATA[<=]]> #{endDateStr} </if>
