package myjdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.*;
import java.sql.SQLException;
import java.util.Properties;

import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import myjdbc.util.WrapperUtil;

public class DatabaseOperation {
    private static DatabaseOperation dbo = null;
    private static final int FETCH_ALL = Integer.MAX_VALUE;
    private static final int NO_OFFSET = -1;
    private static final int DEFAULT_PAGE_SIZE = 20;
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    private DataSource ds = null;
    private static String staticTableConfigPath = "src/conf"; //
    private static String staticDataSourceName = "DataSource";
    //私有構在函數
    private DatabaseOperation() {
        try {
            Context ctx = getWeblogicServerInitialContext();
            ds = (DataSource) ctx.lookup(staticDataSourceName);
        } catch (NamingException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //獲得weblogic的上下文
    private final Context getWeblogicServerInitialContext() throws Exception {
        String url = "t3://127.0.0.1:7001";
        String user = null;
        String password = null;
        Properties properties;
        try {
            properties = new Properties();
            //  properties.put("tableConfigPath","F:/jbuilder_project/writebook/relation_manage/src/conf");
            properties.put(Context.INITIAL_CONTEXT_FACTORY,
                           "weblogic.jndi.WLInitialContextFactory");
            properties.put(Context.PROVIDER_URL, url);
            if (user != null) {
                properties.put(Context.SECURITY_PRINCIPAL, user);
                properties.put(Context.SECURITY_CREDENTIALS,
                               password == null ? "" : password);

            }
            return new javax.naming.InitialContext(properties);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    //獲得DatabaseOperation的對象
    public static DatabaseOperation getInstance(String tableConfigPath,
                                                String dataSource) {
        staticTableConfigPath = tableConfigPath;
        staticDataSourceName = dataSource;
        if (dbo == null) {
            dbo = new DatabaseOperation();
        }
        return dbo;
    }

    public static DatabaseOperation getInstance() {
        if (dbo == null) {
            dbo = new DatabaseOperation();
        }
        return dbo;
    }

    //初始化Connection
    private void openConnection() {
        if (conn == null) {
            try {
                conn = ds.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //關閉connection
    private void colseConnection() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pst != null) {
                pst.close();
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //真正的執行SQL語句的函數
    public List executeSQL(String sql, Object[] paras, int maxRow, int offset) {
        openConnection();
        try {
            sql = sql.toLowerCase();
            pst = conn.prepareStatement(sql);
            pst.setMaxRows(maxRow);
            if (paras != null && paras.length > 0) {
                for (int i = 0, len = paras.length; i < len; i++) {
                    pst.setObject(i + 1, paras[i]);
                }
            }
            if (sql.trim().startsWith("s")) {
                rs = pst.executeQuery();
                if (offset > 0) {
                    rs.absolute(offset);
                }
                return WrapperUtil.getInstance().wrapperToObjectCol(rs, sql,
                        staticTableConfigPath);
            } else {
                int i = pst.executeUpdate();
//       if (i<=0)
//         throw new Exception("數據庫操作執行失敗！");
                return null;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            this.colseConnection();
        }
    }

    public List executeSQL(String sql, Object[] paras, int maxRow) {
        return executeSQL(sql, paras, maxRow, NO_OFFSET);
    }

    public List executeSQL(String sql, Object[] paras) {
        return executeSQL(sql, paras, FETCH_ALL, NO_OFFSET);
    }

    public List executeSQL(String sql) {
        return executeSQL(sql, null, FETCH_ALL, NO_OFFSET);
    }

    //pageNo從0開始
    public List executeSQLSeperatePage(String sql, Object[] paras, int pageSize,
                                       int pageNo) {
        int offset = -1;
        if (pageNo != 1) {
            offset = pageSize * (pageNo - 1);
        }
        //rs.absolute(pageSize * pageNo);
        System.out.println("offset = " + offset);
        return executeSQL(sql, paras, pageSize * (pageNo), offset);
    }

    //獲得總頁數
    public int getTotalPage(String sql, Object[] paras, int pageSize) {
        try {
            openConnection();
            PreparedStatement _pst = conn.prepareStatement(sql);

            if (paras != null && paras.length > 0) {
                for (int i = 0, len = paras.length; i < len; i++) {
                    _pst.setObject(i + 1, paras[i]);
                }
            }
            ResultSet _rs = _pst.executeQuery();
            _rs.last();
            int rows = _rs.getRow();
            return (rows % pageSize == 0) ? rows / pageSize :
                    rows / pageSize + 1;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            colseConnection();
        }

    }

    public int getTotalPage(String sql) {
        return getTotalPage(sql, null, DEFAULT_PAGE_SIZE);
    }

    public int getTotalPage(String sql, int pageSize) {
        return getTotalPage(sql, null, pageSize);
    }

    public int getTotalPage(String sql, Object[] paras) {
        return getTotalPage(sql, paras, DEFAULT_PAGE_SIZE);
    }

    public static void main(String args[]) throws Exception {
        List result = new ArrayList();
        String sql = "select * from orderitem as a";
//    sql = sql.toLowerCase();
        DatabaseOperation database = DatabaseOperation.getInstance();
        //   database.openConnection();
        result = database.executeSQL(sql);
        System.out.println("asdf=====>" + result.size());
//    String[] columns = parserSQL(sql,"select","from");
//    //sql語句如果是select * from table,這樣的模式，則列名數組為空
//    if (columns == null)
//    {
//      String[] tables = getTableName(sql);
//      if (tables.length > 1)
//        throw new Exception("暫時只支持單表的 select * from table 的操作！");
//
//
//    }
//    else {
//      int len = columns.length;
//      int i = 0;
//      try {
//        while (rs.next()) {
//          Object[] temp = new Object[len];
//          while (i < len) {
//            temp[i] = rs.getObject(columns[i]);
//            i++;
//          }
//          result.add(temp);
//          i = 0;
//        }
//      }
//      catch (SQLException ex) {
//        ex.printStackTrace();
//      }
//      prt(result);
//      database.colseConnection();
//    }
    }
//  private static void prt(List list)
//  {
//
//    for (Iterator iter = list.iterator(); iter.hasNext(); ) {
//       WbRelationDetail item = (WbRelationDetail) iter.next();
//     System.out.println("relation sn = " + item.getRelationSn() +" , and relation name = "+ item.getName());
//
//    }
//  }
}