文章目录
- 前言
- 一、创建spring boot工程
- 1. 添加hive-jdbc依赖(无需hadoop依赖)
- 2. 添加德鲁伊连接池依赖
- 3. 完整依赖如下
- 二、使用步骤
- 1.配置Druid连接池
- 2. 配置application.yml
- 3. 编写controller进行测试
- 三、网页显示
- 1. controller修改
- 2. 网页编写
- 四、一些问题
- 1. jar包冲突问题
- 2. thymeleaf检查报错
- 3. 日志jar包冲突
前言
spring boot集成hive,实现在网页查询hive数据。
本帖修改了一些依赖,并加入了lombok,其余同原帖。
本例使用了德鲁伊连接池,原帖还有不使用德鲁伊连接池的例子。
spring boot 2.4.3
hive 3.1.2
一、创建spring boot工程
starters选择web、jdbc、thymeleaf、lombok。
1. 添加hive-jdbc依赖(无需hadoop依赖)
org.eclipse.jetty会冲突,所以排除掉
hive-shims和javax-el如果冲突也可以排除掉,不影响使用
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
<exclusions>
<exclusion>
<groupId>org.eclipse.jetty</groupId>
<artifactId>*</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.hive</groupId>
<artifactId>hive-shims</artifactId>
</exclusion>
<exclusion>
<groupId>org.glassfish</groupId>
<artifactId>javax-el</artifactId>
</exclusion>
<exclusion>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
</exclusion>
</exclusions>
</dependency>
2. 添加德鲁伊连接池依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
3. 完整依赖如下
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- 德鲁伊连接池依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<!-- 添加hive依赖 -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
<exclusions>
<exclusion>
<groupId>org.eclipse.jetty</groupId>
<artifactId>*</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.hive</groupId>
<artifactId>hive-shims</artifactId>
</exclusion>
<exclusion>
<groupId>org.glassfish</groupId>
<artifactId>javax-el</artifactId>
</exclusion>
<!--<exclusion>-->
<!--<groupId>org.slf4j</groupId>-->
<!--<artifactId>slf4j-log4j12</artifactId>-->
<!--</exclusion>-->
<exclusion>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
二、使用步骤
1.配置Druid连接池
import javax.sql.DataSource;
import lombok.Data;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import com.alibaba.druid.pool.DruidDataSource;
@Data
@Configuration
@ConfigurationProperties(prefix = "hive")
public class HiveDruidConfig {
private String url;
private String user;
private String password;
private String driverClassName;
private int initialSize;
private int minIdle;
private int maxActive;
private int maxWait;
private int timeBetweenEvictionRunsMillis;
private int minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private int maxPoolPreparedStatementPerConnectionSize;
@Bean(name = "hiveDruidDataSource")
@Qualifier("hiveDruidDataSource")
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(user);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
// pool configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
return datasource;
}
@Bean(name = "hiveDruidTemplate")
public JdbcTemplate hiveDruidTemplate(@Qualifier("hiveDruidDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
2. 配置application.yml
url、用户名需要改成自己的
hive:
url: jdbc:hive2://hadoop:10000/default
driver-class-name: org.apache.hive.jdbc.HiveDriver
type: com.alibaba.druid.pool.DruidDataSource
user: abc
password:
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: select 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
3. 编写controller进行测试
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* 使用 DataSource 操作 Hive
*/
@RestController
public class HiveDataSourceController {
private static final Logger logger = LoggerFactory.getLogger(HiveDataSourceController.class);
@Autowired
@Qualifier("hiveDruidDataSource")
DataSource druidDataSource;
/**
* 测试spring boot是否正常启动
*/
@RequestMapping("/")
public String hello(){
return "hello world";
}
/**
* 列举当前Hive库中的所有数据表
*/
@RequestMapping("/table/list")
public List<String> listAllTables() throws SQLException {
List<String> list = new ArrayList<String>();
Statement statement = druidDataSource.getConnection().createStatement();
String sql = "show tables";
logger.info("Running: " + sql);
ResultSet res = statement.executeQuery(sql);
while (res.next()) {
list.add(res.getString(1));
}
return list;
}
/**
* 查询指定tableName表中的数据
*/
@RequestMapping("/table/select")
public List<String> selectFromTable(String tableName) throws SQLException {
List<String> list = new ArrayList<String>();
Statement statement = druidDataSource.getConnection().createStatement();
String sql = "select * from " + tableName;
logger.info("Running: " + sql);
ResultSet res = statement.executeQuery(sql);
int count = res.getMetaData().getColumnCount();
String str = null;
while (res.next()) {
str = "";
for (int i = 1; i < count; i++) {
str += res.getString(i) + " ";
}
str += res.getString(count);
logger.info(str);
list.add(str);
}
return list;
}
}
使用table/list实测可以连上
三、网页显示
接收提交的表单数据,并做一些处理后作为查询条件,将查询的结果放在model中,并在网页中显示
1. controller修改
想在网页上显示查询出来的数据的话,还需要修改一下上面的controller
将@RequestMapping("/table/select")改为下面的代码
封装一个Record类用来接收数据。
/**
* 查询指定tableName表中的数据,这是一个普通的controller
*/
@PostMapping("/table/select")
public String selectFromTable(@RequestParam(required = false) String name,
Model model) throws SQLException {
String tableName = "sale_statistics";
List<Record> list = new ArrayList<>();
Statement statement = druidDataSource.getConnection().createStatement();
String sql = "";
/*
此处添加需查询的sql
*/
logger.info("Running: " + sql);
ResultSet res = statement.executeQuery(sql);
int count = res.getMetaData().getColumnCount();
while (res.next()) {
Record record = new Record();
/*
此处将查询到的数据封装到javabean中,如
record.setName(res.getString(1));
下标从1开始
*/
logger.info(record.toString());
list.add(record);
}
model.addAttribute("records",list); //将结果放在model中
return "result"; //返回result页面
}
2. 网页编写
需要注意的就是使用thymeleaf的each标签,可以对数据进行遍历
(thymeleaf不支持高并发)
<tr>
<th>名字</th>
</tr>
<tr th:each="record : ${records}">
<td th:text="${record.name}"></td>
</tr>
四、一些问题
1. jar包冲突问题
表现为
Failed to start component [StandardEngine[Tomcat].StandardHost[localhost].TomcatEmbeddedContext[]]
或日志下面明确给你说了哪两个jar包冲突。
解决方法看上方依赖引入处
2. thymeleaf检查报错
表现为网页能正常显示查询的数据,但是下面代码的name处有红色波浪线。
<td th:text="${record.name}"></td>
并提示Validates unresolved references and invalid expressions
解决方法:网上都说是误报,关闭thymeleaf的自动检查就行了。我一开始报红,没管他,后面跑着跑着就没了,不知道为什么。
3. 日志jar包冲突
在idea中不影响使用,据说打成jar包可能会报错。
表现为
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/C:/Users/.m2/repository/ch/qos/logback/logback-classic/1.2.3/logback-classic-1.2.3.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/Users/.m2/repository/org/apache/logging/log4j/log4j-slf4j-impl/2.14.1/log4j-slf4j-impl-2.14.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/Users/.m2/repository/org/slf4j/slf4j-log4j12/1.7.30/slf4j-log4j12-1.7.30.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [ch.qos.logback.classic.util.ContextSelectorStaticBinder]
实际上使用的是logback
其中一个是hive-jdbc里面zookeeper中的,上面依赖处已经去除
另一个疑似hive-jdbc里面元数据中的,上面依赖处已经去除
完整代码已上传码云
https://gitee.com/mao_com/spring-boot---hive.git