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); } } }