JDBC-＞HIVE/MySQL/Hbase

最新推荐文章于 2024-10-10 21:13:48 发布

A卤蛋

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量525

点赞数

分类专栏：代码配置文件文章标签： hive jdbc mysql log4j

本文链接：https://blog.csdn.net/Forest_sld/article/details/112449680

版权

配置文件同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

代码

3 篇文章 0 订阅

订阅专栏

MySql

前期准备

创建Maven

注意箭头标识
在这里插入图片描述

修改pom.xml文件

修改如下图：
在这里插入图片描述

修改Project Structure

路径： File->Project Structure->Modules->Language level
在这里插入图片描述

修改Settings

**路径：**Settings->Compiler-> Java Compiler
在这里插入图片描述

设置资源文件夹

Resource在main下创建的，不要创错了
在这里插入图片描述

添加

依赖关系

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
</dependency>

日志文件 datasource.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.44.128:3306/kb10mysqltestdb?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=kb10

log4j.properties

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

类

BaseConfig (初始化资源类)

package cn.dsl.mysql;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class BaseConfig {
    //定义内部类Config，使用其对象作为BaseConfig的成员变量，加载驱动等信息
    private class Config {
        String driver;
        String url;
        String username;
        String password;
    }

    private Config config;

    //正则匹配，检查url格式是否有效
    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();
    }
    
    {
        //静态代码块  异常无法抛出
        try {
            init();
            //驱动装载
            Class.forName(config.driver);
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }

    //init方法将配置信息加载到BaseConfig的成员变量config上
    private void init() throws Exception {
        //用线程读取读取路径
        String path = Thread.currentThread().getContextClassLoader()
                .getResource("datasource.properties").getPath();
        //使用Properties包装类解析properties文件
        Properties pro = new Properties();
        pro.load(new FileReader(path));
        String url = pro.getProperty("url");
        if (url == null || !valid(url)) {
            throw new Exception("url is null or Invalid!");
        }
        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", "root");
        pro.clear();

    }

    //获得数据库连接Connection
    protected Connection getCon() throws SQLException {
        return DriverManager.getConnection(config.url, config.username, config.password);
    }

    //关闭线程方法
    protected void close(AutoCloseable... acs) {
        if (acs != null) {
            for (AutoCloseable ac : acs) {
                try {
                    ac.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

BaseDao (执行sql语句)

package cn.dsl.mysql;


import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDao extends BaseConfig {

    //预编译执行器PreparedStatement（防止sql注入）
    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) {
            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;
        List<List<String>> data=new ArrayList<>();
        try {
            con=getCon();
            pst=getPst(con,sql,params);
            rst=pst.executeQuery();
            final int COUNT=rst.getMetaData().getColumnCount();
            while (rst.next()){
                List<String> row=new ArrayList<>(COUNT);
                for (int i = 1; i <= COUNT; i++) {
                    row.add(rst.getObject(i).toString());
                }
                data.add(row);
            }
            return Result.Succeed(data);
        } catch (SQLException e) {
            e.printStackTrace();
            return Result.Fail();
        }finally {
            close(rst,pst,con);
        }
    }

    //readSql方法，用于读取本地文件中的sql语句
    public String readSql(String...paths) throws Exception {
        String path=paths.length==0?"sql/sql.sql":paths[0];
        StringBuilder builder=new StringBuilder();
        BufferedReader read=new BufferedReader(new FileReader(path));
        String line=null;
        while (null!=(line=read.readLine())){
            builder.append(line.trim()+" ");
        }
        return builder.toString();
    }

}

Result (结果类集)

package cn.dsl.mysql;

public class Result<T> {
    private T data;
    private boolean isErr;

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

    public T getData() {
        return data;
    }

    public boolean isErr() {
        return isErr;
    }

    public static <T>Result Succeed(T data){
        return new Result(false,data);
    }

    public static Result Fail(){
        return new Result(true,null);
    }
}

HIVE

前期准备

日志文件

先在resource里添加dataresource.properties日志文件
在这里插入图片描述

driver=org.apache.hive.jdbc.HiveDriver
url=jdbc:hive2://192.168.44.128:10000/default
username=root

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

类

BaseConfig (初始化资源类)

package cn.dsl.hive.jdbc;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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

    }
    private Config config;

    private boolean valid(String url){
        //正则表达式          //|前ip地址,
         // 意思为前面为1~3的数字加个点第四位为一到三位的数字//后面的地址＋端口号
        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();
    }

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

    private void init() throws Exception {
        String path = Thread.currentThread().getContextClassLoader()
                .getResource("dataresource.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","");


    }

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

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

BaseDao (执行sql语句)

package cn.dsl.hive.jdbc;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDao extends BaseConfig{
    private PreparedStatement getPst(Connection con,String sql,Object...params) throws SQLException {
        //通过连接对象获得执行对象
        PreparedStatement pst = con.prepareStatement(sql);
        //判断有没有参数
        //params.length不可能为0
        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 =null;
            if(null != rst && rst.next()){
                table = new ArrayList<>();
                final int COL = rst.getMetaData().getColumnCount(); //列数
                do{
                    List<String> row = new ArrayList<>(COL);
                    for (int i = 1; i <=COL; 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);  //注意顺序//先开的后关  后开的先关
        }
    }

//清楚看清sql语句
    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 (结果类集)

package cn.dsl.hive.jdbc;

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

    // 方法级泛型
    public static <T> Result succeed(T...t){
        return new Result(false,t){};
    }

    public static Result fail(){
        return new Result(true ,null){};
    }


    private 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类

package cn.dsl.hive.jdbc;

import java.util.List;

public class Test {
    public static void main(String[] args) throws Exception {
        BaseDao dao = new BaseDao();
        Result<List<List<String>>> tables = dao.exeQuery(dao.readSql());
        tables.getData().forEach(row->{
            row.forEach(item->{
                System.out.print(item+"\t");
            });
            System.out.println();
        });

    }
}