package com.cn.common.utils;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import oracle.jdbc.OracleConnection;

/**

 * 这个类主要用于手动的辅助写与数据库交互的XML文件，打印一些固定格式的信息，用于写增删改查的数据库SQL语句

 * @author Administrator

 *

 */

public class MetaDataTest {

    /**

     * 主方法，获得表的结构信息

     * @param args

     */

    public static void main(String[] args)

    {

        try

        {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            String url = "THE URL";

            String username="THE USERNAME";

            String password="THE PASSWORD";

            try

            {

                Properties props =new Properties();

                props.put("remarksReporting","true");

                /*

                 * 获得数据库连接，通过URL/USERNAME/PASSWORD，注意，要确定数据的服务是开启的

                 */

                Connection conn = DriverManager.getConnection(url,username,password);

                /*

                 * 设置连接属性,使得可获取到列的REMARK(备注)

                 */

               ((OracleConnection)conn).setRemarksReporting(true);

                DatabaseMetaData dmd = conn.getMetaData();

System.out.println("=============================================The database infor======================================================");

                // 数据库名:

                System.out.println("数据库名称:" + dmd.getDatabaseProductName());

                // 数据库版本号:

                System.out.println("数据库版本号  :"+ dmd.getDatabaseProductVersion());

                // 驱动程序

                System.out.println("数据库驱动程序 :" + dmd.getDriverName());

                // 驱动版本

                System.out.println("数据库驱动版本:" + dmd.getDriverVersion());

System.out.println("=============================================The users infor=========================================================");

                //打印当前数据库的所有用户名称

                getAllSchemas(dmd);

System.out.println("=============================================The tables infor========================================================");

                //打印当前数据库用户下的所有表信息

                getAllTableList(dmd,"THE SCHEMA");

System.out.println("=============================================The table infor=========================================================");

                //打印当前用户当前表结构信息

                getTableColumns(dmd,"THE SCHEMA","THE TABLE");

            }

            catch (SQLException e)

            {

                e.printStackTrace();

            }

        }

        catch (ClassNotFoundException e)

        {

            e.printStackTrace();

        }

    }

     /**

     * 获得表或视图中的列信息

     */

    public static void getTableColumns(DatabaseMetaData dbMetaData,String schemaName, String tableName)

    {

        try

        {

           ResultSet rs = dbMetaData.getColumns(null, schemaName, tableName, "%");

            while (rs.next())

            {

                    String tableCat = rs.getString("TABLE_CAT");//表目录（可能为空）

                    String tableSchemaName = rs.getString("TABLE_SCHEM");//表的架构（可能为空）

                    String tableName_ = rs.getString("TABLE_NAME");//表名

                    String columnName = rs.getString("COLUMN_NAME");//列名

                    int dataType = rs.getInt("DATA_TYPE"); //对应的java.sql.Types类型

                    String dataTypeName = rs.getString("TYPE_NAME");//java.sql.Types类型名称

                    int columnSize = rs.getInt("COLUMN_SIZE");//列大小

                    int decimalDigits = rs.getInt("DECIMAL_DIGITS");//小数位数

                    int numPrecRadix = rs.getInt("NUM_PREC_RADIX");//基数（通常是10进制的）

                    int nullAble = rs.getInt("NULLABLE");//是否允许为null

                    String remarks = rs.getString("REMARKS");//列描述

                    String columnDef = rs.getString("COLUMN_DEF");//默认

                    int sqlDataType = rs.getInt("SQL_DATA_TYPE");//sql数据类型

                    int sqlDatetimeSub = rs.getInt("SQL_DATETIME_SUB");   //SQL日期时间

                    int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");   //char类型的列中的字节长度

                    int ordinalPosition = rs.getInt("ORDINAL_POSITION");  //表中列的索引（从1开始）

                    String propName = colNameToPropName(columnName);

System.out.println("=============================================The bean property infor=================================================");

                    System.out.println("private String " + propName+";");

System.out.println("=============================================The column map infor====================================================");

                    System.out.println("<result column=\""+columnName+"\" property=\""+propName+"\" jdbcType=\"NVARCHAR\" />");

System.out.println("=============================================The input property infor==================================================");

                    System.out.println("#"+propName+"#,");

System.out.println("=============================================The map property  infor===============================================");

                    System.out.println(columnName+"=#"+propName+"#,");

System.out.println("=============================================The bean property remarks infor=========================================");

                    System.out.println("<tbl:th width=\"80\" delim=\"true\" field=\""+propName+"\">"+remarks+"</tbl:th>");

System.out.println("=============================================The property map infor==================================================");

                    System.out.println("<tbl:td delim=\"true\">${element."+propName+"}</tbl:td>");

                    /**

                     * ISO规则用来确定某一列的为空性

                     * YES--如果该参数可以包括空值

                     * NO--如果参数不能包含空值

                     * 空字符串---如果参数为空性是未知的

                     */

                    String isNullAble = rs.getString("IS_NULLABLE");  

                    /**

                     * 指示此列是否是自动增长的

                     * 1--如果该列是自动的

                     * 2--如果不是自动递增的

                     * 空字符串--如果不能确定它是否是自动增长的

                     * 列是自动递增的参数是未知的

                     */

//                    String isAutoincrement = rs.getString("IS_AUTOINCREMENT");

System.out.println("=============================================The column map infor=====================================================");

                    System.out.println(tableCat + "-" + tableSchemaName + "-" + tableName_ + "-" + columnName + "-"

                            + dataType + "-" + dataTypeName + "-" + columnSize + "-" + decimalDigits + "-" + numPrecRadix

                            + "-" + nullAble + "-" + remarks + "-" + columnDef + "-" + sqlDataType + "-" + sqlDatetimeSub

                            + charOctetLength + "-" + ordinalPosition + "-" + isNullAble + "-" +"");

                }

            } catch (SQLException e){

                e.printStackTrace();

            }

    }  

     /**

      * 获得数据库中所有用户名称

      * @param dbMetaData

      */

    public static void getAllSchemas(DatabaseMetaData dbMetaData)

    {

        try

        {

            ResultSet rs = dbMetaData.getSchemas();

            while (rs.next())

            {

                String tableSchem = rs.getString("TABLE_SCHEM");

                System.out.println(tableSchem);

            }

        }

        catch (SQLException e)

        {

            e.printStackTrace();

        }

    }     

    /**

     * 获得指定用户下面的表信息数据

     */

    public static void getAllTableList(DatabaseMetaData dbMetaData,String schemaName)

    {

        try

        {

            /*

             * table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

             */

            String[] types = { "TABLE" };

            ResultSet rs = dbMetaData.getTables(null, schemaName, "%", types);

            while (rs.next())

            {

                String tableName = rs.getString("TABLE_NAME");  //表名称

                String tableType = rs.getString("TABLE_TYPE");  //表类型

                String remarks = rs.getString("REMARKS");       //表备注

                System.out.println(tableName + "-" + tableType + "-" + remarks);

            }

        }

        catch (SQLException e)

        {

            e.printStackTrace();

        }

    }

    /**

     * 将数据库表中的列名转换成JavaBean的属性名称，转换规则是采用驼峰式的命名规则

     * @param columnName

     * @return

     */

    private static String colNameToPropName(String columnName)

    {

        String result="";

        String name = columnName.toLowerCase();

        String[] data = name.split("_");

        for(int i=0;i<data.length;i++)

        {

            if(i==0)

            {

                result  = data[i];

            }

            else

            {

                result  += data[i].replaceFirst(data[i].substring(0, 1), data[i].substring(0, 1).toUpperCase());

            }

        }

        return result;

    }

}