Examples with CloudSpannerJdbcConnection com.google.cloud.spanner.jdbc.CloudSpannerJdbcConnection used on opensource projects

Example 1 with CloudSpannerJdbcConnection

use of com.google.cloud.spanner.jdbc.CloudSpannerJdbcConnection in project java-docs-samples by GoogleCloudPlatform.

the class LoadCsvExample method writeToSpanner.

/**
 * Write CSV file data to Spanner using JDBC Mutation API *
 */
static void writeToSpanner(Iterable<CSVRecord> records, String tableName) throws SQLException {
    System.out.println("Writing data into table...");
    List<Mutation> mutations = new ArrayList<>();
    for (CSVRecord record : records) {
        int index = 0;
        WriteBuilder builder = Mutation.newInsertOrUpdateBuilder(tableName);
        for (String columnName : tableColumns.keySet()) {
            // Iterates through columns in order. Assumes in order columns when no headers provided.
            TypeCode columnType = tableColumns.get(columnName);
            String recordValue = null;
            if (validHeaderField(record, columnName)) {
                recordValue = record.get(columnName).trim();
            } else if (validNonHeaderField(record, index)) {
                recordValue = record.get(index).trim();
                index++;
            }
            if (recordValue != null) {
                switch(columnType) {
                    case STRING:
                        builder.set(columnName).to(recordValue);
                        break;
                    case BYTES:
                        builder.set(columnName).to(Byte.parseByte(recordValue));
                        break;
                    case INT64:
                        builder.set(columnName).to(Integer.parseInt(recordValue));
                        break;
                    case FLOAT64:
                        builder.set(columnName).to(Float.parseFloat(recordValue));
                        break;
                    case BOOL:
                        builder.set(columnName).to(Boolean.parseBoolean(recordValue));
                        break;
                    case NUMERIC:
                        builder.set(columnName).to(Value.numeric(BigDecimal.valueOf(Double.parseDouble(recordValue))));
                        break;
                    case DATE:
                        builder.set(columnName).to(com.google.cloud.Date.parseDate(recordValue));
                        break;
                    case TIMESTAMP:
                        builder.set(columnName).to(com.google.cloud.Timestamp.parseTimestamp(recordValue));
                        break;
                    default:
                        System.out.print("Invalid Type. This type is not supported.");
                }
            }
        }
        mutations.add(builder.build());
    }
    CloudSpannerJdbcConnection spannerConnection = connection.unwrap(CloudSpannerJdbcConnection.class);
    spannerConnection.write(mutations);
    spannerConnection.close();
    System.out.println("Data successfully written into table.");
}

Example 2 with CloudSpannerJdbcConnection

use of com.google.cloud.spanner.jdbc.CloudSpannerJdbcConnection in project java-docs-samples by GoogleCloudPlatform.

the class BufferedWriteExample method bufferedWrite.

// Use mutations to insert new records.
static void bufferedWrite(String projectId, String instanceId, String databaseId) throws SQLException {
    String connectionUrl = String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s", projectId, instanceId, databaseId);
    long singerId = 30;
    long albumId = 10;
    try (Connection connection = DriverManager.getConnection(connectionUrl)) {
        // Unwrap the Cloud Spanner specific interface to be able to access custom methods.
        CloudSpannerJdbcConnection spannerConnection = connection.unwrap(CloudSpannerJdbcConnection.class);
        spannerConnection.setAutoCommit(false);
        Mutation mutationSingers = Mutation.newInsertBuilder("Singers").set("SingerId").to(singerId).set("FirstName").to("Marvin").set("LastName").to("Mooney").set("Revenues").to(new BigDecimal("15600.98")).build();
        Mutation mutationAlbums = Mutation.newInsertBuilder("Albums").set("SingerId").to(singerId).set("AlbumId").to(albumId).set("AlbumTitle").to("Hand in hand").set("MarketingBudget").to(1000).build();
        spannerConnection.bufferedWrite(Arrays.asList(mutationSingers, mutationAlbums));
        spannerConnection.commit();
        System.out.printf("Transaction committed at [%s]%n", spannerConnection.getCommitTimestamp().toString());
    }
}

Example 3 with CloudSpannerJdbcConnection

use of com.google.cloud.spanner.jdbc.CloudSpannerJdbcConnection in project java-spanner-jdbc by googleapis.

the class ITJdbcScriptTest method test11_TestStatementTimeout.

