HBase13(项目03phoenix视图JDBC开发）

1.phoenix视图建立

当创建视图后，就可以使用SQL查询视图，和操作Table一样。

1.视图如何映射到HBase的表？

视图的名字必须是：命名空间.表名

2.视图中的列如何映射到HBase的列族和列？

列名必须是：列族.列名

3.视图中的类如何映射到HBase的ROWKEY？

指定某个列为primary key，自动映射ROWKEY

例子：建立MOMO_CHAT:MSG 视图

create view if not exists "MOMO_CHAT"."MSG"(
    id  varchar primary key,
    "C1"."msg_time" varchar,
    "C1"."sender_nickyname" varchar,
    "C1"."sender_account" varchar,
    "C1"."sender_sex" varchar,
    "C1"."sender_ip" varchar,
    "C1"."sender_os" varchar,
    "C1"."sender_phone_type" varchar,
    "C1"."sender_network" varchar,
    "C1"."sender_gps" varchar,
    "C1"."receiver_nickyname" varchar,
    "C1"."receiver_ip" varchar,
    "C1"."receiver_account" varchar,
    "C1"."receiver_os" varchar,
    "C1"."receiver_phone_type" varchar,
    "C1"."receiver_network" varchar,
    "C1"."receiver_gps" varchar,
    "C1"."receiver_sex" varchar,
    "C1"."msg_type" varchar,
    "C1"."distance" varchar,
    "C1"."message" varchar
);

条件查询

select * from "MOMO_CHAT"."MSG"
where substr("msg_time", 0, 10) = '2023-09-16'
and "sender_account" = '18862883206'
and "receiver_account" = '13869783495';

 2.编写java代码

1. 编写PhoenixChatMessageService实现ChatMessageService接口
2. 在构造器中创建JDBC连接

a) JDBC驱动为：PhoenixDriver.class.getName()

b) JDBC连接URL为：jdbc:phoenix:node1.itcast.cn:2181

1. 基于JDBC实现getMessage查询
2. 在close方法中

package cn.itcast.momo_chat.service.impl;

import cn.itcast.momo_chat.entity.Msg;
import cn.itcast.momo_chat.service.ChatMessageService;
import org.apache.phoenix.jdbc.PhoenixDriver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class PhoenixChatMessageService implements ChatMessageService {

    private Connection connection;

    public PhoenixChatMessageService() throws Exception {
        // 1. 加载驱动
        Class.forName(PhoenixDriver.class.getName());
        // 2. 获取JDBC连接
        connection = DriverManager.getConnection("jdbc:phoenix:node1.itcast.cn:2181");
    }

    @Override
    public List<Msg> getMessage(String date, String sender, String receiver) throws Exception {
        // 1. SQL语句
        String sql = "select * from \"MOMO_CHAT\".\"MSG\" where substr(\"msg_time\", 0, 10) = ? and \"sender_account\" = ? and \"receiver_account\" = ?";

        // 2. 构建一个prepareStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 3. 设置Prestatement对应的参数
        preparedStatement.setString(1, date);
        preparedStatement.setString(2, sender);
        preparedStatement.setString(3, receiver);

        // 4. 执行SQL语句，获取到一个ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<Msg> msgList = new ArrayList<>();

        // 5. 迭代ResultSet将数据封装在Msg里面
        while(resultSet.next()) {
            Msg msg = new Msg();
            msg.setMsg_time(resultSet.getString("msg_time"));
            msg.setSender_nickyname(resultSet.getString("sender_nickyname"));
            msg.setSender_account(resultSet.getString("sender_account"));
            msg.setSender_sex(resultSet.getString("sender_sex"));
            msg.setSender_ip(resultSet.getString("sender_ip"));
            msg.setSender_os(resultSet.getString("sender_os"));
            msg.setSender_phone_type(resultSet.getString("sender_phone_type"));
            msg.setSender_network(resultSet.getString("sender_network"));
            msg.setSender_gps(resultSet.getString("sender_gps"));
            msg.setReceiver_nickyname(resultSet.getString("receiver_nickyname"));
            msg.setReceiver_ip(resultSet.getString("receiver_ip"));
            msg.setReceiver_account(resultSet.getString("receiver_account"));
            msg.setReceiver_os(resultSet.getString("receiver_os"));
            msg.setReceiver_phone_type(resultSet.getString("receiver_phone_type"));
            msg.setReceiver_network(resultSet.getString("receiver_network"));
            msg.setReceiver_gps(resultSet.getString("receiver_gps"));
            msg.setReceiver_sex(resultSet.getString("receiver_sex"));
            msg.setMsg_type(resultSet.getString("msg_type"));
            msg.setDistance(resultSet.getString("distance"));
            msg.setMessage(resultSet.getString("message"));

            msgList.add(msg);
        }

        // 关闭资源
        resultSet.close();
        preparedStatement.close();

        return msgList;
    }

    @Override
    public void close() throws Exception {
        connection.close();
    }
}

package service;

import cn.itcast.momo_chat.entity.Msg;
import cn.itcast.momo_chat.service.ChatMessageService;
import cn.itcast.momo_chat.service.impl.HBaseNativeChatMessageService;
import org.junit.Test;

import java.util.List;

public class ChatMessageServiceTest {
    private ChatMessageService chatMessageService;

    public ChatMessageServiceTest() throws Exception {
        chatMessageService = new HBaseNativeChatMessageService();
//        chatMessageService = new PhoenixChatMessageService();
    }

    @Test
    public void getMesage() throws Exception {
        List<Msg> message = chatMessageService.getMessage("2023-09-16", "18862883206", "13869783495");
        for (Msg msg : message) {
            System.out.println(msg);
        }
    }
}