Quickstart: Use Java to query an Azure SQL database

This article demonstrates how to use Java to connect to an Azure SQL database. You can then use T-SQL statements to query data.

Prerequisites

To complete this sample, make sure you have the following prerequisites:

Get SQL server connection information

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. Sign in to the Azure portal.

  2. 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. 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. Change the folder to sqltest and open pom.xml with your favorite text editor. 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. 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. Save and close pom.xml.

Add code to query database

  1. You should already have a file called App.java in your Maven project located at:

    ..\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();
            }
        }
    }
    

    Note

    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. Verify the top 20 rows are returned and close the app window.

Next steps