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

Read from database using STRICT type mapping

Updated: Sep 3, 2023
View on GitHub
JDBC

This example shows how to read a database table in Java using STRICT type mapping which is defined by Oracle/Sun and implemented as the default by most JDBC drivers. This strategy uses the types in java.sql.Types alone.

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 ReadFromDatabaseUsingStrictMapping {

    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.STRICT);
        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 STRICT. It means mapping database types to Java types based only on java.sql.Types as defined by Sun/Oracle.
  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:49:23,932 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]:INT=[0]:Integer
    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]:INT=[1]:Integer
    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]:INT=[0]:Integer
    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 9.2.0
All Tags | All Examples
Mobile Analytics