ylrz_saas_pharmacy/java/scripts/AuditAllTenantUrls.java

import java.sql.*;
public class AuditAllTenantUrls {
  public static void main(String[] a) throws Exception {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection m = DriverManager.getConnection("jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/ylrz_saas?allowMultiQueries=true","root","Ylrz_1q2w3e4r5t6y");
    Statement st = m.createStatement();
    ResultSet rs = st.executeQuery("SELECT id, db_name, db_url, db_account, db_pwd, status FROM tenant_info ORDER BY id");
    while (rs.next()) {
      long id = rs.getLong(1); String dbName=rs.getString(2); String url=rs.getString(3);
      String user=rs.getString(4); String pwd=rs.getString(5); int status=rs.getInt(6);
      String host="?"; String db="?"; boolean has=false; String err=null;
      try {
        String body = url.substring(13); int slash=body.indexOf('/'); String hp=slash>=0?body.substring(0,slash):body;
        String rest=slash>=0?body.substring(slash+1):dbName; int q=rest.indexOf('?'); db=q>=0?rest.substring(0,q):rest;
        host=hp; Connection c = DriverManager.getConnection(url,user,pwd);
        ResultSet r2 = c.createStatement().executeQuery("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='company_workflow_lobster' AND COLUMN_NAME='visible_company_ids'");
        r2.next(); has=r2.getInt(1)>0; c.close();
      } catch (Exception e) { err=e.getMessage(); }
      System.out.println("tenant="+id+" status="+status+" urlHost="+host+" db="+db+" has="+has+(err!=null?" ERR="+err:""));
    }
    m.close();
  }
}