A database is a persistent collection of data and information which is organized in a particular manner for quick access similarly spreadsheets are another way to store data in tabular form. There are two types of databases of which structured database particularly MySQL database is illustrated here and LibreOffice Calc as the spreadsheet tool. Insertion of data from MySQL to spreadsheet is very useful as it brings ease in viewing and retrieving of data to everyone who is not familiar with SQL. Working with spreadsheets using java requires the use of APACHE POI. Apache Poi is the Java API for working with Microsoft documents. Download the below-mentioned jar files related to apache which will help us to work with spreadsheets. Similarly, JDBC is the Java API used for connecting java to MySQL. Download the mysql-connector-java-5.1.6-bin.jar file from the link given below.
Given the following table structure, all the data present in this table should be stored in the spreadsheet with table attributes as the cell names of the spreadsheet.
- Open a new connection to the database using the database login credentials. This is done using the getConnection() method. Each object to the Connection class represents a new connection to the database.
- Execute the SQL query and store the result.
- Create a new workbook -> sheet -> row.
- Create the new cells corresponding to each column in the database table.
- Iterate through the stored resultset and store the values in the corresponding column.
Syntax, Parameters, and Return Type of Inbuilt functions used:
1. Connection getConnection(String URL, String username, String Password); 2. Statement createStatement(); 3. ResultSet executeQuery(String sql_query); 4. XSSFSheet createSheet(String sheet_name); 5. XSSFRow createRow(int row_no); 6. XSSFCell createCell(int cell_no); 7. void setCellValue(String cell_name); 8. bool next(); 9. void write(FileOutputStream output);
Output: Internal output: SQL table generated in the terminal(CMD for Windows) reflecting changes made in the database created which are illustrated.
External output: This will be an Excel file as per the local directory mentioned in the java program.
Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more, please refer Complete Interview Preparation Course.