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.autogeneratedkeys; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.lang3.SystemUtils; import org.junit.Assert; import org.junit.Test; import org.kawanfw.sql.api.util.PreparedStatementRunner; import org.kawanfw.sql.api.util.SqlUtil; import org.kawanfw.test.parms.ConnectionLoader; import org.kawanfw.test.util.MessageDisplayer; public class InsertStatementTestAutoKeysTest { @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 { if (SystemUtils.IS_JAVA_1_7) { System.setProperty("java.net.preferIPv4Stack", "true"); } new InsertStatementTestAutoKeysTest().test(); } /** * @param connection * @throws SQLException */ public static boolean isAutoGeneratedKeysSupportedForEngine(Connection connection) throws SQLException { if (new SqlUtil(connection).isOracle()) { return false; } if (new SqlUtil(connection).isIngres()) { return false; } if (new SqlUtil(connection).isSQLAnywhere()) { return false; } if (new SqlUtil(connection).isAccess()) { return false; } return true; } public static long getMaxCustomerId(Connection connection) throws SQLException { connection.setAutoCommit(true); String sql = "select max(customer_id) from customer_auto"; // Create the PreparedStatementRunner instance PreparedStatementRunner preparedStatementRunner = new PreparedStatementRunner(connection, sql); // Execute a query ResultSet rs = preparedStatementRunner.executeQuery(); long max = 0; if (rs.next()) { max = rs.getLong(1); } rs.close(); return max; } /** * @param connection * the AceQL Connection * * @throws SQLException * @throws Exception */ public void test(Connection connection) throws SQLException, Exception { MessageDisplayer.initClassDisplay(this.getClass().getSimpleName()); if (!isAutoGeneratedKeysSupportedForEngine(connection)) { MessageDisplayer.display( "Autogenerated Keys are not supported for " + new SqlUtil(connection).getDatabaseProductName()); return; } MessageDisplayer.display("customer_id = " + getMaxCustomerId(connection)); // Insert 1 customer_auto with executeUpdate() insertStatement(connection, 1, false, true); // Insert 1 customer_auto with executeUpdate() in commit off insertStatement(connection, 1, false, false); // Insert 1 customer_auto with raw execute() insertStatement(connection, 1, true, true); } /** * Do a 100 row insert inside a loop * * @param connection * the AceQL Connection * * @param numberToInsert * the number for instances to insert * * @param useRawExecute * if true, we will insert using execute() * * @throws Exception * it any Exception occurs */ public static void insertStatement(Connection connection, int numberToInsert, boolean useRawExecute, boolean autoCommitOn) throws Exception { long maxCustomerId = getMaxCustomerId(connection); MessageDisplayer.display("maxCustomerId: " + maxCustomerId); // We can now use our Remote JDBC Connection as a regular Connection! if (!autoCommitOn) { connection.setAutoCommit(false); } // We will do all our remote insert in a SQL Transaction try { Statement statement = null; String title; String fname; String lname; String addressline; String town; String zipcode; String phone; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " customers..."); statement = connection.createStatement(); // //SystemOutHandle.display("customer_id: " + customerId); title = "Sir"; fname = "Smith_" + numberToInsert; lname = "Smith_" + numberToInsert; addressline = numberToInsert + ", Csar Avenue"; town = "JavaLand_" + numberToInsert; zipcode = numberToInsert + "45"; phone = numberToInsert + "-12345678"; String sql = "insert into customer_auto (customer_title, fname, lname, addressline, town, zipcode, phone) " + " values ( '?2', '?3', '?4', '?5', '?6', '?7', '?8' )"; sql = sql.replace("?2", title); sql = sql.replace("?3", fname); sql = sql.replace("?4", lname); sql = sql.replace("?5", addressline); sql = sql.replace("?6", town); sql = sql.replace("?7", zipcode); sql = sql.replace("?8", phone); if (useRawExecute) { statement.execute(sql, Statement.RETURN_GENERATED_KEYS); } else { statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); } // We do either everything in a single transaction or nothing if (!autoCommitOn) { connection.commit(); // Commit is propagated on Server } ResultSet keys = statement.getGeneratedKeys(); long lastKey = -1; while (keys.next()) { lastKey = keys.getLong(1); } keys.close(); MessageDisplayer.display("Last Key: " + lastKey); // Don't know why: there is a bug in some engines where sometime // increment is > 1 // Assert.assertEquals("last key = maxCustomerId + 1", lastKey, // maxCustomerId + 1); // So do another test: Assert.assertEquals("last key >= 1", true, lastKey > 1); statement.close(); } catch (Exception e) { if (!autoCommitOn) { connection.rollback(); } throw e; } finally { if (!autoCommitOn) { connection.setAutoCommit(true); } } } }