mybatis统计crud行数

发表于 分类于 阅读次数： 阅读次数： Changyan： 本文字数： 9.1k 阅读时长 ≈ 8 分钟 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173


import com.google.common.base.Splitter;
import com.google.common.base.Supplier;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ObjectUtils;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.NumberFormat;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.LongAdder;
import java.util.function.*;
import java.util.stream.IntStream;
import java.util.stream.Stream;

import static com.sun.xml.internal.xsom.impl.UName.comparator;
import static java.util.stream.Collectors.*;

@Slf4j
public class CheckTableRows {
    private static BiFunction<String,Integer,CheckTable> checkTable = CheckTable::new;
    //确认已经移植成功的表,mysql的条目数
    private final static List<CheckTable> markDoneTables = Arrays.asList(
            checkTable.apply("xxx_FD_MESSAGE_T",5951)
            ,checkTable.apply("xxx_LOOKUP_CLASSIFY_T",2631)
            ,checkTable.apply("xxx_PERSONALIZED_SETTING_T",623)
    );
    //临时表，不涉及
    private final static List<String> tempTables =Arrays.asList("temp_test","temp_test1");

    @org.junit.Test
    public void Test1() throws Exception {
        Path pathOracle = Paths.get("C:\\Users\\git\\SaveTheWorld\\soup\\src\\test\\java\\com\\cbg\\tools\\demo\\sql\\move", "all_tables_oracle_new.txt");
        Path pathMysql = Paths.get("C:\\Users\\git\\SaveTheWorld\\soup\\src\\test\\java\\com\\cbg\\tools\\demo\\sql\\move", "all_tables_mysql.txt");
        Stream<String> linesOracle = Files.lines(pathOracle);
        Stream<String> linesMysql = Files.lines(pathMysql);
        //源数据
        List<CheckTable> collectOracle = getCheckTables(linesOracle);
        List<CheckTable> collectMysql = getCheckTables(linesMysql);

        collectOracle.removeIf(e->{
            String tableName = e.getTableName();
            return markDoneTables.stream().filter(m->{
                return (m.getTableName()).equalsIgnoreCase(tableName);
            }).findAny().isPresent()||tempTables.contains(tableName);
        });
        collectOracle.addAll(markDoneTables);
        Map<Boolean, List<CheckTable>> collect1 = collectOracle.parallelStream().collect(partitioningBy(collectMysql::contains));
        List<CheckTable> collectDone = collect1.get(true);
        List<CheckTable> collectNotDone = collect1.get(false);
        System.out.println("已完成："+collectDone);
        System.out.println("总共："+collectOracle.size()+"，已完成："+collectDone.size()+"条"+"，未完成："+collectNotDone.size()+"条");
        NumberFormat instance = NumberFormat.getInstance();
        instance.setMaximumFractionDigits(2);
        System.out.println("完成进度："+instance.format((float)collectDone.size()/(float)collectOracle.size()*100)+"%");
        System.out.println("未完成："+collectNotDone);
        List<String> collectNotDone_noTable = collectNotDone.parallelStream().map(CheckTable::getTableName).filter(e -> {
            return !collectMysql.stream().map(CheckTable::getTableName).collect(toList()).contains(e);
        }).collect(toList());
        System.out.println("未完成（无表）："+collectNotDone_noTable);
        System.out.println("未完成（无表）："+collectNotDone_noTable.size()+"条");
        List<CheckTable> collectNotDone_hasTable_WrongRows = collectNotDone.parallelStream().filter(e -> {
            return !collectNotDone_noTable.contains(e.getTableName());
        }).collect(toList());
        List<List<CheckTable>> collect = collectNotDone_hasTable_WrongRows.parallelStream().map(e -> {
            List<CheckTable> objects = Lists.newArrayList();
            CheckTable checkTable = collectMysql.stream().filter(m -> m.getTableName().equalsIgnoreCase(e.getTableName())).findFirst().get();
            objects.add(e);
            objects.add(checkTable);
            return objects;
        }).collect(toList());
        System.out.println("未完成(oracle)（表相同，数据量不一致）："+collectNotDone_hasTable_WrongRows);
        System.out.println("未完成(oracle)（表相同，数据量不一致）："+collectNotDone_hasTable_WrongRows.size()+"条");
        System.out.println("未完成(oracle,mysql)（表相同，数据量不一致）："+collect);
        System.out.println("未完成(oracle,mysql)（表相同，数据量不一致）："+collect.size()+"条");

    }

