Oracle完整复制表结构信息流程与JAVA例子

最新推荐文章于 2023-07-09 14:23:53 发布

Just-Today

最新推荐文章于 2023-07-09 14:23:53 发布

阅读量453

点赞数

分类专栏： oracle java 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_37607613/article/details/120091228

版权

java 同时被 2 个专栏收录

23 篇文章 2 订阅

订阅专栏

oracle

8 篇文章 0 订阅

订阅专栏

ORALCE所有的名称命名最好都是大写，防止出错

一、查看表是否存在

select count(1) COUNT from user_tables t where table_name= upper('表名')

查询得到大于0即存在

二、表属于哪个表空间

SELECT TABLESPACE_NAME FROM user_tables WHERE TABLE_NAME = upper('表名')

若查询为空，则说明表属于默认表空间，则执行下列语句可得到默认表空间

SELECT DEFAULT_TABLESPACE  FROM user_users

三、数据表定义语句

查看当前用户

SELECT username from USER_USERS

获取数据表定义语句

select dbms_metadata.get_ddl('TABLE',upper('表名'),(SELECT username from USER_USERS)) DDL from dual

注：表定义语句包含了主键等约束、分区定义等信息，不用在额外去获取主键等约束

由于获取表定义语句包含表的表空间和模式，转移到其他库，可能表空间和模式不一样，因此可用标识符替换，后面根据实际表空间和模式进行替换

如，后面会附上完整代码

/**
     * 获取数据表定义语句
     * @param runner sql执行器
     * @param tableName 表名
     * @param username 用户账号
     * @param tablespace 表空间
     * @return
     */
    public String getTableDdlSql(SqlRunner runner, String tableName, String username, String tablespace) {
        StringBuilder stringBuilder = new StringBuilder("select dbms_metadata.get_ddl('TABLE',upper('")
                                                .append(tableName)
                                                .append("')) DDL from dual ");
        try {
            CLOB ddl = (CLOB) runner.selectOne(stringBuilder.toString()).get("DDL");
            if(ddl != null){
                try(BufferedReader br = new BufferedReader(ddl.getCharacterStream())){
                    StringBuilder sb = new StringBuilder();
                    String line;
                    while ((line = br.readLine()) != null) {
                        sb.append(line);
                    }
                    //由于获取表定义语句包含表的表空间和模式，转移到其他库，可能表空间和模式不一样，因此用标识符替换，后面根据实际表空间和模式进行替换
                    return sb.toString().replaceFirst("CREATE TABLE \"" + username + "\".", "##USERNAME##")
                            .replaceAll("TABLESPACE \"" + tablespace + "\"", "##TABLESPACE##");
                }
               
            }else{
                //Exception("获取表[" + tableName + "]定义语句失败:定义语句为空");
            }
        }catch (SQLException | IOException e) {
            logger.error("获取表[" + tableName + "]定义语句失败", e);
            //Exception("获取表[" + tableName + "]定义语句失败" + e.getMessage());
        }
    }

四、表和列注释

1、获取表和列注释

--表注释
select comments from user_tab_comments where table_name= upper('表名')
--列注释 
select column_name,comments from user_col_comments where table_name= upper('表名')

2、添加表注释

  COMMENT ON TABLE '表名' IS '注释';

3、添加列注释

COMMENT ON COLUMN 表名.列名 IS '注释';

五、触发器

1、获取属于表的所有触发器名称

select trigger_name from user_triggers where table_name=upper('表名')

2、根据触发器名称查询触发器定义语句

select text from user_source where type='TRIGGER' AND name='触发器名称' ORDER BY line

3、创建触发器

将查询出来的text按照顺序进行拼装并在开头加上"CREATE "

六、复制序列

1、复制序列原因

注：由于复制别的数据库中的表，可能存在要复制的数据库表的触发器使用该库自己的序列，而复制到本地的数据库中可能缺少对应的序列，导致对新增表中的数据进行操作会出现出错，因此复制表时也需将别的数据库用户拥有的序列复制到本地数据库用户中。

