请教高人写一条灵活的sql语句
请教高人写一条灵活的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语句怎么写呢?
------解决方案--------------------null是sql里面的关键字,直接写null(字符串)就行了。不用特殊处理
------解决方案--------------------case when type2 is not null then type2 else null end,
------解决方案--------------------string sql_ins = "insert into targetTable ( ";
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;
------解决方案--------------------動態組字符SQL
------解决方案--------------------nvl()函数
------解决方案--------------------不知道下面的这些代码是否能满足你的要求,全是动态的
import java.sql.Connection;
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();
}
}
}
}