Início rápido: usar o Java para se conectar e consultar dados no Azure Database para PostgreSQL-servidor únicoQuickstart: Use Java to connect to and query data in Azure Database for PostgreSQL - Single Server

Este guia de início rápido explica como se pode ligar a uma Base de Dados do Azure para PostgreSQL através de uma aplicação Java.This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using a Java application. Explica como utilizar as instruções SQL para consultar, inserir, atualizar e eliminar dados da base de dados.It shows how to use SQL statements to query, insert, update, and delete data in the database. Os passos neste artigo pressupõem que está familiarizado com a programação com Java e que nunca trabalhou com a Base de Dados do Azure para PostgreSQL.The steps in this article assume that you are familiar with developing using Java, and are new to working with Azure Database for PostgreSQL.

Pré-requisitosPrerequisites

Este guia de início rápido utiliza os recursos criados em qualquer um destes guias como ponto de partida:This quickstart uses the resources created in either of these guides as a starting point:

Também tem de:You also need to:

  • Transferir o Controlador PostgreSQL JDBC correspondente à sua versão de Java e o Kit de programação Java.Download the PostgreSQL JDBC Driver matching your version of Java and the Java Development Kit.
  • Incluir o ficheiro jar do PostgreSQL JDBC (por exemplo, postgresql-42.1.1.jar) no caminho da classe da sua aplicação.Include the PostgreSQL JDBC jar file (for example postgresql-42.1.1.jar) in your application classpath. Para obter mais informações, consulte detalhes do caminho da classe.For more information, see classpath details.

Obter informações da ligaçãoGet connection information

Obtenha as informações de ligação necessárias para se ligar à Base de Dados do Azure para PostgreSQL.Get the connection information needed to connect to the Azure Database for PostgreSQL. Necessita do nome do servidor e das credenciais de início de sessão totalmente qualificados.You need the fully qualified server name and login credentials.

  1. Inicie sessão no Portal do Azure.Log in to the Azure portal.
  2. No menu à esquerda no portal do Azure, selecione todos os recursose procure o servidor que você criou (como mydemoserver).From the left-hand menu in Azure portal, select All resources, and then search for the server you have created (such as mydemoserver).
  3. Selecione o nome do servidor.Select the server name.
  4. No painel Descrição geral do servidor, tome nota do Nome do servidor e do Nome de início de sessão de administrador do servidor.From the server's Overview panel, make a note of the Server name and Server admin login name. Caso se esqueça da sua palavra-passe, também pode repor a palavra-passe neste painel.If you forget your password, you can also reset the password from this panel. Nome do servidor da Base de Dados do Azure para PostgreSQLAzure Database for PostgreSQL server name

Ligar, criar tabela e inserir dadosConnect, create table, and insert data

Utilize o código seguinte para se ligar e carregar os dados para a base de dados através da função com a instrução SQL INSERT.Use the following code to connect and load the data into the database using the function with an INSERT SQL statement. Os métodos getConnection(), createStatement() e executeQuery() servem para se ligar à base de dados, remover e criar a tabela.The methods getConnection(), createStatement(), and executeQuery() are used to connect to the database, drop, and create the table. O objeto prepareStatement é utilizado para criar os comandos de introdução, com setString() e setInt() que unem os valores do parâmetro.The prepareStatement object is used to build the insert commands, with setString() and setInt() to bind the parameter values. O método executeUpdate() executa o comando para cada conjunto de parâmetros.Method executeUpdate() runs the command for each set of parameters.

Substitua os parâmetros do sistema anfitrião, da base de dados, do utilizador e da palavra-passe pelos valores que especificou ao criar o seu próprio servidor e base de dados.Replace the host, database, user, and password parameters with the values that you specified when you created your own server and database.

import java.sql.*;
import java.util.Properties;

public class CreateTableInsertRows {

