三、hive--jdbc的使用

一、jdbc连接mysql代码示例

public class TestConnector {    final static String USER = "root";    final static String PASSWORD = "wjt86912572";    final static String URL = "jdbc:mysql://bigdata121:3306/metastore?useSSL=false&serverTimezone=UTC&useUnicode=true";    //这是 mysql.connector 在8.x版本中新驱动，com.mysql.jdbc.Driver在此版本中已弃用    final static String DRIVER = "com.mysql.cj.jdbc.Driver";    public static void main(String[] args) {        Connection connection = null;        Statement statement = null;        ResultSet resultSet = null;        try {            //加载驱动类            Class.forName(DRIVER);            //获取连接对象            connection = DriverManager.getConnection(URL, USER, PASSWORD);            //获取连接的代理对象，用于传递sql请求            statement = connection.createStatement();            boolean result = statement.execute("show databases;");            resultSet = statement.getResultSet();            //resultSet.next()看返回的结果是否还有数据，如果有为true            while (resultSet.next()) {                //resultSet提供了很多获取的方法，getInt,getString等，具体用哪个，看字段的数据类型                System.out.println(resultSet.getString("Database"));            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                if (resultSet != null) {resultSet.close();}                if (statement != null) {statement.close();}                if (connection != null) {connection.close();}            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

二、jdbc连接hive代码示例

首先添加maven依赖：

    org.apache.hive    hive-jdbc    1.2.1

代码：

package com.zy.hivejdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import org.junit.After;import org.junit.Before;import org.junit.Test;public class HiveJDBC {    //这是hive的driver名字    private static String driverName="org.apache.hive.jdbc.HiveDriver";    //数据库连接字符串    private static String url = "jdbc:hive2://192.168.134.153:10000/mydb";    private static String user = "root";    private static String password="root";    private static Connection conn = null;    private static Statement stmt = null;    private static ResultSet rs = null;    @Before    public void init() throws Exception{        Class.forName(driverName);        conn = DriverManager.getConnection(url, user, password);        stmt = conn.createStatement();    }    @Test    public void createDatabase() throws Exception{        String sql = "create database hive_jdbc_test";        System.out.println("Running: " + sql);        stmt.executeQuery(sql);    }    @Test    public void dropDatabase() throws Exception {        String sql = "drop database if exists hive_jdbc_test";        System.out.println("Running: " + sql);        stmt.execute(sql);    }    @Test    public void showDatabases() throws Exception {        String sql = "show databases";        System.out.println("Running: " + sql + "\n");        rs = stmt.executeQuery(sql);        while (rs.next()) {            System.out.println(rs.getString(1) );        }    }    @Test    public void createTable() throws Exception {        String sql = "create table t2(id int ,name String) row format delimited fields terminated by ',';";        System.out.println("Running: " + sql);        stmt.execute(sql);    }    @Test    public void loadData() throws Exception {         String filePath = "/usr/tmp/student";         String sql = "load data local inpath '" + filePath + "' overwrite into table t2";         System.out.println("Running: " + sql);         stmt.execute(sql);    }    @Test    public void selectData() throws Exception {        String sql = "select * from t2";        System.out.println("Running: " + sql);        rs = stmt.executeQuery(sql);        System.out.println("编号" + "\t" + "姓名" );        while (rs.next()) {            System.out.println(rs.getInt(1) + "\t" + rs.getString(2));        }    }    @Test    public static void drop(Statement stmt) throws Exception {        String dropSQL = "drop table t2";        boolean bool = stmt.execute(dropSQL);        System.out.println("删除表是否成功:" + bool);        }    @After    public void destory() throws Exception {        if (rs != null) {            rs.close();        }        if (stmt != null) {            stmt.close();        }        if (conn != null) {            conn.close();        }    }}

基本使用和jdbc连接mysql类似。