CS457 Syllabus & Progress

Error handling in a JDBC program

Effect of execution error

Consider the same program (as in previous example) but with an incorrect query:

import java.sql.*; // Import JDBC package public class Employee { public static void main (String args []) throws Exception { String url = "jdbc:mysql://holland.mathcs.emory.edu:3306/"; String dbName = "companyDB"; String userName = "cs377"; String password = "abc123"; /* ================================== Load the MySQL JDBC driver ================================== */ DriverManager.registerDriver( new com.mysql.jdbc.Driver() ); /* ===================================== Connect to the MySQL database server ===================================== */ Connection conn = null; conn = DriverManager.getConnection(url+dbName,userName,password); /* ============================================== Create a Statement object to process queries ============================================== */ Statement stmt = conn.createStatement (); VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV Ready to process SQL query You can submit multiple queries with the stmt variable ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ /* -------------------------------- Submit a query for execution -------------------------------- */ ResultSet rset = stmt.executeQuery ("select name from employee"); /* ----------------------------------------------------- Iterate through the result and process tuples ----------------------------------------------------- */ while ( rset.next () ) { System.out.println (rset.getString (1) + " " + rset.getString (2) + " " + rset.getString (3) + " " + rset.getString (4) + " " + rset.getString (5) + " " + rset.getString (6) + " " + rset.getString (7) + " " + rset.getString (8) + " " ); } /* ----- Close the ResultSet ----- */ rset.close(); VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV Make sure you FREE the rset set when you are done !!! Statements between these 2 banners can be in a loop.... ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ /* ----- Close the Statement ----- */ stmt.close(); /* ----- Close the connection ----- */ conn.close(); } }

When this program is run, it will exit with an exception:

java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" EmployeeX Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'name' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ....

Example Program: (Demo above code)
- Prog file: /home/cs377001/demo/JDBC/EmployeeX.java

How to run the Demo:

Compile with

cd /home/cs377001/demo/JDBC (demo in class) javac EmployeeX.java

Run program with
The -cp option specifies the classpath that Java must search to look for classes that are used by the running program

Error handling with try/catch-statement

You should always use Java's try/catch-statement (this should be CS170 material) when calling a method in JDBC:

import java.sql.*; // Import JDBC package public class EmployeeX { public static void main (String args []) { String url = "jdbc:mysql://holland.mathcs.emory.edu:3306/"; String dbName = "companyDB"; String userName = "cs377"; String password = "abc123"; /* ================================== Load the MySQL JDBC driver ================================== */ try { DriverManager.registerDriver( new com.mysql.jdbc.Driver() ); } catch ( SQLException e ) { System.out.println("Error: Cannot load mySQL JDBC driver."); System.exit(1); } /* ===================================== Connect to the MySQL database server ===================================== */ Connection conn = null; try { conn = DriverManager.getConnection(url+dbName,userName,password); } catch ( SQLException e ) { System.out.println("Error: Cannot connect to database."); System.exit(1); } /* ============================================== Create a Statement object to process queries ============================================== */ Statement stmt = null; try { stmt = conn.createStatement (); } catch ( SQLException e ) { System.out.println("Error: Cannot create SQL statement object."); System.exit(1); } VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV Ready to process SQL query You can submit multiple queries with the stmt variable ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ /* -------------------------------- Submit a query for execution -------------------------------- */ ResultSet rset = null; try { rset = stmt.executeQuery ("select name from employee"); } catch (SQLException e) { // Handle error - in this example, we print out the error System.out.println("executeQuery( ) error: " + e.getMessage() ); System.exit(1); } /* ----------------------------------------------------- Iterate through the result and process tuples ----------------------------------------------------- */ while ( rset.next () ) { System.out.println (rset.getString (1) + " " + rset.getString (2) + " " + rset.getString (3) + " " + rset.getString (4) + " " + rset.getString (5) + " " + rset.getString (6) + " " + rset.getString (7) + " " + rset.getString (8) + " " ); } /* ----- Close the ResultSet ----- */ rset.close(); VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV Make sure you FREE the rset set when you are done !!! Statements between these 2 banners can be in a loop.... ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ /* ----- Close the Statement ----- */ stmt.close(); /* ----- Close the connection ----- */ conn.close(); } }

Example Program: (Demo above code)

Prog file: /home/cs377001/demo/JDBC/EmployeeX.java

How to run the program:

To compile: javac -cp ".:/home/cs377001/lib/mysql-connector-java.j ar" EmployeeX.java
To run: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" EmployeeX

Output:

cs377@aruba (5129)> java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" EmployeeX executeQuery( ) error: Unknown column 'name' in 'field list'

Comment:

If you wish, you can make your program continue execution after an exception
(I made the program exit by calling System.exit(1).
If I do not call System.exit(1), the Java program will continue execution after the catch-statement)