package test;

import com.sun.rowset.*;
import org.w3c.dom.ranges.Range;

import javax.sql.RowSet;
import javax.sql.rowset.*;
import java.sql.*;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import static java.lang.System.out;


public class Application {

    private final static Logger log = Logger.getLogger(Application.class.getName());
    private static Connection connection;

    public static void main(String[] args) {
        try {
            new Application().start();
        } catch (Throwable e) {
            log.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            close();
        }
    }

    private Connection establishConnection() throws Exception {
        Driver driver = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();
        return connection = driver.connect("jdbc:mysql://localhost/mysql?user=root&password=", new Properties());
    }

    private static void close() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.log(Level.SEVERE, e.getMessage(), e);
            }
        }
    }

    private void start() throws Exception {
        testJdbcRowSet();
        testWebRowSet();
        testFilteredRowSet();
        testCachedRowSet();
        testJoinRowSet();
    }

    private void testJdbcRowSet() {
        out.println(" -- JdbcRowSet Test -- ");
        try {
            Statement statement = establishConnection().createStatement();
            if (statement.execute("show tables")) {
                ResultSet rs = statement.getResultSet();
                JdbcRowSet rowSet = new JdbcRowSetImpl(rs);

                while (rowSet.next()) {
                    rowSet.getString(1);
                    out.println("rowSet.getString(1) = " + rowSet.getString(1));
                }
                out.println(" -------- --------- -------- --------- -------- --------- ");
            }
        } catch (Exception e) {
            log.log(Level.SEVERE, e.getMessage(), e);
        }
    }

    private void testWebRowSet() {
        out.println(" -- WebRowSet Test -- ");
        try {
            Statement statement = establishConnection().createStatement();
            if (statement.execute("show tables")) {
                ResultSet rs = statement.getResultSet();
                WebRowSet rowSet = new WebRowSetImpl();
                rowSet.populate(rs);


                rowSet.absolute(1);
                rowSet.moveToInsertRow();
                rowSet.updateString(1, "new_table_in_result");
                rowSet.insertRow();

                rowSet.moveToCurrentRow();
                rowSet.absolute(4);
                rowSet.deleteRow();

                rowSet.writeXml(out);
                out.println(" -------- ----------------- --------- -------- --------- ");
            }
        } catch (Exception e) {
            log.log(Level.SEVERE, e.getMessage(), e);
        }
    }

    private void testFilteredRowSet() {
        out.println(" -- FilteredRowSet Test -- ");
        try {
            Statement statement = establishConnection().createStatement();
            if (statement.execute("show tables")) {
                ResultSet rs = statement.getResultSet();
                FilteredRowSet rowSet = new FilteredRowSetImpl();
                rowSet.populate(rs);
                rowSet.setFilter(new Predicate() {

                    @Override
                    public boolean evaluate(RowSet rs) {
                        try {
                            return rs.getString(1).startsWith("time");
                        } catch (SQLException e) {
                            return false;
                        }
                    }

                    @Override
                    public boolean evaluate(Object value, int column) throws SQLException {
                        return value.toString().startsWith("time");
                    }

                    @Override
                    public boolean evaluate(Object value, String columnName) throws SQLException {
                        return value.toString().startsWith("time");
                    }
                });

                while (rowSet.next()) {
                    rowSet.getString(1);
                    out.println("rowSet.getString(1) = " + rowSet.getString(1));
                }
                out.println(" -------- --------- -------- --------- -------- --------- ");
            }
        } catch (Exception e) {
            log.log(Level.SEVERE, e.getMessage(), e);
        }
    }

    private void testCachedRowSet() {
        out.println(" -- CachedRowSet Test -- ");
        try {
            Statement statement = establishConnection().createStatement();
            if (statement.execute("show tables")) {
                ResultSet rs = statement.getResultSet();
                CachedRowSet rowSet = new CachedRowSetImpl();

                rowSet.populate(rs);
                out.println("rowSet.getSyncProvider() = " + rowSet.getSyncProvider());
                out.println("rowSet.toCollection() = " + rowSet.toCollection());

                while (rowSet.next()) {
                    rowSet.getString(1);
                    out.println("rowSet.getString(1) = " + rowSet.getString(1));
                }
                out.println(" -------- --------- -------- --------- -------- --------- ");
            }
        } catch (Exception e) {
            log.log(Level.SEVERE, e.getMessage(), e);
        }
    }

    private void testJoinRowSet() {
        out.println(" -- JoinRowSet Test -- ");
        try {
            Statement statement = establishConnection().createStatement();
            if (statement.execute("select host, user, password from user")) {
                CachedRowSet firstRowSet = new CachedRowSetImpl();
                firstRowSet.populate(statement.getResultSet());
                firstRowSet.setMatchColumn(new int[] {1, 2});

                Statement st = establishConnection().createStatement();
                if (st.execute("select host, user, max_user_connections from user")) {
                    JdbcRowSet secondRowSet = new JdbcRowSetImpl(st.getResultSet());
                    secondRowSet.setMatchColumn(new int[] {1, 2});


                    JoinRowSet joinRowSet = new JoinRowSetImpl();
                    joinRowSet.addRowSet(firstRowSet);
                    joinRowSet.addRowSet(secondRowSet);


                    out.println("joinRowSet.getMetaData() = " + joinRowSet.getMetaData().getColumnCount());
                    while (joinRowSet.next()) {
                        out.println("joinRowSet.getString(\"host\") = " + joinRowSet.getString("host"));
                        out.println("joinRowSet.getString(\"user\") = " + joinRowSet.getString("user"));
                        out.println("joinRowSet.getString(\"password\") = " + joinRowSet.getString("password"));
                        out.println("joinRowSet.getString(\"max_user_connections\") = " + joinRowSet.getString("max_user_connections"));
                        if (!joinRowSet.isLast()) {
                            out.println("-- next row --");
                        }
                    }
                }
                out.println(" -------- --------- -------- --------- -------- --------- ");
            }
        } catch (Exception e) {
            log.log(Level.SEVERE, e.getMessage(), e);
        }
    }


}