提供了数据库在c3p0基础上进行数据库二次开发，在以后的过程中会进行完善。类作用如下：
1 ConnectionManager：数据库链接池管理
2 DataSetBean：数据库操作Bean
3 Cursor：数据库结果游标
4 DbObject：数据库配置基本类
5 DbXmlParse：配置文件转换类
6 c3p0-service-complex-db.xml：数据库配置文件
8 QueryPage:分页的实现
[@more@]1 ConnectionManager.java
public class ConnectionManager {
private static ConnectionManager instance = null;
private Map dataSourceMap = null;
private DbXmlParse xmlInstance = null;
/**
* 根据数据库数据源字符串直接获取链接
* @param sourceKey
* @return
*/
public Connection getConnection(String sourceKey) throws Exception{
DataSource ds_pooled= (DataSource) dataSourceMap.get(sourceKey);
if(dataSourceMap.get(sourceKey) == null){
DbObject db = xmlInstance.getDbObject(sourceKey);
ds_pooled = createDataSource(db);
dataSourceMap.put(db.getKey(),ds_pooled);
}
return ds_pooled.getConnection();
}
/**
* 初始化
*/
private void load(){
xmlInstance = DbXmlParse.getInstance();
dataSourceMap = new HashMap();
}
/**
* 根据数据库对象创建一个数据源
* @param db
* @return
*/
private DataSource createDataSource(DbObject db){
try {
Class.forName(db.getClassName()).newInstance();
} catch (Exception ex) {
// TODO 自动生成 catch 块
ex.printStackTrace();
}
DataSource ds_unpooled = null;
DataSource ds_pooled = null;
try {
ds_unpooled = DataSources.unpooledDataSource(db.getDbURL(), db.getUserName(),db.getPassWord());
ds_pooled = DataSources.pooledDataSource( ds_unpooled,db.getKey());
} catch (SQLException e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
}
return ds_pooled;
}
/**
* 释放数据库相关对象
* @param conn
* @param ps
* @param rs
*/
public void releaseConnection(Connection conn,PreparedStatement ps,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
}
rs = null;
}
if(ps != null){
try {
ps.close();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
ps = null;
}
if(conn != null){
try{
conn.close();
}catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
ps = null;
}
}
/**
* 获取单例模式
* @return
*/
public synchronized static ConnectionManager getInstance(){
if(instance == null){
instance = new ConnectionManager();
instance.load();
}
return instance;
}
/**
* 是否所有的数据源链接池
*
*/
public void destroyAllDataSource(){
if(dataSourceMap == null){
return;
}
Set dsSet = dataSourceMap.keySet();
Iterator it = dsSet.iterator();
while(it.hasNext()){
destroyDataSource((DataSource)it.next());
}
dataSourceMap.clear();
dataSourceMap = null;
}
/**
* 释放单个数据链接池
* @param ds_pooled
*/
public void destroyDataSource(DataSource ds_pooled){
try {
DataSources.destroy(ds_pooled);
} catch (SQLException e) {
}finally{
ds_pooled = null;
}
}
public static void main(String[] args){
DataSetBean bean = new DataSetBean("ORACL/DBNAME");
String sql = "SELECT * FROM TABLE where ID = ?";
try {
Object[][] params = new Object[1][2];
params[0][0] = new Integer(Types.VARCHAR);
params[0][1] = "test";
Cursor cursor = bean.executeQuery(sql,params);
Map map = cursor.getNext();
while(map != null){
System.out.println(map.get("COLUMNS"));
map = cursor.getNext();
}
} catch (Exception e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
}
bean = null;
}
}
2 DataSetBean
public class DataSetBean {
// 数据库链接标识
private String DataSourceKey;
private ConnectionManager dbInstance = null;
/**
* 构造函数
* @param DataSourceKey
*/
public DataSetBean(String DataSourceKey) {
this.DataSourceKey = DataSourceKey;
dbInstance = ConnectionManager.getInstance();
}
/**
* 执行脚本，执行单个的数据更新语句
* @param strSQL
* @param params
* @return
* @throws Exception
*/
public int executeUpdate(String strSQL, Object[][] params) throws Exception {
int retValue = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = dbInstance.getConnection(DataSourceKey);
pstmt = conn.prepareStatement(strSQL);
DBCommonManager.fillPreparedStatement(pstmt, params);
retValue = pstmt.executeUpdate();
} catch (SQLException sqle) {
//log.error("DataSetBean:executeQuery is error: " + strSQL);
//log.error(sqle);
throw sqle;
} finally {
dbInstance.releaseConnection(conn, pstmt, null);
}
return retValue;
}
/**
* 执行带参数的sql查询语句
* @param strSQL
* @param params
* @return
* @throws Exception
*/
public Cursor executeQuery(String strSQL, Object[][] params)
throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData metadata;
HashMap result = null;
String value;
Cursor cursor = new Cursor();
try {
conn = dbInstance.getConnection(DataSourceKey);
pstmt = conn.prepareStatement(strSQL);
DBCommonManager.fillPreparedStatement(pstmt, params);
rs = pstmt.executeQuery();
while (rs.next()) {
result = new HashMap();
metadata = rs.getMetaData();
for (int i = 1; i <= metadata.getColumnCount(); i++) {
value = rs.getString(metadata.getColumnName(i));
if (value == null) {
value = "";
}
result.put(metadata.getColumnName(i).toUpperCase(), value
.trim());
}
cursor.add(result);
}
} catch (SQLException sqle) {
//log.error("DataSetBean:executeQuery is error: " + strSQL);
//log.error(sqle);
throw sqle;
} finally {
dbInstance.releaseConnection(conn, pstmt, rs);
}
return cursor;
}
/**
* 获取指定范围的数据记录集
* @param strSQL
* @param offset,指定开始的记录数
* @param pagelen，每次记录的条数
* @return
* @throws Exception
*/
public Cursor executeQuery(String strSQL,Object[][] params,int offset,int pagelen) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData metadata;
HashMap result = null;
String value;
Cursor cursor = new Cursor();
try {
conn = dbInstance.getConnection(DataSourceKey);
pstmt = conn.prepareStatement(strSQL);
DBCommonManager.fillPreparedStatement(pstmt, params);
rs = pstmt.executeQuery();
int count = 0;
int tempPageLen = pagelen;
while (rs.next()) {
result = new HashMap();
count ++;
if(count < offset){
continue;
}
if(tempPageLen == 0){
break;
}
metadata = rs.getMetaData();
for (int i = 1; i <= metadata.getColumnCount(); i++) {
value = rs.getString(metadata.getColumnName(i));
if (value == null) {
value = "";
}
result.put(metadata.getColumnName(i).toUpperCase(), value
.trim());
}
cursor.add(result);
tempPageLen --;
}
} catch (SQLException sqle) {
//log.error("DataSetBean:executeQuery is error: " + strSQL);
//log.error(sqle);
throw sqle;
} finally {
dbInstance.releaseConnection(conn, pstmt, rs);
}
return cursor;
}
/**
* 获取指定条件的某一条记录
* @param strSQL
* @param params
* @return
* @throws Exception
*/
public HashMap getRecord(String strSQL,Object[][] params)throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSetMetaData metadata;
String value;
HashMap result = new HashMap();
try {
conn = dbInstance.getConnection(DataSourceKey);
pstmt = conn.prepareStatement(strSQL);
DBCommonManager.fillPreparedStatement(pstmt, params);
rs = pstmt.executeQuery();
while (rs.next()) {
metadata = rs.getMetaData();
for (int i = 1; i <= metadata.getColumnCount(); i++) {
value = rs.getString(metadata.getColumnName(i));
if (value == null) {
value = "";
}
result.put(metadata.getColumnName(i).toUpperCase(), value
.trim());
}
}
} catch (SQLException sqle) {
//log.error("DataSetBean:executeQuery is error: " + strSQL);
//log.error(sqle);
throw sqle;
} finally {
dbInstance.releaseConnection(conn, pstmt, rs);
}
return result;
}
}
3 Cursor
public class Cursor {
private Vector rows = null;
// 排序类型
/**
* 字符串类型数据
*/
public final int STRING_TYPE = 0;
/**
* 长整形类型数据
*/
public final int LONG_TYPE = 2;
/**
* 双精度类型数据
*/
public final int DOUBLE_TYPE = 3;
// 操作类型
/**
* 比较方式：大于
*/
public final int OPER_MORE = 1;
/**
* 比较方式：小于
*/
public final int OPER_LESS = 2;
private int currentRow;
/**
* 缺省构造函数，初试化Vector
*
*/
public Cursor() {
rows = new Vector();
currentRow = -1;
}
/**
* 在游标中加入一条记录
*
* @param map
* 实现Map接口的记录对象
*/
public void add(Map map) {
rows.add(map);
}
/**
* 从游标中取得下一条记录
*
* @return 返回一个实现Map接口的对象,如果超出游标范围返回null
*/
public Map getNext() {
if (currentRow < rows.size() - 1) {
currentRow++;
return (Map) rows.elementAt(currentRow);
} else
return null;
}
/**
* 从游标中取得上一条记录
*
* @return 返回一个实现Map接口的对象,如果超出游标范围返回null
*/
public Map getPrior() {
if (currentRow > 0) {
currentRow--;
return (Map) rows.elementAt(currentRow);
} else
return null;
}
/**
* 从游标中取得以索引号定义的记录
*
* @param index
* 记录索引号，从0开始。整形值
* @return 返回一个实现Map接口的对象,如果超出游标范围返回null
*/
public Map getRecord(int index) {
if ((index >= 0) && (index < rows.size()))
return (Map) rows.elementAt(index);
else
return null;
}
/**
* 游标复位
*
*/
public void Reset() {
currentRow = -1;
}
/**
* 获取游标大小
*
* @return 返回整形值
*/
public int getRecordSize() {
return rows.size();
}
/**
* 对字符字段field进行分组，存放Cursor数组中。
* 在分组前必须对Cursor进行分组字段的排序，有两种排序选择：
*
*- SQL语句中用 ORDER BY，即存放到Cursor数据已经是排过序的
* - 调用Cursor相关排序方法：sort 来进行排序
*
*
* @param field
* 分组字段名称
* @return Cursor数组，每一组一个Cursor列表
*/
public Cursor[] group(String field) {
Reset();
ArrayList list = new ArrayList();
Cursor c = null;
Map m;
String v1 = null, v2;
for (int i = 0; i < rows.size(); i++) {
m = getRecord(i);
v2 = (String) m.get(field);
if (v1 == null || !v1.equals(v2)) {
c = new Cursor();
} else {
list.add(c);
c = null;
}
c.add(m);
v1 = v2;
}
if (c != null)
list.add(c);
c = null;
return (Cursor[]) list.toArray();
}
public Cursor groupBySum(String field1, String field2, String aliasName, int data) {
Cursor[] arr = group(field1);
Cursor c = new Cursor();
HashMap m;
for (int i = 0; i < arr.length; i++) {
m = arr[i].sum(field2, aliasName, data);
m.put(field1, (String) arr[i].getRecord(0).get(field1));
c.add(m);
}
return c;
}
private boolean search(Vector vector, String field, String value) {
Map h;
String fv;
for (int i = 0; i < vector.size(); i++) {
h = getRecord(i);
fv = (String) h.get(field);
if (fv.equals(value))
return true;
}
return false;
}
public void group(String field1, String field2) {
Vector tmp = new Vector();
Map h;
long v;
String fv1, fv2;
for (int i = 0; i < rows.size(); i++) {
h = getRecord(i);
v = Long.parseLong((String) h.get(field2));
fv1 = (String) h.get(field1);
if (!search(tmp, field1, fv1)) {
for (int j = i + 1; j < rows.size(); j++) {
h = getRecord(j);
fv2 = (String) h.get(field1);
if (fv1.equals(fv2)) {
v += Long.parseLong((String) h.get(field2));
}
}
Hashtable tmph = new Hashtable();
tmph.put(field1, fv1);
tmph.put(field2, new Long(v).toString());
tmp.add(tmph);
}
}
rows = tmp;
tmp = null;
}
public Cursor groupByAvg(String field1, String field2, String aliasName, int data) {
Cursor[] arr = group(field1);
Cursor c = new Cursor();
HashMap m;
for (int i = 0; i < arr.length; i++) {
m = arr[i].avg(field2, aliasName, data);
m.put(field1, (String) arr[i].getRecord(0).get(field1));
c.add(m);
}
return c;
}
public HashMap sum(String field, String aliasName, int data) {
HashMap m = new HashMap();
switch (data) {
case LONG_TYPE:
m.put(aliasName, Long.toString(longSum(field)));
break;
case DOUBLE_TYPE:
m.put(aliasName, Double.toString(doubleSum(field)));
}
return m;
}
public HashMap avg(String field, String aliasName, int data) {
HashMap m = new HashMap();
switch (data) {
case LONG_TYPE:
m.put(aliasName, Long.toString(longSum(field) / rows.size()));
break;
case DOUBLE_TYPE:
m.put(aliasName, Double.toString(doubleSum(field) / rows.size()));
}
return m;
}
private long longSum(String field) {
Map m;
long total = 0L;
for (int i = 0; i < rows.size(); i++) {
m = getRecord(i);
total += Long.parseLong((String) m.get(field));
}
return total;
}
private double doubleSum(String field) {
Map m;
double total = 0.0D;
for (int i = 0; i < rows.size(); i++) {
m = getRecord(i);
total += Double.parseDouble((String) m.get(field));
}
return total;
}
/**
* 对长整形的字段进行DESC排序
*
* @param field
* 排序字段名称
*/
public void sort(String field) {
sort(field, "desc", LONG_TYPE);
}
/**
* 对长整形的字段进行排序，排序方式由seq决定
*
* @param field
* 排序字段名称
* @param seq
* 排序方式
*/
public void sort(String field, String seq) {
sort(field, seq, LONG_TYPE);
}
/**
* 对任意字段进行排序，排序字段类型由data决定,排序方式由seq决定
*
* @param field
* 排序字段名称
* @param seq
* 排序方式
* @param data
* 排序字段类型
*/
public void sort(String field, String seq, int data) {
Map m1, m2;
String v1, v2;
for (int i = 0; i < rows.size(); i++) {
for (int j = i + 1; j < rows.size(); j++) {
m1 = getRecord(i);
v1 = (String) m1.get(field);
m2 = getRecord(j);
v2 = (String) m2.get(field);
if (seq.equals("asc")) {
if (parseValue(v1, v2, data, OPER_MORE)) {
rows.set(i, m2);
rows.set(j, m1);
}
} else if (seq.equals("desc")) {
if (parseValue(v1, v2, data, OPER_LESS)) {
rows.set(i, m2);
rows.set(j, m1);
}
} else {
if (parseValue(v1, v2, data, OPER_LESS)) {
rows.set(i, m2);
rows.set(j, m1);
}
}
}
}
}
private boolean parseValue(String v1, String v2, int data, int oper) {
boolean re = false;
switch (data) {
case STRING_TYPE:
if (oper == OPER_MORE)
re = v1.compareTo(v2) > 0;
else
re = v1.compareTo(v2) < 0;
break;
case LONG_TYPE:
if (oper == OPER_MORE)
re = Long.parseLong(v1) > Long.parseLong(v2);
else
re = Long.parseLong(v1) < Long.parseLong(v2);
break;
case DOUBLE_TYPE:
if (oper == OPER_MORE)
re = Double.parseDouble(v1) > Double.parseDouble(v2);
else
re = Double.parseDouble(v1) < Double.parseDouble(v2);
break;
}
return re;
}
}
4 DbObject
public class DbObject {
// 数据源的主关键字
private String key;
// 数据库jdbc名称
private String className;
// 数据库链接字符串
private String dbURL;
// 用户名
private String userName;
// 密码
private String passWord;
/**
* @return 返回 className。
*/
public String getClassName() {
return className;
}
/**
* @param className 要设置的 className。
*/
public void setClassName(String className) {
this.className = className;
}
/**
* @return 返回 dbURL。
*/
public String getDbURL() {
return dbURL;
}
/**
* @param dbURL 要设置的 dbURL。
*/
public void setDbURL(String dbURL) {
this.dbURL = dbURL;
}
/**
* @return 返回 key。
*/
public String getKey() {
return key;
}
/**
* @param key 要设置的 key。
*/
public void setKey(String key) {
this.key = key;
}
/**
* @return 返回 passWord。
*/
public String getPassWord() {
return passWord;
}
/**
* @param passWord 要设置的 passWord。
*/
public void setPassWord(String passWord) {
this.passWord = passWord;
}
/**
* @return 返回 userName。
*/
public String getUserName() {
return userName;
}
/**
* @param userName 要设置的 userName。
*/
public void setUserName(String userName) {
this.userName = userName;
}
}
5:DbXmlParse
// 配置文件路径
private String configFile = "c3p0-service-complex-db.xml";
// 实例名称
private static DbXmlParse instance = null;
// 存放所有数据库信息的Map，key为主键，DbObject为值
private Map dbMap = null;
/**
* 根据主键返回数据库对象
*/
public DbObject getDbObject(String sourceKey) {
if(dbMap == null){
dbMap = new HashMap();
Document doc = null;
try {
File xmlFile = new File("./", configFile);
SAXBuilder saxBuilder = new org.jdom.input.SAXBuilder(false);
doc = saxBuilder.build(xmlFile);
} catch (Exception ex) {
}
Element rootElement = doc.getRootElement();
// 其次获取每个配置文件中的数据源
List dbSourceList = rootElement.getChildren("db-source");
int num = dbSourceList.size();
for(int i=0;i DbObject db = new DbObject();
Element ele = (Element) dbSourceList.get(i);
db.setKey(ele.getAttributeValue("key"));
db.setClassName(ele.getChild("className").getTextTrim());
db.setDbURL(ele.getChild("dbURL").getTextTrim());
db.setUserName(ele.getChild("userName").getTextTrim());
db.setPassWord(ele.getChild("passWord").getTextTrim());
dbMap.put(db.getKey(),db);
}
}
return (DbObject)dbMap.get(sourceKey);
}
public synchronized static DbXmlParse getInstance(){
if(instance == null){
instance = new DbXmlParse();
}
return instance;
}
}
6 c3p0-service-complex-db.xml
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@192.168.1.2:1521:oracle
system
system
com.microsoft.jdbc.sqlserver.SQLServerDriver
jdbc:microsoft:sqlserver://192.168.1.2:1433;DatabaseName=master
sa
7 c3p0-config.xml
con_test
30000
30
5
30
15
5
200
10
1
0
5
5
5
15
0
0
10
3
3
5
50
5
5
5
15
0
0
10
3
3
5
50
8 QueryPage
public class QueryPage {
private int offset;
private int MaxLine;
private int total;
private int curPage;
private int totalPage;
private String query;
private DataSetBean dataSet;
/**
* 设置数据分页条件
*
* @param query
* 获取数据SQL语句
* @param start
* 每页数据起始点
* @param len
* 每页数据条数
*/
public void initPage(String query, int start, int len,String DataSourceKey) throws Exception{
offset = start;
MaxLine = len;
this.query = query;
dataSet = new DataSetBean(DataSourceKey);
total = getTotal();
totalPage = (int) Math.ceil((double) total / MaxLine);
curPage = (int) Math.floor((double) offset / MaxLine + 1);
}
private int getTotal() throws Exception{
String query_pos;
int begin, end, begin1;
if (query.toUpperCase().indexOf("UNION ALL") == -1) {
begin = query.toUpperCase().indexOf(" FROM ");
end = query.toUpperCase().indexOf(" GROUP ");
if (end == -1)
end = query.toUpperCase().indexOf("ORDER");
if (end == -1)
end = query.length();
query_pos = query.substring(begin, end).trim();
begin1 = query.toUpperCase().indexOf(" DISTINCT ");
String strSQL = "select count(1) As TOTAL "
+ query_pos;
HashMap fields = dataSet.getRecord(strSQL,null);
if (fields != null) {
System.out.println("Record Count: " + fields.get("TOTAL"));
return (Integer.parseInt((String) fields.get("TOTAL")));
} else
return 0;
} else {
Cursor cursor = dataSet.executeQuery(this.query,null);
return (cursor.getRecordSize());
}
}
/**
* 生成分页导航栏
*
* @return 返回导航栏HTML代码
*/
public String getPageBar() {
String strHTML = "";
String strColor = "#535353";
int first, next, prev, last;
first = 1;
next = offset + MaxLine;
prev = offset - MaxLine;
last = (totalPage - 1) * MaxLine + 1;
if (offset > MaxLine)
strHTML += "首页 ";
else
strHTML += "首页 ";
if (prev > 0)
strHTML += "前页 ";
else
strHTML += "前页 ";
if (next <= total)
strHTML += "后页 ";
else
strHTML += "后页 ";
if (totalPage != 0 && curPage < totalPage)
strHTML += "尾页 ";
else
strHTML += "尾页 ";
strHTML += " 页次：" + curPage + "/" + totalPage + "页 ";
strHTML += MaxLine + "条/页 共" + total + "条";
return strHTML;
}
/**
* 生成分页导航栏
*
* @param search
* 获取数据的查询条件
* @return 返回导航栏HTML代码
*/
public String getPageBar(String search) {
String strHTML = "";
String strColor = "#535353";
int first, next, prev, last;
first = 1;
next = offset + MaxLine;
prev = offset - MaxLine;
last = (totalPage - 1) * MaxLine + 1;
if (offset > MaxLine)
strHTML += " + "">首页 ";
else
strHTML += "首页 ";
if (prev > 0)
strHTML += "前页 ";
else
strHTML += "前页 ";
if (next <= total)
strHTML += "后页 ";
else
strHTML += "后页 ";
if (totalPage != 0 && curPage < totalPage)
strHTML += "尾页 ";
else
strHTML += "尾页 ";
strHTML += " 页次：" + curPage + "/" + totalPage + "页 ";
strHTML += MaxLine + "条/页 共" + total + "条";
return strHTML;
}
}
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/49731/viewspace-937578/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/49731/viewspace-937578/
1379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
