Oracle数据库连接生成DDL

package com.bbkj;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Created by Administrator on 2016/12/7.
 */
public class DbUtils {

    public static void main(String [] args){
        testOracle();
    }
    /**
     * 
     */
    public static void testOracle()
    {
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            String url = "jdbc:oracle:thin:@10.248.2.111:1521:orcl";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
            String user = "etrackchb";// 用户名,系统默认的账户名
            String password = "etrackchb";// 你安装时选设置的密码
            con = DriverManager.getConnection(url, user, password);// 获取连接
            String sql = "select * from user_tables";// 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql);// 实例化预编译语句
            //pre.setString(1, "");// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            FileOutputStream fos = new FileOutputStream("E:\oracle.txt",false);
            //fos.write(rs.getInt(1));
            PrintStream p = new PrintStream(fos);
            //System.out
            Set<String> fileds =new HashSet<String>();
            int j=0;
            while (result.next()) {
                StringBuilder sb =new StringBuilder();
                sb.append(result.getString("table_name")).append("
");
                sb.append("CREATE　TABLE  IF NOT EXIST ")
                        .append(result.getString("table_name"))
                        .append(" ").append("
");
                String subsql = "select * from user_tab_columns where table_name = '" + result.getString("table_name") + "'";
                PreparedStatement subpre =con.prepareStatement(subsql);
                ResultSet subresult = subpre.executeQuery();
                int  i=0;
                while (subresult.next()) {
                    if(i>0){
                        sb.append(",").append("
");
                    }else{
                        sb.append("(").append("
");
                    }
                    sb.append(" ").append(subresult.getString("COLUMN_NAME"))
                            .append(" ").append(
                            (Ddl.orcleData2HiveData(subresult.getString("DATA_TYPE")))).append("(")
                            .append(subresult.getString("DATA_LENGTH")).append(")");

//                    sb.append(" ").append(subresult.getString("COLUMN_NAME"))
//                            .append(" (").append(
//                            ( subresult.getString("DATA_TYPE")) ).append(")");
//                    fileds.add(subresult.getString("DATA_TYPE"));
                    System.out.println(subresult.getString("COLUMN_NAME") + "(" + subresult.getString("DATA_TYPE") + ")");
                    i++;
                }
                if(i>0){
                    sb.append(")").append("
");
                }
                sb.append(";").append("
").append("
");
                p.print(sb.toString());
            }
            p.close();
            fos.flush();
            System.out.print(fileds);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭！");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }
}

//取得该用户下所有的表
select * from user_tables;
//取得表名为Sysuser的注释信息
select * from user_tab_comments where table_name = 'SYSUSER';
//取得该用户下表名为Sysuser表的结构
select * from user_tab_columns where table_name='SYSUSER';
//取得该用户下表名为sysuser表中字段的注释信息
select * from user_col_comments where table_name = 'SYSUSER';
//取得该用户下所有表的中文名称和英文名称
select t2.table_name,t2.comments from user_tables t1,user_tab_comments t2 where t1.table_name=t2.table_name;
TABLE_NAME COMMENTS
------------------------------ -----------------------------------------------
SYSUSER 用户表
//取得表sysuser中的英文字段名，中文字段名，字段类型，字段长度
select t1.TABLE_NAME,t1.COLUMN_NAME,t2.COMMENTS,t1.DATA_TYPE,t1.DATA_LENGTH from user_tab_columns t1, user_col_comments t2 where t1.TABLE_NAME='SYSUSER' and t1.TABLE_NAME=t2.TABLE_NAME and t1.COLUMN_NAME=t2.COLUMN_NAME;
TABLE_NAME COLUMN_NAME COMMENTS DATA_TYPE DATA_LENGTH
----------- ----------------------------------------------------------------------
SYSUSER SYSUSERID 用户ID VARCHAR2 50
SYSUSER SYSUSERNAME 用户名 VARCHAR2 50
SYSUSER SYSUSERPASSWORD 密码