package com.aspboy.base.database;
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.util.Hashtable;
import java.util.Vector;
import com.aspboy.base.database.util.KeyGenerator;
public class DBBeanUtil {
/**
* 执行查询语句。
*
* @param strSql
* 任何格式的查询语句
* @retrun Vector对象,包含名/值对（hashtable格式类型）的结果
*/
public static Vector execute(String strSql) throws Exception {
Connection conn = null;// 数据库联接
Statement st = null;// 执行语句
ResultSet rs = null;
ResultSetMetaData rsmd = null;
Vector vResult = new Vector();
try {
conn = DBSource.getConnection();// 取得数据联接
st = conn.createStatement();// 创建执行语句
rs = st.executeQuery(strSql);// 执行查询
rsmd = rs.getMetaData(); // 元数据
int nFieldCount = rsmd.getColumnCount();// 到得字段列数
while (rs.next()) {
Hashtable hRecord = new Hashtable();
for (int k = 1; k <= nFieldCount; k++) {
String strFieldName = rsmd.getColumnName(k);
String strTemp = rs.getString(strFieldName);
if (strTemp == null) {
strTemp = "";
}
hRecord.put(strFieldName, strTemp);
}
vResult.addElement(hRecord);
}
} catch (SQLException sqle) {
// 产生新的异常，则抛出新的程序异常
throw new SQLException("sql 发生异常");
} finally {
close(rs, st, conn);
}
return vResult;// 返回值
}
public static String getSingle(String strSql) throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String strRet = "";
try {
conn = DBSource.getConnection();
st = conn.createStatement();
rs = st.executeQuery(strSql);
if (rs.next())
strRet = rs.getString(1);
} catch (SQLException sqle) {
throw new SQLException("sql 发生异常");
} finally {
if (rs != null)
try {
rs.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (st != null)
try {
st.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
}
return strRet;
}
public static int getIntSingle(String strSql) throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
int strRet = 0;
try {
conn = DBSource.getConnection();
st = conn.createStatement();
rs = st.executeQuery(strSql);
if (rs.next())
strRet = rs.getInt(1);
} catch (SQLException sqle) {
throw new SQLException("sql 发生异常");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (rs != null)
try {
rs.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (st != null)
try {
st.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
}
return strRet;
}
/**
* 关闭ResultSet、Statement和Connection
*
* @param rs
* ResultSet to be closed
* @param stmt
* Statement or PreparedStatement to be closed
* @param conn
* Connection to be closed
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null)
try {
rs.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (stmt != null)
try {
stmt.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
}
public static void close(Statement stmt, Connection conn) {
if (stmt != null)
try {
stmt.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
}
public static void close(PreparedStatement pstmt, Connection conn) {
try {
if (pstmt != null)
pstmt.close();
} catch (Exception e) {
// logger.error(e);
}
if (conn != null)
try {
conn.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
}
public static void close(PreparedStatement pstmt) {
try {
if (pstmt != null)
pstmt.close();
} catch (Exception e) {
// logger.error(e);
}
}
public static void close(Connection conn) {
if (conn != null)
try {
conn.close();
} catch (java.sql.SQLException ex) {
ex.printStackTrace();
}
}
/**
* 执行insert update SQL。 单句时末尾无<b>;</b>。
*
* @return bRet 如果执行成功返回true，否则false。
* @param strSql
* 要执行的单句SQL或begin ...end语句。
*/
public static boolean executeSql(String strSql) throws Exception {
// System.out.println(strSql);
boolean bRet = false;// 返回值
Connection conn = null;// 数据库联接
Statement st = null;// 执行语句
try {
conn = DBSource.getConnection();// 取得数据联接
st = conn.createStatement();// 创建执行语句
bRet = st.execute(strSql);
} catch (SQLException sqle) {
throw new SQLException("sql 发生异常");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
close(st, conn);
}
return bRet;// 返回值
}
public static int getKeyValue(String keyName) {
return KeyGenerator.getInstance().getNextKey(keyName);
}
public static void main(String[] args) throws Exception {
// DBBean db=new DBBean();
/*
* String s="Select Count(*) from jxs"; String i=DBBean.getSingle(s);
* System.out.println("i="+i);
*
*
* String sql="select * from menu2"; sql="select top 2 * from menu2
* minus select top 1 * from menu2 "; Vector v=DBBean.execute(sql);
* System.out.println("v="+v);
*
*
* String insertsql="insert into test (username,password,hits) values
* (3,3,3)"; String updatesql="update test set hits=hits+1 where id=4";
* String deletesql="delete test where id=4";
* //DBBean.executeSql(updatesql); //DBBean.executeSql(insertsql);
* //DBBean.executeSql(deletesql);
*/
for (int k = 0; k < 100; k++) {
int keyvalue = DBBeanUtil.getKeyValue("test");
System.out.println("key" + (k + 1) + "===keyvalue=" + keyvalue);
}
}
public Vector execute(String sql, int pagesize, int currentpage) {
Vector ver = new Vector();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData rmd = null;
try {
conn = DBSource.getConnection();
;
stmt = conn.createStatement(
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sql);
rmd = rs.getMetaData();
int columCount = rmd.getColumnCount();
String[] columNames = new String[columCount];
// 将记录指针定位到待显示页的第一条记录上
rs.absolute((currentpage - 1) * pagesize + 1);
int i = 0;
while (i < pagesize && !rs.isAfterLast()) {
i = i + 1;
// HashMap hash = new HashMap();
Hashtable hash = new Hashtable();
String rss = "";
int idss = 0;
for (int j = 0; j < columCount; j++) {
columNames[idss] = rmd.getColumnName(j + 1);
rss = rs.getString(columNames[idss]);
hash.put(columNames[idss], rss);
}
// ver.add(idss,hash);
ver.addElement(hash);
idss++;
rs.next();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(conn, stmt, rs);
}
return ver;
}
/**
* <p>
* 关闭Connection连接,ResultSet,Statement
* </p>
*/
public void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
System.out.println("连接关闭失败。可能连接未创建成功！" + e.getMessage());
}
} else {
System.out.println("=> [Connection未创建，无法关闭]");
}
}
}