三、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版本中新驅(qū)動(dòng)，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 {
            //加載驅(qū)動(dòng)類
            Class.forName(DRIVER);
            //獲取連接對(duì)象
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            //獲取連接的代理對(duì)象，用于傳遞sql請(qǐng)求
            statement = connection.createStatement();
            boolean result = statement.execute("show databases;");
            resultSet = statement.getResultSet();

            //resultSet.next()看返回的結(jié)果是否還有數(shù)據(jù)，如果有為true
            while (resultSet.next()) {
                //resultSet提供了很多獲取的方法，getInt,getString等，具體用哪個(gè)，看字段的數(shù)據(jù)類型
                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依賴：

<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.2.1</version>
</dependency>

代碼：

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";
    //數(shù)據(jù)庫連接字符串
    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("編號(hào)" + "\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類似。