Connecting to a Database in Java

1
Connecting to a Database in Java

• Dave Elliman

2
Evolution of Data Storage

• 1960s Magnetic Tape
• 1970s Drum and disk
• random access files
• ISAMs
• Early Databases
• 1980s Relational Databases
• 1990s Client-Server RDB

3
Database Connectivity

• There are good reasons to hold your data in a
  central database server - What are they?
• This idea led to the client-server paradigm which
  has been widely adopted
• Nowadays there may be several kinds of server in
  a multi-tiered environment

4
The Client-server
5
Relational Databases

• A relational Database consists of a set of simple
  rectangular tables or relations
• The column headings are fields
• The rows are records
• A field or set of fields that uniquely identifies
  a record is known as a primary key

6
eg Products Customer
7
SQL Statements

• String qry SELECT code FROM mysecret WHERE
  login xxx03u
• String qry INSERT INTO xxx03u (item, perl)
  VALUES (1,My Wise Saying)
• Where you have a String for the saying called
  plaintxt
• String qry INSERT INTO xxx03u (item, perl)
  VALUES (1,plaintext )

8
Connecting to a Database

• We have a network which supports a communication
  protocol e.g. TCP/IP
• The Database listens to a port (a 2 way thing)
• The Database will define some higher level
  protocol for queries (SQL) and returned results
  (tables)
• You DONT WANT to have to code at this level!
• Hence JDBC (it is all done for you again!)

9
ODBC Came First

• Open DataBase Connectivity
• From Microsoft in 1992
• Procedural C interface
• Few functions with many options (clumsy)
• Slow

10
What is JDBC?

• A Java API for database communication
• A set of classes that perform database
  transactions
• Connect to relational databases
• Send SQL commands
• Process results

11
JDBC Architecture
12
JDBC Requires a Driver

• The driver will come in a jar
• Make sure this is in the CLASSPATH
• Register this with the DriverManager

13
Our Database is MySQL

• This is brilliant and free!
• Driver is mysql-connect.jar
• java -cp .\mysql-connect.jar
• Or put it in
• C\j2sdk1.x.x\jre\lib\ext
• This is always in the CLASSPATH

14
About the Database

• host sauron.cs.nott.ac.uk
• database wisdom
• port 3306
• user xxx03u
• password ISentYouThis"

15
Register the Driver
import java.sql. // You NEED
this! Connection contry Class.forName(org
.gjt.mm.mysql.Driver")catch
(ClassNotFoundException ex) System.out.println(
ex.getErrorCode())
16
Connect to Database
Connection con con DriverManager.getConnection(
url, username, passwd) Where String url
jdbcmysql//sauron.cs.nott.ac.uk3306/ wisdom
17
Create and Execute a Statement

• Statement st con.createStatement()
• String qry SELECT ...
• ResultSet rs st.executeQuery(qry)
• String qry INSERT ...
• st.executeUpdate(qry)

18
JDBC Statements

• Getting the data returned
• String code rs.getString(code")
• Needs to be in a try block
• Of course other methods are
• getInt()
• getDouble()

19
Navigating the ResultSet

• while(rs.next())
• process a row

20
Important point
con.close() when you have finished!
21
What about an Access Database?

• Create your database (mydb.mdb)
• Go into control Panel-gtODBC sources and give the
  database a name and select Microsoft Access
  Driver
• Click OK
• Your database is now an ODBC source

22
How to connect in Java

• DBconnect()
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• pConnectionDriverManager.getConnection("jdbcodbc
  mydb.mdb","","")
• pStatement pConnection.createStatement()
• catch(Exception e) e.getMessage() // print
  this out

23
Thats All You Need to Know!