dbwrapper.java

一个完整的代码管理系统的源代码

/*
 * Copyright (c) 2001-2003 Tanming1003 Inc.
 * All rights reserved.
 * 
 * tanming1003<[e-mail]>
 * 
 * 
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions 
 * are met:
 * 
 * Redistributions of source code must retain the above copyright 
 * notice, this list of conditions and the following disclaimer. 
 * Redistributions in binary form must reproduce the above copyright 
 * notice, this list of conditions and the following disclaimer in the 
 * documentation and/or other materials provided with the distribution. 
 * Neither the name of tanming1003 nor the names of its contributors 
 * may be used to endorse or promote products derived from this 
 * software without specific prior written permission. 
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
 * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE 
 * REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 
 * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING 
 * IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 * POSSIBILITY OF SUCH DAMAGE.
 */
package hunnu.edu.cn.product.common.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import hunnu.edu.cn.product.common.db.pool.DBPoolManager;
import hunnu.edu.cn.product.log.LogFactory;
import hunnu.edu.cn.product.log.LogLog;

/**
 * @author tanming1003
 * @date 2008-10-10
 * @time 下午14:39:56
 * @project_name Product
 * @package_name hunnu.edu.cn.product.common.db
 * @file_name    DBUtil.java
 * @version      1.0
 */

public class DBWrapper implements DBProcess
{
    private LogLog log=LogFactory.newInstance().getLog(this.getClass());
	/**
	 * 将数据库连接返还到数据库连接池
	 * 注意与closeConnection方法区分
	 * @param connection
	 * 
	 */
    public void closeConnection(Connection connection) throws SQLException
	{
		if(connection!=null)
		{
			    log.info("开始归还一个连接到数据库连接池.......");
			    DBPoolManager manager=new DBPoolManager();
			    manager.returnConnection(connection);
				log.info("成功归还一个连接到数据库连接池.......");
		}
		return ;
	}
	
	/**
	 *  删除数据库一条记录
	 * @param sql 数据库删除语句
	 *
	 */
	public int deleteRecord(String sql) throws SQLException
	{
	     return executeUpdate(sql);
	}
    