查询数据库用户拥有序列

SELECT * FROM user_SEQUENCES

2、序列含义

和mysql中使用auto_increment不同，oracle中使用序列来实现主键自增，使用序列前必须要先创建一个序列

名称	标识符	含义
SEQUENCE_NAME		序列名称
MIN_VALUE	MINVALUE	最小值
MAX_VALUE	MAXVALUE	最大值
INCREMENT_BY	INCREMENT BY	每次增长多少
LAST_NUMBER	START WITH	从几开始
CACHE_SIZE	CACHE	默认20 需要不需要使用缓存，就是一次生成多少个序列的值，如果生成了多个序列值，如果缓存中有数据，就不需要查询缓存了
CYCLE_FLAG	CYCLE	默认no cycle 序列到达最大值之后怎么办，一般去cycle
ORDER_FLAG	ORDER	默认noorder 获取序列的时候是否按顺序给值。如果多用户一起获取序列的值，使用order可以保证序列值的顺序按访问序列的事件排序

3、创建序列

CREATE SEQUENCE TTTT MINVALUE 2 MAXVALUE 9999999999999999999999999999 INCREMENT BY 3  START WITH 5130 cache 200 order cycle

七、Java例子

//转换成大写
tableName = tableName.toUpperCase();
//获取创建数据表语句信息
Map<String, Object> createTableSql = dbServiceSource.getCreateTableSql(tableName, usernameSource);
//创建表
dbServiceTarget.createTable(createTableSql, tableName, usernameTarget);

