Tutorial: Advanced Java Programming and Database connection

1. Tutorial: Advanced Java Programming and Database connection Eran Toch Methodologies in the Development of Information Systems November 2003 Advanced Java Programming – Eran Toch Methodologies in Information System Development

2. Agenda • Exceptions and Error Handling • What is it and why do we need it? • The try, catch, finally procedure • Database Access with JDBC • Installations • Connecting and querying the database • Complete example • References Advanced Java Programming – Eran Toch Methodologies in Information System Development

3. Exceptions - Introduction • Definition:  An exception is an event that occurs during the execution of a program that disrupts the normal flow of instructions. • What’s wrong with using the return value for error handling? • Advantage 1: Separating Error Handling Code from "Regular" Code • Advantage 2: Propagating Errors Up the Call Stack • Advantage 3: Grouping Error Types and Error Differentiation Advanced Java Programming – Eran Toch Methodologies in Information System Development

4. Exceptions – Advantage 1 Separating Error Handling Code from "Regular" Code: errorCodeType readFile { initialize errorCode = 0; open the file; if (theFileIsOpen) { determine the length of the file; if (gotTheFileLength) { allocate that much memory; if (gotEnoughMemory) { read the file into memory; if (readFailed) { errorCode = -1; } } else { errorCode = -2; } . . . readFile { try { open the file; determine its size; allocate that much memory; read the file into memory; close the file; } catch (fileOpenFailed) { doSomething; } catch (sizeDeterminationFailed) { doSomething; } catch (memoryAllocationFailed) { doSomething; } catch (readFailed) { doSomething; } catch (fileCloseFailed) { doSomething; } } Without Exception With Exception Advanced Java Programming – Eran Toch Methodologies in Information System Development

5. The try Block • try block: • A try statement must be accompanied by at least one catch block or one finally block. try{ System.out.println("Entering try statement"); out=newPrintWriter(newFileWriter("OutFile.txt")); } for (int i = 0; i < size; i++) out.println("Value at: " + i + " = " + victor.elementAt(i)); Advanced Java Programming – Eran Toch Methodologies in Information System Development

6. The catch Block • catch block handles the exception: • Multiple catch blocks can be placed, each handling a different type of exception try{ ... }catch(ArrayIndexOutOfBoundsExceptione){ System.err.println("Caught ArrayIndexOutOfBoundsException: "+ e.getMessage()); }catch(IOExceptione){ System.err.println("Caught IOException: " +e.getMessage()); } Advanced Java Programming – Eran Toch Methodologies in Information System Development

7. Catching Multiple Exceptions • Java exceptions are Throwable objects Throwable Exception MyException MySpecificException2 MySpecificException1 • Catching MyException will catch both the subclasses. Catching Exception will catch all types of Exceptions Advanced Java Programming – Eran Toch Methodologies in Information System Development

8. Catching Multiple Exceptions – cont’d • Example: The following catch block, will catch all types of exceptions: }catch(Exceptione){ System.err.println("Exception caught: "+e.getMessage()); } Advanced Java Programming – Eran Toch Methodologies in Information System Development

9. The finally Block • We can never be sure that either the try block or the finally block will be fully executed. • finally block code will always be executed: • Used frequently for cleanup processes. finally{ if(out!=null){ System.out.println("Closing PrintWriter"); out.close(); }else{ System.out.println("PrintWriter not open"); } } Advanced Java Programming – Eran Toch Methodologies in Information System Development

10. Putting it All Together publicvoidwriteList(){ PrintWriterout=null; try{ System.out.println("Entering try statement"); out=newPrintWriter( newFileWriter("OutFile.txt")); for(inti=0;i<size;i++) out.println("Value at: "+i+" = "+victor.elementAt(i)); }catch(ArrayIndexOutOfBoundsExceptione){ System.err.println("Caught ArrayIndexOutOfBoundsException: "+ e.getMessage()); }catch(IOExceptione){ System.err.println("Caught IOException: "+e.getMessage()); }finally{ if(out!=null){ System.out.println("Closing PrintWriter"); out.close(); }else{ System.out.println("PrintWriter not open"); } } } Advanced Java Programming – Eran Toch Methodologies in Information System Development

11. throw Statement • All Java methods use the throw statement to throw an exception • The method must declare that it might throw something, by using the throws statement publicObjectpop()throwsEmptyStackException{ Objectobj; if(size==0) thrownewEmptyStackException(“exception text”); obj=objectAt(size-1); setObjectAt(size-1,null); size--; returnobj; } Advanced Java Programming – Eran Toch Methodologies in Information System Development

12. Exceptions and JavaDoc • Exception can be documented by Javadoc using the @exception statement /** * regular javadoc text… *@throwsExceptionIftheDriverwasnotfound. *@throwsSQLExceptionIfthethe<code>DriverManager.getConnection *</code>methodreturnedanerror. */ publicvoidcreateConnection()throwsSQLException,Exception{ Advanced Java Programming – Eran Toch Methodologies in Information System Development

13. Agenda • Exceptions and Error Handling • What is it and why do we need it? • The try, catch, finally procedure • Database Access with JDBC • Installations • Connecting and querying the database • Complete example • References Advanced Java Programming – Eran Toch Methodologies in Information System Development

14. Database Connection - Overview • Four stages: • Install and configure the database • Download and configure the JDBC • Create a connection to the database • Access the database • In this tutorial, examples will be based on MySQL. The reference section include a link to instructions for MS Access. Advanced Java Programming – Eran Toch Methodologies in Information System Development

15. Database Install • Download the MySQL database from:http://www.mysql.com/downloads/ • Install it • Create a specific database:create database mytest; • Create a user account:grant all on mytest.* to eran identified by ‘1234’ Advanced Java Programming – Eran Toch Methodologies in Information System Development

16. JDBC Install • Download Connector/J from:http://www.mysql.com/downloads/api-jdbc.html • Unzip it • In order the library to be found, either: • Copy the .jar file to:$JAVA_HOME/jre/lib/ext • Or, add a classpath to the JDBC:C:\> set CLASSPATH=\path\to\mysql-connector-java-[version]-bin.jar;%CLASSPATH% Advanced Java Programming – Eran Toch Methodologies in Information System Development

17. Accessing Database • Load the driver • Creating a connection object • Create a statement object • Execute an SQL query and get results using the ResultSet object Advanced Java Programming – Eran Toch Methodologies in Information System Development

18. Example – Database Management mysql> create database mytest; Query OK, 1 row affected (0.05 sec) mysql> grant all on *.* to eran@localhost identified by '1234'; Query OK, 0 rows affected (0.14 sec) mysql>create table phones (name varchar(255) not null unique key, phone varchar(25) not null); mysql>describe phones; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | name | varchar(255) | | PRI | | | | phone | varchar(25) | | | | | +-------+--------------+------+-----+---------+-------+ mysql> insert into phones values ('Eran Toch', '+972-4-9831894'); Query OK, 1 row affected (0.11 sec) Creating the DB Creating user account Creating the ‘phones’ table Is everything alright? Let’s see… Inserting some data Advanced Java Programming – Eran Toch Methodologies in Information System Development

19. Example – Connection importjava.sql.*; publicclassSQLConnect{ Connectionconn=null; Statementstmt=null; ResultSetrs=null; publicSQLConnect(){} publicvoidcreateConnection(){ try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch(ExceptionE){ System.out.println(E); } try{ conn=DriverManager.getConnection("jdbc:mysql://localhost/mytest?user=test master&password=1234"); } catch(SQLExceptionE){ System.out.println(E); } } Importing java.sql.* that contains all the classes we need Connection, Statement and ResultSet are defined as class variables Dynamically loading the specific JDBC driver. The runtime environment must know where the library is located! Connecting to the database using the url Advanced Java Programming – Eran Toch Methodologies in Information System Development

20. Example – Locating Libraries • If the following error message occurs:java.lang.ClassNotFoundException: com.mysql.jdbc.Driverjava.sql.SQLException: No suitable driver • Then the driver was not found. • For Eclipse, add it in the project properties window • For runtime, add it to theclasspath Project properties window in Eclipse Advanced Java Programming – Eran Toch Methodologies in Information System Development

21. Example – Access and Query Creating a statement publicStringgetPhones(){ Stringoutput=""; try{ stmt=conn.createStatement(); rs=stmt.executeQuery("SELECT * FROM phones"); if(rs!=null){ while(rs.next()){ output+=rs.getString("phone")+"\n"; } } } catch(ExceptionE){ System.out.println(E.getMessage()); } Creating a ResultSet, based on a SQL statement Going through the ResultSet by using rs.next(). Remember – you need to call the next method before you start reading from the ResultSet Reading a field from the ResultSet Advanced Java Programming – Eran Toch Methodologies in Information System Development

22. Example – Cleaning off finally{ if(rs!=null){ try{ rs.close(); } catch(SQLExceptionsqlEx){} rs=null; } if(stmt!=null){ try{ stmt.close(); } catch(SQLExceptionsqlEx){} stmt=null; } } returnoutput; } Cleaning off is best done in the “finally” clause Cleaning off ResultSet Cleaning off Statement, after the ResultSet publicvoidcloseConnection(){ if(conn!=null){ try{ conn.close(); } catch(SQLExceptionsqlEx){} conn=null; } } Cleaning off the connection, in a different method (why?) Advanced Java Programming – Eran Toch Methodologies in Information System Development

23. Example – Test Client publicclassTest{ publicstaticvoidmain(String[]args){ SQLConnectconnect=newSQLConnect(); connect.createConnection(); StringallPhones=connect.getPhones(); connect.closeConnection(); System.out.println("phones:"); System.out.println(allPhones); } } Output phones: +972-4-9831894 Advanced Java Programming – Eran Toch Methodologies in Information System Development

24. Agenda • Exceptions and Error Handling • What is it and why do we need it? • The try, catch, finally procedure • Database Access with JDBC • Installations • Connecting and querying the database • Complete example • References Advanced Java Programming – Eran Toch Methodologies in Information System Development

25. References • Exception handling in the Java tutorial:http://java.sun.com/docs/books/tutorial/essential/exceptions/index.html • JDBC Tutorial:http://java.sun.com/docs/books/tutorial/jdbc/ • MySQL Tutorial:http://www.mysql.com/documentation/mysql/bychapter/ • MySQL JDBC Connector/J Tutorial:http://www.mysql.com/documentation/connector-j/ • Using Microsoft Access with JDBC:http://www.javaworld.com/javaworld/javaqa/2000-09/03-qa-0922-access.html Advanced Java Programming – Eran Toch Methodologies in Information System Development