Java Code Examples for java.sql.Connection#setReadOnly()

private void assertConnectionPreClose(String dsName, Connection conn)
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

@Test
public void shouldDelegateToReadDataSource_readOnly_true_statement() throws Exception {
    Connection connection = lazyReplicationConnectionDataSourceProxy.getConnection();
    connection.setReadOnly(true);

    Statement statement = connection.createStatement();
    statement.close();
    connection.close();
    
    verify(readDataSource).getConnection();
    verify(readConnection).createStatement();
    verify(readConnection).close();
    verify(readStatement).close();
    
    verifyZeroInteractions(writeDataSource, writeConnection);
}
 

private void assertConnectionPreClose(String dsName, Connection conn) 
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

@Override
public void loadWorldBan(Player player) {
	Connection con = null;
	try {
		con = DatabaseFactory.getConnection();
		con.setReadOnly(false);
		PreparedStatement stmt = con.prepareStatement("SELECT * FROM player_world_bans WHERE `player` = ?");
		stmt.setInt(1, player.getObjectId());
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			player.setBannedFromWorld(rs.getString("by"), rs.getString("reason"), rs.getLong("duration"), new Date(rs.getLong("date")));
		}
		rs.close();
		stmt.close();
	}
	catch (MySQLDataException mde) {
	}
	catch (Exception e) {
		log.error("cannot load world ban for player #" + player.getObjectId());
		log.warn(e.getMessage());
	}
	finally {
		DatabaseFactory.close(con);
	}
}
 

@Override
protected void setupDefaults(final Connection connection, final String userName) throws SQLException {
    final Boolean defaultAutoCommit = isDefaultAutoCommit();
    if (defaultAutoCommit != null && connection.getAutoCommit() != defaultAutoCommit.booleanValue()) {
        connection.setAutoCommit(defaultAutoCommit.booleanValue());
    }

    final int defaultTransactionIsolation = getDefaultTransactionIsolation();
    if (defaultTransactionIsolation != UNKNOWN_TRANSACTIONISOLATION) {
        connection.setTransactionIsolation(defaultTransactionIsolation);
    }

    final Boolean defaultReadOnly = isDefaultReadOnly();
    if (defaultReadOnly != null && connection.isReadOnly() != defaultReadOnly.booleanValue()) {
        connection.setReadOnly(defaultReadOnly.booleanValue());
    }
}
 

private void assertConnectionPreClose(String dsName, Connection conn)
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

@Test
public void shouldDelegateGetConnectionWithUsername_readOnly_true_statement() throws Exception {
    final String expectedUsername = "myusername";
    final String expectedPassword = "mypassword";
    
    given(writeDataSource.getConnection(expectedUsername, expectedPassword)).willReturn(writeConnection);
    
    Connection connection = lazyReplicationConnectionDataSourceProxy.getConnection(expectedUsername, expectedPassword);
    connection.setReadOnly(false);

    Statement statement = connection.createStatement();
    statement.close();
    connection.close();

    verify(writeDataSource).getConnection(expectedUsername, expectedPassword);
    verify(writeConnection).createStatement();
    verify(writeConnection).close();
    verify(writeStatement).close();

    verifyZeroInteractions(readDataSource, readConnection);
}
 

@Test
public void shouldDelegateToReadDataSource_readOnly_true_statement() throws Exception {
    Connection connection = lazyReplicationConnectionDataSourceProxy.getConnection();
    connection.setReadOnly(true);

    Statement statement = connection.createStatement();
    statement.close();
    connection.close();
    
    verify(readDataSource).getConnection();
    verify(readConnection).createStatement();
    verify(readConnection).close();
    verify(readStatement).close();
    
    verifyZeroInteractions(writeDataSource, writeConnection);
}
 

private void assertConnectionPreClose(String dsName, Connection conn) 
throws SQLException {

    // before closing the connection, attempt to change holdability
    // and readOnly
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    if (!dsName.equals("Nested2"))
    {
        try {
            conn.setReadOnly(true);
        } catch (SQLException sqle) {
            // cannot set read-only in an active transaction, & sometimes
            // connections are active at this point.
            assertSQLState("25501", sqle);
        }
    }
}
 

public void run() {
  if (url.isEmpty()) {
    logger.severe("Connection url not provided for JDBCRowLoader");
    return;
  }
  try {
    Connection con = getDatabaseConnection();
    logger.info(" Successful connection to target database: " + url);
    con.setReadOnly(true);
    PreparedStatement pstmt = con.prepareStatement(buildQueryString(schema,
        table));
    recyclePooledStatement(pstmt);
  } catch (SQLException e) {
    // Connection count is incremented when the job is scheduled.
    // Since it has failed, decrement the counter
    synchronized (connectionCount) {
      connectionCount = connectionCount - 1;
    }
    logger.log(Level.SEVERE, "Error connecting to target database", e);
  }
}
 

