How can we insert/store files into MySQL database using JDBC?

Generally speaking, the contents of files are stored in the MySQL database under the Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) data type.

JDBC provides support for the Clob data type, which stores the contents of the file into a database table.

PreparedStatementThe setCharacterStream() method of the interface accepts an integer representing the index of the parameter and a Reader object as parameters.

And sets the contents of the given reader object (file) to the value of the parameter (placeholder) at the specified index.

You can use this method whenever you need to send very large text values.

Use JDBC to store text files:

If you need to store files in the database using the JDBC program to create a table with Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) data type, as follows As shown:

CREATE TABLE Articles(Name VARCHAR(255), Article LONGTEXT);

Now, use JDBC to connect to the database and prepare a PreparedStatement Insert the value into the table created above:

String query = "INSERT INTO Tutorial(Name, Article) VALUES (?,?)";PreparedStatement pstmt = con.prepareStatement(query);

Use the setter method of the PreparedStatement interface Set the value of the placeholder and use the setCharacterStream() method to set the value of the Clob data type.

Example

The following is an example demonstrating how to insert a file Use JDBC program to connect to MySQL database. Here, we have created a table with Clob data type and inserted values ​​in 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 a table after inserting data into an HTML file, its output will look like this:

The above is the detailed content of How can we insert/store files into MySQL database using JDBC?. For more information, please follow other related articles on the PHP Chinese website!