Export data from MySQL to CSV file

Export Data from MySQL using Hikari Datasource

Objective

Exporting data from a MySQL table to a CSV file can also be done programmatically using Java and a JDBC driver for MySQL such as the Hikari Datasource. In this blog post, I will show you how to use Hikari Datasource and Java to export data from a MySQL table to a CSV file.

Step 1: Set up Hikari Datasource

The first step is to set up Hikari Datasource in your Java project. You can add the HikariCP dependency to your project's build file or use your preferred build automation tool like Gradle or Maven. After that, you can create a HikariConfig object and set the necessary properties for connecting to your MySQL server, such as the server URL, username, and password.

Here's an example of setting up a Hikari Datasource object:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
config.setUsername("myusername");

config.setPassword("mypassword");

HikariDataSource dataSource = new HikariDataSource(config);

Step 2: Connect to the MySQL database

Once you have set up the Hikari Datasource, you can use it to connect to your MySQL database using the getConnection() method.

Connection connection = dataSource.getConnection();

Step 3: Execute SQL Query

After establishing the database connection, you can execute the SQL query to select the data from the table you want to export.

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery("SELECT * FROM mytable");

Step 4: Write the data to a CSV file

Now that you have the ResultSet object containing the selected data, you can write it to a CSV file using a FileWriter and a CSVWriter. You can iterate through the ResultSet object and write each row to the CSV file.

File file = new File("mytable.csv");

FileWriter outputfile = new FileWriter(file);

CSVWriter writer = new CSVWriter(outputfile);


// Write the header row

String[] header = {"column1", "column2", "column3"};

writer.writeNext(header);


// Write the data rows

while (resultSet.next()) {

 String[] data = {resultSet.getString("column1"), resultSet.getString("column2"), resultSet.getString("column3")};

 writer.writeNext(data);

}

 writer.close();

Step 5: Close the database connection

Finally, you should close the database connection and release any resources used by the Hikari Datasource.

resultSet.close();

statement.close();

connection.close();

dataSource.close();

Conclusion

Exporting data from a MySQL table to a CSV file using Java and Hikari Datasource is a straightforward process. By following the steps outlined in this blog post, you can easily export data from a MySQL table to a CSV file programmatically, which can be useful in situations where you need to automate the process of exporting data.