package com.ww.j2ee;

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

public final class DBUtil {
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/j2ee_booksys?useUnicode=true&characterEncoding=GB2312";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "root";
	
	/**
	 * Get Connection with auto commit
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return getConnection(true);
	}
	
	/**
	 * Get Connection
	 * @param isAutoCommit false if need close auto commit, otherwise true;
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection(boolean isAutoCommit) throws SQLException {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		}
		
		Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		conn.setAutoCommit(isAutoCommit);
		
		return conn;
	}
	
	/**
	 * Commit Transaction
	 * @param conn
	 */
	public static void commit(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Rollback Transaction
	 * @param conn
	 */
	public static void rollback(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Release Connection
	 * @param conn
	 */
	public static void release(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Release Statement
	 * @param conn
	 */
	public static void release(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Release ResultSet
	 * @param conn
	 */
	public static void release(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Release Statement, Connection
	 * @param stmt
	 * @param conn
	 */
	public static void release(Statement stmt, Connection conn) {
		release(stmt);
		release(conn);
	}
	
	/**
	 * Release ResultSet, Statement, Connection
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void release(ResultSet rs, Statement stmt, Connection conn) {
		release(rs);
		release(stmt, conn);
	}
	
	public static void sqlstat(String sql){
		Connection conn=null;
		Statement stmt=null;
		System.out.println("sql:" + sql);
		try {
			//獲取connection, 關閉自動提交, 開啟Transaction
			conn = DBUtil.getConnection(false);
			//執行數據庫操作
			
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
	
			//提交Transaction
			DBUtil.commit(conn);
		} catch (SQLException e) {
			System.out.println("sql ERROR:" + e.getMessage());
			//回滾Transaction
			DBUtil.rollback(conn);
		} finally {
			DBUtil.release(stmt);
			DBUtil.release(conn);
		}
	}
	

}