Java Code Examples for org.springframework.jdbc.datasource.DataSourceUtils#getConnection()

/**
 * Executes the SQL as specified by {@link #getSequenceQuery()}.
 */
@Override
protected long getNextKey() throws DataAccessException {
	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	ResultSet rs = null;
	try {
		stmt = con.createStatement();
		DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
		rs = stmt.executeQuery(getSequenceQuery());
		if (rs.next()) {
			return rs.getLong(1);
		}
		else {
			throw new DataAccessResourceFailureException("Sequence query did not return a result");
		}
	}
	catch (SQLException ex) {
		throw new DataAccessResourceFailureException("Could not obtain sequence value", ex);
	}
	finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
public Dialect getObject() throws SQLException
{
    Connection con = null;
    try
    {
        // make sure that we AUTO-COMMIT
        con = DataSourceUtils.getConnection(dataSource);
        con.setAutoCommit(true);
        DatabaseMetaData meta = con.getMetaData();
        Dialect dialect = DialectFactory.buildDialect(meta.getDatabaseProductName(), meta.getDatabaseMajorVersion(), meta.getDriverName());
        return dialect;
    }
    finally
    {
        try
        {
            con.close();
        }
        catch (Exception e)
        {
        }
    }
}
 

@Override
@Nullable
public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(obtainDataSource());
	try {
		// Create close-suppressing Connection proxy, also preparing returned Statements.
		Connection conToUse = createConnectionProxy(con);
		return action.doInConnection(conToUse);
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		String sql = getSql(action);
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw translateException("ConnectionCallback", sql, ex);
	}
	finally {
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
public Connection getConnection() throws SQLException {
    TransactionProxy proxy = getProxy();
    if (proxy != null) {
        return proxy.getConnection();
    }
    //根据当前激活的数据源 获取jdbc链接
    DataSource dataSource = DataSourceHolder.currentDataSource().getNative();
    String dsId = switcher().currentDataSourceId();
    Connection connection = DataSourceUtils.getConnection(dataSource);
    proxy = new TransactionProxy(dsId, connection, dataSource);
    addProxy(proxy);

    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug(
                "DataSource (" + (dsId == null ? "default" : dsId) + ") JDBC Connection ["
                        + connection
                        + "] will"
                        + (proxy.isConnectionTransactional ? " " : " not ")
                        + "be managed by Spring");
    }

    return connection;
}
 

private boolean isRunningElsewhere(final JobKey jobKey) throws SchedulerException {
    if (!scheduler.getScheduler().getMetaData().isJobStoreClustered()) {
        return false;
    }

    DataSource dataSource = domainHolder.getDomains().get(SyncopeConstants.MASTER_DOMAIN);
    Connection conn = DataSourceUtils.getConnection(dataSource);
    PreparedStatement stmt = null;
    ResultSet resultSet = null;
    try {
        stmt = conn.prepareStatement(
                "SELECT 1 FROM " + Constants.DEFAULT_TABLE_PREFIX + "FIRED_TRIGGERS "
                + "WHERE JOB_NAME = ? AND JOB_GROUP = ?");
        stmt.setString(1, jobKey.getName());
        stmt.setString(2, jobKey.getGroup());

        resultSet = stmt.executeQuery();
        return resultSet.next();
    } catch (SQLException e) {
        throw new SchedulerException(e);
    } finally {
        IOUtil.quietClose(resultSet);
        IOUtil.quietClose(stmt);
        DataSourceUtils.releaseConnection(conn, dataSource);
    }
}
 

/**
 * Execute the given {@link DatabasePopulator} against the given {@link DataSource}.
 * @param populator the {@code DatabasePopulator} to execute
 * @param dataSource the {@code DataSource} to execute against
 * @throws DataAccessException if an error occurs, specifically a {@link ScriptException}
 */
public static void execute(DatabasePopulator populator, DataSource dataSource) throws DataAccessException {
	Assert.notNull(populator, "DatabasePopulator must be provided");
	Assert.notNull(dataSource, "DataSource must be provided");
	try {
		Connection connection = DataSourceUtils.getConnection(dataSource);
		try {
			populator.populate(connection);
		}
		finally {
			if (connection != null) {
				DataSourceUtils.releaseConnection(connection, dataSource);
			}
		}
	}
	catch (Exception ex) {
		if (ex instanceof ScriptException) {
			throw (ScriptException) ex;
		}

		throw new UncategorizedScriptException("Failed to execute database script", ex);
	}
}
 

/**
 * Executes the SQL as specified by {@link #getSequenceQuery()}.
 */
@Override
protected long getNextKey() throws DataAccessException {
	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	ResultSet rs = null;
	try {
		stmt = con.createStatement();
		DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
		rs = stmt.executeQuery(getSequenceQuery());
		if (rs.next()) {
			return rs.getLong(1);
		}
		else {
			throw new DataAccessResourceFailureException("Sequence query did not return a result");
		}
	}
	catch (SQLException ex) {
		throw new DataAccessResourceFailureException("Could not obtain sequence value", ex);
	}
	finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
@Nullable
public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(obtainDataSource());
	try {
		// Create close-suppressing Connection proxy, also preparing returned Statements.
		Connection conToUse = createConnectionProxy(con);
		return action.doInConnection(conToUse);
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		String sql = getSql(action);
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw translateException("ConnectionCallback", sql, ex);
	}
	finally {
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.nextValueIndex < 0 || this.nextValueIndex >= getCacheSize()) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that @@identity
		* returns the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			this.valueCache = new long[getCacheSize()];
			this.nextValueIndex = 0;
			for (int i = 0; i < getCacheSize(); i++) {
				stmt.executeUpdate(getIncrementStatement());
				ResultSet rs = stmt.executeQuery(getIdentityStatement());
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("Identity statement failed after inserting");
					}
					this.valueCache[i] = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
			}
			stmt.executeUpdate(getDeleteStatement(this.valueCache));
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not increment identity", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	return this.valueCache[this.nextValueIndex++];
}
 

