java通过代码给所有表的字段批量加索引

package test;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.zxkj.utils.jdbc.DaoUtils;

public class DbIndexTest {
	public static void main(String[] args) throws SQLException{
		List<Map<String, Object>> tables = DaoUtils.queryMapList("SELECT table_name FROM information_schema.tables WHERE table_schema = 'traindb'");
		System.out.println(tables);
		tables.stream().forEach(e->{
			String tableName =e.get("table_name")+"" ;
			System.out.println(tableName);
			try {
				List<Map<String, Object>> columns = DaoUtils.queryMapList(
					   "select "
					+" a.table_schema, "
					+" a.table_name, "
					+" b.table_comment table_comment, "
					+" a.column_name column_name, "
					+" a.column_comment column_comment, "
					+" a.column_type column_type, "
					+" a.column_key column_key from  information_schema.columns a "
					+" join information_schema.TABLES b on a.table_name = b.TABLE_NAME "
					+" where a.table_schema = 'traindb' "
					+" and a.table_name = '"+tableName+"' ");
				
				System.out.println(columns);
				columns.stream().forEach(c->{
					String columnName =c.get("column_name")+"" ;
					System.out.println(columnName);
					
					if("id".equals(columnName)){
						try {
							DaoUtils.updateTable("ALTER TABLE "+tableName+" ADD UNIQUE ( "+columnName+" )");
						} catch (Exception e1) {
							// TODO Auto-generated catch block
							//e1.printStackTrace();
							System.err.println("添加主键索引失败");
						}
					}else{

						try {
							DaoUtils.updateTable("ALTER TABLE "+tableName+" ADD INDEX "+columnName+"( "+columnName+" )");
						} catch (Exception e1) {
							// TODO Auto-generated catch block
							//e1.printStackTrace();
							System.err.println("添加主键索引失败");
						}
					}
				});
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		});
	}

}
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