java(2014版)串連資料庫的工具類

package util;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;public class JdbcUtil {private static Connection conn;private static PreparedStatement pstmt;private static ResultSet rs;private JdbcUtil() {super();// TODO Auto-generated constructor stub}// 串連資料庫的操作public static Connection getConn() {if (conn == null) {// 載入屬性設定檔// 建立屬性對象Properties prop = new Properties();try {// 載入指定名稱的屬性檔案prop.load(JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));try {// 根據檔案的名稱尋找類檔案Class.forName(prop.getProperty("driver"));// 建立連線物件try {conn = DriverManager.getConnection(prop.getProperty("url"),prop.getProperty("user"),prop.getProperty("pass"));System.out.println("串連資料庫連接成功");} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println("串連資料庫失敗");e.printStackTrace();}} catch (ClassNotFoundException e) {// TODO Auto-generated catch blockSystem.out.println("沒有" + prop.getProperty("driver") + "檔案");e.printStackTrace();}} catch (IOException e) {// TODO Auto-generated catch blockSystem.out.println("屬性檔案載入出錯");e.printStackTrace();}}return conn;}public static void release(ResultSet rs, PreparedStatement pstmt) {// 釋放結果集if (rs != null) {try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}// 釋放準備語句if (pstmt != null) {try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}// 更新資料的操作增,刪,改要用到的封裝方法public static boolean upDate(String sql, Object[] obj) {boolean flag = false;try {// 準備語句的建立,帶有sql命令的對象pstmt = getConn().prepareStatement(sql);for (int i = 1; i <= obj.length; i++) {pstmt.setObject(i, obj[i - 1]);}int i = pstmt.executeUpdate();if (i > 0) {flag = true;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {release(rs, pstmt);}return flag;}// 進行大量刪除處理public static boolean updateBatchDel(String sql, Object[] ids) {boolean flag = false;Connection conn = getConn();PreparedStatement pstmt = null;ResultSet rs = null;try {conn.setAutoCommit(false);pstmt = conn.prepareStatement(sql);for (int i = 0; i < ids.length; i++) {pstmt.setObject(1, ids[i]);System.out.println(sql + "---------------" + ids[i]);pstmt.addBatch();}int[] num = pstmt.executeBatch(); // 批量執行for (int i = 0; i < num.length; i++) {if (num[i] == 0) {try {conn.rollback(); // 進行交易回復return flag;} catch (SQLException ex) {ex.printStackTrace();}}}conn.commit();// 提交事務flag = true;} catch (SQLException e) {e.printStackTrace();} finally {release(rs, pstmt);}return flag;}// 根據傳入的表的名稱,和每頁資料得到傳入表的所有的頁數// tableName:::::操作的資料表名稱// pagesize::::::每頁顯示的資訊條數public static Integer getCountPage(String tableName, Integer pagesize) {Integer countPage = 0;String sql = "select count(*) as c from " + tableName;Connection conn = JdbcUtil.getConn();PreparedStatement pstmt = null;ResultSet rs = null;conn = JdbcUtil.getConn();try {pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery();if (rs.next()) {int countRecord = rs.getInt("c");countPage = countRecord % pagesize == 0 ? countRecord/ pagesize : countRecord / pagesize + 1;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {JdbcUtil.release(rs, pstmt);}return countPage;}}