/**
     * 获取创建数据库表-语句信息
     * @param tableName 表名
     * @param username 用户
     * @return
     */
    public Map<String, Object> getCreateTableSql(String tableName, String username) {
        try (Connection conn = this.jdbcDataSource.getJdbcConnection(this.dsId)) {
            Map<String, Object> result = new HashMap<>();
            final SqlRunner runner = new SqlRunner(conn);

            //获取表空间
            String tablespace = getTableTableSpace(runner, tableName);
            //获取数据库表定义语句
            String ddl = getTableDdlSql(runner, tableName, username, tablespace);
            result.put("ddl", ddl);
            //获取表和列注释
            Map<String, Object> tableComment = getTableComment(runner, tableName);
            result.put("tableComment", tableComment);
            //获取数据库表触发器
            Map<String, String> trigger = getTableTrigger(runner, tableName, username, tablespace);
            result.put("trigger", trigger);
            //获取当前用户序列
            List<Map<String, Object>> sequence = getUserSequence(runner);
            result.put("sequence", sequence);

            return result;
        }catch (SQLException e) {
            logger.error("获取数据库表[" + tableName + "]创建语句失败", e);
            //Exception("获取数据库表[" + tableName + "]创建语句失败" + e.getMessage());
        }
    }

    /**
     * 获取表空间
     * @param runner
     * @return
     */
    public String getTableTableSpace(SqlRunner runner, String tableName) throws SQLException {
        String tableSpace = null;
        StringBuilder stringBuilder = new StringBuilder(" SELECT TABLESPACE_NAME FROM user_tables ")
                                                        .append(" WHERE TABLE_NAME = upper('")
                                                        .append(tableName).append("')");
        List<Map<String, Object>> list = runner.selectAll(stringBuilder.toString());
        if (list != null && list.size() > 0) {
            if(list.get(0).get("TABLESPACE_NAME") != null){
                tableSpace = list.get(0).get("TABLESPACE_NAME").toString();
            }
        }
        if(StringUtils.isNullOrEmpty(tableSpace)){
            //如果tableSpace为空，则说明为默认表空间，则执行下列语句
            list = runner.selectAll(" SELECT DEFAULT_TABLESPACE  FROM user_users ");
            if (list != null && list.size() > 0) {
                tableSpace = list.get(0).get("DEFAULT_TABLESPACE").toString();
            }
        }
        if(StringUtils.isNullOrEmpty(tableSpace)){
            //Exception("获取数据库表空间失败:表空间为空");
        }
        return tableSpace;
    }

    /**
     * 获取数据表定义语句
     * @param runner sql执行器
     * @param tableName 表名
     * @param username 用户账号
     * @param tablespace 表空间
     * @return
     */
    public String getTableDdlSql(SqlRunner runner, String tableName, String username, String tablespace) {
        StringBuilder stringBuilder = new StringBuilder("select dbms_metadata.get_ddl('TABLE',upper('")
                .append(tableName)
                .append("'),(SELECT username from USER_USERS)) DDL from dual ");
        try {
            CLOB ddl = (CLOB) runner.selectOne(stringBuilder.toString()).get("DDL");
            if(ddl != null){
                try(BufferedReader br = new BufferedReader(ddl.getCharacterStream())){
                    StringBuilder sb = new StringBuilder();
                    String line;
                    while ((line = br.readLine()) != null) {
                        sb.append(line);
                    }
                    //由于获取表定义语句包含表的表空间和模式，转移到其他库，可能表空间和模式不一样，因此用标识符替换，后面根据实际表空间和模式进行替换
                    return sb.toString().replaceFirst("CREATE TABLE \"" + username + "\".", "##USERNAME##")
                            .replaceAll("TABLESPACE \"" + tablespace + "\"", "##TABLESPACE##");
                }
                /*try(BufferedInputStream br = new BufferedInputStream(ddl.getAsciiStream());
                    ByteArrayOutputStream bos = new ByteArrayOutputStream()){
                    byte[] buffer=new byte[2048];
                    int length=-1;

                    while( (length = br.read(buffer)) != -1) {
                        bos.write(buffer,0,length);
                    }
                    return bos.toString().replaceFirst("CREATE TABLE \"" + username + "\".", "#USERNAME#")
                            .replaceAll("TABLESPACE \"" + tablespace + "\"", "#TABLESPACE#");
                }*/
            }else{
                //RuntimeException("获取表[" + tableName + "]定义语句失败:定义语句为空");
            }
        }catch (SQLException | IOException e) {
            logger.error("获取表[" + tableName + "]定义语句失败", e);
            //RuntimeException("获取表[" + tableName + "]定义语句失败" + e.getMessage());
        }
    }

    /**
     * 获取数据表触发器
     * @param runner
     * @param tableName
     * @param username
     * @return
     */
    public Map<String, String> getTableTrigger(SqlRunner runner, String tableName, String username, String tablespace) throws SQLException {
        StringBuilder stringBuilder = new StringBuilder("select trigger_name from user_triggers where table_name=upper('")
                .append(tableName)
                .append("')");
        List<Map<String, Object>> list = runner.selectAll(stringBuilder.toString());
        if(list == null || list.size() <= 0){
            return null;
        }
        Map<String, String> result = new HashMap<>();
        for(Map<String, Object> map : list){
            Object triggerName = map.get("TRIGGER_NAME");
            if(triggerName != null){
                stringBuilder = new StringBuilder("select text from user_source where type='TRIGGER' AND name='")
                        .append(triggerName)
                        .append("' ORDER BY line");
                List<Map<String, Object>> textList = runner.selectAll(stringBuilder.toString());
                stringBuilder = new StringBuilder();
                for(Map<String, Object> text : textList){
                    stringBuilder.append(text.get("TEXT").toString().replaceAll("\"" + username + "\".", "##USERNAME##"));
                }
                result.put(triggerName.toString(), stringBuilder.toString());
            }
        }
        return result;
    }

    /**
     * 获取表和列注释
     * @param runner
     * @param tableName
     * @return
     * @throws SQLException
     */
    public Map<String, Object> getTableComment(SqlRunner runner, String tableName) throws SQLException {
        Map<String, Object> result = new HashMap<>();
        StringBuilder stringBuilder = new StringBuilder("select comments from user_tab_comments where table_name=upper('")
                .append(tableName)
                .append("')");
        List<Map<String, Object>> list = runner.selectAll(stringBuilder.toString());
        if(list != null && list.size() > 0){
            if(list.get(0).get("COMMENTS") != null){
                result.put(tableName, new String(list.get(0).get("COMMENTS").toString().getBytes(Charset.forName(EncodeUtils.ISO)), Charset.forName(EncodeUtils.GBK)));
            }
        }
        stringBuilder = new StringBuilder("select column_name,comments from user_col_comments where table_name=upper('")
                .append(tableName)
                .append("')");
        list = runner.selectAll(stringBuilder.toString());
        if(list != null && list.size() > 0){
            for(int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                if(map.get("COMMENTS") != null){
                    if(!StringUtils.isNullOrEmpty(map.get("COMMENTS").toString())){
                        map.put("COMMENTS", new String(map.get("COMMENTS").toString().getBytes(Charset.forName(EncodeUtils.ISO)), Charset.forName(EncodeUtils.GBK)));
                    }
                }
            }
            result.put("columnList", list);
        }
        return result;
    }

    /**
     * 获取当前用户序列
     * @return
     */
    public List<Map<String, Object>> getUserSequence(SqlRunner runner) {
        try {
            List<Map<String, Object>> list = runner.selectAll("SELECT * FROM user_SEQUENCES");
            return list;
        } catch (SQLException e) {
            logger.error("获取数据库序列出错", e);
            //Exception("获取数据库序列出错" + e.getMessage());
        }
    }

    /**
     * 判断表是否存在
     * @return
     */
    public boolean tableIsExists(SqlRunner runner, String tableName) {
        try {
            Map<String, Object> map = runner.selectOne("select count(1) COUNT from user_tables t where table_name= upper('" + tableName + "')");
            return Integer.parseInt(map.get("COUNT").toString()) <= 0 ? false : true;
        } catch (SQLException e) {
            logger.error("判断表[" + tableName + "]是否存在出错", e);
            //Exception("判断表[" + tableName + "]是否存在出错" + e.getMessage());
        }
    }

