Java API 操作Phoenix

Java API 操作Phoenix

• 引入相关依赖

  
    <properties>
          <hbase.version>2.0</hbase.version>
          <hadoop.version>2.7.1</hadoop.version>
     </properties>
  <dependency>
              <groupId>org.apache.phoenix</groupId>
              <artifactId>phoenix-core</artifactId>
              <version>5.0.0-HBase-2.0</version>
          </dependency>
  
          <dependency>
              <groupId>org.apache.hadoop</groupId>
              <artifactId>hadoop-common</artifactId>
              <version>2.7.1</version>
          </dependency>
  

• 编写phoenix.properties

  phoenix.driver=org.apache.phoenix.jdbc.PhoenixDriver
  phoenix.url=jdbc:phoenix:Hbase:2181
  phoenix.user=root
  phoenix.password=root
  

• 编写测试类

  package com.junjia;
  
  import org.junit.After;
  import org.junit.Before;
  import org.junit.Test;
  
  import java.sql.*;
  import java.util.ArrayList;
  
  /**
   * @description:
   * @author: fengqianli
   * @create: 2020/7/6 9:51
   **/
  public class PhoenixTest {
      private Connection conn;
      private Statement stat;
      private ResultSet rs;
  
      /**
       * 获取连接
       * @throws Exception
       */
      @Before
      public void initResource() throws Exception{
          Class.forName(ConfigUtils.getDriver());
          conn = DriverManager.getConnection(ConfigUtils.getUrl(), ConfigUtils.getUserName(), ConfigUtils.getPassWord());
          stat = conn.createStatement();
  
      }
  
      /**
       * 创建表
       * @throws SQLException
       */
      @Test
      public void testCreateTable() throws SQLException {
          String sql="";
          stat.executeUpdate(sql);
          conn.commit();
      }
  
      /**
       * 数据插入
       * @throws SQLException
       */
      @Test
      public void upsert() throws SQLException {
          String sql1="upsert into car_order values(1, 'A001', 10.5, '2019-3-19 23:35:00', 1)";
          String sql2="upsert into car_order values(2, 'A002', 10.6, '2019-3-19 23:36:00', 2)";
          String sql3="upsert into car_order values(3, 'A003', 10.7, '2019-4-19 13:35:00', 3)";
          String sql4="upsert into car_order values(4, 'A004', 10.8, '2019-5-20 20:35:00', 4)";
          String sql5="upsert into car_order values(5, 'A005', 10.9, '2019-5-21 11:23:00', 5)";
          String sql6="upsert into car_order values(6, 'A006', 11.0, '2019-5-21 11:13:34', 6)";
          String sql7="upsert into car_order values(7, 'A007', 12.0, '2019-4-19 23:35:00', 7)";
  
          stat.executeUpdate(sql1);
          stat.executeUpdate(sql2);
          stat.executeUpdate(sql3);
          stat.executeUpdate(sql4);
          stat.executeUpdate(sql5);
          stat.executeUpdate(sql6);
          stat.executeUpdate(sql7);
          conn.commit();
      }
  
      /**
       * 依据主键删除数据
       * @throws SQLException
       */
      @Test
      public void delete() throws SQLException {
          String sql1="delete from car_order where id = 1";
          stat.executeUpdate(sql1);
          conn.commit();
      }
  
      /**
       * 查询所有数据
       * @throws SQLException
       */
      @Test
      public void select() throws SQLException {
          String sql1="select * from  car_order";
          ResultSet resultSet = stat.executeQuery(sql1);
          ArrayList<Order> arr = new ArrayList<Order>();
          while(resultSet.next()){
              int id = resultSet.getInt(1);
              String orderCode = resultSet.getString(2);
              float totalAmount = resultSet.getFloat(3);
              Date createTime = resultSet.getDate(4);
              int userId = resultSet.getInt(5);
              Order order = new Order();
              order.setId(id);
              order.setOrderCode(orderCode);
              order.setTotalAmount(totalAmount);
              order.setCreateTime(createTime);
              order.setUserId(userId);
              arr.add(order);
          }
          for (Order order : arr) {
              System.out.println(order);
          }
          conn.commit();
      }
  
  
      /**
       * 关闭连接
       * @throws SQLException
       */
      @After
      public void closeResource() throws SQLException {
          if(rs!=null){
              rs.close();
          }
          if(stat!=null){
              stat.close();
          }
          if(conn!=null){
              conn.close();
          }
      }
  
  
  }