使用Java通过JDBC连接 Phoenix

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * The class test.
 * <p>
 * Description:
 *
 * @author: bianhaonan
 * @since: 2017/07/20
 * @version:
 */
public class PhoenixJDBCTest {
    @Test
    public void PhoenixJDBCTest(){
        System.out.println(getConnection());
    }

    @Test
    public void create(){
        Connection conn = null;
        try {
            // 获得链接
            conn = getConnection();

            // 检查链接是否为空
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // 检查表是否存在
            ResultSet rs = conn.getMetaData().getTables(null, null, "USER",null);
            if (rs.next()) {
                System.out.println("table user is exist...");
                return;
            }
            // 定义创建表sql
            String sql = "CREATE TABLE user (id varchar PRIMARY KEY,INFO.account varchar ,INFO.passwd varchar)";

            PreparedStatement ps = conn.prepareStatement(sql);

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

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

    @Test
    public void add(){
        Connection conn = null;
        try {
            // 获得链接
            conn = getConnection();

            // 检查链接是否为空
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // 插入数据sql
            String sql = "upsert into user(id, INFO.account, INFO.passwd) values('001', 'admin', 'admin')";

            PreparedStatement ps = conn.prepareStatement(sql);

            // 插入数据
            String msg = ps.executeUpdate() > 0 ? "insert success..."
                    : "insert fail...";

            // 手动提交 *必须项,不然数据插入不成功*
            conn.commit();
            System.out.println(msg);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    @Test
    public void select(){
        Connection conn = null;
        try {
            // 获得链接
            conn = getConnection();

            // 检查链接是否为空
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // 查询sql
            String sql = "select * from user";

            PreparedStatement ps = conn.prepareStatement(sql);

            // 查询数据
            ResultSet rs = ps.executeQuery();

            System.out.println("id" + "\t" + "account" + "\t" + "passwd");
            System.out.println("======================");

            if (rs != null) {
                while (rs.next()) {
                    System.out.print(rs.getString("id") + "\t");
                    System.out.print(rs.getString("account") + "\t");
                    System.out.println(rs.getString("passwd"));
                }
            }

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

    @Test
    public void deleteData(){
        Connection conn = null;
        try {
            // 获得链接
            conn = getConnection();

            // 检查链接是否为空
            if (conn == null) {
                System.out.println("conn is null...");
                return;
            }

            // 创建sql
            String sql = "delete from user where id='001'";

            PreparedStatement ps = conn.prepareStatement(sql);

            // 执行sql
            String msg = ps.executeUpdate() > 0 ? "delete success..."
                    : "delete fail...";

            // 手动提交 *必须项,不然数据删除不成功*
            conn.commit();
            System.out.println(msg);

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

    @Test
    public void deleteTable(){
        Connection conn = null;
        try {
            // get connection
            conn = getConnection();

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

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

            PreparedStatement ps = conn.prepareStatement(sql);

            // execute
            ps.execute();

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

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

    public Connection getConnection(){
        try {
            // load driver
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
            // jdbc 的 url 类似为 jdbc:phoenix [ :<zookeeper quorum> [ :<port number> ] [ :<root node> ] ]，
            // 需要引用三个参数：hbase.zookeeper.quorum、hbase.zookeeper.property.clientPort、and zookeeper.znode.parent，
            // 这些参数可以缺省不填而在 hbase-site.xml 中定义。
            return DriverManager.getConnection("jdbc:phoenix:slave1,slave2,slave3:2181");
            //DriverManager.getConnection("jdbc:phoenix:host1,host2:2181");

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}