modules/core/src/test/java/org/apache/ignite/cache/store/jdbc/JdbcTypesDefaultTransformerTest.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.apache.ignite.cache.store.jdbc;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.UUID;
import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;
import org.junit.Test;

/**
 * Test for {@link JdbcTypesDefaultTransformer}.
 */
public class JdbcTypesDefaultTransformerTest extends GridCommonAbstractTest {
    /**
     * @throws Exception If failed.
     */
    @Test
    public void testTransformer() throws Exception {
        // Connection to H2.
        String jdbcUrl = "jdbc:h2:mem:JdbcTypesDefaultTransformerTest";
        String usr = "sa";
        String pwd = "";

        // Connection to Oracle.
        // -Duser.region=us -Duser.language=en
//        Class.forName("oracle.jdbc.OracleDriver");
//        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
//        String usr = "test";
//        String pwd = "test";

        // Connection to MS SQL.
//        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//        String jdbcUrl = "jdbc:sqlserver://localhost;databaseName=master";
//        String usr = "test";
//        String pwd = "test";

        // Connection to DB2.
//        Class.forName("com.ibm.db2.jcc.DB2Driver");
//        String jdbcUrl = "jdbc:db2://localhost:50000/sample";
//        String usr = "test";
//        String pwd = "test";

        // Connection to Postgre SQL.
//        Class.forName("org.postgresql.Driver");
//        String jdbcUrl = "jdbc:postgresql://localhost:5433/postgres";
//        String usr = "test";
//        String pwd = "test";

        // Connection to My SQL.
//        Class.forName("com.mysql.jdbc.Driver");
//        String jdbcUrl = "jdbc:mysql://localhost:3306/test";
//        String usr = "test";
//        String pwd = "test";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, usr, pwd)) {
            Statement stmt = conn.createStatement();

            try {
                stmt.executeUpdate("DROP TABLE TEST_TRANSFORMER");
            }
            catch (SQLException ignored) {
                // No-op.
            }

            // Create table in H2.
            stmt.executeUpdate("CREATE TABLE TEST_TRANSFORMER(id INTEGER, " +
                "c1 BOOLEAN, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, " +
                "c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 UUID)");

            // Create table in ORACLE.
//                stmt.executeUpdate("CREATE TABLE TEST_TRANSFORMER(id INTEGER, " +
//                    "c1 NUMBER(1), c2 INTEGER, c3 NUMBER(3), c4 NUMBER(4), c5 NUMBER(20), c6 NUMBER(20, 2), c7 NUMBER(20, 2), c8 NUMBER(10, 2), " +
//                    "c9 TIMESTAMP, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 VARCHAR(36))");

            // Create table in MS SQL.
//            stmt.executeUpdate("CREATE TABLE TEST_TRANSFORMER(id INTEGER, " +
//                "c1 BIT, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, " +
//                "c9 TIME, c10 DATE, c11 DATETIME, c12 VARCHAR(100), c13 VARCHAR(36))");

            // Create table in DB2.
//            stmt.executeUpdate("CREATE TABLE TEST_TRANSFORMER(id INTEGER, " +
//                "c1 SMALLINT , c2 INTEGER, c3 SMALLINT , c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, " +
//                "c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 VARCHAR(36))");

            // Create table in Postgre SQL.
//            stmt.executeUpdate("CREATE TABLE TEST_TRANSFORMER(id INTEGER, " +
//                "c1 BOOLEAN, c2 INTEGER, c3 SMALLINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, " +
//                "c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 UUID)");

            // Create table in MySQL.
//            stmt.executeUpdate("CREATE TABLE TEST_TRANSFORMER(id INTEGER, " +
//                "c1 BOOLEAN, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, " +
//                "c9 TIME, c10 DATE, c11 TIMESTAMP(3), c12 VARCHAR(100), c13 VARCHAR(36))");

            // Add data to H2, Postgre SQL and MySQL.
            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
                "VALUES (1, true, 1, 2, 3, 4, 5.35, 6.15, 7.32, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', " +
                "'100', '736bc956-090c-40d2-94da-916f2161f8a2')");
            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
                "VALUES (2, false, 10, 20, 30, 40, 50, 60, 70, current_time, current_date, current_timestamp, " +
                "'100.55', '736bc956-090c-40d2-94da-916f2161cdea')");

            // Add data to Oracle.
