java执行sql处理类，包括CLOB，Blob字段的处理
import java.io.CharArrayWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class ExeSqlDAO {
private Log log = LogFactory.getLog(this.getClass().getName());
privateint rows, cols;
/**
* 执行数据库的更新操作
*
* @param sql SQL语句
* @param con 数据库连接对象
* @exception java.sql.SQLException
*/
//do update
public void update(String sql, Connection con) throws SQLException {
log.info("JDBC SQL: "+sql);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.executeUpdate();
pstmt.close();
}
/**
* 批量执行数据库操作
*
* @param sql
* @param con
* @throws SQLException
*/
public void muchUpdate(String[] sql, Connection con) throws SQLException {
for (int i=0;i<sql.length;i++){
if (sql[i]!=null&&sql[i].length()>0&&!sql[i].equals("")){
update(sql[i],con);
}
}
}
/**
* 执行查询数据库操作
*
* @param sql SQL语句
* @param con 数据库连接对象
* @return Result对象
* @exception java.sql.SQLException
*/
//do select
public Resultobj queryExecute(String sql, Connection con) throws SQLException {
Statement stmt = null;
Resultobj result = new Resultobj();
rows = 0;
stmt = con.createStatement();
log.info("JDBC SQL: "+sql);
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
String s[] = new String[cols];
for (int i = 1; i <= cols; i++) {
s[i - 1] = rsmd.getColumnLabel(i);
}
result.add(s);
rows++;
if(rs==null){
System.out.println("#############");
}
while (rs.next()) {
s = new String[cols];
for (int i = 1; i <= cols; i++) {
s[i - 1] = helper(rs, rsmd.getColumnType(i), i);
}
result.add(s);
rows++;
}
result.setCols(cols);
result.setRows(rows);
rs.close();
stmt.close();
return (result);
}
/**
* 执行查询数据库操作
* @param sql SQL语句
* @param con 数据库连接对象
* @param maxRow 取得最大记录个数
* @return Result对象
* @exception java.sql.SQLException
*/
//do select
public Resultobj queryByMaxRow(String sql, Connection con,int intMaxRows) throws SQLException {
Statement stmt = null;
Resultobj result = new Resultobj();
rows = 0;
stmt = con.createStatement();
stmt.setMaxRows(intMaxRows);
log.info("JDBC SQL: "+sql);
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
String s[] = new String[cols];
for (int i = 1; i <= cols; i++) {
s[i - 1] = rsmd.getColumnLabel(i);
}
result.add(s);
rows++;
while (rs.next()) {
s = new String[cols];
for (int i = 1; i <= cols; i++) {
s[i - 1] = helper(rs, rsmd.getColumnType(i), i);
}
result.add(s);
rows++;
}
result.setCols(cols);
result.setRows(rows);
//System.out.print("cols"+cols);
//System.out.print("rows"+rows);
rs.close();
stmt.close();
return (result);
}
/**
* 对记录中的字段的类型转换
* @param rs
* @param dataType
* @param col
* @return
* @throws SQLException
*/
public String helper(ResultSet rs, int dataType, int col) throws SQLException {
String retValue = null;
Integer intObj;
// ask for data depending on the datatype
switch (dataType) {
case Types.DATE :
java.sql.Date date = rs.getDate(col);
if (date != null)
retValue = date.toString();
break;
case Types.TIME :
java.sql.Time time = rs.getTime(col);
if (time != null)
retValue = time.toString();
break;
case Types.TIMESTAMP :
java.sql.Timestamp timestamp = rs.getTimestamp(col);
if (timestamp != null)
retValue = timestamp.toString();
break;
case Types.CHAR :
case Types.VARCHAR :
case Types.LONGVARCHAR :
retValue = rs.getString(col);
break;
case Types.NUMERIC :
case Types.DECIMAL ://zhanpeng 修改了不建议使用的api
java.math.BigDecimal numeric = rs.getBigDecimal(col);
if (numeric != null)
retValue = numeric.toString();
break;
case Types.BIT :
boolean bit = rs.getBoolean(col);
Boolean boolObj = new Boolean(bit);
retValue = boolObj.toString();
break;
case Types.TINYINT :
byte tinyint = rs.getByte(col);
intObj = new Integer(tinyint);
retValue = intObj.toString();
break;
case Types.SMALLINT :
short smallint = rs.getShort(col);
intObj = new Integer(smallint);
retValue = intObj.toString();
break;
case Types.INTEGER :
int integer = rs.getInt(col);
intObj = new Integer(integer);
retValue = intObj.toString();
break;
case Types.BIGINT :
long bigint = rs.getLong(col);
Long longObj = new Long(bigint);
retValue = longObj.toString();
break;
case Types.REAL :
float real = rs.getFloat(col);
Float floatObj = new Float(real);
retValue = floatObj.toString();
break;
case Types.FLOAT :
case Types.DOUBLE :
double longreal = rs.getDouble(col);
Double doubleObj = new Double(longreal);
retValue = doubleObj.toString();
break;
case Types.BINARY :
case Types.VARBINARY :
case Types.LONGVARBINARY :
byte[] binary = rs.getBytes(col);
if (binary != null)
retValue = new String(binary);
break;
case Types.CLOB :
if(rs.getClob(col)!=null){
try {
Clob cBlob =(Clob)rs.getClob(col);
retValue=readClob(cBlob);
} catch (IOException e) {
}
}
break;
}
if (retValue==null) {
retValue = "";
}
return retValue;
}
//处理Clob字段
public String readClob(Clob cBlob)throws java.sql.SQLException, java.io.IOException {
Reader r = cBlob.getCharacterStream();
char[] b = new char[1024 * 3];
int i = 0;
CharArrayWriter caw = new CharArrayWriter();
while ((i = r.read(b)) > 0) {
caw.write(b, 0, i);
}
b = caw.toCharArray();
String result = new String(b);
return result;
}
public Resultobj queryExecute (ResultSet rs) throws SQLException{
Resultobj result = new Resultobj();
ResultSetMetaData rsmd = rs.getMetaData();
int rows = 0;
int cols = rsmd.getColumnCount();
String s[] = new String[cols];
for(int i = 1;i<=cols;i++){
s[i-1] = rsmd.getColumnLabel(i);
}
result.add(s);
rows++;
while(rs.next()){
s = new String[cols];
for(int i =1;i<=cols;i++){
s[i-1]=helper(rs,rsmd.getColumnType(i),i);
}
result.add(s);
rows++;
}
result.setCols(cols);
result.setRows(rows);
return result;
}
}
