1、pom.xml
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-client</artifactId>
<version>1.2.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.phoenix/phoenix-core -->
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-core</artifactId>
<version>4.12.0-HBase-1.2</version>
</dependency>
2、jdbc连接类
package com.cn.until;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class PhoenixUtil {
public static Connection getConnection(){
try {
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection connection = null;
//此处为zookeeper地址,可以为一个或多个:zookeeper的端口号:zookeeper.znode.parent(此处默认为 /hbase默认可不填写)
connection= DriverManager.getConnection("jdbc:phoenix:master,slaves1,slaves2:2181"); //connection=DriverManager.getConnection("jdbc:phoenix:master,slaves1,slaves2:2181:/hbase-unsecure");
return connection;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void closeConnection(Connection conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3、各种增删改查操作
package com.cn.service;
import com.cn.until.PhoenixUtil;
import org.junit.Test;
import java.sql.*;
import java.util.*;
/**
* 更新数据与插入数据方法一样
*/
public class Phoenix_Hbase_opt {
public static void main(String[] args){
//如果不指定列簇,默认使用列簇 0
// String sql = "CREATE TABLE teacher (id varchar PRIMARY KEY,cf.name varchar ,cf.classroom varchar)";
// createTable(sql);
// String sql_insert = "upsert into teacher(id, name, classroom) values('001', 'MrsZhang', '0302')";
// insertData(sql_insert);
// String sql_delData = "delete from teacher where id='001'";
// delData(sql_delData);
// String sql_delTable = "drop table teacher";
// dropTable(sql_delTable);
// List<String> tables = getTables();
// for (String table:tables) {
// System.out.println(table);
// }
List<Map<String, String>> stuList = getList("STU");
for (Map<String, String> map:stuList) {
for (Map.Entry<String, String> entry : map.entrySet()) {
System.out.println(entry.getKey() + ":" + entry.getValue());
}
}
}
/**
* 创建表
*/
public static void createTable(String sql) {
Connection conn = PhoenixUtil.getConnection();
try {
PreparedStatement sta = conn.prepareStatement(sql);
sta.execute();
System.out.println("创建成功...");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
PhoenixUtil.closeConnection(conn);
}
}
}
/**
* 插入数据
*/
public static void insertData(String sql) {
Connection conn = PhoenixUtil.getConnection();
try {
PreparedStatement ps = conn.prepareStatement(sql);
String msg = ps.executeUpdate() >0 ? "插入成功..."
:"插入失败...";
conn.commit();
System.out.println(msg);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
PhoenixUtil.closeConnection(conn);
}
}
}
/**
* 删除数据
*/
public static void delData(String sql) {
Connection conn = PhoenixUtil.getConnection();
try {
PreparedStatement ps = conn.prepareStatement(sql);
String msg = ps.executeUpdate() > 0 ? "删除成功..."
: "删除失败...";
conn.commit();
System.out.println(msg);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
PhoenixUtil.closeConnection(conn);
}
}
}
/**
* 删除表
*/
public static void dropTable(String sql) {
Connection conn = PhoenixUtil.getConnection();
//String sql = "drop table user";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.execute();
System.out.println("删除表成功...");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
PhoenixUtil.closeConnection(conn);
}
}
}
/**
* 获取Phoenix中的表(系统表除外)
*/
public static List<String> getTables() {
Connection conn = PhoenixUtil.getConnection();
List<String> tables = new ArrayList<>();
try{
DatabaseMetaData metaData = conn.getMetaData();
String[] types = {"TABLE"}; //"SYSTEM TABLE"
ResultSet resultSet = metaData.getTables(null, null, null, types);
while (resultSet.next()) {
tables.add(resultSet.getString("TABLE_NAME"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (conn != null) {
PhoenixUtil.closeConnection(conn);
}
}
return tables;
}
/**
* 获取表中的所有数据
*/
public static List<Map<String, String>> getList(String tableName) {
String sql = "SELECT * FROM " + tableName;
Connection conn = PhoenixUtil.getConnection();
List<Map<String, String>> resultList = new ArrayList<>();
try{
PreparedStatement preparedStatement = conn.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
while (resultSet.next()) {
Map<String, String> result = new HashMap<>();
for (int i = 1, len = resultSetMetaData.getColumnCount(); i <= len; i++) {
result.put(resultSetMetaData.getColumnName(i), resultSet.getString(i));
}
resultList.add(result);
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (conn != null) {
PhoenixUtil.closeConnection(conn);
}
}
return resultList;
}
}