Back to JDBC Hub

Nearby lessons

25 of 37
JDBC - Connection PoolingJDBC - PropertiesJDBC - ExcelJDBC - MySQLJDBC - Create Database

JDBC - Working with Excel Sheets

📌 What You Will Learn
  • What cursors are and the two types
  • SYS_REFCURSOR — returning a ResultSet from a procedure
  • Stored functions vs stored procedures
  • Working with Excel files (modern approach)

Working with Excel Sheets is a fundamental part of database programming with JDBC. This lesson explains JDBC with Excel Sheets (Modern Approach) with complete, runnable code examples, clear step-by-step explanations, and common mistakes to avoid with exam-style MCQs at the end.

JDBC with Excel Sheets (Modern Approach)

The classic material used the Type-1 (JDBC-ODBC) driver with a DSN to read Excel — but that driver is removed from JDK 8, so that approach no longer works. The modern, professional way to read/write Excel in Java is the Apache POI library.

Key idea: an Excel workbook has sheets, each sheet has rows, each row has cells — just like a database table.

Trainer's Note: Trainer tip: Excel and JDBC were once connected through the removed ODBC bridge. Today, if you need to import Excel data into a database, the common pattern is: read Excel with Apache POI → insert into the database with PreparedStatement. If your project uses Spring, Apache POI + Spring Data makes this very clean.
Example01
JCode Cell
1import org.apache.poi.ss.usermodel.*;
2import org.apache.poi.xssf.usermodel.XSSFWorkbook;
3import java.io.*;
4 
5public class ExcelDemo {
6 public static void main(String[] args) throws Exception {
7 // READ an Excel file
8 FileInputStream fis = new FileInputStream("employees.xlsx");
9 Workbook wb = new XSSFWorkbook(fis);
10 Sheet sheet = wb.getSheet("Sheet1"); // each sheet is like a table
11 
12 for (Row row : sheet) { // each row is like a record
13 System.out.println(
14 row.getCell(0).getStringCellValue() + " " + // column 1
15 row.getCell(1).getStringCellValue()); // column 2
16 }
17 wb.close();
18 }
19}
📝 Key Takeaways
  • Cursor = database object to walk through SQL results row by row.
  • Implicit cursors are auto-created (%ROWCOUNT, %FOUND); explicit ones are created by the developer (SYS_REFCURSOR).
  • SYS_REFCURSOR lets a procedure return a whole ResultSet; register it with OracleTypes.CURSOR.
  • Function returns one value and can be used inside SQL; procedure performs actions.
  • The old Excel-via-ODBC approach is gone; use Apache POI to read/write Excel today.

🧠 Test Your Knowledge

4 Questions
Progress: 0 / 4