org.springframework.jdbc.core.ConnectionCallback Java Examples

public AbstractDbDialect(final JdbcTemplate jdbcTemplate, LobHandler lobHandler) {
    this.jdbcTemplate = jdbcTemplate;
    this.lobHandler = lobHandler;
    // 初始化transction
    this.transactionTemplate = new TransactionTemplate();
    transactionTemplate.setTransactionManager(new DataSourceTransactionManager(jdbcTemplate.getDataSource()));
    transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);

    // 初始化一些数据
    jdbcTemplate.execute(new ConnectionCallback() {

        public Object doInConnection(Connection c) throws SQLException, DataAccessException {
            DatabaseMetaData meta = c.getMetaData();
            databaseName = meta.getDatabaseProductName();
            databaseMajorVersion = meta.getDatabaseMajorVersion();
            databaseMinorVersion = meta.getDatabaseMinorVersion();

            return null;
        }
    });

    initTables(jdbcTemplate);
}
 

@Override
public List<String> getMetaData() {
  ConnectionCallback<Object> connectionCallback = new ConnectionCallback<Object>() {
    public Object doInConnection(Connection connection) throws SQLException {
      try (Statement statement = connection.createStatement()) {
        statement.execute("show timeseries root.*");
        try(ResultSet resultSet = statement.getResultSet()) {
          logger.info("Start to get timeseries");
          List<String> columnsName = new ArrayList<>();
          while (resultSet.next()) {
            String timeseries = resultSet.getString(1);
            columnsName.add(timeseries.substring(5));
          }
          return columnsName;
        }
      }
    }
  };
  return (List<String>) jdbcTemplate.execute(connectionCallback);
}
 

protected int countTableResults(String valueToVerify) {
    final String valueToCheck = valueToVerify;
    final DataSource dataSource = TestHarnessServiceLocator.getDataSource();
    return (Integer) new JdbcTemplate(dataSource).execute(new ConnectionCallback() {
        public Object doInConnection(final Connection connection) throws SQLException {
            Statement statement = null;
            try {
                statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                final ResultSet resultSet = statement.executeQuery("Select * from " + TEST_TABLE_NAME + " where COL = '" + valueToCheck + "'");
                assertNotNull("ResultSet should not be null",resultSet);
                int count = 0;
                while (resultSet.next()) {
                    count++;
                }
                return count;
            } finally {
                if (statement != null) {
                    statement.close();
                }
            }
        }
    });
}
 

/**
 * 查找sqlserver的主键索引
 * 
 * @param dbInofId
 * @param tableName
 * @return 返回主键索引的值
 * @throws Exception
 */
public String findSqlServerPKIndex(String dbInofId, final String tableName) throws Exception {
	DataSource ds = getDataSourceByDbInfoId(dbInofId);
	JdbcTemplate jdbcTemplate = SpringJdbcUtils.getJdbcTemplate(ds);
	String s = jdbcTemplate.execute(new ConnectionCallback<String>() {
		public String doInConnection(Connection con) throws SQLException, DataAccessException {
			String pkName = null;
			if (con.getMetaData().getURL().toLowerCase().contains("sqlserver")) {
				CallableStatement call = con.prepareCall("{call sp_pkeys(?)}");
				call.setString(1, tableName);
				ResultSet rs = call.executeQuery();
				while (rs.next()) {
					pkName = rs.getString("PK_NAME");
				}
			}
			return pkName;

		}
	});
	return s;
}
 

/**
 * 根据DataSource判断一下数据库类型
 */
public static DbType judgeDbType(DataSource dataSource) {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    return (DbType) jdbcTemplate.execute(new ConnectionCallback() {

        public Object doInConnection(Connection c) throws SQLException, DataAccessException {
            DatabaseMetaData meta = c.getMetaData();
            String databaseName = meta.getDatabaseProductName();
            String version = meta.getDatabaseProductVersion();

            if (StringUtils.startsWithIgnoreCase(databaseName, "oracle")) {
                return DbType.ORACLE;
            } else if (StringUtils.startsWithIgnoreCase(databaseName, "mysql")) {
                if (StringUtils.contains(version, "-TDDL-")) {
                    return DbType.DRDS;
                } else {
                    return DbType.MYSQL;
                }
            } else {
                throw new YuGongException("unknow database type " + databaseName);
            }
        }
    });

}
 

