用java去连接mysql、hive、hbase

首先先创建一个maven工程，在main里面创建一个resource目录，并将其设置为Resources模式。把下面的log4j.properties文档放入到resource目录下

	log4j.rootLogger=INFO, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=target/hadoop.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d %p [%c] - %m%n

一、java连mysql
1、在resource目录下创建 datasource.properties 文件，用来编写
driver、url、username
/

/加载驱动包
driver=com.mysql.jdbc.Driver

//" IP_ADDRESS : PORT " 处，输入对应的虚机的IP地址及端口号
//" DATABASE " 处，输入对应的数据库
url=jdbc:mysql://" IP_ADDRESS : PORT "/" DATABASE "?useUnicode=true&characterEncoding=utf8&useSSL=true

//" USER_NAME " 处，输入对应的虚机的用户名
username=" USER_NAME "

//" USER_PASSWORD " 处，输入对应的虚机的密码
password=" USER_PASSWORD "
```/
2、创建BaseConfig、BaseDao、Result、Test类，代码如下
BaseConfig类

```java
public class BaseConfig{

    private 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.out.println(e.getMessage());
        }
    }

    public void init() throws Exception {
        String path = Thread.currentThread().getContextClassLoader().getResource("datasource.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");
        
        //" USER_NAME " 处，输入对应的虚机的用户名
		//" USER_PASSWORD " 处，输入对应的虚机的密码
        String username = pro.getProperty("username", " USER_NAME ");
        String password = pro.getProperty("password", " USER_PASSWORD ");

        
        this.config = new Config(driver,url,username,password);
    }

    protected Connection getCon() throws SQLException {
        return DriverManager.getConnection(config.url,config.username,config.password);
    }

    protected void close(AutoCloseable...acs){
        for (AutoCloseable ac : acs) {
            if (ac!=null) {
                try {
                    ac.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

BaseDao类

public class BaseDao extends BaseConfig{
    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) {
            return Result.fail();
        }finally {
            close(pst,con);
        }
    }

    public Result exeQuery(String sql,Object...params){
        Connection con=null;
        PreparedStatement pst=null;
        ResultSet rst = null;
        try {
            con=getCon();
            pst=getPst(con,sql,params);
            rst=pst.executeQuery();
            List<List<String>> data = new ArrayList<>();
            if (rst!=null&&rst.next()) {
                final int CC = rst.getMetaData().getColumnCount();
                do{
                    List<String> row = new ArrayList<>(CC);
                    for (int i = 1; i <= CC; i++) {
                        row.add(rst.getObject(i).toString());
                    }
                    data.add(row);
                }while(rst.next());
            }
            return Result.succeed(data);
        } catch (SQLException e) {
            return  Result.fail();
        }finally {
            close(rst,pst,con);
        }
    }
}

Result类

public class Result<T>{

    private boolean err;
    private T data;

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

    public boolean isErr() {
        return err;
    }

    public T getData() {
        return data;
    }

    public static <T>Result succeed(T data){
        return  new Result(false,data);
    }
    public static Result fail(){
        return new Result(true,null);
    }
}

Test类

public class Test {
    public static void main(String[] args) {
        BaseDao dao = new BaseDao();
        Result<List<List<String>>> result = dao.exeQuery("select * from student");

        if (!result.isErr()) {
            List<List<String>> data = result.getData();
            for (List<String> row : data) {
                for (String col : row) {
                    System.out.print(col+"\t");
                }
                System.out.println();
            }
        }else{
            System.out.println("查询异常");
        }
    }
}

二、java连接hive
1、第一步还是一样，在resource目录下创建 datasource.properties 文件，用来编写driver、url、username

//加载驱动包
driver=org.apache.hive.jdbc.HiveDriver
		
//" IP_ADDRESS : PORT " 处，输入对应的虚机的IP地址及端口号
//" DATABASE " 处，输入对应的数据库
url=jdbc:hive2://" IP_ADDRESS : PORT "/" DATABASE "	

//" USER_NAME " 处，输入对应的虚机的用户名
username= " USER_NAME "	

2、创建BaseConfig、BaseDao、Result、Test

BaseConfig类

public class BaseConfig {
    private class Config{
        private  String driver;
        private String url;
        private String username;
        private String password;
    }

    private Config config;

    private boolean valid(String url) {
        Pattern p = Pattern.compile("jdbc:\\w+://((\\d{1,3}\\.){3}\\d{1,3}|\\w+):\\d{1,5}/\\w+");
        Matcher m =p.matcher(url);
        return m.matches();
    }

    private void init() throws Exception {
        String path = Thread.currentThread().getContextClassLoader().
                getResource("datasource.properties").getPath();
        Properties pro = new Properties();
        pro.load(new FileReader(path));
        String url = pro.getProperty("url");
        if (null==url || !valid(url)) {
            throw new Exception("no or invalid url exception");
        }
        config = new Config();
        config.url=url;
        config.driver = pro.getProperty("driver", "com.mysql.jdbc.Driver");
        config.username = pro.getProperty("username", "root");
        config.password = pro.getProperty("password", "");
    }

    {
        try {
            init();
            Class.forName(config.driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    Connection getCon() throws SQLException {
        return DriverManager.getConnection(config.url,config.username,config.password);
    }

      void close(AutoCloseable...acs){
        for (AutoCloseable ac : acs) {
            if (ac!=null) {
                try {
                    ac.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

BaseDao

public class BaseDao extends BaseConfig {
    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);
            pst.execute();
            return Result.succeed();
        } 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;
        try {
            con=getCon();
            pst=getPst(con,sql,params);
            rst=pst.executeQuery();
            List<List<String>> table = new ArrayList<>();
            if (null!=rst&&rst.next()) {
              final int CC = rst.getMetaData().getColumnCount();
                do{
                    List<String> row = new ArrayList<>(CC);
                    for (int i = 1; i <=CC ; i++) {
                        row.add(rst.getObject(i).toString());
                    }
                    table.add(row);
                }while(rst.next());
            }
            return Result.succeed(table);
        } catch (SQLException e) {
            e.printStackTrace();
            return Result.fail();
        }finally {
            close(rst,pst,con);
        }
    }

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

Result类

public abstract class Result<T> {
    private boolean err;
    private T data;

    public static Result fail(){
        return new Result(true) {
        };
    }
    public static <T> Result succeed(T...t){
        return new Result(false,t) {
        };
    }


    public Result(boolean err, T...data) {
        this.err = err;
        this.data = data.length>0 ? data[0]:null;
    }

    public boolean isErr() {
        return err;
    }

    public T getData() {
        return data;
    }
}

Test类

public class Test{
    public static void main( String[] args ) throws Exception {
        BaseDao dao = new BaseDao();
        StringBuilder sql = new StringBuilder();
        sql.append("");
        sql.append("");

        Result<List<List<String>>> tables = dao.exeQuery(dao.readSql());
        tables.getData().forEach(row->{
            row.forEach(cell->{
                System.out.print(cell+"\t");
            });
            System.out.println();
        });
    }
}

三、java连接hbase
代码如下：

public class HBASEClientDemo {
    @Test
    public void createTable() throws IOException {
        //1.获取hbase连接，配置
        //hbase.zookeeper.property.clientPort
        Configuration conf = HBaseConfiguration.create();
        
        //ATTENTION PLEASE!!!        
        //" IP_ADDRESS " 处，输入对应的虚机的IP地址，或者输入对应的虚机的主机名
        conf.set("hbase.zookeeper.quorum"," IP_ADDRESS ");
        //" 端口号 " 处，输入对应的端口号
        conf.set("hbase.zookeeper.property.clientPort"," 端口号 ");
        
        
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.创建admin
        Admin admin = conn.getAdmin();
        //4.创建表的相关信息，表名
        HTableDescriptor student = new HTableDescriptor(TableName.valueOf("student"));
        //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.配置
        Configuration conf = HBaseConfiguration.create();
        
        //ATTENTION PLEASE!!!       
        //" IP_ADDRESS " 处，输入对应的虚机的IP地址，或者输入对应的虚机的主机名
        conf.set("hbase.zookeeper.quorum"," IP_ADDRESS ");
        //" 端口号 " 处，输入对应的端口号
        conf.set("hbase.zookeeper.property.clientPort"," 端口号 ");
        
        
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.获取table
        Table student = conn.getTable(TableName.valueOf("student"));
        //4.往表中添加数据
        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(10));
        //6.插入数据
        student.put(put);

        //7.关闭连接
        conn.close();
    }
    //读取数据
    @Test
    public void getDataFromTable() throws IOException {
        //1.配置
        Configuration conf = HBaseConfiguration.create();
      
        //ATTENTION PLEASE!!!        
        //" IP_ADDRESS " 处，输入对应的虚机的IP地址，或者输入对应的虚机的主机名
        conf.set("hbase.zookeeper.quorum"," IP_ADDRESS ");
        //" 端口号 " 处，输入对应的端口号
        conf.set("hbase.zookeeper.property.clientPort"," 端口号 ");
        
        
        //2.连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.获取table
        Table student = conn.getTable(TableName.valueOf("student"));
        //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();
    }
    //删除数据
    public void dropTable() throws IOException {
        //1.配置
        Configuration conf = HBaseConfiguration.create();
        
        //ATTENTION PLEASE!!!
        //" IP_ADDRESS " 处，输入对应的虚机的IP地址，或者输入对应的虚机的主机名
        conf.set("hbase.zookeeper.quorum"," IP_ADDRESS ");
        //" 端口号 " 处，输入对应的端口号
        conf.set("hbase.zookeeper.property.clientPort"," 端口号 ");
        
        
        //2.连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.get admin
        Admin admin = conn.getAdmin();
        //4.禁用表
        admin.disableTable(TableName.valueOf("student"));
        //5.删除表
        admin.deleteTable(TableName.valueOf("student"));

        conn.close();
    }
}