Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How do we insert/store a file into MySQL database using JDBC?
In general, the contents of a file are stored under Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) datatype in MySQL database.
JDBC provides support for the Clob datatype, to store the contents of a file in to a table in a database.
The setCharacterStream() method of the PreparedStatement interface accepts an integer representing the index of the parameter and, a Reader object as a parameter.
And sets the contents of the given reader object (file) as value to the parameter (place holder) in the specified index.
Whenever you need to send very large text value you can use this method.
Storing text file using JDBC:
If you need to store a file in a database using JDBC program create table with a Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) datatype as shown below:
CREATE TABLE Articles(Name VARCHAR(255), Article LONGTEXT);
Now, using JDBC connect to the database and prepare a PreparedStatement to insert values into the above created table:
String query = "INSERT INTO Tutorial(Name, Article) VALUES (?,?)";PreparedStatement pstmt = con.prepareStatement(query);
Set values to the place holders using the setter methods of the PreparedStatement interface and for the Clob datatype set value using the setCharacterStream() method.
Example
Following is an example demonstrating how to insert file in to a MySQL database using JDBC program. Here we are have created a table with a Clob datatype, inserted values in to it.
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertingFileToDatabase {
public static void main(String args[]) throws Exception {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Inserting values
String query = "INSERT INTO Articles(Name, Article) VALUES (?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, "article1");
FileReader reader = new FileReader("E:\data\article1.txt");
pstmt.setCharacterStream(2, reader);
pstmt.execute();
pstmt.setString(1, "article2");
reader = new FileReader("E:\data\article2.txt");
pstmt.setCharacterStream(2, reader);
pstmt.execute();
pstmt.setString(1, "article3");
reader = new FileReader("E:\data\article3.txt");
pstmt.setCharacterStream(2, reader);
pstmt.execute();
System.out.println("Data inserted......");
}
}Output
Connection established...... Data inserted......
Using MySQL workbench, you can export the contents of a table to various files such as html files, .csv files text files etc. If you export the contents of the table after inserting data to a HTTML file its output will look like:
4K+ Views