public static void verifyTestEnvironment(DataSource dataSource) {
    if (dataSource == null) {
        Assert.fail("Could not locate the data source.");
    }
    JdbcTemplate template = new JdbcTemplate(dataSource);
    template.execute(new ConnectionCallback() {
        public Object doInConnection(Connection connection) throws SQLException {
            ResultSet resultSet = connection.getMetaData().getTables(null, null, TEST_TABLE_NAME, null);
            if (!resultSet.next()) {
                LOG.error("No table named '"+TEST_TABLE_NAME+"' was found in the configured database.  " +
                        "You are attempting to run tests against a non-test database!!!");
                LOG.error("The test environment will not start up properly!!!");
                Assert.fail("No table named '"+TEST_TABLE_NAME+"' was found in the configured database.  " +
                		"You are attempting to run tests against a non-test database!!!");
            }
            return null;
        }
    });
}
 

/**
 * Gets the platform information from the {@link DataSource}.
 *
 * @param dataSource the {@link DataSource} to consult.
 * @return the platform information from the {@link DataSource}.
 */
public static DatabasePlatformInfo detectPlatform(DataSource dataSource) {
    if (dataSource == null) {
        throw new IllegalArgumentException("DataSource must not be null.");
    }
    DatabasePlatformInfo platformInfo = platformCache.get(dataSource);
    if (platformInfo == null) {
        JdbcTemplate template = new JdbcTemplate(dataSource);
            platformCache.put(dataSource, template.execute(new ConnectionCallback<DatabasePlatformInfo>() {
                        @Override
                        public DatabasePlatformInfo doInConnection(
                                Connection connection) throws SQLException, DataAccessException {
                            DatabaseMetaData metadata = connection.getMetaData();
                            String vendorName = metadata.getDatabaseProductName();
                            int version = metadata.getDatabaseMajorVersion();
                            return new DatabasePlatformInfo(vendorName, version);
                        }
                }));
        if (platformInfo == null) {
            platformInfo = platformCache.get(dataSource);
        }
    }
    return platformInfo;
}
 

/**
 * retrieve the objects with the specified keys and pass them to the consumer.
 *
 * @param keys       the keys
 * @param consumer the handler that is callback for each row
 * @throws DataAccessException if an error occurs
 */
public <RH extends CheckedConsumer<? super T>> RH read(final Collection<K> keys, final RH consumer) throws DataAccessException {
    return
        jdbcTemplate.execute(new ConnectionCallback<RH>() {
            @Override
            public RH doInConnection(Connection connection) throws SQLException, DataAccessException {
                return crud.read(connection, keys, consumer);
            }
        });
}
 

private void runDestroyScripts() {
    final ResourceDatabasePopulator resourceDatabasePopulator = createResourceDatabasePopulator();
    jdbcTemplate.execute(new ConnectionCallback<Void>() {
        @Override
        public Void doInConnection(Connection con) throws SQLException, DataAccessException {
            resourceDatabasePopulator.setScripts(getDestroyScripts());
            resourceDatabasePopulator.populate(con);
            return null;
        }
    });
}
 

/**
 * update the object.
 *
 * @param value      the object
 * @throws DataAccessException if an error occurs
 */
public void update(final T value) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.update(connection, value);
            return null;
        }
    });
}
 

/**
 * update the objects.
 *
 * @param values      the objects
 * @throws DataAccessException if an error occurs
 */
public void update(final Collection<T> values) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.update(connection, values);
            return null;
        }
    });
}
 

/**
 * delete the object with the specified key.
 *
 * @param key        the key
 * @throws DataAccessException if an error occurs
 */
public void delete(final K key) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.delete(connection, key);
            return null;
        }
    });
}
 

/**
 * delete the objects with the specified keys.
 *
 * @param keys       the keys
 * @throws DataAccessException if an error occurs
 */
public void delete(final List<K> keys) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.delete(connection, keys);
            return null;
        }
    });
}
 

/**
 * UPSERT only supported on Mysql
 * @param value the value
 * @throws DataAccessException
 * @throws UnsupportedOperationException
 */
public void createOrUpdate(final T value) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.createOrUpdate(connection, value);
            return null;
        }
    });
}
 

