Spring Boot 集成 Phoenix + HBase

本文默认已安装好HBase1.4.7，不再介绍
Docker 安装HBase 并使用_羁客%的博客-CSDN博客
Windows 安装 HBase 单机_羁客%的博客-CSDN博客

一.安装python

apk add --no-cache python

二.下载|解压Phoenix

wget https://archive.apache.org/dist/phoenix/apache-phoenix-4.13.1-HBase-1.3/bin/apache-phoenix-4.13.1-HBase-1.3-bin.tar.gz
tar -xzvf apache-phoenix-4.13.1-HBase-1.3-bin.tar.gz
mv apache-phoenix-4.13.1-HBase-1.3-bin phoenix-4.13.1

三.复制jar包

cp phoenix-4.13.1-HBase-1.3-server.jar ../hbase/lib/

五.修改hbase-site.xml 配置文件

保持phoenix-4.13.1/bin/hbase-site.xml与hbase/conf/hbase-site.xml的一致，在hbase原有上新增映射设置

<property>
  <name>phoenix.schema.isNamespaceMappingEnabled</name>
   <value>true</value>
</property>
<property>
  <name>phoenix.schema.mapSystemTablesToNamespace</name>
  <value>true</value>
</property>

六.启动使用phoenix

1.启动

./sqlline.py

 2.创建|删除 命名空间

create schema IF NOT EXISTS "user";
drop schema "user";

3.使用命名空间

USE "user";

4.创建表

CREATE TABLE IF NOT EXISTS "student"(
  id VARCHAR NOT NULL primary key, 
  name VARCHAR,
  age VARCHAR,
  sex VARCHAR,
  date Date
);

5.插入测试数据

upsert into "student" (id,name,age,sex,date) values('1001','wangwu1','19','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1002','wangwu2','21','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1003','wangwu3','22','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1004','wangwu4','21','女','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1005','wangwu5','23','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1006','wangwu6','27','女','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1007','wangwu7','23','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1008','wangwu8','20','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1009','wangwu9','24','男','2023-9-18 22:50:15'); 

upsert into "student" (id,name,age,sex,date) values('1010','张三1','19','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1011','张三2','21','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1012','张三3','22','男','2023-9-18 22:50:15');
upsert into "student" (id,name,age,sex,date) values('1013','张三3','22','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1014','张三4','21','女','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1015','张三5','23','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1016','张三6','27','女','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1017','张三7','23','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1018','张三8','20','男','2023-9-18 22:50:15'); 
upsert into "student" (id,name,age,sex,date) values('1019','张三9','24','男','2023-9-18 22:50:15'); 

七.spring boot集成 使用phoenix 

1.引入依赖（版本与下载的phoenix保持一致，不然会连不上）

<!--phoenix core-->
<dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
    <version>4.13.1-HBase-1.3</version>
    <exclusions>
        <exclusion>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.mortbay.jetty</groupId>
            <artifactId>servlet-api-2.5</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.mortbay.jetty</groupId>
            <artifactId>servlet-api-2.5-6.1.14</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!--管理protobuf-->
<dependencyManagement>
  <dependencies>
     <dependency>
        <groupId>com.google.protobuf</groupId>
        <artifactId>protobuf-java</artifactId>
        <version>2.5.0</version>
    </dependency>
  </dependencies>
</dependencyManagement>

2.在resources下创建hbase-site.xml文件

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>phoenix.schema.mapSystemTablesToNamespace</name>
        <value>true</value>
    </property>
    <property>
        <name>phoenix.schema.isNamespaceMappingEnabled</name>
        <value>true</value>
    </property>
</configuration>

3.配置application.yml

spring:
  #数据源
  datasource:
    phoenix:
      driverClassName: org.apache.phoenix.jdbc.PhoenixDriver
      jdbcUrl: jdbc:phoenix:127.0.0.1:2381
      type: com.zaxxer.hikari.HikariDataSource
      initialSize: 10
      maxActive: 20
      minIdle: 1
      maximumPoolSize: 20
      autoCommit: true
      poolName: HikariPool_phoenix
      connectionTestQuery: SELECT 1

4.配置hikairi连接池

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.xxx.xxx.mapper", sqlSessionFactoryRef = "phoenixSessionFactory")
public class HikariPhoenixConfig {

    /**
     * @ConfigurationProperties 读取yml中的配置参数映射成为一个对象
     */
    @Bean(name = "phoenixDateSource")
    @ConfigurationProperties(prefix = "spring.datasource.phoenix")
    public HikariDataSource phoenixDateSource() {
        return new HikariDataSource();
    }