//            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
//                "VALUES (1, 1, 1, 2, 3, 4, 5.35, 6.15, 7.32, " +
//                "TO_TIMESTAMP('2016-01-01 00:01:08', 'YYYY-MM-DD HH24:MI:SS'), " +
//                "TO_DATE('2016-01-01', 'YYYY-MM-DD')," +
//                "TO_TIMESTAMP('2016-01-01 00:01:08.296', 'YYYY-MM-DD HH24:MI:SS.FF3'), " +
//                "'100', '736bc956-090c-40d2-94da-916f2161f8a2')");
//            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
//                "VALUES (2, 0, 10, 20, 30, 40, 50, 60, 70," +
//                "TO_TIMESTAMP('2016-01-01 00:01:08', 'YYYY-MM-DD HH24:MI:SS'), " +
//                "TO_DATE('2016-01-01', 'YYYY-MM-DD')," +
//                "TO_TIMESTAMP('2016-01-01 00:01:08.296', 'YYYY-MM-DD HH24:MI:SS.FF3'), " +
//                "'100.55', '736bc956-090c-40d2-94da-916f2161cdea')");

            // Add data to MS SQL or IBM DB2.
//            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
//                "VALUES (1, 1, 1, 2, 3, 4, 5.35, 6.15, 7.32, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', " +
//                "'100', '736bc956-090c-40d2-94da-916f2161f8a2')");
//            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
//                "VALUES (2, 0, 10, 20, 30, 40, 50, 60, 70, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', " +
//                "'100.55', '736bc956-090c-40d2-94da-916f2161cdea')");

            stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
                "VALUES (3, null, null, null, null, null, null, null, null, null, null, null, null, null)");

            ResultSet rs = stmt.executeQuery("select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13 from TEST_TRANSFORMER order by id");

            assertTrue(rs.next());

            JdbcTypesDefaultTransformer transformer = JdbcTypesDefaultTransformer.INSTANCE;

            // c1: Test BOOLEAN column.
            assertTrue((Boolean)transformer.getColumnValue(rs, 1, boolean.class));
            assertTrue((Boolean)transformer.getColumnValue(rs, 1, Boolean.class));
            assertEquals(1, transformer.getColumnValue(rs, 1, int.class));
            assertEquals(1.0, transformer.getColumnValue(rs, 1, double.class));

            String s = (String)transformer.getColumnValue(rs, 1, String.class);
            assertTrue("true".equalsIgnoreCase(s) || "1".equals(s) || "t".equalsIgnoreCase(s));

            // c2: Test INTEGER column.
            assertEquals(1, transformer.getColumnValue(rs, 2, int.class));
            assertEquals(1, transformer.getColumnValue(rs, 2, Integer.class));
            assertEquals(1L, transformer.getColumnValue(rs, 2, Long.class));
            assertEquals(1.0, transformer.getColumnValue(rs, 2, double.class));
            assertEquals("1", transformer.getColumnValue(rs, 2, String.class));

            // c3: Test TINYINT column.
            byte b = 2;
            assertEquals(b, transformer.getColumnValue(rs, 3, byte.class));
            assertEquals(b, transformer.getColumnValue(rs, 3, Byte.class));
            assertEquals(2.0, transformer.getColumnValue(rs, 3, double.class));
            assertEquals("2", transformer.getColumnValue(rs, 3, String.class));

            // c4: Test SMALLINT column.
            short shrt = 3;
            assertEquals(shrt, transformer.getColumnValue(rs, 4, short.class));
            assertEquals(shrt, transformer.getColumnValue(rs, 4, Short.class));
            assertEquals(3.0, transformer.getColumnValue(rs, 4, double.class));
            assertEquals("3", transformer.getColumnValue(rs, 4, String.class));

            // c5: Test BIGINT column.
            assertEquals(4L, transformer.getColumnValue(rs, 5, long.class));
            assertEquals(4L, transformer.getColumnValue(rs, 5, Long.class));
            assertEquals(4, transformer.getColumnValue(rs, 5, int.class));
            assertEquals(4, transformer.getColumnValue(rs, 5, Integer.class));
            assertEquals(4.0, transformer.getColumnValue(rs, 5, double.class));
            assertEquals("4", transformer.getColumnValue(rs, 5, String.class));
            assertEquals(new BigDecimal("4"), transformer.getColumnValue(rs, 5, BigDecimal.class));

            // c6: Test DECIMAL column.
            assertEquals(new BigDecimal("5.35"), transformer.getColumnValue(rs, 6, BigDecimal.class));
            assertEquals(5L, transformer.getColumnValue(rs, 6, long.class));
            assertEquals("5.35", transformer.getColumnValue(rs, 6, String.class));

            // c7: Test DOUBLE column.
            assertEquals(6.15, transformer.getColumnValue(rs, 7, double.class));
            assertEquals(6.15, transformer.getColumnValue(rs, 7, Double.class));
            assertEquals(6, transformer.getColumnValue(rs, 7, int.class));
            assertEquals(6, transformer.getColumnValue(rs, 7, Integer.class));
            assertTrue(transformer.getColumnValue(rs, 7, String.class).toString().startsWith("6.15"));

            // c8: Test REAL column.
            assertTrue((7.32f - (Float)transformer.getColumnValue(rs, 8, float.class)) < 0.01);
            assertTrue((7.32f - (Float)transformer.getColumnValue(rs, 8, Float.class)) < 0.01);
            assertTrue((7.32 - (Double)transformer.getColumnValue(rs, 8, double.class)) < 0.01);
            assertTrue((7.32 - (Double)transformer.getColumnValue(rs, 8, Double.class)) < 0.01);
            assertEquals(7, transformer.getColumnValue(rs, 8, int.class));
            assertEquals(7, transformer.getColumnValue(rs, 8, Integer.class));
            assertTrue(transformer.getColumnValue(rs, 8, String.class).toString().startsWith("7.32"));

            // c9: Test TIME column.
            assertTrue(transformer.getColumnValue(rs, 9, Time.class) instanceof Time);
            assertTrue(transformer.getColumnValue(rs, 9, String.class).toString().contains("00:01:08"));

            // c10: Test DATE column.
            assertTrue(transformer.getColumnValue(rs, 10, Date.class) instanceof Date);
            assertTrue(transformer.getColumnValue(rs, 10, String.class).toString().startsWith("2016-01-01"));

            // c11: Test TIMESTAMP column.
            transformer.getColumnValue(rs, 11, Timestamp.class);
            assertTrue(transformer.getColumnValue(rs, 11, String.class).toString().startsWith("2016-01-01 00:01:08.29"));

            // c12: Test VARCHAR column.
            assertEquals("100", transformer.getColumnValue(rs, 12, String.class));
            assertEquals(100, transformer.getColumnValue(rs, 12, int.class));

            // c13: Test UUID column.
            transformer.getColumnValue(rs, 13, UUID.class);
            assertEquals("736bc956-090c-40d2-94da-916f2161f8a2", transformer.getColumnValue(rs, 13, String.class));

            assertTrue(rs.next());

            // Test BOOLEAN column.
            assertFalse((Boolean)transformer.getColumnValue(rs, 1, boolean.class));
            assertFalse((Boolean)transformer.getColumnValue(rs, 1, Boolean.class));
            assertEquals(0, transformer.getColumnValue(rs, 1, int.class));
            assertEquals(0.0, transformer.getColumnValue(rs, 1, double.class));

            s = (String)transformer.getColumnValue(rs, 1, String.class);
            assertTrue("false".equalsIgnoreCase(s) || "0".equals(s) || "f".equalsIgnoreCase(s));

            assertTrue(rs.next());

            // Check how null values will be transformed.
            assertNotNull(transformer.getColumnValue(rs, 1, boolean.class));
            assertNull(transformer.getColumnValue(rs, 1, Boolean.class));

            assertEquals(0, transformer.getColumnValue(rs, 2, int.class));
            assertNull(transformer.getColumnValue(rs, 2, Integer.class));

            assertEquals((byte)0, transformer.getColumnValue(rs, 3, byte.class));
            assertNull(transformer.getColumnValue(rs, 3, Byte.class));

            assertEquals((short)0, transformer.getColumnValue(rs, 4, short.class));
            assertNull(transformer.getColumnValue(rs, 4, Short.class));

            assertEquals(0L, transformer.getColumnValue(rs, 5, long.class));
            assertNull(transformer.getColumnValue(rs, 5, Long.class));

            assertNull(transformer.getColumnValue(rs, 6, BigDecimal.class));

            assertEquals(0d, transformer.getColumnValue(rs, 7, double.class));
            assertNull(transformer.getColumnValue(rs, 7, Double.class));

            assertEquals(0f, transformer.getColumnValue(rs, 8, float.class));
            assertNull(transformer.getColumnValue(rs, 8, Float.class));

            assertNull(transformer.getColumnValue(rs, 9, Time.class));

            assertNull(transformer.getColumnValue(rs, 10, Date.class));

            assertNull(transformer.getColumnValue(rs, 11, Timestamp.class));

            assertNull(transformer.getColumnValue(rs, 12, String.class));

            assertNull(transformer.getColumnValue(rs, 13, UUID.class));
        }
    }
}