Java Code Examples for java.sql.DriverManager#getConnection()

@Test
public void testRangeQuery1() throws Exception {
    String query = "SELECT uint_key, ulong_key, string_key FROM HBASE_NATIVE WHERE uint_key > 20 and ulong_key >= 400";
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(40, rs.getInt(1));
        assertEquals(400L, rs.getLong(2));
        assertEquals("d", rs.getString(3));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtractionCompareNumber() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and ? - date > 3"; 
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 6 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testRVCWithInListClausePossibleNullValues() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
    //we have a row present in aTable where x_integer = 5 and y_integer = NULL which gets translated to 0 when retriving from HBase. 
    String query = "SELECT x_integer, y_integer FROM aTable WHERE ? = organization_id AND (x_integer, y_integer) IN ((5))";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(5, rs.getInt(1));
        assertEquals(0, rs.getInt(2));
    } finally {
        conn.close();
    }
}
 

@Test
public void unsignedInteger() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());

    String ddl = "CREATE TABLE IF NOT EXISTS last_test_table "
            + "(id INTEGER NOT NULL PRIMARY KEY, page_id UNSIGNED_LONG,"
            + " date UNSIGNED_INT, \"value\" UNSIGNED_INT)";
    conn.createStatement().execute(ddl);

    conn.createStatement().execute("UPSERT INTO last_test_table (id, page_id, date, \"value\") VALUES (1, 8, 1, 3)");
    conn.createStatement().execute("UPSERT INTO last_test_table (id, page_id, date, \"value\") VALUES (2, 8, 2, 7)");
    conn.createStatement().execute("UPSERT INTO last_test_table (id, page_id, date, \"value\") VALUES (3, 8, 3, 9)");
    conn.createStatement().execute("UPSERT INTO last_test_table (id, page_id, date, \"value\") VALUES (5, 8, 4, 2)");
    conn.createStatement().execute("UPSERT INTO last_test_table (id, page_id, date, \"value\") VALUES (4, 8, 5, 4)");
    conn.commit();

    ResultSet rs = conn.createStatement().executeQuery(
            "SELECT LAST_VALUE(\"value\") WITHIN GROUP (ORDER BY date ASC) FROM last_test_table GROUP BY page_id"
    );
    assertTrue(rs.next());
    assertEquals(rs.getInt(1), 4);
    assertFalse(rs.next());
}
 

/**
 * 准备好connection和preparedStatement
 * 获取mysql连接实例，考虑多线程同步，
 * 不用synchronize是因为获取数据库连接是远程操作，耗时不确定
 * @return
 */
private void buildPreparedStatement() {
    if(null==connection) {
        boolean hasLock = false;
        try {
            hasLock = reentrantLock.tryLock(10, TimeUnit.SECONDS);

            if(hasLock) {
                Class.forName("com.mysql.cj.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://192.168.50.43:3306/flinkdemo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC", "root", "123456");
            }

            if(null!=connection) {
                preparedStatement = connection.prepareStatement("insert into student (name, age) values (?, ?)");
            }
        } catch (Exception e) {
            //生产环境慎用
            e.printStackTrace();
        } finally {
            if(hasLock) {
                reentrantLock.unlock();
            }
        }
    }
}
 

/**
 * 测试删除数据
 */
private static void delete() {
	Connection conn = null;
	Statement stmt = null;
	
	try {
		Class.forName("com.mysql.jdbc.Driver");  
		
		conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/compute-realtime-view",
				"root", 
				"root"); 
		stmt = conn.createStatement();
		
		String sql = "delete from test_user where name='李四'";
		int rtn = stmt.executeUpdate(sql);
		
		System.out.println("SQL语句影响了【" + rtn + "】行。");  
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if(stmt != null) {
				stmt.close();
			} 
			if(conn != null) {
				conn.close();
			}
		} catch (Exception e2) {
			e2.printStackTrace(); 
		}
	}
}
 

