org.kawanfw.test.api.client.autogeneratedkeys.InsertPrepStatementAutoKeysTest.java Source code

Introduction

Here is the source code for org.kawanfw.test.api.client.autogeneratedkeys.InsertPrepStatementAutoKeysTest.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.autogeneratedkeys;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

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.util.MessageDisplayer;

public class InsertPrepStatementAutoKeysTest {

    @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 InsertPrepStatementAutoKeysTest().test();
    }

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

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

        if (!InsertStatementTestAutoKeysTest.isAutoGeneratedKeysSupportedForEngine(connection)) {
            MessageDisplayer.display(
                    "Autogenerated Keys are not supported for " + new SqlUtil(connection).getDatabaseProductName());
            return;
        }

        // Insert 1 customer_auto with executeUpdate()
        insertPrepStatementExecuteUpdate(connection, 1, false, true);

        // Insert 1 customer_auto with executeUpdate() in commit off
        insertPrepStatementExecuteUpdate(connection, 1, false, false);

        // Insert 1 customer_auto with raw execute()

        if (new SqlUtil(connection).isAdaptiveServerEnterprise()) {
            MessageDisplayer
                    .display("Last Keys retrieval not supported in Sybase ASE for PreparedStatement.execute()");
            return;
        }

        insertPrepStatementExecuteUpdate(connection, 1, true, true);

    }

    /**
     * Do a 100 row insert inside a loop
     * 
     * @param connection
     *            the AceQL Connection
     * 
     * @throws Exception
     *             it any Exception occurs
     */
    public static void insertPrepStatementExecuteUpdate(Connection connection, int valueToInsert,
            boolean useRawExecute, boolean autoCommitOn) throws Exception {
        // We can now use our Remote JDBC Connection as a regular Connection!

        long maxCustomerId = InsertStatementTestAutoKeysTest.getMaxCustomerId(connection);
        MessageDisplayer.display("");
        MessageDisplayer.display("maxCustomerId: " + maxCustomerId);

        if (!autoCommitOn) {
            connection.setAutoCommit(false);
        }

        // We will do all our remote insert in a SQL Transaction
        try {
            // 1) First create a Customer
            String sql = "insert into customer_auto (customer_title, fname, lname, addressline, town, zipcode, phone) "
                    + " values ( ?, ?, ?, ?, ?, ?, ? )";

            MessageDisplayer.display("");
            MessageDisplayer.display("Inserting " + valueToInsert + " customers...");

            PreparedStatement prepStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            int i = valueToInsert;
            int j = 1;
            prepStatement.setString(j++, "Sir");
            prepStatement.setNull(j++, Types.VARCHAR);
            prepStatement.setString(j++, "Smith_" + i);
            prepStatement.setString(j++, i + ", Csar Avenue");
            prepStatement.setString(j++, "JavaLand_" + i);
            prepStatement.setString(j++, i + "45");
            prepStatement.setString(j++, i + "-12345678");

            int rc = -1;

            if (!useRawExecute) {
                rc = prepStatement.executeUpdate();
                MessageDisplayer.display("after executeUpdate(): row count: " + rc);

            } else {
                prepStatement.execute();

                rc = prepStatement.getUpdateCount();
                MessageDisplayer.display("after execute(): prepStatement.getUpdateCount(): " + rc);
            }

            if (!autoCommitOn) {
                connection.commit();
            }

            ResultSet keys = prepStatement.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....
            // Assert.assertEquals("last key = maxCustomerId + 1", lastKey,
            // maxCustomerId + 1);

            // So do another test:
            Assert.assertEquals("last key >= 1", true, lastKey >= 1);

            prepStatement.close();

        } catch (Exception e) {
            e.printStackTrace();

            if (!autoCommitOn) {
                connection.rollback();
            }

            throw e;
        } finally {
            if (!autoCommitOn) {
                connection.setAutoCommit(true);
            }

        }

    }

}