package com.trulytech.mantis.thread;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentLinkedQueue;
import com.trulytech.mantis.system.Properties;
import com.trulytech.mantis.system.logWriter;
import com.trulytech.mantis.result.DBResult;
import com.trulytech.mantis.system.ConnManager;
/**
*
* <p>
* Title: Mantis
* </p>
*
* <p>
* Description: SQL帮助类
* </p>
*
* <p>
* Copyright: Copyright (c) 2002
* </p>
*
* <p>
* Company:
* </p>
*
* @author Wang Xian
* @version 1.0
*/
public class SQLHelper {
// 数据库连接
private Connection conn = null;
private static java.util.concurrent.ConcurrentLinkedQueue cacheSQLs = new ConcurrentLinkedQueue();
/**
* 构造方法
*
* @throws Exception
*/
public SQLHelper() throws Exception {
this.conn = ConnManager.getConnection();
}
/**
* 获得数据库连接
*
* @return Connection 数据库连接
*/
public Connection getConn() {
return conn;
}
/**
* 执行update,delete操作
*
* @param SQL
* String SQL语句
* @throws SQLException
*/
public void SQLExecute(String SQL) throws SQLException {
Statement stmt = null;
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
logWriter.Debug(SQL);
stmt.executeUpdate(SQL);
}
finally {
try {
if (stmt != null)
stmt.close();
} catch (Exception e) {
}
}
}
/**
* 查询SQL操作
*
* @param SQL
* String SQL语句
* @return String[]
* @throws SQLException
*/
public String[] QuerySingle(String SQL) throws SQLException {
String[] Ret = null;
Statement stmt = null;
ResultSet rs = null;
logWriter.Debug(SQL);
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Properties.Fetch_Size);
stmt.setMaxRows(Properties.MaxRows);
stmt.setQueryTimeout(Properties.QueryTimeout);
rs = stmt.executeQuery(SQL);
if (rs.next()) {
int Count = rs.getMetaData().getColumnCount();
Ret = new String[Count];
for (int i = 1; i <= Count; i++)
Ret[i - 1] = rs.getString(i) == null ? "" : rs.getString(i);
}
}
finally {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
}
try {
if (stmt != null)
stmt.close();
} catch (Exception e) {
}
}
return Ret;
}
/**
* 返回多记录的SQL查询
*
* @param SQL
* String SQL语句
* @return DBResult
* @throws SQLException
*/
public DBResult QueryExecute(String SQL) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
DBResult ret = new DBResult();
logWriter.Debug(SQL);
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Properties.Fetch_Size);
stmt.setMaxRows(Properties.MaxRows);
stmt.setQueryTimeout(Properties.QueryTimeout);
rs = stmt.executeQuery(SQL);
ret.setColumnCount(rs.getMetaData().getColumnCount());
while (rs.next()) {
int Count = rs.getMetaData().getColumnCount();
LinkedHashMap rec = new LinkedHashMap();
for (int i = 1; i <= Count; i++) {
rec.put(rs.getMetaData().getColumnLabel(i).toLowerCase(), rs
.getString(i) == null ? "" : rs.getString(i));
}
ret.getResultBuffer().add(rec);
}
}
finally {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
}
try {
if (stmt != null)
stmt.close();
} catch (Exception e) {
}
}
return ret;
}
public void Close() throws SQLException {
if (this.conn != null)
ConnManager.closeConnection(this.conn);
}
/**
* Cache SQL
* @param SQL
* @throws SQLException
*/
public void CacheSQLExecute(String SQL) throws SQLException {
cacheSQLs.offer(SQL);
logWriter.Debug("add to Cache" + " " + SQL);
if (cacheSQLs.size() < Properties.SQL_Cache) {
return;
}
Statement stmt = null;
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
for (int i = 0; i < 100; i++) {
String tmpSQL = (String) cacheSQLs.poll();
logWriter.Debug(tmpSQL);
stmt.executeUpdate(tmpSQL);
}
}
finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
}
}
}
}