import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Properties;
import javax.swing.JTabbedPane;
/**
* jdbc 的链接数据库 通用类
*
* @author zhangheng
*
*/
public class JDBCTemplate {
//mysql driver = "com.mysql.jdbc.Driver";
//sqlserver driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
private static String driver = "org.apache.hadoop.hive.jdbc.HiveDriver";
private String url = "jdbc:hive://ip:port/default?useUnicode=true&characterEncoding=utf-8&useSSL=false";
/*private String username = "user";
private String password = "password";*/
// 声明链接数据对象变量
Connection conn;
Statement st;
PreparedStatement ps;
ResultSet rs;
// 注册驱动
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获得链接
private void getConnection() {
try {
conn = DriverManager.getConnection(url, null, null);
//conn.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("获得链接失败!");
}
}
/**
* 提交
* @throws Exception
*/
public void myCommit() throws Exception{
try{
if(conn != null){
conn.commit();
}
}catch(Exception e){
throw e;
}
}
/**
* 回滚
*/
public void rollBack(){
try{
if(conn != null){
conn.rollback();
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 创建状态通道
*/
public void getstatment(){
this.getConnection();
try {
st = conn.createStatement();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("创建状态通道失败!");
}
}
/**
* 基于状态通道执行操作
* @param sql
* @return
*/
public ResultSet getData(String sql){
this.getstatment();
try {
rs = st.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("在状态通道下执行数据查询失败!");
}
return rs;
}
/**
* 创建预备状态通道
*/
private void preStatment(String sql) {
this.getConnection();
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("创建预备状态通道失败!");
}
}
/**
* 基于预状态进行数据操作
*
* @param sql
* update ?? insert into ?? delete ??
*/
public boolean updateData(String sql, String[] params) {
this.preStatment(sql);
boolean isok = false;
try {
this.bandle(params);
// 执行完整sql 语句
int result = ps.executeUpdate();
if (result > 0) {
isok = true;
}
closeRes();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("在预备状态通道下执行数据操作失败!");
}
return isok;
}
/**
* 基于预状态通道下的批处理操作
* @param sql insert int user_tb values(?,?,?,?)
* @param params String params = {{"23","23","23",23"},{"12","12","12","12"},{"44","44","44","44"}};
*/
public void executeBatch(String sql,String params[][]){
try {
this.preStatment(sql);
if(params != null){
for (int i = 0; i < params.length; i++) {
// 内存循环就是给 一条sql 的问号 赋值
//System.out.println(params[i].length);
for (int j = 0; j < params[i].length; j++) {
ps.setString(j+1, params[i][j]);
}
// 把补全的sql 语句拿到 批处理缓存中
ps.addBatch();
// System.out.println("正在插入");
}
//
ps.executeBatch();
//myCommit();
closeRes();
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("批处理丢失数据");
//rollBack();
}
System.out.println("插入完成!");
}
/**
* 绑定参数
* @param params
* @throws SQLException
*/
private void bandle(String [] params) throws SQLException{
// 绑定参数
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
ps.setString(i + 1, params[i]);
}
}
}
/**
* 查询 基于预备状态 通道
* @param //select ????
*/
public ResultSet query(String sql,String []params){
this.preStatment(sql);
// 绑定参数
try {
this.bandle(params);
// 执行
rs = ps.executeQuery();
closeRes();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("在预备状态通道下执行查询失败!");
}
return rs;
}
/**
* 关闭资源
*/
public void closeRes(){
try {
if(rs != null){
rs.close();
}
if(ps != null){
ps.close();
}
if(st != null){
st.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
} |
此为吾呕心沥血之从吾之代码中copy来,忘君不思量,自难忘!