import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
public class TestSql
{
public static void main(String[] args) throws Exception{
// String paraSql="provider_id in(select p.provider_id from t_provider p where p.attribution_id = 1)";
// String bmsTables="t_provider@attribution_id=1,t_provider_contact@"+paraSql+",t_operator@"+paraSql+",t_content_provider@"+paraSql+","+
// "t_service_provider@"+paraSql+",T_PROVIDER_BUSINESS@"+paraSql+","+
// "T_PROSPEC_R,T_PROD_RESOURCE_R,t_productoffering_r,"+
// "t_price_policy,t_prodoffering_price_r,t_promotion_policy,t_prodoffering_promotion_r";
// String ip="172.20.14.6";
// String user="bmsb036gd_0716";
String iepgmTables="T_COLUMN@parent_id!=0,T_IEPG_ASSET,T_IEPG_ASSET_FILE,T_IEPG_CHANNEL_TYPE,T_IEPG_CHANNEL,T_IEPG_CHANNEL_TYPE_RELATION,T_IEPG_GOODS" +
",T_IEPG_PROGRAM_GUIDE,T_IEPG_PROVIDER,T_NPVR_RECORD,T_PRODOFFERING,T_PO_RESOURCE,T_REGION_FREQ,T_RESOURCE_POSTER,T_RES_COLUMN_MAP,T_USERINFO,T_USER_RECOMMAND";
String ip="172.20.100.25";
String user="sdp_xxx_b052";
printSql(iepgmTables,ip,user,"xxxxxx");
}
private static void printSql(String tables,String ip,String user,String pass) throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = null;
try{
con = DriverManager.getConnection("jdbc:oracle:thin:@"+ip+":1521:orcl",user,pass);
Map<String ,Object> tempMap = new HashMap<String ,Object>();
for(String table : tables.split(",")){
if(tempMap.containsKey(table)){
continue;
}else{
tempMap.put(table, table);
}
String[] tw = table.split("@");
table = tw[0];
String where =null;
if(tw.length==2){
where = tw[1];
}
String gen = getSql(con, table,where,user);
System.out.println(gen);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(con!=null) con.close();
}
}
private static String getSql(Connection con,String table,String where,String owner)throws Exception{
String sql="select column_name,data_type from all_tab_columns where table_name='"+table.toUpperCase()+"' AND owner='"+owner.toUpperCase()+"'";
ResultSet rs = con.createStatement().executeQuery(sql);
String gen="select 'insert into "+table.toLowerCase()+"(`";
List<String> cols = new ArrayList<String>();
List<String> values = new ArrayList<String>();
while(rs.next()){
String col = rs.getString("COLUMN_NAME");
cols.add(col);
String type= rs.getString("DATA_TYPE");
if(type.contains("DATE")||type.contains("TIME")){
values.add("'||decode("+col+",null,'null',''''||to_char("+col+",'yyyy-mm-dd hh24:mi:ss')||'''')||'");
}else if(type.contains("CHAR")){
values.add("'||decode("+col+",null,'null',''''||"+col+"||'''')||'");
}else if(type.contains("NUMBER")){
values.add("'||decode("+col+",null,'null',"+col+")||'");
}
}
gen += StringUtils.join(cols.toArray(),"`,`")+"`) values (";
gen += StringUtils.join(values.toArray(),",")+");' from "+table+(where==null?"":" where "+where)+";";
return gen;
}
}