Java -Save the results of select for MySQL into a file No.85 - Database & Big Data & Programming & Investments

In this blog, how to save the results of select for MySQL on Ubuntu into a file is shown.

Contents

▼1. Prerequisites
- 1-1. Install MySQL and create a table with some records
▼2. Save the results of select for MySQL on Ubuntu into a file by Java
▼3. Reference

▼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 a file by Java

2-1. Create an Apache Maven project

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

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

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

2-3. Start Visual Studio Code

cd ./mysqlconnection
$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>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>
</project>

2-5. Save the results of select for MySQL on Ubuntu into a file

Create the java code “mysqlsavetest.java” that uses the database “testdb” created before. user name and password are set in this code.

package org.example.mysqlcon;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;
import java.util.List;
import java.io.FileWriter;
import java.io.IOException;

public class mysqlsavetest{

    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";
    private static final String filename = "/home/xxx/mysqlOutput.csv";

    static {
        log = Logger.getLogger(mysqlcontest.class.getName());
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement stmt = null;

        // Register JDBC driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            log.log(Level.SEVERE, "Where is your MySQL JDBC Driver?", e);
            return;
        }

        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(filename);
                for(String str : list){
                    fw.write(str + System.getProperty("line.separator"));
                }
                fw.close();
            } 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 successfully");
    }
}

2-6. Run the code

Check the output file of this code.

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

▼3. Reference

That’s all. Have a nice day ahead !!!