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