Database di Azure per PostgreSQL: usare Java per connettersi ai dati ed eseguire queryAzure Database for PostgreSQL: Use Java to connect and query data

Questa guida introduttiva illustra come connettersi a un database di Azure per PostgreSQL usando un'applicazione Java.This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using a Java application. Spiega come usare le istruzioni SQL per eseguire query, inserire, aggiornare ed eliminare dati nel database.It shows how to use SQL statements to query, insert, update, and delete data in the database. Le procedure descritte in questo articolo presuppongono che si abbia familiarità con lo sviluppo con Java, ma non con Database di Azure per 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.

prerequisitiPrerequisites

Questa guida introduttiva usa le risorse create in una delle guide seguenti come punto di partenza:This quickstart uses the resources created in either of these guides as a starting point:

È anche necessario:You also need to:

  • Scaricare il driver JDBC PostgreSQL corrispondente alla versione di Java e Java Development Kit.Download the PostgreSQL JDBC Driver matching your version of Java and the Java Development Kit.
  • Includere il file JAR JDBC PostgreSQL (ad esempio postgresql-42.1.1.jar) nel classpath dell'applicazione.Include the PostgreSQL JDBC jar file (for example postgresql-42.1.1.jar) in your application classpath. Per altre informazioni, vedere i dettagli sul classpath.For more information, see classpath details.

Ottenere informazioni di connessioneGet connection information

Ottenere le informazioni di connessione necessarie per connettersi al database di Azure per PostgreSQL.Get the connection information needed to connect to the Azure Database for PostgreSQL. Sono necessari il nome del server completo e le credenziali di accesso.You need the fully qualified server name and login credentials.

  1. Accedere al Portale di Azure.Log in to the Azure portal.
  2. Nel menu a sinistra nel portale di Azure fare clic su Tutte le risorse e quindi cercare il server creato, ad esempio mydemoserver.From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
  3. Fare clic sul nome del server.Click the server name.
  4. Nel pannello Panoramica del server prendere nota dei valori riportati in Nome server e Nome di accesso dell'amministratore server.From the server's Overview panel, make a note of the Server name and Server admin login name. Se si dimentica la password, in questo pannello è anche possibile reimpostarla.If you forget your password, you can also reset the password from this panel. Nome del server Database di Azure per PostgreSQLAzure Database for PostgreSQL server name

Connettersi, creare tabelle e inserire datiConnect, create table, and insert data

Usare il codice seguente per connettersi e caricare i dati nel database usando la funzione con un'istruzione SQL INSERT.Use the following code to connect and load the data into the database using the function with an INSERT SQL statement. I metodi getConnection(), createStatement() ed executeQuery() vengono usati per connettersi al database ed eliminare e creare la tabella.The methods getConnection(), createStatement(), and executeQuery() are used to connect to the database, drop, and create the table. L'oggetto prepareStatement viene usato per compilare i comandi di inserimento, con setString() e setInt() per associare i valori dei parametri.The prepareStatement object is used to build the insert commands, with setString() and setInt() to bind the parameter values. Il metodo executeUpdate() esegue il comando per ogni set di parametri.Method executeUpdate() runs the command for each set of parameters.

Sostituire i parametri host, database, user e password con i valori specificati al momento della creazione del server e del database.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.");
    }
}

Leggere i datiRead data

Usare il codice seguente per leggere i dati con un'istruzione SQL SELECT.Use the following code to read the data with a SELECT SQL statement. I metodi getConnection(), createStatement() ed executeQuery() vengono usati per connettersi al database, creare ed eseguire l'istruzione select.The methods getConnection(), createStatement(), and executeQuery() are used to connect to the database, create, and run the select statement. I risultati vengono elaborati usando un oggetto ResultSet.The results are processed using a ResultSet object.

Sostituire i parametri host, database, user e password con i valori specificati al momento della creazione del server e del database.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.");
    }
}

Aggiornare i datiUpdate data

Usare il codice seguente per modificare i dati con un'istruzione SQL UPDATE.Use the following code to change the data with an UPDATE SQL statement. I metodigetConnection(), prepareStatement() ed executeUpdate() vengono usati per connettersi al database, preparare ed eseguire l'istruzione update.The methods getConnection(), prepareStatement(), and executeUpdate() are used to connect to the database, prepare, and run the update statement.

Sostituire i parametri host, database, user e password con i valori specificati al momento della creazione del server e del database.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.");
    }
}

Eliminare i datiDelete data

Usare il codice seguente per rimuovere i dati con un'istruzione SQL DELETE.Use the following code to remove data with a DELETE SQL statement. I metodigetConnection(), prepareStatement() ed executeUpdate() vengono usati per connettersi al database, preparare ed eseguire l'istruzione delete.The methods getConnection(), prepareStatement(), and executeUpdate() are used to connect to the database, prepare, and run the delete statement.

Sostituire i parametri host, database, user e password con i valori specificati al momento della creazione del server e del database.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.");
    }
}

Passaggi successiviNext steps