@SuppressWarnings("deprecation")
@Test
public void testNullBigDecimalWithScale() throws Exception {
    final String table = generateUniqueName();
    final Connection conn = DriverManager.getConnection(getUrl());
    conn.setAutoCommit(true);
    try (Statement stmt = conn.createStatement()) {
        assertFalse(stmt.execute("CREATE TABLE IF NOT EXISTS " + table + " (\n" +
            "PK VARCHAR(15) NOT NULL\n," +
            "\"DEC\" DECIMAL,\n" +
            "CONSTRAINT TABLE_PK PRIMARY KEY (PK))"));
    }

    try (PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (PK, \"DEC\") VALUES(?, ?)")) {
        stmt.setString(1, "key");
        stmt.setBigDecimal(2, null);
        assertFalse(stmt.execute());
        assertEquals(1, stmt.getUpdateCount());
    }

    try (Statement stmt = conn.createStatement()) {
        final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("key", rs.getString(1));
        assertNull(rs.getBigDecimal(2));
        assertNull(rs.getBigDecimal(2, 10));
    }
}
 

@Test
public void testOrderByOptimizedOutWithMultiplePredicatesInView() throws Exception {
    // Arrange
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute("CREATE TABLE t(t_id CHAR(15) NOT NULL, k1 CHAR(3) NOT NULL, k2 CHAR(5) NOT NULL, k3 DATE NOT NULL, v1 VARCHAR," +
            " CONSTRAINT pk PRIMARY KEY(t_id, k1, k2, k3 DESC)) multi_tenant=true");
    conn.createStatement().execute("CREATE VIEW v1  AS SELECT * FROM t WHERE k1 = 'xyz' AND k2='abcde'");
    conn = createTenantSpecificConnection();

    // Query without predicate ordered by full row key
    String sql = "SELECT * FROM v1 ORDER BY k3 DESC";
    String expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','abcde']"; 
    assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput);
    assertOrderByHasBeenOptimizedOut(conn, sql);
    
    // Query without predicate ordered by full row key, but without column view predicate
    sql = "SELECT * FROM v1 ORDER BY k3 DESC";
    expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','abcde']"; 
    assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput);
    assertOrderByHasBeenOptimizedOut(conn, sql);

    // Query with predicate ordered by full row key
    sql = "SELECT * FROM v1 WHERE k3 <= TO_DATE('" + createStaticDate() + "') ORDER BY k3 DESC";
    expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','abcde',~'2015-01-01 08:00:00.000'] - ['tenant123456789','xyz','abcde',*]";
    assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput);
    assertOrderByHasBeenOptimizedOut(conn, sql);

    // Query with predicate ordered by full row key with date in reverse order
    sql = "SELECT * FROM v1 WHERE k3 <= TO_DATE('" + createStaticDate() + "') ORDER BY k3";
    expectedExplainOutput = "CLIENT PARALLEL 1-WAY REVERSE RANGE SCAN OVER T ['tenant123456789','xyz','abcde',~'2015-01-01 08:00:00.000'] - ['tenant123456789','xyz','abcde',*]";
    assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput);
    assertOrderByHasBeenOptimizedOut(conn, sql);

}
 

