import java.text.SimpleDateFormat; import java.util.Date; /** * @Author crd * @Date 2020-11-4 下午5:52:35 * @Version 1.0 业务说明:主要用于oracle拼接sql,handleNull方法的参数说明,第二个布尔值是指是否需要添加逗号 * */ public class SqlAddUtil { public static String handleNull(Date d, Boolean isNeedComma) { SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); String s = null; if (d != null) { if (isNeedComma) { s = "to_date('" + sdf.format(d) + "', 'YYYYMMdd')" + ","; } else { s = "to_date('" + sdf.format(d) + "', 'YYYYMMdd')"; } } else { if (isNeedComma) { s = null + ","; } else { s = null; } } return s; } public static String handleNull(String str, Boolean isNeedComma) { String s = null; if (str != null) { if (isNeedComma) { s = "'" + str + "'" + ","; } else { s = "'" + str + "'"; } } else { if (isNeedComma) { s = null + ","; } else { s = null; } } return s; } public static String handleNull(Double d, Boolean isNeedComma) { String s = ""; if (d != null) { if (isNeedComma) { s = d + ","; } else { s = d + ""; } } else { if (isNeedComma) { s = null + ","; } else { s = null; } } return s; } public static String handleNull(Float f, Boolean isNeedComma) { String s = ""; if (f != null) { if (isNeedComma) { s = f + ","; } else { s = f + ""; } } else { if (isNeedComma) { s = null + ","; } else { s = null; } } return s; } public static String handleNull(Long l, Boolean isNeedComma) { String s = ""; if (l != null) { if (isNeedComma) { s = l + ","; } else { s = l + ""; } } else { if (isNeedComma) { s = null + ","; } else { s = null; } } return s; } public static String handleNull(Integer i, Boolean isNeedComma) { String s = ""; if (i != null) { if (isNeedComma) { s = i + ","; } else { s = i + ""; } } else { if (isNeedComma) { s = null + ","; } else { s = null; } } return s; } }
功能:主要用于oracle拼接sql,handleNull方法的参数说明,第二个布尔值是指是否需要添加逗号
方法
使用列子:
String sql = "INSERT INTO W_FHYCGZ(MY_ID_,WDID,RQ,XQ,SJWDZG,SJWDZD,TQ,FHID,ZDFHZG,ZDFHZD,TDFHZG,TDFHZD,QWGDL,ZBGDL,ZDEMSGDFH,TDXSDDL,ZDTDXSDDL,ZDFDL,ZXFDL,ZDGFDL,GF14SDL,BZ,ZT,BYZD,BYZDDATE) VALUES " + "(" + handleNull(e.getMyId(),true) + handleNull(e.getWdid(),true) + handleNull(e.getRq(),true) + handleNull(e.getXq(),true) + handleNull(e.getSjwdzg(),true) + handleNull(e.getSjwdzd(),true) + handleNull(e.getTq(),true) + handleNull(e.getFhid(),true) + handleNull(e.getZdfhzg(),true) + handleNull(e.getZdfhzd(),true) + handleNull(e.getTdfhzg(),true) + handleNull(e.getTdfhzd(),true) + handleNull(e.getQwgdl(),true) + handleNull(e.getZbgdl(),true) + handleNull(e.getZdemsgdfh(),true) + handleNull(e.getTdxsddl(),true) + handleNull(e.getZdtdxsddl(),true) + handleNull(e.getZdfdl(),true) + handleNull(e.getZxfdl(),true) + handleNull(e.getZdgfdl(),true) + handleNull(e.getGf14sdl(),true) + handleNull(e.getBz(),true) + handleNull(e.getZt(),true) + handleNull(e.getByzd(),true) + handleNull(e.getByzddate(),false) + ")"; System.out.println("==================================================="+sql); jdbcTemplate.execute(sql);