1. Home
  2. Documentation
  3. Examples
  4. Read from database using ORIGINAL type mapping

Read from database using ORIGINAL type mapping

Updated: Sep 14, 2023
View on GitHub
JDBC

This example shows how to read a database table in Java using ORIGINAL type mapping, which was used by DataPipeline prior to version 8.0. This strategy is similar to OPINIONATED with improvements after analyzing the behavior of several databases and drivers.

You can reference JDBC Type Mapping guide for more details on mapping JDBC/database types to fields in records.

 

Input SQL File

DROP TABLE jewellery IF EXISTS;
CREATE TABLE jewellery (
	title VARCHAR(100),
	published BOOLEAN,
	variant_inventory_qty INTEGER,
	gift_card TINYINT,
	variant_price FLOAT,
	variant_compare_at_price DOUBLE,
	min_price DECIMAL(5,0),
	max_price DECIMAL(30,10),
	height DECIMAL(15,0),
	width DECIMAL(8,0),
	weight DECIMAL(25,0),
	taxable CHAR,
	created_on TIMESTAMP,
	launch_date DATE,
	launch_time TIME
);

INSERT INTO jewellery
(title, published, variant_inventory_qty, gift_card,
variant_price, variant_compare_at_price, min_price, max_price,
height, width, weight,
created_on, launch_date, launch_time)
VALUES
('7 Shakra Bracelet', false, 5, 0,
123.123, 1234.1234, 100, 9765490,
12345, 98765, 12345678901234567890,
'2022-09-25 21:35:13', '2022-09-25', '09:30:17'),

('Anchor Bracelet Mens', true, 10, 1,
987.987, 5678.5678, 300, 1256793,
67890, 456235, 98765432109876543210,
'2022-12-21 21:35:13', '2023-01-12', '09:30:17'),

('Bangle Bracelet', true, 10, 0,
123.123, 1234.1234, 100, 9765490,
556677, 99441166, 11223344556677889900,
'2022-09-25 21:35:13', '2022-09-25', '09:30:17')
;

 

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.internal.jdbc.JdbcFacade;
import com.northconcepts.datapipeline.jdbc.JdbcConnectionFactory;
import com.northconcepts.datapipeline.jdbc.JdbcReader;
import com.northconcepts.datapipeline.jdbc.JdbcValueReader;
import com.northconcepts.datapipeline.job.Job;

public class ReadFromDatabaseUsingOriginalMapping {

    public static void main(String[] args) {
        JdbcConnectionFactory connectionFactory = JdbcConnectionFactory.wrap("org.h2.Driver", "jdbc:h2:mem:ReadFromDatabaseUsingOpinionatedMapping;MODE=MySQL", "sa", "");

        JdbcFacade jdbcFacade = new JdbcFacade(connectionFactory);
        System.out.println("====================================Start: Executing script====================================");
        jdbcFacade.executeFile(new File("example/data/input", "jewellery.sql"));
        System.out.println("====================================End: Executing script====================================");

        JdbcReader reader = new JdbcReader(connectionFactory, "SELECT * FROM jewellery;");
        reader.setValueReader(JdbcValueReader.ORIGINAL);
        Job.run(reader, new StreamWriter(System.out));

    }
}

 

Code Walkthrough

  1. Database connection parameters are specified using JdbcConnectionFactory and JdbcFacade.
  2. The input SQL query is executed using jdbcFacade.executeFile() method.
  3. JdbcReader is created to obtain records from the database table.
  4. JdbcValueReader type is set to ORIGINAL.
  5. Job.run() is used to transfer the data from reader to StreamWriter(System.out). See how to compile and run data pipeline jobs. 

 

Console Output

====================================Start: Executing script====================================
execute file /IdeaProjects/DataPipeline-Examples/example/data/input/jewellery.sql
    execute:  DROP TABLE jewellery IF EXISTS
        - recordsAffected=0
    execute:  CREATE TABLE jewellery ( title VARCHAR(100), published BOOLEAN, variant_inventory_qty INTEGER, gift_card TINYINT, variant_price FLOAT, variant_compare_at_price DOUBLE, min_price DECIMAL(5,0), max_price DECIMAL(30,10), height DECIMAL(15,0), width DECIMAL(8,0), weight DECIMAL(25,0), taxable CHAR, created_on TIMESTAMP, launch_date DATE, launch_time TIME )
        - recordsAffected=0
    execute:  INSERT INTO jewellery (title, published, variant_inventory_qty, gift_card, variant_price, variant_compare_at_price, min_price, max_price, height, width, weight, created_on, launch_date, launch_time) VALUES ('7 Shakra Bracelet', false, 5, 0, 123.123, 1234.1234, 100, 9765490, 12345, 98765, 12345678901234567890, '2022-09-25 21:35:13', '2022-09-25', '09:30:17'), ('Anchor Bracelet Mens', true, 10, 1, 987.987, 5678.5678, 300, 1256793, 67890, 456235, 98765432109876543210, '2022-12-21 21:35:13', '2023-01-12', '09:30:17'), ('Bangle Bracelet', true, 10, 0, 123.123, 1234.1234, 100, 9765490, 556677, 99441166, 11223344556677889900, '2022-09-25 21:35:13', '2022-09-25', '09:30:17') 
        - recordsAffected=3
