Java连MySQL
一、添加config配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.95.138:3306/kb06mysqltestdb?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=javakb10
二、初始化资源类
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
class BaseConfig {
private class Config{
String driver;
String url;
String username;
String password;
public Config(String driver, String url, String username, String password) {
this.driver = driver;
this.url = url;
this.username = username;
this.password = password;
}
}
private Config config;
{
try{
init();
Class.forName(config.driver);
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
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("缺少url配置项异常");
}
String driver = pro.getProperty("driver","com.mysql.jdbc.Driver");
String username = pro.getProperty("username","root");
String password = pro.getProperty("password","root");
this.config = new Config(driver,url,username,password);
}
protected Connection getCon() throws SQLException {
return DriverManager.getConnection(config.url,config.username,config.password);
}
protected void close(AutoCloseable...acs){
for (AutoCloseable ac : acs) {
if(null != ac){
try {
ac.close();
} catch (Exception e) {
e.getMessage();
}
}
}
}
}
三、执行sql语句类
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 final class BaseDao extends BaseConfig {
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) {
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>> data = new ArrayList<>();
if(null != rst && rst.next()){
final int CC = rst.getMetaData().getColumnCount();
do{
List<String> row = new ArrayList<>(CC);
for (int i = 1; i <CC ; i++) {
row.add(rst.getObject(i).toString());
}
data.add(row);
}while(rst.next());
}
return Result.succeed(data);
} catch (SQLException e) {
e.printStackTrace();
return Result.fail();
}finally {
close(rst,pst,con);
}
}
}
四、结果集类
public final class Result<T> {
private boolean err;
private T data;
public static <T> Result succeed(T data){
return new Result(false,data);
}
public static Result fail(){
return new Result(true,null);
}
private Result(boolean err,T data){
this.err = err;
this.data =data;
}
public boolean isErr() {
return err;
}
public T getData() {
return data;
}
import java.util.List;
public class Test {
public static void main(String[] args) {
BaseDao dao = new BaseDao();
Result<List<List<String>>> result= dao.exeQuery("select * from student");
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();
}
}else{
System.out.println("查询异常");
}
}
}
Java和MySQL运行结果一致
Java连Hive
一、添加config配置文件
与上述java连接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 {
private class Config{
private String driver;
private String url;
private String username;
private String password;
}
private Config config;
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();
}
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 || !valid(url)){
throw new Exception("no 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","javakb10");
}
{
try {
init();
Class.forName(config.driver);
} catch (Exception e) {
e.printStackTrace();
}
}
Connection getCon() throws SQLException {
return DriverManager.getConnection(
config.url,
config.username,
config.password);
}
void close(AutoCloseable...closeables){
for (AutoCloseable closeable : closeables) {
if(null != closeable){
try {
closeable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
三、执行sql语句类
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 {
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);
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);
}
}
public String readSql(String...paths) throws Exception {
String path = paths.length==0 ? "sql/sql2.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();
}
}
四、结果集类
public abstract class Result<T> {
private boolean err;
private T data;
public static Result fail(){
return new Result(true) {};
}
public static <T> Result succeed(T...t){
return new Result(false,t) {};
}
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;
}
}
五、测试类
import java2hive.text.BaseDao;
import java2hive.text.Result;
import java.util.List;
public class App
{
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(cell->{
System.out.print(cell+"\t");
});
System.out.println();
});
}
}
六、在执行sql语句类的对应文件中写入sql语句
Java连Hbase
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.*;
import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.util.Bytes;
import org.junit.Test;
import java.io.IOException;
public class HBASEClientDemo {
//创建一个表
@Test
public void createTable() throws IOException {
//1.获取hbase连接,配置
Configuration conf = HBaseConfiguration.create();
conf.set("hbase.zookeeper.quorum","192.168.95.138");
conf.set("hbase.zookeeper.property.clientPort","2181");
//2.创建连接
Connection conn = ConnectionFactory.createConnection(conf);
//3.创建admin
Admin admin=conn.getAdmin();
//4.创建表的相关信息,表名
HTableDescriptor student = new HTableDescriptor(TableName.valueOf("student"));
//5.添加列族信息
student.addFamily(new HColumnDescriptor("info"));
student.addFamily(new HColumnDescriptor("score"));
//6.调用创建表的方法进行建表操作
admin.createTable(student);
//7.关闭连接
conn.close();
}
//向表中添加数据
@Test
public void putData2Table() throws IOException {
//1、配置
Configuration conf = HBaseConfiguration.create();
conf.set("hbase.zookeeper.quorum","192.168.95.138");
conf.set("hbase.zookeeper.property.clientPort","2181");
//2.创建连接
Connection conn = ConnectionFactory.createConnection(conf);
//3、获取table
Table student = conn.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"));
put.addColumn(Bytes.toBytes("info"),Bytes.toBytes("age"),Bytes.toBytes(18));
//6、插入数据
student.put(put);
//7、关闭连接
conn.close();
}
//读取数据
@Test
public void getDataFromTable() throws IOException {
//1、配置
Configuration conf = HBaseConfiguration.create();
conf.set("hbase.zookeeper.quorum","192.168.95.138");
conf.set("hbase.zookeeper.property.clientPort","2181");
//2、连接
Connection conn = ConnectionFactory.createConnection(conf);
//3、获取table
Table student = conn.getTable(TableName.valueOf("student"));
//4、读取数据,Get
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("value:"+Bytes.toString(CellUtil.cloneValue(cell)));
System.out.println("--------------------------");
}
//7、关闭连接
conn.close();
}
//删除
public void dropTable() throws IOException {
//1.获取hbase连接,配置
Configuration conf = HBaseConfiguration.create();
conf.set("hbase.zookeeper.quorum","192.168.95.158");
conf.set("hbase.zookeeper.property.clientPort","2181");
//2.创建连接
Connection conn = ConnectionFactory.createConnection(conf);
//3.创建admin
Admin admin=conn.getAdmin();
//4、禁用表
admin.disableTable(TableName.valueOf("student"));
//5、删除表
admin.deleteTable(TableName.valueOf("student"));
//7、关闭连接
conn.close();
}
}
createTable之前Hbase中没有student表:
createTable之后Hbase中出现student表: