关于数据库的连接，以及对数据增删该查的操作的封装

这是我写的连接数据库（mysql） 对连接数据库，采用了特殊的方法，还有 对数据库的增删该查的操作 将其封装起来。方便以后的调用。

/*******************************************************************
文件夹名称:com.jdbc
文件名称:DbConn.java
系统名称:jtlcWeb
模块名称:
功能说明: 
系统版本:1.0
开发人员: lsit
开发时间:2012-9-7下午12:38:27
相关文档:TODO
 ***************************************************************** */
package com.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

/*
 * 
 * 
 * 
 * 数据库连接
 */

public class DbConn
{
	// 连接数据库的用户名
	private String username;
	// 连接数据库的密码
	private String password;
	// 连接数据库的url
	private String url;
	// 连接数据库的驱动
	private String driver;
	// 连接池，用来存放连接的
	private Vector<Connection> pool;
	// 连接池的大小 ，换句话说就是连接池中(pool)存入连接的数量
	private int poolSize = 1;

	private static DbConn instance = null;

	public DbConn()
	{
		init();
	}

	/**
	 * 初始化连接池
	 */
	private void init()
	{

		pool = new Vector<Connection>();
		readConfig();
		addConnction();
		// TODO Auto-generated method stub

	}

	/*
	 * 提供返回本类实例的一个接口
	 */

	public static DbConn getInstance()
	{
		if (null == instance)
		{
			instance = new DbConn();
		}

		return instance;
	}

	/**
	 * 向连接池添加连接
	 */

	private void addConnction()
	{

		try
		{
			Class.forName(this.driver);
			Connection connection = null;

			for (int i = 0; i < poolSize; i++)
			{
				connection = DriverManager.getConnection(url, username,
						password);

				pool.add(connection);

			}

		} catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * 从连接池中取出一个连接
	 */

	public synchronized Connection getConnection()
	{
		if (pool.size() > 0)
		{
			Connection connection = pool.get(0);
			pool.remove(connection);

			return connection;
		} else
		{
			return null;
		}

	}

	/**
	 * 将连接放回连接池
	 */

	public synchronized void reasle(Connection connection)
	{

		pool.add(connection);

	}

	/*
	 * 读取配置文件
	 */
	public void readConfig()
	{

		Properties properties = new Properties();

		InputStream iStream = this.getClass().getResourceAsStream(
				"pool.properties");

		try
		{
			properties.load(iStream);
			this.username = properties.getProperty("username");
			this.password = properties.getProperty("password");
			this.driver = properties.getProperty("driver");
			this.url = properties.getProperty("url");
			this.poolSize = Integer
					.parseInt(properties.getProperty("poolSize"));

		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}





package com.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * 
 */

public class DbUtils
{

	private Connection conn;

	private PreparedStatement stat;

	private ResultSet rs;

	private static DbUtils instance;

	public DbUtils()
	{

	}

	/**
	 * 返回本类的一个实例
	 */
	public static DbUtils getInstance()
	{
		if (null == instance)
		{
			instance = new DbUtils();
		}

		return instance;
	}

	/*
 * 
 */

	public boolean save(String sql)
	{
		boolean isOk = false;
		try
		{
			conn = DbConn.getInstance().getConnection();
			stat = conn.prepareStatement(sql);

			stat.execute();

			isOk = true;
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isOk;
	}

	/*
	 *
	 * 
	 */

	public boolean update(String sql)
	{
		boolean isOk = false;
		conn = DbConn.getInstance().getConnection();
		try
		{
			stat = conn.prepareStatement(sql);
			stat.executeUpdate(sql);

			isOk = true;
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return isOk;
	}

	/*
	 * 
	 */

	public ResultSet query(String sql)
	{
		rs = null;
		try
		{
			conn = DbConn.getInstance().getConnection();
			stat = conn.prepareStatement(sql);
			rs = stat.executeQuery();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rs;
	}

	/*
	 * 
	 */

	public boolean delete(String sql)
	{
		boolean isOk = false;
		conn = DbConn.getInstance().getConnection();
		try
		{
			stat = conn.prepareStatement(sql);
			stat.executeUpdate(sql);

			isOk = true;
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return isOk;
	}

	/**
	 * 分页查询
	 * 
	 * @param page
	 *            显示第几页
	 * @param pageSize
	 *            一页显示多少条记录
	 * @param sql
	 *            要执行的select 语句
	 * @return ResultSet 分页查询的结果
	 * @author Administrator
	 */
	public ResultSet queryByPage(int page, int pageSize, String sql)
	{
		rs = null;
		try
		{
			int begin = (page - 1) * pageSize;
			sql = sql + " limit " + begin + "," + pageSize;
			rs = query(sql);
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 获取总记录数
	 * 
	 * @param sql
	 *            要执行的select 语句
	 * @return int 总记录数
	 */
	public int getRecordCount(String sql)
	{
		int count = 0;
		try
		{
			sql = "select count(*) " + sql.substring(sql.indexOf("from"));
			rs = query(sql);
			rs.next();
			count = rs.getInt(1);
		} catch (Exception e)
		{
			e.printStackTrace();
		} finally
		{
			try
			{
				rs.close();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return count;
	}

	/**
	 * 获取总页数
	 * 
	 * @param sql
	 *            要执行的select语句
	 * @param pageSize
	 *            一页显示的记录数
	 * @return int 总页数
	 */
	public int getPageCount(String sql, int pageSize)
	{
		int count = 0;
		int total = this.getRecordCount(sql);
		count = total % pageSize == 0 ? (total / pageSize)
				: (total / pageSize + 1);
		return count;
	}

	/**
	 * 释放连接 关闭statement
	 */

	public void close()
	{
		try
		{
			if (rs != null)
			{
				rs.close();
			}
			if (stat != null)
			{
				stat.close();
			}
			if (conn != null)
			{
				conn.close();
			}
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}