封裝了增刪改查功能
適用于MySQL翰舌、Oracle嚣潜、SQLServer、DB2椅贱、Sybase懂算、JTDS、PostgreSql夜涕、SQLite、Derby属愤、H2女器、HSQLDB、ODBC 等等數(shù)據(jù)庫住诸，有需要的還可以自己增加驾胆。
package com.tgb.hz.jdbc;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* 數(shù)據(jù)訪問基類<br>
* <strong>支持 MySQL、Oracle贱呐、SQLServer丧诺、DB2、Sybase奄薇、PostgreSql驳阎、SQLite、Derby馁蒂、H2呵晚、HSQLDB、ODBC沫屡、SQLServer_JTDS饵隙、Sybase_JTDS</strong>
* <br>切換數(shù)據(jù)庫時(shí)，需要指定相關(guān)參數(shù)
*
* @author hezhao
* @Time 2017年7月27日 下午4:22:23
* @Description 無
* @Version V 1.0
*/
public class BaseDao {
private final Logger logger = LoggerFactory.getLogger(BaseDao.class);
private final boolean isLog = false;//是否記錄SQL日志
private final boolean isConfig = false;//是否需要配置文件
private final boolean isDataSource = false;//是否從連接池取連接
private final String datasource = "";//如果是從連接池取連接沮脖，那么給出連接名稱
private final DBType type = DBType.MySQL;//數(shù)據(jù)庫類型
// 數(shù)據(jù)庫名
private final String db = "dbname";
private final String ip = "127.0.0.1";
private String name = "root";
private String pwd = "root";
private String driver;
private String url;
private Connection conn = null;
private PreparedStatement pstmt = null;
protected ResultSet rs = null;
{
//把JDBC配置信息放在配置文件中
if(isConfig){
final Properties properties = new Properties();
try {
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
} catch (final IOException e) {
logger.error("加載JDBC配置信息失敗...");
logger.error(e.toString(),e);
}
driver = properties.getProperty("jdbc.driver");
url = properties.getProperty("jdbc.url");
name = properties.getProperty("jdbc.username");
pwd = properties.getProperty("jdbc.password");
}else{
if(type == DBType.MySQL){
driver = "com.mysql.jdbc.Driver";
url = "jdbc:mysql://"+ip+":3306/"+db+"?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
}else if(type == DBType.SQLServer){
//1金矛、使用sqljdbc
driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
url = "jdbc:sqlserver://"+ip+":1433;DatabaseName="+db;
}else if(type == DBType.SQLServer_JTDS){
//2芯急、使用jtds
driver = "net.sourceforge.jtds.jdbc.Driver";
url = "jdbc:jtds:sqlserver://"+ip+":1433;DatabaseName="+db;
}else if(type == DBType.Oracle){
driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@"+ip+":1521:"+db;
}else if(type == DBType.DB2){
driver = "com.ibm.db2.jcc.DB2Driver";
url = "jdbc:db2://"+ip+":50000/"+db;
}else if(type == DBType.Sybase){
driver = "com.sybase.jdbc.SybDriver";
url = "jdbc:sybase:Tds:"+ip+":5007/"+db;
}else if(type == DBType.Sybase_JTDS){
driver = "net.sourceforge.jtds.jdbc.Driver";
url = "jdbc:jtds:sybase://"+ip+":5007/"+db;
}else if(type == DBType.PostgreSql){
driver = "org.postgresql.Driver";
url = "jdbc:postgresql://"+ip+"/"+db;
}else if(type == DBType.SQLite){
driver = "org.sqlite.JDBC";
url = "jdbc:sqlite:"+db; //jdbc:sqlite:person.db
}else if(type == DBType.H2){
driver = "org.h2.Driver";
url = "jdbc:h2:tcp://"+ip+"/~/"+db;
}else if(type == DBType.HSQLDB){
driver = "org.hsqldb.jdbcDriver";
url = "jdbc:hsqldb:hsql://"+ip+":9001/"+db;
}else if(type == DBType.Derby){
driver = "org.apache.derby.jdbc.ClientDriver";
url = "jdbc:derby://"+ip+":1527/"+db+";";
}else if(type == DBType.ODBC){
driver = "sun.jdbc.odbc.JdbcOdbcDriver";
url = "jdbc:odbc:"+db;
}
}
}
private void getConnection() {
if(!isDataSource){
//1、通過JDBC獲得鏈接
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, name, pwd);
} catch (Exception e) {
logger.error(e.toString(),e);
}
}else{
//2驶俊、通過數(shù)據(jù)連接池獲得鏈接
try {
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(datasource);
this.conn = ds.getConnection();
} catch (NamingException e) {
logger.error(e.toString(),e);
} catch (SQLException e) {
logger.error(e.toString(),e);
}
}
}
/**
* 關(guān)閉資源
*/
protected void closeAll() {
try {
if (this.rs != null) {
rs.close();
}
if (this.pstmt != null) {
pstmt.close();
}
if (this.conn != null) {
conn.close();
}
} catch (SQLException e) {
logger.error(e.toString(),e);
}
}
/**
* 增刪改通用
* @author hezhao
* @Time 2017年7月27日 下午4:22:09
* @Description 無
* @Version V 1.0
* @param sql
* @param prams
* @return
*/
protected int executeUpdate(String sql, Object... prams) {
try {
this.getConnection();
this.pstmt = conn.prepareStatement(sql);
if (prams != null) {
for (int i = 0; i < prams.length; i++) {
this.pstmt.setObject(i + 1, prams[i]);
}
}
log(sql, prams);
return this.pstmt.executeUpdate();
} catch (SQLException e) {
logger.error(e.toString(),e);
} finally {
closeAll();
}
return 0;
}
/**
* 新增娶耍，返回主鍵
* @author hezhao
* @Time 2017年1月18日 下午2:23:07
* @param sql
* @param prams
* @return
*/
protected int executeInsert(String sql, Object... prams) {
try {
this.getConnection();
//傳入?yún)?shù)：STATEMENT.RETURN_GENERATED_KEYS，指定返回生成的主鍵
this.pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
if (prams != null) {
for (int i = 0; i < prams.length; i++) {
this.pstmt.setObject(i + 1, prams[i]);
}
}
log(sql, prams);
//執(zhí)行sql
int count = this.pstmt.executeUpdate();
//返回的主鍵
int autoIncKey = 0;
if(count > 0){
ResultSet rs = pstmt.getGeneratedKeys(); //獲取結(jié)果
if (rs.next()) {
autoIncKey = rs.getInt(1);//取得ID
} else {
// throw an exception from here
logger.error("插入語句出錯(cuò)废睦，沒有獲取到新增的ID伺绽！");
}
}
return autoIncKey;
} catch (SQLException e) {
logger.error(e.toString(),e);
} finally {
closeAll();
}
return 0;
}
/**
* 查詢通用
* @author hezhao
* @Time 2017年7月27日 下午4:22:45
* @Description 無
* @Version V 1.0
* @param sql
* @param prams
*/
protected void executeQuery(String sql, Object... prams) {
try {
this.getConnection();
this.pstmt = conn.prepareStatement(sql);
if (prams != null) {
for (int i = 0; i < prams.length; i++) {
this.pstmt.setObject(i + 1, prams[i]);
}
}
log(sql, prams);
this.rs = this.pstmt.executeQuery();
} catch (SQLException e) {
logger.error(e.toString(),e);
}
}
/**
* 打印日志
* @author hezhao
* @Time 2017年7月27日 下午4:22:56
* @Description 無
* @Version V 1.0
* @param sql
* @param prams
*/
private void log(String sql, Object[] prams) {
if(isLog) {
System.out.println("===========" + sql);
if (prams != null && prams.length > 0) {
System.out.println("參數(shù)：");
for (int i = 0; i < prams.length; i++) {
if (i == prams.length - 1) {
System.out.print(prams[i] + "\n");
} else {
System.out.print(prams[i] + ",");
}
}
}
}
}
/**
* 數(shù)據(jù)庫類型
* @author hezhao on 2017年7月27日 上午11:26:08
*
*/
static enum DBType{
MySQL,
Oracle,
SQLServer, //SQLServer2005
SQLServer_JTDS, //使用jtds連接SQLServer
DB2,
Sybase,
Sybase_JTDS, //使用jtds連接Sybase
PostgreSql,
SQLite,
Derby,
H2,
HSQLDB,
ODBC; //橋連接
}
}
