Inserting Mysql CLOB data using Servlet
In this Section, we will insert "clob" data using "servlet".
In this Section, we will insert "clob" data using "servlet".Inserting Mysql CLOB data using Servlet
In this Section, we will insert "clob" data using "servlet". A CLOB is a Character Large Object in a Database table. CLOB data is used to store a block of text. It is designed to store ASCII text data including formatted text such as HTML. CLOB values are not stored as a part of the row of the database table, they are usually allocated space in whole disk pages.
Advantages
1.You can read or write any portion of the CLOB data.
2.Equallity of 2 CLOBs can be check by using equals operator.
3.Some default characteristics for the column ,can be override by 'application programmer' ,when they create a CLOB object.
Disadvantage
1.Due to allocation of whole disk pages, a short 'CLOB' wastes space.
2.Ristriction on how you can use a CLOB column in an sql statement.
Creating CLOB data type in Mysql table
In Mysql, CLOB data type is of 4 types--TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. 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).
Creating table in 'Mysql'
| CREATE TABLE `article` ( `ID` int(11) NOT NULL, `Subject` varchar(256)
NOT NULL, `Body` longtext, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
InsertClobexample.java
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 InsertClobExample extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException {
response.setContentType("text/html");
String clobData = null;
Connection con = null;
Integer id = 1;
String fileName = "san";
String sourceURL = "http://localhost:8080/JavaExample/images/san.dat";
ServletOutputStream out = response.getOutputStream();
out.println("<html><head><title>Insert Clob Example</title></head>");
try {
Class.forName("com.mysql.jdbc.Driver");
con =DriverManager.getConnection ("jdbc:mysql://192.168.10.59:3306/example", "root", "root");
clobData = getClobDataAsString(sourceURL);
insertClob(con, id, fileName, clobData);
out.println("<body><h4><font color='green'>Successfully insert
Your Record with id=" + id + "</font></h4></body></html>");
} catch (Exception e) {
e.printStackTrace();
out.println("<body><h4><font color='red'>Unable to insert" + e.getMessage() + "</font></h4></body></html>");
}
}
public void insertClob(Connection con, Integer id, String fileName, String fileData)throws Exception{
PreparedStatement ps = null;
try {
ps = con.prepareStatement("insert into article(ID, Subject, Body) values (?, ?, ?)");
ps.setInt(1, id);
ps.setString(2, fileName);
ps.setString(3, fileData);
ps.executeUpdate();
}catch(Exception e){
System.out.println(e);
}finally {
ps.close();
}
}
public static String getClobDataAsString(String urlData) throws Exception {
InputStream is = null;
try {
URL url = new URL(urlData);
System.out.println("url"+url);
URLConnection urlConn = url.openConnection();
urlConn.connect();
is = urlConn.getInputStream();
int BUFFER_SIZE = 1024;
ByteArrayOutputStream output = new ByteArrayOutputStream();
int length;
byte[] buffer = new byte[BUFFER_SIZE];
while ((length = is.read(buffer)) != -1) {
output.write(buffer, 0, length);
}
return new String(output.toByteArray());
} finally {
is.close();
}
}
}
OUTPUT