public <T> T execute(boolean isReadOnly, SqlExecutor<T> executor) throws SQLException {
    Connection conn = null;
    try {
        conn = dataSource.getConnection();
        if (isReadOnly) {
            conn.setReadOnly(true);
        }
        return executor.run(conn);
    } finally {
        close(conn);
    }
}
 

static void closeTx(Connection conn) {
    try {
        if (conn != null) {
            if (conn.isReadOnly()) {
                conn.setReadOnly(false);  // restore NOT readOnly before return to pool
            }
            conn.close();
        }
    } catch (SQLException e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        TRANSACT_CONN.set(null);
    }
}
 

ExecutorJdbcConnectionHolder(Set<Stand> standSet) {
    for (Stand stand : standSet) {
        if (isNotEmpty(stand.getDbUrl())) {
            try {
                Connection connection = DriverManager.getConnection(stand.getDbUrl(), stand.getDbUser(), stand.getDbPassword());
                connection.setAutoCommit(false);
                connection.setReadOnly(true);
                standConnectionMap.put(stand, connection);
            } catch (SQLException e) {
                log.warn("sql exception", e);
                standConnectionMap.put(stand, null);
            }
        }
    }
}
 

/**
 * Return the target Connection, fetching it and initializing it if
 * necessary.
 */
private Connection getTargetConnection(Method operation) throws SQLException {
	// No target Connection held -> fetch one.
	if (logger.isDebugEnabled()) {
		logger.debug("Connecting to database for operation '" + operation.getName() + "'");
	}

	// Fetch physical Connection from DataSource.
	Connection target = (this.username != null)
			? dataSourceRout.getTargetDataSource().getConnection(this.username, this.password)
			: dataSourceRout.getTargetDataSource().getConnection();

	// Apply kept transaction settings, if any.
	if (this.readOnly) {
		try {
			target.setReadOnly(this.readOnly);
		} catch (Exception ex) {
			// "read-only not supported" -> ignore, it's just a hint
			// anyway
			logger.debug("Could not set JDBC Connection read-only", ex);
		}
	}
	if (this.transactionIsolation != null && !this.transactionIsolation.equals(defaultTransactionIsolation())) {
		target.setTransactionIsolation(this.transactionIsolation);
	}
	if (ConnectionHold.READ.equals(ConnectionHold.CURRENT_CONNECTION.get())) {
		try {
			target.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	if (this.autoCommit != null && this.autoCommit != target.getAutoCommit()) {
		if (ConnectionHold.WRITE.equals(ConnectionHold.CURRENT_CONNECTION.get())) {
			target.setAutoCommit(this.autoCommit);
		}
	}
	return target;
}
 

@Override
public Connection getConnection(JdbcIdentity identity, JdbcSplit split)
        throws SQLException
{
    Connection connection = connectionFactory.openConnection(identity);
    try {
        connection.setReadOnly(true);
    }
    catch (SQLException e) {
        connection.close();
        throw e;
    }
    return connection;
}
 

/**
    * Put the Connection in auto-commit mode and in read only false
    * 
    * @param connection
    *            the JDBC Connection to init
    * @throws SQLException
    *             if any SQL Exception occurs
    */
   public static void connectionInit(Connection connection)
    throws SQLException {

// Make sure Connection extracted from the pool is always on autocommit
// mode
// This avoid for client side to send a connection.getAutoCommit()
// before
// starting working.
// This is anyway mandatory for C# as all Connections are per default
// auto commit mode.
if (!connection.getAutoCommit()) {
    connection.rollback();
    connection.setAutoCommit(true);
}

// Make sure we are not in read only. Don't trap Exception because of
// Drivers not supporting this call
if (connection.isReadOnly()) {
    try {
	connection.setReadOnly(false);
    } catch (Exception e) {
	// Ignore
	System.err.println(e.toString());
    }
}
   }
 

/**
 * Applies the current isolation level value and read-only flag
 * to the returned Connection.
 * @see #getCurrentIsolationLevel()
 * @see #getCurrentReadOnlyFlag()
 */
@Override
protected Connection doGetConnection(String username, String password) throws SQLException {
	Connection con = super.doGetConnection(username, password);
	Boolean readOnlyToUse = getCurrentReadOnlyFlag();
	if (readOnlyToUse != null) {
		con.setReadOnly(readOnlyToUse);
	}
	Integer isolationLevelToUse = getCurrentIsolationLevel();
	if (isolationLevelToUse != null) {
		con.setTransactionIsolation(isolationLevelToUse);
	}
	return con;
}
 

public void testReadOnlyWithProcBodyAccess() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        Connection replConn = null;
        Properties props = getHostFreePropertiesFromTestsuiteUrl();
        props.setProperty("autoReconnect", "true");

        try {
            createProcedure("testProc1", "()\nREADS SQL DATA\nbegin\nSELECT NOW();\nend\n");

            createProcedure("`testProc.1`", "()\nREADS SQL DATA\nbegin\nSELECT NOW();\nend\n");

            replConn = getMasterSlaveReplicationConnection();
            replConn.setReadOnly(true);

            CallableStatement cstmt = replConn.prepareCall("CALL testProc1()");
            cstmt.execute();
            cstmt.execute();

            cstmt = replConn.prepareCall("CALL `" + replConn.getCatalog() + "`.testProc1()");
            cstmt.execute();

            cstmt = replConn.prepareCall("CALL `" + replConn.getCatalog() + "`.`testProc.1`()");
            cstmt.execute();

        } finally {

            if (replConn != null) {
                replConn.close();
            }
        }
    }
}
 

