package jsswt.sendsms;
/**
* <p>Title: </p>
* <p>Description: 数据库操作类
* 支持动态配置文件dbconfig.txt
* 支持Struts连接池和其他连接池（需要外部连接池支持）
* 支持数据分页显示（与laosun.servlet.Pagelet配合使用可以实现不用写一行代码实现分页JSP）
* 支持类反射机制自动完成任何标准FormBean的数据赋值（用于Struts）
* </p>
* <p>Copyright: Copyright (c) 2004</p>
* <p>Company: </p>
* @author SunHui
* @version 1.01
*/
import java.io.*;
import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import javax.servlet.http.*;
import javax.sql.*;
import laosun.string.*;
public class DBO {
private boolean connected = false; // 是否连接上了
private Connection conn = null; // 数据库连接
private File cfgFile = null; // 数据库配置信息文件，主要用于试验数据库连接，不要用于商用
private String driverClass = null; // 数据库驱动类
private String url = null; // 数据库url
private String user = null; // 数据库连接用户
private String password = null; // 数据库连接密码
private Vector columnNames = new Vector(); // 列名
private Vector vContent = new Vector(); // 表数据
private int totalRec = 0; // 总记录数，用于分页
private int totalPage = 1; // 总页数
private long aa;
private int totalR=0;
public DBO() {
}
// 用于在Struts环境下自动获得连接
public DBO(HttpServlet servlet, String dataSourceName) throws
Exception {
if (servlet != null && dataSourceName != null) {
DataSource ds = (DataSource) servlet.getServletContext().getAttribute(
dataSourceName);
setConn(ds.getConnection());
}
else {
throw new Exception("Servlet or dataSourceName is null !");
}
}
public DBO(DataSource dataSource) {
try {
setConn(dataSource.getConnection());
}
catch (SQLException ex) {
ex.printStackTrace();
}
}
/**
* determine connect state
* @return boolean
*/
public boolean isConnected() {
return connected;
}
/**
* load database config file
* @param filename String
* @return boolean
* @deprecated 使用该连接方式可能导致数据库性能不佳
*/
public boolean loadCfgFile(String filename) {
Properties p = new Properties();
try {
cfgFile = new File(filename);
p.load(new FileInputStream(cfgFile));
driverClass = p.getProperty("driverClass");
url = p.getProperty("url");
user = p.getProperty("user");
password = p.getProperty("password");
return true;
}
catch (Exception ex) {
System.out.println("Error in DBOperator.loadCfgFile(filename)" + ex);
return false;
}
}
/**
* 打开数据库连接
* @return boolean
*/
public boolean openDB() {
if (connected) {
return true;
}
if (!connected && cfgFile == null) {
System.out.println(
"Error operation! Please load database config file first!");
System.out.println("Example of database config file:");
System.out.println("----------------------------/n"
+ "sqlserver2000 example/n"
+
"driverClass=com.microsoft.jdbc.sqlserver.SQLServerDriver/n"
+ "url=jdbc:microsoft:sqlserver://localhost:1433/n"
+ "user=shy/n"
+ "password=shy/n"
+ "----------------------------/n"
+ "oracle example/n"
+ "driverClass=oracle.jdbc.driver.OracleDriver/n"
+ "url=jdbc:oracle:thin:@localhost:1521:sid/n"
+ "user=shy/n"
+ "password=shy/n"
+ "----------------------------/n"
+ "odbc example/n"
+ "driverClass=sun.jdbc.odbc.JdbcOdbcDriver/n"
+ "url=jdbc:odbc:odbcname or jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=xxx.mdb/n"
+ "user=shy/n"
+ "password=shy/n"
+ "----------------------------/n"
+ "mysql example/n"
+ "driverClass=com.mysql.jdbc.Driver/n"
+ "url=jdbc:mysql://localhost:3306/database/n"
+ "user=shy/n"
+ "password=shy/n"
);
connected = false;
}
try {
Class.forName(driverClass).newInstance();
conn =
DriverManager.getConnection(url, user, password);
}
catch (Exception e) {
close(null, null, conn);
connected = false;
System.out.println("Error in DBOperator.openDB()" + e);
connected = false;
}
connected = true;
System.out.println("Connect to " + url + " successful");
return connected;
}
/**
* 取表列名集合
* @return Vector
*/
public Vector getColumnNames() {
if (columnNames.isEmpty()) {
System.out.println("Column names empty!");
}
return columnNames;
}
/**
* 运用类反射机制生成数据对象的集合，用于Struts环境
* @param sql String 查询语句
* @param className String 类名
* @param params Vector 参数集合
* @return v 数据对象的集合
*/
public Vector reflect(String sql, String className, Vector params) {
Vector v = new Vector();
openDB();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
if (!connected) {
return v;
}
pstmt = conn.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
if (StrUtil.count(sql, "?") != params.size()) {
System.out.println("参数个数不匹配");
}
else {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
}
}
rs = pstmt.executeQuery();
Object recordValue; // 数据库字段值
Object formBean; // 封装表的bean
Object oTmp = new Object(); // 对象模板
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
formBean = Class.forName(className).newInstance();
for (int i = 1; i <= columnCount; i++) {
recordValue = (rs.getObject(i) != null) ? rs.getObject(i) : "null";
// 列名首字母大写，其余小写
String prop = Character.toUpperCase(rsmd.getColumnName(i).charAt(0)) +
rsmd.getColumnName(i).toLowerCase().substring(1);
String mName = "set" + prop;
// 默认所有formBean方法的参数都为Object型，这样可以简化操作
Method m = formBean.getClass().getMethod(mName,
new Class[] {oTmp.getClass()});
m.invoke(formBean, new Object[] {recordValue});
}
v.add(formBean);
}
}
catch (Exception e) {
e.printStackTrace();
}
finally {
close(pstmt, rs, conn);
return v;
}
}
/**
* 运用类反射机制生成数据对象的集合，用于Struts环境
* @param sql String 查询语句
* @param className String 类名
* @param pageNum int 页码
* @param pageSize int 每页最多记录数
* @return vBean 数据对象的集合
*/
public Vector reflect(int totalRec,String sql, String className, long pageNum,
int pageSize) {
Vector vBean = new Vector();
Vector vTemp = new Vector();
try {
vTemp = queryPage(totalRec,sql, pageNum, pageSize);
}
catch (Exception ex) {
ex.printStackTrace();
}
if (vTemp.size() > 0) {
for (int i = 0; i < vTemp.size(); i++) {
Object recordValue; // 数据库字段值
Object formBean = null; // 封装表的bean
Object oTmp = new Object(); // 对象模板
HashMap hm = (HashMap) vTemp.get(i);
try {
formBean = Class.forName(className).newInstance();
for (int j = 0; j < columnNames.size(); j++) {
recordValue = hm.get(columnNames.get(j).toString());
// 列名首字母大写，其余小写
String prop = Character.toUpperCase(columnNames.get(j).toString().
charAt(0)) +
columnNames.get(j).toString().toLowerCase().substring(1);
String mName = "set" + prop;
// 默认所有formBean方法的参数都为Object型，这样可以简化操作
Method m = formBean.getClass().getMethod(mName,
new Class[] {oTmp.getClass()});
m.invoke(formBean, new Object[] {recordValue});
}
vBean.add(formBean);
}
catch (Exception ex) {
ex.printStackTrace();
}
}
}
return vBean;
}
/**
* 根据sql语句生成ByteArray的集合，用于图像显示
* @param sql String
* @return vByteArray
*/
public Vector createByteArrays(String sql) {
Vector vByteArray = new Vector();
openDB();
if (!connected) {
return vByteArray;
}
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
while (rs.next()) {
Object o = rs.getObject(1);
if (o != null) {
if (o instanceof byte[]) {
byte[] b = (byte[]) o;
vByteArray.addElement(b);
}
}
else {
vByteArray.addElement(null);
}
}
}
catch (Exception e) {
System.out.println("Error in DBOperator.createByteArray(sql)" + e);
}
finally {
close(pstmt, rs, conn);
return vByteArray;
}
}
/**
* 根据sql语句生成ByteArray的集合，用于图像显示
* @param sql String
* @param offset int
* @return vByteArray
*/
public Vector createByteArrays(String sql, int offset) {
Vector vByteArray = new Vector();
openDB();
if (!connected) {
return vByteArray;
}
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
while (rs.next()) {
Object o = rs.getObject(offset);
if (o != null) {
if (o instanceof byte[]) {
byte[] b = (byte[]) o;
vByteArray.addElement(b);
}
}
else {
vByteArray.addElement(null);
}
}
}
catch (Exception e) {
System.out.println("Error in DBOperator.createByteArray(sql)" + e);
}
finally {
close(pstmt, rs, conn);
return vByteArray;
}
}
/**
* 在没有执行查询的情况下释放连接
*/
public void releaseConn() {
close(null, null, conn);
}
/**
* 关闭所有连接
* @param resultSet ResultSet
* @param statement Statement
* @param connection Connection
*/
private void close(Statement statement, ResultSet resultSet,
Connection connection) {
try {
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
if (connection != null) {
connection.close();
}
}
catch (Exception e) {
System.out.println("Error in DBOperator.close()" + e);
}
finally {
connected = false;
}
}
/**
* 取总页数
* @return int
*/
public int getTotalPage() {
return totalPage;
}
/**
* 取总记录数
* @return int
*/
public int getTotalRec() {
return totalRec;
}
//***********追加 康庆（08.24）根据单一表取得数据总数***********start
//取得总纪录条数
public int queryTotalRec(String sql){
clear();
openDB();
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
rs.next();
totalRec=Integer.parseInt(rs.getString(1));
return totalRec;
}
catch (Exception e)
{
System.out.print("查找企业总数发生异常~~~~~~~");
return 0;
}
}
//***********追加 郭宏亮（9.4）根据单一表取得数据总数***********end
public int queryTotal(String sql){
clear();
openDB();
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
if(rs.next())
rs.last();
totalR=rs.getRow();
return totalR;
}
catch (Exception e)
{
System.out.print("查找企业总数发生异常~~~~~~~");
return 0;
}
}
/**
* 分页显示，用于各种数据库分页显示
* @param sql String
* @param pageNum int 页号
* @param pageSize int 每页个数
* @return Vector
* @throws java.lang.Exception
*/
public Vector queryPage(int totalRec,String sql, long pageNum, int pageSize) throws
Exception {
aa = pageNum*12-12;
sql=sql + " limit " + aa + " , 12";
System.out.println("sql======"+sql);
clear();
if (pageNum < 1 || pageSize < 1) {
throw new Exception("PageNum or pageSize error !");
}
if (!connected) {
System.err.println("Not connected !");
return new Vector();
}
openDB();
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
// 获得总记录数
// System.out.println("in the dbo totalrec" + totalRec);
// if (totalRec==0){
// System.out.println("dsffsdafsadfsdfsfffffffffffffffffffffffffffffffffff");
// if (rs.next()) {
// rs.last();
// System.out.println("44444444444="+rs.getString(6));
// totalRec = rs.getRow();
// System.out.println("totalRec1111111111111111111111111=" + totalRec);
//
// }
// }
// 总记录数小于每页个数
if (totalRec < pageSize) {
rs.beforeFirst();
}
else {
// 得到总页数
totalPage = (totalRec % pageSize == 0) ? (totalRec / pageSize) :
(totalRec / pageSize) + 1;
totalPage = (totalPage == 0) ? 1 : totalPage;
// 如果定位页码大于总页数，就默认取第一页
if (pageNum > totalPage) {
pageNum = 1;
}
// 定位到指定页
// long pointer = (pageNum - 1) * pageSize;
// 定位到首页
// if (pointer == 0) {
// rs.beforeFirst();
// }
// else {
// rs.absolute( (int) pointer);
// }
}
// 构建数据
buildDataVector(rs, pageSize);
}
catch (Exception e) {
System.out.println("Error int DBOperator.getPage(sql, pageNum, pageSize)" +
e);
}
finally {
close(pstmt, rs, conn);
return vContent;
}
}
/**
* 不分页取出所有数据
* @param sql String
* @return Vector
*/
public Vector doQuery(String sql) {
openDB();
clear();
if (!connected) {
return new Vector();
}
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
buildDataVector(rs, -1);
}
catch (Exception e) {
System.out.println("Error int DBOperator.doQuery()" + e);
}
finally {
close(pstmt, rs, conn);
return vContent;
}
}
/**
* 不分页取出所有数据
* @param sql String
* @param params Vector
* @return Vector
* @throws Exception
*/
public Vector doQuery(String sql, Vector params) {
openDB();
clear();
if (!connected) {
return new Vector();
}
if (StrUtil.count(sql, "?") != params.size()) {
System.out.println("参数个数不匹配");
}
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
rs = pstmt.executeQuery();
buildDataVector(rs, -1);
}
catch (Exception e) {
throw e;
}
finally {
close(pstmt, rs, conn);
return vContent;
}
}
/**
* 清除所有历史记录
*/
private void clear() {
vContent.clear();
columnNames.clear();
}
/**
* 从结果集得到数据，并将数据处理成两种类型，一种是Vector容纳HashMap（放置Object），
* 另一种是Vector容纳Vector（放置String，用于构建TableModel）
* @param rs ResultSet
* @param pageSize int
* @throws Exception
*/
private void buildDataVector(ResultSet rs, int pageSize) throws
Exception {
//get column name
int lvColumnNu = rs.getMetaData().getColumnCount();
String[] lvColumnNames = new String[lvColumnNu];
for (int lvLoop = 0; lvLoop < lvColumnNu; lvLoop++) {
lvColumnNames[lvLoop] = rs.getMetaData().getColumnName(lvLoop + 1).
toLowerCase();
columnNames.add(lvColumnNames[lvLoop]);
}
// 不分页取出所有数据
if (pageSize == -1) {
while (rs.next()) {
HashMap hm = new HashMap();
Vector vTemp = new Vector();
for (int lvLoop = 0; lvLoop < lvColumnNames.length; lvLoop++) {
Object o = rs.getObject(lvColumnNames[lvLoop]);
if (o == null) {
o = "";
}
hm.put(lvColumnNames[lvLoop], o);
}
vContent.addElement(hm);
}
}
// 分页取出所需页记录
else {
for (int i = 0; i < pageSize; i++) {
while(rs.next()){
HashMap hm = new HashMap();
Vector vTemp = new Vector();
for (int lvLoop = 0; lvLoop < lvColumnNames.length; lvLoop++) {
Object o = rs.getObject(lvColumnNames[lvLoop]);
vTemp.add(o);
if (o == null) {
o = "";
}
hm.put(lvColumnNames[lvLoop], o);
}
vContent.addElement(hm);
}
}
}
}
/**
* 从外部获得一个连接
* @param c Connection
*/
public void setConn(Connection c) {
if (c != null) {
this.conn = c;
this.connected = true;
}
else {
this.conn = null;
this.connected = false;
}
}
public Connection getConn() {
return conn;
}
/**
* 执行批量操作
* @param sqlVector Vector
* @return boolean
*/
public boolean doBatch(Vector sqlVector) {
openDB();
boolean success = false;
if (!connected || sqlVector.isEmpty()) {
System.out.println("Database not connected or SQL vector empty!");
return success;
}
Statement stmt = null;
try {
stmt = conn.createStatement();
for (Iterator i = sqlVector.iterator(); i.hasNext(); ) {
String sql = (String) i.next();
stmt.addBatch(sql);
}
stmt.executeBatch();
success = true;
}
catch (Exception e) {
success = false;
System.out.println("Error in DBOperator.doBatch(sqlVector)" + e);
}
finally {
close(stmt, null, conn);
return success;
}
}
/**
* 执行单条更新操作
* @param sql String
* @param params Vector
* @return boolean
*/
public boolean doUpdate(String sql, Vector params) {
openDB();
boolean success = false;
if (!connected) {
return success;
}
// 如果参数个数不匹配
if (StrUtil.count(sql, "?") != params.size()) {
System.out.println("参数个数不匹配");
return success;
}
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
pstmt.executeUpdate();
}
catch (Exception e) {
System.out.println("Error in DBOperator.doUpdate(String, Vector)" + e);
}
finally {
close(pstmt, null, conn);
return success;
}
}
/**
* 执行单条更新操作
* @param sql String
* @return boolean
*/
public boolean doUpdate(String sql) {
openDB();
boolean success = false;
if (!connected) {
return success;
}
Statement stmt = null;
try {
stmt = conn.createStatement();
stmt.executeUpdate(sql);
success=true;
}
catch (Exception e) {
System.out.println("Error in DBOperator.doUpdate(sql)" + e);
}
finally {
close(stmt, null, conn);
return success;
}
}
}
扫一扫
8907
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
