Java Code Examples for java.sql.Connection#TRANSACTION_NONE

/**
 * Tests isolation level functionality
 * 
 * @throws Exception
 *             if an error occurs
 */
public void testIsolationLevel() throws Exception {
    if (versionMeetsMinimum(4, 0)) {
        String[] isoLevelNames = new String[] { "Connection.TRANSACTION_NONE", "Connection.TRANSACTION_READ_COMMITTED",
                "Connection.TRANSACTION_READ_UNCOMMITTED", "Connection.TRANSACTION_REPEATABLE_READ", "Connection.TRANSACTION_SERIALIZABLE" };

        int[] isolationLevels = new int[] { Connection.TRANSACTION_NONE, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_READ_UNCOMMITTED,
                Connection.TRANSACTION_REPEATABLE_READ, Connection.TRANSACTION_SERIALIZABLE };

        DatabaseMetaData dbmd = this.conn.getMetaData();

        for (int i = 0; i < isolationLevels.length; i++) {
            if (dbmd.supportsTransactionIsolationLevel(isolationLevels[i])) {
                this.conn.setTransactionIsolation(isolationLevels[i]);

                assertTrue(
                        "Transaction isolation level that was set (" + isoLevelNames[i]
                                + ") was not returned, nor was a more restrictive isolation level used by the server",
                        this.conn.getTransactionIsolation() == isolationLevels[i] || this.conn.getTransactionIsolation() > isolationLevels[i]);
            }
        }
    }
}
 

@Override
public Connection getConnection(String sessionId, IRunningStorageAttribute rsa,boolean isAutoCommit) {
    IStorageAttribute sa = rsa.getStorageAttribute();
    String key = sa.getName() + rsa.getDatabase();
    DataSource ds = getDatasource(key, rsa);
    AlbianServiceRouter.getLogger2().log(IAlbianLoggerService2.AlbianSqlLoggerName,
            sessionId, AlbianLoggerLevel.Info,
            "Get the connection from storage:%s and database:%s by connection pool.",
            sa.getName(), rsa.getDatabase());
    try {
        Connection conn = ds.getConnection();
        if (null == conn) return null;
        if (Connection.TRANSACTION_NONE != sa.getTransactionLevel()) {
            conn.setTransactionIsolation(sa.getTransactionLevel());
        }
        conn.setAutoCommit(isAutoCommit);
        return conn;
    } catch (SQLException e) {
        AlbianServiceRouter.getLogger2().log(IAlbianLoggerService2.AlbianRunningLoggerName,
                sessionId, AlbianLoggerLevel.Error, e,
                "Get the connection with storage:%s and database:%s form connection pool is error.",
                sa.getName(), rsa.getDatabase());
        return null;
    }
}
 

/**
 * execute with retries in case of node failures for transactions but not for
 * non-transactional case
 */
protected static int executeUpdate(Statement stmt, String sql)
    throws SQLException {
  while (true) {
    try {
      if (sql != null) {
        return stmt.executeUpdate(sql);
      }
      else {
        return ((PreparedStatement)stmt).executeUpdate();
      }
    } catch (SQLException sqle) {
      Connection conn = stmt.getConnection();
      if (conn.getTransactionIsolation() == Connection.TRANSACTION_NONE) {
        throw sqle;
      }
      String sqlState = sqle.getSQLState();
      if (!"40XD0".equals(sqlState) && !"40XD2".equals(sqlState)) {
        throw sqle;
      }
    }
  }
}
 

/**
 * Return a String representation for the given numerical
 * java.sql.Connection Transaction level.
 * <P>
 * Database implementations are free to provide their own transaction
 * isolation levels, so you can't depend upon this method to much.
 * </P>
 * Returns null, since DB implementations are free to provide
 */