@Test
public void testMultiColumnGTScanKey() throws Exception {
    long ts = nextTimestamp();
    // TODO: add compile test to confirm start/stop scan key
    String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE A_STRING=? AND A_ID=? AND B_STRING>?";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        initTableValues(null, ts);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, "abcd");
        statement.setString(2, "222");
        statement.setString(3, "xy");
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("abcd", rs.getString(1));
        assertEquals("2", rs.getString(2));
        assertEquals("xyz", rs.getString(3));
        assertEquals(3, rs.getInt(4));
        assertEquals(10, rs.getInt(5));
        assertTrue(rs.next());
        assertEquals("abcd", rs.getString(1));
        assertEquals("2", rs.getString(2));
        assertEquals("xyzz", rs.getString(3));
        assertEquals(4, rs.getInt(4));
        assertEquals(40, rs.getInt(5));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

private int upsertSelectRecordsInCursorTableForTenant(String tableOrViewName, boolean queryAgainstTenantView, String tenantId, String cursorQueryId,
    final String cursorTable) throws Exception {
    String sequenceName = "\"" + tenantId + "_SEQ\"";
    Connection conn = queryAgainstTenantView ? getTenantSpecificConnection(tenantId) : DriverManager.getConnection(getUrl());
    
    // Create a sequence. This sequence is used to fill cursor_order column for each row inserted in the cursor table.
    conn.createStatement().execute("CREATE SEQUENCE " + sequenceName + " CACHE " + Long.MAX_VALUE);
    conn.setAutoCommit(true);
    if (queryAgainstTenantView) {
        createTenantSpecificViewIfNecessary(tableOrViewName, conn);
    }
    try {
        String tenantIdFilter = queryAgainstTenantView ? "" : " WHERE TENANT_ID = ? ";
        
        // Using dynamic columns, we can use the same cursor table for storing primary keys for all the tables.  
        String upsertSelectDML = "UPSERT INTO " + cursorTable + " " +
                                 "(TENANT_ID, QUERY_ID, CURSOR_ORDER, PARENT_ID CHAR(15), CREATED_DATE DATE, ENTITY_HISTORY_ID CHAR(15)) " + 
                                 "SELECT ?, ?, NEXT VALUE FOR " + sequenceName + ", PARENT_ID, CREATED_DATE, ENTITY_HISTORY_ID " +
                                 " FROM " + tableOrViewName + tenantIdFilter;
        
        PreparedStatement stmt = conn.prepareStatement(upsertSelectDML);
        stmt.setString(1, tenantId);
        stmt.setString(2, cursorQueryId);
        if (!queryAgainstTenantView)  {
            stmt.setString(3, tenantId);
        }
        int numRecords = stmt.executeUpdate();
        return numRecords;
    } finally {
        try {
            conn.createStatement().execute("DROP SEQUENCE " + sequenceName);
        } finally {
            conn.close();
        }
    }
}
 

@Test
public void testRenewLeasePreventsUpsertSelectFromFailing() throws Exception {
    String table1 = "testRenewLeasePreventsUpsertSelectFromFailing";
    String table2 = "testRenewLeasePreventsUpsertSelectFromFailing2";

    try (Connection conn = DriverManager.getConnection(url)) {
        conn.createStatement().execute(
            "CREATE TABLE " + table1 + " (PK1 INTEGER NOT NULL PRIMARY KEY, KV1 VARCHAR)");
        conn.createStatement().execute(
            "CREATE TABLE " + table2 + " (PK1 INTEGER NOT NULL PRIMARY KEY, KV1 VARCHAR)");
        int numRecords = 5;
        int i = 0;
        String upsert = "UPSERT INTO " + table1 + " VALUES (?, ?)";
        Random random = new Random();
        PreparedStatement stmt = conn.prepareStatement(upsert);
        while (i < numRecords) {
            stmt.setInt(1, random.nextInt());
            stmt.setString(2, "KV" + random.nextInt());
            stmt.executeUpdate();
            i++;
        }
        conn.commit();
    }

    try (PhoenixConnection phxConn =
            DriverManager.getConnection(url).unwrap(PhoenixConnection.class)) {
        String upsertSelect = "UPSERT INTO " + table2 + " SELECT PK1, KV1 FROM " + table1;
        // at every next call wait for this period. This will cause lease to expire.
        long delayAfterInit = 2 * LEASE_TIMEOUT_PERIOD_MILLIS;
        phxConn.setTableResultIteratorFactory(new DelayedTableResultIteratorFactory(
                delayAfterInit));
        Statement s = phxConn.createStatement();
        s.setFetchSize(2);
        s.executeUpdate(upsertSelect);
    }
}
 

@Test
public void testOrderByDroppedCompositeKey() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute("CREATE TABLE foo (j INTEGER NOT NULL, k BIGINT NOT NULL, v VARCHAR CONSTRAINT pk PRIMARY KEY (j,k)) IMMUTABLE_ROWS=true");
    PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
    QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY j,k");
    assertEquals(OrderBy.FWD_ROW_KEY_ORDER_BY,plan.getOrderBy());
}
 

