展开全部

public class DBRecordSet {

static private Logger log = Logger.getLogger(DBRecordSet.class.getName());

ResultSetMetaData md = null;

Connection conInner = null;

private int firstElementOfThisList = 0; //当前缓冲池62616964757a686964616fe59b9ee7ad9431333231626164中保存的记录在整个结果集中的位置

private int countOfElementsInthisList = 0; //缓冲池中记录的数目

private List resultList = null; //保存结果的缓冲池

private Vector columnMap = null;

private int cacheSize = -1; //保存结果的缓冲池大小

private int maxRecords = 10; //执行结果集的时候得到的最多的记录数

private String strSQLStmt = null; //打开结果集的时候执行的SQL语句

private boolean isClosed = true; //结果集是否已经open

private int columnCount = -1; //结果集字段数目

private int columnTypeInt[] = null;

private String columnTypeString[] = null;

private int curRow = 0; // 当前光标所在行，基数为 1

private int maxRow = -1; // 执行查询语句得到的记录数，基数为 1

private int curPage = -1; // 分页显示时当前所在页，基数为 1

private int pageSize = -1; // 分页显示时每页记录数，基数为 1

private int pageCount = -1; // 分页显示时总页数，基数为 1

private int updateCount = -1;

private boolean cursorDir = true;

DBConnectionManager connMgr = null;

private DBConnectionManager getConnectionManager() {

if (connMgr == null) {

connMgr = DBConnectionManager.getInstance();

}

return connMgr;

}

private int getCacheSize() {

if (this.cacheSize == -1) {

cacheSize = getConnectionManager().getCacheSize();

if (cacheSize <= 0)

cacheSize = 50;

}

return this.cacheSize;

}

public void setCacheSize(int size) {

this.cacheSize = size;

}

/**

* 构造函数

*/

public DBRecordSet() {

close();

}

public int execute(Connection con, String sql) {

if (con == null || sql == null || sql.length() <= 0) {

return -1;

}

Statement stmt = null;

try {

if (con.isClosed()) return -1;

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

// 设置 ResultSet 对象可包含的最多行数

if (maxRecords > 1) stmt.setMaxRows(maxRecords);

//执行语句，判断语句类型

log.debug("开始执行SQL语句: " + sql);

int resultCount = stmt.executeUpdate(sql);

log.debug("执行SQL语句成功: " + sql + "; 返回结果数目为" + resultCount);

return resultCount;

} catch (Exception e) {

log.error("执行SQL语句失败:" + e.getMessage());

} finally {

try {

if (stmt != null)

stmt.close();

} catch (Exception e) {

log.error("关闭Statement失败:" + e.getMessage());

return -1;

}

}

return -1;

}

public boolean openSelect(Connection con, String sql) {

Statement stmt = null;

ResultSet rs = null;

int n = 0, i = 0;

if (con == null) return false;

firstElementOfThisList = 0;

countOfElementsInthisList = 0;

try {

if (con.isClosed()) return false;

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

// 设置 ResultSet 对象可包含的最多行数

if (maxRecords > 1) stmt.setMaxRows(maxRecords);

//执行语句，判断语句类型

log.debug("开始执行SQL语句: " + sql);

if (stmt.execute(sql)) {

log.debug("执行查询语句成功");

rs = stmt.getResultSet();

md = rs.getMetaData();

columnCount = md.getColumnCount();

updateCount = -1;

if (resultList != null) {

resultList.clear();

resultList = null;

}

resultList = new ArrayList();

if (columnMap != null) {

columnMap = null;

}

columnMap = new Vector(columnCount);

for (n = 1; n <= columnCount; ++n) {

String columnName = md.getColumnName(n).trim();

columnName = columnName.toLowerCase();

if (columnName == null || columnName.length() <= 0) {

columnName = "vcolumn_" + String.valueOf(n);

}

if (columnMap.contains(columnName)) {

columnName += "_" + String.valueOf(n);

}

columnMap.add(columnName);

}

/*

for (n = 1; n <= columnCount; ++n){

log.debug("查询语句结果集的列" + n + ":" + String.valueOf(columnMap.get(n - 1)));

}

*/

PropertyContainer property = null;

while (rs.next() && i < getCacheSize()) {

property = new PropertyContainerImpl();

for (n = 1; n <= columnCount; ++n) {

try {

property.addPropertyBy(String.valueOf(columnMap.get(n - 1)), rs.getObject(n));

} catch (Exception e) {

property.addPropertyBy(String.valueOf(columnMap.get(n - 1)), rs.getString(n));

}

}

resultList.add(property);

i++;

}

if (property != null)

log.debug("Open查询的最后一条记录是：" + property.valueToString());

if (i > 0) { //如果有记录取出

firstElementOfThisList = 1;

countOfElementsInthisList = resultList.size();

maxRow = i;

if (i >= getCacheSize()) { //记录没有取完

//注意：为了兼容以前代码，这里需要将结果集滚动到最后，用来获得记录数目

while (rs.next()) maxRow++;

maxRow++;

}

curRow = 0;

} else {//如果没有记录

firstElementOfThisList = 0;

countOfElementsInthisList = 0;

curRow = -1;

maxRow = 0;

log.debug("没有记录返回：" + sql);

}

log.debug("open: 读取从第0条记录开始的" + getCacheSize() + "条记录, 返回记录" + countOfElementsInthisList + "条。总记录数为" + maxRow + "条");

} else {

// 执行更新语句后将查询结果集关闭并清除各项信息

int updatecount = stmt.getUpdateCount();

close();

updateCount = updatecount;

log.debug("成功执行更新语句");

} //保存执行的SQL语句

strSQLStmt = sql;

} catch (SQLException e) {

log.error(e.toString() + " : 执行SQL语句时出错：" + sql);

return false;

} catch (Exception e) {

log.error(e.toString() + " : 执行SQL语句时出错：" + sql);

return false;

} finally {

try {

if (rs != null) {

rs.close();

rs = null;

}

if (stmt != null) {

stmt.close();

stmt = null;

}

//getConnectionManager().freeConnectionInner(con);

} catch (Exception e) {

log.error(e.toString() + " : 结果集不能正确关闭");

//getConnectionManager().freeConnectionInner(con);

return false;

}

}

isClosed = false;

return true;

}

/**

* 执行SQL语句，可以为查询或更新语句。

* 执行更新语句后调用 getUpdateCount() 取得所更新的记录数

*

* @param sql 执行的SQL语句

*/

public boolean open(String sql) {

Statement stmt = null;

ResultSet rs = null;

int n = 0, i = 0;

Connection con = getConnectionManager().getConnectionInner();

if (con == null) return false;

firstElementOfThisList = 0;

countOfElementsInthisList = 0;

try {

if (con.isClosed()) return false;

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

// 设置 ResultSet 对象可包含的最多行数

if (maxRecords > 1) stmt.setMaxRows(maxRecords);

//执行语句，判断语句类型

log.debug("开始执行SQL语句: " + sql);

if (stmt.execute(sql)) {

log.debug("执行查询语句成功");

rs = stmt.getResultSet();

md = rs.getMetaData();

columnCount = md.getColumnCount();

updateCount = -1;

if (resultList != null) {

resultList.clear();

resultList = null;

}

resultList = new ArrayList();

if (columnMap != null) {

columnMap = null;

}

columnMap = new Vector(columnCount);

for (n = 1; n <= columnCount; ++n) {

String columnName = md.getColumnName(n).trim();

columnName = columnName.toLowerCase();

if (columnName == null || columnName.length() <= 0) {

columnName = "vcolumn_" + String.valueOf(n);

}

if (columnMap.contains(columnName)) {

columnName += "_" + String.valueOf(n);

}

columnMap.add(columnName);

}

/*

for (n = 1; n <= columnCount; ++n){

log.debug("查询语句结果集的列" + n + ":" + String.valueOf(columnMap.get(n - 1)));

}

*/

PropertyContainer property = null;

while (rs.next() && i < getCacheSize()) {

property = new PropertyContainerImpl();

for (n = 1; n <= columnCount; ++n) {

try {

property.addPropertyBy(String.valueOf(columnMap.get(n - 1)), rs.getObject(n));

} catch (Exception e) {

property.addPropertyBy(String.valueOf(columnMap.get(n - 1)), rs.getString(n));

}

}

resultList.add(property);

i++;

}

if (property != null)

log.debug("Open查询的最后一条记录是：" + property.valueToString());

if (i > 0) { //如果有记录取出

firstElementOfThisList = 1;

countOfElementsInthisList = resultList.size();

maxRow = i;

if (i >= getCacheSize()) { //记录没有取完

//注意：为了兼容以前代码，这里需要将结果集滚动到最后，用来获得记录数目

while (rs.next()) maxRow++;

maxRow++;

}

curRow = 0;

} else {//如果没有记录

firstElementOfThisList = 0;

countOfElementsInthisList = 0;

curRow = -1;

maxRow = 0;

log.debug("没有记录返回：" + sql);

}

log.debug("open: 读取从第0条记录开始的" + getCacheSize() + "条记录, 返回记录" + countOfElementsInthisList + "条。总记录数为" + maxRow + "条");

} else {

// 执行更新语句后将查询结果集关闭并清除各项信息

int updatecount = stmt.getUpdateCount();

close();

updateCount = updatecount;

log.debug("成功执行更新语句");

} //保存执行的SQL语句

strSQLStmt = sql;

} catch (SQLException e) {

log.error(e.toString() + " : 执行SQL语句时出错：" + sql);

return false;

} catch (Exception e) {

log.error(e.toString() + " : 执行SQL语句时出错：" + sql);

return false;

} finally {

try {

if (rs != null) {

rs.close();

rs = null;

}

if (stmt != null) {

stmt.close();

stmt = null;

}

getConnectionManager().freeConnectionInner(con);

} catch (Exception e) {

log.error(e.toString() + " : 结果集不能正确关闭");

getConnectionManager().freeConnectionInner(con);

return false;

}

}

isClosed = false;

return true;

}

/**

* 根据语句，得到从startIndex开始的count条记录

*/

private void getResultAt(int start, int count) throws Exception {

if (isClosed) {

throw new Exception("还没有打开结果集");

}

Statement stmt = null;

ResultSet rs = null;

Connection con = getConnectionManager().getConnectionInner();

int readStart = start;

int readCount = count;

if (con == null) {

log.error("无法获得有效连接");

throw new Exception("getResultAt: 无法获得有效连接");

}

try {

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

rs = stmt.executeQuery(strSQLStmt);

if (resultList != null) {

resultList.clear();

resultList = null;

}

resultList = new ArrayList();

// skip initial rows as specified by the start parameter.

while (start-- > 1 && rs.next()) ;

//分别对每一个字段，取出数值，放到BaseBusinessObject的PropertyContainer中

while (count-- > 0 && rs.next()) {

PropertyContainer property = new PropertyContainerImpl();

for (int n = 1; n <= columnCount; ++n) {

try {

property.addPropertyBy(String.valueOf(columnMap.get(n - 1)), rs.getObject(n));

} catch (Exception e) {

property.addPropertyBy(String.valueOf(columnMap.get(n - 1)), rs.getString(n));

}

}

resultList.add(property);

}

log.debug("getResultAt: 读取从第" + readStart + "条记录开始的" + readCount + "条记录, 返回记录" + resultList.size() + "条");

} catch (SQLException e) {

throw new Exception(e.toString());

} finally {

try {

if (rs != null) {

rs.close();

rs = null;

}

if (stmt != null) {

stmt.close();

stmt = null;

}

getConnectionManager().freeConnectionInner(con);

} catch (Exception e) {

log.error(e.toString() + " : 结果集不能正确关闭");

getConnectionManager().freeConnectionInner(con);

}

}

}

/**

* 执行SQL语句，可以为查询或更新语句。

* 执行更新语句后调用 getUpdateCount() 取得所更新的记录数

*

* @param sql 执行的SQL语句

*/

public boolean openGBK(String sql) {

return open(Convert.toGBK(sql));

}

/**

* 返回执行查询语句后表的列数

*/

public int getColumnCount() {

return columnCount;

}

/**

* 返回每列的类型，基数为 1

*

* @param schema 表模式名

* @param table 表名

*/

public int[] getColumnType(String schema, String table) {

Connection con = null;

ResultSet results = null;

List list = new ArrayList();

if (columnTypeInt == null) {

log.debug("getColumnType: getConnection");

con = getConnectionManager().getConnectionInner();

if (con == null) return null;

try {

DatabaseMetaData dmd;

dmd = con.getMetaData();

results = dmd.getColumns(null, null, table.toUpperCase(), null);

//

while (results.next()){

list.add(new Integer(results.getInt("DATA_TYPE")));

}

columnTypeInt = new int[list.size()];

for(int i = 0; i < list.size(); i++){

Integer type = (Integer)list.get(i);

columnTypeInt[i] = type.intValue();

}

} catch (Exception e) {

return null;

} finally {

try {

results.close();

getConnectionManager().freeConnectionInner(con);

} catch (Exception e) {

getConnectionManager().freeConnectionInner(con);

log.error(e.toString() + " : 结果集不能正确关闭");

return null;

}

}

}

return columnTypeInt;

}

/**

* 返回每列的名称，基数为 1

*

* @param schema 表模式名

* @param table 表名

*/

public String[] getColumnName(String schema, String table) {

Connection con = null;

ResultSet results = null;

if (columnTypeString == null) {

log.debug("getColumnName: getConnection");

con = getConnectionManager().getConnectionInner();

if (con == null) return null;

try {

DatabaseMetaData dmd;

dmd = con.getMetaData();

results = dmd.getColumns(null, null, table.toUpperCase(), null);

//

int i = 1;

while (results.next()) i++;

columnTypeString = new String[i];

i = 1;

results.beforeFirst();

while (results.next()) columnTypeString[i++] = results.getString("COLUMN_NAME").trim();

} catch (Exception e) {

return null;

} finally {

try {

results.close();

getConnectionManager().freeConnectionInner(con);

} catch (Exception e) {

getConnectionManager().freeConnectionInner(con);

log.error(e.toString() + " : 结果集不能正确关闭");

return null;

}

}

}

return columnTypeString;

}

/**

* 返回执行更新语句后实际更新的记录数

*/

public int getUpdateCount() {

return updateCount;

}

/**

* 设置查询语句执行完后取得的最大记录数

*

* @param maxrec 最大记录数

*/

public void setMaxRecords(int maxrec) {

maxRecords = maxrec;

}

/**

* 返回查询语句执行完后取得的最大记录数

*/

public int getMaxRecords() {

return maxRecords;

}

/**

* 关闭查询结果集并清除各项信息

*/

public void close() {

md = null;

firstElementOfThisList = 0; //当前缓冲池中保存的记录在整个结果集中的位置

countOfElementsInthisList = 0; //缓冲池中记录的数目

if (resultList != null) {

int size = resultList.size();

本回答由提问者推荐

已赞过

已踩过<

你对这个回答的评价是？

评论

收起