Lookup Data in a Database

This example shows you how to look up data from a database using DataPipeline's JdbcLookup and the input file using CSVReader.

Lookup mainly searches for data in another source to merge with each record passing through this lookup's transformer -- the streaming version of a join.

Lookup is similar to how SQL queries work where you have, for example, an id of a product and you would like to get all data that is associated with that id.

Java Code Listing

/*
 * Copyright (c) 2006-2022 North Concepts Inc.  All rights reserved.
 * Proprietary and Confidential.  Use is subject to license terms.
 * 
 * https://northconcepts.com/data-pipeline/licensing/
 */
package com.northconcepts.datapipeline.examples.cookbook;

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

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.FieldList;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.transform.TransformingReader;
import com.northconcepts.datapipeline.transform.lookup.JdbcLookup;
import com.northconcepts.datapipeline.transform.lookup.Lookup;
import com.northconcepts.datapipeline.transform.lookup.LookupTransformer;

public class LookupDataInADatabase {
    
    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 CSVReader(new File("example/data/input/credit-balance-01.csv"))
            .setFieldNamesInFirstRow(true);
        

        /* This lookup matches
         *    [example/data/input/credit-balance-01.csv].[Rating] to [dp_rating].[rating_code]
         * to return    
         *   [dp_rating].[rating_description]
         */ 
        Lookup lookup = new JdbcLookup(connection, 
            "SELECT rating_decription FROM dp_rating WHERE rating_code=?");
        
        reader = new TransformingReader(reader)
                .add(new LookupTransformer(new FieldList("Rating"), lookup));
        
        Job.run(reader, new StreamWriter(System.out));
        
        connection.close();
    }
    
}

Code walkthrough

1. First, database connection parameters are specified.
2. Class.forName() is used to register a database driver. In this example sun.jdbc.odbc.JdbcOdbcDriver is registered.
3. CSVReader is created corresponding to an input file credit-balance-01.csv.
4. setFieldNamesInFirstRow(true) is invoked to specify that the names specified in the first row should be used as field names (disabled by default). If this method is not invoked, the fields would be named A1, A2 by default.
5. JdbcLookup accepts the Connection object and a query to execute. The query filters data by rating_code from the table dp_rating and returns only rating_description column.
6. TransformingReader is a proxy that applies the transformations to records passing through.
7. The lookup matches "credit-balance-01.csv".[Rating] with dp_rating.[rating_code]. The reader contains the all fields in a CSV file along with the  rating_description column of the database table.
8. Data is transferred from the reader to the StreamWriter(System.out) via Job.run() method.

Console Output

Obtained records will be printed on the console. The field names of the output will be similar to the following:

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating,rating_description