有人能帮忙提供一个封装好的数据库连接的类吗？

[maodie007]

希望不要涉及到开源之类的，只是用普通的java代码以JDBC来实现，连接oracle数据库（当然如果能连更多种类也更好）
这个类还能够提供对数据库基本的 增删改 操作

我在这里以前有个人也给我段代码，但是不是很好用，总是有问题存在
代码如果，如果有高手觉得代码有不妥的地方希望指点一下，谢谢了

import   java.sql.*; 

public   class   DataStore   { 

private   static   DataStore   db   =   null; 

private   static   Connection   conn   =   null; 

private   Statement   stmt   =   null; 
/** 
  *   DataStore()构造函数主要是即在数据驱动， 
  *   然后通过conn对象连接到数据库， 
  *   最后创建对数据块的访问块stmt 
  *   
  */ 
private   DataStore()   throws   Exception   { 
if   (conn   ==   null)   { 
Class.forName("com.mysql.jdbc.Driver"); 
conn   =   DriverManager.getConnection( 
"jdbc:mysql://localhost:3306/fileData",   "root",   "root"); 
stmt   =   conn.createStatement(); 
} 
} 
/** 
  *   用來返回唯一的DataStore實例， 
  *在外部調用可以返回一個DataStore的一個實例 
  */ 
public   static   DataStore   getInstance()   { 
if   (db   ==   null)   { 
try   { 
db   =   new   DataStore(); 
}   catch   (Exception   e)   { 
conn   =   null; 
return   null; 
} 
} 
return   db; 

} 
/* 
传递一个String类型的变量，该函数就是对数据库的操作， 
主要为插入，更新，删除等操作*/ 
synchronized   public   void   execute(String   sql)   { 
try   { 
if   (stmt   !=   null) 
stmt.executeUpdate(sql); 
}   catch   (SQLException   e)   { 

} 
} 
/** 
  *   传递一个String类型的变量，该函数主要是实现对数据库中数据的查询操作 
*/ 
synchronized   public   ResultSet   read(String   sql)   { 
ResultSet   tmp   =   null; 
try   { 
if   (stmt   !=   null)   { 
tmp   =   stmt.executeQuery(sql); 
return   tmp; 
}   else   { 
return   null; 
} 
}   catch   (SQLException   e)   { 
return   null; 
} 
} 
/* 
  *   传递一个String类型的变量 
  *   */ 
synchronized   public   int   readCount(String   sql)   { 
int   nCount   =   0; 

try   { 
if   (stmt   !=   null)   { 
ResultSet   tmp   =   null; 
tmp   =   stmt.executeQuery(sql); 
if   (tmp   !=   null   &&   tmp.next())   { 
nCount   =   tmp.getInt(1); 
}   else   { 
nCount   =   0; 
} 
}   else   { 
nCount   =   0; 
} 
}   catch   (SQLException   e)   { 
nCount   =   0; 
} 

return   nCount; 
} 

synchronized   public   void   stop()   { 
try   { 
if   (conn   !=   null)   { 
conn.close(); 
stmt.close(); 
} 
}   catch   (Exception   e)   { 
/*   记录到日志   */ 
//   Log   log   =   LogFactory.getLog("mylog"); 
//   log.error("数据库关闭出错-DataStore.java"); 
}   finally   { 
conn   =   null; 
} 
} 
}

