package server.servlets;

// package pFormC;

import java.sql.*;
import java.util.Vector;

public class SQLBeanie {

    private String driver = "org.gjt.mm.mysql.Driver";
    private String url = "jdbc:mysql://localhost:3306/test";
    //  private String userId = "DeltaHall";
    private String userId = null;
    private String password = null;
    //    private String password = "Pongo1";

    private Connection cn;
    private Statement stmt;
//  private ResultSet rs;


//   private String url = "jdbc:mysql://JHVY501/test";
//   private String url = "jdbc:odbc:db1";
//   private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
//   private String userId = "Admin";
//   private String password = "";

    private Connection c;
    private DatabaseMetaData dbmd;
    private boolean isReadOnly = false;
    private boolean usesLocalFiles = false;
    private String driverName = null;
    private String catalogName = null;
    private String productName = null;

    public boolean getUsesLocalFiles() {
        return usesLocalFiles;
    }

    public boolean getReadOnly() {
        return isReadOnly;
    }

    public String getCatalogName() {
        return catalogName;
    }

    public String getDriverName() {
        return driverName;
    }

    public String getProductName() {
        return productName;
    }

    public void setUserId(String _userId) {
        userId = _userId;
    }

    public void setPassword(String _password) {
        password = _password;
    }

    public void setUrl(String _url) {
        url = _url;
    }

    public void setDriver(String _driver) {
        driver = _driver;
    }

    public SQLBeanie() {
    }

    public static void main(String args[]) {
        SQLBeanie s = new SQLBeanie();
        System.out.println("will try to open");
        s.open();
        s.printInfo();
        System.out.println("I opened!");
        s.close();
        System.out.println("I closed");
    }

    public void open() {
        try {
            Class.forName(driver);
            c = DriverManager.getConnection(
                    url, userId, password);
            dbmd = c.getMetaData();
            catalogName =
                    c.getCatalog();
            isReadOnly = c.isReadOnly();
            usesLocalFiles =
                    dbmd.usesLocalFiles();
            driverName =
                    dbmd.getDriverName();
            productName =
                    dbmd.getDatabaseProductName();
            stmt = c.createStatement();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Opened Connection:" + url);
    }

    public void printInfo() {
        println("productName=" + productName);
        println("catalogName=" + catalogName);
        println("is ReadOnly=" + getReadOnly());
        println("usesLocalFiles=" + getUsesLocalFiles());
        println("driverName=" + driverName);
    }

    public void close() {
        try {
            c.close();
            System.out.println("closed connection");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private ResultSet rs;
    private ResultSetMetaData rsmd;

    public void execute(String sql) {
        rs = query(sql);
        rsmd = getResultSetMetaData(rs);
    }

    public String[][] getResults() {
        if (rs == null) return null;
        if (rsmd == null) return null;
        String names[] = getColumnNames(rsmd);
        int c = names.length;
        Vector v = new Vector();
        v.add(names);
        while (nextRow(rs)) {
            v.add(getRowAsString(rs));
        }
        int r = v.size();
        String sa[][] = new String[r][c];
        for (int x = 0; x < r; x++) {
            String s[] = (String[]) v.elementAt(x);
            for (int y = 0; y < c; y++) {
                sa[x][y] = s[y];
            }
        }
        return sa;

    }


    public ResultSet query(String sql) {
        try {
            Statement s = c.createStatement();
            return s.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public ResultSetMetaData
            getResultSetMetaData(ResultSet rs) {
        try {
            return rs.getMetaData();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int getColumnCount(
            ResultSetMetaData rsmd) {
        try {
            return rsmd.getColumnCount();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public String[] getcolumnTypeNames(
            ResultSetMetaData rsmd) {
        int count = getColumnCount(rsmd);
        String sa [] = new String[count];
        try {
            for (int i = 0; i < sa.length; i++) {
                sa[i] = rsmd.getColumnTypeName(i + 1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sa;
    }

    public String[] getRowAsString(ResultSet rs) {

        int N = getColumnCount(
                getResultSetMetaData(rs));
        String s[] = new String[N];
        try {
            for (int i = 0; i < N; i++) {
                s[i] = rs.getString(i + 1);
//deh
                System.out.println(s[i]);
            }
//deh
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }

    public void print(ResultSet rs) {
        int i;
        String cn[] = getColumnNames(
                getResultSetMetaData(rs));
        println(cn);
        boolean more = false;
        while (more = nextRow(rs))
            println(getRowAsString(rs));
    }

    public void println(Object o) {
        System.out.println(o);
    }

    public void println(String s[]) {
        for (int i = 0; i < s.length; i++)
            System.out.print(s[i] + '\t');
        System.out.println();
    }

    public void println(String s[][]) {
        for (int r = 0; r < s.length; r++) {
            for (int c = 0; c < s[r].length; c++)
                System.out.print(s[r][c] + '\t');
            System.out.println();
        }
    }

    public boolean nextRow(ResultSet rs) {
        try {
            return rs.next();
        } catch (SQLException e) {
            return false;
        }
    }

    public String[] getColumnNames(ResultSetMetaData rsmd) {
        String s []
                = new String[getColumnCount(rsmd)];
        try {
            for (int i = 1; i <= s.length; i++)
                s[i - 1] = rsmd.getColumnLabel(i);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return s;
    }

    public DatabaseMetaData getDatabaseMetaData() {
        return dbmd;
    }

    public void insert(String sql) {
        try {
            Statement s = c.createStatement();
            int insertResult = s.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
/*   public static void main(String args[]) {
        SqlBeanie sb = new SqlBeanie();
        sb.open();


        sb.execute(
                "SELECT FirstName, LastName FROM mailingList"
                );
        sb.println(sb.getResults());
        sb.close();
   }
*/
    /**
     * Return true/false
     *
     * @parm         String        sql
     * @return       boolean
     */

    public boolean checkRecordExists(String sql) {

        try {
            open();
            System.out.println("The SQL is " + sql);
            rs = stmt.executeQuery(sql);
            close();
            return rs.next();
        }
//      catch (ClassNotFoundException cnf) {
//          System.out.println("\n ClassNotFound");
//          cnf.printStackTrace();
//          return false;
//  }
        catch (SQLException sqle) {
            System.out.println("\n checkRecordExists Failure");
//          sqle.printStackTrace();
            return false;
        } catch (Exception e) {
            System.out.println("\n Exception");
            e.printStackTrace();
            return false;
        }
    }

}