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.math.BigDecimal; 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.lang3.SystemUtils; import org.junit.Assert; import org.junit.Test; 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 InsertAndUpdatePrepStatementTest { /** The increase factor when we uppdate the row */ private int increaseFactor = 100; private Timestamp dateShippedUpdated; public static void main(String[] args) throws Exception { if (SystemUtils.IS_JAVA_1_7) { System.setProperty("java.net.preferIPv4Stack", "true"); } new InsertAndUpdatePrepStatementTest().test(); } @Test public void test() throws Exception { Connection connection = null; try { connection = ConnectionLoader.getAceqlConnection(); test(connection); } finally { if (connection != null) { connection.close(); } } } /** * @param connection * the AceQL Connection * * @throws SQLException * @throws Exception */ public void test(Connection connection) throws SQLException, Exception { MessageDisplayer.initClassDisplay(this.getClass().getSimpleName()); // delete all values in database SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isAdaptiveServerEnterprise()) { MessageDisplayer.display("TIMESTAMP update not supported Adaptive Server Enterprise."); return; } if (sqlUtil.isAccess()) { MessageDisplayer.display("setBytes(i++, null) not supported for MS Access"); return; } DeletePreparedStatementTest.deleteAllCustomers(connection); DeletePreparedStatementTest.deleteAllOrderlog(connection); // Insert 100 customers insertLoopPrepStatement(connection, SqlTestParms.ROWS_TO_INSERT); int customerId = 50; int itemId = 50; // Update values updateValues(connection, customerId, itemId); // Now test that the values were correctly inserted: selectPrepStatementTest(connection, customerId, itemId); } /** * Update the values of a row with an increase factor and a new datetime * * @param connection * @param customerId * @param itemId * @throws Exception */ private void updateValues(Connection connection, int customerId, int itemId) throws Exception { String sql = "update orderlog set " + " date_placed = ? " + " , date_shipped = ? " + " , cost_price = ? " + " , is_delivered = ? " + " , quantity = ? " + " where customer_id = ? and item_id = ?"; PreparedStatement prepStatement = connection.prepareStatement(sql); long newTime = (new java.util.Date()).getTime(); Date datePlaced = new Date(newTime); dateShippedUpdated = new Timestamp(newTime); MessageDisplayer.display("dateShippedUpdated : " + dateShippedUpdated); MessageDisplayer .display("dateShippedUpdated.substring.(0, 19): " + dateShippedUpdated.toString().substring(0, 19)); int i = 1; prepStatement.setDate(i++, datePlaced); prepStatement.setTimestamp(i++, dateShippedUpdated); // We use the increase factor prepStatement.setBigDecimal(i++, new BigDecimal(customerId * increaseFactor)); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 1); } else { prepStatement.setBoolean(i++, true); } prepStatement.setInt(i++, customerId * increaseFactor * 2); // Key value prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, itemId); prepStatement.executeUpdate(); prepStatement.close(); } /** * Test that the values were correclty inserted * * @param connection */ @SuppressWarnings("deprecation") public void selectPrepStatementTest(Connection connection, int customerId, int orderId) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; BigDecimal cost_price_scale; Date date_placed; Timestamp date_shipped; byte[] jpeg_image = null; boolean is_delivered; int quantity; String sql = "select * from orderlog where customer_id = ? and item_id = ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, orderId); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); 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"); cost_price_scale = rs.getBigDecimal("cost_price", 5); date_placed = rs.getDate("date_placed"); date_shipped = rs.getTimestamp("date_shipped"); jpeg_image = rs.getBytes("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"); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("cost_price_scale: " + cost_price_scale); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped : " + date_shipped); MessageDisplayer.display("jpeg_image : " + jpeg_image); MessageDisplayer.display("is_delivered : " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // Assert done on first 18 chars (ex: 2011-11-02 16:26:14), because // MySql Truncs // the remaining milliseconds Assert.assertEquals(dateShippedUpdated.toString().substring(0, 19), date_shipped.toString().substring(0, 19)); if (new SqlUtil(connection).isSQLAnywhere()) { // Because SQLK Anywhere stores 5000.0000 instead of 5000 in db Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(), cost_price.toString().substring(0, 4)); } else { Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(), cost_price.toString()); } Assert.assertEquals(true, is_delivered); Assert.assertEquals(customer_id * increaseFactor * 2, quantity); i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); int iForCostPrice = i; cost_price = rs.getBigDecimal(i++); cost_price_scale = rs.getBigDecimal(iForCostPrice, 5); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); // NO! do not read twice the same file ==> has been delete at first // read // jpeg_image = rs.getBytes(i++); i++; 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("cost_price_scale: " + cost_price_scale); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped : " + date_shipped); MessageDisplayer.display("jpeg_image : " + jpeg_image); MessageDisplayer.display("is_delivered : " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // Assert done on first 18 chars (ex: 2011-11-02 16:26:14), because // MySql Truncs // the remaining milliseconds Assert.assertEquals(date_shipped.toString().substring(0, 18), dateShippedUpdated.toString().substring(0, 18)); if (new SqlUtil(connection).isSQLAnywhere()) { // Because SQLK Anywhere stores 5000.0000 instead of 5000 in db Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(), cost_price.toString().substring(0, 4)); } else { Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(), cost_price.toString()); } Assert.assertEquals(true, is_delivered); Assert.assertEquals(new Integer(customer_id * increaseFactor * 2), new Integer(quantity)); } prepStatement.close(); rs.close(); MessageDisplayer.display("Select done!"); } /** * Do a 100 row insert inside a loop * * @param connection * the AceQL Connection * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert) 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 values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog..."); SqlUtil sqlUtil = new SqlUtil(connection); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); 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.setBytes(i++, null); // No Blob in this example. if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); 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); } } }