import java.sql.*;
public class DbQuery3 {
  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=5000&socketTimeout=15000";
    try (Connection c = DriverManager.getConnection(url, "mes_cloud", "s2ENj7KaEySTC7dW")) {
      q(c, "SELECT company_code, company_name FROM js_sys_company ORDER BY company_code");
      q(c, "SELECT office_code, office_name, parent_code FROM js_sys_office WHERE office_code IN ('SD','111','110','11001','11005') OR office_name LIKE '%\u8bb7%' OR parent_code='0' OR parent_code='SD' ORDER BY office_code");
      q(c, "SELECT e.emp_name, e.company_code, u.user_name FROM js_sys_employee e LEFT JOIN js_sys_user u ON u.ref_code=e.emp_code AND u.user_type='employee' AND u.status='0' WHERE e.office_code='11005' AND e.status='0' AND e.company_code='SD' ORDER BY e.emp_name");
      q(c, "SELECT e.emp_name, e.company_code, u.user_name FROM js_sys_employee e LEFT JOIN js_sys_user u ON u.ref_code=e.emp_code AND u.user_type='employee' AND u.status='0' WHERE e.office_code='11005' AND e.status='0' AND e.company_code='110' ORDER BY e.emp_name");
      q(c, "SELECT e.emp_name, e.office_code, e.company_code FROM js_sys_employee e JOIN js_sys_user u ON u.ref_code=e.emp_code WHERE u.user_code='\u5f20\u98ce'");
    }
  }
  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("---");
    }
  }
}