Java MySQL 資料函式庫整合查詢插入與自動化

在 Java 開發中，資料函式庫操作是不可或缺的一環。本文將示範如何使用 Java 連線 MySQL 資料函式庫，執行 SQL 查詢和插入操作，並利用 PreparedStatement 避免 SQL 注入風險。同時，我們也將探討如何使用 CallableStatement 呼叫儲存程式，簡化資料函式庫操作流程。此外，文章也將介紹物件關聯對映（ORM）的概念，以及如何使用 cron、Bash 和 SQL 指令碼自動化天氣資料更新流程，包含資料函式庫與資料表設計、資料型別選擇以及約束條件設定等實務技巧，提供讀者更全面的資料函式庫操作知識。最後，我們將示範如何建立天氣資料函式庫，並使用 cron 定期更新資料，確保資料的即時性和有效性。

使用Java插入資料到MySQL資料函式庫

在這個範例中，我們建立了一個名為MountainNew的Java類別，該類別包含一個main()方法。在main()方法中，我們建立了一個連線到MySQL資料函式庫的連線物件，並將其儲存為conn變數。

然後，我們定義了一個SQL插入陳述式，該陳述式用於插入一列新的資料到mountain表格中。注意到，這個SQL陳述式使用了問號作為佔位符，以便我們可以使用PreparedStatement物件來設定實際的引數值。

接下來，我們使用Connection物件的prepareStatement()方法建立一個PreparedStatement物件，該物件用於執行SQL插入陳述式。然後，我們使用setString()和setInt()方法設定PreparedStatement物件的引數值，最後，我們呼叫executeUpdate()方法執行SQL插入陳述式。

import java.sql.*;

public class MountainList {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost/topography";
        String username = "top_app";
        String password = "pQ3fgR5u5";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement();
            String sql = "select mountain_name, location, height from mountain";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                System.out.println(rs.getString("mountain_name") + " | " + rs.getString("location") + " | " + rs.getInt("height"));
            }
            conn.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

import java.sql.*;

public class MountainNew {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost/topography";
        String username = "top_app";
        String password = "pQ3fgR5u5";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "insert into mountain(mountain_name, location, height) values (?,?,?)";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, "Kangchenjunga");
            stmt.setString(2, "Asia");
            stmt.setInt(3, 28169);
            stmt.executeUpdate();
            conn.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

內容解密：

上述Java程式碼示範瞭如何使用Java連線MySQL資料函式庫並執行查詢和插入操作。首先，我們需要載入MySQL Connector/J的驅動程式，然後建立一個連線到MySQL資料函式庫的連線物件。接下來，我們可以使用Statement或PreparedStatement物件來執行SQL查詢或插入陳述式。

在查詢操作中，我們使用Statement物件的executeQuery()方法執行SQL查詢，並取得查詢結果的ResultSet物件。然後，我們可以使用while迴圈遍歷ResultSet物件，將每一列的資料印出到主控臺。

在插入操作中，我們使用PreparedStatement物件來設定實際的引數值，然後呼叫executeUpdate()方法執行SQL插入陳述式。

圖表翻譯：

上述Plantuml圖表描述了Java程式碼的流程，從載入MySQL驅動程式開始，到建立連線物件、建立Statement或PreparedStatement物件、執行SQL查詢或插入陳述式、取得查詢結果或執行結果，最後關閉連線物件。

呼叫儲存程式

以下是Java程式，示範如何呼叫MySQL儲存程式。

import java.sql.*;

public class MountainAsia {

