Phoenix增删改查JDBC

1、pom.xml

        <dependency>
            <groupId>org.apache.hbase</groupId>
            <artifactId>hbase-client</artifactId>
            <version>1.2.6</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.phoenix/phoenix-core -->
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>4.12.0-HBase-1.2</version>
        </dependency>

2、jdbc连接类

package com.cn.until;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class PhoenixUtil {
    public static Connection getConnection(){
        try {
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
            Connection connection = null;
           //此处为zookeeper地址，可以为一个或多个：zookeeper的端口号：zookeeper.znode.parent（此处默认为 /hbase默认可不填写)
            connection= DriverManager.getConnection("jdbc:phoenix:master,slaves1,slaves2:2181");     //connection=DriverManager.getConnection("jdbc:phoenix:master,slaves1,slaves2:2181:/hbase-unsecure");
            return connection;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public static void closeConnection(Connection conn){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3、各种增删改查操作

package com.cn.service;

import com.cn.until.PhoenixUtil;
import org.junit.Test;

import java.sql.*;
import java.util.*;

/**
 * 更新数据与插入数据方法一样
 */
public class Phoenix_Hbase_opt {
    public static void main(String[] args){
        //如果不指定列簇，默认使用列簇 0
//        String sql = "CREATE TABLE teacher (id varchar PRIMARY KEY,cf.name varchar ,cf.classroom varchar)";
//        createTable(sql);
//        String sql_insert = "upsert into teacher(id, name, classroom) values('001', 'MrsZhang', '0302')";
//        insertData(sql_insert);
//        String sql_delData = "delete from teacher where id='001'";
//        delData(sql_delData);
//        String sql_delTable = "drop table teacher";
//        dropTable(sql_delTable);
//        List<String> tables = getTables();
//        for (String table:tables) {
//            System.out.println(table);
//        }
        List<Map<String, String>> stuList = getList("STU");
        for (Map<String, String> map:stuList) {
            for (Map.Entry<String, String> entry : map.entrySet()) {
                System.out.println(entry.getKey() + ":" + entry.getValue());
            }
        }
    }

    /**
     * 创建表
     */
    public static void createTable(String sql) {
        Connection conn = PhoenixUtil.getConnection();
        try {
            PreparedStatement sta = conn.prepareStatement(sql);
            sta.execute();
            System.out.println("创建成功...");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                PhoenixUtil.closeConnection(conn);
            }
        }

    }
    /**
     * 插入数据
     */
    public static void insertData(String sql) {
        Connection conn = PhoenixUtil.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            String msg = ps.executeUpdate() >0 ? "插入成功..."
                    :"插入失败...";
            conn.commit();
            System.out.println(msg);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                PhoenixUtil.closeConnection(conn);
            }
        }

    }

    /**
     * 删除数据
     */
    public static void delData(String sql) {
        Connection conn = PhoenixUtil.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            String msg = ps.executeUpdate() > 0 ? "删除成功..."
                    : "删除失败...";
            conn.commit();
            System.out.println(msg);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                PhoenixUtil.closeConnection(conn);
            }
        }

    }

    /**
     * 删除表
     */
    public static void dropTable(String sql) {
        Connection conn = PhoenixUtil.getConnection();
        //String sql = "drop table user";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.execute();
            System.out.println("删除表成功...");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                PhoenixUtil.closeConnection(conn);
            }
        }
    }

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

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (conn != null) {
                PhoenixUtil.closeConnection(conn);
            }
        }
        return tables;
    }
    /**
     * 获取表中的所有数据
     */
    public static List<Map<String, String>> getList(String tableName)  {
        String sql = "SELECT * FROM " + tableName;
        Connection conn = PhoenixUtil.getConnection();
        List<Map<String, String>> resultList = new ArrayList<>();
        try{
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            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);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (conn != null) {
                PhoenixUtil.closeConnection(conn);
            }
        }
        return resultList;
    }
}