What is the JDBC Blob data type? How to store and read the data in it?

BLOB is a binary large object that can hold a variable amount of data with a maximum length of 65535 characters.

They are used to store large amounts of binary data, such as images or other types of data. document. Fields defined as TEXT also hold large amounts of data. The difference between the two is that sorting and comparisons of stored data are case-sensitive in BLOBs but not case-sensitive in TEXT fields. You did not specify the length using BLOB or TEXT.

Storing Blobs to the Database

To store the Blob data type to the database, follow these steps using a JDBC program

Step 1: Connect to the database

You can connect to the database using the DriverManagergetConnection() method

by passing the MySQL URL (jdbc:mysql://localhost /sampleDB) (where exampleDB is the database name), username and password as parameters to the getConnection() method.

String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");

Step 2: Create a prepared statement

Use the prepareStatement() method of the Connection interface to create a PreparedStatement object. Pass the insertion query (with placeholders) as a parameter to this method.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyTableVALUES(?, ?)");

Step 3: Set the value for the placeholder

Use the setter method of the PreparedStatement interface to set the value to the placeholder. Select the method based on the data type of the column. For example, if the column is of type VARCHAR, use the setString() method; if the column is of type INT, you can use the setInt() method.

If the column is of type Blob, you can set its value using the setBinaryStream() or setBlob() method. These methods are passed an integer variable representing the parameter index and an object of the InputStream class as parameters.

pstmt.setString(1, "sample image");
//Inserting Blob type
InputStream in = new FileInputStream("E:\images\cat.jpg");
pstmt.setBlob(2, in);

Step 4: Execute the statement

Use the execute() method of the PreparedStatement interface to execute the PreparedStatement created above object.

Retrieve blobs from the database

The getBlob() method of the ResultSet interface accepts an integer representing the index of the column (or a string value representing the column name), and retrieves the value of the specified column , and returned as a Blob object. The

getBytes()

method of the

while(rs.next()) {
   rs.getString("Name");
   rs.getString("Type");
   Blob blob = rs.getBlob("Logo");
}

Blob interface retrieves the contents of the current Blob object and returns it as a byte array.

Using the getBlob() method, you can get the contents of the blob into a byte array and create the image using the write() method FileOutputStream Object.

byte byteArray[] = blob.getBytes(1,(int)blob.length());
FileOutputStream outPutStream = new FileOutputStream("path");
outPutStream.write(byteArray);

Example

The following example creates a table of blob data type in a MySQL database and inserts an image into it. Retrieve and store it in the local file system.

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class BlobExample {
   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......");
      //Creating a table
      Statement stmt = con.createStatement();
      stmt.execute("CREATE TABLE SampleTable( Name VARCHAR(255), Image BLOB)");
      System.out.println("Table Created");
      //Inserting values
      String query = "INSERT INTO SampleTable(Name,image) VALUES (?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "sample image");
      FileInputStream fin = new FileInputStream("E:\images\cat.jpg");
      pstmt.setBlob(2, fin);
      pstmt.execute();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from SampleTable");
      int i = 1;
      System.out.println("Contents of the table are: ");
      while(rs.next()) {
         System.out.println(rs.getString("Name"));
         Blob blob = rs.getBlob("Image");
         byte byteArray[] = blob.getBytes(1,(int)blob.length());
         FileOutputStream outPutStream = new
         FileOutputStream("E:\images\blob_output"+i+".jpg");
         outPutStream.write(byteArray);
         System.out.println("E:\images\blob_output"+i+".jpg");
         System.out.println();
         i++;
      }
   }
}

Output

Connection established......
Table Created
Contents of the table are:
sample image
E:\images\blob_output1.jpg

The above is the detailed content of What is the JDBC Blob data type? How to store and read the data in it?. For more information, please follow other related articles on the PHP Chinese website!