import java.sql.*;

public class DbQuery {
	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";
		String roleName = "\u8bbe\u5907\u7ef4\u4fee";
		String userName = "\u5f20\u51e4";
		String officeName = "\u6280\u672f\u8d28\u91cf";
		try (Connection c = DriverManager.getConnection(url, "mes_cloud", "s2ENj7KaEySTC7dW")) {
			run(c, "SELECT role_code, role_name, data_scope FROM js_sys_role WHERE role_code='dy0017' OR role_name LIKE '%" + roleName + "%'");
			run(c, "SELECT ctrl_type, ctrl_permi, ctrl_data FROM js_sys_role_data_scope WHERE role_code='dy0017' ORDER BY ctrl_type, ctrl_data");
			run(c, "SELECT u.user_code, u.login_code, u.user_name FROM js_sys_user u WHERE u.user_name LIKE '%" + userName + "%' LIMIT 10");
			run(c, "SELECT ur.user_code, u.user_name, ur.role_code FROM js_sys_user_role ur JOIN js_sys_user u ON u.user_code=ur.user_code WHERE ur.role_code='dy0017'");
			run(c, "SELECT o.office_code, o.office_name FROM js_sys_office o WHERE o.office_name LIKE '%" + officeName + "%'");
			run(c, "SELECT e.company_code, COUNT(*) c FROM js_sys_employee e WHERE e.office_code IN (SELECT office_code FROM js_sys_office WHERE office_name LIKE '%" + officeName + "%') AND e.status='0' GROUP BY e.company_code");
			run(c, "SELECT COUNT(*) cnt FROM js_sys_user u JOIN js_sys_employee e ON e.emp_code=u.ref_code WHERE u.user_type='employee' AND u.status='0' AND e.office_code IN (SELECT office_code FROM js_sys_office WHERE office_name LIKE '%" + officeName + "%')");
			run(c, "SELECT u.user_name, e.office_code, IFNULL(e.company_code,'') company_code FROM js_sys_user u JOIN js_sys_employee e ON e.emp_code=u.ref_code WHERE u.user_type='employee' AND u.status='0' AND e.office_code IN (SELECT office_code FROM js_sys_office WHERE office_name LIKE '%" + officeName + "%') AND (e.company_code IS NULL OR e.company_code='') ORDER BY u.user_name");
		}
	}

	static void run(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("---");
		}
	}
}