SQL数据库连接工具类

package cn.itcast;

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

/**
 * 查询结果处理接口
 * 
 * @author 作者：LiuJunGuang
 * @version 创建时间：2011-11-10 下午07:26:41
 */
interface ResultSetHander {
	Object hander(ResultSet rs);
}

/**
 * 类说明：数据库连接和释放
 * 
 * @author 作者: LiuJunGuang
 * @version 创建时间：2011-11-6 上午11:49:14
 */
public class JDBCUtils {
	private static String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/chat";
	private static String user = "root";
	private static String password = "root";
	private static Connection con = null;
	// 注册驱动
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 创建连接
	public static Connection getConnection() {
		if (con == null) {
			try {
				con = DriverManager.getConnection(url, user, password);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		return con;
	}

	// 关闭连接
	public static void colse(ResultSet rs, Statement st, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	// 更新数据库
	public static void update(String sql, Object[] params) {
		Connection con = JDBCUtils.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ParameterMetaData psm = ps.getParameterMetaData();// 获得查询语句的元数据信息
			int paramNum = psm.getParameterCount();// 得到参数的总个数
			for (int i = 1; i <= paramNum; i++) {
				ps.setObject(i, params[i - 1]);
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtils.colse(rs, stmt, con);
		}
	}

	// 查询数据库
	public static Object query(String sql, Object[] params, ResultSetHander rsh) {
		Connection con = JDBCUtils.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ParameterMetaData psm = ps.getParameterMetaData();// 获得查询语句的元数据信息
			int paramNum = psm.getParameterCount();// 得到参数的总个数
			for (int i = 1; i <= paramNum; i++) {
				ps.setObject(i, params[i - 1]);
			}
			rs = ps.executeQuery();
			return rsh.hander(rs);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtils.colse(rs, stmt, con);
		}
	}
}

package cn.itcast;

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

import org.junit.Test;

/**
 * 类说明：数据库操作类
 * 
 * @author 作者: LiuJunGuang
 * @version 创建时间：2011-11-6 下午12:02:36
 */
public class JDBCUtilsTest {
	@Test
	public void find() {
		String sql = "select * from history";

		JDBCUtils.query(sql, null, new ResultSetHander() {
			@Override
			public Object hander(ResultSet rs) {
				try {
					while (rs.next()) {
						System.out.println(rs.getString("username"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return null;
			}
		});
	}

	@Test
	public void update() {
		String sql = "delete from history where id = ?";
		Object obj[] = { 1 };
		JDBCUtils.update(sql, obj);
	}

	@Test
	public void find2() {
		Connection con = JDBCUtils.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			String find = "select * from history where username=? and action=?";
			st = con.prepareStatement(find);
			st.setString(1, "nihao");
			st.setString(2, "微笑着");
			rs = st.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString("username"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.colse(rs, st, con);
		}
	}
}