public Connection getReadOnlyConnection() throws SQLException {
    final Connection conn = getConnection();
    conn.setReadOnly(true);
    return conn;
}
 

public void testReadOnlyToWritableTran() throws SQLException, Exception
{
    // This fixture will run twice, once with embedded, once with client,
    // and insert 2 rows in addition to the 5 rows inserted during setup. 
    // The fixture tests a commit, so before running, try to remove row 
    // 6 and 7 in case this is the second run of the fixture.
    Statement s = createStatement();
    s.executeUpdate("delete from autocommitxastart where i = 6");
    s.executeUpdate("delete from autocommitxastart where i = 7");
    
    // TESTING READ_ONLY TRANSACTION FOLLOWED BY WRITABLE TRANSACTION
    // Test following sequence of steps
    // 1)start a read-only global transaction 
    // 2)finish that read-only transaction
    // 3)start another global transaction 

    XADataSource dsx = J2EEDataSource.getXADataSource();
    XAConnection xac5 = dsx.getXAConnection();
    Xid xid5a = new cdsXid(5, (byte) 119, (byte) 129);
    Connection conn5 = xac5.getConnection();
    Statement sru5a = conn5.createStatement();
    XAResource xar = xac5.getXAResource();
    xar.start(xid5a, XAResource.TMNOFLAGS);
    conn5.setReadOnly(true);

    // Read-Only XA transaction;
    // holdability: (hold, or close cursors over commit) , 
    // transaction isolation: read-committed, 
    // auto-commit false, read-only true (with embedded)
    if (usingEmbedded()) 
    {
        assertConnectionState(
            ResultSet.CLOSE_CURSORS_AT_COMMIT, 
            Connection.TRANSACTION_READ_COMMITTED,
            false, true, conn5);
    }
    // Note: the original test had no comments about this difference
    //       between Embedded and DerbyNetClient, this has apparently
    //       been accepted behavior.
    else if (usingDerbyNetClient())
    {
        assertConnectionState(
            ResultSet.CLOSE_CURSORS_AT_COMMIT, 
            Connection.TRANSACTION_READ_COMMITTED,
            false, false, conn5);
    }
    
    ResultSet rs5 = sru5a.executeQuery(
        "select count(*) from autocommitxastart");
    rs5.next();
    assertEquals(5, rs5.getInt(1));
    rs5.close();
    xar.end(xid5a, XAResource.TMSUCCESS);
    xar.commit(xid5a, true);
    conn5.close();
    
    //now start a new transaction
    conn5 = xac5.getConnection();
    sru5a = conn5.createStatement();
    xar.start(xid5a, XAResource.TMNOFLAGS);
    
    // Writeable XA transaction
    // holdability: (hold, or close cursors over commit) , 
    // transaction isolation: read-committed, 
    // auto-commit false, read-only false
    assertConnectionState(
            ResultSet.CLOSE_CURSORS_AT_COMMIT, 
            Connection.TRANSACTION_READ_COMMITTED,
            false, false, conn5);
    sru5a.executeUpdate("insert into autocommitxastart values 6,7");
    rs5 = sru5a.executeQuery("select count(*) from autocommitxastart");
    rs5.next();
    assertEquals(7, rs5.getInt(1));
    xar.end(xid5a, XAResource.TMSUCCESS);
    xar.commit(xid5a, true);
    conn5.close();
    xac5.close();
    sru5a.close();
}