@Test
public void testIntToDecimalMultiplyExpression() throws Exception {
    String query = "SELECT entity_id FROM aTable where A_INTEGER * 1.5 > 9";
    Properties props = new Properties(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertValueEqualsResultSet(rs, Arrays.<Object>asList(ROW7, ROW8, ROW9));
    } finally {
        conn.close();
    }
}
 

public Connection getConnection(){
    try {
        return
        DriverManager.getConnection("jdbc:mysql://localhost:3306/atividade_06?", "root","12345");
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
 

@Test
public void testSplitWithCachedMeta() throws Exception {
    // Tests that you don't get an ambiguous column exception when using the same alias as the column name
    String query = "SELECT a_string, b_string, count(1) FROM atable WHERE organization_id=? and entity_id<=? GROUP BY a_string,b_string";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(getUrl(), props);
    HBaseAdmin admin = null;
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, ROW4);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(B_VALUE, rs.getString(2));
        assertEquals(2, rs.getLong(3));
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(C_VALUE, rs.getString(2));
        assertEquals(1, rs.getLong(3));
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(E_VALUE, rs.getString(2));
        assertEquals(1, rs.getLong(3));
        assertFalse(rs.next());
        
        byte[] tableName = Bytes.toBytes(ATABLE_NAME);
        admin = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin();
        HTable htable = (HTable) conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(tableName);
        htable.clearRegionCache();
        int nRegions = htable.getRegionLocations().size();
        if(!admin.tableExists(TableName.valueOf(MetaDataUtil.getLocalIndexTableName(ATABLE_NAME)))) {
            admin.split(tableName, ByteUtil.concat(Bytes.toBytes(tenantId), Bytes.toBytes("00A" + Character.valueOf((char) ('3' + nextRunCount())) + ts))); // vary split point with test run
            int retryCount = 0;
            do {
                Thread.sleep(2000);
                retryCount++;
                //htable.clearRegionCache();
            } while (retryCount < 10 && htable.getRegionLocations().size() == nRegions);
            assertNotEquals(nRegions, htable.getRegionLocations().size());
        } 
        
        statement.setString(1, tenantId);
        rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(B_VALUE, rs.getString(2));
        assertEquals(2, rs.getLong(3));
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(C_VALUE, rs.getString(2));
        assertEquals(1, rs.getLong(3));
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(E_VALUE, rs.getString(2));
       assertEquals(1, rs.getLong(3));
        assertFalse(rs.next());
    } finally {
        if (admin != null) {
        admin.close();
        }
        conn.close();
    }
}
 