public static String tiToString(int ti) {
    switch (ti) {
        case Connection.TRANSACTION_READ_UNCOMMITTED:
            return "TRANSACTION_READ_UNCOMMITTED";
        case Connection.TRANSACTION_READ_COMMITTED:
            return "TRANSACTION_READ_COMMITTED";
        case Connection.TRANSACTION_REPEATABLE_READ:
            return "TRANSACTION_REPEATABLE_READ";
        case Connection.TRANSACTION_SERIALIZABLE:
            return "TRANSACTION_SERIALIZABLE";
        case Connection.TRANSACTION_NONE:
            return "TRANSACTION_NONE";
    }
    return "Custom Transaction Isolation numerical value: " + ti;
}
 

/**
 * Convert transaction isolation level into string.
 *
 * @param isolationLevel
 *         transaction isolation level as integer constant.
 * @return corresponding string representation.
 */
public static String getTransactionIsolationName(int isolationLevel) {
    switch (isolationLevel) {
    case Connection.TRANSACTION_NONE:
        return TRANSACTION_NONE;

    case Connection.TRANSACTION_READ_UNCOMMITTED:
        return TRANSACTION_READ_UNCOMMITTED;

    case Connection.TRANSACTION_READ_COMMITTED:
        return TRANSACTION_READ_COMMITTED;

    case Connection.TRANSACTION_REPEATABLE_READ:
        return TRANSACTION_REPEATABLE_READ;

    case Connection.TRANSACTION_SERIALIZABLE:
        return TRANSACTION_SERIALIZABLE;

    default:
        throw new IllegalArgumentException("Incorrect transaction isolation level.");
    }
}
 

/**
 * execute with retries in case of node failures for transactions but not for
 * non-transactional case
 */
protected static int executeUpdate(Statement stmt, String sql)
    throws SQLException {
  while (true) {
    try {
      if (sql != null) {
        return stmt.executeUpdate(sql);
      }
      else {
        return ((PreparedStatement)stmt).executeUpdate();
      }
    } catch (SQLException sqle) {
      Connection conn = stmt.getConnection();
      if (conn.getTransactionIsolation() == Connection.TRANSACTION_NONE) {
        throw sqle;
      }
      String sqlState = sqle.getSQLState();
      if (!"40XD0".equals(sqlState) && !"40XD2".equals(sqlState)) {
        throw sqle;
      }
    }
  }
}
 

private static String getTransactionIsolationAsString(
    int transactionIsolationLevel) {

if (transactionIsolationLevel == Connection.TRANSACTION_NONE) {
    return HttpParameter.NONE;
} else if (transactionIsolationLevel == Connection.TRANSACTION_READ_UNCOMMITTED) {
    return HttpParameter.READ_UNCOMMITTED;
} else if (transactionIsolationLevel == Connection.TRANSACTION_READ_COMMITTED) {
    return HttpParameter.READ_COMMITTED;
} else if (transactionIsolationLevel == Connection.TRANSACTION_REPEATABLE_READ) {
    return HttpParameter.REPEATABLE_READ;
} else if (transactionIsolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
    return HttpParameter.SERIALIZABLE;
} else {
    return "UNKNOWN";
}
   }
 

/**
 * Sets the transaction isolation level.
 *
 * @param level transaction level: TRANSACTION_NONE or TRANSACTION_READ_COMMITTED
 * @throws SQLException if any SQL error occurs
 */
@Override
public void setTransactionIsolation(int level) throws SQLException
{
  logger.debug(
      "void setTransactionIsolation(int level), level = {}", level);
  raiseSQLExceptionIfConnectionIsClosed();
  if (level == Connection.TRANSACTION_NONE
      || level == Connection.TRANSACTION_READ_COMMITTED)
  {
    this.transactionIsolation = level;
  }
  else
  {
    throw new SQLFeatureNotSupportedException(
        "Transaction Isolation " + level + " not supported.",
        FEATURE_UNSUPPORTED.getSqlState(),
        FEATURE_UNSUPPORTED.getMessageCode());
  }
}
 

