Lookup Data in a Database

This example shows you how to perform data lookups, by retrieving information from a database and combining it with data from a file. It provides a powerful capability to enrich and enhance datasets by integrating data from external sources, enabling comprehensive analysis and data-driven decision-making.

In CRM systems, the example can be used to enrich customer data by getting additional information from a database. This can include customer preferences, purchase history, or demographic data, enabling personalized marketing campaigns, customer segmentation, or targeted sales efforts.

Java Code Listing

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