====================================End: Executing script====================================
23:39:37,118 DEBUG [main] datapipeline:37 - DataPipeline v8.2.0 by North Concepts Inc.
-----------------------------------------------
0 - Record (MODIFIED) {
    0:[TITLE]:STRING=[7 Shakra Bracelet]:String
    1:[PUBLISHED]:BOOLEAN=[false]:Boolean
    2:[VARIANT_INVENTORY_QTY]:INT=[5]:Integer
    3:[GIFT_CARD]:BYTE=[0]:Byte
    4:[VARIANT_PRICE]:DOUBLE=[123.123]:Double
    5:[VARIANT_COMPARE_AT_PRICE]:DOUBLE=[1234.1234]:Double
    6:[MIN_PRICE]:BIG_DECIMAL=[100]:BigDecimal
    7:[MAX_PRICE]:BIG_DECIMAL=[9765490]:BigDecimal
    8:[HEIGHT]:BIG_DECIMAL=[12345]:BigDecimal
    9:[WIDTH]:BIG_DECIMAL=[98765]:BigDecimal
    10:[WEIGHT]:BIG_DECIMAL=[12345678901234567890]:BigDecimal
    11:[TAXABLE]:STRING=[null]
    12:[CREATED_ON]:DATETIME=[2022-09-25 21:35:13.0]:Timestamp
    13:[LAUNCH_DATE]:DATE=[2022-09-25]:Date
    14:[LAUNCH_TIME]:TIME=[09:30:17]:Time
}

-----------------------------------------------
1 - Record (MODIFIED) {
    0:[TITLE]:STRING=[Anchor Bracelet Mens]:String
    1:[PUBLISHED]:BOOLEAN=[true]:Boolean
    2:[VARIANT_INVENTORY_QTY]:INT=[10]:Integer
    3:[GIFT_CARD]:BYTE=[1]:Byte
    4:[VARIANT_PRICE]:DOUBLE=[987.987]:Double
    5:[VARIANT_COMPARE_AT_PRICE]:DOUBLE=[5678.5678]:Double
    6:[MIN_PRICE]:BIG_DECIMAL=[300]:BigDecimal
    7:[MAX_PRICE]:BIG_DECIMAL=[1256793]:BigDecimal
    8:[HEIGHT]:BIG_DECIMAL=[67890]:BigDecimal
    9:[WIDTH]:BIG_DECIMAL=[456235]:BigDecimal
    10:[WEIGHT]:BIG_DECIMAL=[98765432109876543210]:BigDecimal
    11:[TAXABLE]:STRING=[null]
    12:[CREATED_ON]:DATETIME=[2022-12-21 21:35:13.0]:Timestamp
    13:[LAUNCH_DATE]:DATE=[2023-01-12]:Date
    14:[LAUNCH_TIME]:TIME=[09:30:17]:Time
}

-----------------------------------------------
2 - Record (MODIFIED) {
    0:[TITLE]:STRING=[Bangle Bracelet]:String
    1:[PUBLISHED]:BOOLEAN=[true]:Boolean
    2:[VARIANT_INVENTORY_QTY]:INT=[10]:Integer
    3:[GIFT_CARD]:BYTE=[0]:Byte
    4:[VARIANT_PRICE]:DOUBLE=[123.123]:Double
    5:[VARIANT_COMPARE_AT_PRICE]:DOUBLE=[1234.1234]:Double
    6:[MIN_PRICE]:BIG_DECIMAL=[100]:BigDecimal
    7:[MAX_PRICE]:BIG_DECIMAL=[9765490]:BigDecimal
    8:[HEIGHT]:BIG_DECIMAL=[556677]:BigDecimal
    9:[WIDTH]:BIG_DECIMAL=[99441166]:BigDecimal
    10:[WEIGHT]:BIG_DECIMAL=[11223344556677889900]:BigDecimal
    11:[TAXABLE]:STRING=[null]
    12:[CREATED_ON]:DATETIME=[2022-09-25 21:35:13.0]:Timestamp
    13:[LAUNCH_DATE]:DATE=[2022-09-25]:Date
    14:[LAUNCH_TIME]:TIME=[09:30:17]:Time
}

-----------------------------------------------
3 records
Get DataPipeline 8.3.0
All Tags | All Examples
Mobile Analytics