使用 PostgreSQL 適用的 Cloud SQL

本頁說明如何從 App Engine 應用程式連線到 PostgreSQL 適用的 Cloud SQL 執行個體,以及如何讀取及寫入 Cloud SQL。Cloud SQL 是存放在 Google 雲端系統的 SQL 資料庫。

如要進一步瞭解 Cloud SQL,請參閱 Cloud SQL 說明文件。如要瞭解 Cloud SQL 定價和相關限制,請參閱 Cloud SQL 定價頁面。App Engine 應用程式也會受到 App Engine 配額的限制。

事前準備

  1. 在 GCP 主控台中建立或選取 GCP 專案,接著確認該專案含有 App Engine 應用程式並啟用了計費功能:
    前往 App Engine

    如果您的專案中已存在 App Engine 應用程式且已經啟用計費功能,系統就會開啟「Dashboard」(資訊主頁)。否則,請按照提示選取地區,然後啟用計費功能。

  2. 啟用Cloud SQL API。

    啟用 API

  3. 如要將 Java 應用程式部署至 App Engine,您必須先設定您的環境,詳情請參閱使用 Apache Maven 和 App Engine 外掛程式

設定 Cloud SQL 執行個體

如要建立並設定 Cloud SQL 執行個體:

  1. 建立 PostgreSQL 執行個體適用的 Cloud SQL
  2. 如果您尚未在 Cloud SQL 執行個體上為預設使用者設定密碼,請先執行下列指令完成設定作業:
    gcloud sql users set-password postgres no-host --instance [INSTANCE_NAME] --password [PASSWORD]
    
  3. 如果您不想使用預設使用者進行連線,請建立使用者
  4. 記錄該執行個體的連線名稱:
    gcloud sql instances describe [INSTANCE_NAME]
    

    例如:

    connectionName: project1:us-central1:instance1
    

    在 Google Cloud Platform 主控台的「執行個體詳細資料」頁面也可以找到此值。

  5. 在此範例中,在名為 sqldemo 的 Cloud SQL 執行個體上建立資料庫。
    gcloud sql databases create sqldemo --instance=[INSTANCE_NAME]
    
    如要進一步瞭解如何建立和管理資料庫,請參閱 Cloud SQL 說明文件

設定連線字串和新增資料庫

使用 Cloud SQL JDBC Socket Factory 通訊端資料庫連結您的 Cloud SQL 執行個體,以便進行本機測試和部署作業。

  1. 使用您的連線名稱、使用者名稱與密碼更新 pom.xml

      <properties>
    <!--
        INSTANCE_CONNECTION_NAME from Cloud Console > SQL > Instance Details > Properties
        or `gcloud sql instances describe <instance> | grep connectionName`
    -->
        <INSTANCE_CONNECTION_NAME>Project:Region:Instance</INSTANCE_CONNECTION_NAME>
        <user>root</user>
        <password>myPassword</password>
        <database>sqldemo</database>
        <sqlURL>jdbc:postgresql://google/${database}?useSSL=false&amp;socketFactoryArg=${INSTANCE_CONNECTION_NAME}&amp;socketFactory=com.google.cloud.sql.postgres.SocketFactory&amp;user=${user}&amp;password=${password}</sqlURL>
      </properties>

  2. 將所需資源篩選加入至 pom.xml

    <resources>
      <resource>
        <directory>src/main/resources</directory>
        <filtering>true</filtering>
      </resource>
    </resources>

  3. 建立名為 config.properties 且包含以下這一行的檔案:

    sqlUrl=${sqlURL}

    這個檔案需要 pom.xml 中設定的資源篩選。

  4. 將依附元件新增至 pom.xml,以將 JDBC 程式庫與 Cloud SQL JDBC 通訊端處理站新增至您的應用程式:

    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.2.6</version>
    </dependency>
    
    <dependency>
      <groupId>com.google.cloud.sql</groupId>
      <artifactId>postgres-socket-factory</artifactId>
      <version>1.0.14</version>
    </dependency>

執行程式碼範例

