Database di Azure per MySQL: usare Java per connettersi ed eseguire query sui datiAzure Database for MySQL: Use Java to connect and query data

Questa guida introduttiva illustra come connettersi a un database di Azure per MySQL usando un'applicazione Java.This quickstart demonstrates how to connect to an Azure Database for MySQL by 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. Questo argomento presuppone che si abbia familiarità con lo sviluppo con Java, ma non con Database di Azure per MySQL.This topic assumes that you are familiar with developing using Java and that you are new to working with Azure Database for MySQL.

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 MySQL Connector/JDownload the JDBC driver MySQL Connector/J
  • Includere il file JAR JDBC (ad esempio, mysql-connector-java-5.1.42-bin.jar) nel percorso di classe dell'applicazione.Include the JDBC jar file (for example mysql-connector-java-5.1.42-bin.jar) into your application classpath. In caso di problemi, vedere la documentazione dell'ambiente relativa alle specifiche per il percorso delle classi, ad esempio Apache Tomcat o Java SEIf you have trouble with this, please consult your environment's documentation for class path specifics, such as Apache Tomcat or Java SE
  • Assicurarsi che la sicurezza della connessione al database di Azure per MySQL sia configurata con il firewall aperto e le impostazioni SSL siano regolate per la corretta connessione dell'applicazione.Ensure your Azure Database for MySQL connection security is configured with the firewall opened and SSL settings adjusted for your application to connect successfully.

Ottenere informazioni di connessioneGet connection information

Ottenere le informazioni di connessione necessarie per connettersi al database di Azure per MySQL.Get the connection information needed to connect to the Azure Database for MySQL. 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 riquadro a sinistra fare clic su Tutte le risorse e cercare il server creato, ad esempio myserver4demo.In the left pane, click All resources, and then search for the server you have created (for example, myserver4demo).
  3. Fare clic sul nome del server.Click the server name.
  4. Selezionare la pagina Proprietà del server e prendere nota dei valori riportati in Nome server e Nome di accesso dell'amministratore server.Select the server's Properties page, and then make a note of the Server name and Server admin login name. Nome del server del database di Azure per MySQLAzure Database for MySQL server name
  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 usando la funzione con un'istruzione SQL INSERT.Use the following code to connect and load the data using the function with an INSERT SQL statement. Il metodo getConnection() viene usato per connettersi a MySQL.The getConnection() method is used to connect to MySQL. I metodi createStatement() ed execute() vengono usati per rilasciare e creare la tabella.Methods createStatement() and execute() are used to 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 per cui inserire i valori.Method executeUpdate() runs the command for each set of parameters to insert the values.

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 = "myserver4demo.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@myserver4demo";
        String password = "<server_admin_password>";

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

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

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mysql://%s/%s", host, database);

            // Set connection properties.
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("useSSL", "true");
            properties.setProperty("verifyServerCertificate", "true");
            properties.setProperty("requireSSL", "false");

            // 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. Il metodo getConnection() viene usato per connettersi a MySQL.The getConnection() method is used to connect to MySQL. I metodi createStatement() ed executeQuery() vengono usati per connettersi ed eseguire l'istruzione select.The methods createStatement() and executeQuery() are used to connect 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 = "myserver4demo.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@myserver4demo";
        String password = "<server_admin_password>";

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

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

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mysql://%s/%s", host, database);

            // Set connection properties.
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("useSSL", "true");
            properties.setProperty("verifyServerCertificate", "true");
            properties.setProperty("requireSSL", "false");

            // 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. Il metodo getConnection() viene usato per connettersi a MySQL.The getConnection() method is used to connect to MySQL. I metodi prepareStatement() ed executeUpdate() vengono usati per preparare ed eseguire l'istruzione update.The methods prepareStatement() and executeUpdate() are used to 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 = "myserver4demo.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@myserver4demo";
        String password = "<server_admin_password>";

        // check that the driver is installed
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException e)
        {
            throw new ClassNotFoundException("MySQL JDBC driver NOT detected in library path.", e);
        }
        System.out.println("MySQL JDBC driver detected in library path.");

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mysql://%s/%s", host, database);

            // set up the connection properties
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("useSSL", "true");
            properties.setProperty("verifyServerCertificate", "true");
            properties.setProperty("requireSSL", "false");

            // 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. Il metodo getConnection() viene usato per connettersi a MySQL.The getConnection() method is used to connect to MySQL. I metodi prepareStatement() ed executeUpdate() vengono usati per preparare ed eseguire l'istruzione update.The methods prepareStatement() and executeUpdate() are used to 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 DeleteTable {
    public static void main (String[] args)  throws Exception
    {
        // Initialize connection variables.
        String host = "myserver4demo.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@myserver4demo";
        String password = "<server_admin_password>";

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

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

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mysql://%s/%s", host, database);

            // set up the connection properties
            Properties properties = new Properties();
            properties.setProperty("user", user);
            properties.setProperty("password", password);
            properties.setProperty("useSSL", "true");
            properties.setProperty("verifyServerCertificate", "true");
            properties.setProperty("requireSSL", "false");

            // 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