001package bradleyross.library.debugging;
002import org.slf4j.Logger;
003import org.slf4j.LoggerFactory;
004import bradleyross.library.helpers.ExceptionHelper;
005import java.sql.Statement;
006import java.sql.Connection;
007import java.sql.ResultSet;
008// import java.sql.PreparedStatement;
009import java.sql.SQLException;
010// import java.sql.Driver;
011import java.sql.DriverManager;
012// import java.sql.Connection;
013// import java.sql.ResultSet;
014// import java.sql.Statement;
015// import java.sql.ResultSetMetaData;
016import java.util.List;
017import java.util.Properties;
018import java.util.ArrayList;
019/**
020 * 
021 * @author Bradley Ross
022 *
023 * @see Connection
024 * @see DriverManager
025 * @see Statement
026 * @see ResultSet
027 */
028public class SQLExample {
029        protected static Logger logger = null;
030        protected static ExceptionHelper helper = null;
031        protected String accountName = "sample";
032        protected String password = "mypass";
033        protected int portNumber = 1521;
034        protected String domainName = "localhost";
035        protected String handlerClass="com.mysql.jdbc.Driver";
036        protected String connectionString="jdbc:mysql://localhost/sample" + 
037                        "?sessionVariables=sql_mode='ANSI'";
038        static {
039                Properties props = System.getProperties();
040                if (!props.containsKey("catalina.home")) {
041                        System.setProperty("catalina.home", System.getProperty("user.home"));
042                }
043                logger = LoggerFactory.getLogger(bradleyross.library.debugging.SQLExample.class);
044                helper = new ExceptionHelper(logger);
045        }
046        public void run() {
047                Connection connection = null;
048                Statement statement = null;
049                try
050                {
051                        Class.forName(handlerClass).newInstance();
052                }
053                catch (InstantiationException e)
054                {
055                        List<String> strings = new ArrayList<String>();
056                        strings.add("*** Error in establishing instance of handler");
057                        strings.add("*** Instantiation Exception");
058                        helper.error(strings, e);
059                        return;
060                }
061                catch (IllegalAccessException e )
062                {
063                        List<String> strings = new ArrayList<String>();
064                        strings.add("*** Error in establishing instance of handler");
065                        strings.add("*** Illegal Access Exception");
066                        helper.error(strings, e);
067                        return;
068                }
069                catch (ClassNotFoundException e)
070                {
071                        List<String> strings = new ArrayList<String>();
072                        strings.add("*** Error in establishing instance of handler");
073                        strings.add("*** Class Not Found Exception");
074                        helper.error(strings, e);
075                        return;
076                }
077                try
078                {
079                        connection = DriverManager.getConnection(connectionString, accountName,
080                                        password); 
081                        connection.setAutoCommit(true);
082                }
083                catch (SQLException e)
084                {
085                        List<String> strings = new ArrayList<String>();
086                        System.out.println("SQL Exception");
087                        System.out.println(e.getMessage());
088                        helper.error(strings, e);
089                        return;
090                }
091                /*  First statement */
092                helper.info("Database is now open");
093                helper.info("First statement is SELECT STATE, NAME FROM STATE");
094                try {
095                        statement = connection.createStatement();
096                        helper.info("Statement created");
097                        ResultSet rs = statement.executeQuery("SELECT STATE, NAME FROM STATE");
098                        helper.info("ResultSet created");
099                        while (rs.next()) {
100                                System.out.println(rs.getString("STATE") + "  " +
101                        rs.getString("NAME"));
102                        }
103                } catch (SQLException e) {
104                        helper.error("Problem with first statement", e);
105                } catch (RuntimeException e) {
106                        helper.error("RuntimeException encountered", e);
107                        throw e;
108                } finally {
109                        try {
110                                if (statement != null) {
111                                        statement.close();
112                                }
113                        } catch (SQLException e) {
114                                helper.error("Problem closing statement", e);
115                        }
116                }
117                /* 
118                 * Second statement
119                 */
120                helper.info("Second statement is SELECT STATE, NAME FROM NOTTHERE");
121                try {
122                        statement = connection.createStatement();
123                        helper.info("Statement created");
124                        ResultSet rs = statement.executeQuery("SELECT STATE, NAME FROM NOTTHERE");
125                        helper.info("ResultSet created for second statement");
126                        while (rs.next()) {
127                                System.out.println(rs.getString("STATE") + "  " +
128                        rs.getString("NAME"));
129                        }
130                } catch (SQLException e) {
131                        helper.error("Problem with second statement", e);
132                } catch (RuntimeException e) {
133                        helper.error("RuntimeException encountered in second statement", e);
134                        throw e;
135                } finally {
136                        try {
137                                if (statement != null) {
138                                        statement.close();
139                                }
140                        } catch (SQLException e) {
141                                helper.error("Problem closing second statement", e);
142                        }
143                }
144                helper.info("Third statement is SELECT STATE, NAME, NOTTHERE FROM STATE");
145                try {
146                        statement = connection.createStatement();
147                        helper.info("Statement created");
148                        ResultSet rs = statement.executeQuery("SELECT STATE, NAME, NOTTHERE FROM STATE");
149                        helper.info("ResultSet created");
150                        while (rs.next()) {
151                                System.out.println(rs.getString("STATE") + "  " +
152                        rs.getString("NAME"));
153                        }
154                } catch (SQLException e) {
155                        helper.error("Problem with third statement", e);
156                } catch (RuntimeException e) {
157                        helper.error("RuntimeException encountered in third statement", e);
158                        throw e;
159                } finally {
160                        try {
161                                if (statement != null) {
162                                        statement.close();
163                                }
164                        } catch (SQLException e) {
165                                helper.error("Problem closing third statement", e);
166                        }
167                }
168                try {
169                        connection.close();
170                } catch (SQLException e) {
171                        helper.error("Error while closing connection", e);
172                }
173        }
174        public static void main(String[] args) {
175
176                SQLExample instance = new SQLExample();
177                instance.run();
178        }
179}