private void testDeleteAllFromTableWithIndex(boolean autoCommit, boolean isSalted, boolean localIndex) throws SQLException {
    Connection con = null;
    try {
        con = DriverManager.getConnection(getUrl());
        con.setAutoCommit(autoCommit);

        Statement stm = con.createStatement();
        String s = "CREATE TABLE IF NOT EXISTS web_stats (" +
                "HOST CHAR(2) NOT NULL," +
                "DOMAIN VARCHAR NOT NULL, " +
                "FEATURE VARCHAR NOT NULL, " +
                "DATE DATE NOT NULL, \n" + 
                "USAGE.CORE BIGINT," +
                "USAGE.DB BIGINT," +
                "STATS.ACTIVE_VISITOR INTEGER " +
                "CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE))" + (isSalted ? " SALT_BUCKETS=3" : "");
        stm.execute(s);
        if (localIndex) {
            stm.execute("CREATE LOCAL INDEX local_web_stats_idx ON web_stats (CORE,DB,ACTIVE_VISITOR)");
        } else {
            stm.execute("CREATE INDEX web_stats_idx ON web_stats (CORE,DB,ACTIVE_VISITOR)");
        }
        stm.close();

        PreparedStatement psInsert = con
                .prepareStatement("UPSERT INTO web_stats(HOST, DOMAIN, FEATURE, DATE, CORE, DB, ACTIVE_VISITOR) VALUES(?,?, ? , ?, ?, ?, ?)");
        psInsert.setString(1, "AA");
        psInsert.setString(2, "BB");
        psInsert.setString(3, "CC");
        psInsert.setDate(4, new Date(0));
        psInsert.setLong(5, 1L);
        psInsert.setLong(6, 2L);
        psInsert.setLong(7, 3);
        psInsert.execute();
        psInsert.close();
        if (!autoCommit) {
            con.commit();
        }
        
        con.createStatement().execute("DELETE FROM web_stats");
        if (!autoCommit) {
            con.commit();
        }
        
        ResultSet rs = con.createStatement().executeQuery("SELECT /*+ NO_INDEX */ count(*) FROM web_stats");
        assertTrue(rs.next());
        assertEquals(0, rs.getLong(1));
        if(localIndex){
            rs = con.createStatement().executeQuery("SELECT count(*) FROM local_web_stats_idx");
        } else {
            rs = con.createStatement().executeQuery("SELECT count(*) FROM web_stats_idx");
        }
        assertTrue(rs.next());
        assertEquals(0, rs.getLong(1));

    } finally {
        try {
            con.close();
        } catch (Exception ex) {
        }
    }
}
 

@Test
public void testSumGroupByOrderPreservingDesc() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String tableName = generateUniqueName();

    PreparedStatement stmt = conn.prepareStatement("CREATE TABLE " + tableName + " (k1 char(1) not null, k2 integer not null, constraint pk primary key (k1,k2)) split on (?,?,?)");
    stmt.setBytes(1, ByteUtil.concat(PChar.INSTANCE.toBytes("a"), PInteger.INSTANCE.toBytes(3)));
    stmt.setBytes(2, ByteUtil.concat(PChar.INSTANCE.toBytes("j"), PInteger.INSTANCE.toBytes(3)));
    stmt.setBytes(3, ByteUtil.concat(PChar.INSTANCE.toBytes("n"), PInteger.INSTANCE.toBytes(3)));
    stmt.execute();
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 1)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 2)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 3)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 4)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('b', 5)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 1)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 2)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 3)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 4)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 1)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 2)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 3)");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 4)");
    conn.commit();
    QueryBuilder queryBuilder = new QueryBuilder()
        .setSelectExpression("K1,SUM(K2)")
        .setSelectExpressionColumns(Lists.newArrayList("K1", "K2"))
        .setFullTableName(tableName)
        .setGroupByClause("K1")
        .setOrderByClause("K1 DESC");
    ResultSet rs = executeQuery(conn, queryBuilder);
    assertTrue(rs.next());
    assertEquals("n", rs.getString(1));
    assertEquals(10, rs.getLong(2));
    assertTrue(rs.next());
    assertEquals("j", rs.getString(1));
    assertEquals(10, rs.getLong(2));
    assertTrue(rs.next());
    assertEquals("b", rs.getString(1));
    assertEquals(5, rs.getLong(2));
    assertTrue(rs.next());
    assertEquals("a", rs.getString(1));
    assertEquals(10, rs.getLong(2));
    assertFalse(rs.next());
    String expectedPhoenixPlan = "CLIENT PARALLEL 1-WAY REVERSE FULL SCAN OVER " + tableName + "\n" +
            "    SERVER FILTER BY FIRST KEY ONLY\n" +
            "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]";
    validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null);
}
 