/**
 * UPSERT only supported on Mysql
 * @param values the values to upsert
 * @throws DataAccessException
 * @throws UnsupportedOperationException
 */
public void createOrUpdate(final Collection<T> values) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.createOrUpdate(connection, values);
            return null;
        }
    });
}
 

@Override
public <R> R doInTransaction(final SQLFunction<? super Connection, ? extends R> sqlFunction) throws DataAccessException {
    return jdbcOperations.execute(new ConnectionCallback<R>() {
        @Override
        public R doInConnection(Connection con) throws SQLException, DataAccessException {
            return sqlFunction.apply(con);
        }
    });
}
 

/**
 * {@inheritDoc}
 */
@Override
protected synchronized long getNextKey() throws DataAccessException {
    return template.execute(new ConnectionCallback<Long>() {
        @Override
        public Long doInConnection(Connection con) throws SQLException, DataAccessException {
            Statement statement = null;
            ResultSet resultSet = null;
            try {
                statement = con.createStatement();
                String sql = "INSERT INTO " + getIncrementerName() + " VALUES (NULL)";
                statement.executeUpdate(sql);
                sql = "SELECT LAST_INSERT_ID()";
                resultSet = statement.executeQuery(sql);
                if (resultSet != null) {
                    resultSet.first();
                    return resultSet.getLong(1);
                } else {
                    throw new IncorrectResultSizeDataAccessException("Failed to get last_insert_id() for sequence incrementer table '" + getIncrementerName() + "'", 1);
                }
            } finally {
                JdbcUtils.closeResultSet(resultSet);
                JdbcUtils.closeStatement(statement);
            }
        }
    }).longValue();
}
 

public boolean performEncryption(
        final String tableName, 
        final List<Map<String, List<String>>> rowsToEncryptColumnNameOldNewValuesMap) throws Exception {

    Boolean success = (Boolean) getJdbcTemplate().execute(new ConnectionCallback() {
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                try{
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    int counter = 0;
                    for (Map<String, List<String>> columnNameOldNewValuesMap: rowsToEncryptColumnNameOldNewValuesMap) {
                        statement.addBatch(getUpdateBackupTableColumnsSql(tableName, columnNameOldNewValuesMap));
                        counter++;
                    }
                    statement.executeBatch();
                    conn.commit();
                    LOG.info(new StringBuffer("Encrypted ").append(" attributes of table ").append(tableName));
                }
                catch (Exception e) {
                    LOG.error(new StringBuffer("Caught exception, while encrypting ").append(" attributes of table ").append(tableName), e);
                    conn.rollback();
                    return false;
                }
                return true;
            }
        });
    return success;
}
 

protected void verifyTestEnvironment(final DataSource dataSource) {
    new JdbcTemplate(dataSource).execute(new ConnectionCallback<Object>() {
        public Object doInConnection(final Connection connection) throws SQLException {
            String dbUrl = connection.getMetaData().getURL();
            Assert.assertTrue("No table named '" + TEST_TABLE_NAME + "' was found in the configured database.  " +
                    dbUrl + "  You are attempting to run tests against a non-test database!!!", isTestTableInSchema(connection));
            return null;
        }
    });
}
 

private void runInitScripts() {
    final ResourceDatabasePopulator resourceDatabasePopulator = createResourceDatabasePopulator();
    jdbcTemplate.execute(new ConnectionCallback<Void>() {
        @Override
        public Void doInConnection(Connection con) throws SQLException, DataAccessException {
            resourceDatabasePopulator.setScripts(getInitScripts());
            resourceDatabasePopulator.populate(con);
            return null;
        }
    });
}
 

@Override
public DbDialect load(MediaSourceInfo mediaSourceInfo) throws Exception {
    DataSource dataSource = DataSourceFactory.getDataSource(mediaSourceInfo);
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    return jdbcTemplate.execute(
            new ConnectionCallback<DbDialect>() {
                @Override
                public DbDialect doInConnection(Connection connection) throws SQLException, DataAccessException {
                    DatabaseMetaData meta = connection.getMetaData();
                    String databaseName = meta.getDatabaseProductName();
                    int databaseMajorVersion = meta.getDatabaseMajorVersion();
                    int databaseMinorVersion = meta.getDatabaseMinorVersion();
                    DbDialect dialect = generate(jdbcTemplate, databaseName,
                            databaseMajorVersion,
                            databaseMinorVersion, mediaSourceInfo.getType());
                    if (dialect == null) {
                        throw new UnsupportedOperationException("no dialect for" + databaseName);
                    }

                    if (logger.isInfoEnabled()) {
                        logger.info(String.format("--- DATABASE: %s, SCHEMA: %s ---",
                                databaseName,
                                (dialect.getDefaultSchema() == null) ? dialect.getDefaultCatalog() : dialect.getDefaultSchema()));
                    }

                    return dialect;
                }
            });
}
 