[unsalted]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConn {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/s2"; 
	private static final String user = "root"; 
	private static final String password = "root"; 
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

[火龙果被占用了]

下面这个是C3P0的连接工厂，Config类是从配置文件中读取信息的类。

需要用到C3P0的jar包：c3p0-0.x.x.x.jar
http://www.mchange.com/projects/c3p0/index.html

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class ConnectionFactory {

    private ConnectionFactory(){
    }
    
    private static ComboPooledDataSource ds = null;
    
    static {
        try {
            Logger log = Logger.getLogger("com.mchange");
            // 去掉C3P0的初始化信息
             log.setLevel(Level.WARNING);
            ds = new ComboPooledDataSource();
            ds.setDriverClass(Config.getDriverClass());
            ds.setJdbcUrl(Config.getJdbcUrl());
            ds.setUser(Config.getDatabaseUsername());
            ds.setPassword(Config.getDatabasePassword());
            ds.setMaxPoolSize(Config.getMaxPoolSize());
            ds.setMinPoolSize(Config.getMinPoolSize());
            ds.setMaxStatements(Config.getMaxStatement());
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }
    
    public static synchronized Connection getConnection() {
        Connection con = null;
        try {
            con = ds.getConnection();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return con;
    }
}

[枫叶rain]

你可以用数据库连接池来做的
import java.sql.*;
import javax.naming.*;
import javax.sql.*;

public class DatabaseConn {
private static ThreadLocal local=new ThreadLocal();


public static void close(){
try{
Connection cc=(Connection)(local.get());
if(cc!=null){
cc.close();
}
local.remove();
}catch(Exception e){
e.printStackTrace();
}
}

public static Connection getConnection() throws Exception{
Connection cc=(Connection)(local.get());
if(cc==null){
cc=getConnect();
if(cc!=null){
local.set(cc);
}
}
//return cc;
return getConnect();

}

private static synchronized Connection getConnect() throws Exception{
Context initCtx=new javax.naming.InitialContext();
Context ts=(Context)initCtx.lookupLink("java:comp/env");
DataSource ds=(DataSource)ts.lookup("jdbc/bs");
Connection cc=ds.getConnection();
return cc;
}
}

[枫叶rain]

我也给你提供一个c3p0的代码
import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.beans.PropertyVetoException;

/**
* DBPool.java User: wanghy Date: 2008-1-9
*/
public class DBPool {
private static DBPool dbPool;

private ComboPooledDataSource dataSource;

static {
dbPool = new DBPool();
}

private DBPool() {
try {
dataSource = new ComboPooledDataSource();
dataSource.setUser("root");
dataSource.setPassword("5315142");
dataSource.setJdbcUrl("jdbc:mysql://192.168.1.102:3306/hello?useUnicode=true&characterEncoding=UTF-8");
dataSource.setDriverClass("org.gjt.mm.mysql.Driver");
dataSource.setInitialPoolSize(2);
dataSource.setMinPoolSize(1);
dataSource.setMaxPoolSize(50000);
dataSource.setMaxStatements(50);
dataSource.setMaxIdleTime(60);
} catch (PropertyVetoException e) {
throw new RuntimeException(e);
}
}

public final static DBPool getInstance() {
return dbPool;
}

public final Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("无法从数据源获取连接", e);
}
}

public final void closeAll(ResultSet rs, Statement ps, Connection conn) {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

[wuxo84]

import java.sql.*;

public class DBConnection 
{
	//jdbc driver, jdbc-odbc bridge
	String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
	
	//the database to be connected
	String dbName = "jdbc:odbc:user";
	
	//database's user name
	String dbUser = "";
	
	//database's password
	String dbPass= "";
	
	//connection
	Connection connection = null;
	
	//result collection
	ResultSet resultSet = null;

	/**
	 * constructor
	 * */
	public DBConnection()
	{
		try
		{
			Class.forName( dbDriver );
		}
		catch( ClassNotFoundException classNotFoundException ) 
		{
			System.err.println( "DBConnection(): " + classNotFoundException.getMessage() );
		}
	}
 
	
	/**
	 * execute query
	 * @param sql sentence
	 * @return the result collection
	 * */
	public ResultSet executeQuery( String sql ) 
	{
		resultSet = null;
		try
		{
			connection = DriverManager.getConnection( dbName, dbUser, dbPass );
			Statement stmt = connection.createStatement();
			resultSet = stmt.executeQuery( sql );
		}
		catch( SQLException sqlException ) 
		{
			System.err.println( "executeQuery:"+sqlException.getMessage() );
		}
		return resultSet;
	}
	
	public static void main( String[] args )
	{
		
	}
}

[maodie007]

火龙果
我用你的方法取得连接会出现这样的错误，大家懂的能看下吗？谢谢了！包引入了都没问题
我新建了一类testDB

import java.sql.*;

public class testDB {
public static void main(String args[]){
	Connection conn=ConnectionFactory.getConnection();
	System.out.println(conn);
}
}

出现这样的错误
INFO [2008-02-18 11:06:38,218] (MLog.java:80) - MLog clients using log4j logging.
INFO [2008-02-18 11:06:38,515] (C3P0Registry.java:204) - Initializing c3p0-0.9.1.1 [built 15-March-2007 01:32:31; debug? true; trace: 10]
DEBUG [2008-02-18 11:06:38,593] (DynamicPooledDataSourceManagerMBean.java:236) - MBean: com.mchange.v2.c3p0:type=PooledDataSource[1hge14t7sbsle9z8bna3h|1ce3fc5] registered.
DEBUG [2008-02-18 11:06:38,828] (BasicResourcePool.java:404) - incremented pending_acquires: 1
DEBUG [2008-02-18 11:06:38,828] (BasicResourcePool.java:404) - incremented pending_acquires: 2
DEBUG [2008-02-18 11:06:38,828] (BasicResourcePool.java:404) - incremented pending_acquires: 3
DEBUG [2008-02-18 11:06:38,843] (BasicResourcePool.java:404) - incremented pending_acquires: 4
DEBUG [2008-02-18 11:06:38,843] (BasicResourcePool.java:404) - incremented pending_acquires: 5
DEBUG [2008-02-18 11:06:38,843] (BasicResourcePool.java:289) - com.mchange.v2.resourcepool.BasicResourcePool@a23610 config: [start -> 3; min -> 5; max -> 40; inc -> 3; num_acq_attempts -> 30; acq_attempt_delay -> 1000; check_idle_resources_delay -> 0; mox_resource_age -> 0; max_idle_time -> 0; excess_max_idle_time -> 0; destroy_unreturned_resc_time -> 0; expiration_enforcement_delay -> 0; break_on_acquisition_failure -> false; debug_store_checkout_exceptions -> false]
INFO [2008-02-18 11:06:38,937] (AbstractPoolBackedDataSource.java:462) - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge14t7sbsle9z8bna3h|1ce3fc5, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> oracle.jdbc.driver.OracleDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge14t7sbsle9z8bna3h|1ce3fc5, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:@192.168.1.203:1521:ora9i, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 40, maxStatements -> 140, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
DEBUG [2008-02-18 11:06:39,203] (BasicResourcePool.java:538) - acquire test -- pool size: 0; target_pool_size: 5; desired target? 1
DEBUG [2008-02-18 11:06:39,203] (BasicResourcePool.java:1291) - awaitAvailable(): [unknown]
DEBUG [2008-02-18 11:06:39,203] (BasicResourcePool.java:1644) - trace com.mchange.v2.resourcepool.BasicResourcePool@a23610 [managed: 0, unused: 0, excluded: 0]
DEBUG [2008-02-18 11:06:39,640] (BasicResourcePool.java:1831) - An exception occurred while acquiring a poolable resource. Will retry.
java.sql.SQLException: 不支持的特性
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.jdbc.driver.OracleConnection.getHoldability(OracleConnection.java:3085)
at com.mchange.v2.c3p0.impl.NewPooledConnection.<init>(NewPooledConnection.java:106)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:198)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

[zhuzhusany]

一个简单的连接Oracle父类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import java.sql.Statement;

/**
 * 数据库连接
 * @author 
 * @version 1.00 2006/05/15 作成<BR>
 * 
 * @since jdk1.4.2
 *  
 */
public class DBFactory {

    /** 创建Connection对象 */
    private Connection conn = null;

    /** 创建statement对象 */
    private Statement stmt = null;

    /** 用户名 */
    private String userID;

    /** 密码 */
    private String password;

    /** 连接字符串 */
    private String url;

    /**
     * 构造函数
     *  
     */
    public DBFactory() {

        this.userID = "**";
        this.password = "**";
        this.url = "jdbc:oracle:thin:@192.168.***.***:1521:***";

    }

    /**
     * 连接数据库

     *  
     */
    public void connect() {

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, userID, password);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            conn.setAutoCommit(true);

            System.err.println("Connect to DB: Succ!");

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Connect to DB: Fail!");

        }

    }

    /**
     * 释放连接
     *  
     */
    public void disconnect() {

        try {
            stmt.close();
            conn.close();

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

        }

    }

    /**
     * 执行SQL语句并得到结果    
     * 
     * @return 结果集

     * 
     * @param sql
     *            SQL语句
     */
    public ResultSet getData(String sql) {

        try {
            System.out.println("sql->" + sql);
            ResultSet rs = stmt.executeQuery(sql);
            return rs;

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error getData: " + sql);
            return null;

        }

    }

    /**
     * 执行SQL语句     
     * 
     * @return 执行是否成功 <br>
     *         true：成功/false：失败
     * 
     * @param sql
     *            SQL语句
     */
    public boolean executeSQL(String sql) {

        try {
            System.out.println("sql->" + sql);
            stmt.executeUpdate(sql);
            return true;

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error executeSQL: " + sql);
            return false;

        }

    }

    

}

