- Exporting data from one system to another is a very common use case in companies.
- In this article, we will build a logic to export data from an SQL database and write it to an Excel file that might be useful for some other teams like sales/ marketing for their operation.
- We will create a NEWS_ARTICLES table that contains all the data of news.
CREATE TABLE `NEWS_ARTICLES` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(300) NOT NULL, `link` varchar(300) DEFAULT NULL, `image_url` varchar(300) DEFAULT NULL, `category` varchar(1000) DEFAULT NULL, `published_date` datetime NOT NULL, `create_timestamp` datetime NOT NULL, `update_timestamp` datetime NOT NULL, `provider` varchar(100) NOT NULL, `manual_tags` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8375 DEFAULT CHARSET=latin1;
- At first, we are executing SQL Query on our input table NEWS_ARTICLE.
This execution will return Resultset object.
- Once We have ResultSet we will call writeToSheet method whose responsibility is to read ResultSet and write to an excel sheet.
- After that, we write created a sheet to output the file.
- getNewsArticle method takes sqlQuery as a parameter, this defines the query that we would like to execute on the table.
- We create a JDBC Connection object, then create a Statement object, and finally, executeQuery will take the query as an argument.
- executeQuery method returns ResultSet which contains the result of the query that was executed against the table.
Write to Excel Sheet
- We will use a third-party library called Apache POI to operate on an Excel file.
- In our writeToSheet method, we first create a sheet object and then iterate over Resultset and createRow for the sheet.
- We are interested in the title of the article and the link to the article. We are also tracking row numbers so that we write different articles for different rows.
- Now once our sheet is ready and all the content are written then we can finally write the sheet to output file.
- When we run our client code we can check if the file and sheet is created or not.
- The project is available on GitHub.
- In this article, we develop a program to export the data from MySQL database and write to excel sheet using Apache POI library.
- If you want to upskill your Java, you should definitely check out this bestseller course