Tutorial: Advanced Java Programming And Database Connection

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

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

2

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

3

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

4

The try Block • try block: try { System.out.println("Entering try statement"); out = new PrintWriter(new FileWriter("OutFile.txt")); } for (int i = 0; i < size; i++) out.println("Value at: " + i + " = " + victor.elementAt(i));

• A try statement must be accompanied by at least one catch block or one finally block. Advanced Java Programming – Eran Toch Methodologies in Information System Development

5

The catch Block • catch block handles the exception: try { . . . } catch (ArrayIndexOutOfBoundsException e) { System.err.println("Caught ArrayIndexOutOfBoundsException: " + e.getMessage()); } catch (IOException e) { System.err.println("Caught IOException: " + e.getMessage()); }

• Multiple catch blocks can be placed, each handling a different type of exception Advanced Java Programming – Eran Toch Methodologies in Information System Development

6

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

7

Catching Multiple Exceptions – cont’d • Example: The following catch block, will catch all types of exceptions: } catch (Exception e) { System.err.println("Exception caught: " + e.getMessage()); }

Advanced Java Programming – Eran Toch Methodologies in Information System Development

8

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: finally { if (out != null) { System.out.println("Closing PrintWriter"); out.close(); } else { System.out.println("PrintWriter not open"); } }

• Used frequently for cleanup processes. Advanced Java Programming – Eran Toch Methodologies in Information System Development

9

Putting it All Together public void writeList() { PrintWriter out = null; try { System.out.println("Entering try statement"); out = new PrintWriter( new FileWriter("OutFile.txt")); for (int i = 0; i < size; i++) out.println("Value at: " + i + " = " + victor.elementAt(i)); } catch (ArrayIndexOutOfBoundsException e) { System.err.println("Caught ArrayIndexOutOfBoundsException: " + e.getMessage()); } catch (IOException e) { 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

10

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 public Object pop() throws EmptyStackException { Object obj; if (size == 0) throw new EmptyStackException(“exception text”); obj = objectAt(size - 1); setObjectAt(size - 1, null); size--; return obj; } Advanced Java Programming – Eran Toch Methodologies in Information System Development

11

Exceptions and JavaDoc • Exception can be documented by Javadoc using the @exception statement /** * regular javadoc text… * @throwsExceptionIf the Driver was not found. * @throwsSQLExceptionIf the the DriverManager.getConnection * method returned an error. */ public void createConnection()throws SQLException, Exception{

Advanced Java Programming – Eran Toch Methodologies in Information System Development

12

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

13

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

14

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

15

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

16

Accessing Database 1. • • •

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

17

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

18

Example – Connection import java.sql.*; public class SQLConnect { Connection conn = null; Statement stmt = null; ResultSet rs = null;

Importing java.sql.* that contains all the classes we need Connection, Statement and ResultSet are defined as class variables

public SQLConnect(){} public void createConnection(){ try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception E){ Dynamically loading the specific System.out.println(E); JDBC driver. The runtime environment } must know where the library is located! try{ conn = DriverManager.getConnection("jdbc:mysql://localhost/mytest?user=test master&password=1234"); } Connecting to the database using catch (SQLException E){ the url System.out.println(E); } } Advanced Java Programming – Eran Toch Methodologies in Information System Development

19

Example – Locating Libraries • If the following error message occurs: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver java.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 the classpath

Project properties window in Eclipse Advanced Java Programming – Eran Toch Methodologies in Information System Development

20

Example – Access and Query Creating a statement public String getPhones(){ String output = ""; Creating a ResultSet, based try { on a SQL statement stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM phones"); if (rs != null){ while (rs.next()){ Going through the output += rs.getString("phone") + "\n"; ResultSet by using } rs.next(). Remember – you } need to call the next method } before you start reading from the ResultSet catch (Exception E){ System.out.println(E.getMessage()); }

Reading a field from the ResultSet Advanced Java Programming – Eran Toch Methodologies in Information System Development

21

Example – Cleaning off finally { if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) {} rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) {} stmt = null; } } return output; }

Cleaning off the connection, in a different method (why?) Advanced Java Programming – Eran Toch Methodologies in Information System Development

Cleaning off is best done in the “finally” clause Cleaning off ResultSet

Cleaning off Statement, after the ResultSet public void closeConnection(){ if (conn != null){ try { conn.close(); } catch (SQLException sqlEx){} conn = null; } } 22

Example – Test Client public class Test { public static void main(String[] args) { SQLConnect connect = new SQLConnect(); connect.createConnection(); String allPhones = 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

23

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

24

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

25