Java Code Examples for java.sql.DriverManager#getConnection()
The following examples show how to use
java.sql.DriverManager#getConnection() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: NativeHBaseTypesTest.java From phoenix with BSD 3-Clause "New" or "Revised" License | 6 votes |
@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(); } }
Example 2
Source File: ProductMetricsTest.java From phoenix with BSD 3-Clause "New" or "Revised" License | 6 votes |
@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(); } }
Example 3
Source File: RowValueConstructorIT.java From phoenix with Apache License 2.0 | 6 votes |
@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(); } }
Example 4
Source File: LastValueFunctionIT.java From phoenix with Apache License 2.0 | 6 votes |
@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()); }
Example 5
Source File: MySQLSinkFunction.java From blog_demos with Apache License 2.0 | 6 votes |
/** * 准备好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(); } } } }
Example 6
Source File: JdbcCRUD.java From BigDataPlatform with GNU General Public License v3.0 | 5 votes |
/** * 测试删除数据 */ 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(); } } }
Example 7
Source File: QueryMoreIT.java From phoenix with Apache License 2.0 | 5 votes |
@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)); } }
Example 8
Source File: TenantSpecificViewIndexCompileTest.java From phoenix with Apache License 2.0 | 5 votes |
@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); }
Example 9
Source File: VariableLengthPKIT.java From phoenix with Apache License 2.0 | 5 votes |
@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(); } }
Example 10
Source File: QueryMoreIT.java From phoenix with Apache License 2.0 | 5 votes |
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(); } } }
Example 11
Source File: ScannerLeaseRenewalIT.java From phoenix with Apache License 2.0 | 5 votes |
@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); } }
Example 12
Source File: QueryOptimizerTest.java From phoenix with BSD 3-Clause "New" or "Revised" License | 5 votes |
@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()); }
Example 13
Source File: QueryTest.java From phoenix with BSD 3-Clause "New" or "Revised" License | 5 votes |
@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(); } }
Example 14
Source File: ConnectionPokemon.java From dctb-utfpr-2018-1 with Apache License 2.0 | 5 votes |
public Connection getConnection(){ try { return DriverManager.getConnection("jdbc:mysql://localhost:3306/atividade_06?", "root","12345"); } catch (Exception e) { throw new RuntimeException(e); } }
Example 15
Source File: AggregateQueryIT.java From phoenix with Apache License 2.0 | 4 votes |
@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(); } }
Example 16
Source File: DeleteIT.java From phoenix with Apache License 2.0 | 4 votes |
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) { } } }
Example 17
Source File: BaseAggregateIT.java From phoenix with Apache License 2.0 | 4 votes |
@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); }
Example 18
Source File: UpsertBigValuesIT.java From phoenix with Apache License 2.0 | 4 votes |
@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()); */ }
Example 19
Source File: OperateSystemDBImpl.java From tmxeditor8 with GNU General Public License v2.0 | 4 votes |
@Override protected Connection getConnection(String driver, String url, Properties prop) throws ClassNotFoundException, SQLException { Class.forName(driver); return DriverManager.getConnection(url, prop); }
Example 20
Source File: ImmutableIndexIT.java From phoenix with Apache License 2.0 | 4 votes |
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()); } }