【十二】Phoenix Java API操作

项目目录

phoenix.properties

phoenix.driver=org.apache.phoenix.jdbc.PhoenixDriver
phoenix.url=jdbc:phoenix:node1:2181
phoenix.user=root
phoenix.password=密码

pom.xml

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sid.hbase</groupId>
    <artifactId>hbase-train</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <hbase.version>1.4.4</hbase.version>
        <hadoop.version>2.9.0</hadoop.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>4.14.0-HBase-1.4</version>
        </dependency>

    </dependencies>
</project>

configutils.java

package com.sid.hbase;

import java.io.IOException;
import java.util.Properties;

public class ConfigUtils {
    public static Properties p =new Properties();

    static {
        try{
            p.load(ClassLoader.getSystemResourceAsStream("phoenix.properties"));
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    public static String getDriver() throws IOException {
        p.load(ClassLoader.getSystemResourceAsStream("phoenix.properties"));
        return p.getProperty("phoenix.driver");
    }

    public static String getUrl(){
        return p.getProperty("phoenix.url");
    }

    public static String getUserName(){
        return p.getProperty("phoenix.user");
    }

    public static String getPassWord(){
        return p.getProperty("phoenix.password");
    }

    public static void main(String[] args) throws IOException {


        System.out.println(getDriver());
        System.out.println(getUrl());
        System.out.println(getUserName());
        System.out.println(getPassWord());
    }
}

PhoenixTest.java

package com.sid.hbase;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

public class PhoenixTest {
    private Connection conn;
    private Statement stat;
    private ResultSet rs;

    @Before
    public void initResource() throws Exception{
        Class.forName(ConfigUtils.getDriver());
        conn = DriverManager.getConnection(ConfigUtils.getUrl(), ConfigUtils.getUserName(), ConfigUtils.getPassWord());
        stat = conn.createStatement();

    }

    @Test
    public void testCreateTable() throws SQLException {
        String sql="create table test_phoenix_api(mykey integer not null primary key ,mycolumn varchar )";
        stat.executeUpdate(sql);
        conn.commit();
    }

    @Test
    public void upsert() throws SQLException {
        String sql1="upsert into test_phoenix_api values(1,'test1')";
        String sql2="upsert into test_phoenix_api values(2,'test2')";
        String sql3="upsert into test_phoenix_api values(3,'test3')";
        stat.executeUpdate(sql1);
        stat.executeUpdate(sql2);
        stat.executeUpdate(sql3);
        conn.commit();
    }

    @Test
    public void delete() throws SQLException {
        String sql1="delete from test_phoenix_api where mykey = 1";
        stat.executeUpdate(sql1);
        conn.commit();
    }



    @After
    public void closeResource() throws SQLException {
        if(rs!=null){
            rs.close();
        }
        if(stat!=null){
            stat.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
}

分别运行创建表、插入数据、删除数据后在phoenix中查看结果

0: jdbc:phoenix:node1,node2,node3,node4> !table
+------------+--------------+-------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-----+
| TABLE_CAT  | TABLE_SCHEM  |    TABLE_NAME     |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | INDEX_STATE  | IMMUTABLE_ROWS  | SALT_BUCKETS  | MUL |
+------------+--------------+-------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-----+
|            | SYSTEM       | CATALOG           | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | fal |
|            | SYSTEM       | FUNCTION          | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | fal |
|            | SYSTEM       | LOG               | SYSTEM TABLE  |          |            |                            |                 |              | true            | 32            | fal |
|            | SYSTEM       | SEQUENCE          | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | fal |
|            | SYSTEM       | STATS             | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | fal |
|            |              | TEST_PHOENIX_API  | TABLE         |          |            |                            |                 |              | false           | null          | fal |
|            |              | US_POPULATION     | TABLE         |          |            |                            |                 |              | false           | null          | fal |
|            |              | wc                | VIEW          |          |            |                            |                 |              | false           | null          | fal |
+------------+--------------+-------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-----+
0: jdbc:phoenix:node1,node2,node3,node4> select * from test_phoenix_api
. . . . . . . . . . . . . . . . . . . .> ;
+--------+-----------+
| MYKEY  | MYCOLUMN  |
+--------+-----------+
| 1      | test1     |
| 2      | test2     |
| 3      | test3     |
+--------+-----------+
3 rows selected (0.325 seconds)
0: jdbc:phoenix:node1,node2,node3,node4> select * from test_phoenix_api;
+--------+-----------+
| MYKEY  | MYCOLUMN  |
+--------+-----------+
| 2      | test2     |
| 3      | test3     |
+--------+-----------+
2 rows selected (0.14 seconds)
0: jdbc:phoenix:node1,node2,node3,node4>