Export Oracle table to CSV file in Java

In this post, we will see how to export table data from Oracle to a comma-separated file(CSV).

The connection to Oracle from Java can be established using the Oracle JDBC(Java Database Connectivity)

The JDBC connector will be different for each RDBMS. The JDBC connector is in the form of a JAR that can be imported to the project. In this project, we have used jdbc-oracle.jar

Export Oracle table to CSV

package com.oracle.executor;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class oracle_executor_client {

	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String serverName = "serverName";
			String portNumber = "port";
			String sid = "YOur_Connection_Sid";
			String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + "/" + sid;
			String username = "username";
			String password = "password";
			System.out.println("Connection URL: " + url);

			Connection conn = DriverManager.getConnection(url, username, password);
			if(conn != null)
			{
				System.out.println("Connection established");
			}
			
			String query = "select * from dual";
			System.out.println("Query: " + query);
			PreparedStatement stmt = conn.prepareStatement(query);
			ResultSet rs = stmt.executeQuery();
			String outputFile = "C:\\OracleOutput\\OracleResult.csv"
			resultToCsv(rs, outputFile);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}

	public static void resultToCsv(ResultSet rs, String outputFile) throws SQLException, FileNotFoundException {
		
		PrintWriter csvWriter = new PrintWriter(new File(outputFile));
		ResultSetMetaData meta = rs.getMetaData();
		int numberOfColumns = meta.getColumnCount();
		String dataHeaders = "\"" + meta.getColumnName(1) + "\"";
		for (int i = 2; i < numberOfColumns + 1; i++) {
			dataHeaders += ",\"" + meta.getColumnName(i).replaceAll("\"", "\\\"") + "\"";
		}
		csvWriter.println(dataHeaders);
		while (rs.next()) {
			String row = "\"" + rs.getString(1).trim().replaceAll("\"", "\\\"") + "\"";
			for (int i = 2; i < numberOfColumns + 1; i++) {
				row += ",\"" + rs.getString(i).trim().replaceAll("\"", "\\\"") + "\"";
			}
			csvWriter.println(row);
		}
		csvWriter.close();
	}

}

Let us decode the above code into pieces.

Connection conn = DriverManager.getConnection(url, username, password);

The main classes used in the above code for querying and fetching the data are,

PreparedStatement – This class is used to pass the query to the connection object

ResultSetMetaData – This class is used to get the Meta Information about the ResultSet. The main usage of this class is to fetch the column names from the ResultSet

ResultSet – This is the object type that will be returned when we run the query

To focus on the export table part, check the method “resultToCsv” in the above code.

Also check “Parsing SQL Select query using JSQL parser in Java“, “Parsing SQL Insert query using JSQL parser in Java“