快速入門:使用 JAVA 查詢 Azure SQL 資料庫Quickstart: Use Java to query an Azure SQL database

本文示範如何使用 Java 連線至 Azure SQL 資料庫。This article demonstrates how to use Java to connect to an Azure SQL database. 您可以接著使用 T-SQL 陳述式來查詢資料。You can then use T-SQL statements to query data.

必要條件Prerequisites

若要完成此範例,請確定您具有下列必要條件:To complete this sample, make sure you have the following prerequisites:

取得 SQL Server 連線資訊Get SQL server connection information

取得連線到 Azure SQL 資料庫所需的連線資訊。Get the connection information you need to connect to the Azure SQL database. 在後續程序中,您將需要完整的伺服器名稱或主機名稱、資料庫名稱和登入資訊。You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

  1. 登入 Azure 入口網站Sign in to the Azure portal.

  2. 瀏覽至 [SQL 資料庫] 或 [SQL 受控執行個體] 頁面。Navigate to the SQL databases or SQL managed instances page.

  3. 在 [概觀] 頁面上,針對單一資料庫檢閱 [伺服器名稱] 旁的完整伺服器名稱,若為受控執行個體,則檢閱 [主機] 旁的完整伺服器名稱。On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. 若要複製伺服器名稱或主機名稱,請將滑鼠暫留在其上方,然後選取 [複製] 圖示。To copy the server name or host name, hover over it and select the Copy icon.

建立專案Create the project

  1. 從命令提示字元,建立名為 sqltest 的新 Maven 專案。From the command prompt, create a new Maven project called sqltest.

    mvn archetype:generate "-DgroupId=com.sqldbsamples" "-DartifactId=sqltest" "-DarchetypeArtifactId=maven-archetype-quickstart" "-Dversion=1.0.0" --batch-mode
    
  2. 將資料夾切換到 sqltest,並使用您慣用的文字編輯器開啟 pom.xml 。Change the folder to sqltest and open pom.xml with your favorite text editor. 使用下列代碼將 Microsoft JDBC Driver for SQL Server 新增到專案的相依性。Add the Microsoft JDBC Driver for SQL Server to your project's dependencies using the following code.

    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>7.0.0.jre8</version>
    </dependency>
    
  3. 此外,在 pom.xml 中,將下列屬性新增至您的專案。Also in pom.xml, add the following properties to your project. 如果您沒有屬性區段,您可以將它新增在相依性後面。If you don't have a properties section, you can add it after the dependencies.

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
  4. 儲存並關閉 pom.xml 。Save and close pom.xml.

新增程式碼以查詢資料庫Add code to query database

  1. 您的 Maven 專案中應該已經有名為 App.java 的檔案,位於:You should already have a file called App.java in your Maven project located at:

    ..\sqltest\src\main\java\com\sqldbsamples\App.java..\sqltest\src\main\java\com\sqldbsamples\App.java

  2. 開啟檔案,並以下列程式碼取代其內容。Open the file and replace its contents with the following code. 然後,為您的伺服器、資料庫、使用者和密碼新增適當的值。Then add the appropriate values for your server, database, user, and password.

    package com.sqldbsamples;
    
    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.DriverManager;
    
    public class App {
    
        public static void main(String[] args) {
    
            // Connect to database
            String hostName = "your_server.database.windows.net"; // update me
            String dbName = "your_database"; // update me
            String user = "your_username"; // update me
            String password = "your_password"; // update me
            String url = String.format("jdbc:sqlserver://%s:1433;database=%s;user=%s;password=%s;encrypt=true;"
                + "hostNameInCertificate=*.database.windows.net;loginTimeout=30;", hostName, dbName, user, password);
            Connection connection = null;
    
            try {
                connection = DriverManager.getConnection(url);
                String schema = connection.getSchema();
                System.out.println("Successful connection - Schema: " + schema);
    
                System.out.println("Query data example:");
                System.out.println("=========================================");
    
                // Create and execute a SELECT SQL statement.
                String selectSql = "SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName "
                    + "FROM [SalesLT].[ProductCategory] pc "  
                    + "JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid";
    
                try (Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(selectSql)) {
    
                    // Print results from select statement
                    System.out.println("Top 20 categories:");
                    while (resultSet.next())
                    {
                        System.out.println(resultSet.getString(1) + " "
                            + resultSet.getString(2));
                    }
                    connection.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    

    注意

    程式碼範例會使用適用於 Azure SQL 的 AdventureWorksLT 範例資料庫。The code example uses the AdventureWorksLT sample database for Azure SQL.

執行程式碼Run the code

  1. 在命令提示字元中,執行應用程式。At the command prompt, run the app.

    mvn package -DskipTests
    mvn -q exec:java "-Dexec.mainClass=com.sqldbsamples.App"
    
  2. 請確認前 20 個資料列已傳回,然後關閉應用程式視窗。Verify the top 20 rows are returned and close the app window.

後續步驟Next steps