Read from a Database

Updated: Feb 21, 2022

View on GitHub

This example shows you how to read a database table in Java using the JdbcReader class.

This example can easily be modified to show how to write to a database.

Database Input

The dp_credit_balance database table is used as input.

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.sql.Connection;
import java.sql.Driver;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.northconcepts.datapipeline.core.DataEndpoint;
import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.Record;
import com.northconcepts.datapipeline.jdbc.JdbcReader;

public class ReadFromADatabase {

    public static final Logger log = DataEndpoint.log; 

    public static void main(String[] args) throws Throwable {
        // connect to the database
        Driver driver = (Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        Properties properties = new Properties();
        properties.put("user", "scott");
        properties.put("password", "tiger");
        Connection connection = driver.connect("jdbc:odbc:dp-cookbook", properties);

        DataReader reader = new JdbcReader(connection, "SELECT * FROM dp_credit_balance")
            .setAutoCloseConnection(true);
        reader.open();
        try {
            Record record;
            while ((record = reader.read()) != null) {
                log.info(record);
            }
        } finally {
            reader.close();
        }
    }
}

Code Walkthrough

1. A JDBC Driver is created and a Connection is obtained to the database from which the data is to be read, this is standard JDBC code.
2. A JdbcReader is created using the using the Connection object and the query string to query the database i.e. SELECT * FROM dp_credit_balance.
3. The JdbcReader.setAutoCloseConnection method is invoked to automatically close the Connection.
4. The reader is opened via reader.open method.
5. A while loop iterates through the input data.
6. Each record is read as a Record object and printed to the console via the Datapipeline logger.
7. After the while loop completes, the reader.close method is invoked in a finally block to close the JdbcReader.

JdbcReader

JdbcReader is an input reader that is used to read from a database. It is created using Connection object, query string and/or other object parameters. It is a sub-class of DataReader and overrides the open, close, read among other methods in order to handle database access. An important method is the setAutoCloseConnection which when invoked with a true value instructs the JdbcReader to close the Connection when its close method is invoked.

Console Output

The output of this code is the contents of the dp_credit_balance table displayed on the standard console.