以下範例可將造訪資訊寫入至 Cloud SQL,然後讀取並傳回最後十次造訪的資訊:
@SuppressWarnings("serial")
@WebServlet(name = "postgresql", value = "")
public class PostgresSqlServlet extends HttpServlet {
  Connection conn;

  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
      ServletException {

    final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id SERIAL NOT NULL, "
        + "user_ip VARCHAR(46) NOT NULL, ts timestamp NOT NULL, "
        + "PRIMARY KEY (visit_id) );";
    final String createVisitSql = "INSERT INTO visits (user_ip, ts) VALUES (?, ?);";
    final String selectSql = "SELECT user_ip, ts FROM visits ORDER BY ts DESC "
        + "LIMIT 10;";

    String path = req.getRequestURI();
    if (path.startsWith("/favicon.ico")) {
      return; // ignore the request for favicon.ico
    }

    PrintWriter out = resp.getWriter();
    resp.setContentType("text/plain");

    // store only the first two octets of a users ip address
    String userIp = req.getRemoteAddr();
    InetAddress address = InetAddress.getByName(userIp);
    if (address instanceof Inet6Address) {
      // nest indexOf calls to find the second occurrence of a character in a string
      // an alternative is to use Apache Commons Lang: StringUtils.ordinalIndexOf()
      userIp = userIp.substring(0, userIp.indexOf(":", userIp.indexOf(":") + 1)) + ":*:*:*:*:*:*";
    } else if (address instanceof Inet4Address) {
      userIp = userIp.substring(0, userIp.indexOf(".", userIp.indexOf(".") + 1)) + ".*.*";
    }

    Stopwatch stopwatch = Stopwatch.createStarted();
    try (PreparedStatement statementCreateVisit = conn.prepareStatement(createVisitSql)) {
      conn.createStatement().executeUpdate(createTableSql);
      statementCreateVisit.setString(1, userIp);
      statementCreateVisit.setTimestamp(2, new Timestamp(new Date().getTime()));
      statementCreateVisit.executeUpdate();

      try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
        stopwatch.stop();
        out.print("Last 10 visits:\n");
        while (rs.next()) {
          String savedIp = rs.getString("user_ip");
          String timeStamp = rs.getString("ts");
          out.println("Time: " + timeStamp + " Addr: " + savedIp);
        }
        out.println("Elapsed: " + stopwatch.elapsed(TimeUnit.MILLISECONDS));
      }
    } catch (SQLException e) {
      throw new ServletException("SQL error", e);
    }
  }

  @Override
  public void init() throws ServletException {
    String url;

    Properties properties = new Properties();
    try {
      properties.load(
          getServletContext().getResourceAsStream("/WEB-INF/classes/config.properties"));
      url = properties.getProperty("sqlUrl");
    } catch (IOException e) {
      log("no property", e);  // Servlet Init should never fail.
      return;
    }

    log("connecting to: " + url);
    try {
      Class.forName("org.postgresql.Driver");
      conn = DriverManager.getConnection(url);
    } catch (ClassNotFoundException e) {
      throw new ServletException("Error loading JDBC Driver", e);
    } catch (SQLException e) {
      throw new ServletException("Unable to connect to PostGre", e);
    } finally {
      // Nothing really to do here.
    }
  }
}

測試與部署

  1. 如要在本機上測試您的應用程式,請執行下列指令:

    mvn clean jetty:run
    
  2. 本機測試結束後,如要將您的應用程式部署到 App Engine,請執行下列指令:

    mvn clean appengine:deploy
    

  3. 如要啟動瀏覽器並在 http://[YOUR_PROJECT_ID].appspot.com 查看應用程式,請執行下列指令:

    gcloud app browse
    

在不同專案中執行 Cloud SQL 和 App Engine

如果您的 App Engine 應用程式和 Cloud SQL 執行個體位在不同的 Google Cloud Platform 專案中,您必須使用服務帳戶允許 App Engine 應用程式存取 Cloud SQL。

這個服務帳戶代表您的 App Engine 應用程式,系統預設會在您建立 Google Cloud Platform 專案時一併建立此帳戶。

  1. 如果您的 App Engine 應用程式和 Cloud SQL 執行個體位於相同專案內,請略過此節,然後前往設定連線字串和新增資料庫。否則,請繼續下一步。
  2. 找出與 App Engine 應用程式相關聯的服務帳戶。預設的 App Engine 服務帳戶名稱為 [PROJECT-ID]@appspot.gserviceaccount.com

    您可以在「IAM Permissions」(IAM 權限) 頁面中驗證該 App Engine 服務帳戶。請確認您選取的是 App Engine 應用程式的專案,而非 Cloud SQL 執行個體的專案。

    前往「IAM Permissions」(IAM 權限) 頁面

  3. 前往 Google Cloud Platform 主控台中的「IAM & Admin Projects」(IAM 與管理員) 的專案頁面。

    前往「IAM & Admin Projects」(IAM 與管理員) 的專案頁面

  4. 選取包含 Cloud SQL 執行個體的專案。
  5. 搜尋服務帳戶名稱。
  6. 如果該服務帳戶已存在,並具備包含 cloudsql.instances.connect 權限的角色,您可以繼續設定連線字串和新增資料庫

    Cloud SQL ClientCloud SQL EditorCloud SQL Admin 角色皆提供所需的權限,舊版 EditorOwner 專案角色亦然。

  7. 如果沒有服務帳戶,請按一下 [Add] (新增)來新增服務帳戶。
  8. 在「Add members」(新增成員) 對話方塊中輸入服務帳戶的名稱,然後選取具備 cloudsql.instances.connect 權限的角色 (除了「檢視者」之外,任何 Cloud SQL 預先定義的角色皆可)。

    或者,您也可以選取 [Project] (專案) > [Editor] (編輯者) 來使用原始編輯者角色,不過編輯者角色包含 Google Cloud Platform 中的各種權限。

    如果您沒有看到這些角色,即表示您的 Google Cloud Platform 使用者可能沒有 resourcemanager.projects.setIamPolicy 權限。您可以前往 Google Cloud Platform 主控台的 IAM 頁面,然後搜尋您的使用者 ID,藉此檢查您的權限。

  9. 按一下 [Add] (新增)。

    畫面上應會列出具備指定角色的服務帳戶。

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Java 適用的 App Engine 彈性環境文件