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 cercare il server creato, ad esempio mypgserver-20170401.From the left-hand menu in Azure portal, click All resources and search for the server you have created, such as mypgserver-20170401.
  3. Fare clic sul nome del server mypgserver-20170401.Click the server name mypgserver-20170401.
  4. Selezionare la pagina Panoramica del server.Select the server's Overview page. Annotare il Nome server e il nome di accesso dell'amministratore del server.Make a note of the Server name and Server admin login name. Database di Azure per PostgreSQL - Accesso dell'amministratore del serverAzure Database for PostgreSQL - Server Admin Login
  5. Se si dimenticano le informazioni di accesso per il server, passare alla pagina Panoramica per visualizzare il nome di accesso dell'amministratore del server e, se necessario, reimpostare la password.If you forget your server login information, navigate to the Overview page to view the Server admin login name and, if necessary, reset the password.

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 = "mypgserver-20170401.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mypgserver-20170401";
        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 = "mypgserver-20170401.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mypgserver-20170401";
        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 = "mypgserver-20170401.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mypgserver-20170401";
        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 = "mypgserver-20170401.postgres.database.azure.com";
        String database = "mypgsqldb";
        String user = "mylogin@mypgserver-20170401";
        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