一、前言
使用 Spring+Mybatis 操作 Phoenix 和操作其他的关系型数据库（如 Mysql，Oracle）在配置上是基本相同的，下面会分别给出 Spring/Spring Boot 整合步骤，完整代码见本仓库：
- Spring + Mybatis + Phoenix
- SpringBoot + Mybatis + Phoenix
二、Spring + Mybatis + Phoenix
2.1 项目结构
2.2 主要依赖
除了 Spring 相关依赖外，还需要导入 phoenix-core 和对应的 Mybatis 依赖包
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| 1<!--mybatis 依赖包-->
2<dependency>
3 <groupId>org.mybatis</groupId>
4 <artifactId>mybatis-spring</artifactId>
5 <version>1.3.2</version>
6</dependency>
7<dependency>
8 <groupId>org.mybatis</groupId>
9 <artifactId>mybatis</artifactId>
10 <version>3.4.6</version>
11</dependency>
12<!--phoenix core-->
13<dependency>
14 <groupId>org.apache.phoenix</groupId>
15 <artifactId>phoenix-core</artifactId>
16 <version>4.14.0-cdh5.14.2</version>
17</dependency>
18 |
2.3 数据库配置文件
在数据库配置文件 jdbc.properties 中配置数据库驱动和 zookeeper 地址
1
2
3
4
5
| 1# 数据库驱动
2phoenix.driverClassName=org.apache.phoenix.jdbc.PhoenixDriver
3# zookeeper地址
4phoenix.url=jdbc:phoenix:192.168.0.105:2181
5 |
2.4 配置数据源和会话工厂
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
| 1<?xml version="1.0" encoding="UTF-8"?>
2<beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
5 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
6 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
7
8 <!-- 开启注解包扫描-->
9 <context:component-scan base-package="com.heibaiying.*"/>
10
11 <!--指定配置文件的位置-->
12 <context:property-placeholder location="classpath:jdbc.properties"/>
13
14 <!--配置数据源-->
15 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
16 <!--Phoenix 配置-->
17 <property name="driverClassName" value="${phoenix.driverClassName}"/>
18 <property name="url" value="${phoenix.url}"/>
19 </bean>
20
21 <!--配置 mybatis 会话工厂 -->
22 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
23 <property name="dataSource" ref="dataSource"/>
24 <!--指定 mapper 文件所在的位置-->
25 <property name="mapperLocations" value="classpath*:/mappers/**/*.xml"/>
26 <property name="configLocation" value="classpath:mybatisConfig.xml"/>
27 </bean>
28
29 <!--扫描注册接口 -->
30 <!--作用:从接口的基础包开始递归搜索，并将它们注册为 MapperFactoryBean(只有至少一种方法的接口才会被注册;, 具体类将被忽略)-->
31 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
32 <!--指定会话工厂 -->
33 <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
34 <!-- 指定 mybatis 接口所在的包 -->
35 <property name="basePackage" value="com.heibaiying.dao"/>
36 </bean>
37
38</beans>
39 |
2.5 Mybtais参数配置
新建 mybtais 配置文件，按照需求配置额外参数， 更多 settings 配置项可以参考官方文档
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| 1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE configuration
3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
5
6<!-- mybatis 配置文件 -->
7<configuration>
8 <settings>
9 <!-- 开启驼峰命名 -->
10 <setting name="mapUnderscoreToCamelCase" value="true"/>
11 <!-- 打印查询 sql -->
12 <setting name="logImpl" value="STDOUT_LOGGING"/>
13 </settings>
14</configuration>
15 |
2.6 查询接口
1
2
3
4
5
6
7
8
9
10
11
| 1public interface PopulationDao {
2
3 List<USPopulation> queryAll();
4
5 void save(USPopulation USPopulation);
6
7 USPopulation queryByStateAndCity(@Param("state") String state, @Param("city") String city);
8
9 void deleteByStateAndCity(@Param("state") String state, @Param("city") String city);
10}
11 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| 1<!DOCTYPE mapper
2 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4
5<mapper namespace="com.heibaiying.dao.PopulationDao">
6
7
8 <select id="queryAll" resultType="com.heibaiying.bean.USPopulation">
9 SELECT * FROM us_population
10 </select>
11
12 <insert id="save">
13 UPSERT INTO us_population VALUES( #{state}, #{city}, #{population} )
14 </insert>
15
16 <select id="queryByStateAndCity" resultType="com.heibaiying.bean.USPopulation">
17 SELECT * FROM us_population WHERE state=#{state} AND city = #{city}
18 </select>
19
20 <delete id="deleteByStateAndCity">
21 DELETE FROM us_population WHERE state=#{state} AND city = #{city}
22 </delete>
23
24</mapper>
25 |
2.7 单元测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
| 1@RunWith(SpringRunner.class)
2@ContextConfiguration({"classpath:springApplication.xml"})
3public class PopulationDaoTest {
4
5 @Autowired
6 private PopulationDao populationDao;
7
8 @Test
9 public void queryAll() {
10 List<USPopulation> USPopulationList = populationDao.queryAll();
11 if (USPopulationList != null) {
12 for (USPopulation USPopulation : USPopulationList) {
13 System.out.println(USPopulation.getCity() + " " + USPopulation.getPopulation());
14 }
15 }
16 }
17
18 @Test
19 public void save() {
20 populationDao.save(new USPopulation("TX", "Dallas", 66666));
21 USPopulation usPopulation = populationDao.queryByStateAndCity("TX", "Dallas");
22 System.out.println(usPopulation);
23 }
24
25 @Test
26 public void update() {
27 populationDao.save(new USPopulation("TX", "Dallas", 99999));
28 USPopulation usPopulation = populationDao.queryByStateAndCity("TX", "Dallas");
29 System.out.println(usPopulation);
30 }
31
32
33 @Test
34 public void delete() {
35 populationDao.deleteByStateAndCity("TX", "Dallas");
36 USPopulation usPopulation = populationDao.queryByStateAndCity("TX", "Dallas");
37 System.out.println(usPopulation);
38 }
39}
40 |
三、SpringBoot + Mybatis + Phoenix
3.1 项目结构
3.2 主要依赖
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| 1<!--spring 1.5 x 以上版本对应 mybatis 1.3.x (1.3.1)
2 关于更多 spring-boot 与 mybatis 的版本对应可以参见 <a href="http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/">-->
3<dependency>
4 <groupId>org.mybatis.spring.boot</groupId>
5 <artifactId>mybatis-spring-boot-starter</artifactId>
6 <version>1.3.2</version>
7</dependency>
8<!--phoenix core-->
9<dependency>
10 <groupId>org.apache.phoenix</groupId>
11 <artifactId>phoenix-core</artifactId>
12 <version>4.14.0-cdh5.14.2</version>
13</dependency>
14<dependency>
15 |
spring boot 与 mybatis 版本的对应关系：
1.3.x (1.3.1)
1.3 or higher
1.5 or higher
1.2.x (1.2.1)
1.3 or higher
1.4 or higher
1.1.x (1.1.1)
1.3 or higher
1.3 or higher
1.0.x (1.0.2)
1.2 or higher
1.3 or higher
3.3 配置数据源
在 application.yml 中配置数据源，spring boot 2.x 版本默认采用 Hikari 作为数据库连接池，Hikari 是目前 java 平台性能最好的连接池，性能好于 druid。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| 1spring:
2 datasource:
3 #zookeeper 地址
4 url: jdbc:phoenix:192.168.0.105:2181
5 driver-class-name: org.apache.phoenix.jdbc.PhoenixDriver
6
7 # 如果不想配置对数据库连接池做特殊配置的话,以下关于连接池的配置就不是必须的
8 # spring-boot 2.X 默认采用高性能的 Hikari 作为连接池 更多配置可以参考 https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
9 type: com.zaxxer.hikari.HikariDataSource
10 hikari:
11 # 池中维护的最小空闲连接数
12 minimum-idle: 10
13 # 池中最大连接数，包括闲置和使用中的连接
14 maximum-pool-size: 20
15 # 此属性控制从池返回的连接的默认自动提交行为。默认为 true
16 auto-commit: true
17 # 允许最长空闲时间
18 idle-timeout: 30000
19 # 此属性表示连接池的用户定义名称，主要显示在日志记录和 JMX 管理控制台中，以标识池和池配置。 默认值：自动生成
20 pool-name: custom-hikari
21 #此属性控制池中连接的最长生命周期，值 0 表示无限生命周期，默认 1800000 即 30 分钟
22 max-lifetime: 1800000
23 # 数据库连接超时时间,默认 30 秒，即 30000
24 connection-timeout: 30000
25 # 连接测试 sql 这个地方需要根据数据库方言差异而配置 例如 oracle 就应该写成 select 1 from dual
26 connection-test-query: SELECT 1
27
28# mybatis 相关配置
29mybatis:
30 configuration:
31 # 是否打印 sql 语句 调试的时候可以开启
32 log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
33 |
3.4 新建查询接口
上面 Spring+Mybatis 我们使用了 XML 的方式来写 SQL，为了体现 Mybatis 支持多种方式，这里使用注解的方式来写 SQL。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 1@Mapper
2public interface PopulationDao {
3
4 @Select("SELECT * from us_population")
5 List<USPopulation> queryAll();
6
7 @Insert("UPSERT INTO us_population VALUES( #{state}, #{city}, #{population} )")
8 void save(USPopulation USPopulation);
9
10 @Select("SELECT * FROM us_population WHERE state=#{state} AND city = #{city}")
11 USPopulation queryByStateAndCity(String state, String city);
12
13
14 @Delete("DELETE FROM us_population WHERE state=#{state} AND city = #{city}")
15 void deleteByStateAndCity(String state, String city);
16}
17 |
3.5 单元测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| 1@RunWith(SpringRunner.class)
2@SpringBootTest
3public class PopulationTest {
4
5 @Autowired
6 private PopulationDao populationDao;
7
8 @Test
9 public void queryAll() {
10 List<USPopulation> USPopulationList = populationDao.queryAll();
11 if (USPopulationList != null) {
12 for (USPopulation USPopulation : USPopulationList) {
13 System.out.println(USPopulation.getCity() + " " + USPopulation.getPopulation());
14 }
15 }
16 }
17
18 @Test
19 public void save() {
20 populationDao.save(new USPopulation("TX", "Dallas", 66666));
21 USPopulation usPopulation = populationDao.queryByStateAndCity("TX", "Dallas");
22 System.out.println(usPopulation);
23 }
24
25 @Test
26 public void update() {
27 populationDao.save(new USPopulation("TX", "Dallas", 99999));
28 USPopulation usPopulation = populationDao.queryByStateAndCity("TX", "Dallas");
29 System.out.println(usPopulation);
30 }
31
32
33 @Test
34 public void delete() {
35 populationDao.deleteByStateAndCity("TX", "Dallas");
36 USPopulation usPopulation = populationDao.queryByStateAndCity("TX", "Dallas");
37 System.out.println(usPopulation);
38 }
39
40}
41
42 |
附：建表语句
上面单元测试涉及到的测试表的建表语句如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 1CREATE TABLE IF NOT EXISTS us_population (
2 state CHAR(2) NOT NULL,
3 city VARCHAR NOT NULL,
4 population BIGINT
5 CONSTRAINT my_pk PRIMARY KEY (state, city));
6
7-- 测试数据
8UPSERT INTO us_population VALUES('NY','New York',8143197);
9UPSERT INTO us_population VALUES('CA','Los Angeles',3844829);
10UPSERT INTO us_population VALUES('IL','Chicago',2842518);
11UPSERT INTO us_population VALUES('TX','Houston',2016582);
12UPSERT INTO us_population VALUES('PA','Philadelphia',1463281);
13UPSERT INTO us_population VALUES('AZ','Phoenix',1461575);
14UPSERT INTO us_population VALUES('TX','San Antonio',1256509);
15UPSERT INTO us_population VALUES('CA','San Diego',1255540);
16UPSERT INTO us_population VALUES('CA','San Jose',912332);
17 |
更多大数据系列文章可以参见 GitHub 开源项目： 大数据入门指南
转载于:https://www.cnblogs.com/heibaiying/p/11418690.html
