Write an JDBC example for inserting value for Clob data type into a table?

JDBCJava 8MySQLMySQLi Database

Assume we already have a table named MyData in the database with the following description.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| Name    | varchar(255) | YES  |     | NULL    |       |
| Article | longtext     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

If you need to insert value int to the clob data type using a JDBC program you need to use the methods which reads the contents of a file and sets to the database rows. PreparedStatement interface provides such methods.

The void setCharacterStream(int parameterIndex, Reader reader) method sets the contents of the reader object as a value to the parameter at the given index. Other variant of this method are:

  • void setCharacterStream(int parameterIndex, Reader reader, int length)

  • void setCharacterStream(int parameterIndex, Reader reader, long length)

The void setClob(int parameterIndex, Clob x) method sets the given java.sql.Clob object as a value to the parameter at the given index. Other variant of this method are:

  • void setClob(int parameterIndex, Reader reader)

  • void setClob(int parameterIndex, Reader reader, long length)

You can set value to the Clob data type using either of these methods.

Example

Following example sets value to a Clob datatype using the setClob() method.

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertingValueInClob {
   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 MyData(Name, Article ) VALUES (?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      FileReader reader = new FileReader("E:\\images\\javafx.txt");
      pstmt.setClob(2, reader);
      pstmt.execute();
      System.out.println("Data inserted");
   }
}

Output

Connection established......
Table Created......
Contents of the table are:
JavaFX
E:\images\MyData_clob_output1.txt

If you try to view the clob value in the record using the MySQL work bench you can see the inserted text data as shown below:

raja
Published on 22-Mar-2019 15:37:30
Advertisements