    public static void main(String args[]) {
        String url = "jdbc:mysql://localhost/topography";
        String username = "top_app";
        String password = "pQ3fgR5u5";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "call p_get_mountain_by_loc(?)";
            CallableStatement stmt = conn.prepareCall(sql);
            stmt.setString(1, "Asia");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.println(
                    rs.getString("mountain_name") + " | " +
                    rs.getInt("height")
                );
            }
            conn.close();

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

這次，我們使用CallableStatement來呼叫儲存程式。設定第一個（也是唯一一個）引數為Asia，然後使用CallableStatement的executeQuery()方法執行查詢。接著，我們遍歷結果，顯示每個山的名稱和高度。

結果如下：

Mount Everest | 29029
K2 | 28252
Makalu | 27766
Lhotse | 27940
Kangchenjunga | 28169

物件關聯對映（ORM）

物件關聯對映（Object-Relational Mapping，ORM）工具提供了一種不同的方法來使用MySQL於程式語言中。ORM允許您使用您喜愛的物件導向程式語言來與資料函式庫互動，而不是使用SQL。ORM使得資料函式庫中的資料以物件的形式呈現給您，您可以在程式碼中操控這些物件。

建立天氣資料函式庫

在這個專案中，您將為一家貨運公司建立一個天氣資料函式庫。該公司在美國東海岸運輸貨物，需要取得其司機經過的主要城市的當前天氣情況。

專案需求

公司已經有一個 MySQL 資料函式庫，包含貨運資料，但您需要新增一個新的資料函式庫，詳細記錄司機行駛區域的當前天氣狀況。這將使您能夠將天氣資料整合到現有的貨運應用程式中，以顯示天氣對排程的影響，並提醒司機注意黑冰、雪和極端溫度等危險條件。

資料來源

您將從一家提供天氣資料的第三方公司獲得天氣資料。該公司已同意每小時透過 FTP（檔案傳輸協定）將 CSV 檔案傳送到您的 Linux 伺服器上的 /home/weather_load/ 目錄中。該檔案約每小時到達一次，但可能會有延遲，因此您需要撰寫一個程式，每 5 分鐘執行一次，以檢查檔案是否可用，並在可用時將其載入您的資料函式庫中。

專案步驟

1. 建立資料函式庫和表格：您需要建立兩個表格，current_weather_load 和 current_weather，以儲存天氣資料。
2. 載入天氣資料：您將撰寫一個程式，使用 Bash 和 SQL 指令碼，將 CSV 檔案中的資料載入 current_weather_load 表格中。
3. 驗證資料：一旦資料載入，您需要確保資料沒有問題。
4. 複製資料：確認資料正確後，您將複製 current_weather_load 表格中的資料到 current_weather 表格中，這是您的貨運應用程式將使用的表格。

使用技術

• MySQL：資料函式倉管理系統
• cron：任務排程器
• Bash：命令列指令碼語言
• SQL 指令碼：用於與資料函式庫互動

cron 的使用

cron 是一個任務排程器，允許您在 Unix-like 作業系統（包括 Linux 和 macOS）上排程任務。您可以使用 cron 安排指令碼在背景執行，而不需要終端機連線。要安裝 cron，請在命令列中輸入 wsl --install（適用於 Windows Subsystem for Linux）。

要學習更多關於 cron 的資訊，您可以在命令列中輸入 man cron。如果 cron 沒有安裝在您的電腦上，您需要線上搜尋安裝它的命令。

您可以啟動和停止 cron，但啟動和停止的命令因環境而異。您可以使用 man cron 來找到這些命令，並檢查 cron 的狀態以檢視它是否正在執行。

要列出您的排程任務，您可以使用 crontab -l 命令。如果您需要編輯您的 crontab 組態檔案，您可以使用 crontab -e 命令，這將開啟一個文字編輯器，允許您新增、修改或刪除任務。

要排程一個 cron 任務，您需要提供六個資訊，按以下順序：

1. 分鐘（0-59）
2. 小時（0-23）
3. 日（1-31）
4. 月（1-12）
5. 星期（0-6），其中 0 代表星期日
6. 命令：要執行的命令或指令碼

內容解密：

上述步驟和技術是用於建立一個天氣資料函式庫，以供貨運公司使用。cron 技術被用於排程任務，以確保天氣資料定期更新。Bash 指令碼和 SQL 指令碼被用於載入和驗證資料。

圖表翻譯：

上述流程圖顯示了建立天氣資料函式庫的步驟。首先，建立資料函式庫和表格，然後載入天氣資料，接著驗證資料，最後複製資料到最終的表格中。

自動化天氣資料更新

介紹

在本章中，我們將使用 cron 工具來自動化天氣資料的更新。cron 是一個強大的工具，允許您在特定時間或間隔執行命令或指令碼。這裡，我們將使用 cron 來執行一個 Bash 指令碼，每 5 分鐘檢查一次新的天氣資料檔案是否存在，如果存在，就會將資料載入資料函式庫中。

cron 工具

cron 工具的基本語法如下：

minute hour day month day_of_week command

其中：

• minute：分鐘（0-59）
• hour：小時（0-23）
• day：日（1-31）
• month：月（1-12）
• day_of_week：星期（0-6，Sunday=0）
• command：要執行的命令或指令碼

例如，如果您想要在每年的 3 月 14 日凌晨 3:14 執行一個指令碼，您可以使用以下 cron 工具語法：

14 3 14 3 * /usr/local/bin/pi_day.sh

這裡，pi_day.sh 是您想要執行的指令碼。

Bash 指令碼

Bash 指令碼是一種 shell 指令碼，通常具有 .sh 的副檔名。這裡，我們將使用 Bash 指令碼來檢查新的天氣資料檔案是否存在，如果存在，就會將資料載入資料函式庫中。

SQL 指令碼

SQL 指令碼是一種包含 SQL 命令的文字檔案。這裡，我們將使用兩個 SQL 指令碼：load_weather.sql 和 copy_weather.sql。load_weather.sql 指令碼將負責載入天氣資料到 current_weather_load 表中，而 copy_weather.sql 指令碼將負責將資料從 current_weather_load 表複製到 current_weather 表中。

專案概覽

以下是專案的流程圖：

1. 每 5 分鐘，cron 工具將執行 weather.sh 指令碼。
2. 如果新的天氣資料檔案存在，指令碼將載入資料到 current_weather_load 表中。
3. 如果載入資料沒有錯誤，指令碼將呼叫 copy_weather.sql 指令碼將資料從 current_weather_load 表複製到 current_weather 表中。

天氣資料檔案

天氣資料檔案是一個 CSV 檔案，包含以下欄位：

• station_id：天氣站的 ID
• station_city：天氣站所在地的城市
• station_state：天氣站所在地的州（兩個字元的程式碼）
• station_lat：天氣站的緯度
• station_lon：天氣站的經度
• as_of_datetime：資料收集的日期和時間
• temp：溫度
• feels_like：感覺到的溫度

以下是 CSV 檔案的範例：

station_id,station_city,station_state,station_lat,station_lon,as_of_datetime,temp,feels_like
12345,Portland,ME,43.6532,-70.2567,2023-03-14 14:30:00,45,42

在下一節中，我們將開始實作這個專案。

建立天氣資料函式庫及資料表

為了儲存天氣資料，我們需要建立一個名為 weather 的 MySQL 資料函式庫。以下是建立資料函式庫的 SQL 指令：

CREATE DATABASE weather;

接下來，我們需要建立兩個資料表：current_weather_load 和 current_weather。current_weather_load 資料表用於載入 CSV 檔案中的天氣資料，而 current_weather 資料表則是用於儲存已經載入並驗證過的天氣資料。

以下是建立 current_weather_load 資料表的 SQL 指令：

CREATE TABLE current_weather_load (
  station_id INT PRIMARY KEY,
  station_city VARCHAR(100),
  station_state CHAR(2),
  station_lat DECIMAL(6, 4) NOT NULL,
  station_lon DECIMAL(7, 4) NOT NULL,
  as_of_dt DATETIME,
  temp INT NOT NULL,
  feels_like INT,
  wind INT,
  wind_direction VARCHAR(3),
  precipitation DECIMAL(3, 1),
  pressure DECIMAL(6, 2),
  visibility DECIMAL(3, 1) NOT NULL,
  humidity INT,
  weather_desc VARCHAR(100) NOT NULL,
  sunrise TIME,
  sunset TIME,
  CONSTRAINT check_station_lat CHECK (station_lat BETWEEN -90 AND 90),
  CONSTRAINT check_station_lon CHECK (station_lon BETWEEN -180 AND 180),
  CONSTRAINT check_as_of_dt CHECK (as_of_dt BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW()),
  CONSTRAINT check_temp CHECK (temp BETWEEN -50 AND 150),
  CONSTRAINT check_feels_like CHECK (feels_like BETWEEN -50 AND 150),
  CONSTRAINT check_wind CHECK (wind BETWEEN 0 AND 300),
  CONSTRAINT check_wind_direction CHECK (wind_direction IN ('N', 'S', 'E', 'W', 'NE', 'NW', 'SE', 'SW', 'NNE', 'ENE', 'ESE', 'SSE', 'SSW', 'WSW', 'WNW', 'NNW')),
  CONSTRAINT check_precipitation CHECK (precipitation BETWEEN 0 AND 400),
  CONSTRAINT check_pressure CHECK (pressure BETWEEN 0 AND 1100),
  CONSTRAINT check_visibility CHECK (visibility BETWEEN 0 AND 20),
  CONSTRAINT check_humidity CHECK (humidity BETWEEN 0 AND 100)
);

接下來，我們可以建立 current_weather 資料表，其結構與 current_weather_load 資料表相同：

CREATE TABLE current_weather LIKE current_weather_load;

這樣，我們就有了一個用於載入 CSV 檔案中的天氣資料的資料表，以及一個用於儲存已經載入並驗證過的天氣資料的最終、使用者導向的資料表。

內容解密：

在上述 SQL 指令中，我們定義了 current_weather_load 資料表的結構，包括各個欄位的名稱、資料型別和約束條件。其中，station_id 欄位是主鍵，station_lat 和 station_lon 欄位分別代表天氣站的緯度和經度，as_of_dt 欄位代表天氣資料的時間，temp 和 feels_like 欄位分別代表溫度和感覺溫度，wind 和 wind_direction 欄位分別代表風速和風向，precipitation 和 pressure 欄位分別代表降水量和氣壓，visibility 和 humidity 欄位分別代表能見度和相對濕度，weather_desc 欄位代表天氣描述，sunrise 和 sunset 欄位分別代表日出和日落時間。

我們還定義了多個約束條件，以確保天氣資料的有效性和一致性。例如，station_lat 和 station_lon 欄位的值必須在指定的範圍內，as_of_dt 欄位的值必須在最近的一天內，temp 和 feels_like 欄位的值必須在指定的範圍內，等等。

圖表翻譯：

以下是 Plantuml 圖表，用於視覺化展示天氣資料表的結構：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Java MySQL 資料函式庫整合查詢插入與自動化

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

這個圖表展示了 current_weather_load 和 current_weather 資料表之間的關係，以及各個欄位的名稱和資料型別。

選擇適合的資料型別

選擇適合的資料型別對於資料函式庫的設計至關重要。例如，若要儲存氣象站的ID、溫度、風速和濕度等資料，應該選擇能夠精確代表這些資料的資料型別。例如，氣象站的ID可以使用整數（int）型別，因為它通常不會包含小數點。另一方面，溫度和濕度等資料可能需要使用帶有小數點的資料型別，如decimal。

從資料函式庫設計、程式碼撰寫到自動化流程的建構，本文完整展示瞭如何使用 Java 與 MySQL 建立並維護一個天氣資料函式庫。透過 JDBC 連線、PreparedStatement 與 CallableStatement，我們深入探討了資料函式庫的查詢、新增資料以及呼叫儲存程式的實作細節。此外，文章也觸及了 ORM 的概念，為讀者提供了更進階的資料函式庫操作思路。

觀察產業技術趨勢，資料函式庫自動化管理的重要性日益凸顯。本文以 cron 工具和 Bash 指令碼的整合為例，展現瞭如何自動化天氣資料的更新流程，有效提升資料函式庫維護效率並降低人為錯誤的風險。值得一提的是，文章對於資料函式庫表格設計的嚴謹性，從資料型別選擇到約束條件的設定，都體現了最佳實務的應用。尤其在current_weather_load資料表設計中，欄位型別與約束條件的設定，有效確保了資料的完整性與一致性，這對於資料函式庫的長期穩定執行至關重要。

展望未來，隨著資料量的持續增長和資料函式庫應用場景的日益複雜，更高效、更自動化的資料函式倉管理方案將成為主流趨勢。預計雲端原生資料函式庫、Serverless 技術以及 AI 驅動的資料函式倉管理工具將扮演更重要的角色。對於開發者而言，持續學習並掌握這些新興技術，才能在未來的技術浪潮中保持競爭力。玄貓認為，掌握資料函式庫自動化管理技術，不僅能提升開發效率，更能為企業創造更大的商業價值，是技術人員不可或缺的核心技能。