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
Advanced Java Programming – Eran Toch
Methodologies in Information System Development
With Exception
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. 2. 3. 4. 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
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 catch (SQLException E){ using the url System.out.println(E); } } Advanced Java Programming – Eran Toch
Methodologies in Information System Development 19
Connection, Statement and ResultSet are defined as class variables
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 catch (Exception E){ ResultSet 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 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
Cleaning off the connection, in a different method (why?)
Advanced Java Programming – Eran Toch
Methodologies in Information System Development
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-0922access.html
Advanced Java Programming – Eran Toch
Methodologies in Information System Development 25