Home Tutorial Java Jdbc Inserting Mysql CLOB data using Servlet

 
 

Share on Google+Share on Google+
Inserting Mysql CLOB data using Servlet
Posted on: June 5, 2010 at 12:00 AM
Advertisement
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

Download Source Code

Advertisement

Related Tags for Inserting Mysql CLOB data using Servlet:


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: June 5, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com