Phoenix（6）：JAVA api 连接phoenix

1.功能作用

使用java 代码，连接phoenix，从而实现hbase数据库的增删改查。

2.配置

（1）添加mysql的maven仓库

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.27</version>
</dependency>

（2）添加phoenix-4.9.0-cdh5.7.0-client.jar包，将phoenix对应版本的client添加到项目工程。

3.代码

（1）获取连接

    public static Connection getConnection() {
        try {
            // load driver
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");

            // get connection
            // jdbc 的 url 类似为 jdbc:phoenix [ :<zookeeper quorum> [ :<port number> ] [ :<root node> ] ]，
            return DriverManager.getConnection("jdbc:phoenix:hadoop:2181/hbase");
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

（2）创建表

   public  void create() {
        Connection conn = null;
        try {
            // get connection
            conn = PhoenixJavaAPI.getConnection();

            // check connection
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // check if the table exist
            ResultSet rs = conn.getMetaData().getTables(null, null, "USER01",
                    null);
            if (rs.next()) {
                System.out.println("table user01 is exist...");
                return;
            }
            // create sql
            String sql = "CREATE TABLE user01 (id varchar PRIMARY KEY,INFO.name varchar ,INFO.passwd varchar)";

            PreparedStatement ps = conn.prepareStatement(sql);

            // execute
            ps.execute();
            System.out.println("create success...");

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

    }

（3）插入数据

   public  void upsert() {

        Connection conn = null;
        try {
            // get connection
            conn = PhoenixJavaAPI.getConnection();

            // check connection
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // create sql
            String sql = "upsert into user01(id, INFO.name, INFO.passwd) values('001', 'admin', 'admin')";

            PreparedStatement ps = conn.prepareStatement(sql);

            // execute upsert
            String msg = ps.executeUpdate() > 0 ? "insert success..."
                    : "insert fail...";

            // you must commit
            conn.commit();
            System.out.println(msg);

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

（4）查询

    public  void query() {

        Connection conn = null;
        try {
            // get connection
            conn = PhoenixJavaAPI.getConnection();

            // check connection
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // create sql
            String sql = "select * from us_population";

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();

            System.out.println("state" + "\t" + "city" + "\t" + "population");
            System.out.println("======================");

            if (rs != null) {
                while (rs.next()) {
                    System.out.print(rs.getString("state") + "\t");
                    System.out.print(rs.getString("city") + "\t");
                    System.out.println(rs.getString("population"));
                }
            }

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

    }

（5）删除数据

    public  void delete() {

        Connection conn = null;
        try {
            // get connection
            conn = PhoenixJavaAPI.getConnection();

            // check connection
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // create sql
            String sql = "delete from user01 where id='001'";

            PreparedStatement ps = conn.prepareStatement(sql);

            // execute upsert
            String msg = ps.executeUpdate() > 0 ? "delete success..."
                    : "delete fail...";

            // you must commit
            conn.commit();
            System.out.println(msg);

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

    }

（6）删除表

    public  void drop() {

        Connection conn = null;
        try {
            // get connection
            conn = PhoenixJavaAPI.getConnection();

            // check connection
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // create sql
            String sql = "drop table user01";

            PreparedStatement ps = conn.prepareStatement(sql);

            // execute
            ps.execute();

            System.out.println("drop success...");

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

    }