@Test
public void test11_TestStatementTimeout() throws Exception {
    // Statements with set operations in subqueries are not supported
    assumeFalse(dialect.dialect == Dialect.POSTGRESQL);
    try (CloudSpannerJdbcConnection connection = createConnection(dialect.dialect)) {
        verifier.verifyStatementsInFile(JdbcGenericConnection.of(connection), dialect.executeQueriesFiles.get("TEST_STATEMENT_TIMEOUT"), ITAbstractJdbcTest.class, false);
    }
    try (Connection connection = createConnection(dialect.dialect)) {
        // Create a statement with a query timeout, but do not set a statement timeout on the
        // connection. SHOW STATEMENT_TIMEOUT should then return a null value.
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(1);
        statement.execute("SHOW VARIABLE STATEMENT_TIMEOUT");
        try (ResultSet rs = statement.getResultSet()) {
            assertThat(rs.next(), is(true));
            assertThat(rs.getString("STATEMENT_TIMEOUT"), is(nullValue()));
        }
        // Now set a STATEMENT_TIMEOUT on the connection that is different from the query timeout of
        // the statement. SHOW STATEMENT_TIMEOUT should now return the STATEMENT_TIMEOUT of the
        // connection.
        statement.execute("SET STATEMENT_TIMEOUT='100ms'");
        statement.execute("SHOW VARIABLE STATEMENT_TIMEOUT");
        try (ResultSet rs = statement.getResultSet()) {
            assertThat(rs.next(), is(true));
            assertThat(rs.getString("STATEMENT_TIMEOUT"), is(equalTo("100ms")));
        }
        // Remove the statement timeout again, and verify that SHOW STATEMENT_TIMEOUT once again
        // returns null.
        statement.execute("SET STATEMENT_TIMEOUT=NULL");
        statement.execute("SHOW VARIABLE STATEMENT_TIMEOUT");
        try (ResultSet rs = statement.getResultSet()) {
            assertThat(rs.next(), is(true));
            assertThat(rs.getString("STATEMENT_TIMEOUT"), is(nullValue()));
        }
    }
}

Example 4 with CloudSpannerJdbcConnection

use of com.google.cloud.spanner.jdbc.CloudSpannerJdbcConnection in project java-spanner-jdbc by googleapis.

the class ITJdbcConnectTest method testDefaultConnection.

private void testDefaultConnection(Connection connection) throws SQLException {
    assertThat(connection.isWrapperFor(CloudSpannerJdbcConnection.class)).isTrue();
    CloudSpannerJdbcConnection cs = connection.unwrap(CloudSpannerJdbcConnection.class);
    assertThat(cs.getAutoCommit()).isTrue();
    assertThat(cs.isReadOnly()).isFalse();
    try (ResultSet rs = connection.createStatement().executeQuery("SELECT 1")) {
        assertThat(rs.next()).isTrue();
        assertThat(rs.getInt(1)).isEqualTo(1);
    }
    cs.setAutoCommit(false);
    assertThat(cs.isRetryAbortsInternally()).isTrue();
}

Example 5 with CloudSpannerJdbcConnection

use of com.google.cloud.spanner.jdbc.CloudSpannerJdbcConnection in project java-docs-samples by GoogleCloudPlatform.

the class TransactionWithRetryLoopExample method transactionWithRetryLoop.

static void transactionWithRetryLoop(String projectId, String instanceId, String databaseId) throws SQLException {
    // Create a connection that has automatic retry for aborted transactions disabled.
    String connectionUrl = String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s" + ";retryAbortsInternally=false", projectId, instanceId, databaseId);
    long singerId = 31;
    long albumId = 11;
    try (Connection connection = DriverManager.getConnection(connectionUrl)) {
        while (true) {
            try {
                CloudSpannerJdbcConnection spannerConnection = connection.unwrap(CloudSpannerJdbcConnection.class);
                spannerConnection.setAutoCommit(false);
                Mutation mutationSingers = Mutation.newInsertBuilder("Singers").set("SingerId").to(singerId).set("FirstName").to("Breanna").set("LastName").to("Fountain").set("Revenues").to(new BigDecimal("29809.93")).build();
                Mutation mutationAlbums = Mutation.newInsertBuilder("Albums").set("SingerId").to(singerId).set("AlbumId").to(albumId).set("AlbumTitle").to("No discounts").set("MarketingBudget").to(1000).build();
                spannerConnection.bufferedWrite(Arrays.asList(mutationSingers, mutationAlbums));
                spannerConnection.commit();
                System.out.printf("Transaction committed at [%s]%n", spannerConnection.getCommitTimestamp().toString());
                break;
            } catch (JdbcAbortedException e) {
                // Rollback the current transaction to initiate a new transaction on the next statement.
                connection.rollback();
                // Transaction aborted, retry.
                System.out.println("Transaction aborted, starting retry");
            }
        }
    }
}