public void populate() {
  // resources based on PostgreSQL ACL schema in Spring ACL artifact JAR
  List<String> resourceNames = new ArrayList<>(4);
  resourceNames.add("/sql/create_table_acl_sid.sql");
  resourceNames.add("/sql/create_table_acl_class.sql");
  resourceNames.add("/sql/create_table_acl_object_identity.sql");
  resourceNames.add("/sql/create_table_acl_entry.sql");

  Connection connection = DataSourceUtils.getConnection(dataSource);
  try {
    resourceNames.forEach(resourceName -> createTable(connection, resourceName));
  } finally {
    DataSourceUtils.releaseConnection(connection, dataSource);
  }
}
 

@Override
public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(getDataSource());
	try {
		Connection conToUse = con;
		if (this.nativeJdbcExtractor != null) {
			// Extract native JDBC Connection, castable to OracleConnection or the like.
			conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
		}
		else {
			// Create close-suppressing Connection proxy, also preparing returned Statements.
			conToUse = createConnectionProxy(con);
		}
		return action.doInConnection(conToUse);
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex);
	}
	finally {
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
public List<TransactionInfo> listSince(Date date) {

	Connection conn= DataSourceUtils.getConnection(dataSourceAdaptor.getDataSource());

	List<TransactionInfo> transactionInfos = new ArrayList<TransactionInfo>();

	PreparedStatement stmt = null;

	try {

		StringBuilder builder = new StringBuilder();
		builder.append(SELECT_DQL + " FROM TRANSACTION_INFO WHERE GMT_MODIFIED > ?");

		stmt = conn.prepareStatement(builder.toString());

		stmt.setTimestamp(1, new Timestamp(date.getTime()));

		ResultSet resultSet = stmt.executeQuery();

		while (resultSet.next()) {
			transactionInfos.add(resultSet2Bean(resultSet));
		}
	} catch (Throwable e) {
		throw new DistributedTransactionException(e);
	} finally {
		closeStatement(stmt);
		releaseConnection(conn);
	}

	return transactionInfos;
}
 

public static Connection getConnection(DataSource dataSource) 
	throws CannotGetJdbcConnectionException, Exception {
	
	if (dataSource==null) {
		return null;
	}
	return DataSourceUtils.getConnection(dataSource);
}
 

@PostConstruct
public void statusReport() throws SQLException {
    try (var connection = DataSourceUtils.getConnection(dataSource)) {
        var metaData = connection.getMetaData();
        log.info("Database: {} {}", metaData.getDatabaseProductName(), metaData.getDatabaseProductVersion());
        log.info("User: {}", metaData.getUserName());
        log.info("Connection URL: {} (configuration was {})", metaData.getURL(), dataSourceProperties.getUrl());
        log.info("Flyway locations: {}", flywayProperties.getLocations());
    }
}
 

/**
 * Shutdown the embedded HSQLDB database. After this has run, the database cannot be accessed again from the same DataSource.
 */
private void shutdownHsqldbDatabase() {
    try {
        LOG.debug("Database shutdown in progress...");
        JdbcTemplate jdbcTemplate = getJdbcTemplate();
        try (Connection conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource())) {
            jdbcTemplate.execute("SHUTDOWN");
        }
        LOG.debug("Database shutdown complete.");

    } catch (SQLException e) {
        LOG.error("Database shutdown failed", e);
    }
}
 

