java桌面客户端，需连接数据库。我设计了一个类JDatabaseModel，使用c3p0数据库连接池。JDatabaseModel含有connect、disconnect、executeQuery、getConnection、getString……等为了彻底将数据库操作完全封装在JDatabaseModel里，打算取消getConnection的public，代之以1、getList返回一个json格式的记录集；2、设计JDataTable、JDataSet、JData。由于我没有查到现成的模块，所以目前我是用第2种方式实现的。但所谓不要造相同的轮子，所以希望知道的同学给个答案。
import com.mchange.v2.c3p0.DataSources;
import java.beans.PropertyVetoException;
import java.sql.*;public class JDatabaseModel {
private ComboPooledDataSource connPool; private String ClassName, JDBCUrl, DBUser, DBPassword; private int MinPoolSize, MaxPoolSize, IdleConnectionTestPeriod;
public JDatabaseModel() { }
public void dispose() {
disconnect();
} public void setConfig(String className, String jdbcUrl, String dbUser, String dbPassword, int minPoolSize, int maxPoolSize, int idleConnectionTestPeriod){
ClassName = className;
JDBCUrl = jdbcUrl; DBUser = dbUser;
DBPassword = dbPassword;
MinPoolSize = minPoolSize;
MaxPoolSize = maxPoolSize;
IdleConnectionTestPeriod = idleConnectionTestPeriod;
}
public boolean connect() {
boolean result = false;
if(connPool!=null){
try {
DataSources.destroy(connPool);
} catch (SQLException ex) {
} finally {
connPool = null;
}
}
connPool = new ComboPooledDataSource();
try {
connPool.setDriverClass(ClassName); //loads the jdbc driver
connPool.setJdbcUrl(JDBCUrl);
connPool.setUser(DBUser);
connPool.setPassword(DBPassword);
connPool.setMinPoolSize(MinPoolSize);
connPool.setMaxPoolSize(MaxPoolSize);
connPool.setInitialPoolSize(MinPoolSize);
connPool.setAcquireIncrement(1);
connPool.setMaxIdleTime(300);
connPool.setAutoCommitOnClose(false);
connPool.setBreakAfterAcquireFailure(false);
connPool.setIdleConnectionTestPeriod(IdleConnectionTestPeriod);
connPool.setTestConnectionOnCheckout(false);
connPool.setTestConnectionOnCheckin(false);
result = true;
} catch (PropertyVetoException ex) {
}
return result;
}
private void disconnect() { if(connPool!=null){
try {
DataSources.destroy(connPool);
} catch (SQLException ex) {
} finally {
connPool = null;
}
} }
private Connection getConnection(){
Connection conn = null;
try {
conn = connPool.getConnection();
} catch (SQLException ex) {
}
return conn;
}
public boolean executeQuery(String sqlString){
boolean result = false; if(sqlString!=null){
if(!sqlString.isEmpty()){
Connection conn = getConnection();
if(conn!=null){
Statement stat;
try {
stat = conn.createStatement();
if(stat!=null){ stat.execute(sqlString);
//测试一下能否连到数据库
result = true; try {
stat.close();
} catch (SQLException ex) { } catch (IllegalArgumentException ex) { } finally {
stat = null;
}
}
} catch (SQLException ex) {
} try {
conn.close();
} catch (SQLException ex) { } finally {
conn = null;
}
}
}
} return result;
}
public JDataTable getDataTable(String sqlString){
JDataTable dataTable = new JDataTable();
boolean over = false;
if(sqlString!=null){
if(!sqlString.isEmpty()){
Connection conn = getConnection();
if(conn!=null){
Statement stat = null;
try {
stat = conn.createStatement();
if(stat!=null){ ResultSet rs = null;
try{
rs = stat.executeQuery(sqlString);
while(rs.next()){
ResultSetMetaData rsmd = rs.getMetaData(); int colCount = rsmd.getColumnCount();
if(colCount>0){
JDataSet dataSet = new JDataSet();
for(int i = 1; i <= colCount; i++){
dataSet.add(rsmd.getColumnLabel(i), new JData(rs.getString(i)) );
}
dataTable.addDataSet(dataSet);
}
}
over = true; } catch (NullPointerException ex) { } catch (SQLException ex) { } finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException ex) { } finally {
rs = null;
}
}
} }
} catch (SQLException ex) {
} finally {
if(stat!=null){
try {
stat.close();
} catch (SQLException ex) { } catch (IllegalArgumentException ex) { } finally {
stat = null;
}
}
}
try {
conn.close();
} catch (SQLException ex) {
} finally {
conn = null;
} }
if(!over){
dataTable.clear();
}
} }
return dataTable;
}
......}
public JDataTable(){
}
public void addDataSet(JDataSet dataSet){
this.add(dataSet);
}
public JDataSet getDataSet(int i){
int count = this.size();
if(count>0){
if( i>=0 && i<=count-1){
return this.get(i);
}else{
return null;
}
}else{
return null;
}
}
}
import java.util.LinkedHashMap;public class JDataSet extends ArrayList<String> {
private LinkedHashMap<String, JData> ValueList = new LinkedHashMap();
public JDataSet(){ }
public void add(String name, JData value){
String key = name.toLowerCase();
if(this.contains(key)){
ValueList.put(key, value);
}else{
this.add(key); ValueList.put(key, value);
}
}
public JData getValue(String name){
String key = name.toLowerCase();
if(this.contains(key)){
return ValueList.get(key);
}else{
return null;
}
}
public JData getValue(int i){
int count = this.size();
if(count > 0){
if( i >=0 && i<=count - 1 ){ return ValueList.get( this.get(i) ); }else{ return null; }
}else{
return null;
}
}
}