根据原有表名添加主键工具

原创
04/25 16:24
阅读数 38

import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.*;

public class ClientTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        String url = "jdbc:mysql://132.175.58.199:3306/test?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=exception&useCompression=true&useSSL=false";
        String username = "root";
        String pwd = "1234";
        String driverClass = "com.mysql.jdbc.Driver";

        Class.forName(driverClass);

        Connection connection = DriverManager.getConnection(url, username, pwd);
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet itr = metaData.getTables(null, "%","%",new String[]{"TABLE"});

        String addPK ="alter table %s add constraint pk_index primary key (%s);";

        Map<String,List<String>> map = new HashMap<>();
        while(itr.next()){
            String tableName = itr.getString("TABLE_NAME");
            ResultSet primaryKeyResultSet = metaData.getPrimaryKeys(null,null,tableName);
            while(primaryKeyResultSet.next()){
                String primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");
                List<String> ids = map.get(tableName);
                if(CollectionUtils.isEmpty(ids)){
                    ids = new ArrayList<>();
                }
                ids.add(primaryKeyColumnName);
                map.put(tableName,ids);
            }
        }


        for (Map.Entry<String, List<String>> entry : map.entrySet()) {
            List<String> idList = entry.getValue();
            String idStr = idList.get(0);
            if(idList.size() > 1){
                StringBuilder ids = new StringBuilder();
                Iterator<String> iterator = idList.iterator();
                for (;;) {
                    String id = iterator.next();
                    ids.append(id);
                    if(iterator.hasNext()){
                        ids.append(",");
                    }else {
                        idStr = ids.toString();
                        break;
                    }
                }
            }
            System.out.println(String.format(addPK,entry.getKey(),idStr));
        }
    }

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部