Jsoup爬取HTTPS页面数据资源，并导入数据库（Java）

一、实现思路

示例页面：
2020年12月中华人民共和国县以上行政区划代码

• 忽略https请求的SSL证书
• 通过Jsoup获取页面标签
• 遍历行标签，分别获取每个行标签的第二个和第三个列标签
• 将获取到的行政代码和单位名称分别插入sql语句占位符
• 执行sql语句，插入数据

二、代码实现

• 新建数据库，存储爬虫数据

CREATE TABLE `test_table`
(
    `BM` varchar(16) NOT NULL COMMENT '字典代码',
    `MC` varchar(32) DEFAULT NULL COMMENT '名称',
    `JB` char(1)     DEFAULT NULL COMMENT '级别',
    PRIMARY KEY (`BM`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3
  ROW_FORMAT = DYNAMIC COMMENT ='省市区字典表';

• 创建JDBC连接工具类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author Odinpeng
 * @since 2023/12/07
 **/
public class JdbcUtil {

    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
    private static String username = "username";
    private static String password = "password";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}

• 创建HTTPS请求忽略SSL证书工具类

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import java.security.cert.X509Certificate;

/**
 * @author Odinpeng
 * @since 2023/12/07
 **/
public class SslUtils {

    /**
     * 忽略HTTPS请求的SSL证书
     *
     * @throws Exception
     */
    public static void ignoreSsl() throws Exception {
        HostnameVerifier hv = (urlHostName, session) -> {
            System.out.println("Warning: URL Host: " + urlHostName + " vs. " + session.getPeerHost());
            return true;
        };
        trustAllHttpsCertificates();
        HttpsURLConnection.setDefaultHostnameVerifier(hv);
    }

    private static void trustAllHttpsCertificates() throws Exception {
        TrustManager[] trustAllCerts = new TrustManager[1];
        TrustManager tm = new miTM();
        trustAllCerts[0] = tm;
        SSLContext sc = SSLContext.getInstance("SSL");
        sc.init(null, trustAllCerts, null);
        HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
    }

    static class miTM implements TrustManager, X509TrustManager {
        public X509Certificate[] getAcceptedIssuers() {
            return null;
        }

        public boolean isServerTrusted(X509Certificate[] certs) {
            return true;
        }

        public boolean isClientTrusted(X509Certificate[] certs) {
            return true;
        }

        public void checkServerTrusted(X509Certificate[] certs, String authType) {
        }

        public void checkClientTrusted(X509Certificate[] certs, String authType) {
        }
    }
}

• 爬虫方法

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 爬取数据
 *
 * @author Odinpeng
 * @since 2023/12/07
 **/
public class TestReport {

    @Test
    public void getRegionSql() throws Exception {
        // 获取数据库连接
        Connection conn = JdbcUtil.getConnection();
        // 忽略SSL证书验证
        try {
            SslUtils.ignoreSsl();
        } catch (Exception e1) {
            throw new RuntimeException("获取SSL证书异常");
        }
        // 通过Jsoup库连接到指定URL，获取网页内容
        String url = "https://www.mca.gov.cn/mzsj/xzqh/2020/20201201.html";
        Document doc = Jsoup.connect(url).header("User-Agent", "Mozilla/5.0 (Windows NT 10.0 Win64 x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.131 Safari/537.36")
                .header("Accept", "text/html,application/xhtml+xml,application/xmlq=0.9,image/webp,image/apng,*/*q=0.8,application/signed-exchangev=b3")
                .maxBodySize(0)
                .timeout(100000)
                .get();
        // 正则表达式，判断是否为六位数字的字符串
        String regionCodePattern = "^[1-9]\\d{5}$";
        // 获取所有行标签
        Elements trs = doc.select("tr");
        // 记录执行条数
        int count = 0;
        // 遍历行标签
        for (Element tr : trs) {
            // 获取行标签中的列标签
            Elements tds = tr.select("td");
            // 筛选出数量>3的列标签
            if (tds.size() > 3) {
                // 获取第二个<td>
                String dm = tds.get(1).text();
                // 获取第一个<td>,去除空格前包括空格的字符（字段含有 ）
                String mc = tds.get(2).text().replaceAll(".*\\s", "");
                // 代码含4个0，级别1（省、直辖市），代码含2个0，级别2（市），其他，级别3（区，县，县级市）
                if (dm.matches(regionCodePattern)) {
                    int jb = 2;
                    if (!dm.endsWith("00")) {
                        jb = 3;
                    }
                    if (dm.endsWith("0000")) {
                        jb = 1;
                    }
                    count++;
                    // 将代码、地区名称、级别拼接到sql中
                    String sql = String.format("insert into test_table (bm, mc, jb) " + " values ('%s','%s','%s');", dm, mc, jb);
                    System.out.println(sql);
                    try {
                        // 执行sql
                        Statement s = conn.createStatement();
                        s.executeUpdate(sql);
                    } catch (SQLException throwables) {
                        System.out.println("数据存储异常：" + throwables.getMessage());
                    }
                }
            }
        }
        System.out.println("总数量为：" + count);
        // 关闭数据库连接
        conn.close();
    }
}

三、测试

• 执行单元测试，数据库：