public static int toIntTransactionIsolation(String isolation) {
	if (isolation != null && !"".equals(isolation)) {
		if ("TRANSACTION_NONE".equals(isolation)) {
			return Connection.TRANSACTION_NONE;
		} else if ("TRANSACTION_READ_COMMITTED".equals(isolation.trim())) {
			return Connection.TRANSACTION_READ_COMMITTED;
		} else if ("TRANSACTION_READ_UNCOMMITTED".equals(isolation.trim())) {
			return Connection.TRANSACTION_READ_UNCOMMITTED;
		} else if ("TRANSACTION_REPEATABLE_READ".equals(isolation.trim())) {
			return Connection.TRANSACTION_REPEATABLE_READ;
		} else if ("TRANSACTION_SERIALIZABLE".equals(isolation.trim())) {
			return Connection.TRANSACTION_SERIALIZABLE;
		} else {
			return -1;
		}
	} else {
		return -1;
	}
}
 

protected void printIsolationLevel(Connection conn) {
  try {
  	int isolation = conn.getTransactionIsolation();
  	String isoLevel;
  	switch (isolation) {
  	case Connection.TRANSACTION_NONE:
  		isoLevel = "TRANSACTION_NONE";
  		break;
  	case Connection.TRANSACTION_READ_COMMITTED:
  		isoLevel = "TRANSACTION_READ_COMMITTED";
  		break;
  	case Connection.TRANSACTION_REPEATABLE_READ:
  		isoLevel = "TRANSACTION_REPEATABLE_READ";
  		break;
  	case Connection.TRANSACTION_SERIALIZABLE:
  		isoLevel = "TRANSACTION_SERIALIZABLE";
  		break;
 		default:
  			isoLevel = "unknown";    		    		
  	}
  	Log.getLogWriter().info("the connection isolation level is " + isoLevel);
  	java.sql.SQLWarning w =conn.getWarnings();
  	SQLHelper.printSQLWarning(w);
  } catch (SQLException se) {
  	
  }
}
 

/**
 * Sets the value of defaultTransactionIsolation, which defines the state of connections handed out from this pool.
 * The value can be changed on the Connection using Connection.setTransactionIsolation(int). The default is JDBC
 * driver dependent.
 *
 * @param v
 *            Value to assign to defaultTransactionIsolation
 */
public void setDefaultTransactionIsolation(final int v) {
    assertInitializationAllowed();
    switch (v) {
    case Connection.TRANSACTION_NONE:
    case Connection.TRANSACTION_READ_COMMITTED:
    case Connection.TRANSACTION_READ_UNCOMMITTED:
    case Connection.TRANSACTION_REPEATABLE_READ:
    case Connection.TRANSACTION_SERIALIZABLE:
        break;
    default:
        throw new IllegalArgumentException(BAD_TRANSACTION_ISOLATION);
    }
    this.defaultTransactionIsolation = v;
}
 

@DataProvider(name = "rowSetIsolationTypes")
protected Object[][] rowSetIsolationTypes() throws Exception {
    RowSet rs = newInstance();

    return new Object[][]{
        {rs, Connection.TRANSACTION_NONE},
        {rs, Connection.TRANSACTION_READ_COMMITTED},
        {rs, Connection.TRANSACTION_READ_UNCOMMITTED},
        {rs, Connection.TRANSACTION_REPEATABLE_READ},
        {rs, Connection.TRANSACTION_SERIALIZABLE}
    };
}
 

/**
 * Tests isolation level functionality
 * 
 * @throws Exception
 *             if an error occurs
 */
