Servlet Example To Delete Mysql Clob Data

A Clob is a Character Large Object in a Database. A large character data file upto 4 GB can be stored as CLOB type. JDBC provides java.sql.Clob interface for handling large character/text object. To delete an existing database record, which has a clob column, we can do by providing the primary key for the desired record (to be deleted). For deleting the record we will use the "article" table wher "ID" column is the primary key, and "Body" is the CLOB column.

Mysql Clob

According to the Mysql, there are four text types TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT which can be taken as CLOB type and have the maximum lengths and storage requirements. The maximum length of TINYTEXT has 255 character (8 bits), TEXT has 16,535 character (16 bits), MEDIUMTEXT has 16,777,216 character (24 bits) and LONGTEXT has 4,294,967,295 character (32 bits).

How to define Clob Datatype in Mysql Table

CREATE TABLE `article` (
`ID` int(11) NOT NULL,
`Subject` varchar(256) NOT NULL,
`Body` longtext,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Delete Mysql Clob Data

We have developed a servlet (DeleteClobExample.java) that accepts the ID of a file and delete the associated file.

import java.io.*;
import java.util.*;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.net.URL;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLConnection;
import java.sql.PreparedStatement;
import java.io.ByteArrayOutputStream;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DeleteClobExample extends HttpServlet {
  public void doGet(HttpServletRequest request, HttpServletResponse response) 
  throws IOException,ServletException {
  response.setContentType("text/html");
  Connection con = null;
  PreparedStatement ps = null;

  Integer id = 1;
  ServletOutputStream out = response.getOutputStream();
  out.println("<html><head><title>Delete Clob Example</title></head>");
  try {
  Class.forName("com.mysql.jdbc.Driver");
  con =DriverManager.getConnection ("jdbc:mysql://192.168.10.59:3306/
   example", "root", "root");
  ps = con.prepareStatement("delete from article where id = ?");
  ps.setInt(1, id);
  ps.executeUpdate();
  out.println("<body><h4><font color='green'>Successfully deleted clob 
   data of id= " + id + "</font></h4></body></html>");
  } catch (Exception e) {
  e.printStackTrace();
  out.println("<body><h4><font color='red'>File could not
  be deleted <br><br> Exception thrown:<br> " + e.getMessage()
    + "</font></h4></body></html>");
  }finally {
  try {
  ps.close();
  con.close();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  }
  }
}

Mapping of servlet (DeleteClobExample.java) in web.xml

<servlet>
<servlet-name>DeleteClobExample</servlet-name>
<servlet-class>DeleteClobExample</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DeleteClobExample</servlet-name>
<url-pattern>/DeleteClobExample</url-pattern>
</servlet-mapping>

Run the servlet (DeleteClobExample.java) on this url: http://localhost:8080/JavaExample/DeleteClobExample. The figure below is shown if record is deleted successfully.

and if in case any error in database connection exists or if no any file on given id then the following message will be displayed.

Download Source Code

Servlet Example To Delete Mysql Clob Data