    public static void main (String[] args)  throws Exception
    {

        // Initialize connection variables.
        String host = "mydemoserver.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mydemoserver";
        String password = "<server_admin_password>";

        // check that the driver is installed
        try
        {
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException e)
        {
            throw new ClassNotFoundException("PostgreSQL JDBC driver NOT detected in library path.", e);
        }

        System.out.println("PostgreSQL JDBC driver detected in library path.");

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:postgresql://%s/%s", host, database);
            
            // set up the connection properties
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("ssl", "true");

            // get connection
            connection = DriverManager.getConnection(url, properties);
        }
        catch (SQLException e)
        {
            throw new SQLException("Failed to create connection to database.", e);
        }
        if (connection != null) 
        { 
            System.out.println("Successfully created connection to database.");
        
            // Perform some SQL queries over the connection.
            try
            {
                // Drop previous table of same name if one exists.
                Statement statement = connection.createStatement();
                statement.execute("DROP TABLE IF EXISTS inventory;");
                System.out.println("Finished dropping table (if existed).");
    
                // Create table.
                statement.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);");
                System.out.println("Created table.");
    
                // Insert some data into table.
                int nRowsInserted = 0;
                PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO inventory (name, quantity) VALUES (?, ?);");
                preparedStatement.setString(1, "banana");
                preparedStatement.setInt(2, 150);
                nRowsInserted += preparedStatement.executeUpdate();

                preparedStatement.setString(1, "orange");
                preparedStatement.setInt(2, 154);
                nRowsInserted += preparedStatement.executeUpdate();

                preparedStatement.setString(1, "apple");
                preparedStatement.setInt(2, 100);
                nRowsInserted += preparedStatement.executeUpdate();
                System.out.println(String.format("Inserted %d row(s) of data.", nRowsInserted));
    
                // NOTE No need to commit all changes to database, as auto-commit is enabled by default.
    
            }
            catch (SQLException e)
            {
                throw new SQLException("Encountered an error when executing given sql statement.", e);
            }       
        }
        else {
            System.out.println("Failed to create connection to database.");
        }
        System.out.println("Execution finished.");
    }
}

Ler dadosRead data

Utilize o código seguinte para ler os dados com a instrução SQL SELECT.Use the following code to read the data with a SELECT SQL statement. Os métodos getConnection(), createStatement() e executeQuery() servem para se ligar à base de dados, criar e executar a declaração de seleção.The methods getConnection(), createStatement(), and executeQuery() are used to connect to the database, create, and run the select statement. Os resultados são processados através de um objeto ResultSet.The results are processed using a ResultSet object.

Substitua os parâmetros do sistema anfitrião, da base de dados, do utilizador e da palavra-passe pelos valores que especificou ao criar o seu próprio servidor e base de dados.Replace the host, database, user, and password parameters with the values that you specified when you created your own server and database.

import java.sql.*;
import java.util.Properties;

public class ReadTable {

    public static void main (String[] args)  throws Exception
    {

        // Initialize connection variables.
        String host = "mydemoserver.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mydemoserver";
        String password = "<server_admin_password>";

        // check that the driver is installed
        try
        {
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException e)
        {
            throw new ClassNotFoundException("PostgreSQL JDBC driver NOT detected in library path.", e);
        }

        System.out.println("PostgreSQL JDBC driver detected in library path.");

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:postgresql://%s/%s", host, database);
            
            // set up the connection properties
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("ssl", "true");

            // get connection
            connection = DriverManager.getConnection(url, properties);
        }
        catch (SQLException e)
        {
            throw new SQLException("Failed to create connection to database.", e);
        }
        if (connection != null) 
        { 
            System.out.println("Successfully created connection to database.");
        
            // Perform some SQL queries over the connection.
            try
            {
    
                Statement statement = connection.createStatement();
                ResultSet results = statement.executeQuery("SELECT * from inventory;");
                while (results.next())
                {
                    String outputString = 
                        String.format(
                            "Data row = (%s, %s, %s)",
                            results.getString(1),
                            results.getString(2),
                            results.getString(3));
                    System.out.println(outputString);
                }
            }
            catch (SQLException e)
            {
                throw new SQLException("Encountered an error when executing given sql statement.", e);
            }       
        }
        else {
            System.out.println("Failed to create connection to database.");
        }
        System.out.println("Execution finished.");
    }
}

Atualizar dadosUpdate data

Utilize o código seguinte para alterar os dados com a instrução SQL UPDATE.Use the following code to change the data with an UPDATE SQL statement. Os métodos getConnection(), prepareStatement() e executeUpdate() servem para se ligar à base de dados, preparar e executar a instrução de atualização.The methods getConnection(), prepareStatement(), and executeUpdate() are used to connect to the database, prepare, and run the update statement.

