Java –Save records for MySQL into Azure Blob Storage No.86 

In this blog, how to save the results of select query for MySQL into Azure Blob Storage by Java.

▼1. Prerequisites

1-1. Install MySQL and create a table with some records

Java – Connect to MySQL on Ubuntu and run select query No.80

# Create a table with some records
mysql> CREATE DATABASE testdb;
mysql> USE testdb;
mysql> create table testtbl (c1 int, c2 int);
mysql> insert testtbl values(1,1),(2,2),(3,3);
mysql> select * from testtbl;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

▼2. Save the results of select for MySQL on Ubuntu into Azure Blob Storage by Java

2-1. Create an Apache Maven project

mvn archetype:generate -DinteractiveMode=false -DgroupId=org.example.mysqlcon -DartifactId=mysqlblob -DarchetypeArtiFactId=maven-archetype-quickstart

2-2. Remove the existed both App.java and AppTest.java

rm ./mysqlblob/src/main/java/org/example/mysqlcon/App.java 
rm ./mysqlblob/src/test/java/org/example/mysqlcon/AppTest.java 

2-3. Start Visual Studio Code

cd ./mysqlblob
$code .

2-4. Update pom.xml as below and save it (Ctrl+S)

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.30</version>
    </dependency>
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>11.2.1.jre8</version>
    </dependency>
    <dependency>
      <groupId>com.azure</groupId>
      <artifactId>azure-storage-blob</artifactId>
      <version>12.13.0</version>
    </dependency>  
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

2-5. Save the results of select for MySQL into Azure Blob Storage

Create the java code “copymysqltoblob.java” that uses the database “testdb” created before. user name and password are set in this code. the connection string of Azure Blob Storage is set to “connectStr” parameter.

package org.example.mysqlcon;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.io.FileWriter;
import java.io.IOException;
import com.azure.storage.blob.*;

public class copymysqltoblob {
    private static final Logger log;
    private static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USER = "hiveuser";
    private static final String PASSWORD = "hivepassword";
    
    static {
        log = Logger.getLogger(mysqlcontest.class.getName());
    }

    // select data from SQL Server
    public static void main(String[] args) throws SQLException {
        String connectStr = "YourConnectionStringOfAzureBlobStorage";
        String containerName = "files";
        String fileNameOut = "MysqlOutput.csv";
        String localPath = "/home/xxx/temp/";
        String filenamecsv = localPath + fileNameOut;

        // Get a reference to a container
        BlobServiceClient BlobServiceClient = new BlobServiceClientBuilder().connectionString(connectStr).buildClient();
        BlobContainerClient containerClinet = BlobServiceClient.getBlobContainerClient(containerName);
        Connection conn = null;
        Statement stmt = null;

        try {
            // Register JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // pen a connection
            log.log(Level.INFO, "Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

            // Execute a query
            log.log(Level.INFO, "Creating statement...");
            stmt = conn.createStatement();
            String sql = "SELECT * FROM testtbl";
            ResultSet rs = stmt.executeQuery(sql);

            List<String> list = new ArrayList<String>();
            while (rs.next()) {
                String id = rs.getString("c1");
                String num = rs.getString("c2");
                String str = id + "," + num;
                list.add(str);
            }
            rs.close();

            // save list to file
            try {
                FileWriter fw = new FileWriter(filenamecsv);
                for (String str : list) {
                    fw.write(str + System.getProperty("line.separator"));
                }
                fw.close();

                // Upload a file to the blob
                BlobClient blobClient = containerClinet.getBlobClient(fileNameOut);
                System.out.println("Uploading to Azure Blob storage as blob:\n\t" + blobClient.getBlobUrl());
                blobClient.uploadFromFile(localPath + "/" + fileNameOut);

            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            } // nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        log.log(Level.INFO, "Operation done.");
    }
}

2-6. Run the code

This is a stdout for this code.

Nov 27, 2022 4:12:57 PM org.example.mysqlcon.copymysqltoblob main
INFO: Connecting to database...
Nov 27, 2022 4:12:58 PM org.example.mysqlcon.copymysqltoblob main
INFO: Creating statement...
Uploading to Azure Blob storage as blob:
        https://xxx.blob.core.windows.net/files/MysqlOutput.csv
Nov 27, 2022 4:12:59 PM org.example.mysqlcon.copymysqltoblob main
INFO: Operation done.

Check the output file of this code.

$ cat MysqlOutput.csv
1,1
2,2
3,3

▼3. Reference

  1. MySQL  https://dev.mysql.com/doc/refman/8.0/en/what-is-mysql.html
  2. Java – Connect to MySQL on Ubuntu and run select query No.80
  3. Java -Save the results of select for MySQL into a file No.85

Leave a Reply

Your email address will not be published. Required fields are marked *