/**
 * insert values into the db through the specified connection.
 *
 * @param values      the values
 * @throws DataAccessException if an error occurs
 */
public void create(final Collection<T> values) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.create(connection, values);
            return null;
        }
    });
}
 

/**
 * insert value into the db through the specified connection.
 *
 * @param value      the value
 * @throws DataAccessException if an error occurs
 */
public void create(final T value) throws DataAccessException {
    jdbcTemplate.execute(new ConnectionCallback<Object>() {
        @Override
        public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
            crud.create(connection, value);
            return null;
        }
    });
}
 

public JdbcTemplateCrud<T, K> to(JdbcOperations jdbcOperations) {
    final JdbcMapperFactory factory = JdbcMapperFactory.newInstance(jdbcTemplateMapperFactory);

    Crud<T, K> crud =
            jdbcOperations.execute(new ConnectionCallback<Crud<T, K>>() {
                @Override
                public Crud<T, K> doInConnection(Connection connection) throws SQLException, DataAccessException {
                    return factory.<T, K>crud(target, keyTarget).to(connection);
                }
            });

    return new JdbcTemplateCrud<T, K>(jdbcOperations, crud);
}
 

public JdbcTemplateCrud<T, K> to(JdbcOperations jdbcOperations, final String table) {
    final JdbcMapperFactory factory = JdbcMapperFactory.newInstance(jdbcTemplateMapperFactory);

    Crud<T, K> crud =
        jdbcOperations.execute(new ConnectionCallback<Crud<T, K>>() {
            @Override
            public Crud<T, K> doInConnection(Connection connection) throws SQLException, DataAccessException {
                return factory.<T, K>crud(target, keyTarget).table(connection, table);
            }
        });

    return new JdbcTemplateCrud<T, K>(jdbcOperations, crud);
}
 

protected IDialect getDialect(JdbcTemplate jdbcTemplate){
	return jdbcTemplate.execute(new ConnectionCallback<IDialect>(){
		public IDialect doInConnection(Connection connection) throws SQLException,
				DataAccessException {
			IDialect result=null;
			for(IDialect dialect : dialects){
				if(dialect.support(connection)){
					result=dialect;
					break;
				}
			}
			return result;
		}
	});
}
 

/**
 * retrieve the object with the specified key.
 *
 * @param key        the key
 * @return the object or null if not found
 * @throws DataAccessException if an error occurs
 */
public T read(final K key) throws DataAccessException {
    return
        jdbcTemplate.execute(new ConnectionCallback<T>() {
            @Override
            public T doInConnection(Connection connection) throws SQLException, DataAccessException {
                return crud.read(connection, key);
            }
        });
}
 

private String getProduct() {
    return this.jdbcTemplate.execute(new ConnectionCallback<String>() {
        @Override
        public String doInConnection(Connection connection) throws SQLException,
            DataAccessException {

            return connection.getMetaData().getDatabaseProductName();
        }
    });
}
 

private static String getDatabaseProductName(Configuration configuration) {
    try {
        return configuration.getJdbcTemplate().execute((ConnectionCallback<String>) connection -> connection.getMetaData().getDatabaseProductName());
    } catch (Exception e) {
        logger.debug("Can not determine database product name " + e.getMessage());
        return "Unknown";
    }
}
 

private void testConnection(DataSource dataSource) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String version = (String) jdbcTemplate.execute(new ConnectionCallback() {

        public Object doInConnection(Connection con) throws SQLException, DataAccessException {
            DatabaseMetaData metaData = con.getMetaData();
            return metaData.getDatabaseProductName() + "-" + metaData.getDatabaseProductVersion();
        }
    });

    System.out.println(version);
    Assert.assertNotNull(version);
}