Convert Excel to Blob with java, save it, read it from DB and output it as a file!

When downloading a file with a web application ... The first thing that comes to mind is to set up a file server, but this method is also ... (I don't care about the size.)

** 1. Read the file **

main


//File reading
File uploadFile = new File("[directory]\\tmp.xlsx");
FileInputStream finstream = new FileInputStream(uploadFile);

Very simple.

** 2. Keep in DB **

main


Class.forName("oracle.jdbc.driver.OracleDriver");
//Connect to Oracle8i
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "system", "password");
//Create statement
Statement stmt = conn.createStatement();
//SQL definition
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO [Schema].test_table(file_id, deta) VALUES(0, ?)");
//Bind to statement
pstmt.setBinaryStream(1, finstream, (int) uploadFile.length());
//Run
int result = pstmt.executeUpdate();

Binary with setBinaryStream and set.

** 3. Extract binary data from DB **

main


//Binary data acquisition
ResultSet rset = stmt.executeQuery("select deta from [Schema].upload_file where file_id = '0'");
//Get Blob data and output to a file
Blob blob = null;
while (rset.next()) {
	blob = rset.getBlob("deta");
}
byte[] buffer = blob.getBytes(1, (int) blob.length());

Store in byte array with getBytes

** 4. Spit to file **

main


//Specify output file
File file = new File("[directory]\\test001_output.xlsx");
DataOutputStream dos = new DataOutputStream(new FileOutputStream(file));
//write byte array
dos.write(buffer);
dos.close();

Simply generate and write a file.

** 5. Routine closing process **

main


//Close result set
rset.close();
//Close statement
stmt.close();
//Close connection
conn.close();

//tmp file deletion
uploadFile.delete();

Various closes. And delete the uploaded temp file. A file is created in the specified directory.

** 6. Challenge: man_tone1: **

Last time I generated Excel using POI, I want to binaryize the file generation process and keep it in the DB with the same flow ... (I spit it out as a temp file once, read it again and make it binary)

I can't think of it for a moment, so let's try it next time!

Recommended Posts

Convert Excel to Blob with java, save it, read it from DB and output it as a file!
How to record JFR (Java Flight Recorder) and output a dump file
I want to download a file on the Internet using Ruby and save it locally (with caution)
Read a string in a PDF file with Java
[Java] Convert and import file values with OpenCSV
How to ZIP a JAVA CSV file and manage it in a Byte array
Convert Java enum enums and JSON to and from Jackson
How to jump from Eclipse Java to a SQL file
I tried to read and output CSV with Outsystems
[Spring] Read a message from a YAML file with MessageSource
I called YouTube video from DB with haml and tried to embed and display it
How to save a file with the specified extension under the directory specified in Java to the list
How to convert A to a and a to A using AND and OR in Java
How to convert a file to a byte array in Java
21 Load the script from a file and execute it
Gzip-compress byte array in Java and output to file
How to read a library from a JAR file with VS Code << How to not use Maven / Gradle >>
How to request a CSV file as JSON with jMeter
I want to make a list with kotlin and java!
[Java] How to convert a character string from String type to byte type
I want to make a function with kotlin and java!
Even in Java, I want to output true with a == 1 && a == 2 && a == 3
Connect to DB with Java
Read the data of Shizuoka point cloud DB with Java and try to detect the tree height.
Convert line feed code to html line feed tag with Thymeleaf and output
Read the first 4 bytes of the Java class file and output CAFEBABE
How to open a script file from Ubuntu with VS code
Sample to read and write LibreOffice Calc fods file in Java 2021
[Java] How to convert from String to Path type and get the path
Introduction to Scala from a Java perspective, decompiled and understood (basic)
Read barometric pressure and temperature with Java from Raspberry Pi 3 & BMP180
Run a batch file from Java
When calling sshpass from Java with shell etc., it seems that it is necessary to have a path.
Log output to file in Java
Output PDF and TIFF with Java 8
EXCEL file update sample with JAVA
[Java] Output the result of ffprobe -show_streams in JSON and map it to an object with Jackson
[Java] Get Json from URL and handle it with standard API (javax.script)
The story of forgetting to close a file in Java and failing
Introduction to Scala from a Java perspective (Class-Tuple edition) to decompile and understand
Even in Java, I want to output true with a == 1 && a == 2 && a == 3 (PowerMockito edition)
[Java] Create a jar file with both compressed and uncompressed with the jar command
Create a program to post to Slack with GO and make it a container
I wrote a Lambda function in Java and deployed it with SAM
How to make an app with a plugin mechanism [C # and Java]
<Java> Quiz to batch convert file names separated by a specific character string with a part of the file name
Convert from java UTC time to JST time
[Java] How to output and write files!
Read xlsx file in Java with Selenium
Convert JSON to TSV and TSV to JSON with Ruby
[Rails] How to read the XML file uploaded from the screen with Hash type
Created a native extension of Ruby with Rust and published it as a gem
Java: Download the file and save it in the location selected in the dialog [Use HttpClient]
Even in Java, I want to output true with a == 1 && a == 2 && a == 3 (Javassist second decoction)
I want to be able to read a file using refile with administrate [rails6]
It took a month from application to taking the Oracle Java SE 11 Silver
Memo: [Java] Process the read csv (extract, change according to the conditions) and output
How to convert a value of a different type and assign it to another variable
I tried to automatically generate a class to convert from a data class to a Bundle with APT
How to create an Excel form using a template file with Spring MVC
Even in Java, I want to output true with a == 1 && a == 2 && a == 3 (black magic edition)