org.kawanfw.test.api.client.InsertAndUpdateClobTestNew.java Source code

Java tutorial

Introduction

Here is the source code for org.kawanfw.test.api.client.InsertAndUpdateClobTestNew.java

Source

/*
 * This file is part of AceQL. 
 * AceQL: Remote JDBC access over HTTP.                                     
 * Copyright (C) 2015,  KawanSoft SAS
 * (http://www.kawansoft.com). All rights reserved.                                
 *                                                                               
 * AceQL is free software; you can redistribute it and/or                 
 * modify it under the terms of the GNU Lesser General Public                    
 * License as published by the Free Software Foundation; either                  
 * version 2.1 of the License, or (at your option) any later version.            
 *                                                                               
 * AceQL is distributed in the hope that it will be useful,               
 * but WITHOUT ANY WARRANTY; without even the implied warranty of                
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU             
 * Lesser General Public License for more details.                               
 *                                                                               
 * You should have received a copy of the GNU Lesser General Public              
 * License along with this library; if not, write to the Free Software           
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  
 * 02110-1301  USA
 *
 * Any modifications to this file must keep this entire header
 * intact.
 */
package org.kawanfw.test.api.client;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.io.IOUtils;
import org.junit.Assert;
import org.junit.Test;
import org.kawanfw.commons.util.FrameworkFileUtil;
import org.kawanfw.commons.util.Sha1Util;
import org.kawanfw.sql.api.util.PreparedStatementRunner;
import org.kawanfw.sql.api.util.SqlUtil;
import org.kawanfw.test.parms.ConnectionLoader;
import org.kawanfw.test.parms.SqlTestParms;
import org.kawanfw.test.util.MessageDisplayer;

public class InsertAndUpdateClobTestNew {

    /** Universal and clean line separator */
    public static String CR_LF = System.getProperty("line.separator");

