Java连接Mysql,Hive,Hbase

于 2021-01-09 14:35:37 发布
阅读量104 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： jdbc mysql hive hbase
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/dontlikerabbit/article/details/112391353
博客主要介绍了Java分别连接MySQL、Hive和Hbase的情况。Java与MySQL、Hive运行结果一致；在连接Hbase时，展示了创建表、插入数据、查询表以及删除表等操作的结果。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

Java连MySQL

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");
        String username = pro.getProperty("username", "root");
        String password = pro.getProperty("password", "root");
        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();
                }
            }
        }
    }
}


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);
        }
    }
}


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);
    }
}


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和MySQL运行结果一致
java中的运行结果截图
MySQL中运行结果截图

Java连Hive

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();
                }
            }
        }
    }

}

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();
    }


}


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;
    }
}

sql.sql文件写的HQL语句
在这里插入图片描述

public class App 
{
    public static void main( String[] args ) throws IOException {
        BaseDao dao = new BaseDao();
        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和Hive运行结果一致
在这里插入图片描述
在这里插入图片描述

Java连Hbase

public class HBASEClientDemo {
    @Test
    public void createTable() throws IOException, SQLException {

        //1.获取hbase连接，配置
        //hbase.zookeeper.property.clientPort
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum", "192.168.181.129");
        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("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();
        conf.set("hbase.zookeeper.quorum", "192.168.181.129");
        conf.set("hbase.zookeeper.property.clientPort", "2181");
        //2.创建连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.获取table
        Table student = conn.getTable(TableName.valueOf("student"));
        //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 getDateFromTable() throws IOException {
        //1.配置
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum","192.168.181.129");
        conf.set("hbase.zookeeper.property.clientPort","2181");
        //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("-----------------------------------");
        }
        //7.关闭连接
        conn.close();
    }

    //删除表
    @Test
    public void dropTable() throws IOException {
        //1.配置
        Configuration conf = HBaseConfiguration.create();
        conf.set("hbase.zookeeper.quorum","192.168.181.129");
        conf.set("hbase.zookeeper,property.clientPort","2181");
        //2.连接
        Connection conn = ConnectionFactory.createConnection(conf);
        //3.创建admin
       Admin admin = conn.getAdmin();
        //4.禁用表
        admin.disableTable(TableName.valueOf("student"));
        //5.删除表
        admin.deleteTable(TableName.valueOf("student"));
        //6.关闭连接
        conn.close();
    }

}

createTable之前Hbase中没有student表：
在这里插入图片描述
createTable之后Hbase中出现student表：
在这里插入图片描述
没有运行putData2Table，添加数据前，student是空表
在这里插入图片描述
运行putData2Table插入数据后，hbase中查询student表，结果如图
在这里插入图片描述
运行putData2Table插入数据后，java中查询student表，结果如图
在这里插入图片描述
运行dropTable后，显示student表已被删除
在这里插入图片描述

dontlikerabbit
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值