Java tutorial
/* * 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.BufferedInputStream; import java.io.BufferedWriter; import java.io.File; import java.io.FileInputStream; import java.io.FileWriter; import java.io.InputStream; import java.io.InputStreamReader; 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 InsertAndUpdateClobTestAsciiStream { /** 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 InsertAndUpdateClobTestAsciiStream().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; } if (sqlUtil.isMySQL()) { MessageDisplayer.display("CLOB test not supported for MySQL"); return; } if (sqlUtil.isSQLAnywhere()) { MessageDisplayer.display("CLOB test not supported for SQL Anwywhere"); return; } if (sqlUtil.isAccess()) { MessageDisplayer.display("CLOB test not supported for MS Access"); 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); selectClobTestAlternateSyntax(connection, SqlTestParms.TEXT_FILE_1, shaHexa); File clobFile2 = SqlTestParms.getFileFromUserHome(SqlTestParms.TEXT_FILE_2); // Update a record with a new text updateclob(connection, clobFile2); } /** * 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(""); InputStream in = 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()); MessageDisplayer.display("file: " + file); try { in = rs.getAsciiStream("item_doc"); if (in != null) { Reader reader = new InputStreamReader(in); writer = new BufferedWriter(new FileWriter(file)); IOUtils.copy(reader, writer); } else { MessageDisplayer.display("item_doc column is null!"); } } finally { IOUtils.closeQuietly(in); 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(""); InputStream in = 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 { in = clob.getAsciiStream(); if (in != null) { Reader reader = new InputStreamReader(in); writer = new BufferedWriter(new FileWriter(file)); IOUtils.copy(reader, writer); } else { MessageDisplayer.display("item_doc column is null!"); } } finally { IOUtils.closeQuietly(in); 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. InputStream in = new BufferedInputStream(new FileInputStream(file)); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setAsciiStream(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); } } 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); InputStream in = new BufferedInputStream(new FileInputStream(file)); int i = 1; prepStatement.setAsciiStream(i++, in, (int) file.length()); prepStatement.setInt(i++, 1); // System.err.println("file: " + FileUtils.readFileToString(file)); prepStatement.executeUpdate(); prepStatement.close(); // important to delete temp files } }