dbaccess.java

这是jsp通用模块导航的程序原代码,里面有新闻,bbs等jsp与javabean结合的程序.

package com.wxpn.tutorial.ec.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;

import com.wxpn.tutorial.db.ConnectionPool;
import com.wxpn.tutorial.db.DB;

public class DBAccess {

	public void performInsert(String sTableName, HashMap map)
			throws SQLException {

		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = conn.createStatement();
		String sSQL1 = "INSERT INTO " + sTableName + " (";
		String sSQL2 = ") VALUES (";
		try {

			Iterator it = map.keySet().iterator();
			while (it.hasNext()) {
				String sKey = (String) it.next();
				String sValue = (String) map.get(sKey);
				sSQL1 += sKey + ",";
				sSQL2 += sValue + ",";
			}
			sSQL1 = sSQL1.substring(0, sSQL1.length() - 1);
			sSQL2 = sSQL2.substring(0, sSQL2.length() - 1);
			sSQL1 = sSQL1 + sSQL2 + " )";
			sSQL1 = new String(sSQL1.getBytes("ISO8859-1"), "GB2312");
			System.out.println(sSQL1);
			int successRows = stmt.executeUpdate(sSQL1);
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭连接，释放数据库资源：
			try {

				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}

	}

	public void performDeleteByKey(String sTableName, int id)
			throws SQLException {

		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = conn.createStatement();
		String sSQL = "DELETE FROM " + sTableName + "where orderid='" + id
				+ "'";
		try {

			stmt.executeUpdate(sSQL);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭连接，释放数据库资源：
			try {

				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}

	}

	public void performDeleteAll(String sTableName) throws SQLException {

		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = conn.createStatement();
		String sSQL = "DELETE FROM " + sTableName;
		try {

			sSQL = new String(sSQL.getBytes("ISO8859-1"), "GB2312");
			stmt.executeUpdate(sSQL);
			stmt.close();
			connPool.freeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭连接，释放数据库资源：
			try {

				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAll(String sTableName) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from  " + sTableName;

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection resultList = new ArrayList();
			rs = stmt.executeQuery(sql);
			TravValueMapBean tempBean = null;
			while (rs.next()) {
				HashMap nsDataObj = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					nsDataObj.put(rsmd.getColumnName(i), StringUtil
							.nullToSpace(rs.getString(i)).replaceAll("亅",
									"@@僟僢僔儏@@"));
				}
				tempBean = new TravValueMapBean(nsDataObj);
				resultList.add(tempBean);

			}
			rs.close();
			stmt.close();
			return resultList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAllByClause(String sTableName, String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from  " + sTableName + " " + clause;

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection resultList = new ArrayList();
			TravValueMapBean tempBean = null;
			while (rs.next()) {
				HashMap nsDataObj = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					nsDataObj.put(rsmd.getColumnName(i), StringUtil
							.nullToSpace(rs.getString(i)).replaceAll("亅",
									"@@僟僢僔儏@@"));
				}
				tempBean = new TravValueMapBean(nsDataObj);
				resultList.add(tempBean);
			}
			rs.close();
			stmt.close();
			return resultList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public String getAddSeqNum(String tablekey, String sTableName)
			throws SQLException {

		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet result = null;
		String maxid = "1";
		String sqlStr = "select maxid = case num when 0 then 1 else maxid+1 end from (SELECT max("
				+ tablekey
				+ ") maxid ,count(*) num FROM "
				+ sTableName + " )T";

		System.out.println(sqlStr);

		try {
			result = stmt.executeQuery(sqlStr);

			// if(result.getString("maxid")!=null){
			result.next();
			maxid = StringUtil
					.nullToSpace(result.getString("maxid").toString());
			// }else{
			// sqlStr="select max(planeorderid)+1 from "+sTableName;
			// rs=stmt.executeQuery(sqlStr);

			// }

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			// 关闭连接，释放数据库资源：
			try {

				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
		return maxid;

	}

	public int getCount(String sTableName, String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：

			String sql = "select count(*) from  " + sTableName + " " + clause;
			// sql = new String(sql.getBytes("gb2312"), "ISO8859-1");
			rs = stmt.executeQuery(sql);
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			System.out.println(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAll(String sTableName, int pagesize, int page,
			String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from  " + sTableName + " " + clause;
			// + " limit " + (page - 1) * pagesize + ","
			// + pagesize;
			// 执行sql语句，返回一个记录集到rs：
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			Collection resultList = new ArrayList();
			TravValueMapBean tempBean = null;
			while (rs.next()) {
				HashMap nsDataObj = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					nsDataObj.put(rsmd.getColumnName(i), StringUtil
							.nullToSpace(rs.getString(i)).replaceAll("亅",
									"@@僟僢僔儏@@"));
				}
				tempBean = new TravValueMapBean(nsDataObj);
				resultList.add(tempBean);
			}
			rs.close();
			stmt.close();
			return resultList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public ArrayList executeQueryGetBeanList(String sql) {

		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {

			// System.out.println(sql);
			ArrayList resultList = new ArrayList();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				HashMap nsDataObj = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					nsDataObj.put(rsmd.getColumnName(i), StringUtil
							.nullToSpace(rs.getString(i)).replaceAll("亅",
									"@@僟僢僔儏@@"));
				}
				TravValueMapBean tempBean = new TravValueMapBean(nsDataObj);
				resultList.add(tempBean);
			}
			rs.close();
			stmt.close();
			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;

		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}
}