import java.sql.*;
public class DbQuery5 {
  public static void main(String[] args) throws Exception {
    String url = "jdbc:mysql://192.168.9.181:3306/mes_cloud?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&connectTimeout=8000&socketTimeout=15000";
    try (Connection c = DriverManager.getConnection(url, "mes_cloud", "s2ENj7KaEySTC7dW")) {
      q(c, "SELECT COUNT(*) total_110 FROM js_sys_employee WHERE company_code='110'");
      q(c, "SELECT COUNT(*) under_sd FROM js_sys_employee e JOIN js_sys_office o ON o.office_code=e.office_code WHERE e.company_code='110' AND (o.office_code='SD' OR o.parent_code='SD' OR o.parent_codes LIKE '%,SD,%')");
      q(c, "SELECT e.office_code, o.office_name, o.parent_code, COUNT(*) c FROM js_sys_employee e LEFT JOIN js_sys_office o ON o.office_code=e.office_code WHERE e.company_code='110' GROUP BY e.office_code, o.office_name, o.parent_code ORDER BY c DESC");
      q(c, "SELECT e.emp_code, e.emp_name, e.office_code, o.office_name, o.parent_code FROM js_sys_employee e LEFT JOIN js_sys_office o ON o.office_code=e.office_code WHERE e.company_code='110' AND NOT (o.office_code='SD' OR o.parent_code='SD' OR IFNULL(o.parent_codes,'') LIKE '%,SD,%') LIMIT 50");
    }
  }
  static void q(Connection c, String sql) throws Exception {
    System.out.println("SQL> " + sql);
    try (Statement s = c.createStatement(); ResultSet rs = s.executeQuery(sql)) {
      ResultSetMetaData md = rs.getMetaData();
      int n = md.getColumnCount(); int rows=0;
      while (rs.next()) {
        StringBuilder sb=new StringBuilder();
        for (int i=1;i<=n;i++){ if(i>1)sb.append(" | "); sb.append(md.getColumnLabel(i)).append("=").append(rs.getString(i)); }
        System.out.println(sb); rows++;
      }
      System.out.println("rows="+rows); System.out.println("---");
    }
  }
}