Transcript powerpoint document - Department of Information Technology

Database programming in
Java
An introduction to Java Database
Connectivity (JDBC)
Introduction

Two standard ways to work with databases in
Java

JDBC


SQLJ


A Call level interface similar to ODBC
SQL code imbedded in Java, like SQL embedded in C
JDBC is the most common way and it’s
supported by almost all database vendors
Java Database Connectivity



JDBC is a specification from Sun and part of
Java 2
We will talk about JDBC 2
JDBC applications are portable

Switch database without rewriting your program




If there is a driver for the database
If you use only standard SQL (i.e. no vendor specific
code)
JDBC is the Java version of ODBC
There are four levels for JDBC drivers
Level 1 Drivers



Level 1 is a JDBC-ODBC bridge
The actual database communication is done
via a ODBC driver
Requires the Database client library to be
installed


The ODBC drivers and all libraries that the driver
needs
Suns JDBC-ODBC bridge is single threaded
Level 2 Drivers


This is a partly Java solution
All JDBC calls are converted from to calls in
the vendor specific client API

The library must be installed on the client machine
Level 3 Drivers


Level 3 is a multi tier solution
On the client it’s all Java


No vendor specific client library is needed
The connection is made to a server that
connects to the database


The server can use ODBC or some other
technology
Several databases can be supported by the
server
Level 4 Drivers





Level 4 is an all Java solution
No client API is needed besides the JDBC
Driver
This is the most common type, and the one
that we will use
All JDBC calls are directly transformed to the
vendor specific protocoll
Direct calls from the client to the database
server
Important JDBC Classes/Interfaces







java.sql.DriverManager
java.sql.Driver
java.sql.Connection
java.sql.Statement
java.sql.PreparedStatement
java.sql.CallableStatement
java.sql.ResultSet



Scrollable or not
Updateable or not
javax.sql.DataSource
java.sql.DriverManager


The DriverManager is responsible for loading
the correct Driver
The DriverManager is used to get a
connection to the database
java.sql.Driver




This is the actual implementation of the JDBC
Driver
The only part that’s vendor specific
Used if DriverManager is used to get
connection
Loaded with Class.forName(“driverclass”)

The driver name for Mimer SQL is
“com.mimer.jdbc.Driver”
java.sql.Connection



A Connection represent an actual connection
to the database
The Connection is used to create statements
(queries)
A Connection is returned from the
DriverManager


DriverManger.getConnection(url, username,
password)
DriverManager.getConnection(url)
java.sql.Connection – important methods






setAutoCommit(boolean)
createStatement()
prepareStatement(“SQL query”)
commit()
rollback()
close()

ALLWAYS close your connections
java.sql.Connection – important methods

getMetaData() returns a DatabaseMetaData
object

From the DatabaseMetaData you can get
information about the database



Vendor name
Version
Supported functions
java.sql.Statement


A Statement is the simplest of the statement
types
It’s used to pass a query to the database and
to return a ResultSet
java.sql.Statement - important methods

executeQuery(“sql query”)


execute(“sql query”)




Returns a ResultSet
Mostly used when the type of query is unknown
executeUpdate(“sql query”)
getResultSet()
close()

ALLWAYS close your Statements
java.sql.PreparedStatement




A prepared statement is a Statement with
parameters
The prefered way if you have conditions in
your query
Will be compiled once at the server and then
cached
Give you an easier to read code
java.sql.PreparedStatement – important
methods
Can do all that a Statement can
 setXXX() is used to set the different
parameters
pstmt = con.prepareStatement(“select * from
person where cars=`?”);
pstmt.setInt(1,carId);
pstmt.executeQuery();

java.sql.CallableStatement



CallableStatement is used to prepare and call
stored procedures in the database
prepareCall(“statement”)
execute()
java.sql.ResultSet




The ResultSet is used to get the information
from the Database
Retured from executeQuery() or
getResultSet()
Like a cursor in embedded SQL
Just like with Connections and Statements,
ALLWAYS close when you’re done
java.sql.ResultSet


Before the first fetch, the position is before
the first row
ResultSet can be of several types

Updateable



Can be used to perform updates in the database directly
Rows can be inserted
Scrollable

The cursor can be moved forward and backwards
java.sql.ResultSet – important methods

next()





getXXX(position)


Used when looping over the result
Returns true if there was a row to fetch and false otherwise
Moves the cursor one step forward
The classic loop is while(rs.next()) where rs is a ResultSet
Gets the column with postion
getXXX(name)


Gets the column with the matching name
The name is the same as in the select list
java.sql.ResultSet – important methods

getMetaData() returns a ResultSetMeta
where you can get information about the
ResultSet



Number of columns
Type of ResultSet
NOT the number of rows
javax.sql.DataSource

DataSource can be used instead of
DriverManager and Driver

If possible, use it
Retrieved via JNDI (Java Naming and
Directory Interface)
DataSource ds =
(DataSource)context.lookup(“java:com/env/jd
bc/multi1”);
ds.getConnection();

Simple example
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
package com.mimer.kurs.uu.jdbc;
import java.sql.*;
public class JdbcOne {
public static void main(String[] args) {
try{
Class.forName("com.mimer.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mimer:multi1","fredrik","fredrik");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES");
while(rs.next()){
System.out.println(rs.getString("TABLE_NAME"));
}
}
catch(Exception e){
System.out.println("Error:" + e.getMessage());
}
}
}
More advanced example
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
package com.mimer.kurs.uu.jdbc;
import java.sql.*;
import java.io.*;
public class JdbcTwo {
public static void main(String[] args) {
String driver="com.mimer.jdbc.Driver";
String url="jdbc:mimer:multi1";
String username="fredrik";
String password="fredrik";
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection con = null;
//All accessible tables for the current ident
String query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=?";
try{
Class.forName("com.mimer.jdbc.Driver");
con = DriverManager.getConnection(url,username,password);
pstmt = con.prepareStatement(query);
pstmt.setString(1, "BASE TABLE");
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("TABLE_NAME"));
23.
}
24.
25.

}
More advanced example, continued
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
catch(ClassNotFoundException cnfe){
System.out.println("Could not load Driver");
}
catch(SQLException sqle){
System.out.println("SQL Error: " + sqle.getMessage());
}
catch(Exception e){
System.out.println("Error:" + e.getMessage());
}
finally{
try{
rs.close();
}
catch(Exception e){
}
try{
pstmt.close();
}
catch(Exception e){
}
try{
con.close();
}
catch(Exception e){
}
}
52.
}
53.
54.
}
Assignment
1.
Create a table in the database:
create table PERSON(
PNR INTEGER,
NAME CHARACTER(10default 'Unknown',
SURNAME CHARACTER(10),
SEX CHARACTER(4) not null,
AGE INTEGER,
primary key(PNR));
Assignment

Create a simple Java program that adds
persons to the database.



It can be interactive or it can take all the
arguments on the commandline
Tip: use PreparedStatement
Create a simple Java program that lists all
persons older than a given age


It can be interactive or it can take all the
arguments on the commandline
Tip: use PreparedStatement