hive学习之-------hiveJDBC

hive学习之-------hiveJDBC

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

public class HiveJDBCDemo1 {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String url = "jdbc:hive2://master:10000/hive_jdbc_test";
    private static String user = "root";
    private static String password = "123456";

    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 SQLException {
        String sql = "create database hive_jdbc_test1";
        System.out.println("Running:"+sql);
        stmt.execute(sql);
    }

    //查询所有的数据库
    @Test
    public void showDatabases() throws SQLException {
        String sql = "show databases";
        System.out.println("Running:"+sql);
        rs = stmt.executeQuery(sql);
        while(rs.next()){
            System.out.println(rs.getString(1));
        }
    }
    //使用数据库
    @Test
    public void usedatabase() throws Exception{
        String sql = "use hive_jdbc_test";
        System.out.println("Running:" + sql);
        stmt.execute(sql);
    }

    //创建表
    @Test
    public void createTable() throws Exception{
        String sql = "create table studentsn" +
                "(n" +
                "    id bigint,n" +
                "    name string,n" +
                "    age int,n" +
                "    gender string,n" +
                "    clazz stringn" +
                ")n" +
                "ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ";
        System.out.println("Running:"+sql);
        stmt.execute(sql);
    }

    //插叙所有表
    @Test
    public void showTable() throws Exception{
        String sql = "show tables";
        System.out.println("Running:"+sql);
        rs = stmt.executeQuery(sql);
        while (rs.next()){
            System.out.println(rs.getString(1));
        }
    }

    //查询表结构
    @Test
    public void descTable() throws Exception{
        String sql = "desc students";
        System.out.println("Running:" + sql);
        rs = stmt.executeQuery(sql);
        while (rs.next()){
            System.out.println(rs.getString(1)+"t"+rs.getString(2));
        }
    }


    //加载数据
    @Test
    public void loadData() throws Exception{
        String filePath = "/usr/local/kayleigh/data/students.txt";
        String sql = "load data local inpath '" + filePath +"' overwrite into table students";
        System.out.println("Running:" + sql);
        stmt.execute(sql);
    }

    //查询数据
    @Test
    public void selectData() throws Exception{
        String sql = "select * from students";
        System.out.println("Running:" + sql);
        rs = stmt.executeQuery(sql);
        System.out.println("ID:"+ "name" + "age"+"gender"+"clazz");
        while (rs.next()){
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String gender = rs.getString("gender");
                String clazz = rs.getString("clazz");
                System.out.println(id+"--"+name+"--"+age+"--"+gender+"--"+clazz);
            }
        }
    }

    //统计查询
    @Test
    public void countData() throws Exception {
        String sql = "select count(1) from students";
        System.out.println("Running: " + sql);
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.println(rs.getInt(1) );
        }
    }

    //删除数据库
    @Test
    public void dropDatabase() throws Exception{
        String sql = "drop database if exists hive_jdbc_test1";
        System.out.println("Running:" + sql);
        stmt.execute(sql);
    }

    // 删除数据库表
    @Test
    public void deopTable() throws Exception {
        String sql = "drop table if exists students";
        System.out.println("Running: " + sql);
        stmt.execute(sql);
    }

    // 释放资源
    @After
    public void destory() throws Exception {
        if ( rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}