/**
* Description:数据库封装操作，连接池
* Author：方继祥
* Version：1.0
*确认已导入oracle相关数据包
*/
package com.fangjixiang.db;
import java.sql.*;
import java.io.*;
import java.io.Reader;
import javax.naming.*;
import java.util.Properties;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
import oracle.sql.BLOB;
public class DBExecute {
private Connection conn = null;
private Statement stmt = null; //有回滚的
private PreparedStatement preStmt = null; //@param查询
private String sTableName;
private String sPK;
private String sID;
private int iID;
private String sUF;
private int iUF;
private int iUFV;
private String sUFV;
private boolean isAutoCommit;
/**
* 数据库连接，通过JNDI查找数据源的方式连接数据库
* 返回值小于0时，连接失败，等于0时，连接成功
*/
public DBExecute(){
connectDB() ;
}
public int connectDB() {
if (conn == null) {
try {
//System.setProperty(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
//System.setProperty(Context.PROVIDER_URL, "t3://liuhl:7001");
InputStream is = getClass().getResourceAsStream(
"/db.properties");
Properties dbProps = new Properties();
try {
dbProps.load(is);
} catch (Exception e) {
System.err.println("不能读取属性文件。请确保db.properties在你的CLASSPATH中");
return -200;
}
InitialContext initCtx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) initCtx.lookup(
dbProps.getProperty("dbJndi"));
//javax.sql.DataSource ds = (javax.sql.DataSource) initCtx.lookup("java:comp/env/oraDB");
if (ds != null) {
this.conn = ds.getConnection();
} else {
return -300;
}
return 0;
} catch (NamingException ex) {
String strErrLog = "NamingException " + ex.getMessage();
System.out.println(strErrLog);
return -201;
} catch (SQLException ex) {
String strErrLog = "SQLException " + ex.getMessage();
return -1 * Math.abs(ex.getErrorCode());
} catch (Exception ex) {
String strErrLog = ex.toString();
return -202;
}
} else {
return 0;
}
}
/**
/**
* 过程开始
* @throws SQLException 捕捉错误
*/
public void beginTrans() throws SQLException {
try {
isAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.print("beginTrans Errors");
throw ex;
}
}
/**
* 获取数据库连接
*/
public Connection getConnection() {
return conn;
}
/**
* 数据事务提交
* @throws SQLException 捕捉错误
*/
public void commit() throws SQLException {
try {
conn.commit();
conn.setAutoCommit(isAutoCommit);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.print("Commit Errors!");
throw ex;
}
}
/**
* 数据事务回滚
*/
public void rollback() {
try {
conn.rollback();
conn.setAutoCommit(isAutoCommit);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.print("Roolback Error!");
}
}
/**
* 判断是否为自动加入数据模式
* @return boolean值
* @throws SQLException 捕捉错误
*/
public boolean getAutoCommit() throws SQLException {
boolean result = false;
try {
result = conn.getAutoCommit();
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.println("getAutoCommit fail " + ex.getMessage());
throw ex;
}
return result;
}
/**
* 清空PrepareStatement中的参数。
*
* @throws SQLException SQL异常
*/
public void clearParameters() throws SQLException {
if (null != this.preStmt) {
preStmt.clearParameters();
}
}
public PreparedStatement pstmt(String str_sql) {
this.preStmt = null;
try {
this.preStmt = conn.prepareStatement(str_sql);
}
catch (SQLException ex) {
System.err.println("preparedStatement(): " + ex.getMessage());
}
return this.preStmt;
}
/**
* 设置字符串值
*
* @param index 索引
* @param value 字符串值
* @throws SQLException SQL异常
*/
public void setString(int index, String value) throws SQLException {
preStmt.setString(index, value);
}
public void setInt(int index, int value) throws SQLException {
preStmt.setInt(index, value);
}
public void setBoolean(int index, boolean value) throws SQLException {
preStmt.setBoolean(index, value);
}
public void setDate(int index, Date value) throws SQLException {
preStmt.setString(index, value.toString());
}
public void setLong(int index, long value) throws SQLException {
preStmt.setLong(index, value);
}
public void setFloat(int index, float value) throws SQLException {
preStmt.setFloat(index, value);
}
public void setBytes(int index, byte[] value) throws SQLException {
preStmt.setBytes(index, value);
}
/**
* 设置PrepareStatement，并同时其清空参数列表。
*
* @param sql SQL语句
* @throws SQLException SQL异常
*/
public void setPrepareStatement(String sql) throws SQLException {
this.clearParameters();
this.preStmt = this.conn.prepareStatement(sql);
}
/**
* executeQuery操作，用于数据查询，主要是Select
* @param sql 查询字段
* @return 数据集
* @throws SQLException 捕捉错误
*/
public ResultSet executeQuery(String sql) throws SQLException {
ResultSet rs = null;
try {
stmt =
conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.println("dbTrans.executeQuery:" + ex.getMessage());
throw ex;
}
return rs;
}
/**
* executeUpdate操作，用于数据更新，主要是Update，Insert
* @param sql 查询字段
* @throws SQLException 捕捉错误
*/
public int executeUpdate(String sql) throws SQLException {
int i = 0;
try {
stmt =
conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
i = stmt.executeUpdate(sql);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.println("dbTrans.executeUpdate:" + ex.getMessage());
throw ex;
}
return i;
}
public void close() throws Exception {
if (stmt != null) {
stmt.close();
stmt = null;
}
if (preStmt != null) {
preStmt.close();
preStmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
}
/*
* clob Insert
* inSql : Insert SQL语句或者UPDATE语句
* querySql : select SQL语句
*/
public void clobInsert(String upSql, String querySql, String s) throws
Exception {
try {
this.isAutoCommit = false;
this.beginTrans();
this.executeUpdate(upSql);
ResultSet rs = this.executeQuery(querySql);
while (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
BufferedWriter out = new BufferedWriter(clob.
getCharacterOutputStream());
//BufferedReader in = new BufferedReader(new FileReader(s)); //File
Reader in = new StringReader(s); //Text
int c;
while ( (c = in.read()) != -1) {
out.write(c);
}
in.close();
out.close();
}
this.commit();
}
catch (Exception ex) {
this.rollback();
throw ex;
}
}
/*
* CLOB数据的读取，将clob字段的内容读出并插入到某个文件中
* querySql: select clobfiled from table where ....
* s:生成的文件
*
*/
public void clobRead(String querySql, String s) throws Exception {
try {
this.isAutoCommit = false;
this.beginTrans();
ResultSet rs = this.executeQuery(querySql);
while (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(s));
int c;
while ( (c = in.read()) != -1) {
out.write(c);
}
out.close();
in.close();
}
this.commit();
}
catch (Exception ex) {
this.rollback();
throw ex;
}
}
/*
* 读取clob字段中内容，返回String
*
*/
public String clobRead(String querySql) throws Exception {
String s = "";
try {
String str;
this.isAutoCommit = false;
this.beginTrans();
ResultSet rs = this.executeQuery(querySql);
while (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
BufferedReader in = new BufferedReader(clob.getCharacterStream());
StringBuffer buffer = new StringBuffer();
while ( (str = in.readLine()) != null) {
buffer.append(str).append("\n");
}
s = buffer.toString();
in.close();
}
this.commit();
}
catch (Exception ex) {
this.rollback();
throw ex;
}
return s;
}
/*
* BLOB Insert
*
*
*/
public void blobReplace(String upSql, String querySql, String s) throws
Exception {
try {
this.isAutoCommit = false;
this.beginTrans();
this.executeUpdate(upSql);
ResultSet rs = this.executeQuery(querySql);
while (rs.next()) {
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1);
BufferedOutputStream out = new BufferedOutputStream(blob.
getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new
FileInputStream(s));
int c;
while ( (c = in.read()) != -1) {
out.write(c);
}
in.close();
out.close();
}
this.commit();
}
catch (Exception ex) {
this.rollback();
throw ex;
}
}
/*
* BLOB Read
*
*/
public void blobRead(String querySql, String s) throws Exception {
try {
this.isAutoCommit = false;
this.beginTrans();
ResultSet rs = this.executeQuery(querySql);
while (rs.next()) {
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1);
/* 以二进制形式输出 */
BufferedOutputStream out = new BufferedOutputStream(new
FileOutputStream(s));
BufferedInputStream in = new BufferedInputStream(blob.
getBinaryStream());
int c;
while ( (c = in.read()) != -1) {
out.write(c);
}
in.close();
out.close();
}
this.commit();
}
catch (Exception ex) {
this.rollback();
throw ex;
}
}
public static void main(String[] args) throws Exception {
DBExecute dbc = new DBExecute();
try {
int indt = dbc.connectDB();
System.out.print(indt);
/* CLOB Insert */
//String upSql = "INSERT FANG_TEST VALUES(2,EMPTY_CLOB())";
//String querySql =
//"SELECT CONTENT FROM FANG_TEST WHERE ID=2 FOR UPDATE";
//String s = "c:/clobReplace2.txt";
//dbc.clobInsert(upSql,querySql,s);
/* CLOB Replace */
//String upSql = "UPDATE FANG_TEST SET CONTENT = EMPTY_CLOB() WHERE ID=2";
//String querySql = "SELECT CONTENT FROM FANG_TEST WHERE ID=2 FOR UPDATE";
//String s = "c:/clobReplace2.txt";
//dbc.clobInsert(upSql,querySql,s);
/* CLOB Read */
//String querySql = "SELECT CONTENT FROM FANG_TEST WHERE ID=2";
//System.out.println(dbc.clobRead(querySql));
/* BLOB Replace */
//String upSql = "UPDATE FANG_TEST_BLOB SET CONTENT = EMPTY_BLOB() WHERE ID=2";
//String querySql = "SELECT CONTENT FROM FANG_TEST_BLOB WHERE ID = 2 FOR UPDATE";
//String s = "c:/755.jpg";
//dbc.blobReplace(upSql,querySql,s);
/* BLOB Read */
//String querySql = "SELECT CONTENT FROM FANG_TEST_BLOB WHERE ID=2";
//String s = "c:/756.jpg";
//dbc.blobRead(querySql,s);
}
catch (Exception e) {
throw e;
}
finally {
dbc.close();
}
}
}