@Test
public void testBigIntPK() throws Exception {
  // NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of 
  // bigint is not recognizable in the current version. As a result, we start with 
  // Long.MIN_VALUE+1 as the smallest value.
    long[] testNumbers = {Long.MIN_VALUE+1 , Long.MIN_VALUE+2 , 
            -2L, -1L, 0L, 1L, 2L, Long.MAX_VALUE-1, Long.MAX_VALUE};
    ensureTableCreated(getUrl(),"PKBigIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String upsert = "UPSERT INTO PKBigIntValueTest VALUES(?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i=0; i<testNumbers.length; i++) {
        stmt.setLong(1, testNumbers[i]);
        stmt.execute();
    }
    conn.commit();
    conn.close();
    
    String select = "SELECT COUNT(*) from PKBigIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    
    select = "SELECT count(*) FROM PKBigIntValueTest where pk >= " + (Long.MIN_VALUE + 1);
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT pk FROM PKBigIntValueTest WHERE pk >= " + (Long.MIN_VALUE + 1) +
            " GROUP BY pk ORDER BY pk ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());
    
    select = "SELECT count(*) FROM PKBigIntValueTest where pk <= " + Long.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT pk FROM PKBigIntValueTest WHERE pk <= " + Long.MAX_VALUE + 
            " GROUP BY pk ORDER BY pk DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());
    
    /* NOTE: This section currently fails due to the fact that we cannot parse literal values
       that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
       before enabling this section of the test.
    select = "SELECT count(*) FROM PKBigIntValueTest where pk >= " + LONG_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT pk FROM PKBigIntValueTest WHERE pk >= " + LONG_MIN_MINUS_ONE +
            " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());
    
    select = "SELECT count(*) FROM PKBigIntValueTest where pk <= " + LONG_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT pk FROM PKBigIntValueTest WHERE pk <= " + LONG_MAX_PLUS_ONE +
            " GROUP BY pk ORDER BY pk DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length-1; i >= 0; i--) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());
    */
}
 

@Override
protected Connection getConnection(String driver, String url, Properties prop) throws ClassNotFoundException,
		SQLException {
	Class.forName(driver);
	return DriverManager.getConnection(url, prop);
}
 

public void testIndexWithNullableFixedWithCols(boolean localIndex) throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);
    ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
    populateTestTable();
    String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
                + " (char_col1 ASC, int_col1 ASC)"
                + " INCLUDE (long_col1, long_col2)";
    PreparedStatement stmt = conn.prepareStatement(ddl);
    stmt.execute();
    
    String query = "SELECT char_col1, int_col1 from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE;
    ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
    if(localIndex) {
        assertEquals(
            "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_INDEX_TEST.INDEX_DATA_TABLE [-32768]\n" + 
            "    SERVER FILTER BY FIRST KEY ONLY\n" +
            "CLIENT MERGE SORT",
            QueryUtil.getExplainPlan(rs));
    } else {
        assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX\n"
                + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
    }
    
    rs = conn.createStatement().executeQuery(query);
    assertTrue(rs.next());
    assertEquals("chara", rs.getString(1));
    assertEquals(2, rs.getInt(2));
    assertTrue(rs.next());
    assertEquals("chara", rs.getString(1));
    assertEquals(3, rs.getInt(2));
    assertTrue(rs.next());
    assertEquals("chara", rs.getString(1));
    assertEquals(4, rs.getInt(2));
    assertFalse(rs.next());
    
    conn.createStatement().execute("DROP INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE);
    
    query = "SELECT char_col1, int_col1 from " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE;
    rs = conn.createStatement().executeQuery(query);
    assertTrue(rs.next());
    
    query = "SELECT char_col1, int_col1 from IDX ";
    try{
        rs = conn.createStatement().executeQuery(query);
        fail();
    } catch (SQLException e) {
        assertEquals(SQLExceptionCode.TABLE_UNDEFINED.getErrorCode(), e.getErrorCode());
    }
    
    
}