SQL语句,如果字段不为空,就插入数值,如果为空,就插入空值,大家有没有什么好的实现思路 sql数据库jdbc 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 首先,你说的这一批数据之后,肯定能明确各列的数据类型。然后,你肯定是使用jdbc的批处理去处理。在批处理,输入参数的时候对可能为空的 字符串列,或者可能为空的 number列进行判定。字符串列就改输入“”,number列就改输入0。 可以写个拼sql语句的方法 public static <E> String getInsertSql(String[] tables, E[] args) { StringBuilder sqlSub1 = new StringBuilder(); StringBuilder sqlSub2 = new StringBuilder(); for (int i=0; i<tables.length; i++) { sqlSub1.append(args[i] == null ? "" : tables[i] + ","); sqlSub2.append(args[i] == null ? "" : "?,"); } sqlSub1 = sqlSub1.delete(sqlSub1.length() - 1, sqlSub1.length()); sqlSub2 = sqlSub2.delete(sqlSub2.length() - 1, sqlSub2.length()); return "insert into test (" + sqlSub1 + ") values (" + sqlSub2 + ")"; } public static void main(String[] args) { System.out.println(getInsertSql(new String[]{"a","b","c","d"}, new Integer[]{1,2,3,null})); } public static <E> String getInsertSql(String tableName, String[] fields, E[] args) { StringBuilder sqlSub1 = new StringBuilder(); StringBuilder sqlSub2 = new StringBuilder(); for (int i=0; i<fields.length; i++) { sqlSub1.append(args[i] == null ? "" : fields[i] + ","); sqlSub2.append(args[i] == null ? "" : "?,"); } sqlSub1.delete(sqlSub1.length() - 1, sqlSub1.length()); sqlSub2.delete(sqlSub2.length() - 1, sqlSub2.length()); return "insert into" + tableName + "(" + sqlSub1 + ") values (" + sqlSub2 + ")"; }上面那个小改了一下 使用预处理 String a = "a", b = null, c = "c", d = "d", e = null; PreparedStatement stm = conn.prepareStatement("insert into tb_table (a, b, c, d, e) values (?, ?, ?, ?, ?)"); stm.setString(1, a); if (b == null) stm.setNull(2, Types.INTEGER); else stm.setInt(2, Integer.parseInt(b)); stm.setString(3, c); stm.setString(4, d); if (e == null) stm.setNull(5, Types.FLOAT); else stm.setFloat(5, Float.parseFloat(e)); stm.execute(); stm.close(); 请教---为什么加个按钮就不能监听jframe? 怎么让矩形顺着我画的线溜下来 javamail 开发遇到的一个奇怪问题 怎样让JLabel显示的gif图像静态显示 老师布置的一道题目,不知道该咋作,请高手帮帮忙。 求教:如何辨别一个文件是可执行文件? 一个百思不得其解的问题! 用jbuilder如何生成.exe文件? 高分求救!200 类方法里面不能写循环语句吗? java 中的yield()方法为什么有时候不会让出cpu? 请教:如何发布并保护jar library?
然后,你肯定是使用jdbc的批处理去处理。
在批处理,输入参数的时候对可能为空的 字符串列,或者可能为空的 number列进行判定。
字符串列就改输入“”,number列就改输入0。
StringBuilder sqlSub1 = new StringBuilder();
StringBuilder sqlSub2 = new StringBuilder();
for (int i=0; i<tables.length; i++) {
sqlSub1.append(args[i] == null ? "" : tables[i] + ",");
sqlSub2.append(args[i] == null ? "" : "?,");
}
sqlSub1 = sqlSub1.delete(sqlSub1.length() - 1, sqlSub1.length());
sqlSub2 = sqlSub2.delete(sqlSub2.length() - 1, sqlSub2.length());
return "insert into test (" + sqlSub1 + ") values (" + sqlSub2 + ")";
}
public static void main(String[] args) {
System.out.println(getInsertSql(new String[]{"a","b","c","d"}, new Integer[]{1,2,3,null}));
}
StringBuilder sqlSub1 = new StringBuilder();
StringBuilder sqlSub2 = new StringBuilder();
for (int i=0; i<fields.length; i++) {
sqlSub1.append(args[i] == null ? "" : fields[i] + ",");
sqlSub2.append(args[i] == null ? "" : "?,");
}
sqlSub1.delete(sqlSub1.length() - 1, sqlSub1.length());
sqlSub2.delete(sqlSub2.length() - 1, sqlSub2.length());
return "insert into" + tableName + "(" + sqlSub1 + ") values (" + sqlSub2 + ")";
}上面那个小改了一下
PreparedStatement stm = conn.prepareStatement("insert into tb_table (a, b, c, d, e) values (?, ?, ?, ?, ?)");
stm.setString(1, a);
if (b == null)
stm.setNull(2, Types.INTEGER);
else
stm.setInt(2, Integer.parseInt(b));
stm.setString(3, c);
stm.setString(4, d);
if (e == null)
stm.setNull(5, Types.FLOAT);
else
stm.setFloat(5, Float.parseFloat(e));
stm.execute();
stm.close();