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}