import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TestSwingDB {
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

    private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db";

    private static final String DB_USER = "tester";

    private static final String DB_PASSWORD = "******";

    private Connection conn = null;

    public void connect() throws TestSwingException {
        try {
            if (this.conn == null || this.conn.isClosed()) {
                Class.forName(DRIVER_NAME);
                this.conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
                this.conn.setAutoCommit(false);
            }
        } catch (ClassNotFoundException clsEx) {
            clsEx.printStackTrace();
            throw new TestSwingException(clsEx);
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        }
    }

    public PreparedStatement getPreparedStatement(String sql) throws TestSwingException {
        try {
            return this.conn.prepareStatement(sql);
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        }
    }

    public void setPreparedStatementParameters(PreparedStatement preparedStatement, String[][] params)
            throws TestSwingException {
        try {
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setString(Integer.parseInt(params[i][0]) + 1, params[i][1]);
                }
            }
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        } catch (NumberFormatException numberEx) {
            numberEx.printStackTrace();
            throw new TestSwingException(numberEx);
        }
    }

    public int executeUpdate(PreparedStatement preparedStatement) throws TestSwingException {
        try {
            return preparedStatement.executeUpdate();
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        }
    }

    public Object[][] executeQuery(PreparedStatement preparedStatement, int columnSize) throws TestSwingException {
        ResultSet resultSet = null;
        List<Object[]> list = new ArrayList<Object[]>();
        try {
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Object[] record = new Object[columnSize];
                for (int i = 1; i <= columnSize; i++) {
                    record[i - 1] = resultSet.getObject(i);
                }
                list.add(record);
            }
            return list.toArray(new Object[list.size()][]);
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException sqlEx) {
                    sqlEx.printStackTrace();
                    throw new TestSwingException(sqlEx);
                }
            }
        }
    }

    public void commit() throws TestSwingException {
        try {
            if (this.conn != null && !this.conn.isClosed()) {
                this.conn.commit();
            }
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        }
    }

    public void disconnect() throws TestSwingException {
        try {
            if (this.conn != null && !this.conn.isClosed()) {
                this.conn.close();
            }
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new TestSwingException(sqlEx);
        }
    }
}