Spring Boot + MyBatis + Phoenix集成示例

1. 初始化数据

/usr/local/Cellar/phoenix/apache-phoenix-4.14.1-HBase-1.2-bin/examples/init.sql

-- 创建订单
CREATE TABLE IF NOT EXISTS tbl_order (
	id BIGINT not null primary key,
	order_code char(20),
	total_amount decimal(10,2),
	create_time date,
	user_id bigint
);

-- 插入数据
upsert into tbl_order values(1, 'A001', 10.5, '2019-3-19 23:35:00', 1);
upsert into tbl_order values(2, 'A002', 60.0, '2019-3-19 23:36:00', 2);
upsert into tbl_order values(3, 'B001', 66.6, '2019-3-20 01:01:00', 3);
upsert into tbl_order values(4, 'C001', 66.4, '2019-3-20 02:01:00', 3);

执行sql文件

cd /usr/local/Cellar/phoenix/apache-phoenix-4.14.1-HBase-1.2-bin/bin
./psql.py localhost:2181 ../examples/init.sql

2. pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.14</version>
</dependency>
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-client</artifactId>
    <version>3.2.0</version>
    <exclusions>
        <exclusion>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<!-- phoenix-core 版本号要和本地安装的phoenix版本号保持一致 -->
<dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
    <version>4.14.1-HBase-1.2</version>
    <exclusions>
        <exclusion>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
        </exclusion>
        <exclusion>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

3. application.properties

# datasource
spring.datasource.driver-class-name=org.apache.phoenix.jdbc.PhoenixDriver
spring.datasource.url=jdbc:phoenix:127.0.0.1:2181
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.data-username=
spring.datasource.data-password=

# mybatis
mybatis.typeAliasesPackage=com.example.phoenix.entity
mybatis.mapperLocations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

logging.level.com.example.phoenix.mapper=debug

4. entity

@Data
@ToString
@RequiredArgsConstructor
public class Order {
    private Long id;
    private String orderCode;
    private BigDecimal totalAmount;
    private Date createTime;
    private Long userId;
}

5. mapper

public interface OrderMapper {
    List<Order> getOrders();

    void updateOrder(@Param("id") Long id, @Param("totalAmount") BigDecimal totalAmount);
}

6. mapper/OrderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.phoenix.mapper.OrderMapper">
    
    <select id="getOrders" resultType="Order">
        SELECT * FROM tbl_order
        WHERE id >= 1
        ORDER BY create_time DESC
        LIMIT 2 OFFSET 1
    </select>

    <update id="updateOrder">
        UPSERT INTO tbl_order(id, total_amount) VALUES(#{id}, #{totalAmount})
    </update>

</mapper>

7. Application

@SpringBootApplication
@MapperScan("com.example.phoenix.mapper")
public class SpringbootMybatisPhoenixApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisPhoenixApplication.class, args);
    }

}

8. test

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootMybatisPhoenixApplicationTests {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void testGetOrders() {
        List<Order> orders = orderMapper.getOrders();
        orders.forEach(System.out::println);
    }

    @Test
    public void testUpdateOrder() {
        orderMapper.updateOrder(2L, BigDecimal.valueOf(88.8));
    }
}

注意：首次连接会打印一条info级别的日志，这不是错误。只会在首次连接的时候会打印此日志。