Substitua os parâmetros do sistema anfitrião, da base de dados, do utilizador e da palavra-passe pelos valores que especificou ao criar o seu próprio servidor e base de dados.Replace the host, database, user, and password parameters with the values that you specified when you created your own server and database.

import java.sql.*;
import java.util.Properties;

public class UpdateTable {
    public static void main (String[] args)  throws Exception
    {

        // Initialize connection variables.
        String host = "mydemoserver.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mydemoserver";
        String password = "<server_admin_password>";

        // check that the driver is installed
        try
        {
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException e)
        {
            throw new ClassNotFoundException("PostgreSQL JDBC driver NOT detected in library path.", e);
        }

        System.out.println("PostgreSQL JDBC driver detected in library path.");

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:postgresql://%s/%s", host, database);
            
            // set up the connection properties
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("ssl", "true");

            // get connection
            connection = DriverManager.getConnection(url, properties);
        }
        catch (SQLException e)
        {
            throw new SQLException("Failed to create connection to database.", e);
        }
        if (connection != null) 
        { 
            System.out.println("Successfully created connection to database.");
        
            // Perform some SQL queries over the connection.
            try
            {
                // Modify some data in table.
                int nRowsUpdated = 0;
                PreparedStatement preparedStatement = connection.prepareStatement("UPDATE inventory SET quantity = ? WHERE name = ?;");
                preparedStatement.setInt(1, 200);
                preparedStatement.setString(2, "banana");
                nRowsUpdated += preparedStatement.executeUpdate();
                System.out.println(String.format("Updated %d row(s) of data.", nRowsUpdated));
    
                // NOTE No need to commit all changes to database, as auto-commit is enabled by default.
            }
            catch (SQLException e)
            {
                throw new SQLException("Encountered an error when executing given sql statement.", e);
            }       
        }
        else {
            System.out.println("Failed to create connection to database.");
        }
        System.out.println("Execution finished.");
    }
}

Eliminar dadosDelete data

Utilize o código seguinte para remover os dados com a instrução SQL DELETE.Use the following code to remove data with a DELETE SQL statement. Os métodos getConnection(), prepareStatement() e executeUpdate() servem para se ligar à base de dados, preparar e executar a instrução de eliminação.The methods getConnection(), prepareStatement(), and executeUpdate() are used to connect to the database, prepare, and run the delete statement.

Substitua os parâmetros do sistema anfitrião, da base de dados, do utilizador e da palavra-passe pelos valores que especificou ao criar o seu próprio servidor e base de dados.Replace the host, database, user, and password parameters with the values that you specified when you created your own server and database.

import java.sql.*;
import java.util.Properties;

public class DeleteTable {
    public static void main (String[] args)  throws Exception
    {

        // Initialize connection variables.
        String host = "mydemoserver.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mydemoserver";
        String password = "<server_admin_password>";

        // check that the driver is installed
        try
        {
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException e)
        {
            throw new ClassNotFoundException("PostgreSQL JDBC driver NOT detected in library path.", e);
        }

        System.out.println("PostgreSQL JDBC driver detected in library path.");

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:postgresql://%s/%s", host, database);
            
            // set up the connection properties
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("ssl", "true");

            // get connection
            connection = DriverManager.getConnection(url, properties);
        }
        catch (SQLException e)
        {
            throw new SQLException("Failed to create connection to database.", e);
        }
        if (connection != null) 
        { 
            System.out.println("Successfully created connection to database.");
        
            // Perform some SQL queries over the connection.
            try
            {
                // Delete some data from table.
                int nRowsDeleted = 0;
                PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM inventory WHERE name = ?;");
                preparedStatement.setString(1, "orange");
                nRowsDeleted += preparedStatement.executeUpdate();
                System.out.println(String.format("Deleted %d row(s) of data.", nRowsDeleted));
    
                // NOTE No need to commit all changes to database, as auto-commit is enabled by default.
            }
            catch (SQLException e)
            {
                throw new SQLException("Encountered an error when executing given sql statement.", e);
            }       
        }
        else {
            System.out.println("Failed to create connection to database.");
        }
        System.out.println("Execution finished.");
    }
}

Passos seguintesNext steps