InsertClobToMySqlServlet.java Source code

Java tutorial

Introduction

Here is the source code for InsertClobToMySqlServlet.java

Source

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

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 InsertClobToMySqlServlet extends HttpServlet {
    public static Connection getConnection() throws Exception {
        String driver = "org.gjt.mm.mysql.Driver";
        String url = "jdbc:mysql://localhost/databaseName";
        String username = "root";
        String password = "root";
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws IOException, ServletException {

        String clobData = null;
        Connection conn = null;

        String id = "001";
        String name = "fileName";
        String fileAsURL = "http://yourwebsite/fileName.dat";
        ServletOutputStream out = response.getOutputStream();

        response.setContentType("text/html");
        out.println("<html><head><title>Insert Clob To MySql Servlet</title></head>");

        try {
            conn = getConnection();
            clobData = getClobsContentAsString(fileAsURL);
            insertCLOB(conn, id, name, clobData);
            out.println("<body><h4>OK: inserted a new record with id=" + id + "</h4></body></html>");
        } catch (Exception e) {
            e.printStackTrace();
            out.println("<body><h4>Error: " + e.getMessage() + "</h4></body></html>");
        }
    }

    public void insertCLOB(Connection conn, String id, String name, String fileContent) throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("insert into datafiles(id, filename, filebody) values (?, ?, ?)");
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.setString(3, fileContent);
            pstmt.executeUpdate();
        } finally {
            pstmt.close();
        }
    }

    public static String getClobsContentAsString(String urlAsString) throws Exception {
        InputStream content = null;
        try {
            URL url = new URL(urlAsString);
            URLConnection urlConn = url.openConnection();
            urlConn.connect();
            content = urlConn.getInputStream();

            int BUFFER_SIZE = 1024;
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            int length;
            byte[] buffer = new byte[BUFFER_SIZE];

            while ((length = content.read(buffer)) != -1) {
                output.write(buffer, 0, length);
            }
            return new String(output.toByteArray());
        } finally {
            content.close();
        }
    }

}