/**
     * 创建表
     * @param ddl 表创建语句
     * @return
     */
    public void createTable(Map<String, Object> createTableSql, String tableName, String username) {
        try (Connection conn = this.jdbcDataSource.getJdbcConnection(this.dsId)) {
            final SqlRunner runner = new SqlRunner(conn);

            //判断表是否存在
            if(tableIsExists(runner, tableName)){
                throw new DutyRuntimeException("创建表[" + tableName + "]出错:表已存在");
            }else{
                String tablespace = getTableTableSpace(runner, tableName);
                //数据库表定义语句
                String ddl = createTableSql.get("ddl").toString().replaceFirst("##USERNAME##", "CREATE TABLE \"" + username + "\".")
                        .replaceAll("##TABLESPACE##", "TABLESPACE \"" + tablespace + "\"");
                try{
                    runner.run(ddl);
                }catch (Exception e){
                    logger.error("创建表[" + tableName + "]失败", e);
                    //Exception("创建表[" + tableName + "]失败" + e.getMessage());
                }
                //当前用户缺少要复制表数据库用户序列，需要添加，防止触发器运用到缺少序列导致出错
                List<Map<String, Object>> addSequence = new ArrayList<>();
                try{
                    //表和列注释
                    Map<String, Object> tableComment = (Map<String, Object>) createTableSql.get("tableComment");
                    if(tableComment != null && tableComment.size() > 0){
                        //表注释
                        Object comment = tableComment.get(tableName);
                        if(comment != null){
                            runner.run("COMMENT ON TABLE " + tableName + " IS '" + comment + "'");
                        }
                        //列注释
                        List<Map<String, Object>> columnList = (List<Map<String, Object>>) tableComment.get("columnList");
                        if(columnList != null){
                            for(int i = 0; i < columnList.size(); i++){
                                Map<String, Object> map = columnList.get(i);
                                if(map.get("COMMENTS") != null){
                                    runner.run("COMMENT ON COLUMN " + tableName + "." + map.get("COLUMN_NAME") + " IS '" + map.get("COMMENTS") + "'");
                                }
                            }
                        }
                    }
                    //数据库表触发器
                    Map<String, String> trigger = (Map<String, String>) createTableSql.get("trigger");
                    if(trigger != null && trigger.size() > 0){
                        try{
                            for(String t : trigger.values()){
                                runner.run("CREATE " + t.replaceAll("##USERNAME##", "\"" + username + "\"."));
                            }
                        }catch (Exception e){
                            logger.error("创建表[" + tableName + "]触发器失败", e);
                            //Exception("创建表[" + tableName + "]触发器失败" + e.getMessage());
                        }
                    }
                    List<Map<String, Object>> sequenceSource = (List<Map<String, Object>>) createTableSql.get("sequence");

                    if(sequenceSource != null && sequenceSource.size() > 0) {
                        //获取当前用户序列
                        List<Map<String, Object>> sequence = getUserSequence(runner);
                        for (int i = 0; i < sequenceSource.size(); i++) {
                            String sourceSequenceName = sequenceSource.get(i).get("SEQUENCE_NAME").toString();
                            int j = 0;
                            for (; j < sequence.size(); j++) {
                                String sequenceName = sequence.get(j).get("SEQUENCE_NAME").toString();
                                if (sourceSequenceName.equals(sequenceName)) {
                                    break;
                                }
                            }
                            if (j >= sequence.size()) {
                                //当前用户缺少要复制表数据库用户序列
                                addSequence.add(sequenceSource.get(i));
                            }
                        }
                        StringBuilder stringBuilder;
                        try{
                            //当前用户缺少要复制表数据库用户序列，需要添加，防止触发器运用到缺少序列导致出错
                            for (int i = 0; i < addSequence.size(); i++) {
                                Map<String, Object> map = addSequence.get(i);
                                stringBuilder = new StringBuilder("CREATE SEQUENCE ").append(map.get("SEQUENCE_NAME"))
                                        .append(" MINVALUE ").append(map.get("MIN_VALUE"))
                                        .append(" MAXVALUE ").append(map.get("MAX_VALUE"))
                                        .append(" INCREMENT BY ").append(map.get("INCREMENT_BY"))
                                        .append(" START WITH ").append(map.get("LAST_NUMBER"));
                                if(!"0".equals(map.get("CACHE_SIZE").toString())){
                                    stringBuilder.append(" CACHE ").append(map.get("CACHE_SIZE"));
                                }
                                if("Y".equals(map.get("CYCLE_FLAG").toString())){
                                    stringBuilder.append(" CYCLE ");
                                }
                                if("Y".equals(map.get("ORDER_FLAG").toString())){
                                    stringBuilder.append(" ORDER ");
                                }
                                runner.run(stringBuilder.toString());
                            }
                        }catch (Exception e){
                            //Exception("创建序列失败" + e.getMessage());
                        }
                    }
                }catch (Exception e){
                    //删除已经创建的表
                    try{
                        runner.run("drop table " + tableName);
                    }catch (Exception ee){

                    }
                    //删除已经创建的序列
                    for (int i = 0; i < addSequence.size(); i++) {
                        try{
                            runner.run("drop sequence " + addSequence.get(i).get("SEQUENCE_NAME"));
                        }catch (Exception ee){

                        }
                    }
                    throw e;
                }
            }
        }catch (SQLException e) {
            logger.error("创建表[" + tableName + "]出错", e);
            //Exception("创建表[" + tableName + "]出错" + e.getMessage());
        }
    }

参考地址：https://blog.csdn.net/weixin_33882452/article/details/89830369

https://www.php.cn/oracle/451035.html

https://blog.csdn.net/iteye_3619/article/details/82202264

https://www.cnblogs.com/duguangming/p/10815935.html