JDBC连接数据库

JDBC连接数据库

- 定义

JDBC(Java DataBase Connectivity) 称为Java数据库连接，它是一种用于数据库访问的应用程序API，由一组用Java语言编写的类和接口组成

- 操作步骤

• Class.format加载驱动
• 获取数据库连接
• 创建Statement或PreparedStatement对象，执行sql语句
• 返回并处理执行结果（若查询操作，返回ResultSet）
• 释放资源

- 具体实现1（java->hive）

先导入驱动 Jar 包

/*配置文件预览
driver=org.apache.hive.jdbc.HiveDriver
url=jdbc:hive2://192.168.37.200:10000/default
username=root
*/
public class Base {
    private  class Config{
        private String driver;
        private String url;
        private String username;
        private String password;
    }
    private Config config;
    
    {
        try {
            init();
            //加载mysql驱动
            Class.forName(config.driver);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    //获取配置文件
    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){
            throw new 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
    protected 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 Base{

    PreparedStatement getSta (Connection con,String sql,Object...pamas) throws SQLException {
        PreparedStatement sta = con.prepareStatement(sql);
        for (int i = 0; i <pamas.length ; i++) {
            sta.setObject(i+1,pamas[i]);
        }
        return sta;
    }

    public Result exeQuery(String sql,Object...pamas){
        Connection con=null;
        PreparedStatement sta=null;
        ResultSet rst = null;
        try {
            con = getCon();
            sta=getSta(con,sql,pamas);
            rst=sta.executeQuery();
            List<List<String>> table = new ArrayList<>();
            final int COL = rst.getMetaData().getColumnCount();
            while (rst.next()){
                List<String> row = new ArrayList<>(COL);
                for (int i = 1; i <=COL; i++) {
                    row.add(rst.getObject(i).toString());
                }
                table.add(row);
            }
            return Result3.Successd(table);
        } catch (SQLException e) {
            return Result3.fail();
        }finally {
            close(rst,sta,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 ((line=reader.readLine())!=null){
            builder.append(line.trim()+"");
        }
        return builder.toString();
    }
}

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

    public Result(boolean err, T...data) {
        this.err = err;
        this.data = data.length>0?data[0]:null;
    }

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

    public static<T> Result Successd(T...t){
        return new Result(false,t);
    }
    public boolean isErr() {
        return err;
    }

    public T getData() {
        return data;
    }
}

测试类：
public class App {
    public static void main( String[] args ) throws Exception {
        BaseDao dao = new BaseDao();
        Result<List<List<String>>> result = dao.exeQuery("select * from shop  ");
//      Result<List<List<String>>> result = dao.exeQuery(dao.readSql());
        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();
            }
        }
    }
}

运行结果：

- 具体实现2（java->hbase）

public class AppTest 
{
    @Test
    public  void createTable() throws IOException {
        //获取hbase连接，配置
        Configuration config= HBaseConfiguration.create();
        config.set("hbase.zookeeper.quorum","single");
        config.set("hbase.zookeeper.property.clientPort","2181");
        //创建连接
        Connection connect= ConnectionFactory.createConnection(config);
        //创建admin
        Admin admin = connect.getAdmin();
        //创建表的相关信息，表名
        HTableDescriptor student = new HTableDescriptor(TableName.valueOf("student"));
        //添加列族信息
        student.addFamily(new HColumnDescriptor("info"));
        student.addFamily(new HColumnDescriptor("score"));
        //建表操作
        admin.createTable(student);
        //禁用表
        admin.disableTable(TableName.valueOf("student"));
        //删除表
        admin.deleteTable(TableName.valueOf("student"));
        //关闭连接
        connect.close();
    }

    @Test
    public void putDataTable() throws IOException {
        //1、获取hbase连接，配置
        Configuration config= HBaseConfiguration.create();
        config.set("hbase.zookeeper.quorum","single");
        config.set("hbase.zookeeper.property.clientPort","2181");
        //2、创建连接
        Connection connect= ConnectionFactory.createConnection(config);
       //3、获取table
        Table student = connect.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"));
        //6、插入数据
        student.put(put);
        //关闭连接
        connect.close();
    }

    @Test
    public void getDataTable() throws IOException {
        //1、获取hbase连接，配置
        Configuration config = HBaseConfiguration.create();
        config.set("hbase.zookeeper.quorum", "single");
        config.set("hbase.zookeeper.property.clientPort", "2181");
        //2、创建连接
        Connection connect = ConnectionFactory.createConnection(config);
        //3、获取table
        Table student = connect.getTable(TableName.valueOf("student"));
        //4、读取数据
        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("值:"+Bytes.toString(CellUtil.cloneValue(cell)));
            System.out.println("---------------------");
        }
        //7、关闭连接
        connect.close();
    }
}