请教高人写一条灵活的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();
}
}
}
}
/**
* 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;
}
}
[解决办法]
import java.util.*;
public class sssss{
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();
}
}
[解决办法]
要达到要求的sql语句一定是组装出来的，不过你可以在不同的地方组装罢了
[解决办法]
你可以把数据库中的TYPE通过JDBC 都取出来存到一个LIST中 ，然后分别对一行数据做循环判断！ 如果要是 ' '的话，就continue ，如果不是‘’ 就进行插入操作
[解决办法]
insert into table
select name,type1,nvl(type2, ' '),nvl(type3, ' ')...nvl(type10, ' ') from dual;
