* 所有DAO的基類,提供通用的訪問資料庫的代碼
* <p>
* 注意事項:
* <p>
* <ul>
* <li>調用者需要調用<code>new DBDAO();</code><code>dao.close()</code>方法來建立和關閉連接.</li>
* <li>調用者調用<code>commit()</code>方法一般相應地在異常處理裏要增加rollback()方法處理一個事務內的所有操作</li>
* </ul>
* <p>
* 批處理時﹐調用者代碼框架:
* </p>
*
* <pre>
* ...
* DBDAO dao = new DBDAO();
* try {
* dao.doBatch();
* ....
* dao.commit(); //只有有調用executeUpdate()的時候才需要commit
* }catch(SQLException e){
* dao.rollback(); //在有commit的時候處理回滾
* }finally{
* dao.close(); //最後別忘了關閉連接
* }
* ...
* </pre>
*/
public class DBDAO {
private static Logger LOG = Logger.getLogger(DBDAO.class.getName());
// 常量用于硬連接
public static final String JDBC_CLASS_NAME = "oracle.jdbc.driver.OracleDriver";
// public static final String JDBC_CONN_URL =
// "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST="
// + "(ADDRESS=(PROTOCOL=TCP)(HOST=10.142.252.156)(PORT=1521))"
// + "(ADDRESS=(PROTOCOL=TCP)(HOST=10.142.252.157)(PORT=1521))"
// + "(ADDRESS=(PROTOCOL=TCP)(HOST=10.142.252.158)(PORT=1521))"
// + "(LOAD_BALANCE=yes)(FAILOVER=on))"
// + "(CONNECT_DATA=(SERVICE_NAME=pdm)))";
public static final String JDBC_CONN_URL =
"jdbc:oracle:thin:@10.142.252.156:1521:pdm1";
public static final String JDBC_USER_PASSWORD = "wtadmin";
public static final String JDBC_USER_NAME = "wtadmin";
private Connection connection = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultset = null;
private boolean isAutoCommit = true;
private static boolean count = true;
private String connUrl = "proxool.realdb1";
// 有連接池時=true;無連接池時=false;
private boolean usePool = false;
private String poolType = "PROCOOL";
private String jndiName = "PROCOOL";
/**
* 構造方法﹐直接創建連接
*
* @throws Exception
*/
public DBDAO() throws Exception {
connection = getConn();
}
/**
* 構造方法﹐直接創建連接
*
* @throws Exception
*/
public DBDAO(boolean isAutoCommit) throws Exception {
this();
this.isAutoCommit = isAutoCommit;
connection.setAutoCommit(isAutoCommit);
}
/**
* 構造方法﹐直接創建連接,isAutoCommit = true
*
* @param connUrl
* @param uid
* @param psd
* @throws Exception
*/
public DBDAO(String connUrl, String uid, String psd) throws Exception {
this(connUrl, uid, psd, true);
}
/**
* 構造方法﹐直接創建連接
*
* @param connUrl
* @param uid
* @param psd
* @param isAutoCommit,是否自動提交,true/false
* @throws Exception
*/
public DBDAO(String connUrl, String uid, String psd, boolean isAutoCommit) throws Exception {
connection = getConnection(connUrl, uid, psd);
this.isAutoCommit = isAutoCommit;
connection.setAutoCommit(isAutoCommit);
}
/**
* 返回使用中的conn
*
* @return
* @throws Exception
*/
public Connection getConnection() throws Exception {
return this.connection;
}
/**
* 用直接連接的方式建立連接
*
* @return Connection
*/
public Connection getConnection(String connUrl, String uid, String psd) throws Exception {
try {
Class.forName(JDBC_CLASS_NAME);
connection = DriverManager.getConnection(connUrl, uid, psd);
} catch (ClassNotFoundException e) {
LOG.error(e.toString(), e);
throw e;
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
}
return this.connection;
}
/**
* 取得連接
*
* @return
* @throws Exception
*/
private Connection getConn() throws Exception {
if (usePool) {
if (poolType.equals("WEBLOGIC")) {
try {
InitialContext initialcontext = new InitialContext();
DataSource datasource = (DataSource) initialcontext.lookup(jndiName);
connection = datasource.getConnection();
} catch (NamingException e) {
LOG.error(e.toString(), e);
} catch (SQLException e) {
LOG.error(e.toString(), e);
}
} else if (poolType.equals("TOMCAT")) {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup(jndiName);
connection = ds.getConnection();
} else {
System.out.println("");
if (count)
connUrl = "proxool.realdb1";
else
connUrl = "proxool.realdb2";
try {
connection = DriverManager.getConnection(connUrl);
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
}
count = !count;
}
} else {
getConnection(JDBC_CONN_URL, JDBC_USER_NAME, JDBC_USER_PASSWORD);
}
return connection;
}
/**
* 設置是否為自動提交﹐不設置時默認為true
*
* @param b
* true或者false
*/
public void setAutoCommit(boolean b) throws SQLException {
try {
isAutoCommit = b;
connection.setAutoCommit(b);
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
}
}
/**
* 返回完整的SQL
*
* @param s
* @param pram
* @return
*/
public static String sqlString(String s, Object[] pram) {
String str = s;
if(pram!=null){
for (int i = 0; i < pram.length; i++) {
try {
str = str.replaceFirst("//?", "'" + pram[i] + "'");
} catch (PatternSyntaxException e) {
System.err.println(e.toString());
}
}
}else{
LOG.error("DBDAO.sqlString:pram is null");
}
return str;
}
/**
* 返回完整的SQL
*
* @param s
* @param pram
* @return
*/
public static String toSqlString(String s, Object[] pram) {
String str = s;
if(pram!=null){
for (int i = 0; i < pram.length; i++) {
try {
str = str.replaceFirst("//?", "'" + pram[i] + "'");
} catch (PatternSyntaxException e) {
System.err.println(e.toString());
}
}
}else{
LOG.error("DBDAO.sqlString:pram is null");
}
return str;
}
/**
* 返回是否自動提交標誌
*
* @return boolean自動提交返回真;不自動提交返回假
*/
public boolean isAutoCommit() {
return isAutoCommit;
}
/**
* 設置SQL語句中?為字串的變數的對應值 <br>
*
* @param index
* int
* @param value
* String
* @throws SQLException
*/
public void setString(int index, String value) throws SQLException {
preparedStatement.setString(index, value);
}
/**
* 設置SQL語句中?為字元留的變數的對應值 <br>
*
* @param reader
* Reader
* @throws SQLException
* SQL異常
*/
public void setCharacterStream(int index, Reader reader, int length) throws SQLException {
preparedStatement.setCharacterStream(index, reader, length);
}
/**
* 設置SQL語句中?為INT的變數的對應值 <br>
*
* @param index
* int 預製值所在位置
* @param value
* int 預製值
* @throws SQLException
* SQL異常
*/
public void setInt(int index, int value) throws SQLException {
preparedStatement.setInt(index, value);
}
/**
* 設置SQL語句中?為布爾型的變數的對應值 <br>
*
* @param index
* int 預製值所在位置
* @param value
* boolean 預製值
* @throws SQLException
* SQL異常
*/
public void setBoolean(int index, boolean value) throws SQLException {
preparedStatement.setBoolean(index, value);
}
/**
* 設置SQL語句中?為Date的變數的對應值 <br>
*
* @param index
* int 預製值所在位置
* @param value
* Date 預製值
* @throws SQLException
* SQL異常
*/
public void setDate(int index, Date value) throws SQLException {
preparedStatement.setDate(index, value);
}
/**
* 設置SQL語句中?為LONG的變數的對應值 <br>
*
* @param index
* int 預製值所在位置
* @param value
* long 預製值
* @throws SQLException
* SQL異常
*/
public void setLong(int index, long value) throws SQLException {
preparedStatement.setLong(index, value);
}
/**
* 設置SQL語句中?為FLOAT的變數的對應值 <br>
* Method setFloat.
*
* @param index
* int 預製值所在位置
* @param value
* float 預製值
* @throws SQLException
* SQL異常
*/
public void setFloat(int index, float value) throws SQLException {
preparedStatement.setFloat(index, value);
}
/**
* 設置SQL語句中?為CLOB的變數的對應值 <br>
*
* @param index
* int 預製值所在位置
* @param value
* Clob 預製值
* @throws SQLException
* SQL異常
*/
public void setClob(int index, Clob value) throws SQLException {
preparedStatement.setClob(index, value);
}
/**
dbdao2.txt
*/
}