sybase库中导出全部表的oracle、mysql和sybase的建表语句

package tdh.db.gen; import java.io.File; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.commons.io.FileUtils; import org.apache.log4j.Logger; import tdh.utils.DBUtils; import tdh.utils.LoggerFactory; /** * * Sybase 类型数据库 导出oracle和mysql的建表语句 * */ public class DbGenCreateSql { private static Map<Integer,String> systypes = new HashMap<Integer,String>(); private static Logger log = new LoggerFactory(DbGenCreateSql.class.toString()).getLogger(); /** * @param args * @throws Exception */ public static void main(String[] args) throws Exception { DBUtils.init(); List<DbTable> tablist2 = new ArrayList<DbTable>(); Connection srcconn = null; try{ srcconn = DBUtils.getSrcConnection(); setSystypes(srcconn); Map<String,String> tabsmap1 = getAllUserTabs(srcconn); int n1 = 1; log.debug("数据库建表语句生成中....."); for(Map.Entry<String, String> entry : tabsmap1.entrySet()){ String tabname = entry.getKey(); String tabid = entry.getValue(); log.debug("====正在处理："+n1+"."+tabname); DbTable tab = new DbTable(tabname,(n1++)+". "+tabname,0); List<String> pklist = getPrimaryKeyList(srcconn,tabname); List<DbColumn> collist = getTabcols(srcconn,tabname,tabid,pklist); // 获取索引信息 Map<String,DbIndex> indexMap = getIndexInfo(srcconn,tabname); tab.setCollist(collist); tab.setIndexMap(indexMap); tablist2.add(tab); } List<String> oracleList =new ArrayList<String>(); List<String> mysqlList =new ArrayList<String>(); List<String> sybaseList =new ArrayList<String>(); int tableCount = tablist2.size(); sybaseList.add("/**一共导出"+tableCount+"张表\n **/"); sybaseList.add("/**==========================**/"); for(DbTable tab:tablist2) { //log.debug(tab.getTabname()+":生成sql中....."); sybaseList= addTable(tab,sybaseList,0); sybaseList= addIndex(tab,sybaseList,0); oracleList= addTable(tab,oracleList,1); oracleList= addIndex(tab,oracleList,1); mysqlList= addTable(tab,mysqlList,4); mysqlList= addIndex(tab,mysqlList,4); } File fileSybase = new File("sybase数据库建表.sql"); File fileOracle = new File("oracle数据库建表.sql"); File fileMysql = new File("mysql数据库建表.sql"); FileUtils.writeLines(fileSybase,"utf-8", sybaseList); FileUtils.writeLines(fileOracle,"utf-8", oracleList); FileUtils.writeLines(fileMysql,"utf-8", mysqlList); //log.debug("数据库建表语句生成成功，生成的文件路径在:"+DBUtils.OUT_PATH); log.debug("数据库建表语句生成结束"); }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.closeConn(srcconn); } } public static List<String> changeType(DbColumn col1 , DbColumn col2 , String tableName , List<String> list){ StringBuilder sb = new StringBuilder(); sb.append("alter table "); sb.append(tableName); sb.append(" modify "); sb.append(col2.getColname()); sb.append(" "); sb.append(col1.getColtype()); sb.append(" "); sb.append(col1.getIsnull()); sb.append(";"); String sql = sb.toString(); list.add(sql); return list; } public static List<String> changeIsNull(DbColumn col1 , DbColumn col2 , String tableName , List<String> list){ StringBuilder sb = new StringBuilder(); sb.append("alter table "); sb.append(tableName); sb.append(" modify "); sb.append(col2.getColname()); sb.append(" "); sb.append(col1.getIsnull()); sb.append(";"); String sql = sb.toString(); list.add(sql); return list; } public static List<String> deleteCol(DbColumn col2 , String tableName , List<String> list){ StringBuilder sb = new StringBuilder(); sb.append("alter table "); sb.append(tableName); sb.append(" drop "); sb.append(col2.getColname()); sb.append(";"); String sql = sb.toString(); list.add(sql); return list; } public static List<String> addCol(DbColumn col1 , String tableName , List<String> list){ StringBuilder sb = new StringBuilder(); sb.append("alter table "); sb.append(tableName); sb.append(" add "); sb.append(col1.getColname()); sb.append(" "); sb.append(col1.getColtype()); sb.append(" "); sb.append(col1.getIsnull()); sb.append(";"); String sql = sb.toString(); list.add(sql); return list; } public static List<String> deleteTable(String tableName , List<String> list){ StringBuilder sb = new StringBuilder(); sb.append("drop "); sb.append("table "); sb.append(tableName); sb.append(";"); String sql = sb.toString(); list.add(sql); return list; } /** * 生成建表语句 * @param tab * @param list * @param dbType * @return */ public static List<String> addTable(DbTable tab , List<String> list,int dbType){ StringBuilder sb = new StringBuilder(); sb.append("\r\n"); sb.append("create table "); String tabName =tab.getTabname(); sb.append(tabName); sb.append(" ( "); list.add(sb.toString()); sb.delete(0, sb.length()); StringBuffer pkcols = new StringBuffer(); int i = 0,size =0; if(null != tab.getCollist()){ size = tab.getCollist().size(); } for(DbColumn col : tab.getCollist()){ i++; String colName= escapeKeyWord(col.getColname(),dbType); String colType= " "+col.getColtype()+" "; colType = convertSql(colType, dbType); String colDef=col.getColDefault(); if(1==dbType||4==dbType) { colDef = colDef.replaceAll("\"", "'").replaceAll("\\(", "").replaceAll("\\)", ""); } sb.append(" "); sb.append(colName); sb.append(" "); sb.append(colType); sb.append(" "); if(isEmpty(colDef)) { sb.append(col.getIsnull()); } else { sb.append(" DEFAULT "+colDef+" null "); if("1".equals(col.getPk())){ sb.append(col.getIsnull()); } } sb.append(","); if(i < size){ list.add(sb.toString()); }else{ sb = sb.deleteCharAt(sb.length()-1); list.add(sb.toString()); } sb.delete(0 , sb.length()); if("1".equals(col.getPk())){ pkcols.append(",").append(colName); } } sb.append(");"); list.add(sb.toString()); if(pkcols.length()>0){ StringBuffer pkbuf = new StringBuffer(); String cols = pkcols.toString().replaceFirst(",", ""); String pkSql = "alter table "+tabName+" add constraint "+ ("PK_"+tab.getTabname()) +" primary key nonclustered ("+cols+");"; if(1==dbType) { pkSql = pkSql.replaceAll(" (?i)clustered ", ""); pkSql = pkSql.replaceAll(" (?i)nonclustered ", ""); } pkbuf.append(pkSql) ; list.add(pkbuf.toString()); } //list.add("\r\n"); return list; } /** * 生成建索引语句 * @param tab * @param list * @param dbType * @return */ public static List<String> addIndex(DbTable tab , List<String> list,int dbType){ Map<String,DbIndex> indexMap = tab.getIndexMap(); if(indexMap.size()>0) { String tabName= tab.getTabname(); for (String indexCol : indexMap.keySet()) { DbIndex dbIndex = indexMap.get(ind