Read from database using OPINIONATED type mapping
Updated: Sep 14, 2023
This example shows how to read a database table in Java using OPINIONATED type mapping. This strategy maps the types in java.sql.Types along with other properties (like size, decimals, and other metadata). This example can be used when building analytical dashboards, generating reports, or powering your backend processes.
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 ReadFromDatabaseUsingOpinionatedMapping {
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.OPINIONATED);
Job.run(reader, new StreamWriter(System.out));
}
}
Code Walkthrough
- Database connection parameters are specified using JdbcConnectionFactory and JdbcFacade.
- The input SQL query is executed using
jdbcFacade.executeFile()method. - JdbcReader is created to obtain records from the database table.
- JdbcValueReader type is set to
OPINIONATED. It means mapping database types to Java types based only onjava.sql.Types, column display size, and scale. - Job.run() is used to transfer the data from
readertoStreamWriter(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:23:34,014 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]:INT=[100]:Integer
7:[MAX_PRICE]:BIG_DECIMAL=[9765490]:BigDecimal
8:[HEIGHT]:LONG=[12345]:Long
9:[WIDTH]:INT=[98765]:Integer
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]:INT=[300]:Integer
7:[MAX_PRICE]:BIG_DECIMAL=[1256793]:BigDecimal
8:[HEIGHT]:LONG=[67890]:Long
9:[WIDTH]:INT=[456235]:Integer
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]:INT=[100]:Integer
7:[MAX_PRICE]:BIG_DECIMAL=[9765490]:BigDecimal
8:[HEIGHT]:LONG=[556677]:Long
9:[WIDTH]:INT=[99441166]:Integer
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
