package com.mescs.baseinfo.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;/**
 * 
 * 類名：DatabaseUtil <br />
 *
 * 功能：數(shù)據(jù)庫操作
 *
 */
 public class DatabaseUtil { 
 private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class); 
 private static final String DRIVER = "org.firebirdsql.jdbc.FBDriver"; 
 private static final String URL = "jdbc:firebirdsql:embedded:mescs.fdb?lc_ctype=UTF8"; 
 private static final String USERNAME = "root"; 
 private static final String PASSWORD = "root"; 
 private static final String SQL = "DELETE FROM ";// 數(shù)據(jù)庫數(shù)據(jù)庫表內(nèi)容清空
 
 static { try {
 Class.forName(DRIVER);
 } catch (ClassNotFoundException e) {
 LOGGER.error("數(shù)據(jù)庫驅(qū)動加載失敗===>>>", e);
 }
 } /**
 * 獲取數(shù)據(jù)庫連接
 *
 * @return */
 public static Connection getConnection() {
 Connection conn = null; 
 try {
 conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
 } catch (SQLException e) {
 LOGGER.error("數(shù)據(jù)庫連接失敗===>>>", e);
 } return conn;
 } /**
 * 關(guān)閉數(shù)據(jù)庫連接
 * @param conn */
 public static void closeConnection(Connection conn) { 
 if(conn != null) { 
 try {
 conn.close();
 } catch (SQLException e) {
 LOGGER.error("數(shù)據(jù)庫關(guān)閉失敗===>>>", e);
 }
 }
 } /**
 * 獲取數(shù)據(jù)庫下的所有表名 */
 public static List<String> getTableNames() {
 List<String> tableNames = new ArrayList<String>();
 Connection conn = getConnection();
 ResultSet rs = null; 
 try { //獲取數(shù)據(jù)庫的元數(shù)據(jù)
 DatabaseMetaData db = conn.getMetaData(); //從元數(shù)據(jù)中獲取到所有的表名
 rs = db.getTables(null, null, null, new String[] { "TABLE" }); 
 while(rs.next()) {
 tableNames.add(rs.getString(3));
 }
 } catch (SQLException e) {
 LOGGER.error("獲取數(shù)據(jù)庫表名失敗===>>>", e);
 } finally { try {
 rs.close();
 closeConnection(conn);
 } catch (SQLException e) {
 LOGGER.error("關(guān)閉數(shù)據(jù)庫失敗===>>>", e);
 }
 } return tableNames;
 } /*
 * 清空數(shù)據(jù)庫表 */
 public static void emptyDatabase() throws SQLException {
 List<String> tableNames = getTableNames();
 Connection conn = getConnection();
 PreparedStatement pStemt = null; 
 for(String table:tableNames){ 
 if(!table.equals("SYSPARAM") && !table.equals("T_CLEAR_DATA")){
 String tableSql = SQL + table; 
 try {
 pStemt = conn.prepareStatement(tableSql);
 Statement stmt=conn.createStatement();
 stmt.executeUpdate(tableSql);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }
 conn.close();
 }
}