	/**
	 * @param sql 数据库更新语句
	 * @return List 将结果集以List形式存储。List中存储Map，以字段-值的方式存储查询结果。
	 */
	public List<Map<String, Object>> executeQuery(String sql) throws SQLException
	{
		Connection connection=this.getConnection();
		List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
	    Statement statement=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
	    try
	    {
	    	ResultSet rs=statement.executeQuery(sql);
	    	ResultSetMetaData rsmd=rs.getMetaData();
	    	while(rs.next())
	    	{
	    		Map<String, Object> map=new HashMap<String, Object>();
	    		for(int i=0;i<rsmd.getColumnCount();i++)
	    		{
	    			map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));
	    		}
	    		list.add(map);
	    	}
	    }
	    catch(SQLException e)
	    {
	    	log.debug("SQLSQLException:", e);
	    	e.printStackTrace();
	    }
	    finally
		{
	    	statement.close();
	    	closeConnection(connection);
		}
		return list;
	}
	
    /**
     *@param sql 数据库更新语句
     *@return int型整数 表示成功更新的记录数
     */
	public int executeUpdate(String sql) throws SQLException
	{
	    int count=0;
	    Connection connection=this.getConnection();
	    Statement statement=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    try
	    {
	    	count=statement.executeUpdate(sql);
	    	connection.commit();
	    	connection.setAutoCommit(true);
	    }
	    catch(SQLException e)
	    {
	    	log.debug("SQLSQLException :", e);
	    	connection.rollback();
	    }
	    finally
	    {
	    	closeStatement(statement);
	    	closeConnection(connection);
	    }
		return count;
	}
    
	/**
	 * 从数据库连接池中得到一个连接
	 * @param null
	 */
	public Connection getConnection() throws SQLException
	{
		Connection con;
		DBPoolManager manager=new DBPoolManager();
		con=manager.getConnection();
		return con;
	}
	
    /**
     *连接数据库，使用driver，url，name，password参数。
     *@param driver 数据库驱动 url 数据库连接地址 name用户名 password 用户密码 
     */
	public Connection getConnection(String driver, String url, String name,
			String password) throws SQLException
	{
		try
		{
			Class.forName(driver);
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		Connection conn=DriverManager.getConnection(url,name,password);
		return conn;
	}
    
	/**
	 * 插入一条记录
	 * @param sql 数据库插入语句
	 * 
	 */
	public int insertRecord(String sql) throws SQLException
	{
	     return executeUpdate(sql);	
	}
    
	/**
	 * 查询一条记录
	 * @param sql  数据库查询语句
	 * 
	 */
	public List<?> queryRecord(String sql) throws SQLException
	{
		return executeQuery(sql);
	}
    
	/**
	 * 更新一条记录
	 * @param sql 数据库更新语句
	 * 
	 */
	public int updateRecord(String sql) throws SQLException
	{
	     return executeUpdate(sql);	
	}
    
	/**
	 * @param sql 数据库更新语句 param 使用prepareStatement要设置的参数对象
	 */
	public int executeUpdate(String sql, Object[] param) throws SQLException
	{
		int count=0;
		Connection connection=this.getConnection();
		PreparedStatement ps=connection.prepareStatement(sql);
		try
		{
			for(int i=0;i<param.length;i++)
			{
				ps.setObject(i+1,param[i]);
			}
			count=ps.executeUpdate();
			connection.commit();
		}
		catch(SQLException e)
		{
		    log.debug("SQLSQLException :", e);
			connection.rollback();
		}
		finally
		{
			closeStatement(ps);
			closeConnection(connection);
		}
		return count;
	}
    
	/**
	 * 关闭数据库连接句柄
	 * @param  statement 数据库句柄
	 * 
	 */
	public void closeStatement(Statement statement) throws SQLException
	{
	    if(statement!=null)
	    {
	    	try
	    	{
	    		statement.close();
	    	}
	    	catch(SQLException e)
	    	{
	    		log.debug("Can't close the statement.", e);
	    		e.printStackTrace();
	    	}
	    }
	    return;
	}
    
	/**
	 * 执行语句
	 * @param sql 数据库执行语句 param 要传入的参数对象
	 * 
	 */
	public boolean execute(String sql, Object[] param) throws SQLException
	{
		boolean count=false;
		Connection connection=this.getConnection();
		PreparedStatement ps=connection.prepareStatement(sql);
		connection.setAutoCommit(false);
		try
		{
			for(int i=0;i<param.length;i++)
			{
				ps.setObject(i+1,param[i]);
			}
			count=ps.execute();
			connection.commit();
		}
		catch(SQLException e)
		{
		    log.debug("Can't execute the sql .", e);
			connection.rollback();
		}
		finally
		{
			this.closeStatement(ps);
			closeConnection(connection);
		}
		return count;
	}
    
	/**
	 * 数据库查询操作
	 * @param sql 数据库查询语句 param 要设置的参数对象
	 * 
	 */
	public List<Map<String, Object>> executeQuery(String sql, Object[] param) throws SQLException
	{
		Connection connection=this.getConnection();
		List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
		PreparedStatement ps=connection.prepareStatement(sql);
		try
		{
			for(int i=0;i<param.length;i++)
				ps.setObject(i+1, param[i]);
			ResultSet rs=ps.executeQuery();
			ResultSetMetaData rsmd=rs.getMetaData();
			while(rs.next())
			{
				Map<String, Object> map=new HashMap<String, Object>();
				for(int i=0;i<rsmd.getColumnCount();i++)
				{
//					System.out.println(rsmd.getColumnName(i+1)+"\t"+rs.getObject(i+1));
					map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));
				}
				list.add(map);
			}
		}
		catch(SQLException e)
		{
			log.debug("Can't execute the query.",e);
			e.printStackTrace();
		}
		finally
		{
			closeStatement(ps);
			closeConnection(connection);
		}
		return list;
	}
    
	/**
	 * 数据库删除操作
	 * @param sql 数据库查询语句 param 要设置的参数对象
	 * 
	 */
	public boolean deleteRecord(String sql, Object[] param) throws SQLException
	{
		
		return execute(sql,param);
	}
    
	/**
	 *  数据库插入操作
	 * @param sql 数据库查询语句 param 要设置的参数对象
	 *
	 */
	public boolean insertRecord(String sql, Object[] param) throws SQLException
	{
	
		return execute(sql,param);
	}
	
    /**
     *数据库查询操作 s
     *@param sql 数据库查询语句 param 要设置的参数对象
     *
     */
	public List<?> queryRecord(String sql, Object[] param) throws SQLException
	{
		
		return executeQuery(sql,param);
	}
    
	/**
	 * 数据库更新操作
	 * @param sql 数据库查询语句 param 要设置的参数对象
	 * 
	 */
	public int updateRecord(String sql, Object[] param) throws SQLException
	{
		return executeUpdate(sql,param);
	}
    
	/**
	 * 关闭数据库连接池
	 */
	public void destroyPool() throws SQLException
	{
		DBPoolManager manager=new DBPoolManager();
		manager.destroyPool();
	}

}