org.kawanfw.test.api.client.ArrayTest.java Source code

Introduction

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

import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.lang3.SystemUtils;
import org.junit.Assert;
import org.junit.Test;
import org.kawanfw.sql.api.client.RemoteConnection;
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 ArrayTest {

    @Test
    public void test() throws Exception {
        Connection connection = null;
        try {
            SqlTestParms.ACEQL_URL_2 = SqlTestParms.ACEQL_URL_TOMCAT_EMBEDED_LOCALHOST_SSL_2;
            connection = ConnectionLoader.getAceqlConnection2();
            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 ArrayTest().test();
    }

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

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

        DatabaseMetaData databaseMetaData = connection.getMetaData();
        MessageDisplayer.display(
                "databaseMetaData.getDatabaseProductName()   : " + databaseMetaData.getDatabaseProductName());

        if (connection instanceof RemoteConnection) {
            RemoteConnection connectionHttp = (RemoteConnection) connection;
            if (connectionHttp.isStatelessMode()) {
                MessageDisplayer.display("Arrays are not supported in stateless mode");
                return;
            }
        }

        if (!new SqlUtil(connection).isPostgreSQL() && !new SqlUtil(connection).isHSQLDB()
                && !new SqlUtil(connection).isOracle()) {
            MessageDisplayer.display("Arrays tests are only supported in HSQLDB, Oracle Database & PostgreSQL");
            return;
        }

        Object[] northEastRegion = { "10022", "02110", "07399" };

        String sql = null;
        boolean doInsert = true;

        if (doInsert) {

            // Array aArray = connection.createArrayOf("varchar",
            // northEastRegion);

            Array aArray = null;

            if (new SqlUtil(connection).isOracle()) {
                aArray = connection.createArrayOf("VCARRAY", northEastRegion);
            } else {
                aArray = connection.createArrayOf("varchar", northEastRegion);
            }

            PreparedStatement pstmt = connection.prepareStatement("delete from REGIONS where region_name = ?");
            pstmt.setString(1, "NorthEast");
            pstmt.executeUpdate();
            pstmt.close();

            PreparedStatement pstmt2 = connection
                    .prepareStatement("insert into REGIONS (region_name, zips) " + "VALUES (?, ?)");
            pstmt2.setString(1, "NorthEast");
            pstmt2.setArray(2, aArray);
            pstmt2.executeUpdate();
            pstmt2.close();

        }

        sql = "select * from REGIONS";
        PreparedStatement prepStatement = connection.prepareStatement(sql);
        ResultSet rs = prepStatement.executeQuery();

        while (rs.next()) {
            String region_name = rs.getString(1);
            MessageDisplayer.display(region_name + "  ");

            Array z = rs.getArray("ZIPS");

            Object[] zips = (Object[]) z.getArray();

            for (int i = 0; i < zips.length; i++) {
                System.out.print(zips[i] + " ");
            }
            MessageDisplayer.display("");

            if (region_name.equals("NorthEast")) {
                MessageDisplayer.display("Region is NorthEast. Test arrays equality");

                Assert.assertArrayEquals("Region is NorthEast. Test arrays equality", northEastRegion, zips);
            }
        }

        rs.close();

        MessageDisplayer.display("Done!");

    }
}