请教高人写一条灵活的sql语句:要求:如数据库中字段NAME(Not null),TYPE1(Not null),TYPE2,TYPE3,TYPE4,TYPE5,TYPE6,TYPE7,TYPE8,TYPE9,TYPE10
,现在想通过JDBC向数据库Insert一条记录,但是,TYPE2~TYPE10的值要求在程序中进行判断,如果Type2为null,就在SQL语句中不对TYPE2处理,如果不为null,在SQL语句中就加入Type2字段和值。依此类推!!!这样的话SQL语句怎么写呢?
,现在想通过JDBC向数据库Insert一条记录,但是,TYPE2~TYPE10的值要求在程序中进行判断,如果Type2为null,就在SQL语句中不对TYPE2处理,如果不为null,在SQL语句中就加入Type2字段和值。依此类推!!!这样的话SQL语句怎么写呢?
string sql_val = "values (";
sql_ins += "name,type1";
sql_val += "'" + o.getName() + "',";
sql_val += "'" + o.getType1() + "'";
if (!(o.getType2() == null || "".equals(o.getType2())))
{
sql_ins += ",type2";
sql_val += ",'" + o.getType2() + "'";
}//TYPE3-Type10的处理与此相同
sql_val += ")";
sql_ins + ")";
string sql = sql_ins + " " + sql_val;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import java.util.TreeMap;public class Insert { public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con = null;
PreparedStatement ps = null;
try {
String url = "jdbc:mysql://localhost:3306/bookstore";
String user = "root";
String password = "root";
con = DriverManager.getConnection(url, user, password);
Tbl tbl = new Tbl();
tbl.setName("pengyiming");
tbl.setType1("a");
tbl.setType3("c");
Map<String, String> map = tbl.getMap();
// 通过 map 生成 SQL
StringBuffer type = new StringBuffer("INSERT INTO tbl (");
StringBuffer value = new StringBuffer(" VALUES (");
for(Map.Entry<String, String> entry : map.entrySet()) {
type.append(entry.getKey()).append(",");
value.append("?,");
}
String sql = type.append(")").append(value).append(")").toString();
sql = sql.replace(",)", ")");
ps = con.prepareStatement(sql);
// 通过 map 设置 PreparedStatement 的值
int i = 1;
for(Map.Entry<String, String> entry : map.entrySet()) {
ps.setString(i, entry.getValue());
i++;
}
ps.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally{
try {
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}/**
* Tbl 的 POJO 类,在每个 set 方法中,增加 map.put
* 方便起见,只设置了三个
*/
class Tbl {
private String name;
private String type1;
private String type2;
private String type3;
private Map<String, String> map = new TreeMap<String, String>();
public Tbl() {
}
public Tbl(String name, String type1) {
this.name = name;
this.type1 = type1;
map.put("name", name);
map.put("type1", type1);
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
map.put("name", name);
}
public String getType1() {
return type1;
}
public void setType1(String type1) {
this.type1 = type1;
map.put("type1", type1);
}
public String getType2() {
return type2;
}
public void setType2(String type2) {
this.type2 = type2;
map.put("type2", type2);
}
public String getType3() {
return type3;
}
public void setType3(String type3) {
this.type3 = type3;
map.put("type3", type3);
}
public Map<String, String> getMap() {
return map;
}
}
public void createSql()
{
String[] listName= {"NAME","TYPE1","TYPE2","TYPE3","TYPE4","TYPE5","TYPE6","TYPE7","TYPE8","TYPE9","TYPE10"};
ArrayList list =new ArrayList();
list.add("NAME");
list.add("TYPE1");
list.add("");
list.add("TYPE3");
list.add("");
list.add("");
list.add("TYPE6");
list.add("");
list.add("");
list.add("");
list.add("TYPE10");
String sql = "insert into sss(";
String sql2 ="";
for(int i = 0; i < listName.length; i++)
{
if(((String)list.get(i)).trim().length() != 0)
{
sql += listName[i];
sql2+="'" + ((String)list.get(i)).trim() + "'";
if(i <= listName.length - 1)
{
sql += ",";
sql2 += ",";
}
}
}
sql = sql.substring(0, sql.lastIndexOf(","));
sql += ") values (";
sql2 = sql2.substring(0, sql2.lastIndexOf(","));
sql2 +=")";
System.out.println(sql+sql2);
//return oper.insertRecord(sql+sql2);
}
public static void main(String args[]){
sssss s= new sssss();
s.createSql();
}
}
我只是没有给你写出来罢了。
用反射的话就不用每个TYPE写个if判断了。可以在循环中,通过method的name来辨别如何处理。
我上面那段代码不简洁的地方就在那块了。其他的基本上每个人都会是那个思路,差别不会太大。
select name,type1,nvl(type2,''),nvl(type3,'')...nvl(type10,'') from dual;