static void readOnlyTransaction(DatabaseClient dbClient) { // ReadOnlyTransaction must be closed by calling close() on it to release resources held by it. // We use a try-with-resource block to automatically do so. try (ReadOnlyTransaction transaction = dbClient.readOnlyTransaction()) { ResultSet queryResultSet = transaction.executeQuery( Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")); while (queryResultSet.next()) { System.out.printf( "%d %d %s\n", queryResultSet.getLong(0), queryResultSet.getLong(1), queryResultSet.getString(2)); } ResultSet readResultSet = transaction.read( "Albums", KeySet.all(), Arrays.asList("SingerId", "AlbumId", "AlbumTitle")); while (readResultSet.next()) { System.out.printf( "%d %d %s\n", readResultSet.getLong(0), readResultSet.getLong(1), readResultSet.getString(2)); } } } // [END spanner_read_only_transaction]
private ResultSet readPrimaryKeyInfo(ReadOnlyTransaction tx) { return tx.executeQuery( Statement.of( "SELECT t.table_name, t.column_name, t.column_ordering" + " FROM information_schema.index_columns AS t " + " WHERE t.index_name = 'PRIMARY_KEY' AND t.table_catalog = ''" + " AND t.table_schema = ''" + " ORDER BY t.table_name, t.ordinal_position")); } }
private ResultSet readTableInfo(ReadOnlyTransaction tx) { // retrieve schema information for all tables, as well as aggregating the // number of indexes that cover each column. this will be used to estimate // the number of cells (table column plus indexes) mutated in an upsert operation // in order to stay below the 20k threshold return tx.executeQuery( Statement.of( "SELECT" + " c.table_name" + " , c.column_name" + " , c.spanner_type" + " , (1 + COALESCE(t.indices, 0)) AS cells_mutated" + " FROM (" + " SELECT c.table_name, c.column_name, c.spanner_type, c.ordinal_position" + " FROM information_schema.columns as c" + " WHERE c.table_catalog = '' AND c.table_schema = '') AS c" + " LEFT OUTER JOIN (" + " SELECT t.table_name, t.column_name, COUNT(*) AS indices" + " FROM information_schema.index_columns AS t " + " WHERE t.index_name != 'PRIMARY_KEY' AND t.table_catalog = ''" + " AND t.table_schema = ''" + " GROUP BY t.table_name, t.column_name) AS t" + " USING (table_name, column_name)" + " ORDER BY c.table_name, c.ordinal_position")); }
private void preparePkMetadata(ReadOnlyTransaction tx, List<Struct> rows) { Type type = Type.struct( Type.StructField.of("table_name", Type.string()), Type.StructField.of("column_name", Type.string()), Type.StructField.of("column_ordering", Type.string())); when(tx.executeQuery( argThat( new ArgumentMatcher<Statement>() { @Override public boolean matches(Object argument) { if (!(argument instanceof Statement)) { return false; } Statement st = (Statement) argument; return st.getSql().contains("information_schema.index_columns"); } }))) .thenReturn(ResultSets.forRows(type, rows)); }
private void preparePkMetadata(ReadOnlyTransaction tx, List<Struct> rows) { Type type = Type.struct( Type.StructField.of("table_name", Type.string()), Type.StructField.of("column_name", Type.string()), Type.StructField.of("column_ordering", Type.string())); when(tx.executeQuery( argThat( new ArgumentMatcher<Statement>() { @Override public boolean matches(Object argument) { if (!(argument instanceof Statement)) { return false; } Statement st = (Statement) argument; return st.getSql().contains("information_schema.index_columns"); } }))) .thenReturn(ResultSets.forRows(type, rows)); }
private void prepareColumnMetadata(ReadOnlyTransaction tx, List<Struct> rows) { Type type = Type.struct( Type.StructField.of("table_name", Type.string()), Type.StructField.of("column_name", Type.string()), Type.StructField.of("spanner_type", Type.string()), Type.StructField.of("cells_mutated", Type.int64())); when(tx.executeQuery( argThat( new ArgumentMatcher<Statement>() { @Override public boolean matches(Object argument) { if (!(argument instanceof Statement)) { return false; } Statement st = (Statement) argument; return st.getSql().contains("information_schema.columns"); } }))) .thenReturn(ResultSets.forRows(type, rows)); }
private void prepareColumnMetadata(ReadOnlyTransaction tx, List<Struct> rows) { Type type = Type.struct( Type.StructField.of("table_name", Type.string()), Type.StructField.of("column_name", Type.string()), Type.StructField.of("spanner_type", Type.string()), Type.StructField.of("cells_mutated", Type.int64())); when(tx.executeQuery( argThat( new ArgumentMatcher<Statement>() { @Override public boolean matches(Object argument) { if (!(argument instanceof Statement)) { return false; } Statement st = (Statement) argument; return st.getSql().contains("information_schema.columns"); } }))) .thenReturn(ResultSets.forRows(type, rows)); }