mybatis mysql upsert_SpringBoot2.3.4+Mybatis+Phoenix操作HBase2.0.6

SpringBoot2.3.4+Mybatis+Phoenix操作HBase2.0.6

1、环境说明

使用单机版安装的hbase，版本为2.0.6，phoenix-core的版本使用5.0.0-HBase-2.0需与hbase版本匹配，使用mybatis直接操作hbase，大部分语法与操作mysql相同，有小部分差异，如insert or update在此需改为upsert表示插入或更新，同时不支持批量插入语法insert into table values(),();

2、配置编写应用代码

创建一个用户表，可以在phoenix客户端直接操作

create table if not exists ioe_user (

id bigint not null primary key,

nick_name varchar(50),

name varchar(20),

phone varchar(20),

address varchar(100),

gender integer,

birthday date,

registry_time date,

update_time date

);

编写pom.xml

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.3.4.RELEASE

com.ioe

phoenix-hbase

0.0.1-SNAPSHOT

phoenix-hbase

Demo project for Spring Boot

1.8

org.springframework.boot

spring-boot-starter-web

org.projectlombok

lombok

true

org.springframework.boot

spring-boot-starter-test

test

org.junit.vintage

junit-vintage-engine

org.apache.hadoop

hadoop-client

3.3.0

org.slf4j

slf4j-log4j12

com.alibaba

druid

1.1.14

org.apache.phoenix

phoenix-core

5.0.0-HBase-2.0

org.slf4j

slf4j-log4j12

log4j

log4j

org.apache.hadoop

hadoop-common

2.9.2

log4j

log4j

1.2.17

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.1.3

com.alibaba

fastjson

1.2.72

org.springframework.boot

spring-boot-maven-plugin

编写类User UserMapper Aggregate

package com.ioe.hbase.entity;

import lombok.Getter;

import lombok.Setter;

import java.util.Date;

@Getter

@Setter

public class User {

private Long id;

private String nickName;

private String name;

private String phone;

private String address;

private Integer gender;

private Date birthday;

private Date registryTime;

private Date updateTime;

}

package com.ioe.hbase.mapper;

import com.ioe.hbase.entity.User;

import com.ioe.hbase.vo.Aggregate;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.Update;

import java.util.Date;

import java.util.List;

@Mapper

public interface UserMapper {

@Select("select * from ioe_user order by registry_time desc limit 10 offset 0 ")

List findLatest();

@Update("upsert into ioe_user(id,nick_name) values(#{id},#{nickName})")

void update(@Param("id") Long id, @Param("nickName") String nickName);

@Update("upsert into ioe_user(id,nick_name,name,phone,address,gender,birthday,registry_time,update_time) " +

"values(#{id},#{nickName},#{name},#{phone},#{address},#{gender},#{birthday},#{registryTime},#{updateTime})")

void insert(@Param("id") Long id, @Param("nickName") String nickName, @Param("name") String name, @Param("phone") String phone,

@Param("address") String address, @Param("gender") Integer gender, @Param("birthday") Date birthday,

@Param("registryTime") Date registryTime, @Param("updateTime") Date updateTime);

@Select("select count(*) from ioe_user ")

Long count();

@Select("select gender,count(*) count from ioe_user group by gender")

List aggregate();

@Update("delete from ioe_user where id=#{id}")

void delete(Long id);

}

package com.ioe.hbase.vo;

import lombok.Getter;

import lombok.Setter;

@Getter

@Setter

public class Aggregate{

private Integer gender;

private Long count;

}

编写配置文件application.yml

server:

port: 8081

spring:

datasource:

driver-class-name: org.apache.phoenix.jdbc.PhoenixDriver

url: jdbc:phoenix:192.168.2.5:2181

type: com.alibaba.druid.pool.DruidDataSource

mybatis:

type-aliases-package: com.ioe.hbase.entity

configuration:

map-underscore-to-camel-case: true

logging:

level:

root: info

com.ioe.hbase: debug

编写PhoenixHBaseApplication PhoenixHBaseTest

package com.ioe.hbase;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.ioe.hbase.mapper")

@SpringBootApplication

public class PhoenixHBaseApplication {

public static void main(String[] args) {

SpringApplication.run(PhoenixHBaseApplication.class, args);

}

}

package com.ioe.hbase;

import com.alibaba.fastjson.JSON;

import com.ioe.hbase.mapper.UserMapper;

import com.ioe.hbase.vo.Aggregate;

import lombok.extern.slf4j.Slf4j;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;

import java.util.List;

@Slf4j

@SpringBootTest

public class PhoenixHBaseTest {

@Autowired

private UserMapper userMapper;

@Test

void insert() {

userMapper.insert(1L, "扬帆起航", "邝小", "13800000000", "北京市东城区", 1, new Date(), new Date(), new Date());

}

@Test

void update() {

userMapper.update(1L, "扬帆启航");

}

@Test

void aggregate() {

List result = userMapper.aggregate();

result.forEach(e -> log.info(JSON.toJSONString(e)));

}

@Test

void count() {

log.info("{}", userMapper.count());

}

@Test

void delete() {

userMapper.delete(1L);

}

}

3、测试运行

运行测试用例，可以看到mybatis对应的sql语句

: ==> Preparing: delete from ioe_user where id=?

: ==> Parameters: 1(Long)

: <== Updates: 1

: ==> Preparing: upsert into ioe_user(id,nick_name,name,phone,address,gender,birthday,registry_time,update_time) values(?,?,?,?,?,?,?,?,?)

: ==> Parameters: 1(Long), 扬帆起航(String), 邝小(String), 13800000000(String), 北京市东城区(String), 1(Integer), 2020-01-25 22:47:47.652(Timestamp), 2020-01-25 22:47:47.652(Timestamp), 2020-01-25 22:47:47.652(Timestamp)

: <== Updates: 1

: ==> Preparing: upsert into ioe_user(id,nick_name) values(?,?)

: ==> Parameters: 1(Long), 扬帆启航(String)

: <== Updates: 1

: ==> Preparing: select count(*) from ioe_user

: ==> Parameters:

: <== Total: 1

: 2

: ==> Preparing: select gender,count(*) count from ioe_user group by gender

: ==> Parameters:

: <== Total: 2

: {"count":1,"gender":1}

: {"count":1,"gender":2}

4、安装使用DBeaver操作HBase

1)、下载DBeaver安装包dbeaver-ce-7.1.3-win32.win32.x86_64.zip，解压后进入目录，在配置文件dbeaver.ini中增加以下配置指定jdk

-vm

C:\Program Files\jdk1.8.0_66\bin

2)、运行dbeaver.exe，选择创建新连接，选中“Hadoop/BigData”栏的“Apache Phoenix”点击下一步，编辑驱动设置中添加驱动文件phoenix-5.0.0-HBase-2.0-client.jar，点选“找到类”即可搜索到对应的驱动类，如图所示；

image.png

image.png

image.png