Hbase(6)【Java Api Phoenix操作Hbase】
两种方式操作Phoenix
官网：http://phoenix.apache.org/faq.html#What_is_the_Phoenix_JDBC_URL_syntax
What is the Phoenix JDBC URL syntax?
1.Thick Driver
pom依赖
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-spark</artifactId>
<version>5.0.0-HBase-2.0</version>
<exclusions>
<exclusion>
<groupId>org.glassfish</groupId>
<artifactId>javax.el</artifactId>
</exclusion>
</exclusions>
</dependency>
java代码
public static void main(String[] args)throws Exception {
//初始化phoenix连接
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection connection = DriverManager.getConnection("jdbc:phoenix:hadoop102,hadoop103,hadoop104:2181");
Statement st = connection.createStatement();
int i = st.executeUpdate("upsert into bigdata.dim_test values ('1003','test3')");
connection.commit();
System.out.println(i);
//PreparedStatement ps = connection.prepareStatement("create table if not exists bigdata.dim_test(id varchar primary key ,info.name varchar)");
//ps.executeUpdate();
System.out.println("执行成功");
}
2.Thin Driver
pom依赖
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-client</artifactId>
<version>2.0.5</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-queryserver-client</artifactId>
<version>5.0.0-HBase-2.0</version>
</dependency>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-server</artifactId>
<version>2.0.5</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.6</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.google.protobuf</groupId>
<artifactId>protobuf-java</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>io.grpc</groupId>
<artifactId>grpc-netty</artifactId>
<version>1.10.0</version>
</dependency>
java代码
package com.bigdata.phoenix;
import org.apache.phoenix.queryserver.client.Driver;
import org.apache.phoenix.queryserver.client.ThinClientUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PhoenixDemo {
@Test
public void createTable() throws Exception{
//1、加载驱动
Class.forName("org.apache.phoenix.queryserver.client.Driver");
//2、获取连接
String url = ThinClientUtil.getConnectionUrl("hadoop102", 8765);
System.out.println(url);
Connection connection = DriverManager.getConnection(url);
//3、创建Statement对象
String sql = "create table xxx(" +
"id varchar primary key," +
"name varchar," +
"age varchar)COLUMN_ENCODED_BYTES=0";
PreparedStatement statement = connection.prepareStatement(sql);
//4、执行sql操作
statement.execute();
//5、关闭
statement.close();
connection.close();
}
/**
*
* @throws Exception
*/
@Test
public void insert() throws Exception{
//1、加载驱动
Class.forName("org.apache.phoenix.queryserver.client.Driver");
//2、获取连接
String url = ThinClientUtil.getConnectionUrl("hadoop102", 8765);
System.out.println(url);
Connection connection = DriverManager.getConnection(url);
//connection.setAutoCommit(true);
//3、获取statement对象
PreparedStatement statement = connection.prepareStatement("upsert into xxx values(?,?,?)");
//4、给参数赋值
statement.setString(1,"1001");
statement.setString(2,"zhangsan");
statement.setString(3,"20");
//5、执行插入,需要手动提交，不然无法插入
statement.execute();
connection.commit();
//6、关闭
statement.close();
connection.close();
}
@Test
public void query() throws Exception{
//1、加载驱动
Class.forName("org.apache.phoenix.queryserver.client.Driver");
//2、获取连接
String url = ThinClientUtil.getConnectionUrl("hadoop102", 8765);
System.out.println(url);
Connection connection = DriverManager.getConnection(url);
//connection.setAutoCommit(true);
//3、获取statement对象
PreparedStatement statement = connection.prepareStatement("select * from xxx");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()){
String id = resultSet.getString("id");
String name = resultSet.getString("name");
String age = resultSet.getString("age");
System.out.println("id="+id+",name="+name+",age="+age);
}
statement.close();
connection.close();
}
}
3.注意事项
3.1报错问题
2021-07-30 11:42:12,773 INFO [org.apache.phoenix.query.ConnectionQueryServicesImpl] - HConnection established. Stacktrace for informational purposes: hconnection-0x3214ee6 java.lang.Thread.getStackTrace(Thread.java:1559)
org.apache.phoenix.util.LogUtil.getCallerStackTrace(LogUtil.java:55)
org.apache.phoenix.query.ConnectionQueryServicesImpl.openConnection(ConnectionQueryServicesImpl.java:432)
org.apache.phoenix.query.ConnectionQueryServicesImpl.access$400(ConnectionQueryServicesImpl.java:272)
org.apache.phoenix.query.ConnectionQueryServicesImpl$12.call(ConnectionQueryServicesImpl.java:2556)
org.apache.phoenix.query.ConnectionQueryServicesImpl$12.call(ConnectionQueryServicesImpl.java:2532)
org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:76)
org.apache.phoenix.query.ConnectionQueryServicesImpl.init(ConnectionQueryServicesImpl.java:2532)
org.apache.phoenix.jdbc.PhoenixDriver.getConnectionQueryServices(PhoenixDriver.java:255)
org.apache.phoenix.jdbc.PhoenixEmbeddedDriver.createConnection(PhoenixEmbeddedDriver.java:150)
org.apache.phoenix.jdbc.PhoenixDriver.connect(PhoenixDriver.java:221)
java.sql.DriverManager.getConnection(DriverManager.java:664)
java.sql.DriverManager.getConnection(DriverManager.java:270)
com.gmall.MethodTest.main(MethodTest.java:21)
这不是报错，就是info中打印的内容，不影响操作
3.2 upsert操作必须commit手动提交
不然不报错，但是数据也无法插入hbase
