java连接hbase、hive、mysql主体代码一览

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

一、Java连接Mysql

连接Hive与连接Mysql区别主要在于pom依赖和resource文件，主体代码除了BaseConfig类中init()方法里password值为空外并无区别

1、编写BaseConfig类

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

class BaseConfig {
    private static class Config{
        String driver;
        String url;
        String username;
        String password;

        public Config(String driver, String url, String username, String password) {
            this.driver = driver;
            this.url = url;
            this.username = username;
            this.password = password;
        }
    }
    private  Config config;

    {
        try {
            init();
            Class.forName(config.driver);
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }
	//初始化各个配置的方法中，要用到Tread.currentTread().getContextClassLoader().gerResource(“file_name”).getPath()方法获取到文件路径，然后使用专门读取配置文件的类Properties(new一个对象)下面的load方法读取这个这个路径下的方法；接下来，使用properties类下的getProperty方法依次读取“url”“driver”“username”“password”；最后把得到的参数传给当前对象。
    private void init() throws Exception {
        String path = Thread.currentThread().getContextClassLoader().getResource("mysql.properties").getPath();

        Properties pro = new Properties();
        pro.load(new FileReader(path));
        String url = pro.getProperty("url");
        if(null==url){
            throw new Exception("缺少url配置项异常");
        }
        String driver = pro.getProperty("driver","com.mysql.jdbc.Driver");
        String username = pro.getProperty("username","root");
        String password = pro.getProperty("password","root");
        this.config = new Config(driver,url,username,password);
    }
	//创建一个注册驱动的getCon()连接方法，把初始化进来的参数传到DriverManager.getConnection方法里；
    protected Connection getCon() throws SQLException {
        return DriverManager.getConnection(config.url,config.username,config.password);
    }
	//创建一个关闭资源释放资源的close()方法
    protected void close(AutoCloseable...acs){
        for (AutoCloseable ac :acs){
            if (null != ac){
                try {
                    ac.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2、编写BaseDAO类

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDao extends BaseConfig {

    //预编译执行器PreparedStatement（防止sql注入）
    private PreparedStatement getPst(Connection con, String sql, Object...params) throws SQLException {
        PreparedStatement pst=con.prepareStatement(sql);
        if(params.length>0){
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
        }
        return  pst;
    }

    //非查询语句调用方法
    public Result exeNonQuery(String sql,Object...params){
        Connection con=null;
        PreparedStatement pst=null;
        try {
            con=getCon();
            pst=getPst(con,sql,params);
            return Result.Succeed(pst.executeUpdate());
        } catch (SQLException e) {
            e.printStackTrace();
            return Result.Fail();
        }finally {
            close(pst,con);
        }
    }

    //查询语句调用方法
    public Result exeQuery(String sql,Object...params){
        Connection con=null;
        PreparedStatement pst=null;
        ResultSet rst=null;
        List<List<String>> data=new ArrayList<>();
        try {
            con=getCon();
            pst=getPst(con,sql,params);
            rst=pst.executeQuery();
            final int COUNT=rst.getMetaData().getColumnCount();
            while (rst.next()){
                List<String> row=new ArrayList<>(COUNT);
                for (int i = 1; i <= COUNT; i++) {
                    row.add(rst.getObject(i).toString());
                }
                data.add(row);
            }
            return Result.Succeed(data);
        } catch (SQLException e) {
            e.printStackTrace();
            return Result.Fail();
        }finally {
            close(rst,pst,con);
        }
    }

    //读取sql语句
    public String readSql(String...paths) throws Exception {
        String path=paths.length==0?"sql/sql.sql":paths[0];
        StringBuilder builder=new StringBuilder();
        BufferedReader read=new BufferedReader(new FileReader(path));
        String line=null;
        while (null!=(line=read.readLine())){
            builder.append(line.trim()+" ");
        }
        return builder.toString();
    }

}

3、编写Result类

public class Result<T> {
    private T data;
    private boolean isErr;

    public Result( boolean isErr,T data) {
        this.data = data;
        this.isErr = isErr;
    }

    public T getData() {
        return data;
    }

    public boolean isErr() {
        return isErr;
    }

    public static <T>Result Succeed(T data){
        return new Result(false,data);
    }

    public static Result Fail(){
        return new Result(true,null);
    }
}

4、编写Test类

public class Test {
    public static void main(String[] args) throws Exception{

        BaseDao dao=new BaseDao();
        Result<List<List<String>>> result = dao.exeQuery(dao.readSql());

        List<List<String>>  table=result.getData();
        table.forEach(tab->{
            tab.forEach(row->{
                System.out.print(row+"\t");
            });
            System.out.println();
        });

        Result result1 = dao.exeNonQuery(dao.readSql());
        System.out.println(result1);
    }
}

二、Java连接Hbase

代码实现

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.*;
import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.util.Bytes;
import org.junit.Test;
import java.io.IOException;

public class HBaseClientDemo {
    //创建一个表
  @Test
    public void createTable() throws IOException {
        //1.获取hbase连接，配置
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum","192.168.182.131");
        conf.set("hbase.zookeeper.property.clientPort","2181");
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.创建admin
        Admin admin=conn.getAdmin();
        //4.创建表的相关信息,表名
        HTableDescriptor student = new HTableDescriptor(TableName.valueOf("student100"));
        //5.添加列族信息
        student.addFamily(new HColumnDescriptor("info"));
        student.addFamily(new HColumnDescriptor("score"));
        //6.调用创建表的方法进行建表操作
        admin.createTable(student);
        //7.关闭连接
        conn.close();
    }
    @Test
    public void putData2Table() throws IOException {
        //1.获取hbase连接，配置
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum","192.168.182.131");
        conf.set("hbase.zookeeper.property.clientPort","2181");
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.获取table
        Table student =conn.getTable(TableName.valueOf("student100"));
        //4.表中添加数据rowkey
        Put put=new Put(Bytes.toBytes("1001"));
        //5.添加列 info:name zhangsan
        put.addColumn(Bytes.toBytes("info"),Bytes.toBytes("name"),Bytes.toBytes("zhangsan"));
        put.addColumn(Bytes.toBytes("info"),Bytes.toBytes("gender"),Bytes.toBytes("male"));
        put.addColumn(Bytes.toBytes("info"),Bytes.toBytes("age"),Bytes.toBytes("11"));
        //6.插入数据
        student.put(put);
        //7.关闭连接
        conn.close();
    }

    //读取数据
    @Test
    public void getDataFromTable() throws IOException {
//1.获取hbase连接，配置
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum","192.168.182.131");
        conf.set("hbase.zookeeper.property.clientPort","2181");
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.获取table
        Table student =conn.getTable(TableName.valueOf("student100"));
        //4.读取数据，Get
        Get get = new Get(Bytes.toBytes("1001"));
        //5.获取结果
        Result result = student.get(get);
        
        //6.遍历
        Cell[] cells = result.rawCells();
        for (Cell cell : cells) {
            System.out.println("rowkey:"+Bytes.toString(CellUtil.cloneRow(cell)));
            System.out.println("列族:"+Bytes.toString(CellUtil.cloneFamily(cell)));
            System.out.println("列名:"+Bytes.toString(CellUtil.cloneQualifier(cell)));
            System.out.println("value:"+Bytes.toString(CellUtil.cloneValue(cell)));
            System.out.println("-----------------");
        }
        conn.close();
    }

    //删除
    @Test
    public void dropTable() throws IOException {
        //1.获取hbase连接，配置
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum","192.168.182.131");
        conf.set("hbase.zookeeper.property.clientPort","2181");
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.创建admin
        Admin admin=conn.getAdmin();
        //4.禁用表
        admin.disableTable(TableName.valueOf("student100"));


        //5.删除表
        admin.deleteTable(TableName.valueOf("student100"));
        conn.close();

    }
}