@Override
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	try {
		Connection conToUse = con;
		if (this.nativeJdbcExtractor != null &&
				this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
			conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
		}
		stmt = conToUse.createStatement();
		applyStatementSettings(stmt);
		Statement stmtToUse = stmt;
		if (this.nativeJdbcExtractor != null) {
			stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
		}
		T result = action.doInStatement(stmtToUse);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

@Override
protected List<TransactionInfo> doFindSince(Date date, Integer[] txStatus, String moduleId) {

	Connection conn= DataSourceUtils.getConnection(dataSourceAdaptor.getDataSource());

	List<TransactionInfo> transactionInfos = new ArrayList<TransactionInfo>();

	PreparedStatement stmt = null;

	try {

		StringBuilder builderOuter = new StringBuilder();
		builderOuter.append(SELECT_DQL + " FROM (");

		StringBuilder builder = new StringBuilder();
		builder.append(SELECT_DQL + " FROM TRANSACTION_INFO WHERE GMT_CREATED > ? ");
				/*.append(getLimitSql(conn, 1000))*/

		boolean hasTriedStatus = false;
		StringBuilder sBuilder = new StringBuilder();
		sBuilder.append(" AND TX_STATUS ");
		if (txStatus.length > 1) {
			sBuilder.append(" IN (");
			for (int i = 0; i < txStatus.length; i++) {
				if (txStatus[i] != TransactionStatusEnum.TRIED.getCode()) {
					if (i == 0) {
						sBuilder.append(txStatus[i]);
					} else {
						sBuilder.append(",").append(txStatus[i]);
					}
				} else {
					hasTriedStatus = true;
				}
			}
			sBuilder.append(")");
		} else if (txStatus.length == 1) {
			if (txStatus[0] != TransactionStatusEnum.TRIED.getCode()) {
				sBuilder.append(" = ");
				sBuilder.append(txStatus[0]);
			} else {
				hasTriedStatus = true;
			}
		}

		builder.append(" AND MODULE_ID = ? ");

		builderOuter.append(builder).append(" AND NEXT_RETRY_TIME <= ? AND NEXT_RETRY_TIME IS NOT NULL").append(sBuilder);
		builderOuter.append(" UNION ALL ").append(builder).append("AND NEXT_RETRY_TIME IS NULL").append(sBuilder);
		if (hasTriedStatus) {
			builderOuter.append(" UNION ALL ").append(builder).append(" AND NEXT_RETRY_TIME <= ? AND NEXT_RETRY_TIME IS NOT NULL")
					.append(" AND TX_STATUS = " + TransactionStatusEnum.TRIED.getCode() + " AND TX_TYPE = " + TransactionTypeEnum.TCC.getCode());
			builderOuter.append(" UNION ALL ").append(builder).append("AND NEXT_RETRY_TIME IS NULL AND GMT_MODIFIED <= ?")
					.append(" AND TX_STATUS = " + TransactionStatusEnum.TRIED.getCode() + " AND TX_TYPE = " + TransactionTypeEnum.TCC.getCode());
		}
		builderOuter.append(") TX ORDER BY PARENT_ID, TX_ID");

		Timestamp gmtCreated = new Timestamp(date.getTime());
		Timestamp nowTime = new Timestamp(System.currentTimeMillis());
		Timestamp timeoutTime = DateUtil.getPrevSecTimestamp(beginTimeoutSecond);

		stmt = conn.prepareStatement(builderOuter.toString());
		stmt.setTimestamp(1, gmtCreated);
		stmt.setString(2, moduleId);
           stmt.setTimestamp(3, nowTime);
		stmt.setTimestamp(4, gmtCreated);
		stmt.setString(5, moduleId);
		stmt.setTimestamp(6, gmtCreated);
		stmt.setString(7, moduleId);
		stmt.setTimestamp(8, nowTime);
		stmt.setTimestamp(9, gmtCreated);
		stmt.setString(10, moduleId);
		stmt.setTimestamp(11, timeoutTime);

		ResultSet resultSet = stmt.executeQuery();
		while (resultSet.next()) {
			transactionInfos.add(resultSet2Bean(resultSet));
		}
	} catch (Throwable e) {
		throw new DistributedTransactionException(e);
	} finally {
		closeStatement(stmt);
		releaseConnection(conn);
	}

	return transactionInfos;
}
 

public static Connection getConnection()throws SQLException{  
	return DataSourceUtils.getConnection((DataSource)SpringConfigTool.getContext().getBean("dataSource"));
}
 

@Override
protected List<TransactionInfo> doFind(TransactionInfo transactionInfo, boolean isLock) {
	Connection conn= DataSourceUtils.getConnection(dataSourceAdaptor.getDataSource());

	PreparedStatement stmt = null;

	List<TransactionInfo> transactionInfos = new ArrayList<TransactionInfo>();

	try {

		StringBuilder builder = new StringBuilder();
		builder.append(SELECT_DQL + " FROM TRANSACTION_INFO WHERE 1=1 ");
		if (transactionInfo.getTxId() > -1L) {
			builder.append("AND TX_ID = ? ");
		}
		if (transactionInfo.getParentId() > -1L) {
			builder.append("AND PARENT_ID = ? ");
		}
		if (!Strings.isNullOrEmpty(transactionInfo.getModuleId())) {
			builder.append("AND MODULE_ID = ? ");
		}
		if (!Strings.isNullOrEmpty(transactionInfo.getBusinessId())) {
			builder.append("AND BUSINESS_ID = ? ");
		}
		if (!Strings.isNullOrEmpty(transactionInfo.getBusinessType())) {
			builder.append("AND BUSINESS_TYPE = ? ");
		}
		if (transactionInfo.getTxType() > -1) {
			builder.append("AND TX_TYPE = ? ");
		}
		if (transactionInfo.getTxStatus() > -1) {
			builder.append("AND TX_STATUS = ? ");
		}
		if (transactionInfo.getGmtCreated() != null) {
			builder.append("AND GMT_CREATED = ? ");
		}
		if (isLock) {
			builder.append(" FOR UPDATE ");
		}
		stmt = conn.prepareStatement(builder.toString());

		int condition = 0;

		if (transactionInfo.getTxId() > -1L) {
			stmt.setLong(++condition, transactionInfo.getTxId());
		}
		if (transactionInfo.getParentId() > -1L) {
			stmt.setLong(++condition, transactionInfo.getParentId());
		}
		if (!Strings.isNullOrEmpty(transactionInfo.getModuleId())) {
			stmt.setString(++condition, transactionInfo.getModuleId());
		}
		if (!Strings.isNullOrEmpty(transactionInfo.getBusinessId())) {
			stmt.setString(++condition, transactionInfo.getBusinessId());
		}
		if (!Strings.isNullOrEmpty(transactionInfo.getBusinessType())) {
			stmt.setString(++condition, transactionInfo.getBusinessType());
		}
		if (transactionInfo.getTxType() > -1) {
			stmt.setInt(++condition, transactionInfo.getTxType());
		}
		if (transactionInfo.getTxStatus() > -1) {
			stmt.setInt(++condition, transactionInfo.getTxStatus());
		}
		if (transactionInfo.getGmtCreated() != null) {
			stmt.setTimestamp(++condition, new Timestamp(transactionInfo.getGmtCreated().getTime()));
		}

		ResultSet resultSet = stmt.executeQuery();

		while (resultSet.next()) {
			transactionInfos.add(resultSet2Bean(resultSet));
		}

	} catch (Throwable e) {
		throw new DistributedTransactionException(e);
	} finally {
		closeStatement(stmt);
		releaseConnection(conn);
	}
	return transactionInfos;
}
 

@Override
protected synchronized long getNextKey() throws DataAccessException {
	if (this.maxId == this.nextId) {
		/*
		* Need to use straight JDBC code because we need to make sure that the insert and select
		* are performed on the same connection (otherwise we can't be sure that last_insert_id()
		* returned the correct value)
		*/
		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
			// Increment the sequence column...
			String columnName = getColumnName();
			stmt.executeUpdate("update "+ getIncrementerName() + " set " + columnName +
					" = last_insert_id(" + columnName + " + " + getCacheSize() + ")");
			// Retrieve the new max of the sequence column...
			ResultSet rs = stmt.executeQuery(VALUE_SQL);
			try {
				if (!rs.next()) {
					throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
				}
				this.maxId = rs.getLong(1);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
			this.nextId = this.maxId - getCacheSize() + 1;
		}
		catch (SQLException ex) {
			throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}
	else {
		this.nextId++;
	}
	return this.nextId;
}