本文共 25743 字,大约阅读时间需要 85 分钟。
访问几乎每一个稍微成型的程序都要用到的知识,怎么高效的访问数据库也是我们学习的一个重点,今天的任务就是总结访问数据库的方法和有关API,java访问数据库主要用的方法是JDBC,它是java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法,下面我们就具体来总结一下JDBC
一:Java访问数据库的具体步骤:
1 加载(注册)数据库
驱动加载就是把各个数据库提供的访问数据库的API加载到我们程序进来,加载JDBC驱动,并将其注册到DriverManager中,每一种数据库提供的数据库驱动不一样,加载驱动时要把jar包添加到lib文件夹下,下面看一下一些主流数据库的JDBC驱动加裁注册的代码:
//Oracle8/8i/9iO数据库(thin模式)
Class.forName(".jdbc.driver.OracleDriver").newInstance();
//Sql Server7.0/2000数据库 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//Sql Server2005/2008数据库 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//DB2数据库
Class.froName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
//数据库 Class.forName("com.mysql.jdbc.Driver").newInstance();
2 建立链接
建立数据库之间的连接是访问数据库的必要条件,就像南水北调调水一样,要想调水首先由把沟通的河流打通。建立连接对于不同数据库也是不一样的,下面看一下一些主流数据库建立数据库连接,取得Connection对象的不同方式:
//Oracle8/8i/9i数据库(thin模式)
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user="scott";
String password="tiger";
Connection conn=DriverManager.getConnection(url,user,password);
//Sql Server7.0/2000/2005/2008数据库
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs";
String user="sa";
String password="";
Connection conn=DriverManager.getConnection(url,user,password);
//DB2数据库
String url="jdbc:db2://localhost:5000/sample";
String user="amdin"
String password=-"";
Connection conn=DriverManager.getConnection(url,user,password);
//MySQL数据库
String url="jdbc:mysql://localhost:3306/testDB?user=root&password=root&useUnicode=true&characterEncoding=gb2312";
Connection conn=DriverManager.getConnection(url);
3. 执行SQL语句
数据库连接建立好之后,接下来就是一些准备工作和执行sql语句了,准备工作要做的就是建立Statement对象PreparedStatement对象,例如:
//建立Statement对象
Statement stmt=conn.createStatement();
//建立PreparedStatement对象
String sql="select * from user where userName=? and password=?";
PreparedStatement pstmt=Conn.prepareStatement(sql);
pstmt.setString(1,"admin");
pstmt.setString(2,"liubin");
做好准备工作之后就可以执行sql语句了,执行sql语句:
String sql="select * from users";
ResultSet rs=stmt.executeQuery(sql);
//执行动态SQL查询
ResultSet rs=pstmt.executeQuery();
//执行insert update delete等语句,先定义sql
stmt.executeUpdate(sql);
4 处理结果集
访问结果记录集ResultSet对象。例如:
while(rs.next)
{
out.println("你的第一个字段内容为:"+rs.getString("Name"));
out.println("你的第二个字段内容为:"+rs.getString(2));
}
5 关闭数据库
依次将ResultSet、Statement、PreparedStatement、Connection对象关 闭,释放所占用的资源.例如:
rs.close();
stmt.clost();
pstmt.close();
con.close();
二:JDBC事务
什么是事务:
首先,说说什么事务。我认为事务,就是一组操作数据库的动作集合。
事务是现代数据库理论中的核心概念之一。如果一组处理步骤或者全部发生或者一步也不执行,我们称该组处理步骤为一个事务。当所有的步骤像一个操 作一样被完整地执行,我们称该事务被提交。由于其中的一部分或多步执行失败,导致没有步骤被提交,则事务必须回滚到最初的系统状态。
事务必须服从ISO/IEC所制定的ACID原则。ACID是原子性(atomicity)、一致性(consistency)、隔离性 (isolation)和持久性(durability)的缩写。事务的原子性表示事务执行过程中的任何失败都将导致事务所做的任何修改失效。一致性表示 当事务执行失败时,所有被该事务影响的数据都应该恢复到事务执行前的状态。隔离性表示在事务执行过程中对数据的修改,在事务提交之前对其他事务不可见。持 久性表示当系统或介质发生故障时,确保已提交事务的更新不能丢失。持久性通过数据库备份和恢复来保证。
JDBC 事务是用 Connection 对象控制的。JDBC Connection 接口( java.sql.Connection )提供了两种事务模式:自动提交和手工提交。 java.sql.Connection 提供了以下控制事务的方法: public void setAutoCommit(boolean) public boolean getAutoCommit() public void commit() public void rollback() 使用 JDBC 事务界定时,您可以将多个 SQL 语句结合到一个事务中。JDBC 事务的一个缺点是事务的范围局限于一个数据库连接。一个 JDBC 事务不能跨越多个数据库。
三:java操作数据库连接池
在总结java操作数据库连接池发现一篇很好的文章,所以就不做具体总结了,直接上地址:
最后附一段比较经典的代码吧:
- import java.sql.Connection;
- import java.sql.DatabaseMetaData;
- import java.sql.Driver;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Enumeration;
- import java.util.Vector;
- public class ConnectionPool {
- private String jdbcDriver = "";
- private String dbUrl = "";
- private String dbUsername = "";
- private String dbPassword = "";
- private String testTable = "";
- private int initialConnections = 10;
- private int incrementalConnections = 5;
- private int maxConnections = 50;
- private Vector connections = null;
-
-
-
-
-
-
-
-
-
-
-
-
-
- public ConnectionPool(String jdbcDriver,String dbUrl,String dbUsername,String dbPassword) {
- this.jdbcDriver = jdbcDriver;
- this.dbUrl = dbUrl;
- this.dbUsername = dbUsername;
- this.dbPassword = dbPassword;
- }
-
-
-
-
-
-
-
- public int getInitialConnections() {
-
- return this.initialConnections;
- }
-
-
-
-
-
-
-
-
-
-
-
- public void setInitialConnections(int initialConnections) {
- this.initialConnections = initialConnections;
- }
-
-
-
-
-
-
-
- public int getIncrementalConnections() {
-
- return this.incrementalConnections;
-
- }
-
-
-
-
-
-
- public void setIncrementalConnections(int incrementalConnections) {
-
- this.incrementalConnections = incrementalConnections;
-
- }
-
-
-
-
-
-
- public int getMaxConnections() {
- return this.maxConnections;
- }
-
-
-
-
-
-
-
-
-
-
-
- public void setMaxConnections(int maxConnections) {
-
- this.maxConnections = maxConnections;
-
- }
-
-
-
-
-
-
-
- public String getTestTable() {
-
- return this.testTable;
-
- }
-
-
-
-
-
- public void setTestTable(String testTable) {
- this.testTable = testTable;
- }
-
-
-
-
-
-
-
- public synchronized void createPool() throws Exception {
-
-
-
-
-
- if (connections != null) {
-
- return;
-
- }
-
-
-
- Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
-
- DriverManager.registerDriver(driver);
-
-
-
- connections = new Vector();
-
-
-
- createConnections(this.initialConnections);
-
- System.out.println(" 数据库连接池创建成功! ");
-
- }
-
-
-
-
-
-
-
-
-
- @SuppressWarnings("unchecked")
- private void createConnections(int numConnections) throws SQLException {
-
-
-
- for (int x = 0; x < numConnections; x++) {
-
-
-
-
-
-
-
- if (this.maxConnections > 0 && this.connections.size() >= this.maxConnections) {
-
- break;
-
- }
-
-
-
-
-
- try{
-
- connections.addElement(new PooledConnection(newConnection()));
-
- }catch(SQLException e){
-
- System.out.println(" 创建数据库连接失败! "+e.getMessage());
-
- throw new SQLException();
-
- }
-
- System.out.println(" 数据库连接己创建 ......");
-
- }
- }
-
-
-
-
-
-
-
- private Connection newConnection() throws SQLException {
-
-
-
- Connection conn = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
-
-
-
-
-
-
-
- if (connections.size() == 0) {
-
- DatabaseMetaData metaData = conn.getMetaData();
-
- int driverMaxConnections = metaData.getMaxConnections();
-
-
-
-
-
-
-
-
-
-
-
- if (driverMaxConnections > 0 && this.maxConnections > driverMaxConnections) {
-
- this.maxConnections = driverMaxConnections;
-
- }
- }
- return conn;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public synchronized Connection getConnection() throws SQLException {
-
-
-
- if (connections == null) {
-
- return null;
-
- }
-
- Connection conn = getFreeConnection();
-
-
-
- while (conn == null){
-
-
-
- wait(250);
-
- conn = getFreeConnection();
-
-
-
-
-
- }
-
- return conn;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- private Connection getFreeConnection() throws SQLException {
-
-
-
- Connection conn = findFreeConnection();
-
- if (conn == null) {
-
-
-
-
-
- createConnections(incrementalConnections);
-
-
-
- conn = findFreeConnection();
-
- if (conn == null) {
-
-
-
- return null;
-
- }
-
- }
-
- return conn;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- private Connection findFreeConnection() throws SQLException {
-
- Connection conn = null;
-
- PooledConnection pConn = null;
-
-
-
- Enumeration enumerate = connections.elements();
-
-
-
- while (enumerate.hasMoreElements()) {
-
- pConn = (PooledConnection) enumerate.nextElement();
-
- if (!pConn.isBusy()) {
-
-
-
- conn = pConn.getConnection();
-
- pConn.setBusy(true);
-
-
-
- if (!testConnection(conn)) {
-
-
-
-
-
- try{
-
- conn = newConnection();
-
- }catch(SQLException e){
-
- System.out.println(" 创建数据库连接失败! "+e.getMessage());
-
- return null;
-
- }
-
- pConn.setConnection(conn);
-
- }
-
- break;
-
- }
-
- }
-
- return conn;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- private boolean testConnection(Connection conn) {
-
- try {
-
-
-
- if (testTable.equals("")) {
-
-
-
-
-
-
-
- conn.setAutoCommit(true);
-
- } else {
-
-
-
- Statement stmt = conn.createStatement();
-
- stmt.execute("select count(*) from " + testTable);
-
- }
-
- } catch (SQLException e) {
-
-
-
- closeConnection(conn);
-
- return false;
-
- }
-
-
-
- return true;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void returnConnection(Connection conn) {
-
-
-
- if (connections == null) {
-
- System.out.println(" 连接池不存在,无法返回此连接到连接池中 !");
-
- return;
-
- }
-
- PooledConnection pConn = null;
-
- Enumeration enumerate = connections.elements();
-
-
-
- while (enumerate.hasMoreElements()) {
-
- pConn = (PooledConnection) enumerate.nextElement();
-
-
-
- if (conn == pConn.getConnection()) {
-
-
-
- pConn.setBusy(false);
-
- break;
-
- }
-
- }
-
- }
-
-
-
-
-
-
-
-
-
- public synchronized void refreshConnections() throws SQLException {
-
-
-
- if (connections == null) {
-
- System.out.println(" 连接池不存在,无法刷新 !");
-
- return;
-
- }
-
- PooledConnection pConn = null;
-
- Enumeration enumerate = connections.elements();
-
- while (enumerate.hasMoreElements()) {
-
-
-
- pConn = (PooledConnection) enumerate.nextElement();
-
-
-
- if (pConn.isBusy()) {
-
- wait(5000);
-
- }
-
-
-
- closeConnection(pConn.getConnection());
-
- pConn.setConnection(newConnection());
-
- pConn.setBusy(false);
-
- }
-
- }
-
-
-
-
-
-
-
- public synchronized void closeConnectionPool() throws SQLException {
-
-
-
- if (connections == null) {
-
- System.out.println(" 连接池不存在,无法关闭 !");
-
- return;
-
- }
-
- PooledConnection pConn = null;
-
- Enumeration enumerate = connections.elements();
-
- while (enumerate.hasMoreElements()) {
-
- pConn = (PooledConnection) enumerate.nextElement();
-
-
-
- if (pConn.isBusy()) {
-
- wait(5000);
-
- }
-
-
-
- closeConnection(pConn.getConnection());
-
-
-
- connections.removeElement(pConn);
-
- }
-
-
-
- connections = null;
-
- }
-
-
-
-
-
-
-
-
-
-
-
- private void closeConnection(Connection conn) {
-
- try {
-
- conn.close();
-
- }catch (SQLException e) {
-
- System.out.println(" 关闭数据库连接出错: "+e.getMessage());
-
- }
-
- }
-
-
-
-
-
-
-
-
-
-
-
- private void wait(int mSeconds) {
-
- try {
-
- Thread.sleep(mSeconds);
-
- } catch (InterruptedException e) {
-
- }
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- class PooledConnection {
-
- Connection connection = null;
-
- boolean busy = false;
-
-
-
- public PooledConnection(Connection connection) {
-
- this.connection = connection;
-
- }
-
-
-
- public Connection getConnection() {
-
- return connection;
-
- }
-
-
-
- public void setConnection(Connection connection) {
-
- this.connection = connection;
-
- }
-
-
-
- public boolean isBusy() {
-
- return busy;
-
- }
-
-
-
- public void setBusy(boolean busy) {
-
- this.busy = busy;
-
- }
-
- }
-
- }
-
- =======================================
-
- 这个例子是根据POSTGRESQL数据库写的,
- 请用的时候根据实际的数据库调整。
-
- 调用方法如下:
-
- ① ConnectionPool connPool
- = new ConnectionPool("org.postgresql.Driver"
- ,"jdbc:postgresql://dbURI:5432/DBName"
- ,"postgre"
- ,"postgre");
-
- ② connPool .createPool();
- Connection conn = connPool .getConnection();
本文来自:曹胜欢博客专栏。转载请注明出处:/csh624366188
访问几乎每一个稍微成型的程序都要用到的知识,怎么高效的访问数据库也是我们学习的一个重点,今天的任务就是总结访问数据库的方法和有关API,java访问数据库主要用的方法是JDBC,它是java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法,下面我们就具体来总结一下JDBC
一:Java访问数据库的具体步骤:
1 加载(注册)数据库
驱动加载就是把各个数据库提供的访问数据库的API加载到我们程序进来,加载JDBC驱动,并将其注册到DriverManager中,每一种数据库提供的数据库驱动不一样,加载驱动时要把jar包添加到lib文件夹下,下面看一下一些主流数据库的JDBC驱动加裁注册的代码:
//Oracle8/8i/9iO数据库(thin模式)
Class.forName(".jdbc.driver.OracleDriver").newInstance();
//Sql Server7.0/2000数据库 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//Sql Server2005/2008数据库 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//DB2数据库
Class.froName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
//数据库 Class.forName("com.mysql.jdbc.Driver").newInstance();
2 建立链接
建立数据库之间的连接是访问数据库的必要条件,就像南水北调调水一样,要想调水首先由把沟通的河流打通。建立连接对于不同数据库也是不一样的,下面看一下一些主流数据库建立数据库连接,取得Connection对象的不同方式:
//Oracle8/8i/9i数据库(thin模式)
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user="scott";
String password="tiger";
Connection conn=DriverManager.getConnection(url,user,password);
//Sql Server7.0/2000/2005/2008数据库
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs";
String user="sa";
String password="";
Connection conn=DriverManager.getConnection(url,user,password);
//DB2数据库
String url="jdbc:db2://localhost:5000/sample";
String user="amdin"
String password=-"";
Connection conn=DriverManager.getConnection(url,user,password);
//MySQL数据库
String url="jdbc:mysql://localhost:3306/testDB?user=root&password=root&useUnicode=true&characterEncoding=gb2312";
Connection conn=DriverManager.getConnection(url);
3. 执行SQL语句
数据库连接建立好之后,接下来就是一些准备工作和执行sql语句了,准备工作要做的就是建立Statement对象PreparedStatement对象,例如:
//建立Statement对象
Statement stmt=conn.createStatement();
//建立PreparedStatement对象
String sql="select * from user where userName=? and password=?";
PreparedStatement pstmt=Conn.prepareStatement(sql);
pstmt.setString(1,"admin");
pstmt.setString(2,"liubin");
做好准备工作之后就可以执行sql语句了,执行sql语句:
String sql="select * from users";
ResultSet rs=stmt.executeQuery(sql);
//执行动态SQL查询
ResultSet rs=pstmt.executeQuery();
//执行insert update delete等语句,先定义sql
stmt.executeUpdate(sql);
4 处理结果集
访问结果记录集ResultSet对象。例如:
while(rs.next)
{
out.println("你的第一个字段内容为:"+rs.getString("Name"));
out.println("你的第二个字段内容为:"+rs.getString(2));
}
5 关闭数据库
依次将ResultSet、Statement、PreparedStatement、Connection对象关 闭,释放所占用的资源.例如:
rs.close();
stmt.clost();
pstmt.close();
con.close();
二:JDBC事务
什么是事务:
首先,说说什么事务。我认为事务,就是一组操作数据库的动作集合。
事务是现代数据库理论中的核心概念之一。如果一组处理步骤或者全部发生或者一步也不执行,我们称该组处理步骤为一个事务。当所有的步骤像一个操 作一样被完整地执行,我们称该事务被提交。由于其中的一部分或多步执行失败,导致没有步骤被提交,则事务必须回滚到最初的系统状态。
事务必须服从ISO/IEC所制定的ACID原则。ACID是原子性(atomicity)、一致性(consistency)、隔离性 (isolation)和持久性(durability)的缩写。事务的原子性表示事务执行过程中的任何失败都将导致事务所做的任何修改失效。一致性表示 当事务执行失败时,所有被该事务影响的数据都应该恢复到事务执行前的状态。隔离性表示在事务执行过程中对数据的修改,在事务提交之前对其他事务不可见。持 久性表示当系统或介质发生故障时,确保已提交事务的更新不能丢失。持久性通过数据库备份和恢复来保证。
JDBC 事务是用 Connection 对象控制的。JDBC Connection 接口( java.sql.Connection )提供了两种事务模式:自动提交和手工提交。 java.sql.Connection 提供了以下控制事务的方法: public void setAutoCommit(boolean) public boolean getAutoCommit() public void commit() public void rollback() 使用 JDBC 事务界定时,您可以将多个 SQL 语句结合到一个事务中。JDBC 事务的一个缺点是事务的范围局限于一个数据库连接。一个 JDBC 事务不能跨越多个数据库。
三:java操作数据库连接池
在总结java操作数据库连接池发现一篇很好的文章,所以就不做具体总结了,直接上地址:
最后附一段比较经典的代码吧:
- import java.sql.Connection;
- import java.sql.DatabaseMetaData;
- import java.sql.Driver;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.Enumeration;
- import java.util.Vector;
- public class ConnectionPool {
- private String jdbcDriver = "";
- private String dbUrl = "";
- private String dbUsername = "";
- private String dbPassword = "";
- private String testTable = "";
- private int initialConnections = 10;
- private int incrementalConnections = 5;
- private int maxConnections = 50;
- private Vector connections = null;
-
-
-
-
-
-
-
-
-
-
-
-
-
- public ConnectionPool(String jdbcDriver,String dbUrl,String dbUsername,String dbPassword) {
- this.jdbcDriver = jdbcDriver;
- this.dbUrl = dbUrl;
- this.dbUsername = dbUsername;
- this.dbPassword = dbPassword;
- }
-
-
-
-
-
-
-
- public int getInitialConnections() {
-
- return this.initialConnections;
- }
-
-
-
-
-
-
-
-
-
-
-
- public void setInitialConnections(int initialConnections) {
- this.initialConnections = initialConnections;
- }
-
-
-
-
-
-
-
- public int getIncrementalConnections() {
-
- return this.incrementalConnections;
-
- }
-
-
-
-
-
-
- public void setIncrementalConnections(int incrementalConnections) {
-
- this.incrementalConnections = incrementalConnections;
-
- }
-
-
-
-
-
-
- public int getMaxConnections() {
- return this.maxConnections;
- }
-
-
-
-
-
-
-
-
-
-
-
- public void setMaxConnections(int maxConnections) {
-
- this.maxConnections = maxConnections;
-
- }
-
-
-
-
-
-
-
- public String getTestTable() {
-
- return this.testTable;
-
- }
-
-
-
-
-
- public void setTestTable(String testTable) {
- this.testTable = testTable;
- }
-
-
-
-
-
-
-
- public synchronized void createPool() throws Exception {
-
-
-
-
-
- if (connections != null) {
-
- return;
-
- }
-
-
-
- Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
-
- DriverManager.registerDriver(driver);
-
-
-
- connections = new Vector();
-
-
-
- createConnections(this.initialConnections);
-
- System.out.println(" 数据库连接池创建成功! ");
-
- }
-
-
-
-
-
-
-
-
-
- @SuppressWarnings("unchecked")
- private void createConnections(int numConnections) throws SQLException {
-
-
-
- for (int x = 0; x < numConnections; x++) {
-
-
-
-
-
-
-
- if (this.maxConnections > 0 && this.connections.size() >= this.maxConnections) {
-
- break;
-
- }
-
-
-
-
-
- try{
-
- connections.addElement(new PooledConnection(newConnection()));
-
- }catch(SQLException e){
-
- System.out.println(" 创建数据库连接失败! "+e.getMessage());
-
- throw new SQLException();
-
- }
-
- System.out.println(" 数据库连接己创建 ......");
-
- }
- }
-
-
-
-
-
-
-
- private Connection newConnection() throws SQLException {
-
-
-
- Connection conn = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
-
-
-
-
-
-
-
- if (connections.size() == 0) {
-
- DatabaseMetaData metaData = conn.getMetaData();
-
- int driverMaxConnections = metaData.getMaxConnections();
-
-
-
-
-
-
-
-
-
-
-
- if (driverMaxConnections > 0 && this.maxConnections > driverMaxConnections) {
-
- this.maxConnections = driverMaxConnections;
-
- }
- }
- return conn;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public synchronized Connection getConnection() throws SQLException {
-
-
-
- if (connections == null) {
-
- return null;
-
- }
-
- Connection conn = getFreeConnection();
-
-
-
- while (conn == null){
-
-
-
- wait(250);
-
- conn = getFreeConnection();
-
-
-
-
-
- }
-
- return conn;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- private Connection getFreeConnection() throws SQLException {
-
-
-
- Connection conn = findFreeConnection();
-
- if (conn == null) {
-
-
-
-
-
- createConnections(incrementalConnections);
-
-
-
- conn = findFreeConnection();
-
- if (conn == null) {
-
-
-
- return null;
-
- }
-
- }
-
- return conn;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- private Connection findFreeConnection() throws SQLException {
-
- Connection conn = null;
-
- PooledConnection pConn = null;
-
-
-
- Enumeration enumerate = connections.elements();
-
-
-
- while (enumerate.hasMoreElements()) {
-
- pConn = (PooledConnection) enumerate.nextElement();
-
- if (!pConn.isBusy()) {
-
-
-
- conn = pConn.getConnection();
-
- pConn.setBusy(true);
-
-
-
- if (!testConnection(conn)) {
-
-
-
-
-
- try{
-
- conn = newConnection();
-
- }catch(SQLException e){
-
- System.out.println(" 创建数据库连接失败! "+e.getMessage());
-
- return null;
-
- }
-
- pConn.setConnection(conn);
-
- }
-
- break;
-
- }
-
- }
-
- return conn;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- private boolean testConnection(Connection conn) {
-
- try {
-
-
-
- if (testTable.equals("")) {
-
-
-
-
-
-
-
- conn.setAutoCommit(true);
-
- } else {
-
-
-
- Statement stmt = conn.createStatement();
-
- stmt.execute("select count(*) from " + testTable);
-
- }
-
- } catch (SQLException e) {
-
-
-
- closeConnection(conn);
-
- return false;
-
- }
-
-
-
- return true;
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void returnConnection(Connection conn) {
-
-
-
- if (connections == null) {
-
- System.out.println(" 连接池不存在,无法返回此连接到连接池中 !");
-
- return;
-
- }
-
- PooledConnection pConn = null;
-
- Enumeration enumerate = connections.elements();
-
-
-
- while (enumerate.hasMoreElements()) {
-
- pConn = (PooledConnection) enumerate.nextElement();
-
-
-
- if (conn == pConn.getConnection()) {
-
-
-
- pConn.setBusy(false);
-
- break;
-
- }
-
- }
-
- }
-
-
-
-
-
-
-
-
-
- public synchronized void refreshConnections() throws SQLException {
-
-
-
- if (connections == null) {
-
- System.out.println(" 连接池不存在,无法刷新 !");
-
- return;
-
- }
-
- PooledConnection pConn = null;
-
- Enumeration enumerate = connections.elements();
-
- while (enumerate.hasMoreElements()) {
-
-
-
- pConn = (PooledConnection) enumerate.nextElement();
-
-
-
- if (pConn.isBusy()) {
-
- wait(5000);
-
- }
-
-
-
- closeConnection(pConn.getConnection());
-
- pConn.setConnection(newConnection());
-
- pConn.setBusy(false);
-
- }
-
- }
-
-
-
-
-
-
-
- public synchronized void closeConnectionPool() throws SQLException {
-
-
-
- if (connections == null) {
-
- System.out.println(" 连接池不存在,无法关闭 !");
-
- return;
-
- }
-
- PooledConnection pConn = null;
-
- Enumeration enumerate = connections.elements();
-
- while (enumerate.hasMoreElements()) {
-
- pConn = (PooledConnection) enumerate.nextElement();
-
-
-
- if (pConn.isBusy()) {
-
- wait(5000);
-
- }
-
-
-
- closeConnection(pConn.getConnection());
-
-
-
- connections.removeElement(pConn);
-
- }
-
-
-
- connections = null;
-
- }
-
-
-
-
-
-
-
-
-
-
-
- private void closeConnection(Connection conn) {
-
- try {
-
- conn.close();
-
- }catch (SQLException e) {
-
- System.out.println(" 关闭数据库连接出错: "+e.getMessage());
-
- }
-
- }
-
-
-
-
-
-
-
-
-
-
-
- private void wait(int mSeconds) {
-
- try {
-
- Thread.sleep(mSeconds);
-
- } catch (InterruptedException e) {
-
- }
-
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- class PooledConnection {
-
- Connection connection = null;
-
- boolean busy = false;
-
-
-
- public PooledConnection(Connection connection) {
-
- this.connection = connection;
-
- }
-
-
-
- public Connection getConnection() {
-
- return connection;
-
- }
-
-
-
- public void setConnection(Connection connection) {
-
- this.connection = connection;
-
- }
-
-
-
- public boolean isBusy() {
-
- return busy;
-
- }
-
-
-
- public void setBusy(boolean busy) {
-
- this.busy = busy;
-
- }
-
- }
-
- }
-
- =======================================
-
- 这个例子是根据POSTGRESQL数据库写的,
- 请用的时候根据实际的数据库调整。
-
- 调用方法如下:
-
- ① ConnectionPool connPool
- = new ConnectionPool("org.postgresql.Driver"
- ,"jdbc:postgresql://dbURI:5432/DBName"
- ,"postgre"
- ,"postgre");
-
- ② connPool .createPool();
- Connection conn = connPool .getConnection();
本文来自:曹胜欢博客专栏。转载请注明出处:/csh624366188