/** * @author xichao.dong * @email 406592176@qq.com * @date 2014-8-15 上午8:50:38 * @company (开发公司) 珠海市冰川软件有限公司 * @copyright (版权) 本文件归属珠海市冰川软件有限公司所有 * @version V1.0 * @modify (修改) : 2014-8-15 上午8:50:38 xichao.dong * @Review (审核人) :xichao.dong */ package com.glacier.basic.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import org.springframework.core.io.ClassPathResource; /** * @ClassName: DBHelper * @Description: TODO(获取数据库连接) * @author xichao.dong * @email 406592176@qq.com * @date 2014-8-15 上午11:12:05 */ public final class DBHelper { // 此方法为获取数据库连接 public static Connection getConnection() { Connection conn = null; try { // 通过读取配置文件获取jdbc连接参数 ClassPathResource classPathResource = new ClassPathResource("spring/config.properties"); Properties p = new Properties(); p.load(classPathResource.getInputStream()); String driver = p.getProperty("connection.driver"); // 数据库驱动 String url = p.getProperty("connection.url");// 数据库 String user = p.getProperty("connection.username");// 用户名 String password = p.getProperty("connection.password");// 密码 Class.forName(driver); // 加载数据库驱动 if (null == conn) { conn = DriverManager.getConnection(url, user, password); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 增删改【Add、Del、Update】 * * @param sql * @return int */ public static int executeNonQuery(String sql) { int result = 0; Connection conn = null; Statement stmt = null; try { conn = getConnection(); stmt = conn.createStatement(); result = stmt.executeUpdate(sql); } catch (SQLException err) { err.printStackTrace(); free(null, stmt, conn); } finally { free(null, stmt, conn); } return result; } /** * 增删改【Add、Delete、Update】 * * @param sql * @param obj * @return int */ public static int executeNonQuery(String sql, Object... obj) { int result = 0; Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i++) { pstmt.setObject(i + 1, obj[i]); } result = pstmt.executeUpdate(); } catch (SQLException err) { err.printStackTrace(); free(null, pstmt, conn); } finally { free(null, pstmt, conn); } return result; } /** * 查【Query】 * * @param sql * @return ResultSet */ public static ResultSet executeQuery(String sql) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); } catch (SQLException err) { err.printStackTrace(); free(rs, stmt, conn); } return rs; } /** * 查【Query】 * * @param sql * @param obj * @return ResultSet */ public static ResultSet executeQuery(String sql, Object... obj) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i++) { pstmt.setObject(i + 1, obj[i]); } rs = pstmt.executeQuery(); } catch (SQLException err) { err.printStackTrace(); free(rs, pstmt, conn); } return rs; } /** * 判断记录是否存在 * * @param sql * @return Boolean */ public static Boolean isExist(String sql) { ResultSet rs = null; try { rs = executeQuery(sql); rs.last(); int count = rs.getRow(); if (count > 0) { return true; } else { return false; } } catch (SQLException err) { err.printStackTrace(); free(rs); return false; } finally { free(rs); } } /** * 判断记录是否存在 * * @param sql * @return Boolean */ public static Boolean isExist(String sql, Object... obj) { ResultSet rs = null; try { rs = executeQuery(sql, obj); rs.last(); int count = rs.getRow(); if (count > 0) { return true; } else { return false; } } catch (SQLException err) { err.printStackTrace(); free(rs); return false; } finally { free(rs); } } /** * 获取查询记录的总行数 * * @param sql * @return int */ public static int getCount(String sql) { int result = 0; ResultSet rs = null; try { rs = executeQuery(sql); rs.last(); result = rs.getRow(); } catch (SQLException err) { free(rs); err.printStackTrace(); } finally { free(rs); } return result; } /** * 获取查询记录的总行数 * * @param sql * @param obj * @return int */ public static int getCount(String sql, Object... obj) { int result = 0; ResultSet rs = null; try { rs = executeQuery(sql, obj); rs.last(); result = rs.getRow(); } catch (SQLException err) { err.printStackTrace(); } finally { free(rs); } return result; } /** * 释放【ResultSet】资源 * * @param rs */ public static void free(ResultSet rs) { try { if (rs != null) { rs.close(); } } catch (SQLException err) { err.printStackTrace(); } } /** * 释放【Statement】资源 * * @param st */ public static void free(Statement st) { try { if (st != null) { st.close(); } } catch (SQLException err) { err.printStackTrace(); } } /** * 释放【Connection】资源 * * @param conn */ public static void free(Connection conn) { try { if (conn != null) { conn.close(); } } catch (SQLException err) { err.printStackTrace(); } } /** * 释放所有数据资源 * * @param rs * @param st * @param conn */ public static void free(ResultSet rs, Statement st, Connection conn) { free(rs); free(st); free(conn); } }