    @Test
    public void test() throws Exception {
        Connection connection = null;
        try {
            connection = ConnectionLoader.getAceqlConnection();
            test(connection);
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    public static void main(String[] args) throws Exception {
        new InsertAndUpdateClobTestNew().test();
    }

    /**
     * @param connection
     *            the AceQL Connection
     * 
     * @throws SQLException
     * @throws Exception
     */
    public void test(Connection connection) throws SQLException, Exception {

        MessageDisplayer.initClassDisplay(this.getClass().getSimpleName());

        SqlUtil sqlUtil = new SqlUtil(connection);

        if (sqlUtil.isAdaptiveServerEnterprise()) {
            MessageDisplayer.display("CLOB test not supported for Adaptive Server Enterprise.");
            return;
        }

        // delete all values in database
        PreparedStatementRunner preparedStatementRunner = new PreparedStatementRunner(connection,
                "delete from documentation where item_id >= ? ", 1);
        preparedStatementRunner.executeUpdate();
        preparedStatementRunner.close();

        // Insert 100 records with a Blob
        File clobFile = SqlTestParms.getFileFromUserHome(SqlTestParms.TEXT_FILE_1);

        if (!clobFile.exists()) {
            System.err.println("In order to run this blob test, please put a " + SqlTestParms.TEXT_FILE_1
                    + " in your user.home directory: " + FrameworkFileUtil.getUserHome());
        }

        Assert.assertEquals(true, clobFile.exists());

        // Compute the hash of the file
        Sha1Util sha1 = new Sha1Util();
        String shaHexa = sha1.getHexFileHash(clobFile);

        insertLoopPrepStatement(connection, 1, clobFile);

        // Now test that the values were correctly inserted:
        selectClobTest(connection, SqlTestParms.TEXT_FILE_1, shaHexa);

        File clobFile2 = SqlTestParms.getFileFromUserHome(SqlTestParms.TEXT_FILE_2);

        // Update a record with a new text
        updateclob(connection, clobFile2);

        sha1 = new Sha1Util();
        shaHexa = sha1.getHexFileHash(clobFile2);

        // Now test that the values were correctly inserted:
        selectClobTest(connection, SqlTestParms.TEXT_FILE_2, shaHexa);

        if (!new SqlUtil(connection).isAccess()) {
            selectClobTestAlternateSyntax(connection, SqlTestParms.TEXT_FILE_2, shaHexa);
        }

    }

    public void updateclob(Connection connection, File file) throws Exception {
        PreparedStatement prepStatement = null;

        String sql = "update documentation set " + " item_doc  = ? " + "     where  item_id >= ?";

        prepStatement = connection.prepareStatement(sql);

        Clob clob = connection.createClob();

        Writer writer = null;
        Reader reader = null;

        try {
            reader = new BufferedReader(new FileReader(file));
            writer = clob.setCharacterStream(1);

            IOUtils.copy(reader, writer);

            int i = 1;
            prepStatement.setClob(i++, clob);
            prepStatement.setInt(i++, 1);

            prepStatement.executeUpdate();
            // Close and free are important to delete temp files
            prepStatement.close();
            clob.free();
        } finally {
            IOUtils.closeQuietly(reader);
            IOUtils.closeQuietly(writer);
        }

    }

    /**
     * Test that the blob was were correctly inserted
     * 
     * @param connection
     */
    public void selectClobTest(Connection connection, String originalFileName, String shaHexa) throws Exception {

        int item_id;

        String sql = "select * from documentation where item_id >= ? ";

        PreparedStatement prepStatement = connection.prepareStatement(sql);

        int i = 1;
        prepStatement.setInt(i++, 1);

        ResultSet rs = prepStatement.executeQuery();

        MessageDisplayer.display("");

        Reader reader = null;
        Writer writer = null;

        while (rs.next()) {

            item_id = rs.getInt("item_id");

            File originalFile = SqlTestParms.getFileFromUserHome(originalFileName);
            // String extension = "."
            // + StringUtils.substringAfterLast(originalFile.toString(),
            // ".");

            File file = InsertAndUpdateBlobTest.createTempFile(originalFile.toString());
            try {
                reader = rs.getCharacterStream("item_doc");

                if (reader != null) {
                    writer = new BufferedWriter(new FileWriter(file));
                    IOUtils.copy(reader, writer);

                } else {
                    MessageDisplayer.display("item_doc column is null!");
                }

            } finally {
                IOUtils.closeQuietly(reader);
                IOUtils.closeQuietly(writer);
            }

            i = 1;
            item_id = rs.getInt(i++);

            MessageDisplayer.display("");
            MessageDisplayer.display("item_id     : " + item_id);

            // Compute the hash of the file
            Sha1Util sha1 = new Sha1Util();
            String shaHexaNew = sha1.getHexFileHash(file);

            Assert.assertEquals("Comparing original " + SqlTestParms.TEXT_FILE_1 + " new " + file + " hash values ",
                    shaHexa, shaHexaNew);

            file.delete();

            MessageDisplayer.display("");
            MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file "
                    + SqlTestParms.getFileFromUserHome(originalFileName));

        }

        prepStatement.close();
        rs.close();

        MessageDisplayer.display("Select done!");

    }

    /**
     * Test that the blob was were correctly inserted
     * 
     * @param connection
     */
    public void selectClobTestAlternateSyntax(Connection connection, String originalFileName, String shaHexa)
            throws Exception {

        int item_id;
        Clob clob;

        String sql = "select * from documentation where item_id >= ? ";

        PreparedStatement prepStatement = connection.prepareStatement(sql);

        int i = 1;
        prepStatement.setInt(i++, 1);

        ResultSet rs = prepStatement.executeQuery();

        MessageDisplayer.display("");

        Reader reader = null;
        Writer writer = null;

        while (rs.next()) {

            item_id = rs.getInt("item_id");

            i = 1;
            item_id = rs.getInt(i++);
            clob = rs.getClob(i++);

            File originalFile = SqlTestParms.getFileFromUserHome(originalFileName);
            // String extension = "."
            // + StringUtils.substringAfterLast(originalFile.toString(),
            // ".");

            File file = InsertAndUpdateBlobTest.createTempFile(originalFile.toString());

            try {
                reader = clob.getCharacterStream();

                if (reader != null) {
                    writer = new BufferedWriter(new FileWriter(file));
                    IOUtils.copy(reader, writer);

                } else {
                    MessageDisplayer.display("item_doc column is null!");
                }
            } finally {
                IOUtils.closeQuietly(reader);
                IOUtils.closeQuietly(writer);

                try {
                    clob.free();
                } catch (Throwable e) {
                    MessageDisplayer.display("clob.free() not done: " + e.toString());
                }
            }

            MessageDisplayer.display("");
            MessageDisplayer.display("item_id     : " + item_id);

            // Compute the hash of the file
            Sha1Util sha1 = new Sha1Util();
            String shaHexaNew = sha1.getHexFileHash(file);

            Assert.assertEquals(shaHexa, shaHexaNew);

            file.delete();

            MessageDisplayer.display("");
            MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file "
                    + SqlTestParms.getFileFromUserHome(originalFileName));

        }

        prepStatement.close();
        rs.close();

        MessageDisplayer.display("Select done!");

    }

    /**
     * Insert a CLOB
     * 
     * @throws Exception
     *             it any Exception occurs
     */
    public void insertLoopPrepStatement(Connection connection, int numberToInsert, File file) throws Exception {
        // We can now use our Remote JDBC Connection as a regular Connection!
        connection.setAutoCommit(false);

        // We will do all our remote insert in a SQL Transaction
        try {
            String sql = "insert into documentation values ( ?, ? )";

            // Create a new Prepared Statement
            PreparedStatement prepStatement = null;

            MessageDisplayer.display("");
            MessageDisplayer.display("Inserting " + numberToInsert + " documentation...");

            for (int customerId = 1; customerId < numberToInsert + 1; customerId++) {
                int i = 1;

                // We will insert a Blob (the image of the product).
                // The transfer will be done in streaming both on the client
                // and on the Servlet Server: we can upload/download very big
                // files.

                Reader in = new BufferedReader(new FileReader(file));

                prepStatement = connection.prepareStatement(sql);

                prepStatement.setInt(i++, customerId);

                prepStatement.setCharacterStream(i++, in, (int) file.length());

                prepStatement.executeUpdate();
                prepStatement.close();
            }

            // We do either everything in a single transaction or nothing
            connection.commit(); // Commit is propagated on Server
            MessageDisplayer.display("Remote Commit Done on AceQL Server!");
        } catch (Exception e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }

    }

}