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.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.math.BigDecimal; import java.sql.Blob; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; 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.SqlUtil; import org.kawanfw.test.parms.ConnectionLoader; import org.kawanfw.test.parms.SqlTestParms; import org.kawanfw.test.util.MessageDisplayer; public class InsertAndUpdateBlobTestPsqlOID { @Test public void test() throws Exception { Connection connection = null; try { connection = ConnectionLoader.getAceqlConnection(); test(connection); } finally { // connection.close(). Fails with INGRES. Don't know why SqlUtil sqlUtil = new SqlUtil(connection); if (!sqlUtil.isIngres()) { if (connection != null) { connection.close(); } } } } public static void main(String[] args) throws Exception { new InsertAndUpdateBlobTestPsqlOID().test(); } /** * @param connection * the AceQL Connection * * @throws SQLException * @throws Exception */ public void test(Connection connection) throws SQLException, Exception { // Test is only for PostgreSQL OID SqlUtil sqlUtil = new SqlUtil(connection); if (!sqlUtil.isPostgreSQL()) { return; } MessageDisplayer.initClassDisplay(this.getClass().getSimpleName()); // delete all values in database DeletePreparedStatementTest.deleteAllOrderlog_2(connection); File blobFile = null; SqlTestParms.BLOB_FILE_1 = SqlTestParms.KOALA; blobFile = SqlTestParms.getFileFromUserHome(SqlTestParms.BLOB_FILE_1); // Insert 100 records with a Blob if (!blobFile.exists()) { System.err.println("In order to run this blob test, please put a " + SqlTestParms.BLOB_FILE_1 + " in your user.home/kawanfw-test directory: " + FrameworkFileUtil.getUserHome() + File.separator + "kawanfw-test"); } Assert.assertEquals(true, blobFile.exists()); // Compute the hash of the file Sha1Util sha1 = new Sha1Util(); String shaHexa = sha1.getHexFileHash(blobFile); insertLoopPrepStatement(connection, 1, blobFile); // Now test that the values were correctly inserted: if (new SqlUtil(connection).isTeradata()) { selectBlobTestAlternateSyntax(connection, SqlTestParms.BLOB_FILE_1, shaHexa); } else { // We can now use our Remote JDBC Connection as a regular // Connection! connection.setAutoCommit(false); selectBlobTest(connection, SqlTestParms.BLOB_FILE_1, shaHexa); connection.commit(); } File blobFile2 = SqlTestParms.getFileFromUserHome(SqlTestParms.BLOB_FILE_2); // Update a record with a new image updateBlob(connection, blobFile2); connection.commit(); sha1 = new Sha1Util(); shaHexa = sha1.getHexFileHash(blobFile2); // Now test that the values were correctly inserted: selectBlobTest(connection, SqlTestParms.BLOB_FILE_2, shaHexa); connection.commit(); if (sqlUtil.isPostgreSQL()) { MessageDisplayer.display("getBlob() syntax not supported for PostgreSQL."); return; } // Now test that the values were correctly inserted: if (new SqlUtil(connection).isTeradata()) { selectBlobTestAlternateSyntax(connection, SqlTestParms.BLOB_FILE_2, shaHexa); } else { selectBlobTest(connection, SqlTestParms.BLOB_FILE_2, shaHexa); } } public void updateBlob(Connection connection, File blobFile) throws Exception { PreparedStatement prepStatement = null; String sql = "update orderlog_2 set " + " jpeg_image = ? " + " where customer_id >= ? and item_id >= ?"; prepStatement = connection.prepareStatement(sql); InputStream in = null; try { in = new BufferedInputStream(new FileInputStream(blobFile)); int i = 1; prepStatement.setBinaryStream(i++, in, (int) blobFile.length()); prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); prepStatement.executeUpdate(); prepStatement.close(); // important to delete temp files } finally { IOUtils.closeQuietly(in); } } /** * Create our own temp file * * @param baseName * the base name to use * * @return the tempfile to create */ public static synchronized File createTempFile(String baseName) { String unique = FrameworkFileUtil.getUniqueId(); String tempFile = baseName + "-" + unique + ".txt"; return new File(tempFile); } /** * Test that the blob was were correctly inserted * * @param connection */ public void selectBlobTest(Connection connection, String originalFileName, String shaHexa) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; Date date_placed; Timestamp date_shipped; boolean is_delivered; int quantity; String sql = "select * from orderlog_2 where customer_id >= ? and item_id >= ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); InputStream in = null; OutputStream out = null; SqlUtil sqlUtil = new SqlUtil(connection); while (rs.next()) { File originalBlobFile = SqlTestParms.getFileFromUserHome(originalFileName); // Do the rs.getBinaryStream("jpeg_image") first (INGRES constraint) File file = createTempFile(originalBlobFile.toString()); try { in = rs.getBinaryStream("jpeg_image"); if (in != null) { out = new BufferedOutputStream(new FileOutputStream(file)); IOUtils.copy(in, out); } else { MessageDisplayer.display("jpeg_image column is null!"); } } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); } i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); cost_price = rs.getBigDecimal(i++); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); // pass the image i++; if (sqlUtil.isIngres()) { is_delivered = (rs.getInt(i++) == 1) ? true : false; } else { is_delivered = rs.getBoolean(i++); } quantity = rs.getInt(i++); MessageDisplayer.display(""); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped: " + date_shipped); MessageDisplayer.display("jpeg_image : " + "content stored in file: " + file); MessageDisplayer.display("is_delivered: " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // 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!"); } /** * Test that the blob was were correctly inserted * * @param connection */ public void selectBlobTestAlternateSyntax(Connection connection, String originalFileName, String shaHexa) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; Date date_placed; Timestamp date_shipped; Blob blob; boolean is_delivered; int quantity; String sql = "select * from orderlog_2 where customer_id >= ? and item_id >= ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); InputStream in = null; OutputStream out = null; SqlUtil sqlUtil = new SqlUtil(connection); while (rs.next()) { customer_id = rs.getInt("customer_id"); item_id = rs.getInt("item_id"); description = rs.getString("description"); cost_price = rs.getBigDecimal("cost_price"); date_placed = rs.getDate("date_placed"); date_shipped = rs.getTimestamp("date_shipped"); blob = rs.getBlob("jpeg_image"); if (sqlUtil.isIngres()) { is_delivered = (rs.getInt("is_delivered") == 1) ? true : false; } else { is_delivered = rs.getBoolean("is_delivered"); } quantity = rs.getInt("quantity"); i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); cost_price = rs.getBigDecimal(i++); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); File originalBlobFile = SqlTestParms.getFileFromUserHome(originalFileName); // String extension = "." // + StringUtils.substringAfterLast( // originalBlobFile.toString(), "."); File file = createTempFile(originalBlobFile.toString()); try { in = blob.getBinaryStream(); if (in != null) { out = new BufferedOutputStream(new FileOutputStream(file)); IOUtils.copy(in, out); } else { MessageDisplayer.display("jpeg_image column is null!"); } } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); try { blob.free(); } catch (Throwable e) { MessageDisplayer.display("blob.free() not done: " + e.toString()); } } i++; if (sqlUtil.isIngres()) { is_delivered = (rs.getInt(i++) == 1) ? true : false; } else { is_delivered = rs.getBoolean(i++); } quantity = rs.getInt(i++); MessageDisplayer.display(""); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped: " + date_shipped); MessageDisplayer.display("jpeg_image : " + "content stored in file: " + file); MessageDisplayer.display("is_delivered: " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // 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 blob * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) 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 orderlog_2 values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog_2..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); // 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(blobFile)); InputStream in = new FileInputStream(blobFile); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBinaryStream(i++, in, (int) blobFile.length()); // prepStatement.setBoolean(i++, false); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); // SystemOutHandle.display("Before executeUpdate..."); prepStatement.executeUpdate(); prepStatement.close(); in.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); } } }