[maodie007]

谢谢上面各位 我再试一下wanghy2008 的c3p0代码

[maodie007]

试了wanghy2008的代码一样出现 不支持的特性错误
是不是我JDBC版本低了
我的JDBC是oracle自带的JDBC oracle是9.0的

[火龙果被占用了]

你的JDK版本是多少的？
如果是1.3的话，要用c3p0-0.x.x.x-jdk1.3.jar的包。

PS：你用的是Log4j，那么我的那个代码中的
Logger log = Logger.getLogger("com.mchange");
log.setLevel(Level.WARNING);
这两行就去掉

[火龙果被占用了]

估计是JDK使用了Oracle9安装时硬行装的JDK1.3了。

[maodie007]

我这里数据库连接不是连接的本地数据库
局域网内另外一台数据库，那台数据库是oracle9i，故意也是安装时硬行装的JDK1.3了。
我自己的机器jre环境是1.6
这样的话我去下个c3p0-0.x.x.x-jdk1.3.jar的包？

[火龙果被占用了]

我的那个，你再增加一个：
ds.setMaxIdleTime();
设置一下连接在不用时到被释放的时间，单位（秒）。默认为0，即永远不释放。

[maodie007]

要解决这个办法有什么方便的方法吗？

[火龙果被占用了]

那你把环境变量改一下不就可以了，1.3太老了，改成你装的1.6就OK了啊。

[maodie007]

被连接的数据库的电脑是服务器，不能随便改

[火龙果被占用了]

哦，那你只能再去下个1.3的C3P0试试看。

如果你连接的是远程数据库肯定是要用连接池的否则太慢了，会慢死人的。

[maodie007]

刚才又试了下远程的另外台数据库，他那里的jre环境是1.5的
用 java -version看的
尝试连接那个数据库的时候还是出现一样的错误！
难道1.5的环境都不够？
难道这个错误是oracle的关系？
不是jre的原因！？

[火龙果被占用了]

那就奇怪了，只要1.4的版本都可以用的啊。

那你用用Apache DBCP试试看。

http://commons.apache.org/dbcp/