    @Bean(name = "phoenixSessionFactory")
    public SqlSessionFactory phoenixSessionFactory(@Qualifier("phoenixDateSource") DataSource datasource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);

        //-----单路径是扫描
        //mybatis扫描xml所在位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/phoenix/*.xml"));
        return bean.getObject();
    }

    @Bean("phoenixSessionTemplate")
    public SqlSessionTemplate phoenixSessionTemplate(@Qualifier("phoenixSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

5.在spring boot中使用phoenix

(1).controller

import cn.dev33.satoken.util.SaResult;
import com.github.pagehelper.PageInfo;
import lombok.RequiredArgsConstructor;
import org.apache.ibatis.annotations.Param;
import org.springframework.web.bind.annotation.*;


@RequiredArgsConstructor
@RestController
@RequestMapping("/hbase/student")
public class StudentController {

    private final StudentService studentService;

    /**
     * 根据开始、结束行
     *
     * @param pageNumber 当前页
     * @param pageSize   每页显示条数
     * @return 结果
     */
    @RequestMapping("/list")
    public SaResult list(@Param("pageNumber") Integer pageNumber, @Param("pageSize") Integer pageSize) {
        PageInfo<Student> list = studentService.selectList(pageNumber, pageSize);
        return SaResult.data(list);
    }

    /**
     * 根据开始、结束行
     *
     * @param student 参数对象
     * @return 结果
     */
    @PostMapping("/saveOrUpdate")
    public SaResult saveOrUpdate(@RequestBody Student student) {
        studentService.saveOrUpdate(student);
        return SaResult.ok("保存成功！");
    }

    /**
     * 根据开始、结束行
     *
     * @param id id
     * @return 结果
     */
    @GetMapping("/delete")
    public SaResult delete(@RequestParam("id") String id) {
        studentService.delete(id);
        return SaResult.ok("保存成功！");
    }
} 

(2).service

import com.github.pagehelper.PageInfo;


public interface StudentService {

    /**
     * 根据开始、结束行
     *
     * @param pageNumber 当前页
     * @param pageSize   每页显示条数
     * @return 结果
     */
    PageInfo<Student> selectList(Integer pageNumber, Integer pageSize);

    /**
     * 根据开始、结束行
     *
     * @param student 参数对象
     */
    void saveOrUpdate(Student student);

    /**
     * 根据开始、结束行
     *
     * @param id id
     */
    void delete(String id);
}


import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.gyrw.stock.hbase.mapper.StudentMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * <p>
 *
 * </p >
 *
 * @version: V1.0.0
 */
@RequiredArgsConstructor
@Service
public class StudentServiceImpl implements StudentService {

    private final StudentMapper studentMapper;

    /**
     * 根据开始、结束行
     *
     * @param pageNumber 当前页
     * @param pageSize   每页显示条数
     * @return pageInfo
     */
    @Override
    public PageInfo<Student> selectList(Integer pageNumber, Integer pageSize) {
        PageHelper.startPage(pageNumber, pageSize);
        List<Student> students = studentMapper.queryAll();
        return new PageInfo<>(students);
    }

    /**
     * 根据开始、结束行
     *
     * @param student 参数对象
     */
    @Override
    public void saveOrUpdate(Student student) {
        //查询最大的ID值
        Student studentVo = studentMapper.selectMax();
        if (ObjectUtils.isEmpty(studentVo)) {
            student.setId("1001");
        } else if (!StringUtils.hasLength(student.getId())) {
            student.setId(String.valueOf(Long.parseLong(studentVo.getId()) + 1));
        }
        student.setDate(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        studentMapper.saveOrUpdate(student);
    }

    /**
     * 根据开始、结束行
     *
     * @param id id
     */
    @Override
    public void delete(String id) {
        studentMapper.delete(id);
    }
}

(3).mapper

import com.gyrw.stock.hbase.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 学生的 mapper
 * </p >
 *
 * @copyright: Copyright (c) 2023
 * @version: V1.0.0
 */

public interface StudentMapper {

    @Select("SELECT * from \"user\".\"student\"")
    List<Student> queryAll();

    @Select("select * from \"user\".\"student\" order by id desc limit 1")
    Student selectMax();

    @Insert("upsert into \"user\".\"student\" VALUES(#{id},#{name},#{sex},#{age},#{date})")
    void saveOrUpdate(Student student);

    @Delete("DELETE FROM \"user\".\"student\" WHERE id = #{id}")
    void delete(@Param("id") String id);
}