public void testIsolationLevel() throws Exception {
    // Check initial transaction isolation level
    ((MysqlConnection) this.conn).getPropertySet().getBooleanProperty(PropertyKey.useLocalSessionState).setValue(true);
    int initialTransactionIsolation = this.conn.getTransactionIsolation();

    ((MysqlConnection) this.conn).getPropertySet().getBooleanProperty(PropertyKey.useLocalSessionState).setValue(false);
    int actualTransactionIsolation = this.conn.getTransactionIsolation();

    assertEquals("Inital transaction isolation level doesn't match the server's", actualTransactionIsolation, initialTransactionIsolation);

    // Check setting all allowed transaction isolation levels
    String[] isoLevelNames = new String[] { "Connection.TRANSACTION_NONE", "Connection.TRANSACTION_READ_COMMITTED",
            "Connection.TRANSACTION_READ_UNCOMMITTED", "Connection.TRANSACTION_REPEATABLE_READ", "Connection.TRANSACTION_SERIALIZABLE" };

    int[] isolationLevels = new int[] { Connection.TRANSACTION_NONE, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_READ_UNCOMMITTED,
            Connection.TRANSACTION_REPEATABLE_READ, Connection.TRANSACTION_SERIALIZABLE };

    DatabaseMetaData dbmd = this.conn.getMetaData();
    for (int i = 0; i < isolationLevels.length; i++) {
        if (dbmd.supportsTransactionIsolationLevel(isolationLevels[i])) {
            this.conn.setTransactionIsolation(isolationLevels[i]);

            assertTrue(
                    "Transaction isolation level that was set (" + isoLevelNames[i]
                            + ") was not returned, nor was a more restrictive isolation level used by the server",
                    this.conn.getTransactionIsolation() == isolationLevels[i] || this.conn.getTransactionIsolation() > isolationLevels[i]);
        }
    }
}
 

@Override
public int getDefaultTransactionIsolation() throws SQLException {
    return Connection.TRANSACTION_NONE;
}
 

/**
    * Does the database support the given transaction isolation level?
 *
 * DatabaseMetaData.supportsTransactionIsolation() should return false for
 * isolation levels that are not supported even if a higher level can be
 * substituted.
    *
    * @param level the values are defined in java.sql.Connection
    * @return true if so
    * @see Connection
	*/	
public boolean supportsTransactionIsolationLevel(int level)
						 {
	// REMIND: This is hard-coded for the moment because it doesn't nicely
	// fit within the framework we've set up for the rest of these values.
	// Part of the reason is that it has a parameter, so it's not just a
	// simple value look-up.  Some ideas for the future on how to make this
	// not hard-coded:
	//	  - code it as a query: "select true from <something> where ? in
	//      (a,b,c)" where a,b,c are the supported isolation levels.  The
	//      parameter would be set to "level".  This seems awfully awkward.
	//    - somehow what you'd really like is to enable the instructions
	//      file to contain the list, or set, of supported isolation
	//      levels.  Something like:
	//          supportsTr...ionLevel=SERIALIZABLE | REPEATABLE_READ | ...
	//      That would take some more code that doesn't seem worthwhile at
	//      the moment for this one case.

	/*
		REMIND: this could be moved into a query that is e.g.
		VALUES ( ? in (8,...) )
		so that database could control the list of supported
		isolations.  For now, it's hard coded, and just the one.
	 */

   // GemStone changes BEGIN
   /*
	return (level == Connection.TRANSACTION_SERIALIZABLE    ||
	        level == Connection.TRANSACTION_REPEATABLE_READ ||
		    level == Connection.TRANSACTION_READ_COMMITTED  ||
		    level == Connection.TRANSACTION_READ_UNCOMMITTED);
    */
  // Rahul : since revision 27539 of gemfirexd_dev_Feb10, three isolation
  // levels are supported, NONE, READ_UNCOMMITTED, READ_COMMITTED.
  // [sumedh] also added support for REPEATABLE_READ in the new TX impl
  return (level == Connection.TRANSACTION_READ_COMMITTED  ||
      level == Connection.TRANSACTION_REPEATABLE_READ ||
      level == Connection.TRANSACTION_READ_UNCOMMITTED ||
      level == Connection.TRANSACTION_NONE);
   
   // GemStone changes END 
}
 

@Override
public int getTransactionIsolation() {
  return Connection.TRANSACTION_NONE;
}
 

