The story of throwing BLOB data from EXCEL in DBUnit

This is the 10th day article of Java Advent Calendar 2016 ¥-Qiita. The article on the 9th day was deaf_tadashi's About JMH, a microbenchmark tool. The 11th day is @ leak4mk0.

About this page

When you want to put test data (mainly images or PDF) in BLOB type columns using DBUnit There is a mechanism to specify the file in XML.

However, this time, I would like to introduce how to put test data from EXCEL into a BLOB type column.

If you have never used DBUnit, there is a lot of Japanese information on how to use it, so Please go around.

Purpose of writing

I stumbled upon importing BLOB column data, so it's a share. I think it's a highly rare situation, You can go this way! I would appreciate it if you could know that.

Dependencies

environment

OS : Redhat 6.5 Java : 1.8.20 DB : Oracle 12c

Library

DBUnit : 2.5.3 OJDBC : 8

The beginning of that

In a non-idempotent unit test code execution environment (data is in the DB, but the data can be easily changed by human hands). In the development where if the test code fails, you will be asked to find the cause and fix it. This introduces Ikan and CI, and for test code that has data in the DB, I can not fix it anymore, so Let's migrate the data so that it can be imported by DBUnit so that the data can be restored from the IDE at any time! Measures.

By doing the above, it is possible to prevent the test from being broken except by modifying the software, and it does not take extra man-hours. Introduced by pushing hard on the point and the quality merit obtained by doing CI.

Main story

Write the migration data to Excel used for data import in DBUnit

It was done using the brute force of copying from SQL Developer. BLOB type column data is BASE64 encoded with a SELECT statement. Throw a stored function and copy and paste.

Write the test data written in Excel to DB from EXCEL with DBUnit (main subject)

The problem that occurred

When you execute DatabaseOperation # execute of DBUnit, ClassCastException occurs at the write location of the BLOB column and the process fails.

Cause

On the OJDBC side, simply "If I wanted to write data to a BLOB column, I would use a BlobObject, You can't cast because the data you're pouring in is bytes! " It seems that.

Countermeasures

Aside from reading, when I write a BLOB myself, I don't use a BlobObject, but I think I'm forced to write it in bytes. I need to change the DataType of org.dbunit.dataset.Column to BINARY, so I created the following code.

java code (excerpt)



    IDataSet databaseDataSet = con.createDataSet();
    ITableMetaData tableMetaData = databaseDataSet.getTableMetaData(tableName);

    //OraclePreparedStatement tries to put a BLOB object in a BLOB column,
    //I want to set byte data, so convert it to BINARY
    //As a caveat, ITableMetaData#columns and IDataSet#ITableMetaData#With columns
    //Both need to be updated to BYNARY due to dual management
    ITable table = dataset.getTable(tableName);
    Column[] cols = tableMetaData.getColumns();
    for(int i = 0; i < cols.length; i++){
        //Replace the BLOB guy with a BINARY
        if(DataType.BLOB.equals(cols[i].getDataType())){
            Column col = new Column(cols[i].getColumnName(), DataType.BINARY);
            //Since the elements of the array reference relationship can be freely rewritten, this is used to achieve the purpose.
            cols[i] = col;
            int idx = table.getTableMetaData().getColumnIndex(cols[i].getColumnName());
            table.getTableMetaData().getColumns()[idx] = col;
        }
    }

    //Data flow
    DatabaseOperation.CLEAN_INSERT.execute(con, dataset);

As you can see in the comment, it is realized by forcibly rewriting the object in the reference state.

You have now successfully written BLOB column data to your DB using DBUnit. If you are in the same situation, please do!

Extra

Why wasn't it possible to link resource files with XML?

Because the BLOB data was a Java instance created on the system side (not a PDF or image file, but the progress of business processing) I took this approach. Don't dig into why this is happening.

Also, since there are many opportunities to simply use Excel like a DB, This is because it is more convenient for learning if the developer can define the data in Excel.

Summary

If you use DBUnit to drop data into a BLOB column via JDBC, Set the DataType of the columns field of ITableMetaData and IDataSet # ITableMetaData to BINARY and it's OK! We have delivered a great technique for everyone.

Recommended Posts

The story of throwing BLOB data from EXCEL in DBUnit
The story of RxJava suffering from NoSuchElementException
The story of AppClip support in Anyca
The story of writing Java in Emacs
The story of acquiring Java Silver in two months from completely inexperienced.
[Order method] Set the order of data in Rails
The story of low-level string comparison in Java
The story of making ordinary Othello in Java
The story of learning Java in the first programming
The story of migrating from Paperclip to Active Storage
The story of an Illegal State Exception in Jetty.
The story of raising Spring Boot from 1.5 series to 2.1 series part2
Importing Excel data in Java 2
Import Excel data in Java
Importing Excel data in Java 3
The story that Tomcat suffered from a timeout error in Eclipse
Story of putting ubuntu in HDD from complete ignorance and yolov3
[Swift] Determine the constellation from the date of birth entered in the UIDatePicker
[Java version] The story of serialization
The story of @ViewScoped consuming memory
Filter the fluctuations of raw data
Order of processing in the program
The story of forgetting to close a file in Java and failing
[Payment of work] Summarize the mistakes made in 2020 [2nd year from inexperience]
The story of switching from Amazon RDS for MySQL to Amazon Aurora Serverless
Confirmation and refactoring of the flow from request to controller in [httpclient]
How to change the maximum and maximum number of POST data in Spark
[Git] The horrifying story of deleting the master branch ~ The answer is in English ~
Get the result of POST in Java
[Challenge CircleCI from 0] Learn the basics of CircleCI
The identity of params [: id] in rails
The story of updating SonarQube's Docker Container
Rails refactoring story learned in the field
Ruby from the perspective of other languages
What is the data structure of ActionText?
The contents of the data saved by CarrierWave.
Find the difference from a multiple of 10
Write the movement of Rakefile in the runbook
The story of intentionally using try catch for the first time in my life
Try design patterns in C language! Memento pattern-Let's memorize the memories of the data
How to make a unique combination of data in the rails intermediate table