借助 Apache Phoenix，使用标准 SQL 和 JDBC 接口来操作 HBase

注：本篇博客是对 https://www.cnblogs.com/shanheyongmu/p/15661006.html 这篇博客的补充与实践。 在此膜拜大佬!d(ﾟ∀ﾟd)点赞!

点击查看代码

package com.example;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class HelloController {

    //phoenix驱动
    private String phoenixDriver = "org.apache.phoenix.jdbc.PhoenixDriver";
    //zookeeper地址
    private String phoenixURL = "jdbc:phoenix:node1:2181";

    @GetMapping("/test")
    public void test() throws Exception {
        // 创建表
        System.out.println("\n--- 开始创建 student 表 ---");
        createTable();

        // 获取Phoenix中的表(系统表除外)
        System.out.println("\n--- 获取Phoenix中的表(系统表除外) ---");
        List<String> tables = getTables();
        System.out.println(tables);

        // 插入数据
        System.out.println("\n--- 开始插入数据 ---");
        insertData();

        // 删除数据
        System.out.println("\n--- 开始删除数据 ---");
        deleteData();

        // 查询数据
        System.out.println("\n--- 开始查询数据 ---");
        List<Map<String, String>> list = getData("\"student\"");
        System.out.println(list);

        //删除表
        System.out.println("\n--- 开始删除 student 表 ---");
        dropTable();
    }

    // 获取连接
    public Connection getConnection() throws Exception {
        Class.forName(phoenixDriver);
        return DriverManager.getConnection(phoenixURL);
    }

    // 创建表
    public void createTable() throws Exception {
        //获取连接
        Connection connection = getConnection();
        // 创建Statement对象
        String sql = "CREATE TABLE IF NOT EXISTS \"student\"(" +
                "id VARCHAR primary key," +
                "name VARCHAR," +
                "age VARCHAR)";
        PreparedStatement statement = connection.prepareStatement(sql);
        // 执行sql操作
        statement.execute();
        // 关闭
        statement.close();
        connection.close();
    }

    // 获取Phoenix中的表(系统表除外)
    public List<String> getTables() throws Exception {
        //获取连接
        Connection connection = getConnection();
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        String[] types = {"TABLE"}; //"SYSTEM TABLE"
        ResultSet resultSet = metaData.getTables(null, null, null, types);
        while (resultSet.next()) {
            tables.add(resultSet.getString("TABLE_NAME"));
        }
        return tables;
    }

    // 删除表
    public void dropTable() throws Exception {
        //获取连接
        Connection connection = getConnection();
        // 创建Statement对象
        String sql = "DROP TABLE \"student\"";
        PreparedStatement statement = connection.prepareStatement(sql);
        // 执行sql操作
        statement.execute();
        // 关闭
        statement.close();
        connection.close();
    }

    // 插入数据
    public void insertData() throws Exception {
        //获取连接
        Connection connection = getConnection();

        //获取Statement对象，并进行数据插入
        Statement statement = connection.createStatement();
        statement.executeUpdate("upsert into \"student\" values('1001','大刘','20')");
        statement.executeUpdate("upsert into \"student\" values('1002','小星','22')");
        connection.commit();
        statement.close();

        //获取PreparedStatement对象，并进行数据插入
        PreparedStatement preparedStatement = connection.prepareStatement(
                "upsert into \"student\" values(?,?,?)");
        //给参数赋值
        preparedStatement.setString(1,"1003");
        preparedStatement.setString(2,"hangge");
        preparedStatement.setString(3,"1000");
        //执行插入
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();

        connection.close();
    }

    // 删除数据
    public void deleteData() throws Exception {
        //获取连接
        Connection connection = getConnection();

        //获取Statement对象，并进行数据删除
        Statement statement = connection.createStatement();
        statement.execute("delete from \"student\" where id = '1002'");
        connection.commit();
        statement.close();
        connection.close();
    }

    // 查询数据（获取表中的所有数据）
    public List<Map<String, String>> getData(String tableName) throws Exception {
        //获取连接
        Connection connection = getConnection();
        String sql = "SELECT * FROM " + tableName;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        List<Map<String, String>> resultList = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, String> result = new HashMap<>();
            for (int i = 1, len = resultSetMetaData.getColumnCount(); i <= len; i++) {
                result.put(resultSetMetaData.getColumnName(i), resultSet.getString(i));
            }
            resultList.add(result);
        }
        return resultList;
    }
}

注意事项：

遇到问题：

Class [org.apache.jasper.servlet.JspServlet] is not a Servlet
Caused by: java.lang.ClassCastException: class org.apache.jasper.servlet.JspServlet cannot be cast to class jakarta.servlet.Servlet

这是一个典型的 JSP 与 Tomcat 10 兼容性 问题。

直接用我的pom.xml文件就好了，但是Phoenix版本和springboot版本尽量和我一致。

点击查看代码

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.5.6</version>
        <relativePath/>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>back-Phoenix</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>back-Phoenix</name>
    <description>back-Phoenix</description>

    <properties>
        <java.version>17</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-tomcat</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- 使用 Jetty 替代 Tomcat -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jetty</artifactId>
        </dependency>

        <!-- 引入log4j2依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>

        <!-- phoenix相关依赖配置 -->
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>5.0.0-HBase-2.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>joda-time</groupId>
                    <artifactId>joda-time</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.google.code.gson</groupId>
                    <artifactId>gson</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.servlet.jsp</groupId>
                    <artifactId>jsp-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.glassfish.web</groupId>
                    <artifactId>javax.servlet.jsp</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.mortbay.jetty</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>tomcat</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.9.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.google.code.gson</groupId>
                    <artifactId>gson</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.servlet.jsp</groupId>
                    <artifactId>jsp-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.mortbay.jetty</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.3</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.apache.tomcat.embed</groupId>
                            <artifactId>tomcat-embed-jasper</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

注意直接运行大概率会碰到

java.sql.SQLException: ERROR 726 (43M10): Inconsistent namespace mapping properties. Cannot initiate connection as SYSTEM:CATALOG is found but client does not have phoenix.schema.isNamespaceMappingEnabled enabled

这是因为Phoenix 开启了 SCHEMA。

直接参考 https://www.cnblogs.com/shanheyongmu/p/15661287.html
这篇博客中关闭SCHEMA就好了。