Lookup Data in a Database
Updated: Jun 8, 2023
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
- First, database connection parameters are specified.
Class.forName()
is used to register a database driver. In this examplesun.jdbc.odbc.JdbcOdbcDriver
is registered.- CSVReader is created corresponding to an input file
credit-balance-01.csv
. 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.- JdbcLookup accepts the Connection object and a query to execute. The query filters data by
rating_code
from the tabledp_rating
and returns onlyrating_description
column. - TransformingReader is a proxy that applies the transformations to records passing through.
- 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. - Data is transferred from the
reader
to theStreamWriter(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