package server.servlets;

import java.sql.*;

public class SqlBean {
    private String url = "jdbc:odbc:udb";
    private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    private String userId = "";
    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 SqlBean() {
        init();
    }

    public void init() {
        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();
        } 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();
        }
    }

    public ResultSet query(String sql) {
        try {
            Statement s = c.createStatement();
            ResultSet rs = s.executeQuery(sql);
            return rs;
        } 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);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }

    public int getResultSetIntegerColumnData(ResultSet rs,
                                             int columnIndex) {
        int columnValue = 0;

        try {
            if (rs.next())
                columnValue = rs.getInt(columnIndex);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return columnValue;
    }

    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 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[]) {
        SqlBean sb = new SqlBean();
        sb.init();
        sb.printInfo();
        sb.insert("INSERT INTO mailinglist (firstname,lastname) values ('doug','lyon');");
        sb.print(sb.query("SELECT FirstName, LastName FROM mailingList"));
        sb.close();
    }

}