Accessing relational databases from Java

1
Accessing relational databases from Java

• Nina Summer School August 2006
• Peter Sestoft

2
Why?

• The data in a Java program vanishes when the
  program terminates
• But most data about real-world objects such as
  forests, trees, individual animals, fields and
  crops, the weather, people, scientific
  experiments must be stored and maintained for
  years
• Relational databases are used for this

3
From database to Java

• There are many brands of database
• Microsoft Access part of Microsoft Office
• MySQL very good, simple, open source
• Microsoft SQL Server robust and complex
• Oracle very robust and complex
• IBM DB2 enterprise robust and complex

4
Setup for Java and Windows

• Simplest approach
• Use JDBC Java Database Connectivity
• And ODBC Open Database Connectivity
• Use package java.sql
• Required steps
• Set up a Windows Data Source Name (DSN)
• This gives a name to a database
• Use that name from you Java program

5
DSN for alocal MS Access database

• Click Start
• -gt Control Panel
• -gt Administrative Tools
• -gt Data Sources (ODBC)
• -gt User DSN
• -gt Add
• -gt MS Access Database
• -gt Name northwind-sample
• -gt C\Program Files\Microsoft Office\OFFICE11\SAMP
  LES\Northwind.mdb

6
DSN for a remote MySQL database

• Get the MySQL Connector/ODBC driver from
  www.mysql.com
• Click Start
• -gt Control Panel
• -gt Administrative Tools
• -gt Data Sources (ODBC)
• -gt User DSN
• -gt Add
• -gt MySQL ODBC 3.51 Driver
• -gt Data source name sql-dina-sestoft
• -gt Server sql.dina.kvl.dk
• -gt User sestoft
• -gt Password
• -gt Database sestoft

7
The Java code to access a DSN(partial truth)

• import java.sql.
• public class ReadAccessDatabase
• public void openDb()
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
  
• Connection conn
• DriverManager.getConnection("jdbcodbcnor
  thwind-sample")
• use conn to access the database
• if (conn ! null)
• conn.close()

8
A little more of the truth

• Things may go wrong
• The database does not exist
• We do not have permission to use it
• The network is down
• The MySQL database driver is not installed
• ... and so on
• The Java program must deal with failure
• Java uses try catch () statement for
  this see BK chapter 12

9
Handling things that may go wrong

• public class ReadAccessDatabase
• public void openDb()
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver
  ")
• Connection conn
• DriverManager.getConnection("jdbcodbc
  northwind-sample")
• use conn to access the database
• if (conn ! null)
• conn.close()
• catch (SQLException exn)
• System.out.println("Database problem "
  exn)
• catch (Exception exn)
• System.out.println("Problem " exn)
  

10
Using the database connection(filling in the
dots)

• Statement stmt conn.createStatement()
• String query "SELECT Country FROM Customers"
• boolean ok stmt.execute(query)
• if (ok)
• ResultSet res stmt.getResultSet()
• while (res.next())
• String country res.getString(1)
• System.out.println(country)