快速入门:使用 Java 查询 Azure SQL 数据库中的数据库或 Azure SQL 托管实例Quickstart: Use Java to query a database in Azure SQL Database or Azure SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

在本快速入门中,你将使用 Java 连接到 Azure SQL 数据库中的数据库或 Azure SQL 托管实例,并使用 T-SQL 语句来查询数据。In this quickstart, you use Java to connect to a database in Azure SQL Database or Azure SQL Managed Instance, and use T-SQL statements to query data.

先决条件Prerequisites

若要完成本快速入门,你需要:To complete this quickstart, you need:


重要

本文中脚本的编写目的是使用 Adventure Works 数据库。The scripts in this article are written to use the Adventure Works database.

备注

可以选择使用 Azure SQL 托管实例。You can optionally choose to use an Azure SQL Managed Instance.

若要执行创建和配置操作,请使用 Azure 门户PowerShellCLI,然后设置本地VM 连接性。To create and configure, use the Azure portal, PowerShell, or CLI, and then set up on-premises or VM connectivity.

若要加载数据,请参阅通过 BACPAC 进行还原(使用 Adventure Works 文件),或参阅还原 Wide World Importers 数据库To load data, see restore with BACPAC with the Adventure Works file, or see restore the Wide World Importers database.

获取服务器连接信息Get server connection information

获取连接到 Azure SQL 数据库中的数据库所需的连接信息。Get the connection information you need to connect to the database in 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 托管实例”页。Select SQL databases or open the SQL Managed Instances page.

  3. 在“概述”页上,在“Server 名称”旁查看 Azure SQL 数据库中的数据库的完全限定服务器名称,或在“Host”旁边查看 Azure VM 上的 Azure SQL 托管实例或 SQL Server 的完全限定服务器名称(或 IP 地址) 。On the Overview page, review the fully qualified server name next to Server name for a database in Azure SQL Database or the fully qualified server name (or IP address) next to Host for an Azure SQL Managed Instance or SQL Server on Azure VM. 若要复制服务器名称或主机名称,请将鼠标悬停在其上方,然后选择“复制”图标。To copy the server name or host name, hover over it and select the Copy icon.

备注

有关 Azure VM 上的 SQL Server 的连接信息,请参阅连接到 SQL ServerFor connection information for SQL Server on Azure VM, see Connect to SQL Server.

创建项目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. 如果没有 properties 节,可以将其添加到 dependencies 后面。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 the 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.chinacloudapi.cn"; // 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.chinacloudapi.cn;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 in Azure SQL Database.

运行代码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 that the top 20 rows are returned and close the app window.

后续步骤Next steps