    public List<CheckTable> getCheckTables(Stream<String> linesOracle) {
        return linesOracle.parallel().map(org.apache.logging.log4j.util.Strings::trimToNull).filter(
                ((Predicate<String>)ObjectUtils::isEmpty).negate()
            ).map(e -> {
            CheckTable checkTable = new CheckTable();
            Splitter.on("&").trimResults()
                        .withKeyValueSeparator(";").split(e)
                        .forEach((k, v) -> {
                            checkTable.setTableName(k.toLowerCase());
                            checkTable.setRows(Integer.parseInt(v));
                        });
                return checkTable;
            }).collect(toList());
    }
    @org.junit.Test
    public void getOracleInsertData() throws Exception {
        Path pathOracle = Paths.get("D:\\sqlExport\\");
        Stream<Path> list = Files.list(pathOracle);
        CopyOnWriteArrayList co = new CopyOnWriteArrayList();
        List<CompletableFuture> objects = Lists.newArrayList();
        list.forEach(e->{
            CompletableFuture<String> voidCompletableFuture = CompletableFuture.supplyAsync(() -> {
                try {
                    Stream<String> linesOracle = Files.lines(e);
                    String name = e.getFileName().toString();
                    long count = linesOracle.filter(lo -> lo.contains("INSERT INTO")).count();
                    return name.replace(".sql","") + ";" + count;
                } catch (Exception e1) {
                    String name = e.getFileName().toString();
//                    System.out.println("错误："+name);
//                    e1.printStackTrace();
                    return name.replace(".sql","") + ";" + "Exception";
                }
            }, Executors.newFixedThreadPool(100));
            voidCompletableFuture.thenAccept(co::add);
            objects.add(voidCompletableFuture);
        });
        CompletableFuture.allOf((CompletableFuture[])objects.stream().toArray(size->new CompletableFuture[size])).join();
        co.removeIf(ObjectUtils::isEmpty);
        co.stream().sorted().forEach(System.out::println);

    }

    @org.junit.Test
    public void Test2() throws Exception {
        CheckTable checkTable1 = new CheckTable();
        CheckTable checkTable2 = new CheckTable();
        checkTable1.setTableName("gg");
        checkTable2.setTableName("gg");
        checkTable1.setRows(0);
        checkTable2.setRows(0);
        System.out.println(checkTable1 == checkTable2);
        String s = org.apache.logging.log4j.util.Strings.trimToNull("    ");
        System.out.println(ObjectUtils.isEmpty(s));
        HashMap<Object, Object> objectObjectHashMap = Maps.newHashMap();
    }
    @org.junit.Test
    public void TestRepeat() throws Exception {
    }
}

@Data
@AllArgsConstructor
@NoArgsConstructor
class CheckTable {
    private String tableName;

    private Integer rows;

    public void setTableName(String tableName) {
        this.tableName = org.apache.logging.log4j.util.Strings.trimToNull(tableName);
    }

    @Override
    public String toString() {
        return "{" +
                "tableName:'" + tableName + '\'' +
                ", rows:" + rows +
                '}';
    }

}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
oracle过程：
CREATE OR REPLACE PROCEDURE "FUT"."GETALLTABLEROWS" is     --仅供参考
  allTables varchar(32);
	count1 number(11);
	selectCountSql VARCHAR2(500):='';
	v_date VARCHAR2(50);


  cursor cur is --游标  给查询赋值
  select a.table_name allTables from user_tables a ORDER BY table_name  ;
BEGIN
execute immediate 'drop table temp_test';
  execute immediate 'CREATE TABLE temp_test (
  allTables VARCHAR(200)
)';
execute immediate 'drop table temp_test1';
  execute immediate 'CREATE TABLE temp_test1 (
  rows1 number(11)
)';

    for x in cur loop--游标取值 循环
      allTables :=x.allTables;
			insert into temp_test (allTables) values(allTables) ;
			selectCountSql := 'insert into temp_test1 select count(1) from '||allTables||'';
			-- dbms_output.put_line(selectCountSql);
			execute immediate selectCountSql;
			
     
    --自己的语句
		-- select i_jdid,i_ryid;
      
    end loop;

--这可以给记录表插数据
end ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
mysql:
CREATE DEFINER=`root`@`%` PROCEDURE `getAllTableRows`(OUT `test` text)
BEGIN
DECLARE allTables VARCHAR(200);
DECLARE tui VARCHAR(200);
	DECLARE count int;
	DECLARE i int default 0;
	declare report1 cursor for select a.allTables from temp_test a;
	
declare continue handler for not found set i =1;



	drop table if EXISTS temp_test;
	CREATE TABLE `temp_test` (
  `allTables` VARCHAR(200),
  `rows1` int(0)
);
	insert into temp_test(allTables) 
select table_name allTables from information_schema.`TABLES` where table_schema="fut_back_test" and table_type ="BASE TABLE" ORDER BY table_name;



open report1;

fetch report1 into tui;


while i<>1 do
	set @sql=concat('select count(*) into @count from ', tui);
	prepare stmt from @sql;
	execute stmt;
	DEALLOCATE PREPARE stmt;
	set count =@count;
	
	insert into  temp_test(allTables,rows1) VALUES(tui,count) ;
fetch report1 into tui;

end while;
close report1;
	
delete from temp_test where rows1 is null;

END