package Web;

/**
 * 数据库操作
 * @author Ma
 */

import com.sun.org.apache.regexp.internal.REUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DB {

 public static Connection Connection;
 public static int lastUserTime;

 public static void connection() {

 if ((Options.DBDriver != null) && (Options.DBURL != null) && (Options.DBUser != null) && (Options.DBPassword != null)) {
 String driver = Options.DBDriver; // 驱动程序名
 String url = Options.DBURL; // URL指向要访问的数据库名
 try {
 Class.forName(driver); // 加载驱动程序
 Connection conn = DriverManager.getConnection(url, Options.DBUser, Options.DBPassword); // 连续数据库
 //验证是否连接成功
 if (!conn.isClosed()) {
 DB.Connection = conn;
 DB.lastUserTime = (int) (System.currentTimeMillis() / 1000);
 } else {
 DB.Connection = null;
 }

 //DB.Connection.close();如果会自动处理就不用手动释放资源了
 } catch (ClassNotFoundException ex) {
 Logger.getLogger(Allocation.class.getName()).log(Level.SEVERE, null, ex);
 System.exit(-1);
 } catch (SQLException ex) {
 Logger.getLogger(Allocation.class.getName()).log(Level.SEVERE, null, ex);
 System.exit(-1);
 } catch (Exception ex) {
 Logger.getLogger(Allocation.class.getName()).log(Level.SEVERE, null, ex);
 }
 }
 }

 /**
 * 用原生态的executeQuery 返回ResultSet
 *
 * @param sql
 * @param params
 * @return ResultSet
 */
 public static ResultSet executeQuery(String sql, String... params) {
 DB.cursor();
 PreparedStatement statement;
 try {
 statement = DB.Connection.prepareStatement(sql);
 for (int i = 0; i < params.length; i++) {
 statement.setString((i + 1), params[i]);
 }
 ResultSet r = statement.executeQuery();
 return r;
 } catch (SQLException ex) {
 Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
 }

 return null;
 }

 /**
 * 用原生态的executeUpdate
 *
 * @param sql
 * @param params
 * @return
 */
 public static int executeUpdate(String sql, String... params) {
 DB.cursor();
 try {
 PreparedStatement statement = DB.Connection.prepareStatement(sql);
 for (int i = 0; i < params.length; i++) {
 statement.setString((i + 1), params[i]);
 }
 int r = statement.executeUpdate();
 statement.close();
 return r;
 } catch (SQLException ex) {
 Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
 }
 return 0;

 }

 /**
 * 获取多个结果集
 *
 * @param sql
 * @param params
 */
 public static Object[][] get(String sql, String... params) {
 DB.cursor();
 Object[][] data = null;
 try {
 PreparedStatement statement = DB.Connection.prepareStatement(sql);
 for (int i = 0; i < params.length; i++) {
 statement.setString((i + 1), params[i]);
 }

 ResultSet r = statement.executeQuery();
 //获取字段信息getMetaData
 ResultSetMetaData columns = r.getMetaData();

 int columnCount = columns.getColumnCount();
 String[] columnName = new String[columnCount];
 int[] columnTypeName = new int[columnCount];
 for (int i = 1; i <= columnCount; i++) {
 columnName[i - 1] = columns.getColumnName(i);
 columnTypeName[i - 1] = DB.getTypeName(columns.getColumnType(i));
 }

 r.last();

 int rowCount = r.getRow();
 if (rowCount < 1) {
 return new Object[rowCount][columnCount];
 }
 r.first();
 data = new Object[rowCount][columnCount];
 int i = 0;

 do {
 Object[] temp = new Object[columnCount];
 for (int j = 0; j < columnName.length; j++) {
 switch (columnTypeName[j]) {
 case 0:
 temp[j] = r.getBoolean(j + 1);
 break;
 case 1:
 temp[j] = r.getInt(j + 1);
 break;
 default:
 temp[j] = r.getString(j + 1);
 break;

 }
 }

 data[i] = temp;
 i++;
 } while (r.next());
 //随手关闭
 r.close();

 statement.close();
 return data;


 } catch (SQLException ex) {
 Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex);
 }
 return data;
 }

 /**
 * 获取一行
 *
 * @param sql
 * @param params
 * @return
 */
 public static Object[] find(String sql, String... params) {
 Object[][] r = DB.get(sql, params);
 if (r.length < 1) {
 return new Object[0];
 }
 return r[0];
 }

 /**
 * 获取一个字段
 *
 * @param sql
 * @param params
 * @return
 */
 public static Object getField(String sql, String... params) {
 Object[][] r = DB.get(sql, params);
 if (r.length < 1) {
 return null;
 } else {
 return r[0][0];
 }

 }

 /**
 * 更新数据
 *
 * @param sql
 * @param params
 * @return
 */
 public static int update(String sql, String... params) {
 DB.cursor();
 try {
 PreparedStatement statement = DB.Connection.prepareStatement(sql);
 for (int i = 0; i < params.length; i++) {
 statement.setString((i + 1), params[i]);
 }
 //获取字段信息getMetaData
 int r = statement.executeUpdate();
 statement.close();
 return r;
 } catch (SQLException ex) {
 Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
 }
 return 0;
 }

 public static int insert(String sql, String... params) {
 return DB.update(sql, params);
 }

 public static int delete(String sql, String... params) {
 return DB.update(sql, params);
 }

 /**
 * 解析SQL便于开发
 *
 * @param sql
 * @param params
 * @return
 */
 public static String getSQL(String sql, String... params) {
 for (int i = 0; i < params.length; i++) {
 sql = sql.replaceFirst("\\?", sql);
 }
 return sql;
 }

 public static int getTypeName(int typeId) {
 switch (typeId) {
 case Types.NUMERIC:
 //Int 类型
 return 1;
 case Types.BOOLEAN:
 //Boolean类型
 return 0;
 default:
 //其余的当作String处理
 return 2;
 }
 }

 /**
 * 检查链接时间是否过期
 */
 public static void cursor() {
 if ((DB.lastUserTime + Options.DBIdleTime) < System.currentTimeMillis()) {
 DB.connection();
 } else {
 DB.lastUserTime = (int) System.currentTimeMillis() / 1000;
 }
 }
}