Cache Lookup Values

This example is designed to provide temporary caching functionality for lookup data that is read from a database. It enables users to store and retrieve frequently accessed lookup data in memory, reducing the need for repeated database queries and improving overall performance.

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.CachedLookup;
import com.northconcepts.datapipeline.transform.lookup.JdbcLookup;
import com.northconcepts.datapipeline.transform.lookup.Lookup;
import com.northconcepts.datapipeline.transform.lookup.LookupTransformer;

public class CacheLookupValues {
    
    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=?");
        
        // cache JdbcLookup        
        lookup = new CachedLookup(lookup);   

        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. CachedLookup instance is created to lazily cache the results of successful lookup performed in the previous step.
7. TransformingReader is a proxy that applies the transformations to records passing through.
8. 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.
9. Data is transferred from the reader to the StreamWriter(System.out) via Job.run() method.
10. The database connection is closed.

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