In this blog, how to save the results of select query for MySQL into Azure Blob Storage by Java.
Contents
▼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 |