/**
	 * Create a prepared statement
	 *
	 * @param sqlStmt - SQL statement
	 *
	 * @exception SQLException
	 */
	protected PreparedStatement prepare(String sqlStmt)   throws SQLException
	{
		// save current prepare iso level
		int saveIsolationLevel = -1;
		boolean isolationSet = false;
		if (pkgnamcsn !=null && 
			isolationLevel != Connection.TRANSACTION_NONE)
		{
			saveIsolationLevel = database.getPrepareIsolation();
			database.setPrepareIsolation(isolationLevel);
			isolationSet = true;
		}
		
		parsePkgidToFindHoldability();

		if (isCallableSQL(sqlStmt))
		{
			isCall = true;
			ps = database.getConnection().prepareCall(
				sqlStmt, scrollType, concurType, withHoldCursor);
			setupCallableStatementParams((CallableStatement)ps);
		}
		else
		{
// Gemstone changes BEGIN
		  /* original code
			ps = database.getConnection().prepareStatement(
				sqlStmt, scrollType, concurType, withHoldCursor);
				*/
		  EngineConnection conn = database.getConnection();
		  this.ctx = conn.getLanguageConnectionContext();
                  if (SanityManager.TraceSingleHop) {
                    SanityManager.DEBUG_PRINT(SanityManager.TRACE_SINGLE_HOP,
                        "DRDAStatement::prepare stmt = " + this + " pkgnamcsn: "
                            + pkgnamcsn + " this.sql: " + sqlStmt + ", bucketset: "
                            + this.ctx.getBucketIdsForLocalExecution()
                            + " and sendSingleHopInfo is: " + this.sendSingleHopInfo
                            + " lcc: " + this.ctx);
                  }
		  this.ctx.setSendSingleHopInformation(this.sendSingleHopInfo);
		  ps = conn.prepareStatement(
                      sqlStmt, scrollType, concurType, withHoldCursor);
		  if (!(ps instanceof EmbedPreparedStatement)) {
		    this.ctx = null;
		  }
// Gemstone changes END
		}

		// beetle 3849  -  Need to change the cursor name to what
		// JCC thinks it will be, since there is no way in the 
		// protocol to communicate the actual cursor name.  JCC keeps 
		// a mapping from the client cursor names to the DB2 style cursor names
		if (cursorName != null)//cursorName not null means we are dealing with dynamic pacakges
			ps.setCursorName(cursorName);
		if (isolationSet)
			database.setPrepareIsolation(saveIsolationLevel);
		if (ps instanceof EmbedPreparedStatement)
		        versionCounter = ((EnginePreparedStatement)ps).getVersionCounter();
		return ps;
	}
 

public int getDefaultTransactionIsolation() throws SQLException {
  return Connection.TRANSACTION_NONE;
}
 

@Override
protected void optimizeConnection(OperationType operationType) throws Exception {
	switch (operationType) {
	case READ:
		connection.setAutoCommit(false);
		connection.setReadOnly(true);
		break;
	case WRITE:
	case CALL:
		connection.setAutoCommit(false);
		connection.setReadOnly(false);
		break;

	case TRANSACTION:
		connection.setAutoCommit(true);
		connection.setReadOnly(false);
		break;
	}
	
	//it's not possible to set transaction isolation level
	//log the message about it
	String transactionIsolationLevel = "";
	switch (connection.getTransactionIsolation()) {
	case Connection.TRANSACTION_NONE:
		transactionIsolationLevel = "TRANSACTION_NONE";
		break;
	case Connection.TRANSACTION_READ_COMMITTED:
		transactionIsolationLevel = "TRANSACTION_READ_COMMITTED";
		break;
	case Connection.TRANSACTION_READ_UNCOMMITTED:
		transactionIsolationLevel = "TRANSACTION_READ_UNCOMMITTED";
		break;
	case Connection.TRANSACTION_REPEATABLE_READ:
		transactionIsolationLevel = "TRANSACTION_REPEATABLE_READ";
		break;
	case Connection.TRANSACTION_SERIALIZABLE:
		transactionIsolationLevel = "TRANSACTION_SERIALIZABLE";
		break;
	}
	logger.warn("Transaction isolation level is set to " + transactionIsolationLevel + " and cannot be changed.");
}
 

public int getTestIsolationLevel() {
    if (testIsolation != null)
        return testIsolation.value();
    else
        return Connection.TRANSACTION_NONE;
}