빠른 시작: Java를 사용하여 Azure Database for MySQL에서 데이터 연결 및 쿼리Quickstart: Use Java to connect to and query data in Azure Database for MySQL

이 빠른 시작에서는 Java 애플리케이션과 JDBC 드라이버 MariaDB Connector/J를 사용하여 Azure Database for MySQL에 연결합니다.In this quickstart, you connect to an Azure Database for MySQL by using a Java application and the JDBC driver MariaDB Connector/J. 그런 다음, SQL 문을 사용하여 Mac, Ubuntu Linux 및 Windows 플랫폼에서 데이터베이스의 데이터를 쿼리, 삽입, 업데이트 및 삭제합니다.You then use SQL statements to query, insert, update, and delete data in the database from Mac, Ubuntu Linux, and Windows platforms.

이 항목에서는 Java를 사용하여 개발하는 데 익숙하지만 Azure Database for MySQL을 처음 사용한다고 가정합니다.This topic assumes that you're familiar with developing using Java, but you're new to working with Azure Database for MySQL.

사전 요구 사항Prerequisites

중요

연결하려는 IP 주소에 Azure Portal 또는 Azure CLI를 사용하여 서버의 방화벽 규칙이 추가되었는지 확인합니다.Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI

MariaDB 커넥터 가져오기Obtain the MariaDB connector

다음 방법 중 하나를 사용하여 MariaDB Connector/J 커넥터를 가져옵니다.Obtain the MariaDB Connector/J connector using one of the following approaches:

연결 정보 가져오기Get connection information

MySQL용 Azure Database에 연결하는 데 필요한 연결 정보를 가져옵니다.Get the connection information needed to connect to the Azure Database for MySQL. 정규화된 서버 이름 및 로그인 자격 증명이 필요합니다.You need the fully qualified server name and login credentials.

  1. Azure Portal에 로그인합니다.Log in to the Azure portal.
  2. Azure Portal의 왼쪽 메뉴에서 모든 리소스를 선택한 다음, 생성한 서버를 검색합니다(예: 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. 서버 이름을 선택합니다.Select the server name.
  4. 서버의 개요 패널에 있는 서버 이름서버 관리자 로그인 이름을 기록해 둡니다.From the server's Overview panel, make a note of the Server name and Server admin login name. 암호를 잊어버리면 이 패널에서 암호를 재설정할 수 있습니다.If you forget your password, you can also reset the password from this panel. MySQL용 Azure Database 서버 이름Azure Database for MySQL server name

테이블 연결, 생성 및 데이터 삽입Connect, create table, and insert data

INSERT SQL 문이 있는 함수를 사용하여 데이터를 연결하고 로드하려면 다음 코드를 사용하세요.Use the following code to connect and load the data using the function with an INSERT SQL statement. getConnection() 메서드는 MySQL에 연결하는 데 사용됩니다.The getConnection() method is used to connect to MySQL. createStatement() 및 execute() 메서드는 테이블을 삭제하고 생성하는 데 사용됩니다.Methods createStatement() and execute() are used to drop and create the table. prepareStatement 개체는 매개 변수 값을 바인딩하는 setString() 및 setInt()를 사용하여 insert 명령을 작성하는 데 사용됩니다.The prepareStatement object is used to build the insert commands, with setString() and setInt() to bind the parameter values. executeUpdate() 메서드는 각 매개 변수 집합에 값을 삽입하는 명령을 실행합니다.Method executeUpdate() runs the command for each set of parameters to insert the values.

host, database, user 및 password 매개 변수를 서버 및 데이터베이스를 만들 때 지정한 값으로 바꿉니다.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.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@mydemoserver";
        String password = "<server_admin_password>";

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mariadb://%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.");
    }
}

데이터 읽기Read data

SELECT SQL 문을 사용하여 데이터를 읽으려면 다음 코드를 사용하세요.Use the following code to read the data with a SELECT SQL statement. getConnection() 메서드는 MySQL에 연결하는 데 사용됩니다.The getConnection() method is used to connect to MySQL. createStatement() 및 executeQuery() 메서드는 Select 문을 연결하고 실행하는 데 사용됩니다.Methods createStatement() and executeQuery() are used to connect and run the select statement. 결과는 ResultSet 개체를 사용하여 처리됩니다.The results are processed using a ResultSet object.

host, database, user 및 password 매개 변수를 서버 및 데이터베이스를 만들 때 지정한 값으로 바꿉니다.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.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@mydemoserver";
        String password = "<server_admin_password>";

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mariadb://%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.");
    }
}

데이터 업데이트Update data

UPDATE SQL 문을 사용하여 데이터를 변경하려면 다음 코드를 사용하세요.Use the following code to change the data with an UPDATE SQL statement. getConnection() 메서드는 MySQL에 연결하는 데 사용됩니다.The getConnection() method is used to connect to MySQL. prepareStatement() 및 executeUpdate() 메서드는 Update 문을 준비하고 실행하는 데 사용됩니다.The methods prepareStatement() and executeUpdate() are used to prepare and run the update statement.

host, database, user 및 password 매개 변수를 서버 및 데이터베이스를 만들 때 지정한 값으로 바꿉니다.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.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@mydemoserver";
        String password = "<server_admin_password>";

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mariadb://%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.");
    }
}

데이터 삭제Delete data

DELETE SQL 문을 사용하여 데이터를 제거하려면 다음 코드를 사용하세요.Use the following code to remove data with a DELETE SQL statement. getConnection() 메서드는 MySQL에 연결하는 데 사용됩니다.The getConnection() method is used to connect to MySQL. prepareStatement() 및 executeUpdate() 메서드는 delete 문을 준비하고 실행하는 데 사용됩니다.The methods prepareStatement() and executeUpdate() are used to prepare and run the delete statement.

host, database, user 및 password 매개 변수를 서버 및 데이터베이스를 만들 때 지정한 값으로 바꿉니다.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.mysql.database.azure.com";
        String database = "quickstartdb";
        String user = "myadmin@mydemoserver";
        String password = "<server_admin_password>";

        Connection connection = null;

        // Initialize connection object
        try
        {
            String url = String.format("jdbc:mariadb://%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.");
    }
}

다음 단계Next steps